Databases Reference
In-Depth Information
Following the Client Wins rule, the last write to the hub overwrote the previous change (“Chief Executive
Officer”) then propagated out “Chief Head Officer” to all member databases.
Data Sync Limitations
While SQL Data Sync is quite functional and very flexible, there are some limitations in the pre-release version. The
following is a list of the current limitations for SQL Data Sync.
•
Maximum number of SQL Data Sync Servers per subscription: 1
•
Maximum number of sync groups any database can belong to: 5
•
Filters per table: Up to 12 (optionally 13 if one is on the primary key column)
•
Database, table, schema, and column names: 50 characters per name
•
Tables in a sync group: 100
•
Columns in a table in a sync group: 1000
If you query the HumanResources.Employee table, you'll notice that it contains BusinessEntityID 1 to 290, well
below the filter of 10,000. Even though there is a PK/FK constraint on the hub database, why were records less than
10,000 synchronized in the HumanResources.Employee table?
The answer is that currently filtering is on individual tables. In this example the filter would need to be applied to
both tables. In all reality, the filter would be for records less than 10,000 and applied to both tables.
Additionally, in some cases this
might
mean denormalizing the schema of your database so that the value on
which you want to filter is available in each table.
Existing triggers on source tables, CHECK constraints, and indexes on XML-type columns are not provisioned.
Indexes are created only for the columns selected to be synchronized.
Data Sync Best Practices
A lot has been learned since the last release of this topic, so this section will focus on best practices learned over the
last couple of years. We will discuss topics that will help in the design and planning phase for SQL Data Sync. The key
is to keep in mind that SQL Data Sync is a global solution, allowing you to move data around the world.
Design Considerations
•
Avoid synchronization loops:
A synchronization loop results when there are circular
references within a sync group so that each change in one database is replicated through the
databases in the sync group circularly and endlessly. Sync loops degrade performance and
significantly increase costs. Synchronization loops can be avoided by not including circular
references within a database or table, or between two sync groups.
•
On-premises-to-cloud scenario:
Keep your hub database close to the greatest concentration
of the sync group's database traffic to minimize latency.
•
Cloud-to-cloud scenario:
When all the databases in a sync group are in the same data center,
the hub database should also be located in the same data center. When databases in a sync
group are in different data centers, the hub database should be located in the data center
where the majority of the traffic takes place.
