I’ve just completed a basic implementation using Windows Azure SQL Data Sync that I will be using in a demo for an upcoming course. This is a basic walkthrough that you should be able to follow if you want to get to grips with the new SQL Data Sync functionality in Windows Azure.

The walkthrough is based on the SQL Data Sync August 2012 – Service Update, changes in future versions are possible.

Scenario

The scenario for this walkthrough is shown in the diagram below.

Adventure Works has an existing on-premise SQL database that contains its business information. They plan to create an on-line store application hosted in Windows Azure. In order to keep the product information in the on-line store database up to date, SQL data sync will be used to coordinate the synchronization between the on-premise database and the product database hosted in SQL Azure.

Existing Implementation

The existing implementation consists of a slightly modified version of the Adventureworks2012 database for Windows Azure. The sample database can be downloaded here: http://msftdbprodsamples.codeplex.com/releases/view/37304.

The database has been modified to replace the custom defined data types, which are currently not supported in Data Sync, with regular SQL data types. The modified code to create the Product table is shown below.

CREATE TABLE [Production].[Product](

[ProductID] [int] IDENTITY(1,1) NOT NULL,

[Name] nvarchar(50) NOT NULL,

[ProductNumber] [nvarchar](25) NOT NULL,

[MakeFlag] bit NOT NULL,

[FinishedGoodsFlag] bit NOT NULL,

[Color] [nvarchar](15) NULL,

[SafetyStockLevel] [smallint] NOT NULL,

[ReorderPoint] [smallint] NOT NULL,

[StandardCost] [money] NOT NULL,

[ListPrice] [money] NOT NULL,

[Size] [nvarchar](5) NULL,

[SizeUnitMeasureCode] [nchar](3) NULL,

[WeightUnitMeasureCode] [nchar](3) NULL,

[Weight] [decimal](8, 2) NULL,

[DaysToManufacture] [int] NOT NULL,

[ProductLine] [nchar](2) NULL,

[Class] [nchar](2) NULL,

[Style] [nchar](2) NULL,

[ProductSubcategoryID] [int] NULL,

[ProductModelID] [int] NULL,

[SellStartDate] [datetime] NOT NULL,

[SellEndDate] [datetime] NULL,

[DiscontinuedDate] [datetime] NULL,

[rowguid] [uniqueidentifier] NOT NULL,

[ModifiedDate] [datetime] NOT NULL,

CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED

(

[ProductID] ASC

)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)

)

With the changes made to AdventureWorks2012ForSQLAzure_Schema.sql the CreateAdventureWorksForSQLAzure.cmd is run specifying local server and credentials to create and populate the database on the local instance of SQL Server.

A very WPF basic line-of-business (LOB) application is created in WPF to allow product data to be modified. The XMLT for the min window is shown here:

<Window x:Class=”ProductEditor.MainWindow”

xmlns=”http://schemas.microsoft.com/winfx/2006/xaml/presentation”

xmlns:x=”http://schemas.microsoft.com/winfx/2006/xaml”

Title=”MainWindow” Height=”350″ Width=”525″>

</DataGrid>

</StackPanel>

</Window>

The code-behind here:

public partial class MainWindow : Window

