SQL Server Online Data Migration Guide

1. Introduction

During a migration from Microsoft SQL Server database to Alibaba Cloud, you can choose between an Alibaba Cloud ECS self-built database or Alibaba Cloud ApsaraDB for RDS. We will use the database mirroring feature of Microsoft SQL Server and tell you how to backup and restore your content.

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

We will also show you how to verify your configuration in a final appendix.

1.1 Introducing SQL Server Database Mirroring

Database mirroring maintains 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 clients (the principal server). The other instance acts as a hot or warm standby server (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.

1.2 Database Mirroring Terms and Definitions

The above diagram is a simple logic network diagram, which demonstrates the main components needed to implement database mirroring. The components, terms and definitions of a data mirroring environment include:

Principal database: A read-write database whose transaction log records are applied to a read-only copy of the database (a 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) that 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.

1.3 Our Test Environment

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

2. Prepare and Check the Environment

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.

2.1 Check the Network Connectivity

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

1. On RepA: ping <IP of RepB>
2. On RepB: ping <IP of RepA>

Next, check whether the default port 1433 is reachable:

1. On RepA: telnet <IP of RepB> 1433
2. On RepB: telnet <IP of RepA> 1433

2.2 Set 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.

2.3 Create 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.

2.4 Stop Scheduled Backup Tasks

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

3. Configure the Database Mirroring and Its Certificate

3.1 Create 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.

3.2 Create Certificate and Encrypt with 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 and so on.

3.3 Create 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.

3.4 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.

3.5 Create 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:

4. Back up the Source Database and Restore the Target SQL Server

4.1 Configure the Backup Source Database

First, open the BackUp 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:

4.2 Restore Backup to Target SQL Server

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

First, go to Device and click on the “…”.

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, hit OK on the popup window:

Finally, we need to check the restore result.

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

5. Start 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(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:

That’s it!

We have now migrated data from your Microsoft SQL Server database to an Alibaba Cloud ECS self-built database using the database mirroring feature. We have also demonstrated how to backup and restore your content.

Appendix 1. Verify 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.

Appendix 1.1 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 

Appendix 1.2 Restart Service Traffic

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

Scenario 1: Application Visit SQL Server Database directly

1. Change the IP address of the database in the application configuration.
2. Create a maintenance plan.
3. 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:

Appendix 2: 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