title: MSSQL-最佳實踐-執行個體級别資料庫上雲RDS SQL Server

author: 風移

摘要

到目前，我們完成了SQL Server備份還原專題系列八篇月報分享：三種常見的資料庫備份、備份政策的制定、查找備份鍊、資料庫的三種恢複模式與備份之間的關系、利用檔案組實作冷熱資料隔離備份方案、如何監控備份還原進度、阿裡雲RDS SQL自動化遷移上雲的一種解決方案以及上個月分享的RDS SDK實作資料庫遷移上阿裡雲，本期我們分享如何将使用者線下或者ECS上自建執行個體級别資料庫一鍵遷移上阿裡雲RDS SQL Server。

适用場景

在我們上一個月分享的

RDS SDK實作資料庫遷移上阿裡雲RDS SQL Server

方案中，我們實作了如何将使用者線下或者ECS上自建的SQL Server執行個體中的一個使用者資料庫自動化遷移上雲到RDS SQL Server，話句話說，它實作的是資料庫級别的遷移上雲方案，即每次遷移上雲使用者線下一個資料庫。

但是，有的使用者可能會遇到這樣的場景，我的線下有幾十上百SQL Server執行個體，每個執行個體又有幾十上百個資料庫，總共就有成千上萬個資料庫遷移上雲。如果是資料庫級别的遷移上雲方案顯得力不從心，效率低下。為了解決使用者大批量資料庫遷移上雲RDS for SQL Server，簡化上雲操作步驟，提高上雲效率，執行個體級别資料庫上雲RDS SQL Server是我們迫切需要解決場景。

實作分析

由于在前一個月分享的

中，我們已經實作了單個資料庫遷移上雲方法，是以實作執行個體級别的遷移上雲我們可以采用如下方案：

将使用者線下執行個體上所有的資料庫全量備份檔案上傳到OSS的一個檔案夾中

周遊OSS上該檔案夾所有的資料庫備份檔案

每一個備份檔案生成一個遷移上雲任務

輸入參數

基于以上的分析，我們的實作方法需要包含如下六個輸入參數，以及這六個輸入參數的解析參見下表：

access_key_id        :   阿裡雲使用者 access key id
access_key_secret    :   阿裡雲使用者access key secret
rds_instance_id    :   RDS SQL執行個體ID
oss_endpoint        :   OSS Endpoint位址
oss_bucket         :   OSS Bucket名
directory            :   使用者資料庫備份檔案在OSS上檔案夾路徑，如果是根目錄，請傳入“/”

具體實作

準備工作

參見上一個月的月報分享

MSSQL · 最佳實踐 · RDS SDK實作資料庫遷移上阿裡雲RDS SQL Server

中的準備工作部分。

代碼實作

在本文，我們使用python版RDS SDK實作資料庫遷移上雲RDS SQL Server，當然你也可以使用C#版、Java版等其他版本，詳細的代碼實作如下：

#!/usr/bin/python

# -*- coding: utf-8 -*-

"""***************************************************************************************
# Script name    : RDSSQLCreateMigrateTasksBatchly.py
# Author        : [email protected]
# Create Date   : 2018-05-17 19:27
# Language         : Python 2.7.10
# Run platform  : Mac OS X 10.12.6

# Purpose        : 
                    This script is for batchly Migration user offline SQL Server databases to alibaba cloud RDS SQL Server.
                    Users' FULL backup files are located on theirselves' OSS Bucket folder already.
                    This script helps users to do migration all offline databases backed-up under the OSS Bucket folder to RDS SQL.
                    We achieve those accomplishments by call alibaba cloud RDS OPENAPI.

# Limitation    :
    RDS Edition : Support RDS edition listed below
                    '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'

# Preparation    :
                  1. python 2.7.x installing (I'm using 2.7.10)
                  2. pip install aliyun-python-sdk-rds
                  3. pip install oss2

# Usage         :
    Help         : python RDSSQLCreateMigrateTasksBatchly.py -h
    Example     : 
                    python ~/Downloads/RDSSQLCreateMigrateTasksBatchly.py -k <access_key_id> -s <access_key_secret> -i <rds_instance_id> -e <oss_endpoint> -b <oss_bucket> -d <directory>
                    
                    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
                    oss_endpoint        :   OSS Endpoint address, fg: oss-cn-beijing.aliyuncs.com
                    oss_bucket             :   OSS Bucket name, fg: atp-test-on-ecs
                    directory             :   Sub folder name under OSS Bucket, fg: Migration/OPENAPIDemo


# Output         : There two sesction output, one is the input variables and the other is the migration requests and response.
*********************Input variables*************************************

************************************************************************

*********************Migration requests**********************************

************************************************************************


# Modify Author : [email protected]
# Modify Date   : 2018-05-19 21:43
# Function:
#**************************************************************************************
"""

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 aliyunsdkrds.request.v20140815 import CreateMigrateTaskRequest
from aliyunsdkrds.request.v20140815 import DescribeDBInstanceAttributeRequest


