基于Python的SQL Server数据库对象同步轻量级实现

缘由

日常工作中经常遇到类似的问题：把某个服务器上的某些指定的表同步到另外一台服务器。

类似需求用SSIS或者其他ETL工作很容易实现，比如用SSIS的话，但是会存在相当一部分反复的手工操作。

建源的数据库信息，目标的数据库信息，如果是多个表，需要一个一个地拉source和target，然后一个一个地mapping，然后运行实现数据同步。

不过很可能，这个workflow使用也就这么一次，就寿终正寝了，但是一样要浪费时间去做这个ETL。

快速数据同步实现

于是在想，可不可能快速实现类似需求，尽最大程度减少重复的手工操作？类似基于命令行的方式，简单快捷，不需要太多的手动操作。

于是就有了本文，基于Python（目的是顺便熟悉一下Python的语法），快速实现SQL Server的数据库之间的数据同步操作，

后面又稍微扩展了一下，可以实现不同服务器的数据库之间的表结构，表对应的数据，存储过程，函数，用户自定义类型表（user define table type）的同步

目前支持在两个SQL Server数据源之间：每次同步一张或者多张表/存储过程，也可以同步整个数据库的所有表/存储过程（以及表/存储过程依赖的其他数据库对象）。

需要考虑到一些基本的校验问题：在源服务器上，需要同步的对象是否存在，或者输入的对象是否存在于源服务器的数据库里。

在目标服务器上，对于表的同步：

1，表的存在依赖于schema，需要考虑到表的schema是否存在，如果不存在先在target库上创建表对应的schema

2，target表中是否有数据？如果有数据，是否以覆盖的方式执行

对于存储过程的同步：

1，类似于表，需要考虑存储过程的schema是否存在，如果不存在先在target库上创建表对应的schema

2，类似于表，arget数据库中是否已经存在对应的存储过程，是否以覆盖的方式执行

3，存储过程可能依赖于某些函数，用户自定义表变量等等，同步存储过程的时候需要先同步依赖的对象，这一点比较复杂，实现过程中遇到在很多很多的坑

可能存在对象A依赖于对象B，对象B依赖于对象C……，这里有点递归的意思

这一点导致了重构大量的代码，一开始都是直来直去的同步，无法实现这个逻辑，切实体会到代码的“单一职责”原则

参数说明

参数说明如下，大的包括三类：

1，源服务器信息（服务器地址，实例名，数据库名称，用户名，密码），没有用户名密码的情况下，使用windows身份认证模式

2，目标服务器信息（服务器地址，实例名，数据库名称，用户名，密码），没有用户名密码的情况下，使用windows身份认证模式

3，同步的对象类型以及对象

4，同步的对象在目标服务器上存在的情况下，是否强制覆盖

其实在同步数据的时候，也可以把需要同步的行数提取出来做参数，比较简单，这里暂时没有做。

比如需要快速搭建一个测试环境，需要同步所有的表结构和每个表的一部分数据即可。

表以及数据同步

表同步的原理是，创建目标表，遍历源数据的表，生成insert into values(***),(***),(***)格式的sql，然后插入目标数据库，这里大概步骤如下：

1，表依赖于schema，所以同步表之前先同步schema

2，强制覆盖的情况下，会drop掉目标表（如果存在的话），方式目标表与源表结构不一致，非强制覆盖的情况下，如果字段不一致，抛出异常

3，同步表结构，包括字段，索引，约束等等，但是无法支持外键，刻意去掉了外键，想想为什么？因吹斯汀。

4，需要筛选出来非计算列字段，insert语句只能是非计算列字段（又导致重构了部分代码）

5，转义处理，在拼凑SQL的时候，需要进行转义处理，否则会导致SQL语句错误，目前处理了字符串中的’字符，二进制字段，时间字段的转义或者其他处理

6，鉴于insert into values(***),(***),(***)语法上允许的最大值是1000，因此每生成1000条数据，就同步一次

使用如下参数，同步源数据库的三张表到目标数据库，因为这里是在本机命名实例下测试，因此实例名和端口号输入

python SyncDatabaseObject.py -s_h="127.0.0.1" -s_i="sql2017" -s_P=49744 -s_d=DB01 -t_h=127.0.0.1 -t_i="sql2017" -t_P=49744 -t_d="DB02" -obj_type="tab" -obj="[dbo].[table01],schema1.table01,schema2.table01" -f="Y"

执行同步的效果

说明：

1，如果输入obj_type="tab" 且-obj=为None的情况下，会同步源数据库中的所有表。

2，这个效率取决于机器性能和网络传输，本机测试的话，每秒中可以提交3到4次，也就是每秒钟可以提交3000~4000行左右的数据。

