Performing a failover from the primary to secondary server in a SQL Server Always On Availability Group can be done using SQL Server Management Studio (SSMS). Always On Availability Groups provide high availability and disaster recovery for multiple databases.
Here are the steps to perform a failover using SSMS:
Pre-requisites:
Ensure Always On Availability Group is Configured:
- Make sure that you have already set up an Always On Availability Group with at least one secondary replica.
Connect to SQL Server Instances:
- Open SQL Server Management Studio (SSMS) and connect to the SQL Server instances involved in the Availability Group, including both the primary and secondary replicas.
Perform Failover:
Open the Always On Dashboard:
- In SSMS, navigate to the "Always On High Availability" folder under the server node in the Object Explorer.
View Availability Group Dashboard:
- Right-click on the Availability Group you want to failover and select "Show Dashboard." This opens the Availability Group Dashboard.
Initiate Failover:
- In the Availability Group Dashboard, go to the "Failover" tab.
Review Failover Wizard:
- Click on the "Failover..." button to start the Failover Wizard. Review the information provided in the wizard.
Select Target Replica:
- Choose the secondary replica to which you want to failover. Click "Next."
Confirm Failover:
- Review the summary and click "Finish" to confirm the failover operation.
Monitor Failover Progress:
- The wizard will display the progress of the failover operation. Monitor the process until it completes.
Verify Failover:
- After the failover completes, you can verify the new primary replica by checking the "Availability Replicas" tab in the Availability Group Dashboard.
Review SQL Server Error Log:
- Check the SQL Server Error Log for any messages related to the failover. This log can provide additional information about the failover process.
Validate Applications:
- Validate that applications can connect to the new primary replica and function as expected.
Important Considerations:
Automatic Failover:
- If you have configured automatic failover, the failover process might happen automatically based on predefined conditions.
Read-Only Routing (If Configured):
- If read-only routing is configured, ensure that you update your connection strings accordingly to route read-only connections to the appropriate replica.
Quorum:
- Always ensure that the Availability Group has the required quorum for failover.
It's crucial to perform failovers carefully, especially in a production environment. Understand the implications of the failover on your applications and users. Additionally, consider testing failovers in a controlled environment before doing it in a production setting.