Partial Synchronization with Data Sync Service

[This article was contributed by the SQL Azure team.]

In this previous blog post, I covered the basics of Data Sync Service for SQL Azure. In this post I will talk about a scenario where partial table access is provided to a superset of users for PowerPivot reporting.

There is a common enterprise scenario where you would like to expose certain tables to a wider user base so that they can access the table for reporting. At the same time you don’t want to give them access to your main database because you are unsure of the workload that those users would place against it. With an on premise database, one way to handle this is using replication to send data to a second database server where the users have read-only access.

With SQL Azure you can perform the same functionality using Data Sync Services. By using Data Sync Service for SQL Azure, you can synchronize the same data to multiple databases increasing your performance and scaling out for additional workloads.

To create a member database in SQL Azure for reporting you need to:

• Create a synchronization set on Data Sync Services for SQL Azure that contains your hub database (the source) and a member database (the reporting database) with a subset of the hub tables.
• Synchronize the member database. Even though the SQL Azure administrator can read and write to the member database, writing to it might not make sense. It is just for reporting. For more information about bi-directional synchronization see my previous blog post.
• Give read-only permissions to a superset of users on the member database that need to do the reporting. More information about setting permissions can be found in this blog post.
• Instruct your users to use a tool like PowerPivot to connect to the member database for their reporting. More information about connecting via PowerPivot can be found here.
• Schedule synchronization with Data Sync Services for SQL Azure so that the member database gets regular updates, giving the reporting users accurate data on a schedule.

Managing Relationships

As noted in the previous blog post, the foreign key constraints are not transferred from the hub database to the member database. Because of this the PowerPivot users running on the member database will have to map their own relationships in PowerPivot to associate the tables together. For more about how to do create relationships in PowerPivot see this blog post.

Data Centers

The member database doesn’t need to exist in the same datacenter as the hub database. Which means you can create a “Data Distribution Network” using Data Sync Services for SQL Azure. The idea is to push your data closer to the end user’s by synchronizing it to a data center near them. This will allow them to import the data into PowerPivot faster.

Summary

Do you have questions, concerns, comments? Post them below and we will try to address them.