SQL Server 2016 Virtual Labs on Microsoft Azure Virtual Machines

SQL Server 2016 Virtual Labs on Microsoft Azure Virtual Machines and Cloud Services

SQL Server 2016 (RTM) provides a new set of features and enhancements. It is being released on June 1, 2015. For more information, check out the Microsoft SQL Server webpage. You can also read more about how SQL Server 2016 will help you modernize the application platform for your business-critical applications by clicking here.

After you have deployed/installed your own copy of the SQL 2016 server, you might want to start leveraging some of the powerful new features that are available in this release. One way to do that is through virtual labs that run inside an isolated environment provided by Azure Virtual Machines. This blog post shows how to quickly spin up ten SQL 2016 servers on Azure using Power Shell scripts which consist of one master script and several sub-scripts. To see a list of all available SQL 2016 Power Shell Cmdlets, please refer to the following webpage:

All the code samples in this blog post are provided “AS IS” without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The authors make no claims regarding the correctness, accuracy, completeness, or reliability of these code snippets and sample files. Many scripts were written from scratch to show how specific tasks should be performed using Windows Azure Virtual Machines and Microsoft Power Shell cmdlets.

This blog post series is divided into two parts: Part 1: Deploying and configuring 10 SQL 2016 Virtual Machines Part 2: Connecting to SQL Server and exploring the new features and enhancements of SQL Server 2016

You can clone or download a master script from Git Hub. The master script is used as a blueprint for building all individual virtual machines. All the required configuration settings are defined inside it, including those that enable remote desktop connections by using Enable-PS Remoting cmdlets. Some variables need to be changed before running the master script such as: – Resource Group name – Password generation (if you don’t want to use your own password) – typically you’ll want to run this once until you get familiar with the scripts – then uncomment out code in master script and change variable values accordingly

If you pass a value for the parameter ‘ -Verbose’ to the script, additional information will be displayed on the screen as each machine is built.

This blog post was written by Dimitrios Thilikos ([email protected]). To run all scripts you need: – A Microsoft Azure subscription with SQL and web hosting capabilities enabled – Download and install Windows Azure Power Shell cmdlets Note: Most of this functionality is available in the preview version. You can find more details about the Windows Azure Power Shell cmdlet.

You can either use an existing storage account or create a new one when deploying your virtual machines to Azure.

Executing the following cmdlet will create a new storage account:

New-Azure Storage Account -Location ‘West Europe’ -Name ‘sqlvault’ –sku Standard_LRS –Kind “Storage”

Note: As mentioned earlier, most of this functionality is available in the preview version. You can find more details about creating and managing Azure storage accounts here.

Before you proceed with running the scripts, please make sure that your system meets all requirements listed below: * The script requires administrator privileges * For authentication purposes, we’ll need to supply an Active Directory domain service account that has permissions to perform virtual machine creation and related activities through the Windows Azure portal * The virtual machines will be built in Azure using an ARM template, which works with JSON input By default, if the scripts are executed without specifying credentials, they will prompt for a username and password. Make sure to provide values for these parameters before you run any of the sub-scripts listed down below. Executing each script starts off by executing another Power Shell script that sets up some ‘helper’ functions that simplify common repetitive tasks such as creating multiple virtual machines simultaneously.

Master Script Parameters: -Resource Group Name of the Resource Group to deploy all Virtual Machines into –Admin Username User name of user who has administrative rights on SQL 2016 servers -VMGUID Unique identifier of VMs is created. You can manually specify this value or let it be generated randomly. -admin password of the user who has administrative rights on SQL 2016 servers –VM Name Prefix to use for naming the Virtual Machines being created –Include Management Tools If set to ‘Yes’, all management tools will be installed as part of the provisioning process, otherwise only Data-Tier Applications and Reporting Services are installed

Conclusion: 

SQL Server 2016 is a marked improvement compared to its predecessor. Where it matters, SQL 2016 offers solid performance boosts, especially for OLTP workloads. In addition, the new In-Memory OLTP engine provides an impressive list of benefits as well as a solid foundation for the future of Microsoft’s flagship relational database management system.