适用场景

在我们上一个月分享的方案中，我们实现了如何将用户线下或者ECS上自建的SQL Server实例中的一个用户数据库自动化迁移上云到RDS SQL Server，话句话说，它实现的是数据库级别的迁移上云方案，即每次迁移上云用户线下一个数据库。

但是，有的用户可能会遇到这样的场景，我的线下有几十上百SQL Server实例，每个实例又有几十上百个数据库，总共就有成千上万个数据库迁移上云。如果是数据库级别的迁移上云方案显得力不从心，效率低下。为了解决用户大批量数据库迁移上云RDS for SQL Server，简化上云操作步骤，提高上云效率，实例级别数据库上云RDS SQL Server是我们迫切需要解决场景。

由于在前一个月分享的RDS SDK实现数据库迁移上阿里云RDS SQL Server中，我们已经实现了单个数据库迁移上云方法，因此实现实例级别的迁移上云我们可以采用如下方案：

将用户线下实例上所有的数据库全量备份文件上传到OSS的一个文件夹中

遍历OSS上该文件夹所有的数据库备份文件

每一个备份文件生成一个迁移上云任务

具体实现

参见上一个月的月报分享中的准备工作部分。

在本文，我们使用python版RDS SDK实现数据库迁移上云RDS SQL Server，当然你也可以使用C#版、Java版等其他版本，详细的代码实现如下：

  3. # -*- coding: utf-8 -*-
  5. """***************************************************************************************
  6. # Script name    : RDSSQLCreateMigrateTasksBatchly.py
  7. # Author        : jianming.wjm@alibaba-inc.com
  8. # Create Date   : 2018-05-17 19:27
  9. # Language         : Python 2.7.10
  10. # Run platform  : Mac OS X 10.12.6
  12. # Purpose        : 
  13.                     This script is for batchly Migration user offline SQL Server databases to alibaba cloud RDS SQL Server.
  14.                     Users' FULL backup files are located on theirselves' OSS Bucket folder already.
  15.                     This script helps users to do migration all offline databases backed-up under the OSS Bucket folder to RDS SQL.
  16.                     We achieve those accomplishments by call alibaba cloud RDS OPENAPI.
  18. # Limitation    :
  19.     RDS Edition : Support RDS edition listed below
  20.                     '2012','2012_web','2012_std', '2012_ent', '2012_std_ha', '2012_ent_ha',
  21.                        '2014_web','2014_std', '2014_ent', '2014_std_ha', '2014_ent_ha',
  22.                        '2016_web','2016_std', '2016_ent', '2016_std_ha', '2016_ent_ha'
  24. # Preparation    :
  25.                   1. python 2.7.x installing (I'm using 2.7.10)
  26.                   2. pip install aliyun-python-sdk-rds
  27.                   3. pip install oss2
  29. # Usage         :
  30.     Help         : python RDSSQLCreateMigrateTasksBatchly.py -h
  31.     Example     : 
  32.                     python ~/Downloads/RDSSQLCreateMigrateTasksBatchly.py -k <access_key_id> -s <access_key_secret> -i <rds_instance_id> -e <oss_endpoint> -b <oss_bucket> -d <directory>
  34.                     variables description
  35.                     access_key_id        :    alibaba cloud user access key id, fg: LTAIKeRvKPRwkaU3
  36.                     access_key_secret    :    alibaba cloud user access key secret, fg: BbZ7xhrertQ0dfgMqfAZPByhnp4G2k
  37.                     rds_instance_id        :   RDS SQL instance ID, fg: rm-2zesz4564ud8s7123
  38.                     oss_endpoint        :   OSS Endpoint address, fg: oss-cn-beijing.aliyuncs.com
  39.                     oss_bucket             :   OSS Bucket name, fg: atp-test-on-ecs
  40.                     directory             :   Sub folder name under OSS Bucket, fg: Migration/OPENAPIDemo
  43. # Output         : There two sesction output, one is the input variables and the other is the migration requests and response.
  44. *********************Input variables*************************************
  46. ************************************************************************
  48. *********************Migration requests**********************************
  50. ************************************************************************
  53. # Modify Date   : 2018-05-19 21:43
  54. # Function:
  55. #**************************************************************************************
  56. """
  58. import json
  59. import os
  60. import sys, getopt
  62. import oss2
  63. import time
  65. from aliyunsdkcore.client import AcsClient
  66. from aliyunsdkrds.request.v20140815 import DescribeMigrateTasksForSQLServerRequest
  67. from aliyunsdkrds.request.v20140815 import CreateMigrateTaskRequest
  68. from aliyunsdkrds.request.v20140815 import DescribeDBInstanceAttributeRequest
  71. def main(argv):
  72.     access_key_id =  access_key_secret =  rds_instance_id =  oss_endpoint =  oss_bucket =  directory = ''
  74.     # usage help
  75.     try:
  76.         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="])
  77.     except getopt.GetoptError:
  78.         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]))
  79.         sys.exit(2)
  81.     for opt, arg in opts:
  82.         if opt == '-h':
  83.             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]))
  84.             sys.exit()
  85.         elif opt in ("-k", "-K", "--access_key_id"):
  86.             access_key_id = arg
  87.         elif opt in ("-s", "-S", "--access_key_secret"):
  88.             access_key_secret = arg
  89.         elif opt in ("-i", "-I", "--rds_instance_id"):
  90.             rds_instance_id = arg
  91.         elif opt in ("-e", "-E", "--oss_endpoint"):
  92.             oss_endpoint = arg
  93.         elif opt in ("-b", "-B", "--oss_bucket"):
  94.             oss_bucket = arg
  95.         elif opt in ("-d", "-D", "--directory"):
  96.             if arg.endswith("/"):
  97.                 directory = arg
  98.             else:
  99.                 directory = str("%s/" % arg)
  101.     # show the input parameters
  102.        print ("\n*********************Input variables*************************************\n" \
  103.            "access_key_id = %s\naccess_key_secret = %s\nrds_instance_id = %s\noss_endpoint = %s\noss_bucket = %s\ndirectory = %s\n" \
  104.            "************************************************************************"
  105.            % (access_key_id, access_key_secret, rds_instance_id, oss_endpoint, oss_bucket, directory))
  108.        # check RDS & OSS region to make sure they are located in the same region.
  109.        success, rds_details = rds_instnace_details(access_key_id, access_key_secret, rds_instance_id)
  110.        if not success:
  111.            print ("%s" % rds_details)
  112.            sys.exit()
  114.        rds_db_version, rds_engine, rds_region = rds_details["EngineVersion"], rds_details["Engine"], rds_details["RegionId"]
  116.        success, oss_details = oss_bucket_details(access_key_id, access_key_secret, oss_endpoint, oss_bucket)
  117.            print ("%s" % oss_details)
  118.            sys.exit()
  120.        oss_region = oss_details.location
  124.        if rds_engine != 'SQLServer' \
  125.            or rds_db_version not in [    '2008r2', '2012','2012_web','2012_std', '2012_ent', '2012_std_ha', '2012_ent_ha',
  126.                                        '2014_web','2014_std', '2014_ent', '2014_std_ha', '2014_ent_ha',
  127.                                        '2016_web','2016_std', '2016_ent', '2016_std_ha', '2016_ent_ha']:
  128.            print("RDS engine doesn't support, this is only for RDS SQL Server engine.")
  129.            sys.exit()
  131.        # RDS & OSS Bucket are not under the same region.
  132.        if not oss_region.endswith(rds_region):
  133.            print("RDS & OSS Bucket are not located in the same region.")
  134.            sys.exit()
  136.        # RDS & OSS Bucket are in the same region.
  137.        print ("\n*********************Migration requests**********************************")
  138.        full_migrate(access_key_id, access_key_secret, rds_instance_id, oss_endpoint, oss_bucket, directory, rds_db_version)
  139.        print ("************************************************************************")
  142. def rds_instnace_details(access_key_id, access_key_secret, rds_instance_id):
  143.     request = DescribeDBInstanceAttributeRequest.DescribeDBInstanceAttributeRequest()
  144.     request.set_DBInstanceId(rds_instance_id)
  145.     success, response = _send_request(access_key_id, access_key_secret, request)
  147.     if success:
  148.         if response["Items"]["DBInstanceAttribute"]:
  149.             # print response["Items"]["DBInstanceAttribute"][0]["EngineVersion"]
  150.             # print response["Items"]["DBInstanceAttribute"][0]["RegionId"]
  151.             return True, response["Items"]["DBInstanceAttribute"][0]
  152.         else:
  153.             return False, "Couldn't find specify RDS [%s]." % rds_instance_id
  156.     return False, response

