Azure Microsoft Cloud Purchase: On-premises SQL Server Zero Migration Migration with Azure Database Migration Service
In the process of the evolution of enterprise IT architecture to cloud native, the business scenario that makes CIO sleepless and DBA lose their hair is the most
core database migration to cloud
.
When facing dozens of GB or even several TB of local SQL Server databases, many companies often adopt the most primitive four-part "shutdown, backup export, cross-network transmission, cloud restore. This approach can barely work in a small workshop-style marginal business, but when faced with financial, e-commerce, or 24-hour high-frequency production systems, it is tantamount to a suicidal gamble: it takes several hours to transmit hundreds of GB of backup files across the sea through public or dedicated lines. During this period, the entire company's business must be completely shut down and the white screen of "system maintenance" must be suspended. Once the transmission is interrupted or the cloud restore reports an error, the long rollback process will not only bring down the KPI for the current quarter, but also expose the enterprise to unbearable financial losses.
In order to completely reduce the dimension and crack down on the industry pain point of "long downtime and high migration risk", Microsoft Cloud (Azure) has pulled out a seamless moving artifact tailored for database modernization--
Azure Database Migration Service(DMS, Database Migration Service)
.
Its core logic is tough and elegant:
Leverage continuous replication of online data streams (Online Migration) for a smooth migration with near-zero Downtime.
Its working principle is very similar to inserting a "real-time blood transfer tube" between the local and cloud databases: at the beginning of the migration, it will first do a full data chassis synchronization; Then, even if the local database is still frantically receiving new orders and rewriting new data, DMS will read the transaction log, this incremental data is "poured back" into the Azure SQL Database in the cloud in real time with millisecond latency. When the data on both sides are completely aligned and the difference is compressed to a few seconds, you only need to pick a moment in the dead of night and click "disconnect and cut the flow", and the business can be smoothly switched to the cloud in a few seconds.
Today we reject the piling up of any official concepts and do not pull boring textbook arguments. Starting directly from the most hard-core modern production practice, hand-in-hand will take you to use the specifications of large factories, and set up this real-time blood transfusion tube in the cloud in 10 minutes to send the local SQL Server to the cloud perfectly and without risk.
The first stage: deep dismantling, online zero-stop migration of the "three-dimensional pipeline model"
Before you go to the Azure console and click the mouse, you must build the underlying data flow model of DMS online migration in your mind. Many people frequently report errors when configuring because they do not understand how the secret code is aligned between the three:
Source position: SQL Server in local physical machine/virtual machine: This is your lifeblood of production. In order to achieve "online without dropping"
For incremental synchronization, the local database must have the full recovery model (Full Recovery Model) enabled, and at least one full backup must have been performed. This is to enable DMS to follow suit and capture the order or password change that each user has just made by reading your transaction log (Transaction Log).
Gold Porter: Azure DMS (Fully Managed Migration Engine): This is a high-security, high-concurrency PaaS computing cluster located in the cloud. It's like a tireless digital truck. In order to pull to the local database and the cloud database at the same time, the DMS instance must be deployed in a virtual network (VNet) that can penetrate into your local intranet through a physical line (ExpressRoute) or a high-security VPN.
Azure SQL Database (or SQL Managed Instance): This is the end of the migration. Until the migration officially opens, it's like an empty room. DMS will first clone the table structure, indexes, and constraints at the pixel level that are exactly the same as the local table structure, indexes, and constraints, and then start to continuously accept the incoming data flow.
The second stage: actual combat exercise -10 minutes to raise high-rise buildings on the ground and set up DMS real-time blood transfusion tubes
Make sure you 've set up a clean terminal on Azure in advance:
Azure SQL Database
(or Azure SQL Managed Instance), and the VPN private line between the local data center and Azure virtual network (VNet) has been completely opened.
Step 1: Open a DMS Fully Managed Migration Workspace
Sign in to the Azure portal.
Enter "Azure Database Migration Services" in the search bar above and click to enter the core console.
Click "Create" at the top: Basic Information: Select your resource group, name the migration service dms-core-prod, and select the region closest to your local computer room (such as East Asia Hong Kong). Service Mode (Service Mode): Critical, Azure Resource Manager must be selected ". Pricing tier (Pricing tier): If you want to play "near zero downtime" online incremental replication, you must accurately select "Premium" (advanced version, supports 4 accounting forces). The standard version only supports offline single export, only the advanced version can unlock online blood transfusion black technology, and Microsoft currently provides a very generous free quota for basic testing for this service. Virtual Network (Virtual Network): Select the VNet that is connected to your local computer room through VPN/leased line.
Click Create. Microsoft's Serverless Orchestration Engine
This migration gateway will be polished for you in the background, and the service will be full of blood online in about 3 minutes.
Phase III: Actual Combat Exercise II-Dimensionality Reduction Strike, Open Online Real-Time Data Inflow
When the status of the DMS instance lights up green
Succeeded
At that time, the most hard-core moving campaign officially started. Click to enter the DMS instance.
1. New Migration Dashboard Project (New Migration Project)
Click "New Migration Project" at the top ".
Project name: The name is proj-sql-to-azure.
Source server type: Select SQL Server.
Target server type: Select the Azure SQL Database (based on your cloud receiver choice).
Choose type of activity: As a soul-infusing step, you must and firmly select "Online data migration" (online data migration).
2. Align the two-boundary joint code
Click Next to go to the Credentials filling page for the source and destination stations:
Source details (source details): enter the intranet IP address, port number of your local SQL Server, and DBA account password with sysadmin permissions.
Target details (target details): Enter the server domain name of your Azure SQL Database (for example, sql-prod-srv.database.windows.net) and the administrator account password of the cloud.
Select Database: The system automatically fetches a list of all databases in the local SQL Server. Check the core business library you need to move (such as db_ecommerce).
3. Layout of intermediate transfer stations (network sharing path)
Online migration requires a transfer station that can be read and written by both local and cloud DMS to temporarily store transaction log backups.
In the Network Location form, enter an SMB network share path (such as \\local-nas \SQL _backup) in your local computer room.
Enter the password of the domain account that can read and write the path. DMS will notify the local SQL Server to continuously spit incremental logs into this shared directory, and then DMS will capture, parse, and replay the logs to the cloud at high frequency from here.
Continuously click Next, and finally press
Start Migration
.
The fourth stage: witness the scene of the miracle-second cascade break, business seamless beach landing
After clicking Start, click to enter the active migration task
(Migration Activity) Details page.
You will see a very intuitive streaming monitoring market:
Full Load Phase (Full Load):DMS is frantically packaging and cloning existing local stock data to the cloud in the background.
Incremental synchronization phase (Incremental Sync): After the full run, the status will change to Syncing. At this point, you let the development deliberately insert a test order into the local database, and you will find that the "incremental update counter" in the large market is beating instantly. In less than one second, the new order has been safely placed in the Azure cloud database thousands of kilometers away. The data difference between the two sides (Downtime the Time Capacity) is compressed within 2 seconds.
Ultimate Tangent Flow Tangent Moment (Cutover)
When the state of death remains in
Ready to Cutover
(Ready to switch), it means that the charge on the cloud has sounded.
Issue an administrative order: notify the front-end development, temporarily change the connection pool of the local APP or website to "read-only", or briefly suspend the front-end for a few seconds to ensure that the local database does not generate any new data writes.
Wait for the "unmigrated transactions" in the market to be completely cleared and the delay to become 0 seconds.
One-click soaring: At the top of the DMS details page, decisively press the "Complete Cutover" (complete switching) button. DMS will force the last two seconds of residual log replay, and then completely and safely cut off the two boundary channels.
The operation and maintenance department will modify the database connection string (Connection String) of the front-end APP with one click to sql-prod-srv.database.windows.net the cloud and restart the front-end service.
The whole process goes from shutdown, cut flow, to full blood resurrection in the cloud,
Substantial downtime for the entire line of business was forced to within 15 seconds to 1 minute.
. Overseas buyers even felt that the web page on their mobile phones had been slightly refreshed, and the rear of the digital empire of the whole enterprise had already successfully met and landed in Microsoft Cloud's top-level high-security computer room without anyone noticing.
The fifth stage: the history of avoiding the pit and tears under the migration of industrial-grade zero downtime.
The elegance of this migration scheme in practice is art. But to survive in the real enterprise-class high-traffic, rigorous production audit battlefield, as the chief database architect, you must close the computer before, conveniently weld the following two real-world holes caused by online synchronization:
1. The fatal "local disk is full" hidden danger (Log Chain Broken)
During online migration, DMS needs to locally read and clean transaction log backups frequently.
Disaster: If your local production library is extremely concurrent (e. g. tens of thousands of writes per second), and your local computer room is assigned to a shared backup target.
(SMB Path) disk space is very limited. Once the speed of DMS in the cloud to capture logs slows down due to slight jitter in the cross-border network, the local SQL Server is still frantically spitting logs into this directory, which will completely plug up the local storage space (Disk Full) within a few hours, directly causing your local production database to hang up due to lack of space on the spot.
Large factory standard death-free gold medal specification: before starting DMS, the local shared backup disk must be allocated at least twice the amount of daily log generation, and a timing script must be written using Powershell: once DMS has successfully confirmed that it has replayed (Acknowledged) a batch of logs, it will automatically physically shred and erase them locally. Hedge network uncertainty with a full physical budget.
2. It is strictly forbidden to let foreign key constraints and triggers "help" during the full load period"
If your local database is designed with extremely complex
Foreign key constraints (Foreign Keys) or triggers (Triggers)
(For example, when inserting table A, the trigger will automatically modify table B).
Insider exposure: When DMS performs the first step of full load (Full Load), it smashes data to the cloud in parallel with multiple threads. If the table structure in the cloud has a high-frequency trigger turned on by default, and DMS has just inserted a piece of data, the trigger will cleverly change other tables in the cloud, which will not only lead to serious double conflicts of data and mismatch of accounts, but also explode the CPU of the cloud database alive in an instant, making the speed of full synchronization slow as a turtle crawling.
Hardcore reinforcement specification: strip first, then dress. When writing DDL scripts on the cloud, first create a bare table without foreign key constraints and triggers on the cloud ". Let DMS pour all massive data at full speed and without hindrance with the highest concurrency. On the eve of clicking the Cutover (switching), a standard SQL script is used to "revive" foreign key constraints, triggers and high-frequency indexes in the cloud with one click ". Conform to the bottom of the database to swallow the appetite to design the pipeline, it will give you a real zero error response.
Summary
Using Azure Database Migration Service to realize online zero-downtime migration of local SQL Server, the core industrial essence is actually simplified into 16 words:
Full-volume bottoming, incremental backflow, log matchmaking, second-level cut-off.
You have completely bid farewell to the original workshop where every time you move a database, you have to look at the network bandwidth every day, worry about file damage, and pull the whole company to stay up late at night to fight a protracted war. All the complicated distributed checksum and high-frequency replay are fully hosted to the PaaS-level data brain of the cloud factory. Sitting in front of the computer, watching the data at both ends align at the pixel level in the blink of an eye, gracefully tap enter to welcome the full age of cloud native.
Blood evolution.
