Lesson Learned #122: Checks to perform on your database before using Data Sync?
Published Mar 11 2020 01:54 AM 2,082 Views

During these last days I've been working with some service requests related with DataSync based on some limitations of this feature has. 


Before using DataSync I would like to share with you this post that includes some checks to be performed before using Datasync. 


Following you could find some of these checks to be done:  


  • The user that connects to DataSync has access to more than 500 tables.
    • In this situation, you have to reduce this number because it is limitation of DataSync. You have two possible solutions:
      • Create a new schema, that basically, you need to follow up the next steps:
        • CREATE schema AzureSync
        • CREATE schema DataSync
        • GRANT CONTROL ON Schema::AzureSync To AzureSync
        • GRANT CONTROL ON Schema::DataSync To AzureSync
        • CREATE TABLE AzureSync.Table1 (ID Int primary key)
      • Create a new user and provided control to some specific tables.
        • CREATE USER Azuresync With Password='xxxxxx'
        • CREATE schema DataSync
        • GRANT CONTROL object::tablename to AzureSync.
  • The database has not enabled Snapshot isolation level.
    • You need to enable it, following this URL
  • The length of name of the tables/columns are more than 50 chars.
    • DataSync will create triggers joining table name plus GUID and other text, in this case, you could reach the maxium length of an object of 128 characters. 
  • The user that performs the provisioning process has not permissions to create triggers that DataSync needs to create/run in every table selected to be synced.
    • GRANT CONTROL ON object::tablename to AzureSync
  • It is needed to provision all the tables manually due to some non-clustered indexes that maintain the integrity referential has the same name. 
    • Our suggestion is that all the non-clustered indexes have an unique name. We found that when we are using as constraints to maintain the referential integrity between tables, DataSync creates a constraints and if the index has the same name you are going to have an error because there is not possible to have constraints with same name.
    • To provisioning manually the tables, basically, you need to script out the tables using SQL Server Management Studio and execute it in the target database.
  • The user has not CONTROL access to the schema DataSync.
    • We need to create the schema Datasync - CREATE schema DataSync
    • And grant the permission CONTROL to this schema - GRANT CONTROL ON Schema::DataSync To <UserName>
  • And finally, the collation of the destination and source are different.
    • We need that the source database and destination will have same collation. Remember that DataSync checks the structure of the tables of the different members.


In order to validate these checks, I created the following TSQL that perform these validations, all feedback and improvement are welcome:


  • User with access to mor than 500 tables.
  • Snapshot isolation level.
  • Name tables/columns with more than 50 chars.
  • Check permissions to DataSync Schema and tables in the source database.


Also, remember, that we have the following PowerShell script that you could run once DataSync has been created to review if everything is correct: https://github.com/microsoft/AzureSQLDataSyncHealthChecker





-- Script to validate my database to use DataSync



IF (SELECT ISNULL(COUNT(*),0) FROM sys.tables where sys.tables.is_ms_shipped=0)>=500 

     Print 'Check #1: Fail - You have more than 500 tables'


     Print 'Check #1: Success - You do not have the maximum tables to Sync'



-- Review if you have permissions to DataSync Schema


DECLARE @tablename sysname, @object_id bigint, @nTemporal int 


DECLARE TablesCursor CURSOR FOR SELECT Name, OBJECT_ID FROM sys.tables where sys.tables.is_ms_shipped=0 order by Name;


OPEN TablesCursor 

FETCH NEXT FROM TablesCursor INTO @tablename, @object_id 




    PRINT ' ' 

    PRINT 'Control #2: Table: ' + @tablename + '-- ' + CONVERT(varchar(200),@object_id)


    DECLARE PermissionsCursor CURSOR FOR SELECT TOP 1 grantee_principal_id

                                         FROM sys.database_permissions p

                                         WHERE major_id = @object_id and grantee_principal_id = USER_ID() AND permission_name='CONTROL'

    OPEN PermissionsCursor

    FETCH NEXT FROM PermissionsCursor INTO @nTemporal 

    IF @@FETCH_STATUS = 0  

        PRINT ' -----> Has enough permissions'


           PRINT ' -----> Fail - Has not enough permissions'


    CLOSE PermissionsCursor

    DEALLOCATE PermissionsCursor


       IF LEN(@TableName)>50 PRINT '  -----> Fail - The name of the table reached the maximum length'


    DECLARE ColsCursor CURSOR FOR SELECT TOP 1 object_id

                                         FROM sys.columns

                                         WHERE object_id = @object_id AND len(name)>50

    OPEN ColsCursor

    FETCH NEXT FROM ColsCursor INTO @nTemporal 

    IF @@FETCH_STATUS = 0  

        PRINT ' -----> Fail - The length of some column name of this table is reached the maximum allowed'


           PRINT ' -----> The length of some column name of this table is not reached the maximum allowed'


    CLOSE ColsCursor

    DEALLOCATE ColsCursor


    FETCH NEXT FROM TablesCursor INTO @tablename, @object_id 


CLOSE TablesCursor; 




-- Check if DataSync schema is created and has control permissions


IF (SELECT ISNULL(COUNT(*),0) FROM sys.schemas where name='DataSync')=1 

     Print 'Check #3: You have the schema DataSync created'


     Print 'Check #3: Fail - You do not have the schema DataSync created'




-- Review if you have permissions to DataSync Schema



     Print 'Check #4: You have control permission in DataSync schema'


     Print 'Check #4: You do not have control permission in DataSync Schema'



-- Review if you have permissions to DataSync Schema


IF (SELECT snapshot_isolation_state FROM sys.databases where name=db_name()) = 1 AND

   (SELECT is_read_committed_snapshot_on FROM sys.databases where name=db_name()) = 1

   Print 'Check #5: You have snapshot isolation level'


   Print 'Check #5: You dont have snapshot isolation level'


Version history
Last update:
‎Mar 11 2020 02:02 AM
Updated by: