Skip to main content

Implement High Availability in SQL Server - How to

A week ago I wrote for the replication, how to add articles in replication, how to add filtered articles in replication. Today I would like to write for high availability implementation in SQL server. It is the best option to replace other disaster plans like mirroring, replication and clustering based on need. So let us elaborate it with steps. We will go ahead with an example of SQL Server 2012 enterprise.

1.  Install and configure Windows Failover Cluster’. Go to Server Manager –>Features—>Add features.

2. Select ‘Failover Clustering’ from feature and add it.

3. After Windows Failover Cluster installed Create a Cluster. Go to Failover Cluster Manager –> Create Cluster.

4. Add Servers which need to participate in cluster.

5. Select an option to run validation tests for added servers.

6. Select an option either you wan to run all test or selected tests.

7. Specify a Cluster name and add IP address reserved for windows cluster name object.

8.  Open Failover Cluster Manager and confirm all server added in Nodes.

9. Install SQL Server SQL Server 2012 standalone in all servers and make sure ‘AlwaysOn Availability Groups’ enabled for SQL server instances.

10.  Here we will have a ‘Server1’ as a Primary, ‘Server2’ and ‘Server3’ act as a Secondary. So let us create a sample database in primary server.

11. Make sure the location of databases should be same to all secondary servers and databases have a full recovery model.

12.  Connect primary SQL server instance, go to AlwaysOn High Availability —> New Availability Group Wizard.

13. Specify a AG  name.

14.  Select databases which you would like to participate in AG. Make sure database full backup must be done, also it can be seen in status there.

15. Add ‘Server2’ and ‘Server3’ as a replica and set Server1 and Server2 for Automatic Failover. Make Readable setting for replicas as per need.

16.  Make sure 1433 (or whatever port of instances) and 5022 should be open in the firewall if the firewall is turned on.

17.  Set option of backup preferences and priority where you would like to perform it.

18.  Make one shared folder for full backup of databases which must be accessible to all secondary servers to restore it there.

19. Expand AlwaysOn High Availability—> Availability groups—>Availability Replicas where you can all participated servers in AG.

I will write next some more about it. Hope you enjoy AG. Have a nice day!