Archive for the ‘SQL Server 2016’ Tag

Deploy SQL Server 2016 with Windows Server 2016 Cluster Better Together #SQL #MSIgnite   Leave a comment

Now that Windows Server 2016 and System Center 2016 are GA this is for most a Go Live signal to use them in Production. As I tested the 2016 product line for sometime now I must say it is Awesome. In this demo I use a two node Cluster and two named SQL instances. I use SCVMM 2016 and I did not create a one click deploy image. It is based on several steps that you can combine your self as needed.

Frequently I got the Question can you show me all the screenshots on how to build a cluster or how to build a sql cluster well With Windows 2012R2 I did create a youtube movie on how to create a SQL cluster. or the 2016 version

SQL Server 2016 comes with several features and tools to support cross-platform analytics. Polybase allows you to run queries on external data in Hadoop or Azure blob storage. It can push computation to Hadoop where appropriate, so that your analytical application can join and integrate data from big data stores with the data in the relational store. Microsoft R Services, which is integrated with SQL Server also runs on multiple Hadoop distributions and is also integrated with Azure HDInsight + Spark, enabling both choice and standardization in developing analytics code. And finally, R Tools for Visual Studio allows the ease of use of the modern Visual Studio IDE for developing analytical code in R.

In the next blog post I will go in depth in more SQL features. but first the deployment.

For the First Deployment I use SCVMM 2016 I use a VM template and not the build-in Cluster Features.


image image

Other options for VM deployment would be the build-in Cluster deployment but this will add the file server or Hyper-v this is not needed for a SQL cluster. Or use a WDS PXE server to deploy fresh installed VM’s.

In this case I’ll use a VM template and the Script output in Powershell


Give the VM a name


As you can see in the right top there is a script option I’ll use this to deploy the other node.

The Script is pasted in Powershell ISE in Admin mode



there are two things you need to change if you want to use the script directly.

-JobGroup caa654ea-e43b-49ff-81b9-aebb4c0f1000

-Name "mvpsql1602"

Change both in the script and Run in Edit there is an option to replace all



Now that both nodes are deployed We can check the prereqs for the SQL installation.

As I don’t use the sysprep method, I prefer a clean installation and this is more flexible and just as fast. average 10 minutes install time.

SQL Server 2016 unattended preparation

For Clustering SQL Server I prefer the advanced Cluster Preparation & Cluster Completion


keep in mind you need to create 3 ini Files

  1. Ini file for Advanced Cluster Preparation
  2. Ini file for Advanced Cluster Completion
  3. Ini file for Add node to a SQL failover Cluster

This seems a lot of work but after this your SQL installation was never so easy.


Just before installation there is a quick hint on where the configuration is stored, when you have already a clustered SQL server you can reuse the SQL ini files. As I use the /QS parameter in the setup I need to disable the uimode in the SQL ini files

setup.exe /qs

; Parameter that controls the user interface behavior. Valid values are Normal for the full UI,AutoAdvance for a simplied UI, and EnableUIOnServerCore for bypassing Server Core setup GUI block.


Now that we have the INI files all 3 I rename the files and give them the named instance. For deployment I use a Bogus Account but already added the real account to the security groups So I only need to change the SQL services account when the deployment is ready.



# three steps 1 & 2 and add node to the SQL cluster and this for two instances.

Cluster Creation

the Cluster Creation is real easy in just a few lines of powershell you can add disk , install the options ,test and create the cluster.


Get-WindowsFeature Failover-Clustering
install-WindowsFeature "Failover-Clustering","RSAT-Clustering" -IncludeAllSubFeature -ComputerName mvpsql1601.mvp.local
install-WindowsFeature "Failover-Clustering","RSAT-Clustering" -IncludeAllSubFeature -ComputerName mvpsql1602.mvp.local



#Create cluster validation report
Test-Cluster -Node mvpsql1601,mvpsql1602 -verbose
New-Cluster -Name SQLCL1601 -Node mvpsql1601.mvp.local,mvpsql1602.mvp.local -NoStorage -StaticAddress ""


Get-Cluster |Add-ClusterSharedVolume -Name "Cluster Disk 1"
Get-Cluster |Add-ClusterSharedVolume -Name "Cluster Disk 2"
Get-Cluster |Add-ClusterSharedVolume -Name "Cluster Disk 3"
Get-Cluster |Add-ClusterSharedVolume -Name "Cluster Disk 4"



Cluster Cloud Witness

When using the Cloud Witness option you need an Azure account to create a Storage account

image image

When creating this account keep in mind that you must use the Locally Redundant storage replication option.


When the storage account is created you can get the keys to access the storage. For unattended installs you need to do this before deployment

Running this script “ in admin mode” will create the cloud witness in the cluster


Now that the Cluster is ready we can stat the SQL installation

As I use a Ini file and a batch file to install the SQL 2016 server normally when deploying a SQL server you don’t want to see anything and want just a message when things are done. But for this demo I use the show UI/progress


NOTE: When installing through the command prompt, SQL Server supports full quiet mode by using the /Q parameter, or Quiet Simple mode by using the /QS parameter. The /QS switch only shows progress, does not accept any input, and displays no error messages if encountered. The /QS parameter is only supported when /Action=install is specified.

The license terms are displayed for review and acceptance in the Setup user interface. Unattended installations (using the /Q or /QS parameters) must include the /IACCEPTSQLSERVERLICENSETERMS parameter.

The /QS option does not work with the UIMODE="Normal" in the ini file so quote them out.

; Parameter that controls the user interface behavior. Valid values are Normal for the full UI,AutoAdvance for a simplied UI, and EnableUIOnServerCore for bypassing Server Core setup GUI block.



Some progress screens of the installation


Adding the extra node again I use a installation service account that I later change in a final production account therefor I do not need to store the service account in my installation scripts.



Now you have a perfect running SQL 2016 server with all the different components you can combine them in any form to deploy your server. It all depends on your need en time to deploy a server. I created also a quick demo movie to see the deployment.


Deploy SQL 2016 Server named instance Clustered


Download Windows Server 2016

Follow Me on Twitter @ClusterMVP

Follow My blog

Linkedin Profile Http://

Google Me :

Bing Me :


Posted September 27, 2016 by Robert Smit [MVP] in SQL

Tagged with

  • Twitter

  • RSS Azure and Microsoft Windows Server Blog

  • %d bloggers like this: