Step by Step SQL Server 2019 FCI Cluster on Storage Spaces Direct (S2D) Windows Server 2019 #S2D #SQL #SQLserver2019 #BigData #WinServ #WSSD

This blog is about creating a new SQL cluster with a Failover instance based on a storage space direct configuration. There are a lot off opinions on do’s and don’t for on prem and in Azure with S2D. This is not the blog post on this discussion.  This is just a post on SQL 2019 on Windows server 2019 storage space direct because I can!

And in a later blog post we are extending this to Azure. Still it always depends on running a Full SQL server or use a managed instance in Azure. But for now the new SQL server 2019 is the basic of our cluster running on a Windows Server 2019 cluster. As this time the installation will be GUI based as I got often the question on the screens. normally I run some PowerShell or a command line script that will do the job in several minutes

What are the new features in SQL Server 2019, the list below is just a short list for a full updated list go to the link below.

OR if you want to run a managed SQL server in Azure : https://azure.microsoft.com/en-us/pricing/details/sql-database/managed/

 

The Database Engine is the core service for storing, processing, and securing data. The Database Engine provides controlled access and rapid transaction processing to meet the requirements of the most demanding data consuming applications within your enterprise. The Database Engine also provides rich support for sustaining high availability.

 

 

https://docs.microsoft.com/en-us/sql/sql-server/sql-server-technical-documentation?view=sql-server-ver15

What’s new in SQL Server 2019

• Installation of SQL Server is supported on x64 processors only. It is no longer supported on x86 processors.
• SysPrep is supported for all installations of SQL Server. SysPrep now supports failover cluster installations
• Always On Availability Groups – secondary replica connection redirection
• SQL Server Machine Learning Services failover clusters
• the operating system requirements for the principal editions of SQL Server
• SQL Server Management Studio (SSMS) 18.0 (preview)
• Azure Data Studio
• Azure Data Studio

Always On Availability Groups – more synchronous replicas (CTP 2.0)

• Up to five synchronous replicas: SQL Server 2019 preview increases the maximum number of synchronous replicas to 5, up from 3 in SQL Server 2017 (14.x) . You can configure this group of 5 replicas to have automatic failover within the group. There is 1 primary replica, plus 4 synchronous secondary replicas.

• Secondary-to-primary replica connection redirection: Allows client application connections to be directed to the primary replica regardless of the target server specified in the connection string. This capability allows connection redirection without a listener. Use secondary-to-primary replica connection redirection in the following cases:

  • The cluster technology does not offer a listener capability.
  • A multi subnet configuration where redirection becomes complex.
  • Read scale-out or disaster recovery scenarios where cluster type is NONE.

SQL Server Enterprise /SQL Server Standard :

1. Windows Server 2019
2. Windows Server 2016
3. Windows Server 2012 R2
4. Windows Server 2012

The supported storage types for data files are:

• Local Disk

• Shared Storage

• Storage Spaces Direct (S2D)

• SMB File Share

As the SQL server 2019 is still in preview I download the evaluation version.  Get your SQL server here

 

Pick the version you want, in our case I select the download media.

Pick the ISO format do download this.

The SQL installation is done on my cluster. described in the blog post below

step by step Windows Server 2019 File Server clustering With powershell or GUI

https://robertsmit.wordpress.com/2018/11/29/step-by-step-windows-server-2019-file-server-clustering-with-powershell-or-gui-cluster-ha-azure-windowsadmincenter-windowsserver2019/

In this Windows server 2019 cluster I created a S2D formatted with ReFS. There is plenty of info to find on my blog how to create the S2D in the cluster.

https://robertsmit.wordpress.com/2016/01/05/using-windows-storage-spaces-direct-with-hyper-converged-in-microsoft-azure-with-windows-server-2016/

https://robertsmit.wordpress.com/2017/11/09/azure-storage-spaces-direct-s2d-standard-storage-vs-premium-storage/

We have a Windows server 2019 Cluster in place with the Storagespace direct configuration.

The specific cluster configuration is debatable, in a typical SQL configuration you would have multiple disks.

Our Cluster with storage spaces. In storage spaces all disks come from the same disk pool if you create things default. then multiple disk has a different purpose. As you want to manage the SQL different than others.

Our SQL Installation is done this time by the GUI, show you the screens and options. you can always use the one liner to install the SQL server unattended.

 

And no I do not use the normal cluster installation but I use the advanced Cluster prep and completion. these two steps gives me a bit more freedom and flexibility it the installation fails.

The choice is developer but can also be the one with a product key.

Just do the updates direct as it is a new version and on a new OS, just to make sure that I run the latest bits.

I got a warning on the Firewall as I turned this off during the installation. This is configured by a GPO when I place the Cluster in the Right AD OU

Selecting the features and the installation folders.

Do I go for a default instance or a named instance. I always go for a named instance. Its easy and better management. In the old days some applications just want to run on a default instance.

My service account that has JEA Just enough Access

C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log\20181217_034408\ConfigurationFile.ini

If you want to use a unattended install you can use this ini file to get the same or adjusted values during the install

Ini file content.

Now that the first step is completed we can do the completion step.

Now the second step is needed to finish the SQL 2019 installation on the first node

But running the wizard if failed on me, as the error said the Cluster is not verified!

I just need to run the Cluster validation.

Now with the Passed Cluster validation we try again.

Now this looks good lets stat the installation of the SQL 2019

With the Named instance and usage of an Alias, I can easy change this or move the SQL to another cluster.

The installation will show me what cluster groups I can use and which are used at the moment.

Selecting the right disks for the installation remember These are the Storage space direct disks that are created with the cluster installation. See my other blog post about creating S2D on Windows Server 2019

A fixed IP is needed unless you use the DHCP checkbox.

Some applications needs special Collation, when changing this it is an Instance setting and can’t be changed again.

Normally you will place the SQL admins or some groups. this is a demo cluster so admin access only

Select the right disks and change this in the location fields.

Selecting multiple files for the Temp DB and the DB location

Also in this step we get a Ini file as output this can be reused if needed.

 

Now that the installation on one node is ready you can see the SQL resources in the cluster. As I did only the install on one node we need to do the Add node installation to get the installation done for a full FCI.

The Step to add an extra cluster node with the SQL installation is an easy step.

Follow the Wizard and use the Same account that you used before.

 

As the SQL studio is no longer a default installation you need to download the bits from Microsoft.

SSMS 18.0 Public Preview 6 is now available, and is the latest generation of SQL Server Management Studio that provides support for SQL Server 2019 preview!

