Getting started with SQL Server 2016

Getting started with SQL Server 2016

by -
0 118

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.

Getting started with SQL Server 2016

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.  

I am the Group Chief Marketing Officer at Crayon. My team are focused on driving enhanced lead generation campaigns and nurturing for our sales organisations across multiple geographies though the utilisation and coordination of all online and offline communication channels. We are driving increased brand awareness in the business's core competency areas of Software Asset Management (SAM), cloud and volume licensing solutions and associated consultancy services. I have over 20 years of senior business leadership experience within direct marketing/direct sales and mass distribution businesses, in both the B2B and B2C markets serving on the boards of both private and public multinational corporations.