Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog SQL Server Best Practices: Migrating to ApsaraDB RDS for SQL Server Using SDK

SQL Server Best Practices: Migrating to ApsaraDB RDS for SQL Server Using SDK

In this article, we'll discuss how you can implement the migration of a SQL Server database to Alibaba Cloud ApsaraDB RDS for SQL Server through the RDS 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 id

access_key_secret: Alibaba Cloud user access key secret

rds_instance_id: RDS SQL instance ID

database_name: name of the target instance database

oss_endpoint: OSS Endpoint address

oss_bucket: OSS Bucket name

bak_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.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:

1

Mac/Linux/Unix

$ python -V
Python 2.7.10

Installing 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 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

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

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

Output Results

The output from executing the above command is divided into three parts:

  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

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.

Database Storage dts SQL Server Database Backup Disaster Recovery ApsaraDB for SQL Server
0 0 0
Share on

Read previous post:

SQL Server Best Practices: Using Filegroups to Implement Cold and Hot Data Isolation Backup Solution

Read next post:

SQL Server Best Practices: Migrating Multiple Instance-level Databases to ApsaraDB RDS for SQL Server

Cherish Wang

11 posts | 0 followers

You may also like

Comments

Cherish Wang

11 posts | 0 followers

Related Products

More Posts by Cherish Wang

See All