SSMS 17.9.1 is the current General Availability (GA) version of SSMS

Download SQL Server Management Studio 17.9.1 
Download SQL Server Management Studio 17.9.1 Upgrade Package (upgrades 17.x to 17.9.1)

Version Information

• Release number: 17.9.1
• Build number: 14.0.17289.0
• Release date: November 21, 2018

As we are using the 2019 We need to have the latest version

Now we have the studio ready and can access the SQL instance.

In the next blogs I’ll show you how to extend the SQL to Azure.

Below is an overview of the SQL disks based on storage spaces direct.

With All these steps I hope you can build your own cluster and play with this. As for production never use a next next Finish installation there is always some custom tweaks needed to get the best performance.

 

Follow Me on Twitter @ClusterMVP

Follow My blog https://robertsmit.wordpress.com

Linkedin Profile Robert Smit MVP Linkedin profile

Google  : Robert Smit MVP profile

Deploy SQL Server 2016 with Windows Server 2016 Cluster Better Together #SQL #MSIgnite

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.

 

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.

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

 

 

# 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 "10.255.255.41"

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

 

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

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"

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.

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

 

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

Flickr Tags: SQL Servers,Windows Server 2016,SQL 2016,SCVMM,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

SQL Server 2014 Create a New #SQL Server Failover #Cluster (Setup) in 5 minutes Source Files

When I started this blog post it was more a can I create a Fully cluster in 5 minutes and with 10 min extra a two node cluster and loaded with a two instance cluster. Well I could.

If I had better hardware SSD/fusionIO or other SMB 3.0 huge etc it would be much faster ( donations are Welcome  ) Joking

I posted the vid on youtube and the blog and it seams it is not as common as I thought. no next next Finish Deployment.

As you already know deployments are time eating preparations.  But once you have it in place it rocks.

So I’ll place an update on the source files remember change the domain/user account server names

Old Source blog :

https://robertsmit.wordpress.com/2013/07/08/sql-server-2014-create-a-new-sql-server-failover-cluster-setup-in-5-minutes-sql2014-windows2012r2-wpc13/

Get the ini files here http://sdrv.ms/12dqaya  ( logon with your Microsoft Passport )

Watch this new video I made  http://youtu.be/UyqNY5JyE9k

 

In the source file there are   Create SQL CSV Clustered instance and join other node to the instance.

 

With the create cluster name IP , bind ISCSI etc and one Extra SQL install with out CSV also in 3 steps.

 

All the Files are there. just as an sample on how to do this.

http://1drv.ms/1jgOXzk

 

Have Fun!

@clusterMVP

https://robertsmit.wordpress.com

AlwaysOn Availability Groups (SQL Server) Connecting To #Azure #part3 #AlwaysOn #winserv #SQL #msteched #mvpbuzz

As we did AlwaysOn FCI we make a step into the AlwaysOn AG. The Configuration options are divided with a lot of options. But the methods are the same. Pardon I did already a post https://robertsmit.wordpress.com/2013/09/12/windows-server-2012-r2-with-sql-server-2014-failover-clustered-instance-step-by-step-alwayson-availabilitygroups-what-can-go-wrong-part-1/

As there are a lot of extra options to extend your SQL server and give your DB the HA feeling. I hope the next post will give you insight in a how to get there. In a follow up post I will explain the Azure and extra options of SQL Server 2014.

AlwaysOn Availability Groups (SQL Server)

The AlwaysOn Availability Groups feature is a high-availability and disaster recovery solution that provides an enterprise level alternative to database mirroring. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of read-write primary databases and one to four sets of corresponding secondary databases.

Deploying AlwaysOn Availability Groups requires a Windows Server Failover Cluster. To be enabled for AlwaysOn Availability Groups, an instance of SQL Server must reside on a Windows Server Failover Cluster node, and the Windows Server Failover Cluster and node must be online. Furthermore, each availability replica of a given availability group must reside on a different node of the same Windows Server Failover Cluster.

AlwaysOn Availability Groups supports cross-cluster migration of availability groups for deployments to a new Windows Server Failover Clustering. A cross-cluster migration moves one availability group or a batch of availability groups to the new, destination WSFC cluster with minimal downtime.

By implementing AlwaysOn SQL Server FCI an availability replica can be hosted by either a standalone instance of SQL Server or an FCI instance. Only one FCI partner can host a replica for a given availability group.

AlwaysOn Availability Groups does not depend on any form of shared storage. However, if you use a SQL Server failover cluster instance (FCI) to host one or more availability replicas, each of those FCIs will require shared storage as per standard SQL Server failover cluster instance installation.

You might need to configure a Windows Server Failover Clustering (WSFC) cluster to include shared disks that are not available on all nodes. For example, consider a WSFC cluster across two data centers with three nodes. Two of the nodes host a SQL Server failover clustering instance (FCI) in the primary data center and have access to the same shared disks. The third node hosts a stand-alone instance of SQL Server in a different data center and does not have access to the shared disks from the primary data center. This WSFC cluster configuration supports the deployment of an availability group if the FCI hosts the primary replica and the stand-alone instance hosts the secondary replica.

 

AlwaysOn Availability Groups

I already had my cluster in place with the SQL AlwaysOn FCI and I have also installed a Second Cluster and a Second Instance on the cluster and already extended the SQL site to Azure and with several standalone server.

Before we start we need to enable the AlwaysOn HA option in on the server this is only done on the running server and is cluster aware. One setting for all the nodes for the same instance!

When we tried to enable the AG it is grayed out. in the SQL management.

Go to the SQL Server Configuration Manager

 

When you are connecting to the passive node on the cluster you will see this, on a standalone install you can only connect to the active node.

Go to the other node and Set this setting. You can only change this setting on the running node that hold the SQL server

 

Now that we enabled the AlwaysOn Availability Groups we can start the wizard in SQL

Pick a name for the AG

I just created a dummy DB just to set this up and I will later Add the real DB.

The dummy DB needs to have a full Backup ! So If your DB is as large as a TB a full backup is needed.

This is a interesting Screen Lots of Options and also Connections To Azure.

First we do a on premise connection and build a Replica to Azure.

and make a choice “ add Replica “  When we select the add replica a SQL login screen will popup.

 Remenber you need to connect to the replica SQL server.

This server is my standalone instance but installed on a failover cluster.

and as you can see I connected My Cluster SQL Server with the CSV installation now to a local SQL instance installed on Cluster Node 4

Some basics you need to know when connecting :

• All the cluster nodes must be in the same Active Directory Domain Services (AD DS) domain.

