Demo Transcript: A First Look at SQL Server 2012 (Part 4 - SQL Azure Data Sync) - TechNet Articles - United States (English)

SQL11UPD00-TSCRIPT-06

This wiki is a transcript of a previously recorded video.

Using SQL Azure Data Sync to synchronize an hybrid IT environment

View Previous Demo Transcript: A First Look at SQL Server 2012 (Part 3 - SQL Server Data Tools) (SQL11UPD00-TSCRIPT-05)

Hi welcome back. I'm Roger Doherty, and this is part four of our four part demo exploring a lot of the exciting new capabilities in SQL Server 2012. In this demo we're going to talk about Hybrid IT, and how you can use Data Sync to synchronize data between various on-premise databases as well as cloud databases. So let's jump in.

In the previous demo we finished up replatforming our product catalog maintenance web application in the cloud and we showed how we took the web tier, the data tier and the reporting tier, migrated all that stuff up to Windows Azure and got a functioning version of our app. What we are going to do now is take a look a a second deployment of that same app that we've set up to demonstrate a capability called Data Sync. The first thing I want to show you is the synchronization topology configuration that we've set up in Data Sync. So Data Sync uses a hub-and-spoke approach for synchronizing data, and what we have here is a master database where our web app is actually managing that product catalog information in SQL Azure. So you always have to have a hub. Then you can set up a variety of spokes, including other SQL Azure databases as well as on-premise SQL Server databases as you see here.

If we take a look at what's required to do this, there is a Data Sync Agent that you run on your on-premise SQL Server installation to have it cooperate in these synchronization activities. So I register the thing into the topology and then you can publish data sets back to it. So what this is going to let us do is make changes here to our hub and push those changes out to our spokes through regularly scheduled or manual synchronization operations. So let's see if the application is working.

Let's first go out and connect to our second deployment and pop open our trusty product catalog management system. Let's pump in some new data here. We'll go ahead and add our Kinect Chair again, and we'll use the same image that we've been using for the other demos. Here we've gone ahead and added a new row to our product catalog on our hub SQL Azure database.

To get this out to all of our spokes, including the other SQL Azure database and the on-premise SQL Server database, what we are going to do is switch back to the Windows Azure Management Portal and pull up our Data Sync topology. We are going to initiate a manual synchronization operation. Right now you can see we don't have a scheduled sync set up. We will just pull down the ribbon here and click Sync Now and get some real time information about the status of that synchronization right here in the management portal. You can see we are in a synchronizing state, and we get some feedback about how that sync operation is going as we push that data out to our other SQL Azure database and to our on-premise SQL Server database. So the sync operation succeeded. We made the change here in our hub and that new row should now be out in the other SQL Azure database and in our on-premise SQL Server database.

So let's switch over to Management Studio and see how we've moved this data back into our on-premise SQL Server. So here's a query that's going to show me all the most recent activity that has happened through SQL Azure Data Sync. It looks like the last update happened here on March the 23rd, so if we re-run this query we should get a new row at the top here. And here is some activity that just occurred on March the 26th. If I want to look at the specific change that occurred, I'm going to run this second query and here is the last sync operation that happened on March 23rd. If I re-run this query, we'll see that the latest change happened on the 26th was our Kinect Chair that was added. This data has now been synchronized back to our on-premise database where we can use it in other applications, BI solutions, or whatever it is we need.

So what we've seen here is how SQL Azure, SQL Server and Data Sync technology make it possible for you to deliver on Hybrid IT, and to move your data very seamlessly between the cloud, your on-premise environment, and back again.