SQL Server Best Practices: Migrating to ApsaraDB RDS for SQL Server Using SDK
By Wang Jianming, Senior Engineer
So far, we have discussed Using Asymmetric Keys to Implement Column Encryption, Database Recovery Models and Backups, Monitoring Backup and Restore Progress, and Using Filegroups to Implement Cold and Hot Data Isolation Backup Solution in the SQL Server Best Practices Series.
In this issue, we show you how to use Alibaba Cloud RDS SDK to implement the migration of offline user data backup files to Alibaba Cloud ApsaraDB RDS for SQL Server.
Scenario Description
For those who do not want to implement the database migration to cloud RDS SQL Server through RDS console and have advanced programming ability, consider using RDS SDK to implement the offline database migration to cloud the RDS SQL instance.
Implementation Analysis
The offline user database backup files are located in OSS, so the migration to the cloud requires designing an OSS-related SDK and an RDS-related SDK.
Required OSS Details
The offline user database backup files are stored in Bucket on OSS, so obtaining database backup files from OSS requires using the OSS SDK. When getting the backup files from OSS, we also need to know the Region where OSS Bucket is located because the migration to the cloud requires the RDS instance to be in the same Region as OSS Bucket. From these analyses, we must know the name of OSS Bucket, its Endpoint and backup filename bak_file_key.
Required RDS Details
The RDS instance refers to the target RDS SQL instance that users need to migrate to the cloud. We need to know the version information of the RDS SQL instance (input parameters for RDS SQL 2008R2 and 2012 and above are slightly different), the Region where the instance is located (RDS instance needs to be in the same Region as OSS bucket), and the name of the database on the target instance. Based on the analysis, for the RDS instance, we need to know the RDS SQL ID and database name.
Input Parameters
To access Alibaba Cloud resources, users need to use the AK of the Alibaba Cloud account or sub-account, namely, access_key_id and access_key_secret, therefore, these two parameters are also necessary. So, finally, our input parameters are defined as the following seven parameters.
access_key_id: Alibaba Cloud user access key idaccess_key_secret: Alibaba Cloud user access key secretrds_instance_id: RDS SQL instance IDdatabase_name: name of the target instance databaseoss_endpoint: OSS Endpoint addressoss_bucket: OSS Bucket namebak_file_key: filename that the user backs up in the OSS Bucket
Implementation Steps
After the implementation analysis is completed, the following sections describe the specific implementation methods in detail, including preparations, code implementation, and usage.
Preparations
Installing Python
First, install the appropriate Python version according to the guidance on the Official Website of Python; we recommend installing version 2.7.10.
Once installed, view the Python version.
Windows
C:\>c:\Python27\python.exe -V
Python 2.7.10If the above information is displayed, it means that you have successfully installed Python 2.7.10. If the system prompts “Not an internal or external command”, check the configuration “Environment Variables” — “Path” and add the Python installation path, as shown in the figure:
Mac/Linux/Unix
$ python -V
Python 2.7.10Installing the SDK Dependency Package
Use pip installation or git clone source code installation. We recommend using pip installation, which is simple, convenient and quick.
Pip installation
pip install aliyun-python-sdk-sts
pip install oss2Install via source code
# git clone openapi
git clone https://github.com/aliyun/aliyun-openapi-python-sdk.git# Install Alibaba Cloud SDK core library
cd aliyun-python-sdk-core
python setup.py install# Install Alibaba Cloud ECS SDK
cd aliyun-python-sdk-ecs
python setup.py install# git clone OSS SDK
git clone https://github.com/aliyun/aliyun-oss-python-sdk.git
cd aliyun-oss-python-sdk# Install OSS2
python setup.py install
Source Code for Python SDK
In this article, the Python RDS SDK is used to implement database migration to the cloud RDS SQL Server. You can also use other versions, such as C# and Java. The detailed code is implemented as follows:
#! /usr/bin/env python-*- coding: utf-8 -*-
//*******************************Simple use***************************************************************
# Script name : RdsSQLCreateMigrateTask.py
# Author: jianming.wjm@alibaba-inc.com
# Create Date: 2018-06-10 19:27
# Language: Python 2.7.10
# Dev platform: Mac OS X 10.12.6# Purpose:
This script is for Migration user of SQL Server databases locally to the Alibaba Cloud RDS SQL Server.
The user's FULL backup files are in their OSS Bucket folder.
This script helps users to migrate an offline database to an Alibaba Cloud RDS SQL Server instance.
We achieve this by calling alibaba cloud RDS OPENAPI.# Limitation:
RDS Edition: Support RDS edition listed below
'2008R2', '2012','2012_web','2012_std', '2012_ent', '2012_std_ha', '2012_ent_ha',
'2014_web','2014_std', '2014_ent', '2014_std_ha', '2014_ent_ha',
'2016_web','2016_std', '2016_ent', '2016_std_ha', '2016_ent_ha'# Usage :
Help : python RdsSQLCreateMigrateTask.py -h
Example :
RdsSQLCreateMigrateTask.py -k <access_key_id> -s <access_key_secret> -i <rds_instance_id> -d <database_name> -e <oss_endpoint> -b <oss_bucket> -f <bak_file_key>
variables description
access_key_id : alibaba cloud user access key id, fg: LTAIKeRvKPRwkaU3
access_key_secret : alibaba cloud user access key secret, fg: BbZ7xhrertQ0dfgMqfAZPByhnp4G2k
rds_instance_id : RDS SQL instance ID, fg: rm-2zesz4564ud8s7123
database_name : The database name will be on RDS.
oss_endpoint : OSS Endpoint address, fg: oss-cn-beijing.aliyuncs.com
oss_bucket : OSS Bucket name, fg: test-oss-bucket
bak_file_key : The backup file key on OSS,fg: Migration/TestMigration_FULL_20180523225534.bak : calling example:
$ python RdsSQLCreateMigrateTask.py -k LTAIKeRvKPRwkaU3 -s BbZ7xhrertQ0dfgMqfAZPByhnp4G2k -i rm-2zesz4564ud8s7123 -d testmigrate -e oss-cn-beijing.aliyuncs.com -b test-oss-bucket -f Migration/TestMigration_FULL_20180523225534.bak
# Output: There are two section outputs, one is the input variables and the other is the migration requests and response.
*********************Input variables**********************************************************************************************************************************Migration requests**********************************************************************************************************
# Modify Author: jianming.wjm@alibaba-inc.com
# Modify Date: 2018-06-11 21:43
# Function:
//*******************************Simple use***************************************************************
"""import json
import os
import sys, getopt
import re
import oss2
import timefrom aliyunsdkcore.client import AcsClient
from aliyunsdkrds.request.v20140815 import DescribeMigrateTasksForSQLServerRequest
From Fig. Request. v20140815 import maid
from aliyunsdkvpc.request.v20160428 import DescribeVpcAttributeRequest
def main(argv):
access_key_id = access_key_secret = rds_instance_id = oss_endpoint = oss_bucket = bak_file_key = database_name = '' # usage help
try:
opts, args = getopt.getopt(argv,"hk:s:i:d:e:b:f:",["access_key_id=", "access_key_secret=", "rds_instance_id=", "database_name=", "oss_endpoint=", "oss_bucket=", "bak_file_key="])
except getopt.GetoptError:
print ('%s -k <access_key_id> -s <access_key_secret> -i <rds_instance_id> -d <database_name> -e <oss_endpoint> -b <oss_bucket> -f <bak_file_key>' % (sys.argv[0]))
sys.exit(2) for opt, arg in opts:
if opt == '-h':
print ('%s -k <access_key_id> -s <access_key_secret> -i <rds_instance_id> -d <database_name> -e <oss_endpoint> -b <oss_bucket> -f <bak_file_key>' % (sys.argv[0]))
sys.exit()
elif opt in ("-k", "-K", "--access_key_id"):
access_key_id = arg
elif opt in ("-s", "-S", "--access_key_secret"):
access_key_secret = arg
elif opt in ("-i", "-I", "--rds_instance_id"):
rds_instance_id = arg
elif opt in ("-d", "-D", "--database_name"):
database_name = arg
elif opt in ("-e", "-E", "--oss_endpoint"):
oss_endpoint = arg
elif opt in ("-b", "-B", "--oss_bucket"):
oss_bucket = arg
elif opt in ("-f", "-F", "--bak_file_key"):
bak_file_key = arg # show the input parameters
print ("\n*********************Input variables****************************************\n" \
"access_key_id = %s\naccess_key_secret = %s\nrds_instance_id = %s\ndatabase_name = %s\n" \
"oss_endpoint = %s\noss_bucket = %s\nbak_file_key = %s\n" \
//*******************************Simple use***************************************************************
% (access_key_id, access_key_secret, rds_instance_id, database_name, oss_endpoint, oss_bucket, bak_file_key))
### check RDS & OSS region to make sure they are located in the same region. # get RDS details
success, rds_details = rds_instnace_details(access_key_id, access_key_secret, rds_instance_id)
if not success:
print ("%s" % rds_details)
sys.exit() rds_db_version, rds_engine, rds_region = rds_details["EngineVersion"], rds_details["Engine"], rds_details["RegionId"] # get OSS Bucket
success, oss_details, sign_url = oss_bucket_details(access_key_id, access_key_secret, oss_endpoint, oss_bucket, bak_file_key)
if not success:
print ("%s" % oss_details)
sys.exit() oss_region = oss_details.location
# support db version checking. if rds_engine ! = 'SQLServer' \
or rds_db_version not in [ '2008r2', '2012','2012_web','2012_std', '2012_ent', '2012_std_ha', '2012_ent_ha',
'2014_web','2014_std', '2014_ent', '2014_std_ha', '2014_ent_ha',
'2016_web','2016_std', '2016_ent', '2016_std_ha', '2016_ent_ha']:
print("RDS engine does not support, this is only for RDS SQL Server engine.")
sys.exit() # RDS & OSS Bucket are not under the same region.
if not oss_region.endswith(rds_region):
print("RDS & OSS Bucket are not located in the same region.")
sys.exit() # everything is OK, we can go to the real business.
print ("--[%s] will be migrated to your RDS [%s] and the database name will be [%s]." % (bak_file_key, rds_instance_id, database_name)) # RDS & OSS Bucket are in the same region.
print ("\n*********************Migration response**********************************")
do_migrate_database(access_key_id, access_key_secret, rds_instance_id, database_name, oss_endpoint, oss_bucket, bak_file_key, rds_db_version, sign_url)
print ("************************************************************************")"""
action to migrate database into RDS
"""
def do_migrate_database(access_key_id, access_key_secret, rds_instance_id, database_name, oss_endpoint, oss_bucket, bak_file_key, rds_db_version, sign_url):
request = CreateMigrateTaskRequest.CreateMigrateTaskRequest()
request.set_DBInstanceId(rds_instance_id)
request.set_DBName(database_name)
request.set_BackupMode("FULL")
request.set_IsOnlineDB(True)
if rds_db_version == '2008r2':
request.set_DBName(database_name.lower())
request.set_OSSUrls(sign_url)
else:
request.set_OSSUrls("")
request.set_OssObjectPositions("%s:%s:%s" % (oss_endpoint, oss_bucket, bak_file_key)) # OSSEndpoint:OSSBucket:OSSFileKey
request.set_CheckDBMode("SyncExecuteDBCheck") success, response = _send_request(access_key_id, access_key_secret, request) if success:
print response
else:
print ("OPENAPI Response Error !!!!! : %s" % response)
"""
get RDS SQL Instance details via RDS OPENAPI.
"""
def rds_instnace_details(access_key_id, access_key_secret, rds_instance_id):
request = DescribeDBInstanceAttributeRequest.DescribeDBInstanceAttributeRequest()
request.set_DBInstanceId(rds_instance_id)
success, response = _send_request(access_key_id, access_key_secret, request) if success:
if response["Items"]["DBInstanceAttribute"]:
# print response["Items"]["DBInstanceAttribute"][0]["EngineVersion"]
# print response["Items"]["DBInstanceAttribute"][0]["RegionId"]
return True, response["Items"]["DBInstanceAttribute"][0]
else:
return False, "Couldn't find specify RDS [%s]." % rds_instance_id
return False, response
"""
send request to OPENAPI
and get the response details
"""
def _send_request(access_key_id, access_key_secret, request, region='cn-hangzhou'):
request.set_accept_format('json')
try:
clt = client.AcsClient(access_key_id, access_key_secret, 'cn-hangzhou')
client = client.AcsClient(access_key_id, access_key_secret, region)
response_str = client.do_action_with_exception(request)
response_detail = json.loads(response_str)
return True, response_detail
except Exception as e:
return False, e
"""
get OSS Bucket details via OSS OPENAPI
"""
def oss_bucket_details(access_key_id, access_key_secret, oss_endpoint, oss_bucket, bak_file_key):
try:
bucket = oss2. Bucket(oss2. Auth(access_key_id, access_key_secret), endpoint, bucket_name)
bucket_info = bucket.get_bucket_info()
return True, bucket_info, bucket.sign_url('GET', bak_file_key, 24 * 3600)
except Exception as e:
return False, e, None
"""
process entrance main.
"""
if __name__ == '__main__':
main(sys.argv[1:])
For the above code, you can also download the Python script referenced here.
Implementing the Migration
View Help
You can use -h to see how scripts are used:
$ python ~/Downloads/RdsSQLCreateMigrateTask.py -h
~/Downloads/RdsSQLCreateMigrateTask.py -k <access_key_id> -s <access_key_secret> -i <rds_instance_id> -d <database_name> -e <oss_endpoint> -b <oss_bucket> -f <bak_file_key>Example Implementation
The following is a specific example of migration using the RDS SDK:
$ python ~/Downloads/RdsSQLCreateMigrateTask.py -k LTAIKeRvKPRwkaU3 -s BbZ7xhrertQ0dfgMqfAZPByhnp4G2k -i rm-2zesz4564ud8s7123 -d testmigrate -e oss-cn-beijing.aliyuncs.com -b test-oss-bucket -f Migration/OPENAPIDemo/TestMigration_FULL_20180523225534.bakOutput Results
The output from executing the above command is divided into three parts:
- The first part is input parameters: shows all your input parameters to query for input errors
- The second part is the prompt message: tells you which backup file is migrated to which database of which instance
- The third part is the call return: return information of RDS OPENAPI SDK
*********************Input variables****************************************
access_key_id = LTAIKeRvKPRwkaU3
access_key_secret = BbZ7xhrertQ0dfgMqfAZPByhnp4G2k
rds_instance_id = rm-2zesz4564ud8s7123
database_name = testmigrate
oss_endpoint = oss-cn-beijing.aliyuncs.com
oss_bucket = test-oss-bucket
bak_file_key = Migration/OPENAPIDemo/TestMigration_FULL_20180523225534.bak
//*******************************Simple use***************************************************************--[Migration/OPENAPIDemo/TestMigration_FULL_20180523225534.bak] is migrated to your RDS [rm-2zesz4564ud8s7123] and the database name will be [testmigrate].*********************Migration response**********************************
{u'DBInstanceId': u'rm-2zesz4564ud8s7123', u'BackupMode': u'FULL', u'MigrateTaskId': u'107081', u'RequestId': u'F5419B88-7575-47BA-BDEB-64D280E36C54', u'TaskId': u'70630059', u'DBName': u'testmigrate'}
************************************************************************
Summary
This document described the best practices for using RDS SDK OPENAPI as well as the automation implementation of migrating use offline databases to Alibaba Cloud ApsaraDB RDS for SQL Server.