• Each availability replica in an availability group must reside on a different node of the same Windows Server Failover Clustering (WSFC) cluster.

• The cluster creator must have the following accounts and permissions:

  • Have a domain account in the domain where the cluster will exist.

  • Have local administrator permissions on each cluster node.

  • Have Create Computer objects and Read All Properties permissions in AD DS. For more information, see Failover Cluster Step-by-Step Guide: Configuring Accounts in Active Directory.

 

The Chosen Server is selected and added to secondary. In a cluster there is no automatically failover!

Readable secondary: No
In the secondary role, this availability replica will not allow any connections. I’ll use this pure as a backup and no changes will be made in the backup location. If the cluster is failing I have more problems than a not working Application.

All the options can be set but If you have multiple instances (AlwaysOn FCI ) and installed a local standalone Instance You may need to change the Endpoint Port! the default is 5022. I changed the port to 5023 just to make sure that there is no problem on my server.

Changing the port is easy “ SELECT * FROM sys.tcp_endpoints “  will show you the ports.

With “ ALTER ENDPOINT [hadr_endpoint] AS TCP (listener_port = 5023) “ you change the port to a better one.

Normally If you run this wizard and doing this steps you are fine, but in my demo site I had already a connection to Azure and therefor my listener want not only a local IP but also an Azure IP as described in the error message.

But this error is not saying he you need to do this again no simple add an IP address to your listener  You can Add this by hand or create listener in SQL

 

 

As you could see I needed to add a second IP for my listener that is I already setup a failover to azure.

In the fist step we could choose Azure Replica or a replica

  And I dis the Azure Replica and If you are not already connected and added the thumbprint to your SQL server then you need to do this.

Just click Download and the Azure Login will popup and you need to login with the Azure Admin account that can create the Azure VM

 

  When check the down the Azure login screen will pop up

 

a quick connection screen will popup and does fill in your subscriptions. If you have multiple just pick the right one.

So after connecting and downloading you will have the following. Reminder there is only creating NEW VM available ! If you want to use an existing VM then use the add replica just as in a normal situation.

The bad thing is here you can not pick a SQL server that already is build. But in the Screenshots you will see this is much easier. But it would be nice to tweak this a bit. It Would be handy if you could also pick an existing VM.

After filling in my name and version Size We can go to the next step. keep in mind you can always lower the size of the VM but now it is faster and the setup process will be quicker.

As you can see the Azure Replica server is added.

As I connected the Azure SQL with a Azure Gateway to my LAB environment we can share files thru the domain.

The wizard kick in and we have to wait until it is done. I did not create a listener for this, I just want to replicate the data to Azure.

Real Pity that there is no export to script I would like to see the script that created my azure SQL VM

The progress screen an this can take a while. With a quick peek in Azure We can see this.

This is a Critical Point I have done this now several times and sometimes it fails in a time out , and I found out that I used most of the time a small server and then the script will fail with “Error” so a quick tip use the default size and adjust this after the creation.

Checking the VM 4 cores and with an extra disk from 1TB holding my DB

 

My Lessons learned

As you can see there are multiple disks and the Wizard has run successfully.

My source was clustered and the DB is running on a CSV. Witch s a bad choice for running a Replica. The reason is the Replica wizard want to see the same disk and placing the DB files on C is no problem but a CSV volume placed  C:\ClusterStorage\Volume1\MSSQL12.MSSQL001\MSSQL\DATA

and this path is available for every cluster node and therefore also in the Azure cloud. and the “ normal” wizard tells me he the DB is already there. but now this step is skipped.

Second mistake I used a sample DB. there is no way I can add a second DB because of the CSV problem “ Database is already there “ and this is the Source DB I think this will be better in the next version. Or not using CSV with AlwaysOn AG

Now that the wizard is done and a lot of scripting is passed the line to azure. But what is changed and does it work ?

no votes and an extra node

The replica is created and as shown in the dashboard replicated.

Note:

The Following Issue can happen when you use CSV and or you want to create a replica from FCI to FCI. The reason is the disk letter need to be the same on source and destination, as the CSV volume is mounted to every node and therefor the DB is already there and the setup will fail.

 

Right I use CSV but is the CSV replicated to Azure Yes the cluster does this! But there is no disk mounted in azure and all the files will be placed on the c drive! and the replica can not be created on the location because the source DB is there. If you create the replica by hand you can do this but not by default with the wizard.  just a reminder when you playing with this.

There are some options when you enable AlwaysOn the easiest way is having standalone SQL server running on a cluster node. More advanced is using AlwaysOn FCI. But all this can be done just test everything before you go in production . So that you know how your configuration is working.

And just because you can will not say this is your best solution or design. There are many options and will grow are products evolve.

 

SQL Server AlwaysOn Availability Group concepts

---

A SQL Server Availability Group enables you to specify a set of databases that you want to fail over together as a single entity. When an availability group fails over to a target instance or target server, all the databases in the group fail over also. Because SQL Server 2012 can host multiple availability groups on a single server, you can configure AlwaysOn to fail over to SQL Server instances on different servers. This reduces the need to have idle high performance standby servers to handle the full load of the primary server, which is one of the many benefits of using availability groups.

An availability group consists of the following components:

• Replicas, which are a discrete set of user databases called availability databases that fail over together as a single unit. Every availability group supports one primary replica and up to four secondary replicas.

• A specific instance of SQL Server to host each replica and to maintain a local copy of each database that belongs to the availability group.

Replicas and failover

The primary replica makes the availability databases available for read-write connections from clients and sends transaction log records for each primary database to every secondary replica. Each secondary replica applies transaction log records to its secondary databases.

All replicas can run under asynchronous-commit mode, or up to three of them can run under synchronous-commit mode. For more information about synchronous and asynchronous commit mode, see Availability Modes (AlwaysOn Availability Groups).

Note:

Database issues, such as a database becoming suspect due to a loss of a data file, deletion of a database, or corruption of a transaction log do not cause failovers.

Read the following articles to learn required and important concepts about SQL Server AlwaysOn technology:

• For details about the benefits of AlwaysOn Availability Groups and an overview of AlwaysOn Availability Groups terminology, see AlwaysOn Availability Groups (SQL Server).

• For detailed information about prerequisites, see Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server). This article contains the following information:

  • Windows Server system requirements and recommendations

  • SQL Server instance prerequisites and restrictions

  • Prerequisites and restrictions for using a SQL Server Failover Cluster Instance (FCI) to host an availability replica

• Availability group prerequisites and restrictions

