datasync

最近在负责公司部署在Azure的DB升级的过程中遇到一些问题，公司升级方式采用的是使用Data Sync同步DB数据到目的端，然后升级目的端数据。在Data Sync的时候经常会出错，查看异常发现是:

Database provisioning failed with the exception “Cannot insert the value NULL into column ‘provision_timestamp’, table ‘xxx.DataSync.provision_marker_dss’; column does not allow nulls. INSERT fails.The statement has been terminated.Inner exception: SqlException Error Code: -2146232060 - SqlError Number:515, Message: Cannot insert the value NULL into column ‘provision_timestamp’, table ‘xxx.DataSync.provision_marker_dss’; column does not allow nulls. INSERT fails. SqlError Number:3621, Message: The statement has been terminated. “ For more information, provide tracing ID ‘82c8420e-d184-4e83-8278-14a8f2cb3f00’ to customer support.

一般情况下只要删除所有相关的Data Sync Group就会将这些表清理掉，但有时因为某些原因会导致清理不掉，这个时候就不能进行Data Sync了，免费的功能果然问题多，没办法，只能自己动手删除了。

执行Data Sync，微软会在你的DB里面对应每张表创建Trigger、Procedure、和Schema为DataSync的表。

删除所有的Trigger

如果先删除Procedure，这时插入数据，触发器回去执行对应的存储过程，由于找不到而抛错，导致无法插入数据。

这里有两种方式：

  • 对DB执行下列语句，将结果复制出来，再对DB执行一次 :
SELECT 'DROP TRIGGER ' + name
FROM sysobjects
WHERE type = 'tr'
  • 直接对DB执行 :
DECLARE @Trigger VARCHAR (500)
DECLARE CUR CURSOR
FOR SELECT name FROM sysobjects WHERE type = 'tr'
OPEN CUR
FETCH NEXT FROM CUR INTO @Trigger
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC('DROP TRIGGER ' + @Trigger + '' )
    FETCH NEXT FROM CUR INTO @Trigger
END
CLOSE CUR
DEALLOCATE CUR

删除所有的Procedure

同理也有两种:

  • 对DB执行下列语句，将结果复制出来，再对DB执行一次 :
SELECT 'DROP PROCEDURE DATASYNC.[' + P.NAME + ']' FROM SYS.PROCEDURES P
INNER JOIN SYS. SCHEMAS S ON P.SCHEMA_ID = S.SCHEMA_ID
WHERE P.TYPE = 'P' AND S.NAME = 'DATASYNC' AND P.IS_MS_SHIPPED = 0 AND P.NAME NOT LIKE 'sp[_]%diagram%'
  • 直接对DB执行 :
DECLARE @PROCNAME VARCHAR (500)
DECLARE CUR CURSOR
FOR SELECT P.NAME FROM SYS.PROCEDURES P
       INNER JOIN SYS. SCHEMAS S ON P.SCHEMA_ID = S.SCHEMA_ID
       WHERE P.TYPE = 'P' AND S.NAME = 'DATASYNC' AND P.IS_MS_SHIPPED = 0 AND P.NAME NOT LIKE 'sp[_]%diagram%'
OPEN CUR
FETCH NEXT FROM CUR INTO @PROCNAME
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC('DROP PROCEDURE DATASYNC.[' + @PROCNAME + ']' )
    FETCH NEXT FROM CUR INTO @PROCNAME
END
CLOSE CUR
DEALLOCATE CUR

删除所有的Schema

  • 对DB执行下列语句，将结果复制出来，再对DB执行一次 :
SELECT 'DROP TABLE DataSync.[' +name + ']' FROM sys. tables WHERE schema_name( [schema_id] ) = 'DataSync'
  • 直接对DB执行 :
DECLARE @TableName VARCHAR (500)
DECLARE CUR CURSOR
FOR SELECT NAME FROM sys. tables WHERE schema_name( [schema_id] ) = 'DataSync'
OPEN CUR
FETCH NEXT FROM CUR INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC('DROP TABLE DataSync.[' + @TableName + ']' )
    FETCH NEXT FROM CUR INTO @TableName
END
CLOSE CUR
DEALLOCATE CUR