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:
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.
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.
Do you have questions, concerns, comments? Post them below and we will try to address them.