• Availability database prerequisites and restrictions

AlwaysOn Failover Cluster Instances SQL Server 2014 in #part2 #azure #winserv #SQL #msteched

As described in the other post AlwaysOn Options the First AlwaysOn option is the FCI version.

AlwaysOn Failover Cluster Instances (SQL Server)

Failover cluster instance (FCI)  is in short the old active/passive configuration – Protection level SQL Server / instance

As part of the SQL Server AlwaysOn offering, AlwaysOn Failover Cluster Instances leverages Windows Server Failover Clustering functionality to provide local high availability through redundancy at the server-instance level—a failover cluster instance (FCI).

An FCI is a single instance of SQL Server that is installed across Windows Server Failover Clustering nodes and, possibly, across multiple subnets. On the network, an FCI appears to be an instance of SQL Server running on a single computer, but the FCI provides failover from one Windows Server Failover Clustering node to another if the current node becomes unavailable.

Building the Basic Cluster

The Basic is a Cluster based on Hyper-v with the shared VHDX option. So starting with a PowerShell script that Creates a Two node Cluster and with a file share witness. You can easily change the PowerShell script and use this in your own environment.  ( Make sure when you grab the script the “ are correct. )

#Install cluster options
Get-WindowsFeature Failover-Clustering
install-WindowsFeature “Failover-Clustering”,”RSAT-Clustering” -IncludeAllSubFeature
#Create cluster validation report
Test-Cluster -Node mvpsql141,mvpsql142
#Create cluster
New-Cluster -Name MVPSQL1401 -Node mvpsql141,mvpsql142 -NoStorage -StaticAddress “10.255.255.71″
#Add disks to the cluster
Get-ClusterAvailableDisk -Cluster MVPSQL1401
Get-ClusterAvailableDisk -Cluster MVPSQL1401 |Add-ClusterDisk
#Add disk to CSV
Add-ClusterSharedVolume -Cluster MVPSQL1401 -Name “Cluster Disk 1″
#Set Cluster Quorum
Set-ClusterQuorum -Cluster MVPSQL1401 -FileShareWitness \\mvpdc01\cluster
#set network configuration
(Get-ClusterNetwork “Cluster Network 1”). Role =3

(Get-ClusterNetwork “Cluster Network 2”). Role =1

 

Remember this is a Lab environment

Now that the Cluster is up and running we can start with the next steps.

This Cluster will be the basic of all SQL installations. Speaking off SQL Installations I use only 2014 SQL servers and guess what it has new options that I will show you later.

AlwaysOn Failover Cluster Instances (SQL Server)

Well now that the cluster is ready we will deploy SQL 2014 ENT to the cluster, everybody can follow a wizard So we do as usual a Command line install based on ini files. This works the best and the result is always the same. But you can use also VMM or SCCM to do this.

First I use My SQL Ini files, If you don’t have the ini files no problem You can easily create them during the SQL setup. But if you install only one SQL server there is no point of doing this. Only just because you can

And If you want to install this by Gui Fine just remember, I always install in advanced mode If one step is failing I can rerun the second step without the long wait of installing the whole server. Setups are always failing at the end.

When running these steps at the end there is a location where the ini file is stored. copy the ini and put it on a save spot.

In my case I use c:\SQL

There is only one thing that you need to change UIMODE="Normal" you need to turn it off by placing “ ; “ or delete the line we do not do a UI setup

And if you don’t like the interface is showing what the setup is doing then turn this off also. I like to watch so that my boss is thinking I work hard.

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

; Setup will not display any user interface.

QUIET="False"

When the ini files are in place remember you need 3 ini files

Step 1 : SQL server Advanced Cluster Preparation

Step 2 : SQL server Advanced Cluster Completion

Step 2 : SQL server Join Cluster Node

I mounted the ISO to the Cluster nodes and run this batch file on the first node. As you can see the password is in the file and unencrypted. You can be prompted for this but as this is a how to it is not important right now.

d:\setup.exe /qs /CONFIGURATIONFILE="c:\sql\sql14CSVConfigstep1.ini" /AGTSVCPASSWORD="Password01" /ASSVCPASSWORD="Password01" /SQLSVCPASSWORD="Password01" /ISSVCPASSWORD="Password01" /RSSVCPASSWORD="Password01" /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS
d:\setup.exe /qs /CONFIGURATIONFILE="c:\sql\sql14CSVConfigstep2.ini" /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS
After this is done you will have a One node SQL instance.

 

 

Add A second node To the SQL FCI

The Second step will be running the step3 script Adding the Second node to the Cluster.

And Again I do this by Command line But Did you know there is an option in the setup UI that you can use INI files during the setup ?

When using this the setup is not unattended but all the values are used in the ini file. So it is a NEXT NEXT FINISH install this could be handy if you want to change something.

  

Or run the Command line below the join the node to the SQL Instance.

d:\setup.exe /qs /CONFIGURATIONFILE="C:\sql\step3addnode ConfigurationFile.ini" /AGTSVCPASSWORD="Password01" /ASSVCPASSWORD="Password01" /SQLSVCPASSWORD="Password01" /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS

After these steps the SQL AlwaysOn Failover Cluster Instances is ready.

But there are no disks yes that is right in SQL 2014 you can use Cluster shared Volumes (CSV) this is a new feature of SQL server 2014

If you want to see the installation Steps I created a movie with about the same steps. the whole Process creating and install SQL in just 15 Minutes. not fully untended just for showing you what is possible.

https://robertsmit.wordpress.com/2013/09/30/windows-server-2012-r2-with-sql-server-2014-cluster-installation-in-less-than-15-minutes-winserv-rocks-movie/

Next part will be AlwaysOn Availability Groups (SQL Server) With a connection to Azure

Windows 2012R2 Failover Cluster With SQL Server 2014 AlwaysOn Options #part1 #cloud #azure #winserv #SQL #msteched

With the new version of SQL server 2014 there are a lot of options possible for DR or some extra Configuration options.

In the Old days there was only a failover option in SQL active/passive or if you had multiple instances you could run a instance on every node this could be seen as active/active. en yes mirroring was also an option.

But now the naming is different and there are a lot more configuration options. Remember “ my SQL is running on bare metal much faster “ eh this is not that long ago.  Configurations with a Scale-out file server is not yet common but more and more configurations are using it. Now that SQL Server 2014 can store on CSV. In the following 3 blog post I will show you how to create all this bottom up. easy playground. A lot of terms will pass along like FCI WSFC Azure CSV, FTW SQL LOL