{

AdventureWorks2012Entities m_Entities;

public MainWindow()

{

m_Entities = new AdventureWorks2012Entities();

InitializeComponent();

List<Product> prods =

(from p in m_Entities.Products where p.ListPrice > 0

select p).ToList<Product>();

dgdProducts.ItemsSource = prods;

}

private void btnUpdate_Click(object sender, RoutedEventArgs e)

{

m_Entities.SaveChanges();

}

The application uses an entity framework model to connect to the on-premise Adventureworks2012 database. A screenshot of the application is shown below.

Creating Windows Azure SQL Databases

The first step in creating the cloud-based online store is to create the databases that will be used for the online store and the data synchronization. The following two 1 GB web databases are created using the Windows Azure Portal.

%u00b7 SyncHubDb – A database to act as the hub database for SQL Data Sync.

%u00b7 WebStoreDb – A database for the online web store application.

Both the databases are creates as the 1G Web databases.

Creating a Sync Group

With the SQL Databases created in Windows Azure, the next task is to use SQL Data Sync to define a sync group to synchronize the produce data.

The first step is provision a SQL Data Sync server in an appropriate region. This is currently done using the old Azure portal. Click on the Data Sync button, and then click Provision, select the account, and specify a region, its best to use the same region where your Windows Azure SQL Databases are located.

Once the Data Sync server has been provisioned, a new sync group can be created. With Sync Groups selected, click Create.

Enter the name for the sync group, and then click on the icon for the Sync Hub database.

Enter the database details for the Sync Hub database and click Add.

All data synchronization that is processed by this sync group will pass through the sync hub database, which must be hosted in Windows Azure SQL Database.

Adding an On-Premise Database

In order to use SQL Data Sync with an on-premise database you will need to install and configure Microsoft SQL Data Sync Agent on your local SQL server and register the database with the service.

Click on the icon to add an on-premise database and select Add a new SQL database to the sync group, you can also specify the Sync Direction here, I left the default of Bi-Directional selected.

If you don’t have an agent configured, select Install a new Agent.

Download and install the agent, and then enter a name and generate an agent key.

Start Microsoft SQL Data Sync Agent from the start menu and set the agent key that was generated in the portal. This will open a management console for the on-premise service.

Click on Register, and then select the appropriate database on the local SQL server, and click Save.

You should see that the database is reachable.

Back in the portal, click Next, then Get Database List, select the database, and click Finish.

The on-premise database will now be connected to the Data Sync server in Windows Azure.

Configuring the Sync Schedule

The schedule for data synchronization can now be configured, along with the conflict resolution options, the time interval must be between 5 minutes and one month. I selected a 5 minute interval and Client Wins. The short time interval is good for demo purposes.

Setting Client Wins for conflict resolution means that the on-premise database will win if there are any conflicts with the data synchronization.

Defining the Data Set

The next step is to define the data that will be synchronized by the sync group. In this scenario, only the Product table in the Production schema will be synchronized. To do this, select the AdventureWorks2012 database, and then select the Products table, ensuring all columns are selected. Note that some of the tables contain data types that do not meet the schema requirements for synchronization; this is why the data types in the Product table were modified.

Now the data set has been selected, the sync group can be deployed. Click the Deploy button to do this.

The Sync Group will be provisioned and the first data synchronization will run. After a few seconds the status of the two databases should transition to good.

Opening the SyncHubDb in the Windows Azure portal shows the tables that have been created. The Production.Product table is present, with the 504 rows of product data. There are also a number of tables that are used by SyncFramework to manage the synchronization process.

Selecting data from the Production.Product table shows that the product data is now present in the SyncHubDb database. This was synchronized from the on-premise database when the Sync Group was provisioned. The synchronization will run every 5 minutes to synchronize any changes between the Product tables in both databases.

Adding a Windows Azure SQL Database

The next step is to add the WebStoreDb database to the sync group. This is done in a similar way to adding the SyncHubDb database. Click on the Add Windows Azure SQL Database icon and specify the WebStoreDb database details. With this database the data synchronization will be one directional, data will be synchronized from the hub to the WebStoreDb database, but not from the WebStoreDb database to the sync hub.

Deploy the Sync Group to save the changes, after a while the provisioning and synchronization will complete, and the topology will be as follows.

Clicking on the Log Viewer icon will show logs from the synchronization process. This can be used to verify that synchronization is taking place, and to diagnose any errors with the synchronization process.

Examining the WebStoreDb database shows that the product data has successfully been synchronized. The synchronization will run using the 5 minute schedule to ensure that any changes in the on-premise database are updated in the on-line store.

Creating the Online Store Application

The on-line store application is a simple ASP.net web forms application that uses entity framework to data-bind to the products table in the WebStoreDb database.

The default page has been modified to use a Repeater control to display product information.

<%@ Page Title=”Home Page” Language=”C#” MasterPageFile=”~/Site.Master” AutoEventWireup=”true” CodeBehind=”Default.aspx.cs” Inherits=”AdventureWorksStore._Default” %>

<asp:Content runat=”server” ID=”FeaturedContent” ContentPlaceHolderID=”FeaturedContent”>

<h1>Adventure Works Online Store</h1>

</hgroup>

<h2>Select * from our Products…</h2>

</div>

</section>

</asp:Content>

<asp:Content runat=”server” ID=”BodyContent” ContentPlaceHolderID=”MainContent”>

<asp:Repeater ID=”rptProducts” runat=”server”>

</HeaderTemplate>

<div>

</div>

</ItemTemplate>

</asp:Repeater>

</asp:Content>

The code behind file will use entity framework to load the product data and data-bind it to the Repeater.

public partial class _Default : Page

{

protected void Page_Load(object sender, EventArgs e)

{

AdventureWorks2012Entities ents = new AdventureWorks2012Entities();

List<Product> products =

(from p in ents.Products where p.ListPrice > 0 select p).ToList<Product>();

rptProducts.DataSource = products;

rptProducts.DataBind();

}

The connection string is modified to use the WebStoreDb database hosted in Windows Azure SQL Database.

<!–<add name=”AdventureWorks2012Entities” connectionString=”metadata=res://*/ProductsModel.csdl|res://*/ProductsModel.ssdl|res://*/ProductsModel.msl;provider=System.Data.SqlClient;provider connection string="data source=win7base;initial catalog=AdventureWorks2012;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"” providerName=”System.Data.EntityClient” />–>

</connectionStrings>

The application is deployed as a Windows Azure Web Site, and tested. The default page successfully displays product information.

Testing Data Synchronization & Scheduling

In order to test that the data synchronization and scheduling are working correctly, the WPF LOB application will be used to make some changes to the products data. In this test the first three products are put on special offer, and the changes updated in the on-premise database.

After a few minutes the changes have been successfully synchronized with the WbStoreDb database, and the special offer products are viewable in the online store website.

The log files can be viewed to see the results of the synchronization operations.

From the Adventureworks2012 database to the SyncHubDb database:

Sync completed successfully in 17.27 seconds.

Upload: 3 changes applied

Download: 0 changes applied

For more information, provide tracing id ’cd32f136-c1aa-4cdc-a220-5568b897ce14’ to customer support.

From the SyncHubDb database to the WebStoreDb database:

Sync completed successfully in 1.11 seconds.

Upload: 0 changes applied

Download: 3 changes applied

For more information, provide tracing id ’c229816d-eff7-4549-a94a-6bd5985b4777’ to customer support.

Conclusions

I’ve only taken a quick look at Windows Azure SQL Data Sync and it seems fairly intuitive to get up and running with it and get basic synchronization up and running. I’ve always been a believer that companies will “Extend to the Cloud” rather than “Move to the Cloud”, and will focus on creating hybrid applications, with some services hosted in the cloud, whilst maintaining existing on-premise applications. Windows Azure SQL Data Sync, like Windows Azure Service Bus, is a great technology for bridging the gap between on-premise applications and applications hosted in the cloud.

Windows Azure SQL Data Sync Walkthrough