A Guide to Migrating to an SQL Server Online

This tutorial shows how to migrate data from an Microsoft SQL Server database to an Alibaba Cloud ECS-hosted database or an ApsaraDB for RDS instance.

During a migration from an Microsoft SQL Server database to Alibaba Cloud, you have the choice of either migrating to an Alibaba Cloud Elastic Compute Service (ECS) hosted database or migrating directly to an Alibaba Cloud ApsaraDB for RDS database instance. In this tutorial, we will take advantage of the database mirroring feature of Microsoft SQL Server and tell you how to backup and restore your content.

As a bit of a heads up, in this tutorial, we will carry out the following steps:

Prepare and check the environment
Configure the database mirror and its certificate
Back up the source database and restore to the target SQL Server
Start the database mirroring process

In this tutorial, we will also be showing you how you can verify your configuration in a final appendix.

SQL Server Database Mirroring, a Brief Overview

Database mirroring works by keeping two copies of a single database. These two copies reside on different server instances of the SQL Server Database Engine. Typically, these server instances are physically located at different sites.

When you implement database mirroring on a database, it initiates a relationship, known as a database mirroring session, between these server instances. One server instance serves the database to the client, or the principal server. The other instance acts as a hot or warm standby server, or more specifically the mirror server, depending on the configuration and state of the mirroring session.

When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server, but there is a risk of minor data loss.

The principal and mirror servers communicate and cooperate as partners in a database mirroring session. The two partners perform complementary roles in the session: the principal role and the mirror role. At any given time, one partner performs the principal role, and the other partner performs the mirror role. Each partner is described as owning its current role.

The partner that owns the principal role is known as the principal server, and its copy of the database is the current principal database. The partner that owns the mirror role is known as the mirror server, and its copy of the database is the current mirror database. When database mirroring is deployed in a production environment, the principal database is the production database.

When you conduct database mirroring, every INSERT, UPDATE, and DELETE operation on the principal database must be duplicated onto the mirror database as quickly as possible. This is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database in sequence. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record.

Database Mirroring Terms and Definitions

The above diagram is a simple logic network diagram. It demonstrates the main components needed to implement database mirroring. These components, and related terms are defined below:

Principal database: A read-write database whose transaction log records are applied to a read-only copy of the database, which is the mirror database.
Principal server: The partner database to the principal database.
Mirror database: The copy of the database that is typically fully synchronized with the principal database.
Mirror server: The server instance on which the mirror database resides.
Redo queue: The received transaction log records that are waiting on the disk of a mirror server.
Role: The principal server and mirror server perform complementary principal and mirror roles. Optionally, the role of witness could be performed by a third server instance.
Role switching: The taking over of the principal role by the mirror.
Send queue: Unsent transaction log records that have accumulated on the log disk of the principal server.
Session: The relationship that occurs during database mirroring between the principal server, mirror server, and witness server, if present. After a mirroring session starts or resumes, a session is the process where the log records of the principal database that have accumulated on the principal server are sent to the mirror server. The mirror server then writes these log records to the disk as quickly as possible to catch up with the principal server.
Transaction safety: A mirroring-specific database property that determines whether a database mirroring session operates synchronously or asynchronously. There are two safety levels: FULL and OFF.
Witness: For use only with high-safety mode, this is an optional instance of SQL Server that enables the mirror server to recognize when to initiate an automatic failover. Unlike the two failover partners, the witness does not serve the database. Supporting automatic failover is the only role of the witness.
Automatic failover: The process by which, when the principal server becomes unavailable, the mirror server takes over the role of principal server and brings its copy of the database online as the principal database.
Failover partners: The two server instances, the principal server or the mirror server, which act as role-switching partners for a mirrored database.
Forced service: A failover initiated by the database owner upon the failure of the principal server that transfers service to the mirror database while it is in an unknown state.
High-performance mode: The database mirroring session operates asynchronously and uses only the principal server and mirror server. The only form of role switching is forced service with possible data loss.
High-safety mode: The database mirroring session operates synchronously and, optionally, uses a witness, as well as the principal server and mirror server.
Manual failover: A failover initiated by the database owner, while the principal server is still running, that transfers service from the principal database to the mirror database while they are in a synchronized state.

Our Test Environment

In this guide, we will use the following test environment:

Preparing and Checking the Environment

For this tutorial, we recommend you create the same Windows accounts on both SQL Servers, with the same password. In this section, we will demonstrate how to do this as an Administrator user.

Checking the Network Connectivity

