SQL Server High Availability - Manual Failover by serverku

Availability As we have seen SQL Server High Availability configuration and listener since same, I would like to go ahead for manual failover test in SQL Server 2012. So I would suggest to go through related previous posts.

1. Configure Listener for high availability in SQL Server
2. Implement High Availability in SQL Server - How to

Moving to test manual failover of SQL Server High Availability and following are the steps. Here Server 2 is a primary replica and Server1 is a secondary replica. Now we have to switch over the primary role from Server2 to Server1.

Step 1 : Connect primary replica availability group. Go to Availability Group and right clink on that and click on Failover.

Step 2 :Select instance, which you want to make a primary replica as shown in the image.

Step 3 : Connect SQL server instance, which you selected in earlier steps.

Step 4 : Click on Next and You can see the current primary replica and new primary replica.

Step 5 : Finally success on next step.

Step 6 : Last step to confirm primary replica, so connect with listener name and confirm as shown in the image.

This is just a manual failover test of Availability Group and hope you enjoyed it. In the next post we will see automated failover of Availability Group.

Configure Listener for high availability in SQL Server 2012 by serverku

A week ago I posted for high availability implementation in SQL Server 2012. I would like to read the earlier post and continue that post with and it’s configure a listener for availability group. So let us follow the steps.

Step 1 :  Connect primary server instance of availability group and go to AlwaysOn High Availability –> Availability Groups—> Availability Group Listeners –> Add Listener

Step 2 :  Assign Listener DNS Name, Port and Network Mode which should be DHCP or statistic IP.

Step 3 : We are done and now it ‘s time to connect and confirm SQL Server primary instance.

It should connect SQL Server primary instance, even a case of automatic or manual failover of instance involved in Availability Groups. So whenever a failover or primary server failure happened, we do not need to changed data source\connection string of application or wherever used. Hope you may like it.

Implement High Availability in SQL Server - How to by serverku

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!