SQL Server 2016 general availability

It is now official that SQL Server 2016 will be out on 1st of June 2016. As some of you may know Microsoft dropped the BI edition in it's 2016 release. Looks like there is now Enterprise, Standard, Express and Developer Edition. SQL Server developer edition is now a free edition providing the full feature set of SQL server Enterprise and can be used only for development and testing.

Microsoft SQL Server Announcement

• Microsoft has indicated that support for SQL Server 2005 will end as of April 12^th, 2016.

·         No access to critical security updates, opening the potential for business interruptions

·         Higher maintenance costs for maintaining legacy servers, firewalls, intrusion systems mean increased costs for the business

·         Compliance concerns – as support ends, organizations will likely fail to meet regulatory standards compliance 

• With the introduction of SQL Server 2016, Microsoft will no longer offer the Business Intelligence edition. Beyond this change, they don’t anticipate any changes in the licensing model with the release of SQL Server 2016.

When April the full joke become a reality in just under 5 years

How to run SQL Server on Linux

see this post back in April 1 2011.

http://tenbulls.co.uk/2011/04/01/how-to-run-sql-server-on-linux/

SQL Server on Linux

http://blogs.microsoft.com/blog/2016/03/07/announcing-sql-server-on-linux/

Instance wide script to add user (the 4th way)

My new script to add user to all databases in an instance published on http://www.sqlservercentral.com/ and can be found Instance wide script to add user (the 4th way). The script can be modified and can be used for various purpose.

SQL Server 2016 release date

Looking at the history of the last Microsoft releases, it looks like the release of SQL 2016 will be either March or April.. Hope my prediction is right:)

SQL Server	Release_Date
SQL Server 2016	2016-04/03-??
SQL Server 2014	01/04/2014
SQL Server 2012	06/03/2012
SQL Server 2008 R2	21/04/2010
SQL Server 2008	2008-08-07
SQL Server 2005	07/11/2005

Gartner positions Microsoft as a leader in BI and Analytics Platforms

For the first time, Microsoft is placed furthest in vision within the Leaders quadrant.

sp_server_diagnostics

sp_server_diagnostics sp is used to capture quick  diagnostic data and health information about your SQL Server to detect potential issues. It is a must run sp that I start using recently as a quick check on your SQL server stack. You can even run this procedure when using DAC.   For more detailed info visit an article on www.mssqltips.com.

The new Community Tech Preview of SQL Server 2016, CTP3

I want to point out two features that is either added or improved with CTP3 release of SQL Server 2016.

