Lesson Learned #122: Checks to perform on your database before using Data Sync?

[Jose_Manuel_Jurado]

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
      • CREATE USER AzureSync WITH PASSWORD='x' , DEFAULT_SCHEMA=AzureSync
      • 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.
  • CREATE USER AzureSync WITH PASSWORD='x' 
  • 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

Enjoy!

-------------------------------------------------------

-- 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'

ELSE

     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 

WHILE @@FETCH_STATUS = 0 

BEGIN 

    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'

       ELSE

           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'

       ELSE

           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 

END  

CLOSE TablesCursor; 

DEALLOCATE 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'

ELSE

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

-----------

-- Review if you have permissions to DataSync Schema

-----------

IF HAS_PERMS_BY_NAME('DATASYNC', 'SCHEMA', 'CONTROL')=1 

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

ELSE

     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'

ELSE

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