def main(argv):
    access_key_id =  access_key_secret =  rds_instance_id =  oss_endpoint =  oss_bucket =  directory = ''

    # usage help
    try:
        opts, args = getopt.getopt(argv,"hk:s:i:e:b:d:",["access_key_id=", "access_key_secret=", "rds_instance_id=", "oss_endpoint=", "oss_bucket=", "directory="])
    except getopt.GetoptError:
        print ('%s -k <access_key_id> -s <access_key_secret> -i <rds_instance_id> -e <oss_endpoint> -b <oss_bucket> -d <directory>' % (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> -e <oss_endpoint> -b <oss_bucket> -d <directory>' % (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 ("-e", "-E", "--oss_endpoint"):
            oss_endpoint = arg
        elif opt in ("-b", "-B", "--oss_bucket"):
            oss_bucket = arg
        elif opt in ("-d", "-D", "--directory"):
            if arg.endswith("/"):
                directory = arg
            else:
                directory = str("%s/" % arg)

    # show the input parameters
       print ("\n*********************Input variables*************************************\n" \
           "access_key_id = %s\naccess_key_secret = %s\nrds_instance_id = %s\noss_endpoint = %s\noss_bucket = %s\ndirectory = %s\n" \
           "************************************************************************"
           % (access_key_id, access_key_secret, rds_instance_id, oss_endpoint, oss_bucket, directory))


       # check RDS & OSS region to make sure they are located in the same region.
       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"]

       success, oss_details = oss_bucket_details(access_key_id, access_key_secret, oss_endpoint, oss_bucket)
       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 doesn't 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()

       # RDS & OSS Bucket are in the same region.
       print ("\n*********************Migration requests**********************************")
       full_migrate(access_key_id, access_key_secret, rds_instance_id, oss_endpoint, oss_bucket, directory, rds_db_version)
       print ("************************************************************************")


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


def full_migrate(access_key_id, access_key_secret, rds_instance_id, oss_endpoint, oss_bucket, directory, rds_db_version):
    """
    this supoort full backup files migration.
    """

    # get all backup objects under sub_folder
    key_parts_list, do = oss_list_objects(access_key_id, access_key_secret, oss_endpoint, oss_bucket, directory), 0

    # foreach object
    for key_parts in key_parts_list:
        print ("\n--%s. [%s] migrate to your RDS: [%s] and the database name will be: [%s]." % (do, key_parts.file_key, rds_instance_id, key_parts.db_name))
        do += 1

        # print ("%s" % key_parts.sign_url)

        request = CreateMigrateTaskRequest.CreateMigrateTaskRequest()
        request.set_DBInstanceId(rds_instance_id)
        request.set_DBName(key_parts.db_name)
        request.set_BackupMode("FULL")
        request.set_IsOnlineDB(True)
        if rds_db_version == '2008r2':
            request.set_DBName(key_parts.db_name.lower())
            request.set_OSSUrls(key_parts.sign_url)
        else:
            request.set_OSSUrls("")
            request.set_OssObjectPositions("%s:%s:%s" % (oss_endpoint, oss_bucket, key_parts.file_key)) # OSSEndpoint:OSSBucket:OSSFileKey
            request.set_CheckDBMode("SyncExecuteDBCheck")

        success, response = _send_request(access_key_id, access_key_secret, request)

        if success:
            print response

            print ("--I'm sleeping for 2 seconds....")
            time.sleep(2)
        else:
            print ("OPENAPI Response Error !!!!! : %s" % 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 = AcsClient(access_key_id, access_key_secret, 'cn-hangzhou')
        clt = AcsClient(access_key_id, access_key_secret, region)
        response_str = clt.do_action_with_exception(request)
        response_detail = json.loads(response_str)
        return True, response_detail
    except Exception as e:
        return False, e


class oss_key_parts(object):
    """
    if the whole object file key looks like blow:
    Migration/OPENAPIDemo/TestMigration_FULL_20180518153544.bak
    
    then

    : param str file_key: OSS object file key.
    : param str sub_folder: OSS sub folder name, such as Migration/OPENAPIDemo
    : param str file_name: OSS object file name, such as TestMigration_FULL_20180518153544.bak
    : param str db_name: database name, such as 'TestMigration'
    : param str bak_type: backup type , such as 'FULL'
    : param str date: backup date time, such as '20180518153544'
    : param str ext: backup file extendsion, such as 'bak'

    """
    def __init__(self):
        self.file_key = ''
        self.sub_folder = ''
        self.file_name = ''
        self.db_name = ''
        self.bak_type = ''
        self.date = ''
        self.ext = ''
        self.sign_url = ''

"""
parse the OSS file key string into oss key parts
and return oss_key_parts object.
"""
def oss_key_parse(file_key):

    key_parts = oss_key_parts()
    try:
        if file_key.find('/') >= 0:
            file_key_parts = file_key.rsplit('/', 1)
        else:
            file_key_parts = file_key
            file_key_parts = ['/', file_key]

        key_parts.file_key = file_key
        key_parts.sub_folder = file_key_parts[0]
        key_parts.file_name = file_key_parts[1]

        key_list = file_key_parts[1].rsplit('_', 2)

        key_parts.db_name, \
        key_parts.bak_type, \
        key_parts.date, \
        key_parts.ext = key_list[0], \
                        key_list[1], \
                        key_list[2].rsplit('.', 1)[0], \
                        key_list[2].rsplit('.', 1)[1]
    except Exception, e:
        pass

    return key_parts

def oss_list_objects(access_key_id, access_key_secret, oss_endpoint, oss_bucket, directory):
    """
    list all OSS objects under the specified sub folder
    and return the objects list.
    """
    bucket = oss2.Bucket(oss2.Auth(access_key_id, access_key_secret), oss_endpoint, oss_bucket)
    key_parts_list = []

    # OSS Bucket Root
    if directory == '/':
        for object_info in oss2.ObjectIterator(bucket, delimiter='/'):
            if not object_info.is_prefix():
                key_part = oss_key_parse(object_info.key)

                # get object sign_url
                key_part.sign_url = bucket.sign_url('GET', object_info.key, 24 * 3600)

                if key_part.ext in['bak', 'trn', 'log', 'diff']:
                    key_parts_list.append(key_part)
                else:
                    print ("Warning!!!!!, [%s] is not backup file, filtered." % (key_part.file_key))
    else:
        for i, object_info in enumerate(oss2.ObjectIterator(bucket, prefix=directory)):
            # have to the backup files, not folder
            if not object_info.is_prefix():
                if object_info.key.startswith(directory) and object_info.key != directory:
                    # print ("%s" % (object_info.key))
                    key_part = oss_key_parse(object_info.key)
                    
                    # get object sign_url
                    key_part.sign_url = bucket.sign_url('GET', object_info.key, 24 * 3600)

                    if key_part.ext in['bak', 'trn', 'log', 'diff']:
                        key_parts_list.append(key_part)
                    else:
                        print ("Warning!!!!!, [%s] is not a vaild backup file, filtered." % (key_part.file_key))

    if not key_parts_list:
        print("There is no backup file on OSS Bucket [%s] under [%s] folder, check please." % (oss_bucket, directory))

    return key_parts_list


def oss_bucket_details(access_key_id, access_key_secret, oss_endpoint, oss_bucket):
    try:
        bucket = oss2.Bucket(oss2.Auth(access_key_id, access_key_secret), oss_endpoint, oss_bucket)
        bucket_info = bucket.get_bucket_info()
        # print ("bucket name:%s, region: %s" % (bucket_info.name, bucket_info.location))
        return True, bucket_info
    except Exception as e:
        return False, e

if __name__ == '__main__':
    main(sys.argv[1:])

當然，以上代碼，你也可以去

下載下傳

以上python腳本。

使用方法

我們從以下三個方面簡要介紹下如何使用執行個體級别一鍵遷移上雲：

檢視Help

一個例子

輸出結果

檢視Help

你隻需要使用-h來檢視腳本的使用方法：

python ~/Downloads/RDSSQLCreateMigrateTasksBatchly.py -h
~/Downloads/RDSSQLCreateMigrateTasksBatchly.py -k <access_key_id> -s <access_key_secret> -i <rds_instance_id> -e <oss_endpoint> -b <oss_bucket> -d <directory>

一個例子

以下是一個具體的例子：

python ~/Downloads/RDSSQLCreateMigrateTasksBatchly.py -k LTAIQazXKPRwwErT -s BMkIUhroubQOLpOMqfA09IKlqp4G2k -i rm-2zesz5774ud8s71i5 -e oss-cn-beijing.aliyuncs.com -b atp-test-on-ecs -d Migration/OPENAPIDemo

輸出結果

執行以上指令以後的結果輸出，分為兩個部分：

第一部分輸入參數：展示所有你的輸入參數，以便查詢輸入錯誤

第二部分提示資訊：告訴你，哪一個備份檔案會被遷移到哪個執行個體的哪一個資料庫

如下的一個執行個體的輸出資訊：

*********************Input variables*************************************
access_key_id = LTAIQazXKPRwwErT
access_key_secret = BMkIUhroubQOLpOMqfA09IKlqp4G2k
rds_instance_id = rm-2zesz5774ud8s71i5
oss_endpoint = oss-cn-beijing.aliyuncs.com
oss_bucket = atp-test-on-ecs
directory = Migration/OPENAPIDemo/
************************************************************************

*********************Migration requests**********************************

--0. [Migration/OPENAPIDemo/TestCollation_FULL_20180523225534.bak] migrate to your RDS: [rm-2zesz5774ud8s71i5] and the database name will be: [TestCollation].
{u'DBInstanceId': u'rm-2zesz5774ud8s71i5', u'BackupMode': u'FULL', u'MigrateTaskId': u'106121', u'RequestId': u'67E0DD7F-7219-4F67-AAE7-B27273921303', u'TaskId': u'68244691', u'DBName': u'TestCollation'}
--I'm sleeping for 2 seconds....

--1. [Migration/OPENAPIDemo/TestMigration_FULL_20180523225534.bak] migrate to your RDS: [rm-2zesz5774ud8s71i5] and the database name will be: [TestMigration].
{u'DBInstanceId': u'rm-2zesz5774ud8s71i5', u'BackupMode': u'FULL', u'MigrateTaskId': u'106122', u'RequestId': u'0916CD14-861B-4BF7-A68A-409E3996B0D3', u'TaskId': u'68244695', u'DBName': u'TestMigration'}
--I'm sleeping for 2 seconds....

--2. [Migration/OPENAPIDemo/testdb_FULL_20180523225534.bak] migrate to your RDS: [rm-2zesz5774ud8s71i5] and the database name will be: [testdb].
{u'DBInstanceId': u'rm-2zesz5774ud8s71i5', u'BackupMode': u'FULL', u'MigrateTaskId': u'106123', u'RequestId': u'5835B154-2EE3-4059-BFC4-6F798CDCE546', u'TaskId': u'68244699', u'DBName': u'testdb'}
--I'm sleeping for 2 seconds....
************************************************************************

最後總結

利用本篇文章，我們可以輕松實作使用者線下或者ECS自建的SQL Server執行個體級别資料庫一鍵遷移上雲，以此來極大的提高遷移上雲效率，簡化操作，大大提升了使用者遷移上雲體驗。

參考連結

SQL Server執行個體級别資料庫上雲

MSSQL-最佳實踐-執行個體級别資料庫上雲RDS SQL Server摘要适用場景實作分析具體實作最後總結參考連結

author: 風移

摘要

适用場景

實作分析

輸入參數

具體實作

準備工作

代碼實作

使用方法

檢視Help

一個例子

輸出結果

最後總結

參考連結

繼續閱讀

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

Cloud Studio初體驗

使用 ctypes 進行 Python 和 C 的混合程式設計

【python】【資料處理】畫多元資料分布圖

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

【python】netconf協定對接管理裝置

「Python 網絡自動化」NETCONF —— Python 使用 NETCONF 管理配置 H3C 網絡裝置

在python中建立excel并寫入