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. https://www.youtube.com/watch?v=UyqNY5JyE9k or the 2016 version https://www.youtube.com/watch?v=GYzUHNV-hVI

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 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

image

Give the VM a name

image

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

image

image

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

image

image

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

image

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.

sql

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.

;UIMODE="Normal"

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.

 

 image

# 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.

image

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

 

image

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

image

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"

 

image

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.

image

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

https://robertsmit.wordpress.com

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

image 

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

d:\setup.exe /qs /CONFIGURATIONFILE="C:\sqlsetup\step1SQL1601.ini" /AGTSVCPASSWORD="Password01" /SQLSVCPASSWORD="Password01" /ISSVCPASSWORD="Password01" /RSSVCPASSWORD="Password01" /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS
d:\setup.exe /qs /CONFIGURATIONFILE="C:\sqlsetup\step2SQL1601.ini" /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS

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.

;UIMODE="Normal"

https://robertsmit.wordpress.com

image

Some progress screens of the installation

image

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.

d:\setup.exe /qs /CONFIGURATIONFILE="C:\sqlsetup\step3sql1601add.ini" /AGTSVCPASSWORD="Password01" /ASSVCPASSWORD="Password01" /SQLSVCPASSWORD="Password01" /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS

https://robertsmit.wordpress.com

 

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 https://robertsmit.wordpress.com

Linkedin Profile Http://nl.linkedin.com/in/robertsmit

Google Me : https://www.google.nl

Bing Me : http://tinyurl.com/j6ny39w

LMGTFY : http://lmgtfy.com/?q=robert+smit+mvp+blog

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

Tagged with

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

  • Twitter

  • %d bloggers like this: