Archive for the ‘SQL Server’ Tag

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 :

http://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 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.

http://1drv.ms/1jgOXzk

 

Have Fun!

@clusterMVP

http://robertsmit.wordpress.com

Posted July 10, 2014 by Robert Smit in SQL, SQL 2012, SQL Server 2014

Tagged with

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

image

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

image

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.

image

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 MVPSQL201402SQL2:
i:MSSQL11.SQL001MSSQLDATA;
(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.

image

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

 

image

CREATE AVAILABILITY GROUP SQL001AG04
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR DATABASE AG04 –, …
REPLICA ON — primary:
N’MVPSQL201401sql001′ 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’MVPSQL201402SQL2′ 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 = ‘\mvpfsw01SQLAG04AG04.bak’
WITH INIT, COPY_ONLY, COMPRESSION;

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

image

 

 

 

 

 

 

 

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 = ‘\mvpfsw01SQLAG04AG04.bak’
WITH REPLACE, NORECOVERY, NOUNLOAD,
MOVE ‘AG04′ TO ‘E:MSSQL11.SQL001AG04.mdf’,
MOVE ‘AG04_log’  TO ‘E:MSSQL11.SQL001AG04_log.ldf';

RESTORE LOG AG04 FROM DISK = ‘\mvpfsw01SQLAG04AG04.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 ‘MVPSQL201401sql001′ 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

 

image

 

 

 

 

 

 

 

 

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.

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

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

http://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

image

 

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

clip_image002

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)

d:setup.exe /qs /CONFIGURATIONFILE="C:SQLSetupsqlconfig-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.

clip_image018

 

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:SQLSetupsqlconfig-2.ini" /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS

If all goes right You should see something like this.

image

And you can logon to the SQL manager.

image

 

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 )

clip_image018

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="MVPSql2012"
ASSVCACCOUNT="MVPSql2012"
SQLSVCACCOUNT="MVPSql2012"
RSSVCACCOUNT="NT ServiceReportServer$SQL0002"
FTSVCACCOUNT="NT ServiceMSSQLFDLauncher$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:OLAPData"
ASLOGDIR="h:OLAPLog"
ASBACKUPDIR="h:OLAPBackup"
ASTEMPDIR="h:OLAPTemp"
ASCONFIGDIR="h:OLAPConfig"
ASSYSADMINACCOUNTS="MVPadministrator"
SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
SQLSYSADMINACCOUNTS="MVPadministrator" "MVPSql2012" "MVPsqlserver"
INSTALLSQLDATADIR="h:"
SQLBACKUPDIR="i:MSSQL11.SQL0002MSSQLBackup"
SQLUSERDBLOGDIR="i:MSSQL11.SQL0002MSSQLData"
SQLTEMPDBLOGDIR="i:MSSQL11.SQL0002MSSQLData"

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="MVPSql2012"
ASSVCACCOUNT="MVPSql2012"
SQLSVCACCOUNT="MVPSql2012"
FTSVCACCOUNT="NT ServiceMSSQLFDLauncher$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

Posted January 16, 2013 by Robert Smit in SQL 2012 SP1

Tagged with

  • Tag