New addition :  You will now be able to setup transnational replication from in premise to Azure SQL (Microsoft's cloud based version of SQL Server). this will for sure make it easier to migrate data to cloud with little or no downtime

 Improvement: Integration of R (Revolution Analytics)  package that will allow you to run R analytics in the database without pulling out data for analysis on a separate location or machine.  This preview also added the ability to execute R scripts via system stored procedure to query and pass data to R

Azure data warehouse architecture

Diagram source : https://azure.microsoft.com/en-gb/documentation/articles/sql-data-warehouse-overview-what-is/

Azure SQL Data Warehouse is a distributed database capable of processing big data (both relational and non-relational). Microsoft claims that it is the first cloud based data warehouse that combines SQL capabilities with scalability at its core.

Clustered Columnstore Tables are now default on Azure SQL Data Warehouse platform.

For more info visit: https://azure.microsoft.com/en-us/updates/clustered-columnstore-tables-are-default-in-azure-sql-data-warehouse/

SQL 2016 CTP 2.4 out now

The SQL server team at Microsoft announced availability of SQL Server 2016 CTP 2.4 yesterday. Main changes from previous CTP are:

1. Operational Analytics and In-Memory Data Warehouse
2. x86 server deprecation
3. SQL Server Smart Maintenance Plans
4. Tempdb Scalability improvement
5. SQL Server Integration Services (SSIS) improvements
6. SQL Server Reporting Services (SSRS) improvements

SQL Server 2016 CTP 2.2 is out

Microsoft released SQL Server 2016 CTP 2.2. A bit faster than I thought for this version to be out.  For detailed list of improvements on previous CTP and download go to  http://blogs.technet.com/b/dataplatforminsider/archive/2015/07/28/sql-server-2016-community-technology-preview-2-2-is-available.aspx

Native JSON support (SQL 2016)

The new release of SQL server has taken further steps and added native JSON support. JSON lately become one of important part of any developer’s toolkit. Recently it become clear that sites are start sharing data using JSON in addition to RSS feeds. JSON feeds can be loaded asynchronously much more easily than XML/RSS

JSON (JavaScript Object Notation) outputs human-readable collection of data that consists name/value pairs with individual values separated by comma. Objects are containers of one or more name/value pairs and are contained within curly brackets as shown below.

For instance it will store data as

var Product = {

        "ProductId" : "24",

        "ProductName" : "Lego Jurassic World",

        "Price" : "$30.00"

        };

Appending FOR JSON AUTO to a standard SELECT statement returns the result set in a JSON format. JSON arrays can contain multiple objects, and arrays are contained within square brackets.  Note here JSON is not treated as another data type in SQL server like XML.

Let us now create a table in SQL server and insert some data and output the result.

create table products (productId int primary key not null, productName nvarchar(100), Price money)

go

insert into products(productId, productName, Price)

values (1, 'Terraria Xbox 360', 12),

       (2, 'Batman Arkham Origins',6),

          (3, 'FIFA 16', 40)

go

select productId, ProductName, Price from [dbo].[Products]

FOR JSON PATH , ROOT ('PRODUCTS')

The result of the above select statement is.

{"PRODUCTS":[{"productId":1,"ProductName":"Terraria Xbox 360","Price":12.0000},

                 {"productId":2,"ProductName":"Batman Arkham Origins","Price":6.0000},

                 {"productId":3,"ProductName":"FIFA 16","Price":40.0000}]}

 A simple T-SQL construct with “FOR JSON PATH” outputs JSON output that can be feed to web site.

Azure SQL Data Warehouse - New Cloud offering

Microsoft launched a new cloud based data warehousing services with 21 partners on 24^th of June. Microsoft announced this offerings during build developer conference in Aril in San Francisco (http://venturebeat.com/2015/04/29/microsoft-announces-azure-sql-data-warehouse-and-azure-data-lake-in-preview/)

It is comparable to the leading public cloud Amazon web services Redshift (http://venturebeat.com/2015/04/10/amazons-latest-acquisition-amiato-built-a-cloud-data-warehouse-before-amazon-launched-redshift/)  and the cloud data warehouse from snowflake computing (http://venturebeat.com/2015/06/23/snowflake-raises-45m-and-makes-its-cloud-data-warehouse-generally-available/)

As usual Microsoft designed the SQL data warehouse for databases in the range of 5TB to 10TB. 

Well, that is way too small. As this a limited public preview it will definitely change when it comes operational. 

I personally believe this is indeed a very good move both for Microsoft and for businesses that cannot afford to buy the PDW appliance.

Polybase (Now in Editions of 2016 as new feature)

What is Polybase?  Polybase is a new technology that integrates PDW (SQL server Parallel Data warehouse) with Hadoop Distributed File System.  It used to only work with PDW and most small to medium enterprise that doesn’t have the appliance weren’t able benefit. 

Polybase allows users to access/query non-relational data in Hadoop, blobs, files, data from either on premise or on the cloud and run analytics and BI on the data from within SQL server. It also provides a concept of Data Lake where you query the data from where it is stored and once you complete your query leave it where it was. This concept will facilitate analysis on Big Data from its current location and reduce the costs associated in moving the data. The following diagram is taken from Microsoft white paper and shows the interaction that you can have with different data sources from within SQL server when using PolyBase feature.

 

This feature is now available with Standalone enterprise version of SQL Server 2016. That is really a game changer.  I would love to play with this feature soon and hopefully post on my blog here…