First, ping both servers to check there are no firewall-related issues:

On RepA: ping <IP of RepB>
On RepB: ping <IP of RepA>

Next, check whether the default port 1433 is reachable:

On RepA: telnet <IP of RepB> 1433
On RepB: telnet <IP of RepA> 1433

Setting up the Directory Structure

Query the driver letter and directory where the data files and log files for the demo database (tpcc) reside on principal server (RepA) using the following command:

USE master   
go   
SELECT physical_name
FROM    sys.master_filesWHERE   database_id = DB_ID('tpcc')

When you run this command, the following directory should be set up:

Now, create same directories on RepB server.

Creating the Recovery Model and Compatible Level

Run the following SQL command:

USE master
GO
SELECT NAME [Database Name],
       recovery_model_desc [Recovery Model],
       CASE
         WHEN [compatibility_level] = 90 THEN '2005'
         WHEN [compatibility_level] = 100 THEN '2008'
         WHEN [compatibility_level] > 100 THEN '2008+'
         ELSE '2000 or lower version'
       END Compatibility Level]
FROM   sys.databases
WHERE  NAME = 'tpcc'

When you run this command, the following recovery model should be set up:

If Recovery Model of the demo database is not FULL, use the SQL Server Management Studio to modify this.

To achieve this, open the Database Properties – tpcc, click Options, and change the Recovery model to FULL:

Click OK.

Stopping Scheduled Backup Tasks

In the Object Explorer, open the SQL Server Agent and disable routine backup jobs on the principal server (RepA):

Configuring the Database Mirroring and Its Certificate

Creating the Database Master Key

The master key is used to encrypt the certificate, but you must ensure you have put the appropriate safeguards in place to fully protect your database. You must implement the appropriate password and storage protection for the database master key, which is an instance-level object that has a very wide range of impact. You can use the following statement to achieve this:

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password@123';

Now, use the same method to create the database master key on the mirror server.

Creating the Certificate and Encrypting with the Master Key

Now, you need to create the HOST_A_cert certificate on the principal server using the following command:

USE master
GO
CREATE CERTIFICATE Host_A_Cert WITH Subject = 'Host_A Certificate', Expiry_Date = '2019-1-1';

You can find it here:

Use the same method to create a HOST_B_cert certificate on the mirror server.

When all the operations are performed on the mirror server, notice that the related parameters are changed to the values corresponding to the master server. For example, Host_A should be changed to Host_B, you get the picture.

Creating the Endpoint

Use the following SQL statement to create an endpoint in the principal server and specify that port 5022 be used:

--Create endpoint with Certificate Host_A_Cert   
IF NOT EXISTS (SELECT 1 FROM sys.database_mirroring_endpoints)
  BEGIN
     CREATE ENDPOINT [DatabaseMirroring] 
      STATE = STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) 
      FOR DATABASE_MIRRORING ( 
AUTHENTICATION = CERTIFICATE Host_A_Cert, 
ENCRYPTION = REQUIRED Algorithm AES, 
ROLE = ALL);
  END

You can find the endpoint here:

Now, repeat this step, making a slight modification to the name of the certificate on the mirror server, to create the mirror server endpoint.

Backup the Certificate

The purpose of backing up a certificate is to send it to another server and import the certificate. As a result, another server can access this server, the principal server, with a certificate.

To achieve this, run the following command:

BACKUP CERTIFICATE Host_A_Cert TO FILE = 'E:\ShareFolders\Host_A_Cert.cer';

Repeat this step on the mirror server, paying attention to the certificate name and path.

Copy Host_A_Cert.cer and Host_B_Cert.cer should reside in the same folder on both servers.

Creating your Login Account and Database User Account

For each server to create a separate server login account, we need to create a login for the mirror server. You can achieve this by running the following command:

CREATE LOGIN Host_B_Login WITH PASSWORD = 'Password@123';  
CREATE USER Host_B_User For Login Host_B_Login; 
CREATE CERTIFICATE Host_B_Cert AUTHORIZATION Host_B_User 
FROM FILE = 'E:\Certification\Host_B_Cert.cer';  
GRANT CONNECT ON ENDPOINT::[DatabaseMirroring] TO [Host_B_Login];

Repeat this step on the mirror server, paying attention to the login name, username, certificate name and path.

You can now find the accounts here on the Object Explorer:

Backing up the Source Database and Restoring the Target SQL Server

Configuring the Backup Source Database

First, open the Back Up Database – tpcc window. The Backup Type should be set to FULL:

