SQL Data Sync Feature - Trouble refreshing Schema between 2 SQL Azure DB's (Hub & Member DB)

Question

Agenda: We want to achieve a scenario where we should be able to sync all of the data pushed in our production SQL azure DB to Test SQL Azure DB [ Hosted under the same SQL server ] via "Sync To other Database" feature in SQL Azure.

Error: "Database object name with a '.' or ']' or '[' are not supported [![Error logs in Azure SQL Data Sync feature ]

**Limitations: **

• The names of objects (databases, tables, and columns) cannot contain the printable characters period (.), left square bracket ([), or right square bracket (]).
• Tables with same name but different schema (for example, dbo.customers and sales.customers) are not supported.

source: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-sync-data

Issues: in general almost all of the databases do have schema very similar to this dbo.customer_table etc. And our dBs too.

Ask:

Is there any hack we can use to be able to successfully register for the sync services ?

Thanks in Advance !

Answer 1

Table names with underscore are fine. Have you identified the problem maker? Database name is ok? How many tables with the forbidden chars?

There is no workaround for the same table name under different schemas.

Have you considered Geo-Replication feature? You can enable Geo-Replication to the same region.

You can also automate a daily database copy using Azure Automation.

Answer 2

With the following skript you can identify the tables or columns with a '.' or '[' or ']'

--Tables
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='NameDB'
and 
(
TABLE_NAME like '%.%' or
TABLE_NAME like '%[%' or
TABLE_NAME like '%]%'
)

--Columns
SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'
            ,*
FROM sys.columns c
JOIN sys.tables  t   ON c.object_id = t.object_id
WHERE
c.name like '%.%'
or c.name like '%[%'
or c.name like '%]%'
ORDER BY    TableName
            ,ColumnName

We were facing the same problems. We removed the sysadmin role of the SyncUser and restricted the rights of the schema only on the needed tables. Like this we excludet the problem causing tables. You can also DENY SELECT on specifig objects.

After you have loaded the schema in AzurePortal you can grant the your User the SysAdmin again. Otherwhise he can not create the needet schema wiht the needed helping tables. You can also grand permissions on the schema created by the Sync itself "DataSync" unter Security under your DataBase.

It worked out for us.