Migrating a SQL Server 2012 database mirroring setup to an AlwaysOn Availability Group can be a complex process if you’re not familiar with the workings of SQL Server 2012. In this article, we will guide you through the migration process and provide you with a better understanding of how it works.
Before we begin the migration, there are a few basic setup requirements that need to be met. First, you need to install Active Directory on a domain controller machine and name the domain as “Contoso.com”. Next, add the other three machines (SQLNode1, SQLNode2, SQLNode3) to the domain and disable the Windows firewall. Make sure you are logged in to the SQLNodes with a domain account, not the local administrator user. Once the machines are connected to each other, create the mirroring session between SQLNode1 (Principal), SQLNode2 (Mirror), and SQLNode3 (Witness).
Now, let’s take a look at the migration process. The first step is to create a Windows Server Failover Cluster (WSFC) by installing the Failover Cluster feature from the Server Manager. This cluster will serve as the foundation for the AlwaysOn Availability Group. Once the cluster is set up, remove the mirroring from all three configured databases, which will bring the databases on the secondary server (SQLNode2) into a restoring mode.
To remove the mirroring session, follow these steps:
- Right-click on each of the three configured databases and go to its properties.
- In the Database Properties dialog box, select the Mirroring Page option.
- Click on the “Remove Mirroring” button and confirm by clicking “Yes”.
Repeat these steps for the other two databases.
Next, under SQLNode1, explore the AlwaysOn High Availability option and then Availability Groups. You will notice that no Availability Group has been created yet. To create an Availability Group, right-click and select the “New Availability Group Wizard”. In the wizard, specify the Availability Group Name as “DemoAG” and select the databases you want to move to the Availability Group.
In the Replicas tab, SQLNode1 will already be added with the Primary role. Add the other two nodes (SQLNode2 and SQLNode3) as Secondary Replicas. Choose automatic failover for SQLNode1 and SQLNode2, and select the “Read-intent Only” option for the Readable Secondary. Configure the endpoint, backup preference, and listener settings as per your requirements.
Once all the settings are configured, validate them and click “Finish” to create the Availability Group. You can monitor the Availability Group Replicas using the Dashboard, which will show you the active replica and the list of databases configured for mirroring.
Now that the AlwaysOn High Availability has been configured, you can failover all the databases to the secondary replica by manually right-clicking the Availability Group and selecting “Failover”. This will automatically switch the roles from the Primary Replica (SQLNode1) to one of the Secondary Replicas, if they are not in read-intent only mode.
You can also connect to the SQL Server using the Listener name (DemoAAGListener) in the server name.
We hope this post has provided you with a better understanding of the migration process from SQL Server 2012 Database Mirroring to AlwaysOn Availability Group. If you have any questions or need further assistance, feel free to leave a comment below.