If you want to get a head start on the competition, this guide will show you what you need to know to create enterprise-class database applications with the new software.
With the release of SQL Server 2016, Microsoft has added a ton of new features to make it easier than ever to create enterprise-grade applications. These features can help in organising (structured, semi-structure or even unstructured) and transforming data from raw into actionable knowledge.
With that in mind, let’s start by spinning up a virtual machine running SQL Server 2016. This provides an isolated environment that will allow us to get to grips with how SQL Server 2016 works. It is also quicker and easier than running it on local hardware.
Creating a new SQL Server 2016 Enterprise Virtual Machine
You can create a new virtual machine running SQL Server on Azure. For more details on how to do that, click on this link. Note that SQL VM images include the licensing costs for SQL Server into the per-minute pricing of the VM you create. As we are getting started on SQL Server 2016, we should use the SQL Server Developer, which is free for development/testing (not production) or SQL Express and free for lightweight workloads (less than 1GB memory, less than 10 GB storage).
You can then configure basic settings (i.e. unique virtual machine name, a username for the local administrator account on the VM and provide a strong password). You will also need to type a name for a new resource group in the Resource group box. A resource group is a collection of related resources in Azure. Then you can choose a location for the deployment and click on OK.
The purpose of the virtual machine is to experiment with features, but not assess performance characteristics. Once this virtual machine is created, you can then connect to it through a RDP session or from your local SQL Server Management Studio client.
Uploading sample databases
When getting started with SQL Server 2016, you can use the AdventureWorks sample databases. These have new features already implemented, such as Always Encrypted, Row Level Security, Dynamic Data Masking, and so on. These sample databases can be found here. There are two relational databases and a zip file of samples to be downloaded.
You can then use the SQL Server Management Studio client to restore these backup files to the SQL Server instance running in the virtual machine. The SQL Server Management Studio can help in creating databases, tables, functions, views, stored procedures and other database objects using the GUI or with T-SQL code.
There is also a new Wide World Importers Inc. samples to upload and experiment with. These can be found here.
Working with sample databases
The AdventureWorks samples contain snippets of the following: Advanced Analytics (R Services); Always Encrypted; Data Masking; In-Memory Analytics; In-Memory OLTP; JSON; Polybase; Query Store; Row-Level Security; Stretch DB; and Temporal. There is a readme file that shows you the location of documents that detail how each new feature works and how to use them. These documents and the samples are a fantastic way to get to grips with the new features of SQL Server 2016.
These databases can be useful in testing new functionality. These include: Query Store, which is used to keep track of query performance; Temporal tables to keep track of the history of reference data; JSON to enable AJAX calls to some of the key tables; and In-Memory OLTP to optimise performance of table-valued parameters (TVPs) and consumption of sensor data, to name but a few.
Once you have read through the documents and tried out the features, remember to halt the virtual machine you are working on in order to prevent incurring additional charges.
As a global cloud and software expert, Crayon works with many of the world’s leading organisations to optimise their investment in complex technology. For example, after a 15-year absence, we’re helping Burger King re-establish itself in France by providing the firm with a technology optimisation strategy to support the rapid roll out of over 600 restaurants across the country. To read more about this or other Crayon projects click here or call one of our experts today.