Point in Time Recovery with SQL Server

Read my latest article on Point in Time Recovery. 
http://www.sqlshack.com/point-in-time-recovery-with-sql-server

Azure SQL Data Warehouse with Improved Loading and Monitoring in Azure Portal and SSMS

Azure SQL Data Warehouse has received some significant changes in the Azure Portal, including Azure Data Factory integration, a new troubleshooting blade, and changes to the query drill-down blade

Read more https://winbuzzer.com/2016/12/20/microsoft-updates-azure-sql-data-warehouse-improved-loading-monitoring-azure-portal-ssms-xcxwbn/.

Microsoft R Server 9.0 now available

Microsoft today announced the availability of R Server 9.0 on MSDN and Visual Studio Dev Essentials. This new release is built on top of the latest open source R engine, and comes with several new capabilities. In MRS 9.0, you can combine the algorithms delivered in this package with pre-existing parallel external memory algorithms such as the RevoScaleR package as well as open source tech like CRAN R packages to deliver the best predictive analytics.

Read more at https://mspoweruser.com/microsoft-r-server-9-0-now-available-download/

http://www.cio.com/article/3147822/analytics/microsoft-pushes-r-sql-server-integration.html

Big Announcements in SQL Server 2016

As of SQL Server 2016 SP1, which was shipped this month, from a programmability perspective there is no difference between the editions anymore. All programmability features will be available in all editions from LocalDB to Enterprise edition.

Read more at http://itknowledgeexchange.techtarget.com/sql-server/big-announcements-in-sql-server-2016/

Redgate Announces New SQL Server Database Cloning Tool

Redgate, a Cambridge-UK based software company that develops SQL Server tools, has launched the beta of its new database cloning tool called SQL Clone that enables databases to be cloned quickly, while saving up to 99% of disk space.

According to the company, the new technology resolves a long-standing issue in software development. Typically, this involves database administrators having to provision a copy of the database for each developer request, which takes up valuable time as well as disk space. The result is that teams end up working on outdated versions of the database in a shared environment, rather than having the freedom to work on isolated local versions that can be created and deleted rapidly.

Read more http://www.dbta.com/Editorial/News-Flashes/Redgate-Announces-New-SQL-Server-Database-Cloning-Tool-114985.aspx

CREATE OR ALTER In One Statment

When ever we execute stored procedure script, we always drop the existing one and recreate it.

IF EXISTS (SELECT * FROM sys.procedures WHERE Name = 'Test')
BEGIN
DROP PROC Test
END
GO

CREATE PROCEDURE Test
AS
BEGIN
       SELECT 1
END

However, with SQL Server 2016 SP1, now there is easy way of doing with one statement.

CREATE OR ALTER PROCEDURE Test

AS

BEGIN

         SELECT 1

END

Obvious advantage is this is much cleaner now. This feature is available for Functions, Triggers and Views as well. 

There is an another important benefits out of this.

If you check the stored procedure stats for the drop and create method by running the following query, you will end up with below results. 

SELECT name,create_date,modify_date

 FROM sys.procedures WHERE Name = 'Test'

In this, both the create and the modify dates are same. However, for the new method, now it has a different modify date which means you can track your objects much better. 

SQL Server 2016 Service Pack 1 (SP1) released

Microsoft has announced the availability of SQL Server 2016 Service Pack 1 (SP1). With SQL Server 2016 SP1 key improvements were made allowing a consistent programmability surface area for developers and organizations across SQL Server editions.

2. Now express edition has the lot of features where it was limited to enterprise before. Partitioning, Compression ad In Memory OLTP will be available now for Standard and Express as well.




2. DBCC CLONEDATABASE is added which was missing in SQL Server 2016 RTM and was with SQL Server 2014.

3. Tempdb supportability – A new Errorlog message indicating the number of tempdb files and notifying different size/autogrowth of tempdb data files at server startup.

4, DROP TABLE support for replication – DROP TABLE DDL support for replication to allow replication articles to be dropped.

For all the details, please refer to https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/

Microsoft announces the next version SQL Server for Windows and Linux

Microsoft’s announcement that it was bringing its flagship SQL Server database software to Linux came as a major surprise when the company first announced this in March. Until now, the preview was invite-only, but as Microsoft announced today, anybody who wants to give it a try can now download the bits. That public preview is part of the launch of the next version of SQL Server, which will be the first one that’s available for both Windows and Linux.

Read more at https://techcrunch.com/2016/11/16/microsofts-sql-server-for-linux-is-now-available-for-testing/

Big Stories on Big Data

Every one talks big on big data, But has it really implemented in real world. This article tells us about real world implementation on Big Data.

Read the full article at http://www.smartdatacollective.com/jessoaks11/330428/4-big-companies-using-big-data-successfully

Migration Check List

As SQL Server 2005 support ends. many of the users are now thinking about migration, here is the check list for the database migration.

1.How many (and where are they located) SQL Server 2005 instances in use today?

2. How many databases are hosted on the SQL Server 2005 instances?

3. How much storage is required to accommodate (present/long term) these databases?

4. Are the SQL Server instances targeted for consolidating, physical or Virtual?

5. If you are moving to Virtualization, is your virtual infrastructure storage capable of supporting all consolidated SQL Server databases?

6. Are there any 32-BIT instances of SQL Server?

