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

Implementation Analysis

Required OSS Details

Required RDS Details

Input 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

Preparations

Installing Python

Once installed, view the Python version.

Windows

C:\>c:\Python27\python.exe -V
Python 2.7.10

If 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.10

Installing the SDK Dependency Package

Pip installation

pip install aliyun-python-sdk-sts
pip install oss2

Install 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

#! /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 time
from 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

$ 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

$ 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.bak

Output Results

  1. The first part is input parameters: shows all your input parameters to query for input errors
  2. The second part is the prompt message: tells you which backup file is migrated to which database of which instance
  3. 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

Reference:https://www.alibabacloud.com/blog/sql-server-best-practices-migrating-to-apsaradb-rds-for-sql-server-using-sdk_594482?spm=a2c41.12584177.0.0

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com