Share this:

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

Share this:

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

So placing this on a CSV SQL server 2014

 

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)

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

SQL Server Failover Cluster Installation

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

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

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

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

https://robertsmit.wordpress.com/2012/03/27/how-to-create-a-new-sql-2012-server-failover-cluster-setup-multiple-instances-on-windows-server-2012/

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.

 

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

 

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 )

 

No more Use password for all the services,

 

  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)

d:\setup.exe /qs /CONFIGURATIONFILE="C:\SQLSetup\sqlconfig-1.ini" /AGTSVCPASSWORD="Password01" /ASSVCPASSWORD="Password01" /SQLSVCPASSWORD="Password01" /ISSVCPASSWORD="Password01" /RSSVCPASSWORD="Password01" /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS

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.
d:\setup.exe /qs /CONFIGURATIONFILE="C:\SQLSetup\sqlconfig-2.ini" /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS

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

 

;UIMODE="Normal"

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

QUIETSIMPLE="True"

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
ACTION="PrepareFailoverCluster"

[OPTIONS]
;UIMODE="Normal"
QUIETSIMPLE="True"
INSTANCENAME="SQL0002"
INSTANCEID="SQL0002"
AGTSVCACCOUNT="MVP\Sql2012"
ASSVCACCOUNT="MVP\Sql2012"
SQLSVCACCOUNT="MVP\Sql2012"
RSSVCACCOUNT="NT Service\ReportServer$SQL0002"
FTSVCACCOUNT="NT Service\MSSQLFDLauncher$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
[OPTIONS]
ACTION="CompleteFailoverCluster"
;UIMODE="Normal"
QUIETSIMPLE="True"
INSTANCENAME="SQL0002"
FAILOVERCLUSTERDISKS="Cluster Disk 4" "Cluster Disk 5"
FAILOVERCLUSTERGROUP="SQL Server (SQL0002)"
FAILOVERCLUSTERIPADDRESSES="IPv4;10.255.255.28;Cluster Network 1;255.255.255.0"
FAILOVERCLUSTERNETWORKNAME="SQL02"
ASCOLLATION="Latin1_General_CI_AS"
ASDATADIR="h:\OLAP\Data"
ASLOGDIR="h:\OLAP\Log"
ASBACKUPDIR="h:\OLAP\Backup"
ASTEMPDIR="h:\OLAP\Temp"
ASCONFIGDIR="h:\OLAP\Config"
ASSYSADMINACCOUNTS="MVP\administrator"
SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
SQLSYSADMINACCOUNTS="MVP\administrator" "MVP\Sql2012" "MVP\sqlserver"
INSTALLSQLDATADIR="h:"
SQLBACKUPDIR="i:\MSSQL11.SQL0002\MSSQL\Backup"
SQLUSERDBLOGDIR="i:\MSSQL11.SQL0002\MSSQL\Data"
SQLTEMPDBLOGDIR="i:\MSSQL11.SQL0002\MSSQL\Data"

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
[OPTIONS]
ACTION="AddNode"
;UIMODE="Normal"
QUIETSIMPLE="True"
INSTANCENAME="SQL0002"
FAILOVERCLUSTERGROUP="SQL Server (SQL0002)"
FAILOVERCLUSTERIPADDRESSES="IPv4;10.255.255.28;Cluster Network 1;255.255.255.0"
FAILOVERCLUSTERNETWORKNAME="SQL02"
AGTSVCACCOUNT="MVP\Sql2012"
ASSVCACCOUNT="MVP\Sql2012"
SQLSVCACCOUNT="MVP\Sql2012"
FTSVCACCOUNT="NT Service\MSSQLFDLauncher$SQL0002"

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

Share this:

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.

 

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.

    

 

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

 

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

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

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

 

 

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.

 

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

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

  Now that the disk are ready we configure the Quorum disk

on top of the cluster right click and configure quorum.

 

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

 

So all this in 3 lines powershell

install-WindowsFeature Failover-Clustering

Get-ClusterAvailableDisk -Cluster cluster8

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

 

 

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

it looks like this.

<?xml version="1.0"?>

<Objs xmlns="http://schemas.microsoft.com/powershell/2004/04" Version="1.1.0.1"><Obj RefId="0"><TN RefId="0"><T>System.Collections.ObjectModel.Collection`1[[System.Management.Automation.PSObject, System.Management.Automation, Version=3.0.0.0, 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

A hotfix that improves the performance of the "AlwaysOn Availability Group" feature in SQL Server 2012 is available for Windows Server 2008 R2

Hotfix Download Available
View and request hotfix downloads

 

Assume that you enable the AlwaysOn Availability Group feature in Microsoft SQL Server 2012 on a Windows Server 2008 R2-based failover cluster. However, when you use local and remote Availability Group database replicas, the failover time to the local replica takes longer than expected.
Note This issue occurs because of inter-node communication within the cluster.

Share this:

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