SQL Server 2012 always on Step by step configuration –

Prerequisite to configure always on –

1. Domain accounts for SQL Server services should have local admin   access
2. Two IP addess
One is for Cluster
Other one is for always ON listener
Note – These IP address should be in customer facing sub-net
3. UNC share on any non-cluster server and non-domain controller.
Cluster computer account and each node computer account should have  R\W access to this share.

if you are not having domain controller access ask your Window Admin team

– Pre-staged cluster computer account in Active Directory. Account can be pre-staged according to the following article: http://technet.microsoft.com/en-us/library/cc731002.aspx#BKMK_steps_precreating

– Pre-staged AlwaysOn listener computer account in Active Directory. Account can be pre-staged according to the following article: http://technet.microsoft.com/en-us/library/cc731002(WS.10).aspx#BKMK_steps_precreating2

Windows cluster installation: 

1) Install Failover Cluster feature using Server Manager on both/all nodes.

2) Open Failover Cluster Manager and create a new cluster:

3) In “Select Servers” step add all your cluster nodes. Click Next.

4) Pass the validation, all test. Warnings regarding storage are OK – you’re setting up AlwaysOn, so you don’t have shared storage.

5) Specify cluster name and IP address on the next screen. If cluster account was pre-staged make sure to specify correct name (same as the pre-staged computer account name). Make sure to use IP, that was provided by customer and is 100% not in use.

6) Finish cluster installation.

7) Configure quorum or the newly created cluster:

How to configure Quorun –

a. Right Click on Cluster Name –> More action –>Configure Cluster Quorum setting

b) On the “Select Quorum Configuration Option” window select “Select the quorum witness”. Click “Next”.

c) On the “Select Quorum Witnes” window select “Configure a file share witness”. Click “Next”.

d) Specify a file share path, provided by the customer. Click “Next”

e) Click “Next”, then “Finish”

Installing SQL Server:

1) Install SQL Server as a stand-alone instance.

2) Following components are required and recommended for basing SQL Server installation:

3) Complete the installation.

4) Make sure to add SQL Server service account to sysadmins at each instance. This is required.

Enabling AlwaysOn on instance level:

1) Open SQL Server configuration manager.

2) Select SQL Server Services in the left-side menu.

3) Right click SQL Server service and select “Properties”:

4) Go to “AlwaysOn High Availablity Tab” and check “Enable AlwaysOn Availability Groups”:

5) Click “OK”. Make sure to restart SQL Server service.

6) Repeat 1-5 on each node.

Configuring AlwaysOn high availability group (single interface option)

Please skip to the next part if you want to use separate NICs for mirroring traffic and user connections.

1) Open SQL Server Management studio and connect to the first node.

2) Open AlwaysOn High Availablity -> Availablity groups and select “New Availability Group” (make sure to use 2012 Management Studio):

3) In the new window enter availability group name (it may be any name you wish), click “Add” and specify secondary replica name:

4) You can use default settings or customize them.

5) Click “OK”.

Configuring AlwaysOn availability group listener

1) Go to SQL Server Management Studio, expand AlwaysOn High Availablity, expand your Availability Group, created earlier, right click “Availabilty Group Listeners” and select “Add Listener”:

2) Specify Listener DNS Name (if AlwaysOn listener computer account was pre-staged, this should match the name of the computer), enter 1433 into “Port”, for “Network mode” select Static IP and click “Add” to add an IP address:

3) In the “Add windows” select the correct subnet and enter an IP address provided by customer:

4) Click “OK” and “OK”.

Adding/Removing databases to/from AlwaysOn availability group.

1) To add a database to availability group you select “Add database” in “Availability databases” and follow the Wizard.

– You can use fully automated addition. In this case you will need network share, accessible from both servers and with RW rights for SQL Server service account.

SQL Server will perform backup and restore of the database automatically.

– You can use “Join only” addition. In this case you will need to perform full backup and t-log backup and restore (as when configuring Mirroring, WITH NORECOVERY) and then use the Wizard.

2)  To remove a database from availability group just right click the database and select “Remove database from availablity group…”:

Please note that before deleting database, that is part of AlwaysOn HA, you must delete it from Availability group.