已知的问题：

1，当表的索引为filter index的时候，无法生成包含where条件的索引创建语句，那个看起来蛋疼的表结构导出语句，暂时没时间改它。

2，暂时不支持其他少用的类型字段，比如地理空间字段什么的。

存储过程对象的同步

存储过程同步的原理是，在源数据库上生成创建存储过程的语句，然后写入目标库，这里大概步骤如下：

1，存储过程依赖于schema，所以同步存储过程之前先同步schema（同表）

2，同步的过程会检查依赖对象，如果依赖其他对象，暂停当前对象同步，先同步依赖对象

3，重复第二步骤，直至完成

使用如下参数，同步源数据库的两个存储过程到目标数据库，因为这里是在本机命名实例下测试，因此实例名和端口号输入

python SyncDatabaseObject.py -s_h="127.0.0.1" -s_i="sql2017" -s_P=49744 -s_d=DB01 -t_h=127.0.0.1 -t_i="sql2017" -t_P=49744 -t_d="DB02" -obj_type="p" -obj="[dbo].[sp_test01],[dbo].[sp_test02]" -f="Y"

说明：测试要同步的存储过程之一为[dbo].[sp_test01]，它依赖于其他两个对象：dbo.table01和dbo.fn_test01()

create proc [dbo].[sp_test01]
as
begin
 set nocount on;
 delete from dbo.table01 where id = 1000
 select dbo.fn_test01()
end

而dbo.fn_test01()的如下，依赖于另外一个对象：dbo.table02

create function [dbo].[fn_test01]
(
)
RETURNS int
AS
BEGIN 
 declare @count int = 0
 select @count = count(1) from dbo.table02
 return @count
END

因此，这个测试的[dbo].[sp_test01]就依赖于其他对象，如果其依赖的对象不存在，同步的时候，仅仅同步这个存储过程本身，是没有意义的

同步某一个对象的依赖对象，使用过如下SQL查出来的，因此这里就层层深入，同步依赖对象。

这里就类似于同步A的时候，A依赖于B和C，然后停止同步A，先同步B和C，同步B或者C的时候，可能又依赖于其他对象，然后继续先同步其依赖对象。

效果如下

如果输入obj_type="sp" 且-obj=为None的情况下，会同步源数据库中的所有存储过程以及其依赖对象

已知的问题：

1，加密的存储过程或者函数是无法实现同步的，因为无法生成创建对象的脚本

1，table type的同步也是一个蛋疼的过程，目前支持，但是支持的并不好，原因是创建table type之前，先删除依赖于table type的对象，否则无法修改，创建。

特别说明

依赖对象的解决，还是比较蛋疼的

如果在默认schema为dbo的对象，在存储过程或者函数中没有写schema，使用 sys.dm_sql_referenced_entities这个系统函数是无法找到其依赖的对象的

但是奇葩的是可以找到schema的类型，却没有返回对象本身。

这一点导致在代码中层层深入，进行了长时间的debug，完全没有想到这个函数是这个鸟样子，因为这里找到依赖对象的类型，却找不到对象本身，次奥！！！

另外一种情况就是动态SQL了，无法使用 sys.dm_sql_referenced_entities这个系统函数找到其依赖的对象。

其他对象的同步

支持其他数据库对象的同步，比如function，table type等，因为可以在同步其他存储过程对象的时候附带的同步function，table type比较简单，不做过多说明。

需要改进的地方

1，代码结构优化，更加清晰和条例的结构（一开始用最直接简单粗暴的方式快速实现，后面重构了很多代码，现在自己看起来还有很多不舒服的痕迹）

2，数据同步的效率问题，对于多表的导入导出操作，依赖于单线程，多个大表导出串行的话，可能存在效率上的瓶颈，如何根据表的数据量，尽可能平均地分配多多个线程中，提升效率

3，更加友好清晰的异常提示以及日志记录，生成导出日志信息。

4，异构数据同步，MySQL《==》SQL Server《==》Oracle《==》PGSQL

代码端午节写好了，这几天抽空进行了一些测试以及bug fix，应该还潜在不少未知的bug，工作量比想象中的大的多了去了。

# -*- coding: utf-8 -*-
# !/usr/bin/env python3

__author__ = 'MSSQL123'
__date__ = '2019-06-07 09:36'

import os
import sys
import time
import datetime
import pymssql
from decimal import Decimal