But the Two main options on SQL for clustering are :

AlwaysOn Failover Cluster Instances (SQL Server)

AlwaysOn Availability Groups (SQL Server)

 

AlwaysOn Failover Cluster Instances (SQL Server)

Failover cluster instance (FCI)  is in short the old active/passive configuration – Protection level SQL Server / instance

As part of the SQL Server AlwaysOn offering, AlwaysOn Failover Cluster Instances leverages Windows Server Failover Clustering functionality to provide local high availability through redundancy at the server-instance level—a failover cluster instance (FCI).

An FCI is a single instance of SQL Server that is installed across Windows Server Failover Clustering nodes and, possibly, across multiple subnets. On the network, an FCI appears to be an instance of SQL Server running on a single computer, but the FCI provides failover from one Windows Server Failover Clustering node to another if the current node becomes unavailable.

An FCI can leverage AlwaysOn Availability Groups to provide remote disaster recovery at the database level.

As the “SQL Server (MSSQL001)” is installed on two nodes the instances and the DB are fault tolerant but needs shared storage

This is a AlwaysOn Failover Cluster Instances (SQL Server) FCI solution.

When a SQL Server instance is configured to be an FCI (instead of a standalone instance), the high availability of that SQL Server instance is protected by the presence of redundant nodes in the FCI. Only one of the nodes in the FCI owns the Windows Server Failover Clustering resource group at a time. In case of a failure (hardware failures, operating system failures, application or service failures), or a planned upgrade, the resource group ownership is moved to another Windows Server Failover Clustering node. This process is transparent to the client or application connecting to SQL Server and this minimize the downtime the application or clients experience during a failure.

The following lists some key benefits that SQL Server failover cluster instances provide:

• Protection at the instance level through redundancy

• Automatic failover in the event of a failure (hardware failures, operating system failures, application or service failures)

• Zero reconfiguration of applications and clients during failovers

 

AlwaysOn Availability Groups (SQL Server)

The AlwaysOn Availability Groups feature is a high-availability and disaster recovery solution that provides an enterprise level alternative to database mirroring. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of read-write primary databases and one to four sets of corresponding secondary databases.

Deploying AlwaysOn Availability Groups requires a Windows Server Failover Cluster. To be enabled for AlwaysOn Availability Groups, an instance of SQL Server must reside on a Windows Server Failover Cluster node, and the Windows Server Failover Cluster and node must be online. Furthermore, each availability replica of a given availability group must reside on a different node of the same Windows Server Failover Cluster.

AlwaysOn Availability Groups supports cross-cluster migration of availability groups for deployments to a new Windows Server Failover Clustering. A cross-cluster migration moves one availability group or a batch of availability groups to the new, destination WSFC cluster with minimal downtime.

By implementing AlwaysOn SQL Server FCI an availability replica can be hosted by either a standalone instance of SQL Server or an FCI instance. Only one FCI partner can host a replica for a given availability group.

AlwaysOn Availability Groups does not depend on any form of shared storage. However, if you use a SQL Server failover cluster instance (FCI) to host one or more availability replicas, each of those FCIs will require shared storage as per standard SQL Server failover cluster instance installation.

You might need to configure a Windows Server Failover Clustering (WSFC) cluster to include shared disks that are not available on all nodes. For example, consider a WSFC cluster across two data centers with three nodes. Two of the nodes host a SQL Server failover clustering instance (FCI) in the primary data center and have access to the same shared disks. The third node hosts a stand-alone instance of SQL Server in a different data center and does not have access to the shared disks from the primary data center. This WSFC cluster configuration supports the deployment of an availability group if the FCI hosts the primary replica and the stand-alone instance hosts the secondary replica.

 

The following lists some key benefits that AlwaysOn Availability Groups provide ( depends on your configuration ):

• No shared disk needed

• Only Database protection

• Zero reconfiguration of applications and clients during failovers

 

As the screenshot shows it hold a availability group with a listner. The configuration is only visible in the SQL server manager

This sounds great new options more but how to configure them and how about Azure In the next post I will show you how to create all this.

In the following I created a Cluster connected to azure with a Site to Site VPN. And will show you the HA options this will be in several steps else it would be a long post.

But along the choices there are a lot of options that can be a problem with your configuration or maybe not the best option. And maybe you need a 3th party product the get the job done. Like Datakeeper my fellow Cluster MVP David Bermingham is SteelEye’s Director of Product Management.

In the next part I will start with AlwaysOn Failover Cluster Instances (SQL Server) Followed By AlwaysOn availability group (SQL Server) and Azure Failovers.

Windows Server 2012 R2 with SQL Server 2014 Cluster installation in less than 15 minutes #winserv #Rocks #movie

SQL Server 2014 now Generally Available Build your Cluster #sql

Robert Smit MVP Blog

Just for the fun I recorded a movie on the installation of Windows Server 2012 R2 . I thought just a quick recording and done but as it turns out it took longer than I expected, I made a typo in the script or clicked the wrong script or forgot to clean out my netbios name. then you gona build failsafe items in the scripts and they will be shown as errors because some items are already be set and can’t set twice.

The basic Idea was record a quick movie of an unattended installation of a SQL cluster in a few minutes is this possible yes it cab be done in les than 15 minutes. installation of the OS , Cluster with full Storage validation and a two node SQL cluster based on normal disk and a two node cluster based on CSV.

Eh in 15 minutes two full…

View original post 249 more words

Windows Server 2012 R2 with SQL Server 2014 Failover Clustered Instance #Step-By-Step #AlwaysOn AvailabilityGroups What can go Wrong! Part 1

 

There are a lot of good blog post about how to setup your Availability group, in two blog post I will try to break the basic setup and will show you what you should not do in your production environment. Just because you can does not mean I should do this.

This blog post will also show you most common errors and how to fix them and where to find the errors, but in the end you will have a working two node cluster and one Availability group

First how to setup an Availability group  to make things more complex there are multiple instances, see how they look Naming convention is really important when you do complex configurations. an typo is quickly made!

 

Deploying AlwaysOn Availability Groups requires a Windows Server Failover Clustering (WSFC) cluster. To be enabled for AlwaysOn Availability Groups, an instance of SQL Server must reside on a WSFC node, and the WSFC cluster and node must be online. Furthermore, each availability replica of a given availability group must reside on a different node of the same WSFC cluster. The only exception is that while being migrated to another WSFC cluster, an availability group can temporarily straddle two clusters.

