SQL Server 2012 Database Mirroring or Replication

SQL Server 2012 Database Mirroring to AlwaysOn Availability Group



Migration of SQL Server 2012 Database Mirroring to AlwaysOn Availability group can get a bit tricky if you’re not quite familiar with the workings of SQL Server 2012. Koenig Solutions and its experts are always trying to solve such problems for IT professionals who need to brush up their technical skills or learn an entirely new technology from scratch.
This article gives you a better idea about the migration process of database mirroring to AlwaysON Availability group.
To start with Migration, Basic setup required is as follows:






Install Active Directory on DC machine and name the Domain name as (Contoso.com). Add the other three machines viz. SQLNode1 ,SQLNode2,SQLNode3 to the domain and then switch off the window firewall, open the command prompt and ping to check whether all machines are connected to each other (make sure that you are logged in to the SQLNodes with the domain account not with the local administrator user). When the machines connectivity is ok, start to create the mirroring session between:

• SQLNode1 (PRINCIPAL)
• SQLNode2 (MIRROR)
• SQLNode3 (WITNESS)

We will create 3 mirroring sessions for mirroring and the databases are (AdventureWorks2012, AdventureWorksLT2012, TSQL2012 ) as shown in the figure below :-





As you can see that on SQLNode1, the AdventureWorks2012, AdventureWorksLT2012, TSQL2012 are the Principal and in Synchronized state where as on SQLNode2, AdventureWorks2012, AdventureWorksLT2012, TSQL2012 are in Mirror, Synchronized/ Restoring state.
Now to migrate the database from database mirroring to AlwaysOn, we have to create a WSFC (Windows Server Failover Cluster) by installing Failover Cluster feature from Server Manager (Add features option) as seen in the image below. The Cluster name is SQLCluster.Contoso.com and in the Nodes hierarchy all the three Nodes are visible that we have added to the Cluster.

Now to move the database from database mirroring to AlwaysOn, we have to first remove mirroring from all the three configured databases, that will bring the database on Secondary server i.e., SQLNode2 in Restoring mode.
To remove the mirroring session follow the steps :

• Right click all the three configured database go to its properties.

• In the Database Properties dialog box, select the Mirroring Page option and then click on remove Mirroring button and click on Yes button. This will remove the mirroring between SQLNode1 and SQLNode2 for that particular database.

• Repeat the same step for the other two databases.

Now under the SQLNode1 explore the AlwaysOn High Availability option and then Availability Groups. You will observe that there is no Availability group created yet. So your first step is to create a Availability Group and right click it and select New Availability Group Wizard.

It will open the New Availability Group Wizard, then click Next button and in the next page specify the Availability Group Name as DemoAG, and then click Next. In the select database page, you will find a list of all the databases on SQLNode1 from which you have to choose, which databases you want to move to the Availability group.
In front of each database name, you will find a status column that will show you the status of each database. for example

• Full Recovery Mode is required option is visible when you have not taken a Full backup on the Primary Replica.
• Meet Prerequisites option is visible and it means that you have already taken the full database backup and your database is ready to be added into the AlwaysOn.

On this page we will select all the three database on which we have previously configured the database mirroring (AdventureWorks2012, AdventureWorksLT2012, TSQL2012)and click Next.


Now in the specify Replicas in the Replicas tab, you will find that SQLNode1 node is already added and has the Primary role. On the same page click the Add Replica button and add the other two nodes (SQLNode2 and SQLNode3) that will be treated as Secondary Replica.

Select Automatic failover for SQLNode1 and SQLNode2 and in the Readable Secondary leave the option by default as NO.

Synchronous commit for SQLNode1, SQLNode2, SQLNode3 and in the Readable Secondary select the
Option Read-intent Only.
In the endpoint tab, you will find that the endpoint name has automatically been created and port number has automatically been configured.

In the backup preference tab, you can choose where you want to take the backup. Options are :

• Prefer Secondary
• Secondary Only
• Primary
• Any replica

In our demo we have chosen Secondary Only option. We can also choose the priority for backup storage on nodes and can exclude any node which we don’t want to take backup on.
Then in the Listener tab, choose create an availability group listener option with following configuration:
Listener DNS Name emoAGListener
Port : 1433
Network Mode : Static IP
then click Add and add the IPV4 Address in the following range : 192.168.0.7 and click OK.
Then in the select Data Synchronization page, choose from the following option :
Full :Automatically takes a full backup and log backup of each database and then completes data synchronization process.
Join : The full and log backup of the database has already been taken and we just need to join the selected databases to the availability group.
skip initial data synchronization: Manually takes a full backup and log backup of each database.
For our demo, we have chosen the Join option as we already have the databases in restoring mode on secondary replica.

Then On Validation Page, it will validate whether everything you configured is proper or not, click Next and then click Finish.
Finally we can monitor our Availability Group Replicas using Dashboard which will show us the Active replica and the list of databases configured for mirroring.


Now we can see that AlwaysOn High Availability has been configured and when we explore the Availability group hierarchy, we will find an Availability Group named DemoAAG which we have created and it is the Primary Replica.
Under the Availability replica folder, we can see the three Nodes added

• SQLNode1 (Primary)
• SQLNode2 (Secondary)
• SQLNode3 (Secondary)

Under Availability Databases we can see all the three database.
Under Availability Group Listener, we can see a Listener that we have created named DemoAAGListener.
We can also observe that the databases in the secondary replica are in Synchronized Mode.
Now If we want to failover all the databases to the Secondary replica, we have to do it either manually by right clicking the Availability group that we have created and select failover, which will automatically switch the roles from Primary Replica(SQLNode1) to any of the Secondary Replica, if they are not in read-intent only mode.

We can also connect to the SQL Server by using the Listener name (DemoAAGListener) in the server name.

Hope this post was useful for you. Koenig Solutions offers various SQL Server courses such as Administer and Maintain SQL Server databases, Querying Microsoft SQL Server 2012, High Availability, Business Intelligence Platform. So come and explore the world of SQL Server with us.
This blog post is authored by Mukul Mohta, who is a corporate trainer at Koenig Solutions.


Reference: Pinal Dave (http://blog.sqlauthority.com)