Next, change the Backup type to Transaction Log:

Change the destination to match this new setting. Check the backup files are in the correct repository:

Restoring Backup to the Target SQL Server

Now, we need to restore the backup to the target SQL Server.

First, go to Device and click on the hamburger icon (…). A popup window should appear. Check the backup devices match the devices we set up in the previous step.

Next, go to the Options page and check the Recovery state is set to RESTORE WITH NORECOVERY:

Click Restore and the restore process will begin. A progress bar should appear:

Once the restore process has completed, click OK on the popup window:

Finally, we need to check the restore result.

In Object Explorer, check the tpcc (Restoring…) tab is shown.

Starting the Database Mirroring Process

First, we need to add the partner on the principal and mirror databases. To achieve this, perform the following SQL statement on RepB, which is the Mirroring Database:

ALTER DATABASE tpcc   
    SET PARTNER = 'TCP://RepA:5022';  
GO

Next, perform following SQL statement on RepA(Principal Database):

ALTER DATABASE tpcc   
    SET PARTNER = 'TCP://RepB:5022';  
GO

Next, check the Database Mirroring Status in the Object Explorer:

Click on the Mirroring page of the Database Properties – tpcc. Enter the principal and mirror server network addresses. When you hit Refresh, the Status should read Synchronized: the databases are fully synchronized:

Now, open the Database Mirroring Monitor tool and double check the server instances are synchronized:

In this guide, we are using the HammerDB Load Testing took to simulate service traffic. If you are also using HammerDB Load Testing, check the Virtual User is running. You can omit this step if you are setting up with a live service.

The Current send rate, Current rate of new transactions and Current restore rate should now be populated with figures > 0:

If you click through to the principal server, the following information should appear:

The following information should appear for the mirror server:

Finally, check the Activity Monitor Status in HammerDB (if relevant) to make sure the database is connected:

And that's it! We have now migrated data from your Microsoft SQL Server database to an Alibaba Cloud ECS-hosted database using the database mirroring feature. We have also demonstrated how to backup and restore your content.

Verifying Your Configuration

You may want to verify your configuration to check the database mirroring service is running as expected. We recommend you carry out these steps immediately after the initial setup and at regular intervals to check your configuration is running. However, you should be aware that to verify your configuration we need to stop service traffic, which maybe disruptive to your business.

Manual Failover Database Roles

First, stop the service traffic.

If you are using the HammerDB load test case in a testing environment, then switch database roles by manual failover on the SQL Server Management Studio (SSMS). In a customer production environment, the service should be stopped for a short time during the role switch.

You can check you have successfully stopped your service traffic by opening the Database Mirroring History window. The transaction log generation speed should be greatly reduced:

Next, open the Database Properties – tpcc window and click Failover for a manual failover:

Click Yes when the following alert appears:

When the failover is complete, check the results in Object Explorer:

Also, check the database roles in Database Mirroring Monitor:

Next, we click Remove Mirroring in the Database properties – tpcc tool:

After that, check the status of principal (RepB) and mirror (RepA) database. In this example, RepA is restoring:

Finally, modify the RepA status by running the following SQL statement on RepA:

RESTORE DATABASE tpcc WITH RECOVERY

Restarting Service Traffic

There are two ways to restart the service traffic, either directly or through Alibaba Cloud Server Load Balancer (SLB).

Scenario 1: Application Visit SQL Server Database directly

Change the IP address of the database in the application configuration.
Create a maintenance plan.
Start the service traffic, sent to RepB, which is new production database server.

Scenario 2: Application Visit SQL Server Database via SLB

1. We do not need to change the IP address of database in the application configuration, because the SLB service IP address is unique to the database visit address.

2. However, we do need to modify the weight of backend servers on the Alibaba Cloud Console. Go to Backend Servers and click to swap the weights of the two servers.

3. The following windows will appear. Simply swap the weights of the two servers and click Confirm:

4. After modification, the weights should now appear as:

5. Next, restart the service.

Note: Using this method, we are using Alibaba Cloud's SLB and SQL Server on ECS service. So, we need not change any IP addresses in the HammerDB or when dealing with a customer production site.

Finally, check the service status either in HammerDB:

And/or on the SSMS Activity Monitor:

Some Useful Documents

Database Mirroring (SQL Server): https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-sql-server?spm=a2o8d.corp_prod_task_detail.0.0.8c30a40EmuKvB

Overview of Always On Availability Groups (SQL Server): https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server

Community