usage = '''
         -----parameter explain-----
         
         source database parameter
         -s_h           : soure database host                                          ----- must require parameter
         -s_i           : soure database instace name                                  ----- default instance name MSSQL
         -s_d           : soure database name                                          ----- must require parameter
         -s_u           : soure database login                                         ----- default windows identifier
         -s_p           : soure database login password                                ----- must require when s_u is not null
         -s_P           : soure database instance port                                 ----- default port 1433
         
         target database parameter
         -t_h           : target database host                                         ----- must require parameter
         -t_i           : target database instace name                                 ----- default instance name MSSQL
         -t_d           : target database name                                         ----- must require parameter
         -t_u           : target database login                                        ----- default windows identifier
         -t_p           : target database login password                               ----- must require when s_u is not null
         -t_P           : target database instance port                                ----- default port 1433

         sync object parameter
         -obj_type      : table or sp or function or other databse object              ----- tab or sp or fn or tp
         -obj           : table|sp|function|type name                                  ----- whick table or sp sync
         
         overwirte parameter
         -f             : force overwirte target database object                           ----- F or N 


         --help: help document
         Example:
         python DataTransfer.py -s_h=127.0.0.1 -s_P=1433 -s_i="MSSQL" -s_d="DB01" -obj_type="tab"  -obj="dbo.t1,dbo.t2"  -t_h=127.0.0.1 -t_P=1433 -t_i="MSSQL" -t_d="DB02"  -f="Y"                   
         python DataTransfer.py -s_h=127.0.0.1 -s_P=1433 -s_i="MSSQL" -s_d="DB01" -obj_type="sp"  -obj="dbo.sp1,dbo.sp2" -t_h=127.0.0.1 -t_P=1433 -t_i="MSSQL" -t_d="DB02"  -f="Y"
         '''

class SyncDatabaseObject(object):
    # source databse
    s_h = None
    s_i = None
    s_P = None
    s_u = None
    s_p = None
    s_d = None

    # obj type
    s_obj_type = None
    # sync objects
    s_obj = None

    # target database
    t_h = None
    t_i = None
    t_P = None
    t_u = None
    t_p = None
    t_d = None

    f = None

    file_path = None

    def __init__(self, *args, **kwargs):
        for k, v in kwargs.items():
            setattr(self, k, v)



    # connect to sqlserver
    def get_connect(self, _h, _i, _P, _u, _p, _d):
        cursor = False
        try:
            if ( _u) and (_p):
                conn = pymssql.connect(host=_h,
                                       server=_i,
                                       port=_P,
                                       user = _u,
                                       password = _p,
                                       database=_d)
            else:
                conn = pymssql.connect(host=_h,
                                       server=_i,
                                       port=_P,
                                       database=_d)
            if (conn):
                return conn
        except:
            raise
        return conn

    # check connection
    def validated_connect(self, _h, _i, _P, _u,_p, _d):
        if not (self.get_connect(_h, _i, _P,_u,_p, _d)):
            print("connect to " + str(_h) + " failed,please check you parameter")
            exit(0)


    '''
    this is supposed to be a valid object name just like xxx_name,or dbo.xxx_name,or [schema].xxx_name or schema.[xxx_name]
    then transfer this kind of valid object name to format object name like [dbo].[xxx_name](give a default dbo schema name when no schema name)
    other format object name consider as unvalid,will be rasie error in process
    format object name
        1,xxx_name              ======> [dbo].[xxx_name]
        2,dbo.xxx_name          ======> [dbo].[xxx_name]
        3,[schema].xxx_name     ======> [dbo].[xxx_name]
        3,schema.xxx_name       ======> [schema].[xxx_name]
        4,[schema].[xxx_name]   ======> [schema].[xxx_name]
        5,[schema].[xxx_name    ======> rasie error format message
    '''

    @staticmethod
    def format_object_name(name):
        format_name = ""
        if ("." in name):
            schema_name = name[0:name.find(".")]
            object_name = name[name.find(".") + 1:]
            if not ("[" in schema_name):
                schema_name = "[" + schema_name + "]"
            if not ("[" in object_name):
                object_name = "[" + object_name + "]"
            format_name = schema_name + "." + object_name
        else:
            if ("[" in name):
                format_name = "[dbo]." + name
            else:
                format_name = "[dbo]." + "[" + name + "]"
        return format_name

    '''
    check user input object is a valicated object
    '''

    def exits_object(self, conn, name):
        conn = conn
        cursor_source = conn.cursor()
        # get object by name from source db
        sql_script = r'''select top 1 1 from
                                (
                                    select  concat(QUOTENAME(schema_name(schema_id)),'.',QUOTENAME(name)) as obj_name from sys.objects  
                                    union all
                                    select  concat(QUOTENAME(schema_name(schema_id)),'.',QUOTENAME(name)) as obj_name  from sys.type