AlwaysOn Availability Groups relies on the Windows Failover Clustering (WSFC) cluster to monitor and manage the current roles of the availability replicas that belong to a given availability group and to determine how a failover event affects the availability replicas. A WSFC resource group is created for every availability group that you create. The WSFC cluster monitors this resource group to evaluate the health of the primary replica.

The quorum for AlwaysOn Availability Groups is based on all nodes in the WSFC cluster regardless of whether a given cluster node hosts any availability replicas. In contrast to database mirroring, there is no witness role in AlwaysOn Availability Groups.

The overall health of a WSFC cluster is determined by the votes of quorum of nodes in the cluster. If the WSFC cluster goes offline because of an unplanned disaster, or due to a persistent hardware or communications failure, manual administrative intervention is required. A Windows Server or WSFC cluster administrator will need to force a quorum and then bring the surviving cluster nodes back online in a non-fault-tolerant configuration.

Primary on an FCI with a replica on a different FCI

 

I have a lot of SQL instances and this all runs on a two node Cluster and not all instances are installed on both nodes to trick the installation and to show you the errors you can expect.

  

Enabling the AlwaysOn and you can see the Difference the new AG Wizard is not grayed out any more .

Starting the Wizard  and on system Databases it wil not work AG will only work on your own DB !

You must make a full backup of your DB before you start ( this is always handy )

Now we can add a new replica Check the Server and as it is a Cluster you can not set it to automatic failover.

 

 

 

We do setup a Data share for the replication

Now that we have completed the wizard we do the validation and go for the finish.

An Error ? checking the location ?? eh what should the DB be on the same location ?? not all my SQL server Cluster are the same and are not using all the drive letters. and as I choose to do this on the same cluster ( not supported ) I can not give the other instance the same drive letter. but hat if I had an other cluster and even then I did not have the same Drive letter. Is there a wizard bypass some where.  Wizards are nice If you have a default installation. If not Plan B.

TITLE: Microsoft SQL Server Management Studio
——————————

Checking for compatibility of the database file location on the secondary replica resulted in an error. (Microsoft.SqlServer.Management.HadrTasks)

——————————
ADDITIONAL INFORMATION:

The following folder locations do not exist on the server instance that hosts secondary replica MVPSQL201402\SQL2:
i:\MSSQL11.SQL001\MSSQL\DATA;
(Microsoft.SqlServer.Management.HadrTasks)

So placing this on a CSV SQL server 2014

Well if drive letters is an issue SQL Server 2014 can store the DB on a CSV so no more drive letters.

And It passed the Validation that is Great.

Great thinking but.. the CSV is connected to all SQL servers So the next error is logical.

Yes the DB is already there.. what now ?

Manually Creating an Availability group for a SQL Server 2014 FCI

This sounds great but where to start ? should I bing It ? Let Me Bing That For You!

Well I Create it with a SQL script ( I’m no SQL master  ) So things can be different.

However you can run this in a SQL CMD but here I do this step by Step.

I have My SQL Availibility group name, My DB name,IP, Servers

 

CREATE AVAILABILITY GROUP SQL001AG04
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR DATABASE AG04 –, …
REPLICA ON — primary:
N’MVPSQL201401\sql001′ WITH (ENDPOINT_URL = N’TCP://MVPSQL201401.mvp.local:5023′,
FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)),
— secondary:
N’MVPSQL201402\SQL2′ WITH (ENDPOINT_URL = N’TCP://MVPSQL201402.mvp.local:5022′,
FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));

ALTER AVAILABILITY GROUP SQL001AG04
ADD LISTENER N’MVPLST01′
(WITH IP ((N’10.255.255.69′, N’255.255.255.0′)), PORT=1433);

BACKUP DATABASE AG04 TO DISK = ‘\\mvpfsw01\SQLAG04\AG04.bak’
WITH INIT, COPY_ONLY, COMPRESSION;

BACKUP LOG AG04 TO DISK = ‘\\mvpfsw01\SQLAG04\AG04.trn’
WITH INIT, COMPRESSION;

 

 

 

 

 

 

 

Now we go to the replica server and run the script below.

—————- Run this on The Replica Server!!!!!!!

 

ALTER AVAILABILITY GROUP SQL001AG04 JOIN;

RESTORE DATABASE AG04 FROM DISK = ‘\\mvpfsw01\SQLAG04\AG04.bak’
WITH REPLACE, NORECOVERY, NOUNLOAD,
MOVE ‘AG04’ TO ‘E:\MSSQL11.SQL001\AG04.mdf’,
MOVE ‘AG04_log’  TO ‘E:\MSSQL11.SQL001\AG04_log.ldf’;

RESTORE LOG AG04 FROM DISK = ‘\\mvpfsw01\SQLAG04\AG04.trn’
WITH NORECOVERY, NOUNLOAD;

ALTER DATABASE AG04 SET HADR AVAILABILITY GROUP = SQL001AG04

 

But as You can see in the screen shot it is not working the secondary server is down.

the following error is showing :  The connection to the primary replica is not active.  The command cannot be processed.

Message
A connection timeout has occurred while attempting to establish a connection to availability replica ‘MVPSQL201401\sql001′ with id [F82BBD94-4F04-4B0A-8B75-28A0899F240C]. Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance.

 

Ok I did turnoff all the Firewall, checked the network set permissions now what.

Think :  I have two cluster nodes both are using SQL in the script what do they have in common.

ENDPOINT_URL = N’TCP://MVPSQL201401.mvp.local:5022’

So changed it from 5022 to 5023 and it work like a charm

In the next post I will explain how to check this and how to change this

 

 

 

 

 

 

 

 

 

SO basically it is better to use the script that the wizard well it depends For now in the demo environment running on two different disks it is better and It would be better if the wizard ask you about drive letters or storage locations.

But manually you have more control about the setup and if something fails you can fixit before you go further.  But also you have to think about a lot of issues

Next will be Part 2

More Errors and more fixes on   SQL Server 2014 Failover Clustered Instance (FCI) with Step-By-Step #AlwaysOn Availability Groups #winserv #FCI

How to: Change the Network Binding order for SQL 2012 Windows Server 8 Failover Cluster (Setup)

During the SQL 2012 Setup there is a warning that my binding order is not correct. It looks ok but it is not. there are a couple of KB items on technet. this is on Windows 8 server

 

Why this is appearing did I make a mistake ?  lets see mm my lan nic is on top. this looks ok so why this error. lets look at the SQL log

 

Ok here it is the SQL setup log file is it wrong ? local area connection* 11 is on top ? what is this ? lets lookit up .

 

NetworkBindingFacet: Looking up network binding order.