当然，以上代码，你也可以去下载以上python脚本。

我们从以下三个方面简要介绍下如何使用实例级别一键迁移上云：

查看Help

一个例子

查看Help

你只需要使用-h来查看脚本的使用方法：

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

一个例子

以下是一个具体的例子：

输出结果

执行以上命令以后的结果输出，分为两个部分：

第一部分输入参数：展示所有你的输入参数，以便查询输入错误

第二部分提示信息：告诉你，哪一个备份文件会被迁移到哪个实例的哪一个数据库 如下的一个实例的输出信息：

  1. *********************Input variables*************************************
  2. access_key_id = LTAIQazXKPRwwErT
  3. access_key_secret = BMkIUhroubQOLpOMqfA09IKlqp4G2k
  4. rds_instance_id = rm-2zesz5774ud8s71i5
  5. oss_endpoint = oss-cn-beijing.aliyuncs.com
  6. oss_bucket = atp-test-on-ecs
  7. directory = Migration/OPENAPIDemo/
  8. ************************************************************************
  10. *********************Migration requests**********************************
  12. --0. [Migration/OPENAPIDemo/TestCollation_FULL_20180523225534.bak] migrate to your RDS: [rm-2zesz5774ud8s71i5] and the database name will be: [TestCollation].
  13. {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'}
  14. --I'm sleeping for 2 seconds....
  16. --1. [Migration/OPENAPIDemo/TestMigration_FULL_20180523225534.bak] migrate to your RDS: [rm-2zesz5774ud8s71i5] and the database name will be: [TestMigration].
  17. {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'}
  18. --I'm sleeping for 2 seconds....
  20. --2. [Migration/OPENAPIDemo/testdb_FULL_20180523225534.bak] migrate to your RDS: [rm-2zesz5774ud8s71i5] and the database name will be: [testdb].
  21. {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'}
  22. --I'm sleeping for 2 seconds....

利用本篇文章，我们可以轻松实现用户线下或者ECS自建的SQL Server实例级别数据库一键迁移上云，以此来极大的提高迁移上云效率，简化操作，大大提升了用户迁移上云体验。

参考链接