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  Winking smile ) 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 :


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 image  Create SQL CSV Clustered instance and join other node to the instance.


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



Have Fun!



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!


SQL Server 2014 Failover Clustered Instance (FCI)

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

Windows Server 2012 R2 Failover Cluster with SQL Server 2014 Failover Clustered Instance (FCI) #Step-By-Step #AlwaysOn Availability Groups image

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.

image image imageimage

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

Windows Server 2012 R2 Failover Cluster with SQL Server 2014 Failover Clustered Instance (FCI) #Step-By-Step #AlwaysOn Availability Groupsimageimage

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

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

image image



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)


The following folder locations do not exist on the server instance that hosts secondary replica MVPSQL201402\SQL2:

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



REPLICA ON — primary:
N’MVPSQL201401\sql001′ WITH (ENDPOINT_URL = N’TCP://MVPSQL201401.mvp.local:5023′,
— secondary:
N’MVPSQL201402\SQL2′ WITH (ENDPOINT_URL = N’TCP://MVPSQL201402.mvp.local:5022′,

(WITH IP ((N’′, N’′)), PORT=1433);

BACKUP DATABASE AG04 TO DISK = ‘\\mvpfsw01\SQLAG04\AG04.bak’

BACKUP LOG AG04 TO DISK = ‘\\mvpfsw01\SQLAG04\AG04.trn’









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

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



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’



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.

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











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

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: Create a New #SQL 2012 SP1 Server Failover Cluster (Setup) multiple instances on windows 2012 Quick Guide #TEE13

SQL Server Failover Cluster Installation

The installation of a SQL cluster is easy There is a Menu Item Create new Failover Cluster. #ws2012 #SQLsp1 #sql2012 #cluster #fail #howto

Well I don’t use this option Winking smile Why not to easy eh yes but more important is something fails like wrong IP etc the SQL install is lost , you need to deinstall the whole SQL mesh, I like the advanced version and trust me it will be easy.

OR you can loop at the technet site to see how and when:

SQL Server Failover Cluster Installation

SQL Server 2012

To install a SQL Server failover cluster, you must create and configure a failover cluster instance by running SQL Server Setup.


So how Do we start this installation. Well you can see my blog post :


This blog post covers all the screen shots but this post is more a part 2

What we need :

  1. One ini file that setup the advanced cluster preparation.
  2. one ini file that does advanced cluster completion ( if there is a failure it is always here )
  3. 2 available cluster disk (Data & Log )
  4. SQL Service Accounts
  5. IP number For the SQL Server
  6. Netbios name for the SQL Server
  7. Instance Name



Now that We have the tools lets start.



Just a brief guidance on how to get the INI files.

You can create the ini files based on all the options on teched or create on during the setup wizard.

In this sample I do the add Node to a SQL Server failover Cluster

Start the Wizard

Choose :

  1. add Node to a SQL Server failover Cluster
  2. advanced cluster preparation
  3. advanced cluster completion

You need all 3 ini files and yes if you did choose the create new SQL failover cluster you need only 2.

But now I have More Control and I can deploy the SQL without adding them to the cluster. Like a pre-stage setup.

But for now I take the add Node to a SQL Server failover Cluster


After accepting the license and checked for updates You will get the installation screen.

clip_image004 clip_image006

As you can see I have Multiple SQL instances but currently only on one node, This is a 6 node Cluster and 3 nodes have only the advanced cluster preparation to hold futures SQL instances but I do not want them in my cluster config. Only step 2 will be needed Winking smile


Only one Instance at the time can be added and this is bad It would save you a lot of time. But Scheduled can be your life saver. ( ini file usage )

clip_image008 clip_image010

No more Use password for all the services,

clip_image012 clip_image014

clip_image016  In the Screen ready to install there is a small line

C:\program files \etc

this is the place your ini file is saved for installation. You can cancel the installation now and use the INI file.

SO grab the ini file and lets see if this is working on you cluster.


advanced cluster preparation


Go to the Command Prompt Or Powershell much easyer. or create a cmd file.

Mount the ISO to the server or VM or place it on the disk it is your choice.

So in this location is the SETUP.EXE

I  placed my ini files on a folder sqlsetup and this will removed in my task ( SCCM, VMM etc)


And yes my passwords are in clear text I change the passwords with the accounts when the SQL instance goes to production. Now I can use the same accounts in my scripts and passwords without showing them my real accounts.

I do like the

INDICATEPROGRESS and /QS You can see the progress and or errors.



advanced cluster completion


The second step is the cluster completion.

Use the INI file that is created by the setup with option advanced cluster completion !

Do not use the same ini file. the options are not the same.

So during the setup wizard we created the advanced cluster completion ini file

used it in the same CMD line.

If all goes right You should see something like this.


And you can logon to the SQL manager.



Adjustments to the INI files


Yes some adjustments are needed to the ini files if you want them to use like I did.

First in every ini file there is the uimode quote them out and I used the /QS put it on Thrue



; Setup will display progress only, without any user interaction.


If you do not want to edit the ini files skip the lines in the command line.


And only one setup at the time. But you can run on multiple nodes step 1 if you want more instances ( can not use the same ini files adjustment is needed )


Multiple Instances and the Changes


For multiple instances you need to adjust the ini files

Advanced cluster preparation.ini

For a new instance change the line below in a new value / and or service accounts

;SQL Server 2012 Configuration File advanced cluster preparation

RSSVCACCOUNT="NT Service\ReportServer$SQL0002"

Adjustments for advanced cluster completion.ini

For a new instance change the line below in a new value / and or service accounts use the same as in the advanced cluster preparation.


;SQL Server 2012 Configuration File advanced cluster completion
FAILOVERCLUSTERDISKS="Cluster Disk 4" "Cluster Disk 5"
SQLSYSADMINACCOUNTS="MVP\administrator" "MVP\Sql2012" "MVP\sqlserver"

Add Node to a SQL Server failover Cluster

For a new instance change the line below in a new value / and or service accounts use the same as in the advanced cluster preparation.


;SQL Server 2012 Configuration File

If you have multiple SQL cluster an easy and quick way is change the FAILOVERCLUSTERIPADDRESSES and FAILOVERCLUSTERNETWORKNAME and you have a new cluster that has the same installed base. Or reuse it on your DTAP.

Happy Clustering

How to: Create a Failover Cluster (Setup) Windows Server 2012 Failover Cluster (Setup)

To create a Windows server 2012 failover cluster you need just a few steps.

First we use the group creation to manage the servers easily. I name the Group as it would be my new SQL Cluster. I show you all the screens just so that you can find the options. Not every body find it easy to find the things that he has to do.

clip_image002 clip_image006

clip_image008 So we created the groups and we are adding the Failover cluster Roles to the nodes just right click and add roles and the wizard pops up choose the failover cluster tools do this for both nodes.

 clip_image016  clip_image018 

clip_image020 clip_image022

On top of the screen you can see on what node you install the Roles. Remember you don’t have to go to the node to install the roles you can do this in the server groups.


Or you can use Posh. there are a lot new powershell commands to find them it is easy you can use the *  The above Gui things can be done with one line Posh.

Now there are a few steps you can do to deploy roles

Get-WindowsFeature *

Get-WindowsFeature Failover-Clustering

clip_image024  clip_image026

As you can see this is on the remote cluster node ! I’m logged on on node 1 and it runs also on node 2

install-WindowsFeature Failover-Clustering

Or do it in

clip_image028 When We want to create a new cluster we can do this also in Posh just one line

New-Cluster -Name "myfirstcluster" -Node mvpwin8-n1 -NoStorage -StaticAddress ""

Or gui again I show you all the screens just to see how fast posh is

clip_image030 clip_image032

clip_image034 clip_image036


Remember the validation reports are important to have if you setup fails or you cluster is in trouble run validation and see where to problem is.

clip_image040 clip_image042

7 clicks further your cluster is ready. now we are adding disk to the cluster and configure the witness disk

clip_image044 First in powershell.

My cluster name is : cluster8

the Get-ClusterAvailableDisk -Cluster cluster8

List all available disk


Get-ClusterAvailableDisk -Cluster cluster8

Get-ClusterAvailableDisk -Cluster cluster8 |Add-ClusterDisk

this adds all the disks to the cluster

or in the Gui

clip_image046  Now that the disk are ready we configure the Quorum disk

on top of the cluster right click and configure quorum.

image clip_image050

We have a two node cluster , use the recommended settings.

clip_image052 clip_image054

So all this in 3 lines powershell

install-WindowsFeature Failover-Clustering

Get-ClusterAvailableDisk -Cluster cluster8

New-Cluster -Name "myfirstcluster" -Node mvpwin8-n1 -NoStorage -StaticAddress ""



Did you also see the little export line ? in the Roles installation field save it for later.

image it looks like this.

<?xml version="1.0"?>

<Objs xmlns="http://schemas.microsoft.com/powershell/2004/04" Version=""><Obj RefId="0"><TN RefId="0"><T>System.Collections.ObjectModel.Collection`1[[System.Management.Automation.PSObject, System.Management.Automation, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]</T><T>System.Object</T></TN><LST><Obj RefId="1"><TN RefId="1"><T>Microsoft.Management.Infrastructure.CimInstance#root/Microsoft/Windows/ServerManager/ServerComponent_NET_Framework_Features</T><T>Microsoft.Management.Infrastructure.CimInstance#root/Microsoft/Windows/ServerManager/MSFT_ServerManagerServerComponentDescriptor</T><T>Microsoft.Management.Infrastructure.CimInstance#ServerComponent_NET_Framework_Features</T><T>Microsoft.Management.Infrastructure.CimInstance#MSFT_ServerManagerServerComponentDescriptor</T><T>Microsoft.Management.Infrastructure.CimInstance</T><T>System.Object</T></TN><ToString>ServerComponent_NET_Framework_Features</ToString><Props><Nil N="PSComputerName"/></Props>


So if you want to reuse / import this file on a fresh new server it can.

Import Configuration settings in windows 2012  based on exported Configuration settings in windows 2012

Go to powershell

install-WindowsFeature –configurationfilepath \\server\configfiles\savedconfig.xml

And your done