(05) 2012-03-18 14:03:04 Slp: NetworkBindingFacet: Network: ‘Local Area Connection* 11’ Device: ‘\Device\{2E9E3899-BB35-4E91-98D4-BF56F4ADE4F6}’ Domain: ” Adapter Id: ‘{2E9E3899-BB35-4E91-98D4-BF56F4ADE4F6}’

(05) 2012-03-18 14:03:04 Slp: NetworkBindingFacet: Network: ‘LAN’ Device: ‘\Device\{734667C2-FCC9-4874-B3E0-EDC3BA72F6AB}’ Domain: ‘mvp.local’ Adapter Id: ‘{734667C2-FCC9-4874-B3E0-EDC3BA72F6AB}’

(05) 2012-03-18 14:03:04 Slp: NetworkBindingFacet: Network: ‘ISCSI’ Device: ‘\Device\{1F016AE2-DD00-4B0F-B80B-57173AF2F36E}’ Domain: ” Adapter Id: ‘{1F016AE2-DD00-4B0F-B80B-57173AF2F36E}’

(05) 2012-03-18 14:03:04 Slp: NetworkBindingFacet: Network: ‘Cluster’ Device: ‘\Device\{BA1F99C0-0D58-44AA-85E0-ADFB1EF2C422}’ Domain: ” Adapter Id: ‘{BA1F99C0-0D58-44AA-85E0-ADFB1EF2C422}’

I know what it is it is the hidden cluster adapter which needed to be moved in the binding order, and that had to be done in the registry.

I opened up regedit and drilled down to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\Tcpip\Linkage and opened up the Bind value:

 

 

\Device\{2E9E3899-BB35-4E91-98D4-BF56F4ADE4F6}

\Device\{734667C2-FCC9-4874-B3E0-EDC3BA72F6AB}

Flip the registry keys and you are ready to go.

This is hard to find eh yes but you can use powershell 

wmic nicconfig get Description,SettingID

More info about this is on MSDN

http://msdn.microsoft.com/en-us/library/ms189910(v=sql.110).aspx

changing-the-network-binding-order-in-windows-server-2008

and it you want to change this by powershell the get-itemproperty is the way.

Get-ItemProperty Registry::HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Linkage\bind\*

After the NIC binding order change the SQL setup marked all as passed.

Samples :

 

Get-Item -Path Registry::HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Linkage\bind\* | Select-Object -ExpandProperty Property

Get-WmiObject -Class Win32_NetworkAdapterConfiguration -Filter "IPenabled = $true" | select Description, IPAddress, index, SettingID

gwmi Win32_NetworkAdapterConfiguration | where-object {$_.IPEnabled -eq "True"} | ft Description, SettingID -auto

How to: Create a New SQL 2012 Server Failover Cluster (Setup) multiple instances on windows server 2012 #ws2012

Now that windows 2012 is in beta we can talk almost about anything and it is time to create my demo SQL 2012 machine. In this case I uses the Windows server 8 cluster from the other blog item to create a SQL 2012 cluster with 3 instances. All of the instances are created on different ways , Instance 3 is a ZTI   #WS2012 #TEE #TEE13 #MVP13

For starters build a 2 node cluster or more nodes if you want and use the SQL 2012 Media. I use the Enterprise version.

Remember that I create the Server Group and my cluster I already added the disk to my cluster mounted the SQL 2012 ISO file to the node. Ready for the Setup.

again I use a lot of pictures in this blog and I do almost everything twice just to show you how you can configure your cluster.

the disk listing in the cluster. eh no we do not use the basic setup. Installation should be fun and not follow the wizard ok we have to use the wizard in this part. We use the advanced cluster preparation setup this will setup your SQL cluster in two parts.

 

 

 

Fill in your SQL product key or use a evaluation , accept the agreement.

  

Mm a warning it looks OK yes it is I create a new blog post on this ignore it for now.

 

Yes I corrected the binding order and we continue the setup.

 

I choose to install all the options This cluster will be used for the System center products.

 

 

I use a basic account but for more security you should use different accounts. And I will also configure my report server by hand later.

 

 

Now that the first step is ready we can do the second step but first I want to create a second instance preparation. as a basic I use the setup .ini file that you can save during the setup.

The second instance I use this ini file to create the instance preparation. Open the ini file and I changed the SQL01 in SQL02. easy

in the setup screen I use the install based on a Ini file setup. this is not an unattended setup. But all the screens are already filled in.  So this is like a practice unattended setup like in SQL03  

… import ini file As you can see the installation of a second instance is the same as installing one instance on a node or installing a third instance but you can install less or more options in the instances. they don’t have to be the same.

 

 

 

 

 

Easy just press next and finish almost unattended. You can also use the ini file of this installation if you want to rebuild this instance during a disaster.

Advanced Cluster Completion

Now that the Instance SQL01 and SQL02 are installed but not jet completed in my cluster we need to do the next step Cluster completion. This step we need to do this also twice , and yes one time I do this in the GUI and save the INI file and One I use the Ini file for the SQL02 setup just to see how it can be done.

 

Here we create the Cluster name and the SQL instance As you can see. And You see also the SQL02 instance. but remember you can only do one instance installation at the time

 

 

We choose the Disks and the IP address for this SQL cluster instance.

I use here only two disks but you can use more disk, DB ,Logs, and Temp DB and Analysis all on different disks. if you want more performance. this is if it runs on multiple SAN disks.

for System center the Collation is most important If you choose the wrong one you are in trouble it is hard to reconfigure this.

If you continue Setup using the default collation (SQL_Latin1_General_CP1_CI_AS), you will not be able to support multiple languages in Service Manager. In the future, if you decide you want to support multiple languages, you will have to re-install SQL Server.

Service Manager doesn’t like the default collation method (SQL_Latin1_General_CP1_CI_AS). If your SCSM SQL server is running with that collation mode you will get a warning when you install SCSM.

Further down in the error the Exhange 2010 MP was referenced with regards to “Microsoft.Exchange.2010.Reports.Dataset.Ten

After doing some investigation discovered that nager is only supported with a collation of “SQL_Latin1_General_CP1_CI_AS” hange 2010 management pack relies on the collation being “SQL_Latin1_General_CP1_CI_AS” to place certain data into the Data Warehouse.

Here is the bad news!!! The only way to resolve is it re-install SQL. So make sure if you are installing Operatons Manager your SQL collation is correct.

This error message is because we installed SQL Server in the default SQL_Latin1_General_CP1_CI_AS collation. This collation is the only one that is supported by SCOM2012 and so this is necessary when we want to install SCOM2012 and want to connect SCSM with SCOM2012.