7. Are there any SQL Server 2005 Failover Clustered Instances? a. Details on configuration, servers, storage utilization/type etc.

8. What SQL Server Instance Security Model is required (SQL/Windows or Mixed)?

9. Are there compliance and or audit considerations?

10. Is this Database instance consolidation only or does it involve SSAS, SSRS, SSIS?

11. Provide overview of applications are using SQL Server 2005?
 a. Overview on Application/IIS Servers
 b. Prioritize by mission criticality

12. How are the applications written with regards to accessing the SQL Server Instance name?
a. SQL Alias
b. DSN
c. Hardcoded connection string
d. Are you in a position (developers or 3rd party) to handle the application level changes?
e. What does the application vendor support for latest version of SQL Server?

13. Do you have an understanding of existing workloads for each instance in use today?

14. How is “good performance” measured?
a. As being able to accommodate x number of connections?
b. Server side performance?
i. CPU load, I/O load, user load?
c. End user response to Applications?

15. What is max allowable downtime for instances/databases being upgraded?

16. Details of SQL Server environment:
a. Relational Database: i. Online Transaction Processing (OLTP) ii. Data Warehouse (EDW)
b. SQL Analysis Services (SSAS)
c. SQL Reporting Services (SSRS)
d. SQL Server Integration Services (SSIS)

17. Architecture Details:
a. Failover Cluster
b. Virtualization
c. Geography
d. Replication (SAN)
e. Replication (DB)
f. Mirroring

New age computing could make a difference at Rio 2016 Olympics

Artificial intelligence and machine learning could make the difference where winning margins are just hundredths of a second at Rio 2016.

Already there are reports of Britain’s rowing team and Brazil’s canoeists turning to analytics at Rio with IBM, SAS and GE among companies offering it.

While in Australia the technology is in pilot phase, Microsoft, BizData and The Australian Institute of Sport have taken giant strides in that direction by using machine learning to crunch three years of solid data to predict when an Australian athlete might suffer injuries and illness induced by factors in their body.

Microsoft Australia Director, Cloud & Enterprise Business Group Toby Bowers said machine analysis could make a huge difference in Olympic competition. “The solution that they’ve built with our technology using predictive analytics predicts when an athlete’s going to be injured three days before it happens,” Mr Bowers said.

Read more at http://www.theaustralian.com.au/business/technology/supercomputers-could-make-a-difference-at-rio-2016-olympics/news-story/b259219b5ed86ebf5d7798d476bb74f5

Disable Column Store Indexes for One Database

Disabling column store indexes needs to be done time to time. Following query will generate the script. 

SELECT 'ALTER INDEX [' + Name + +'] ON ' + OBJECT_NAME(object_id) + ' DISABLE'

FROM sys.indexes WHERE TYPE_DESC

LIKE '%COLUMNSTORE%'

Output of the query is shown below.

ALTER INDEX [IndFactResellerSalesXL_CCI] ON FactResellerSalesXL_CCI DISABLE

ALTER INDEX [NonClusteredColumnStoreIndex-20160622-204327] ON DimEmployee DISABLE

ALTER INDEX [NonClusteredColumnStoreIndex-20160622-210321] ON FactInternetSales DISABLE

Free ebook: Introducing Microsoft Power BI

Get started quickly with Microsoft Power BI! Experts Alberto Ferrari and Marco Russo will help you bring your data to life, transforming your company’s data into rich visuals for you to collect and organize, allowing you to focus on what matters most to you. Stay in the know, spot trends as they happen, and push your business to new limits.
This free ebook introduces Microsoft Power BI basics through a practical, scenario-based guided tour of the tool, showing you how to build analytical solutions using Power BI. Read the ebook to get an overview of Power BI, or dig deeper and follow along on your PC using the book’s examples.

Download the book from here.

Demand for SQL Server Skills in India

India’s Information Technology sector is regarded as the biggest private sector employer, with over 10 million employees across India. Talent assessment platform Youth4work has announced its findings about the top five desired skill sets in the Indian IT sector. ASP.net is the most in-demand skill set in the IT sector followed by SQL Server, PHP, Advance Java and JavaScript.

Here are the salary comparison for SQL Server in India. 

Here are the more details about this http://yourstory.com/2016/06/in-demand-it-skill-sets-india/

SQL Server 2016 First Installation is in 2015

Would you believe that first SQL Server 2016 production installation done in 2015. Yes it is. The first customer adopted SQL Server 2016 in production a couple days after it was released CTP2.  CTP2 is the first production-ready build for SQL Server 2016 not just a beta version.

If you have not yet installed SQL Server 2016, it is good time now.

Read more about SQL Server 2016 here

Discontinued Database Engine Functionality in SQL Server 2016

Major discontinued feature of the SQL Server 2016 feature is that exclusion of 32 bit of the SQL Server. So now on, you have only 64 bit version.

Only other discontinued feature is, as usual dropping of the 3rd before compatible level which is 90 or SQL Server 2005.

-https://msdn.microsoft.com/en-us/library/ms144262.aspx

However, even in SQL Server 2014, 90 compatible level which is also an discontinued feature.

https://msdn.microsoft.com/library/ms144262(v=sql.120)

I don't understand the possibility of discontinuing a feature which is already discontinued. Typically Microsoft keeps three versions of backward compatibility. however, in SQL Server 2016 , 4 previous versions of backward compatibility is kept as shown below.