A Lap Around SQL Azure Data Sync - Step By Step Example

SQL Azure Data Sync is a cloud-based service, hosted on Microsoft SQL Azure, which allows many SQL Azure databases to stay consistent with one another. This capability is ideal for a project I’m currently working on where the users need low latency data access across an entire company but they are geographically separated by great distances with one office in USA and one in Europe. SQL Azure Data Sync allows them to have consistently synchronized data physically close to both locations.

SQL Azure Data Sync is super simple to setup (try saying that five times fast). In this article, I’ll show you how to implement a data synchronization between two databases on the same logical SQL Azure server. We’ll be implementing the design in the diagram below.

As of this writing (AOTW), Microsoft is literally giving SQL Azure away (Web Edition – 1GB), so you really have no excuse on why you haven’t signed up yet.

STEP 2 – Create Two Databases OK, here’s where you might get charged. For this example, we’ll create two databases that will sync with each other, and you might get charged when you add the second database. When we're finished, you may want to delete one or both databases to keep costs down. Database usage is metered per day, so your bill should be less than $1, but I haven’t confirmed that.

There are a couple of things to notice about this script. One, each table in SQL Azure needs a clustered index. I’m creating one by specifying a primary key in the create table statement. AOTW, SQL Azure will allow you to create a table without a clustered index, but it won’t allow you to insert data into it, so there’s no reason to bother to even create the heap table. Two, I like the insert syntax where we can insert multiple rows after the VALUES keyword. That’s not SQL Azure specific, I just think it’s cool.

We have two databases, one filled with tables and data and one empty. Let’s fix that and get to the meat of this demo.

c) The first time you go to this site, you’ll have to agree to a license agreement.

d) Click “Add New”.

e) Name the Sync Group "LibrarySync". I don’t know the limit of how long this name can be, but I’ve thrown in a lot of text in there and it took it. I wonder if it’s varchar(max). Then click “Next”.

f) Register your server by typing in your server name, user name, and password. Notice how in red it says “Your credentials will be stored in an encrypted format.” This is good news because it saves your credentials when registering other databases on the same server. g) Then click “Add Hub”. The Hub database is similar to a publishing database in replication. For instance, it will win if there are any update conflicts.

The order you select the tables in this screen is the same order that the tables will be replicated. Remember that we have a foreign key constraint, so it’s real important that we add the authors table before we add the titles table. Click “Finish".

k) OK, now it seems like you’re done, but you’re not. Click on “LibrarySync” and then click “Schedule Sync”. Notice the options you have for scheduling synchronization. You can sync hourly, daily, etc. If you choose hourly, the “Minute” dropdown does not let you schedule at a minute interval, rather it allows you to choose the minute after the hour that the sync will begin. Click “OK”.

l) Technically, your sync is ready to go, but click “Sync Now” and wait a minute or two so we can examine the changes.

Step 5 – Examine the Changes a) Notice the library2 database has all the schema and data from library1. It also has some other things that SQL Azure Data Sync added for us. BAM! We did it!

c) Each user table gets its own tracking table. For instance, we have authors_tracking and titles_tracking. This tells SQL Azure Data Sync which records need to be updated on the other members of the sync group. Notice how this is not an auditing tool like Change Data Capture. It works more like Change Tracking in SQL Server 2008. You won’t get all the changes that led up to the final state of the data.

d) Each user table gets three triggers that are used to keep the databases consistent.

e) There are many stored procedures added to both databases for the same reason. Feel free to poke around and examine the triggers and stored procedures. I found them to be cleanly written. I like how they’re using the MERGE statement, introduced in SQL Server 2008.

f) Feel free to add a record to Library2, and click “Sync Now”. You’ll see it in Library1 in no time, thus proving that the synchronization is indeed bi-directional.

g) On the SQL Azure Data Sync Page, check out the synchronization log by clicking on the Dashboard button.

Final Thoughts At PDC 2010, Microsoft announced that this service will extend to on-premise SQL Servers. They demo’d how it’s done through an agent that’s installed on the on-premise SQL Server. It should be available to use in CTP 2, which is not publicly available AOTW.

Also, although the initial snapshot pushed the schema down to the library2 database, it will not keep schema in sync without tearing down the sync group and rebuilding it. I recommend you finalize your schema before setting up the sync group.

This is built on the Microsoft Sync Framework. I believe we’ll be seeing this used to synchronize all sorts of data from all sorts of data sources. I think it’s worth learning and I hope this gives you a fair introduction to the technology. Remember to delete at least one of your library databases to keep from being charged. Also, delete your data sync. Good luck!

Ike Ellis is the Lead SQL Instructor and SQL Course Author for DevelopMentor. He first worked with Microsoft SQL Server in 1997. Ike started as an independent consultant and trainer in September, 2000. Recent projects include a planning and financial solution used by a major retailer, a SQL Server Analysis Services project for a top ten university, an attorney scorecard tool used by the oldest intellectual property law firm in the US, SQL performance tuning for a medical imaging company, and an enrollment and retention tool used by an online university.

Ike has been Microsoft Certified since the beginning. He currently holds the MCDBA, MCSE, MCSD, MCNE, and MCT certifications. Ike loves consulting, loves technology, and thoroughly enjoys teaching. He’s been doing all three since 1996.

Ike is a popular code camp and user group speaker. In addition to his responsibilities at EllisTeam, he is the current chairperson for the Tech Immersion Group of the San Diego .NET User’s Group. He also volunteers his time with SQLPass and the San Diego .NET Developer’s Group.

About DevelopMentor

DevelopMentor provides solutions for all professionals involved in
the life cycle of software projects.