When installing SCSM 2012 it’s important to use a supported language.

If you use the default collation: SQL_Latin1_General_CP1_CI_AS

multilanguage support will not be available in Service Manager.

Instead you should use a supported collation like eg.: Latin1_General_100_CI_AS

 

More info about this you can find here :

http://technet.microsoft.com/en-us/library/hh495583.aspx

 

If you use system accounts you don’t want to add the domain administrator but in my demo It is ok

 

 

Ready. the one thing that I did not install is a DTC because it is so easy to install on a cluster we do this in just a few steps.  go to the failover cluster manager and add a cluster role

 

 

pick the msdtc and give the role a IP and I always use a own disk for the DTC but you can share this if you want. my advice use a own disk for a more flexible cluster

 

 

as you can see my cluster is ready to use

the second cluster completion I do this with a INI file all the steps are the same as without the ini file only the items are filled in.

..import ini file

 

 

I skiped the other screens

 

Done Now I have two SQL instances and 2 sets of ini files. these file I’m gonna use it for an unattended setup. SQL installation in just 10 minutes

the first step SQL cluster preperation

setup.exe /Q /CONFIGURATIONFILE=”C:\SQLSetup\ConfigurationFile.ini”

/SQLSVCPASSWORD=”<StrongPassword>” /IACCEPTSQLSERVERLICENSETERMS/INDICATEPROGRESS

because I use a account for my SQL server I need to fill in my password.

 

 

 

  this will kick in the cluster completion.

For this unattended setup I created the following file I use the following account for all the services : sql2012 and the password is also sql2012 Remember this is a lap use strong passwords in real life !!!

Step one is the Cluster preperation

d:\setup.exe /q /CONFIGURATIONFILE=”C:\SQLSetup\SQL03-1.ini”

/AGTSVCPASSWORD=”sql2012″

/ASSVCPASSWORD=”sql2012″

/SQLSVCPASSWORD=”sql2012″

/ISSVCPASSWORD=”sql2012″

/RSSVCPASSWORD=”sql2012″ /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS

Step Two is the Cluster Completion

d:\setup.exe /q /CONFIGURATIONFILE=”C:\SQLSetup\SQL03-2.ini”

/AGTSVCPASSWORD=”sql2012″

/ASSVCPASSWORD=”sql2012″

/SQLSVCPASSWORD=”sql2012″

/ISSVCPASSWORD=”sql2012″

/RSSVCPASSWORD=”sql2012″ /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS

 

 

You can do this also in one ini file but if something fails you don’t have to do it all again.

Happy clustering !!

Windows 2008R2 MSTDC Clustering how hard can it be. Linked SQL Servers

The DTC option is a simple feature just turn it on and it works eh yeh sometimes. but in advanced environments you need often some tweaks and how to’s

The following errors are common on misconfiguration on the SQL Cluster / MSDTC source

MSDTC encountered an error (HR=0x80000171)  Or if you are using linked SQL servers If the MSDTC is not working most of them solve the error with code, actually it is a misconfiguration of the MSDTC How to solve this error ?

OLE DB provider "SQLNCLI10" for linked server "SQL instance name" returned message "Cannot start more transactions on this session.".

Unable to start a nested transaction for OLE DB provider "SQLNCLI10" for linked server "SQL instance name". A nested transaction was required because the XACT_ABORT option was set to OFF.

Or error 7391

Well here is a little tip on how to solve this.

 

 

Typical MSDTC cluster resource , But in the resource kit there is a little tool that is named SUBINACL

SubInACL is a command-line tool that enables administrators to obtain security information about files, registry keys, and services, and transfer this information from user to user, from local or global group to group, and from domain to domain. For example, if a user has moved from one domain (DomainA) to another (DomainB), the administrator can replace DomainA\User with DomainB\User in the security information for the user’s files. This gives the user access to the same files from the new domain.
SubInACL enables administrators to do the following:

• Display security information associated with files, registry keys, or services. This information includes owner, group, permission access control list (ACL), discretionary ACL (DACL), and system ACL (SACL).
• Change the owner of an object.
• Replace the security information for one identifier (account, group, well-known security identifier (SID)) with that of another identifier.
• Migrate security information about objects. This is useful if you have reorganized a network’s domains and need to migrate the security information for files from one domain to another.

This update addresses the following issues:

• Fixed bug where subinacl.exe failed to process command line arguments
• Fixed bug where subinacl.exe failed to function correctly with cluster file shares

 

So now that we have the tool let us use this filling in the following command

subinacl /service msdtc /grand=”network services”=qsetil

 

Access denied ! , I am an Admin the CMD is a Admin why the syntax is correct .

Yes all above is ok except the MSDTC part it is a clusterd so you need to use the cluster part.

Ok and now with the Clustered DTC  

Ok done now the error below is gone.

OLE DB provider "SQLNCLI10" for linked server "SQL instance name" returned message "Cannot start more transactions on this session.".

Unable to start a nested transaction for OLE DB provider "SQLNCLI10" for linked server "SQL instance name". A nested transaction was required because the XACT_ABORT option was set to OFF.

Msg 7395, Level 16, State 2, Line 3

But what about the XACT_ABORT option was set to OFF and the error 7391 ?

Yes easy to solve turn it on.

• Start the Distributed Transaction Coordinator (DTC or MSDTC) on all servers that are involved in the distributed transaction.
• Issue this statement before you run your query:

  SET XACT_ABORT ON

  The XACT_ABORT option must be set to ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. This option is not required if the provider supports nested transactions.

• Check whether any of the servers are on a cluster. The DTC on the cluster must have its own IP address. You must verify proper name resolution of the DTC service on each server. The IP address of the DTC must be defined in your name resolution system (such as WINS, DNS or LMHosts). Verify that each server can communicate with the other’s MSDTC by name, not just by IP address. Check in both directions. For example, check from server A to server B’s MSDTC, and then check from server B to server A’s MSDTC. You must resolve all name resolution problems on the network before you run your distributed query.

 

Below is a little example of how to test your transactions just change your servername [SQL instance name].

 

BEGIN TRAN

UPDATE [SQL instance name].test_DTC.dbo.Table1

SET Field1 = ‘MSDTC is a pain if it is not working but now it is working like a charm’

Where Field1 = ‘Second’;

COMMIT TRAN

SET XACT_ABORT OFF

SET XACT_ABORT ON

Select * from [SQL instance name].test_DTC.dbo.Table1