On 16th Nov 2016 Microsoft announced the first Community Test Preview (CTP 1.0) of SQL Server vNext that will run both on Windows & Linux. Not only Linux, but it will be supported on Docker, and macOS (via Docker) too.
For now I’m calling the vNext version as SQL Server 2018. (2018 is not called officially, I’m calling it just to avoid any confusion 🙂 )
This announcement was along with the release of SQL Server 2016 SP1, and the vNext version also supports features added in SQL Server 2016 SP1.
–> Download vNext bits:
To download the SQL Server vNext you can Register and Download the Full version or Free evaluation version (180 days).
Or, directly download the ISO (~2 GB): SQLServerVnextCTP1-x64-ENU.iso
–> Check version and SQL build:
Microsoft SQL Server vNext (CTP1) – 18.104.22.168 (X64)
Nov 1 2016 23:24:39
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Enterprise 6.3 (Build 14393: ) (Hypervisor)
–> New Features & Enhancements:
>> Database Engine:
1. All new features in SQL Server 2016 SP1.
2. New compatibility level 140.
3. Improvements to the way incremental statistics update thresholds are computed.
4. New DMVs added:
– sys.dm_exec_query_statistics_xml added, to get query execution plan for in-flight requests.
– sys.dm_os_host_info, to provide operating system information for both Windows and Linux.
5. Enhancements to In-Memory Tables & SPs:
– Can now use sp_spaceused, sp_rename, CASE, TOP (N) WITH TIES
– More than 8 indexes
6. Clustered Columnstore Indexes now support LOB columns (nvarchar(max), varchar(max), varbinary(max)).
7. New STRING_AGG() aggregate function has been added.
8. Database roles are created with R Services for managing permissions associated with packages.
9. New Japanese collations are added.
– Features added in CTP 1.1
10. Memory-optimized tables and indexes now support computed columns
– Full support for JSON functions
– and CHECK constraints
– Natively compiled modules now support the CROSS APPLY operator
12. New string functions CONCAT_WS, TRANSLATE, and TRIM are added.
– and the WITHIN GROUP clause is now supported for the STRING_AGG function.
– Features added in CTP 1.2
13. Online index build and rebuild support for non-clustered Columnstore indexes.
14. Support for SUSE Linux Enterprise Server v12 SP2
15. SQL Server Early Adoption Program (EAP), msdn blog on EAP
– Features added in CTP 1.3
16. Full text search, now available for all supported Linux distros
17. Resumable online index rebuilds
18. Temporal Tables Retention Policy
19. Indirect checkpoint performance improvements
20. Online non-clustered columnstore index buill and rebuild support added
21. Availability Groups enhancements:
– Cluster-less Availability Groups support added
– Minimum Replica Commit Availability Groups setting
– Availability Groups can now work across Windows-Linux to enable cross-OS migrations and testing
22. New DMV sys.dm_db_stats_histogram, for examining statistics
23. 5 new dynamic management views to return information about Linux process:
>> SQL Server Analysis Services (SSAS):
1. 1400 Compatibility level for tabular models.
2. Modern Get Data query and import data features for tabular models.
3. Support for ragged hierarchies with the new Hide Members property.
4. Support for drillthrough by using Detail Rows. This includes the Default Detail Rows expression property and DETAILROWS DAX function.
5. Table-level security, restricts user permissions on table data and table names.
6. New DAX IN function.
7. Encoding hints, an advanced feature used to optimize processing (data refresh) of large in-memory Tabular models.
>> SQL Server Integration Services (SSIS):
– Support Scale Out of SSIS: to run SSIS on multiple machines. With Scale Out Master and Workers, packages can be distributed to execute on different Workers automatically. Check more details here in MSDN.
>> SQL Server R support: Check full details here in MSDN.
–> Download & Install SQL Server vNext & SSMS:
Its 1st June 2016 and finally Microsoft has released SQL Server 2016 full and final version, and is available for download!
Exactly a year back the first CTP version of SQL Server 2016 was released and we got a chance to get a glimpse of the new features coming in. Then after every month or two Microsoft kept rolling out the CTP versions with more new features, enhancements, and bug fixes on the previous CTP builds.
–> Download SQL Server:
To download SQL Server 2016 you can Register and Download the SQL Server 2016 Full or free Evaluation version (180 days) here.
Or you can Direct download the DVD ISO file image (~2.1 GB) SQLServer2016-x64-ENU.iso
–> Free Developer Version:
Microsoft on March 2016 announced that going forward the Developer version of SQL Server any release will be free for Developers and Learning purpose. Register and Download the Developer version.
Or you can Directly download the DVD ISO image (~2.1 GB): en_sql_server_2016_developer_x64_dvd_8777069.iso
This Developer version is meant for development and testing only, and not for production environments or for use with production data. For more info please check my previous blog post.
–> One big Update on SSMS:
From now onward SSMS i.e. SQL Server Management Studio will not be part of SQL Server installation. This has to be installed separately via the SSMS download page, or from the Installation Center which will download SSMS from online.
As per Microsoft, this is basically to support the move to make a universal version of SSMS for both SQL Server on-Prem & Azure SQL Database, that will ship every month or so.
Check my previous post on installing SSMS separately.
–> Check new features in SQL Server 2016: https://sqlwithmanoj.com/sql-server-2016-articles/
4. Dynamic Data Masking, Link
5. Stretch Database, Link
6. Query Store, Link
7. Live Query Statistics, Link
8. Enhanced In-Memory OLTP, Link
9. Enhanced ColumnStore Indexes, Link
10. Truncate Table partition
11. Other features like, IF EXIST with DROP stmt, STRING_SPLIT fn, COMPRESS & DECOMPRESS fn, etc.
Microsoft on 31st March 2016 announced the free availability of Developer Edition of SQL Server, currently 2014. The Developer Edition is meant for development and testing only, and not for production environments or for use with production data.
With SQL Server 2014 Developer edition developers can build any kind of application on top of SQL Server. It includes all the functionality of Enterprise edition, but is licensed for use as a development and test system, not as a production server.
So, with this edition you are getting the Database Engine as well as DW/BI capabilities ( i.e. SSIS /AS /RS) for free 🙂
This is a very good news for Developers, as till now the SQL Server Express edition used to be the free database for entry-level development, which has lot of limitations like:
1. Only Database Engine, no DW/BI suit (absence of SSIS/RS/AS).
2. Max size of a DataBase is set to 10GB (but you can create multiple databases)
3. No SQL Agent
4. Single CPU utilization
5. Max 1 GB RAM allocation
6. Max 16 number of instances per server
Hence this is a very good deal that you can now get full featured suit of SQL Server with Database Engine as well as with all DW/BI capabilities for free to play, develop and learn with.
SQL Server Express version is a free SQL Server database system that you can Download, Distribute and use for Development, Training & Study purpose. This Express version is targeted for Small scale Applications/Companies, Students, etc.
This Express version provides almost all major features of the full, paid version of the SQL Server, but is limited to the Database Engine itself.
–> Here are some of its Limitations:
1. Max size of a DataBase is set to 10 GB (but you can create multiple databases)
2. No SQL Agent
3. Single CPU utilization (can have multi cores)
4. Max 1 GB RAM allocation
5. Max 16 number of instances per server
6. MS BI features are not available for designing DW/BI workloads (SSIS/AS/RS)
You can also directly download the Express bits from here:
How to download SQL Server?
What are the available options and versions?
Isn’t there any Free version to play with, learn and practice SQL?
I get emails regarding these question many a times from my readers, sometime from my friends and old colleagues too. So I thought to collate all this information into this single post so that people won’t have to search here and there on internet and finally end up nowhere or to any junk sites.
Currently the latest version of SQL Server from Microsoft is SQL Server 2014 released last year on 1st April 2014, and its first Service Pack (SP1) was released just few days back (15th May 2015). You have following options to choose from:
1. SQL Server Full version contains the Database Engine with whole BI suit and is a paid version and you have to buy license key from your MSDN Subscriptions. It is also available for free download for evaluation of 180 days, but post that you have to buy a license key.
2. SQL Server Express is a Free version that can be used by anyone, like students, small setups and companies, etc. It is a lite version which contains the Database Engine with some limitations like: max size of a DataBase is set to 10GB (but you can create multiple databases), no SQL Agent, single CPU utilization & max 1 GB RAM allocation. I think this is a good package and configuration you are getting it for free. And a best option for students and for beginners in SQL if they want to learn and practice SQL Querying without investing anything.
–> Check this video on how to download SQL Server with above two options:
You can also directly download the Express bits from here:
… with the Advanced Services option you get extra tools like Fulltext Search and Reporting Services.
After downloading the bits from Microsoft site you are now ready to install SQL Server.
–> Check this video on how to install SQL Server (its very easy, but just in case):
–> After install completes you can download the sample database AdventureWorks2014 for SQL Server 2014 from CodePlex and restore the Backup (.BAK) file to SQL Server, click on the image below: