If you've been thinking there's a ton of new stuff in SQL Server 2016, then you'd be right. It is huge. Here's a brief recap of each of the most valuable features & enhancements that I wrote up over here: http://www.blue-granite.com/sqlserver.
In terms of some hands-on time to play around in an isolated environment, there's an easy way to become acquainted with the new features.
1. Create an Azure Virtual Machine with SQL Server 2016 Image
Create an Azure virtual machine using the template for CTP 3.3 (Update: now RTM as of 6/1/2016).
A few things to be aware of in this virtual machine:
- Integration Services, Analysis Services (MD mode), Reporting Services, Data Quality Services, and Master Data Services are installed and running (though not everything is fully configured, such as SSRS).
- Local NT service accounts are used for each service. If you plan to use this VM in a completely isolated way then it’s ok. Typically, however, we utilize domain-based service accounts.
- IE Enhanced Security Configuration is on. If you intend to do the next step from within the VM, you'll want to turn it off in Server Manager. (My VM will only be for testing purposes, and will only contain sample data, so I'm less concerned about this than with a normal environment.)
- Both data and log are defaulted to F: drive (which is the only data drive), and TempDB is on the C: drive. Since this is just a temporary environment for learning purposes, for it's ok that it doesn't follow best practices regarding use of physical drives.
Overall, the objective with the VM is to test out features, but not evaluate performance aspects.
When the VM has been created, you can connect to it through RDP session (or from your local SSMS client):
2. Restore Sample Databases
There are AdventureWorks sample databases available which already have some new features implemented such as Always Encrypted, Row Level Security, Dynamic Data Masking, etc. You can find these 2016 sample DBs here: https://www.microsoft.com/en-us/download/details.aspx?id=49502 .
There are three items to be downloaded: Two relational DBs, and a zip file of samples.
Within Management Studio, you want to restore these backup files to the SQL Server instance running in the virtual machine via the GUI or T-SQL, whichever you prefer.
As an alternative to AdventureWorks, you can download the new Worldwide Importers Inc samples which are available here: https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v0.1.
3. Work with the Samples
Now we’ve arrived at the fun part. The zip file that came with the AdventureWorks samples contains the following samples:
- Advanced Analytics (R Services)
- Always Encrypted
- Data Masking
- In-Memory Analytics
- In-Memory OLTP
- JSON
- Polybase
- Query Store
- Row-Level Security
- Stretch DB
- Temporal
The ReadMe.txt file refers you to which file is the starting point within each subfolder:
For example, here’s the items in the Advanced Analytics:
As you can see, the samples are a great way to begin getting familiar with the new features very quickly.
One final comment: Don’t forget to stop your VM when you are finished to avoid incurring charges. You can also use Azure Automation to stop one or more VMs at a scheduled time. I wrote a bit about that here: How to Stop an Azure VM on a Schedule.
The VM comes with Analysis Services in multidimensional mode installed. If you want to install tabular mode, or another feature, you can find the setup.exe file to run SQL Server setup located at C:\SQLServer_13.0_Full within the VM.
Finding More Information
BlueGranite - Overview of SQL Server 2016 Features