Microsoft Azure SQL Database Step by Step
Chapter 7. Microsoft Azure SQL Data Sync
—Leonard Lobel
The Microsoft Azure platform provides a special service called Microsoft Azure SQL Data Sync. You can use this service to automatically discover data changes made in one database and replicate those changes to another database (or to any number of other databases). In this chapter, we’ll begin with an overview of Microsoft Azure SQL Data Sync, and then dive into a series of procedures for you to follow that put this cloud service to use in a number of common scenarios.
Note
In this chapter, we refer to Microsoft Azure SQL Data Sync simply as SQL Data Sync. Furthermore, as mentioned in Chapter 1, “Getting started with Microsoft Azure SQL Database,” the term SQL Database refers specifically to Microsoft Azure SQL Database in the cloud, whereas the term SQL Server refers specifically to local (on-premises) SQL Server.
Getting to know SQL Data Sync
SQL Data Sync is (at the time of this writing) a free Microsoft Azure service that provides automatic data synchronization across a set of databases. There are many common scenarios in which you can benefit from such a service, including
Exporting data mastered on-premises to cloud applications
Importing data in the cloud to on-premises applications
Sharing data between applications running on premises and in the cloud
Sharing data between multiple locations
Scaling out using the cloud
In the sections that follow, we discuss these scenarios and explain how SQL Data Sync can be used to implement a solution for each one of them.
It is incredibly easy to configure and use SQL Data Sync. Everything happens through the Microsoft Azure portal. No local tools are needed. You can use the portal to specify the databases you want synchronized and the datasets within those databases (which tables and columns) to be synchronized. You can also schedule an interval of time that controls how often SQL Data Sync will synchronize the databases automatically, thus controlling how up to date those databases will be. The only time you need to install something locally is when configuring an on-premises SQL Server database for synchronization. This requires the installation of a small agent component, a lightweight Windows Service that registers local databases with SQL Data Sync.
The collection of databases to be synchronized (called reference databases) are defined within a sync group. The reference databases in a sync group can include any number of local (on-premises) SQL Server databases, any number of databases in the cloud (SQL Database), or any combination of on-premises and cloud databases.
Within the sync group, one reference database is designated as the hub and all the other reference databases act as spokes, in what can be viewed as a hub-and-spoke model. In our discussion, we will refer to the spokes as clients, because that is the terminology used by the SQL Data Sync service and documentation. In between the hub and each client, you can monitor and apply changes in one direction (from hub to client), the other direction (from client to hub), or bi-directionally (two-way). When two-way synchronization is enabled between a hub and multiple clients, data changes made to the hub are pushed out to all the clients. A data change in any individual client is first pushed to the hub, and then pushed out again to all the other clients. If the same data is changed in two places within a synchronization pass, you can control who “wins” by setting the conflict-resolution behavior for the sync group.
The only requirement in this model is that the hub database absolutely must be a SQL Database (that is, it must be a database in the cloud). An on-premises SQL Server database cannot function as the hub of a sync group. All the other reference databases in the sync group (the clients) can be either another SQL Database in the cloud or an on-premises SQL Server database.
Important
SQL Data Sync is provided as a “preview” release available to all Microsoft Azure subscribers, so support from Microsoft is not guaranteed. We normally recommend against using preview releases like SQL Data Sync for production applications (as Microsoft does), particularly because it is reasonable to expect that newer and more sophisticated replication services will emerge on Microsoft Azure in the longer term.
However, SQL Data Sync is currently the only synchronization service for SQL Database and SQL Server available from Microsoft on Microsoft Azure. It is also extremely easy to use and reliable, and it has been freely available as a preview release since 2007 (when the service was formerly called SQL Azure Data Sync). In fact, there are reports of customers enjoying great success with SQL Data Sync in production environments, and even (under special circumstances) receiving limited Microsoft support. But to reiterate, with preview release software, there are no guarantees. You must carefully consider all these facets before adopting SQL Data Sync and integrating it into your production solution.
Exporting data from SQL Server to SQL Database
SQL Data Sync gives you the ability to synchronize data between SQL Server on-premises and Microsoft Azure SQL Database in the cloud. This capability is ideal when you have existing applications and existing data residing on-premises, and new cloud-based applications are being developed that need to use some of that data—data that is effectively mastered exclusively on-premises.
In this scenario, Data Sync can be used to synchronize one-way, from the SQL Server client up to the SQL Database hub. (See Figure 7-1.) Changes made on-premises in SQL Server will be replicated automatically to SQL Database in the cloud. However, the cloud applications are not able to affect the on-premises SQL Server database in any way, because with one-way synchronization set up in this direction, SQL Data Sync will not monitor the SQL Database hub for changes that might be made in the cloud; thus, it will never modify the SQL Server client database.
FIGURE 7-1 One-way publishing of data mastered on-premises with SQL Server to SQL Database in the cloud
Importing data from SQL Database to SQL Server
In other cases, you have the exact opposite scenario. You might have cloud-based applications that are inserting, updating, and deleting data in a SQL Database, and those changes are required by applications running on-premises. For example, you can have on-premises Business Intelligence (BI) solutions that analyze data collected in the cloud and pulled down to a local SQL Server database by SQL Data Sync.
In this scenario, a one-way sync in the other direction (from the SQL Database hub down to the SQL Server client) is the requirement. (See Figure 7-2.) By changing the direction of the one-way synchronization, SQL Data Sync will monitor SQL Database only for changes made in the cloud and then push those changes into the on-premises SQL Server database. The on-premises applications are not able to affect the cloud database in any way.
FIGURE 7-2 One-way publishing of data mastered in the cloud with SQL Database to on-premises SQL Server
Sharing data between multiple locations
Yet still, in other scenarios, both on-premises and cloud-based applications operate over a shared database—in which case, full bi-directional (two-way) synchronization is needed. And to extend the scenario a bit further, let’s also introduce multiple on-premises locations. These could be restaurants, hotels, retail stores, branch offices, and so on. Figure 7-3 shows only two on-premises locations, although of course, there can be many more.
FIGURE 7-3 Two-way publishing of shared data between multiple locations via a centralized hub in the cloud
When you have multiple locations, each location runs its own set of applications and uses data in its own SQL Server database. In some cases, the requirement is to share data between the different SQL Server (on-premises) locations. That is, each location has some data that needs to be kept in sync so that it’s available in all the other locations (for example, a product catalog). In this case, SQL Data Sync uses the hub as a conduit through which data is synchronized bi-directionally. Changes are first replicated from the on-premises location to the hub, and then pushed back out to all the other on-premises locations. In this manner, SQL Data Sync can update each location with changes made in any other location, via the SQL Database hub in the cloud.
Another common scenario is to use SQL Database in the cloud as a location to aggregate (combine) the data from these multiple locations. Basically, you can use SQL Data Sync to pull the location-specific data from each location and aggregate it into a centralized SQL Database. Then you can create a cloud-based application that consumes the view of that aggregated data across those multiple locations. This is just one example of how SQL Data Sync can provide great insight by pulling distributed data together into a single SQL Database. The cloud-based application can even update data in the SQL Database (either shared data or location-specific data), causing SQL Data Sync to push those changes back down appropriately; the service will send shared data changes to all locations, and location-specific data changes to just the individual corresponding locations.
Scaling out
Finally, you can also synchronize between multiple databases in the cloud, as illustrated in Figure 7-4. This can work in conjunction with the previously described scenarios by including multiple on-premises locations, as depicted on the left side of the diagram. However, this is entirely optional—a local SQL Server database is never required with SQL Data Sync.
FIGURE 7-4 Synchronizing multiple cloud databases across multiple Microsoft Azure data centers
By maintaining multiple copies of the same data in the cloud (both within a single data center as well as across multiple data centers), you can scale out in significant ways. For example, you can generate one or more replicas of your primary transactional database (often termed the OLTPdatabase, for online transactional processing), keeping both the OLTP and replica databases hosted within the same data center (as depicted by the two SQL Database instances in the center of the diagram). Then you can run your analysis and reports against a replica, rather than the “live” OLTP database.
Taking this approach greatly reduces the demand on your primary transactional database, which needs to remain responsive to data-entry requests at a fast and furious rate. The OLTP database is primarily focused on (and carefully tuned for) inserting, updating, and deleting small amounts of data within atomic transactions (that is, brief series of operations that succeed or fail as a whole). Although there are also read requests, they are relatively few and typically involve small amounts of data. Because reporting requests tend to involve intensive, potentially long-running read-only queries, they typically have a negative (often severely negative) impact when executed directly on the OLTP database. By comparison, once the OLTP and replica databases are fully synchronized, keeping them in sync has a relatively small impact on the OLTP database as it is queried for fresh changes that have occurred in between each synchronization pass. So this problem is easily remedied by replicating the OLTP database or databases to other databases and then running report and analysis queries against only the replicas.
One of the great things about the Microsoft Azure platform is that Microsoft maintains data centers in multiple regions around the world. This infrastructure makes it easy to geographically locate your own applications around the world as well, if and when the need arises to scale out to that level. Thus, you can keep applications (and their associated data) physically closer to the users consuming those applications. In this case, SQL Data Sync can be used to keep replicated databases (or designated parts of replicated databases) in sync so that the same data (or the same subset of data) is available to all instances of the application worldwide, as depicted on the right side of Figure 7-4.
Important
Although the SQL Data Sync service itself is free at the time we are writing this, your Microsoft Azure subscription will most definitely incur normal charges for data transferred between data centers by the service. (See Chapter 2, “Configuration and pricing,” for more information.) Furthermore, there will be a performance hit as a result of all the additional network traffic crossing data centers. For these reasons, if possible, you can and should limit synchronization to include only that portion of your database (the minimum number of tables and columns) that absolutely needs to be available globally.
About Microsoft Azure Traffic Manager
SQL Data Sync makes it easy to synchronize the same database for applications hosted in multiple Microsoft Azure data centers, but properly routing users to the application running in the data center closest to them is a separate matter. Although routing is a topic that falls beyond the scope of this chapter, you should be aware that Microsoft Azure provides a service called Traffic Manager that is specifically designed to handle this concern.
Traffic Manager provides load-balancing capabilities for your applications running on Microsoft Azure in multiple data centers. (Note that this is separate from, and in addition to, the load balancing you can achieve within an individual data center using virtual machines.) It essentially takes the entire collection of Microsoft Azure data centers and places it behind a Domain Name System (DNS) capable of handling the necessary routing. Thus, you can deploy applications to run in multiple data centers, using SQL Data Sync to replicate a copy of the database for each application instance in each data center, and then use Traffic Manager to intelligently direct users to the application instance running in the data center nearest to them, based on their physical location.
Although it’s not required to use Traffic Manager in conjunction with SQL Data Sync, it is a highly recommended option. There are solutions available from other companies that similarly use DNS services to route users to the right place based on their location, but such solutions are often quite expensive and can be very complex to implement correctly. Conversely, Traffic Manager is relatively easy to use and accessible as part of the Microsoft Azure platform. And, at the time of this writing, the service is still in preview and available to be used free of charge.
You can learn more about Microsoft Azure Traffic Manager by visiting http://www.windowsazure.com/en-us/services/traffic-manager.
Creating the SQL Database
To get started, you will use SQL Server Data Tools (SSDT) inside Visual Studio to quickly create a WineCloudDb database similar to the one you’ve been using throughout this book. The procedure assumes you already created a SQL Database server that you can access through its firewall on which you can create this database, as explained in Chapter 1. If you do not already have a server, it’s easy to create one now and open its firewall to your local machine using the Microsoft Azure management portal. (See “Creating a server” in Chapter 1.)
As shown in Listing 7-1, WineCloudDb is a simple database that has Wine and Customer tables and a few rows of data.
Note
Because this procedure does nothing more than create a database and execute a script, you can certainly follow similar steps with SQL Server Management Studio (SSMS) instead of Visual Studio and SSDT. Likewise, you can choose instead to use the Microsoft Azure management portal to create the database and the SQL Database management portal to run the script. It’s largely a matter of preference, so you should use whichever tool is readily available and most convenient for you.
LISTING 7-1 Script to create the WineCloudDb database
CREATE TABLE Wine(
WineId int IDENTITY PRIMARY KEY,
Name nvarchar(50) NOT NULL,
Category nvarchar(15) NOT NULL,
Year int);
CREATE TABLE Customer(
CustomerId int IDENTITY PRIMARY KEY,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
FavoriteWineId int,
CONSTRAINT FK_Customer_Wine FOREIGN KEY (FavoriteWineId) REFERENCES Wine(WineId));
SET IDENTITY_INSERT Wine ON;
INSERT Wine (WineId, Name, Category, Year) VALUES
(1, 'Chateau Penin', 'Bordeaux', 2008),
(2, 'McLaren Valley', 'Cabernet', 2005),
(3, 'Mendoza', 'Merlot', 2010),
(4, 'Valle Central', 'Merlot', 2009);
SET IDENTITY_INSERT Wine OFF;
SET IDENTITY_INSERT Customer ON;
INSERT Customer (CustomerId, FirstName, LastName, FavoriteWineId) VALUES
(1, 'Jeff', 'Hay', 4),
(2, 'Mark', 'Hanson', 3),
(3, 'Jeff', 'Phillips', 2);
SET IDENTITY_INSERT Customer OFF;
To create the WineCloudDb database using Visual Studio 2013, follow these steps:
1. Start Visual Studio 2013.
2. If the SQL Server Object Explorer is not visible, click the VIEW menu and choose SQL Server Object Explorer.
3. In the SQL Server Object Explorer, right-click SQL Server and choose Add SQL Server to display the familiar Connect To Server dialog.
4. In the Connect To Server dialog, do the following:
a. For Server Name, type <servername>.database.windows.net. This is the fully qualified name to the SQL Database server, where <servername> should be replaced by the name assigned to your server.
b. For Authentication, select SQL Server Authentication from the drop-down list. (SQL Database does not support Windows Authentication.)
c. For Login and Password, type the user name and password you assigned the server when you created it.
d. Click the Connect button. The server now appears as a collapsed node in the SQL Server Object Explorer.
5. Expand the server node in the SQL Server Object Explorer.
6. Expand the server’s Databases node.
7. If a previous version of WineCloudDb is present from work you did in an earlier chapter, delete it now:
a. Right-click the existing WineCloudDb database, and choose Delete.
b. Click OK to confirm.
8. Right-click the Databases node, and choose Add New Database.
9. Type WineCloudDb, and press Enter. The new database now appears in the SQL Server Object Explorer.
10. Right-click the WineCloudDb database, and choose New Query to open a new query window.
11. Type the code shown in Listing 7-1 into the query window (or paste it in from the listing file downloaded from the book’s companion website).
12. Press Ctrl+Shift+E to execute the script (or press the play button icon in the query window’s toolbar).
13. Close Visual Studio. (It isn’t necessary to save the script.)
Working with SQL Data Sync
The rest of this chapter contains procedures that guide you through the process of setting up a sync group and configuring SQL Data Sync for many of the different scenarios identified in the previous section. You will start by creating a simple sync group to replicate the WineCloudDbdatabase to another SQL Database on Microsoft Azure. Then you’ll move on to add a local on-premises SQL Server database to the sync group. In other procedures, you will also learn about conflict resolution and how to set an automated synchronization schedule.
Creating a sync group
At a minimum, a sync group requires one SQL Database (the hub) and at least one more SQL Database or on-premises SQL Server database to synchronize with. In the next few procedures, you will create an empty WineCloudDb database on a new SQL Database server, and then create a sync group with the populated WineCloudDb database (the hub) and the new empty one. You will configure for two-way synchronization, so that SQL Data Sync will first replicate from the hub to the empty database and then subsequently replicate data changes bi-directionally between both databases.
To avoid incurring charges to your Microsoft Azure subscription as you follow along with these procedures, you’ll create the new server in the same data center as the one currently hosting WineCloudDb. You will create the sync group in the same region as well, for the same reason. This is ideal for scaling out within a data center, but you can just as easily create the server in a different data center (and incur outgoing data-transfer charges between them) if you wanted to geographically disperse the databases and better service global users in closest proximity to their nearest available data center.
Indeed, for this exercise, you don’t even really need to create a new server at all; you could use the same server you’ve been using all along for WineCloudDb. SQL Data Sync will happily synchronize two SQL Databases on the same server. Nevertheless, you will still create another server. (Of course, you’d be using separate servers in separate data centers if you were actually going to geographically distribute the databases across regions.) If the two databases ran on the same server, you would be forced to name the replica differently (for example, WineCloudDb2), whereas you’ll be able to name both of them WineCloudDb by using separate servers.
Note
In Chapter 1, you already created a server, opened the firewall, and created a database. Although the following instructions are very similar to the ones we provided then, you might still want to refer to “Creating a server” in Chapter 1 because the instructions there include screen shots and details that are not repeated here.
To create a new server, follow these steps:
1. Log in to the Microsoft Azure portal at https://manage.windowsazure.com. This brings you to the main portal page showing ALL ITEMS.
2. Click SQL DATABASES in the vertical navigation pane on the left.
3. Click the SERVERS link at the top of the page.
4. Click the ADD button at the bottom of the page.
5. Provide a new server administrator login name and password, and then reenter the password to confirm.
Tip
To simplify things while you are practicing, we recommend using the same login name (for example, saz) and password you assigned to the first server.
6. Choose a region from the drop-down list. As we explained, you should select the same region you chose for the first server so that your subscription will not incur data-transfer charges when you synchronize between the databases on the two servers.
7. Be sure the ALLOW WINDOWS AZURE SERVICES TO ACCESS THE SERVER check box remains selected. This setting enables Microsoft Azure services like SQL Data Sync to access the server.
8. Click the checkmark icon in the lower right side of the dialog to complete the settings.
9. Wait for the new server status to change from “creating” to “started.”
Next, you’ll open the Microsoft Azure firewall, although this procedure is technically optional. SQL Data Sync does not require you to open the Microsoft Azure firewall to synchronize across servers. However, as explained in Chapter 1, you do need to open the firewall for your machine’s IP address if you want to be able to access the server from the Silverlight-based SQL Database management portal (which you will do in upcoming procedures), and other tools (such as SQL Server Management Studio and SSDT in Visual Studio).
To open the firewall, follow these steps:
1. Click the server name.
2. Click the CONFIGURE link at the top of the page.
3. To the right of your current detected IP address, click ADD TO THE ALLOWED IP ADDRESSES.
4. Click the SAVE button at the bottom of the page.
5. Click the back icon (the large back-pointing arrow) to return to the SQL DATABASES page.
Now you’ll create an empty database on the new server. To do so, follow these steps:
1. Click the DATABASES link at the top of the page.
2. Click the NEW button at the bottom of the page.
3. Click QUICK CREATE.
4. For DATABASE NAME, type WineCloudDb. (Because this database will run on the new server, it can be the same name as your existing database running on the first server.)
Note
Conversely, if you were scaling out to separate OLTP activity from reporting activity, you could use the same server. In that case, you’d be forced to give the replica database a unique name, which might be something like WineCloudReportingDb.
5. Choose the server you created in the previous procedure from the SERVER drop-down list.
6. Click CREATE SQL DATABASE.
After a few more moments, the new WineCloudDb database is created. This database is completely empty, but it will soon contain the same tables with the same data as the existing WineCloudDb on the first server, once you create the sync group, configure the sync rules, and run a manual sync.
When creating the sync group, you will define the hub database and the first client database. (A client database is also often called a reference database.)
To create the sync group, follow these steps:
1. Click the ADD SYNC button at the bottom of the page, and then choose New Sync Group, as shown in Figure 7-5.
FIGURE 7-5 Creating a new sync group
2. On the Sync Group Basic Settings page, do the following:
a. For NAME, type WineCloudSyncGroup.
b. In the REGION drop-down list, choose the same region as the two servers. The page should appear similar to Figure 7-6.
FIGURE 7-6 Naming the sync group and choosing a region to host it in
c. Click the right-arrow Next button.
3. On the Define Sync Hub page, do the following:
a. For HUB DATABASE, choose the WineCloudDb database running on the first server (the database that already has some data in it).
b. Enter the server’s administrator user name and password credentials for HUB USER NAME and HUB PASSWORD.
c. Leave CONFLICT RESOLUTION at its default setting, Hub Wins. (We will discuss conflict resolution shortly.) The page should look similar to Figure 7-7.
FIGURE 7-7 Configuring the hub database
Note
The conflict-resolution setting cannot be changed once the sync group is created. You need to delete the sync group and re-create it if you want to change the setting.
d. Click the right-arrow Next button. Because of the way the portal handles validation on this page, you need to wait a moment after clicking it once, and then click it again.
4. On the Add A Reference Database page, complete the following steps:
a. For REFERENCE DATABASE, choose the WineCloudDb database running on the new server (the empty database you just created). You’ll notice that the database selected for the hub is not even present as a choice in the drop-down list.
b. Enter the server’s administrator user name and password credentials for USER NAME and PASSWORD. (If you assigned the same credentials to the new server as the original server, these are the same values you typed for the hub database on the previous page.)
c. Leave SYNC DIRECTION at its default setting, Bi-Directional. The page should look similar to Figure 7-8.
FIGURE 7-8 Configuring a reference database
Note
The two other available sync direction settings are one-way from the hub to the client and one-way from the client to the hub, either of which you could choose to use rather than bi-directional, if doing so makes sense for the given scenario. (Different one-way scenarios were discussed at the beginning of this chapter.) Also note that the sync direction setting cannot be changed once the sync group is created. You need to remove a client database from the sync group and then add it again if you want to change the setting.
d. Click the checkmark “finish” button to complete the settings. Once again, you might need to wait a moment after clicking it once, and then click it again.
As shown in Figure 7-9, the sync group has been created at this point, but it is not ready. And it will remain in this Not Ready state until you further configure the sync group with sync rules.
FIGURE 7-9 Viewing the newly created sync group
Also notice that the portal has added a third link (SYNC) after the standard DATABASES and SERVERS links on the SQL Databases page. When at least one sync group has been created, the SYNC link takes you to this management view of all your sync groups.
Creating sync rules
With the sync group in place, it’s time to set the sync rules. Essentially, the sync rules define the dataset, which is to say, exactly which tables and columns you want synchronized across all reference databases in the sync group. Until at least one dataset is defined, the sync group will remain in a Not Ready state, so let’s go ahead and define one now.
To configure the sync rules and define the dataset, follow these steps:
1. Click the sync group name WineCloudSyncGroup. This opens to the REFERENCES view that lists all the databases in the sync group, as shown in Figure 7-10. Notice that the status of each database is Not Ready, again, because no sync rules have yet been configured.
FIGURE 7-10 The REFERENCES page lists all the databases in the sync group
2. Click the SYNC RULES link at the top of the page.
3. Click DEFINE SYNC RULES beneath the message stating that you have no sync rules.
4. On the DEFINE DATASET dialog, select the WineCloudDb database running on the first server (the one with pre-existing tables and data, not the new empty one).
5. Click the checkmark icon to close the dialog. The SYNC RULES page now lists all the tables discovered in the WineCloudDb database, as shown in Figure 7-11.
FIGURE 7-11 The SYNC RULES page listing all the tables in the database that can be synchronized
6. Click the SELECT button at the bottom of the page, and click Select All The Columns In All The Tables, as shown in Figure 7-12.
FIGURE 7-12 Selecting the entire database (all columns in all tables) for synchronization
7. Click the SAVE button at the bottom of the page, as shown in Figure 7-13.
FIGURE 7-13 Saving the sync rules
8. You might need to wait a few moments for processing before the portal becomes responsive again. When processing completes, click the REFERENCES link at the top of the page. Both databases are now designated with a status of Good, as shown in Figure 7-14.
FIGURE 7-14 The REFERENCES page now showing all the databases in the sync group with a Good status
9. Click the back icon (the large back-pointing arrow) to return to the SQL DATABASES page for the new sync group. As shown in Figure 7-15, the sync group has now also transitioned to a status of Good.
FIGURE 7-15 The SYNC page now showing the sync group with a Good status
At this point, SQL Data Sync has created tables in the new database exactly as they are defined in the existing database.
Important
SQL Data Sync does more than just replicate the tables you chose to be synchronized; it also adds special objects to each database in the sync group (including on-premises SQL Server databases). To track incremental data changes, SQL Data Sync creates a change-tracking table for each table that is being synchronized, adds triggers to your base tables, and also creates some stored procedures for gathering and applying changes. Therefore, it is highly recommended that you first test SQL Data Sync in a nonproduction environment to ensure it does not have an adverse effect on your existing databases or applications.
Running a manual sync
SQL Data Sync has created tables in the new database, but it has not populated the tables with any rows yet (that is, it has copied only schema, but no data). This is because there has not yet been a synchronization pass, nor will there be until you either define an automated synchronization schedule or run a manual sync.
You’ll create an automated schedule shortly, but right now you’ll start by running a manual sync. To do so, follow these steps:
1. Click the SYNC button at the bottom of the page, as shown in Figure 7-16. After a few moments of processing, the sync group returns to its normal Good status. This means the synchronization was successful.
FIGURE 7-16 Running a manual sync
2. Wait a few moments for processing to complete, and click the sync group name WineCloudSyncGroup.
3. Click the LOGS link at the top of the page. The portal displays a message confirming that the databases synchronized successfully, as shown in Figure 7-17.
FIGURE 7-17 Viewing the sync group logs
Now you’ll rely on your own two eyes to verify that, indeed, everything is working as it should. The best way to do that is to monitor both databases side by side as you change them individually, and then synchronize those changes. You can do this by opening two separate browser tabs to the SQL Database portal—one for each WineCloudDb database.
Note
In Chapter 1, you already saw how to launch a new browser tab to the SQL Database portal. Although the following instructions are very similar to the ones we provided then, you might still want to refer to Chapter 1 because the instructions there include screen shots and details that are not repeated here.
To open the two SQL Database management portal tabs, follow these steps:
1. Click the back icon (the large back-pointing arrow) to return to the SQL DATABASES page.
2. Click the DATABASES link at the top of the page.
3. Click on the first WineCloudDb database.
4. Click the DASHBOARD link at the top of the page.
5. Scroll the page down a bit, and click on the MANAGE URL link in the quick glance section at the right of the page. This opens a new browser tab to the SQL Database portal’s login page for the first WineCloudDb database.
6. On the login page, type the administrator user name and password, and click Log On. This takes you to the Summary page for the database.
7. Click the previous browser tab to return to the Microsoft Azure portal that just launched the new browser tab.
8. Click the back icon (the large back-pointing arrow) to return to the SQL DATABASES page.
9. Click on the second WineCloudDb database.
10. Click the DASHBOARD link at the top of the page.
11. Scroll down and click on the MANAGE URL link on the right to open another new browser tab to the SQL Database portal’s login page for the second WineCloudDb database.
12. On the login page, type the administrator user name and password, and click Log On to go to the Summary page for the database.
Now that your environment is all set up, start by running the same simple query in each database to view and compare the contents of the Wine table. To run the queries, continue working in the current browser tab that’s open to the SQL Database management portal for the secondWineCloudDb database, and follow these steps:
1. Click New Query at the top of the page to open a new query window.
2. Click inside the code window, and type SELECT * FROM Customer.
3. Click Run at the top of the page. SQL Database executes the query and displays the results in the bottom portion of the query window, as shown in Figure 7-18.
FIGURE 7-18 Querying the contents of the Customer table after the first manual sync
4. Click the browser tab that’s open to the SQL Database management portal for the first WineCloudDb database.
5. Click New Query at the top of the page to open a new query window.
6. Click inside the code window, and type SELECT * FROM Customer.
7. Click Run at the top of the page. SQL Database executes the query and displays the results, which should appear identical to the results of the same query you ran in the other database, as just seen in Figure 7-18.
Receiving identical query results in both browser tabs is a clear indication that the first manual sync worked correctly and both databases are in sync. Now update some data on both sides and watch the databases sync up once again. To run the updates, continue working in the current browser tab and follow these steps:
1. On a new line below the SELECT statement you typed in the previous procedure, type UPDATE Customer SET FavoriteWineId = 2 WHERE CustomerId = 1.
2. Click and drag the mouse to highlight the complete UPDATE statement.
3. Click Run at the top of the page. SQL Database executes the statement and indicates that one row was affected. That is, the favorite wine ID for customer ID 1 (Jeff Hay) has changed from 4 to 2.
4. Click the browser tab that’s open to the SQL Database management portal for the other WineCloudDb database.
5. On a new line below the SELECT statement, type INSERT INTO Customer VALUES(‘Chris’, ‘Mayo’, 3).
6. Click and drag the mouse to highlight the complete INSERT statement.
7. Click Run at the top of the page. SQL Database executes the statement and indicates that one row was affected. That is, a new customer row for Chris May was created with a favorite wine ID of 3.
You have now modified each database separately; one database has an updated customer, and the other has a new customer. Next, you will perform another manual sync operation and then confirm visually that each change has been synchronized properly to the other database. To do so, follow these steps:
1. Click the browser tab that’s open to the Microsoft Azure portal. It should still be on the DASHBOARD page for one of the WineCloudDb databases from one of the earlier procedures.
2. Click the back icon (the large back-pointing arrow) to return to the SQL DATABASES page.
3. Click the SYNC link at the top of the page.
4. Click the SYNC button at the bottom of the page. After a few moments of processing, the sync group returns to its normal Good status.
5. Click the browser tab that’s open to the SQL Database management portal for one of the WineCloudDb databases. (It doesn’t matter which.)
6. Click and drag the mouse to highlight the complete SELECT statement.
7. Click Run at the top of the page. SQL Database executes the query and displays the results, which reflect both the updated and inserted customer changes, as shown in Figure 7-19.
FIGURE 7-19 Querying the contents of the Customer table after a bi-directional manual sync
8. Click the browser tab that’s open to the SQL Database management portal for the other WineCloudDb databases.
9. Click and drag the mouse to highlight the complete SELECT statement.
10. Click Run at the top of the page. SQL Database executes the query and displays the results, which should once again appear identical to the results of the same query you ran in the other database, as just seen in Figure 7-19.
Receiving identical query results again in both browser tabs now verifies that bi-directional synchronization is working. The next thing to learn about is conflict resolution. If the same customer row is modified differently in both databases at the same time (meaning, in between synchronizations, not necessarily simultaneously), what happens on the next synchronization?
Establishing conflict resolution
Whenever the same data is modified differently in multiple places, SQL Data Sync will always choose one version of the change to overwrite the other. Which version overwrites which is determined by the conflict resolution setting established when the sync group is created. The two available settings are Hub Wins (the default) and Client Wins.
When you created the sync group, you configured it to use a conflict resolution of Hub Wins. (You did this simply by accepting the default, as shown in Figure 7-7.) Thus, if a conflicting change is made to the same row in both the hub and the client database, the change made to the hub will override (and overwrite) the change made to the client.
When following this next procedure, you need to pay attention to which browser tab is open to the SQL Database management portal for the hub database, and which is open to the portal for the client database. You can distinguish them by the server names that appear in the upper left part of the portal page in each browser tab.
To demonstrate conflict resolution, follow these steps:
1. Click the browser tab that’s open to the SQL Database management portal for the WineCloudDb hub database. (Check the server name in the upper left of the page.)
a. In the code window, delete the existing INSERT or UPDATE statement (but leave the SELECT statement as-is).
b. On a new line below the SELECT statement, type UPDATE Customer SET LastName = ‘Mayo-Hub’ WHERE CustomerId = 4.
c. Click and drag the mouse to highlight the complete UPDATE statement.
d. Click Run at the top of the page. SQL Database executes the statement and indicates that one row was affected.
2. Click the browser tab that’s open to the SQL Database management portal for the other WineCloudDb database (the one on the new server).
a. In the code window, delete the existing INSERT or UPDATE statement (but leave the SELECT statement as-is).
b. On a new line below the SELECT statement, type UPDATE Customer SET LastName = ‘Mayo-Client’ WHERE CustomerId = 4.
c. Click Run at the top of the page. SQL Database executes the statement and indicates that one row was affected—the same row you just modified in the hub database (with a different change).
3. Click the browser tab that’s open to the Microsoft Azure portal. It should still be on the SYNC page from the manual sync you ran in the previous procedure.
4. Click the SYNC button at the bottom of the page. After a few moments of processing, the sync group returns to its normal Good status.
5. Click the browser tab that’s open to the SQL Database management portal for the WineCloudDb hub database.
a. Click and drag the mouse to highlight the complete SELECT statement.
b. Click Run at the top of the page. SQL Database executes the query and displays the results. As shown in Figure 7-20, the customer name change to Mayo-Hub has stuck; it has not been overwritten by the conflicting name change made to the same row in the client database.
FIGURE 7-20 The hub change remains after a conflict with a client, using Hub Wins conflict resolution
6. Click the browser tab that’s open to the SQL Database management portal for the other WineCloudDb database (the client database on the new server).
a. Click and drag the mouse to highlight the complete SELECT statement.
b. Click Run at the top of the page. SQL Database executes the query and displays the results. As shown in Figure 7-21, the customer name change to Mayo-Client has been overwritten by the conflicting name change (Mayo-Hub) made to the same row in the hub database.
FIGURE 7-21 The client change is overwritten after a conflict with a hub, using Hub Wins conflict resolution
If the conflict resolution had been set to Client Wins, the opposite would have occurred; the change made to the client database would have remained on the client, and the change made to the hub would have been overwritten to reflect the client change (that is, the customer would have the name Mayo-Client in both databases).
When a sync group has only one client database in it, the behavior with Hub Wins or Client Wins is absolutely predictable, and it will always work as we just described. But once two or more client databases are involved, and there are conflicting changes across multiple clients (but not the hub), conflict-resolution behavior cannot be predicted, regardless of the setting:
In the case of Hub Wins, the first client change that gets written to the hub is kept. Any conflicting data changes made in any of the other clients are discarded. Then the change written to the hub by the first client is propagated out to all the other clients.
In the case of Client Wins, conflicting data changes made in all clients are written to the hub, each one overwriting the previous one, so that the changes written by the last client are then propagated out to all the other clients.
In either case, the winner cannot be predicted, because the order in which client changes are written to the hub is nondeterministic and can vary from one synchronization pass to another. The only predictable resolution case when multiple clients are involved is when a conflicting change is made in the hub database as well and the conflict resolution is set to Hub Wins. In this case, conflicting changes in the client databases will always be overwritten by the hub change.
Creating an automated sync schedule
Once you have run several manual syncs and are satisfied with your sync group, you won’t want to sync manually any more. At this point, you will want to let SQL Data Sync schedule automated synchronization passes automatically.
Fortunately, SQL Data Sync supports a simple scheduling mechanism. Automation is either turned on or off. If automation is turned on, the schedule frequency can range from (approximately) five minutes to one month. This means the closest you can keep the databases in your sync group up to date by is five minutes.
Note
We say approximately parenthetically, because the service does not guarantee precise timing. For example, you might request to run every 10 minutes and find occasionally that 11 minutes elapse between two passes.
To set an automated sync schedule that runs every 10 minutes, follow these steps:
1. Click the browser tab that’s open to the Microsoft Azure portal. It should still be on the SYNC page from the manual sync you ran in previous procedures.
2. Click the sync group name WineCloudSyncGroup.
3. Click the CONFIGURE link at the top of the page.
4. For AUTOMATIC SYNC, click ON.
5. For SYNC FREQUENCY, type 10.
6. Click anywhere on the page. This is necessary to shift focus away from the SYNC FREQUENCY text box, which causes the SAVE button to appear at the bottom of the page, as shown in Figure 7-22.
FIGURE 7-22 Configuring an automated sync schedule to run every 10 minutes
7. Click the SAVE button.
The schedule is now set. To test the schedule, follow these steps:
1. Click the browser tab that’s open to the SQL Database management portal for one of the WineCloudDb databases. (It doesn’t matter which one.)
2. In the code window, delete the existing INSERT or UPDATE statement (but leave the SELECT statement as-is).
3. On a new line below the SELECT statement, type UPDATE Customer SET LastName = ‘Mayo-Auto’ WHERE CustomerId = 4.
4. Take a break for 10 minutes (or more). Have a glass of wine (or two). You’ve earned it!
5. Run the SELECT statement in each of the SQL Database management portal browser tabs open to the hub and the client, and notice how the customer name is now Mayo-Auto in both databases.
During your 10-minute break, an automatic synchronization pass ran, which copied the change to the other database. At this point, you can leave things alone and let the service synchronize every 10 minutes automatically, although you can still sync manually any time you want to, of course. You can also change the interval or disable automatic synchronization as desired. Every synchronization pass (whether manual or automated) is logged and can be seen by clicking on the LOGS link (as you saw in Figure 7-17).
Creating a local SQL Server database
Now that you’ve got the synchronization working between two SQL Databases in the cloud, you’re ready to add a local on-premises SQL Server database to the sync group. This requires the local installation of a special agent service that you can register on-premises databases with. The SQL Data Sync service in the cloud will gain access to those on-premises databases through the local agent service. Of course, this requires a local SQL Server database.
If you have access to a SQL Server instance you can create a local database on, you can use that SQL Server instance. Otherwise, you will need to install the SQL Server Express edition to host a local database so that you can continue following along. A step-by-step procedure for doing so can be found in the Introduction, in the section “Installing SQL Server Express Edition.”
Note
This chapter assumes you are using the SQL Server Express edition for your local SQL Server database, which has an instance name of .\sqlexpress. If you are using another edition, you must replace the instance name .\sqlexpress specified in the instructions with the name of the instance you are using. For example, if you are running a primary instance of the SQL Server Developer edition on your local machine, you can simply specify the dot (.) symbol or localhost. If you are running a named instance on your local machine, append a backslash followed by the name of the instance (for example, .\myinstance or localhost\myinstance).
In the next procedure, you will create a new database on your local SQL Server instance called WineLocalDb. The local database will start out completely empty, but it will soon become a replica of the other WineCloudDb databases once you add it to the sync group. (This is the same approach you took earlier when you created the second WineCloudDb database and added it to the sync group.)
To create the local database, follow these steps:
1. Start Visual Studio 2013.
2. If the SQL Server Object Explorer is not visible, click the VIEW menu and choose SQL Server Object Explorer.
3. In the SQL Server Object Explorer, right-click SQL Server and choose Add SQL Server to display the Connect To Server dialog.
4. For Server Name, type .\sqlexpress (or the name of your local SQL Server instance).
5. For Authentication, choose Windows Authentication. Or, if your local SQL Server instance is configured not to support Windows Authentication, choose SQL Server Authentication and supply valid credentials for Login and Password. The Connect To Server dialog should appear similar to Figure 7-23.
FIGURE 7-23 Connecting to a local SQL Server Express edition instance
6. Click Connect. The local instance now appears as a collapsed node in the SQL Server Object Explorer.
7. Expand the new node in the SQL Server Object Explorer.
8. Right-click Databases, and choose Add New Database.
9. Type WineLocalDb and press Enter. The new database now appears in the SQL Server Object Explorer, as shown in Figure 7-24.
FIGURE 7-24 Creating the WineLocalDb database on a local SQL Server instance
You now have an empty on-premises database named WineLocalDb. Next, you will configure a sync agent so that this database can participate in the sync group.
Creating a sync agent
SQL Data Sync communicates with on-premises SQL Server databases through the client agent—a special local service that mediates interaction between the local database and the sync group running on Microsoft Azure.
With this approach, the SQL Data Sync service does not communicate directly with the local database; instead, all local-to-cloud communications take place through the agent. This means that the SQL Data Sync service in the cloud can access your on-premises SQL Server databases, even if they are located behind a firewall (which is typically the case in production environments). When the service communicates with the agent, it does so using encrypted connections and a unique token or agent key. The SQL Server databases authenticate the agent using the connection string and agent key, which provides a high level of security.
To install the local sync agent, follow these steps:
1. Log in to the Microsoft Azure portal (or return to the browser window where the Microsoft Azure portal is running, if it’s still open).
2. Click SQL DATABASES in the vertical navigation pane on the left.
3. Click the ADD SYNC button at the bottom of the page, and then choose New Sync Agent, as shown in Figure 7-25.
FIGURE 7-25 Creating a sync agent
4. Click the Install One Here link, as shown in Figure 7-26. This opens a new browser tab to a download page for the sync agent.
FIGURE 7-26 Using the New Sync Agent dialog link to install a new local sync agent
5. Click the large orange Download button.
6. Select SQLDataSyncAgent-Preview-ENU.msi, as shown in Figure 7-27.
FIGURE 7-27 Selecting the SQL Data Sync Agent download
7. Click Next.
8. If you receive a pop-up warning, click Allow Once, as shown in Figure 7-28.
FIGURE 7-28 Temporarily allowing pop-ups to enable the download, if necessary
9. When prompted to run or save the file, choose Run. This downloads and starts the Microsoft SQL Data Sync Agent Preview Setup Wizard:
a. On the Welcome page, click Next.
b. On the License Agreement And Privacy Information page, click I Agree and then click Next.
c. Type a local Windows user name and password for the account that the agent service should run under. The user name should include the local domain or machine name followed by a backslash, as shown in Figure 7-29.
FIGURE 7-29 Configuring the Windows account that the local agent will use to access on-premises SQL Server databases.
d. Click Next.
e. On the Select Installation Folder page, click Next.
f. On the Confirm Installation Page, click Next to begin the installation.
g. If the User Account Control dialog appears, click Yes.
h. When the installation completes, click Close.
The local agent service is now installed. In a moment, you will register the on-premises WineLocalDb database with the local agent. But first you need to complete the agent configuration on Microsoft Azure.
To configure the agent on Microsoft Azure, follow these steps:
1. Return to the browser tab that’s still open to the Microsoft Azure portal on the New Sync Agent page.
2. For NAME, type WineSyncAgent.
3. In the REGION drop-down list, choose the same region as the time zone your local machine is set to. The page should appear similar to Figure 7-30.
FIGURE 7-30 Configuring the sync agent on Microsoft Azure
Important
You will not be able to register local databases with a sync agent located in a different region.
4. Click the checkmark icon in the lower-right side of the dialog to complete the settings.
5. After a few moments, the agent is created and appears offline, as shown in Figure 7-31.
FIGURE 7-31 Viewing the new agent created on Microsoft Azure
6. Click WineSyncAgent. This displays the local databases registered with the agent, which is currently an empty list.
7. Click the MANAGE KEY button at the bottom of the page.
8. Click the green Generate button.
9. Click the clipboard copy button to the right of the generated key, as shown in Figure 7-32. (If you are prompted for clipboard access by Internet Explorer, click Allow Access.)
FIGURE 7-32 Generating an access key that can be used to register local SQL Server databases with the agent
Now that the agent access key has been copied to the clipboard, the last step is to register the local database with the sync agent. To do so, follow these steps:
1. From the Windows Start screen, launch Microsoft SQL Data Sync Agent Preview. You can either scroll through the tiles to find it or just type data sync agent to run an app search, and then click on the Microsoft SQL Data Sync Agent Preview tile, as shown in Figure 7-33.
FIGURE 7-33 Launching the SQL Data Sync agent from the Windows 8 Start screen.
2. If the User Account Control dialog appears, click Yes.
3. Click the Submit Agent Key button .
4. Right-click in the Agent Key text box, and choose Paste. This pastes the key generated on the portal, as shown in Figure 7-34.
FIGURE 7-34 Providing the service-generated access key to the local agent service
5. Click OK.
6. Click the Ping Sync Service button. You should receive a message dialog stating that the agent successfully pinged SQL Data Sync, which you can close by clicking OK.
7. Click Register to display the SQL Server Configuration dialog.
a. For Authentication, choose Windows Authentication. Or, if your local SQL Server instance does not support Windows Authentication, choose SQL Server Authentication and supply valid credentials for Login and Password.
b. For Server Name, type .\sqlexpress (or the name of your local SQL Server instance).
c. For Database, type WineLocalDb. The SQL Server Configuration dialog should appear similar to Figure 7-35.
FIGURE 7-35 The SQL Server Configuration dialog, which is used to register a local SQL Server database with Microsoft Azure SQL Data Sync
d. Click Save. The database is registered with the local agent service, as shown in Figure 7-36.
FIGURE 7-36 Registering an on-premises SQL Server database with the local SQL Data Sync agent service
8. Return to the browser tab that’s still open to the Microsoft Azure portal on the Manage Access Key page.
9. Click the checkmark icon in the lower-right side of the dialog to close the Manage Access Key page.
10. On the WineSyncAgent page, the portal now displays the WineLocalDb databases registered with the agent, as shown in Figure 7-37.
FIGURE 7-37 Viewing on-premises SQL Server databases registered with the agent
Now that the on-premises WineLocalDb database is registered with the agent, it’s easy to add it to the sync group. To do so, follow these steps:
1. Click the back icon (the large back-pointing arrow) to return to the SYNC page. As shown in Figure 7-38, the sync agent status has now transitioned from Offline to Online. (It might be necessary to refresh the page by pressing F5 to see the transitioned status.)
FIGURE 7-38 The WineSyncAgent now appears online in the Microsoft Azure portal
2. Click WineCloudSyncGroup. This displays the reference databases of the sync group, which still currently includes just the two WineCloudDb databases in the cloud.
3. Click the ADD button at the bottom of the page to display the Add A Reference Database dialog.
4. On the Add A Reference Database page, do the following:
a. For REFERENCE DATABASE, choose the WineLocalDb database that appears beneath SQL Server Databases - WineSyncAgent, as shown in Figure 7-39.
FIGURE 7-39 Selecting the on-premises database registered with the agent for inclusion in the sync group.
b. If the local database was registered using Windows Authentication, no credentials are required and the USER NAME and PASSWORD text boxes will be disabled. Otherwise, enter the user name and password you supplied when you registered the local database in the previous procedure’s step 7.
c. Leave the SYNC DIRECTION at its default setting, Bi-Directional. The page should look similar to Figure 7-40.
FIGURE 7-40 Adding the on-premises database to the sync group
d. Click the checkmark “finish” button to complete the settings.
5. Click the SAVE button at the bottom of the page. After a few moments of processing, the database is added to the sync group, and all three databases (the two WineCloudDb databases and the one on-premises WineLocalDb database) are designated with a status of Good, as shown inFigure 7-41.
FIGURE 7-41 The sync group configured with two Microsoft Azure SQL Databases and one on-premises SQL Server database
6. Now either wait approximately 10 minutes (the automatic sync interval you configured for the sync group) or click the SYNC button at the bottom of the page if you’d rather not wait. This updates the entire sync group, which includes the local SQL Server database you just added to the sync group.
7. Switch back to Visual Studio, which should still be open from when you created the empty WineLocalDb database in a previous step.
8. In the SQL Server Object Explorer, expand the WineLocalDb database node, and then expand the Tables node nested beneath the database node.
9. Right-click the dbo.Customer table, and choose View Data. As shown in Figure 7-42, the table now contains all the customer data pulled in from the WineCloudDb hub database.
FIGURE 7-42 The on-premises SQL Server database now containing the customer data synchronized from the WineCloudDb hub database
Congratulations! You just created a fully functional sync group that synchronizes data bi-directionally between multiple SQL Databases on Microsoft Azure and an on-premises SQL Server database running locally. The client databases (the second WineCloudDb database and the on-premises WineLocalDb database) are both configured for bi-directional synchronization, so any changes made in any of the databases in the sync group (including the hub) will be replicated automatically across all the other databases.
Furthermore, because the sync group’s conflict resolution is set to Hub Wins, changes made to rows in the hub database will overwrite changes made to the same rows in any of the client databases if those changes are made within the same sync interval. And, as explained in the section “Establishing Conflict Resolution,” if conflicting changes are made across only client databases but not the hub database, the first client change that gets written to the hub is the one that will be used to update the rest of the sync group; conflicting data changes made in any of the other clients will be discarded. Take some time now to experiment with the sync group by making changes to the hub and client databases (including conflicting changes), and then view the effects of synchronization (either by waiting approximately 10 minutes or forcing a manual sync between changes).
Pitfalls and best practices
We conclude this chapter by discussing a few points to keep in mind when working with SQL Data Sync. There are several things you can do to ensure you gain the best possible performance, starting with choosing the location of your cloud databases. The geographic location where your SQL Databases are hosted can impact both the efficiency and cost of the otherwise free SQL Data Sync service. To minimize latency, locate your SQL Database servers in data centers as physically close as possible to your on-premises SQL Server database locations.
Next, limit your synchronization to include just the items you need to sync. As you saw, SQL Data Sync doesn’t require the entire database to participate in a sync group, so you should always select the fewest tables and columns possible when you configure your dataset. This practice improves performance by reducing the overall payload of a synchronization pass.
Another consideration to bear in mind is the frequency with which a synchronization pass occurs. If a pass attempts to synchronize a sync group that has not yet completed a prior synchronization, the attempt will fail. When planning a schedule, take care that you set the interval sufficiently large enough to ensure that synchronization completes before the next synchronization pass starts. Also, remember that the intervals are approximate, and that the finest automation schedule you can implement is once every five minutes.
The sync schedule can affect your SQL Database costs as well. Although at the time of this writing Microsoft offers SQL Data Sync as a free service, SQL Database fees are charged according to the amount of data moved out of a data center. To minimize costs, you should consider dividing data into separate sync groups according to the frequency with which the data changes. Volatile data should be synchronized at a higher frequency than static or lookup data. Partitioning sync groups in this way allows you to configure an optimal schedule that helps reduce costs by sending data less frequently.
One pitfall to avoid when setting up multiple sync groups is a condition known as a sync loop. A sync loop occurs when a change in a record in one sync group is rewritten to the same record by a second sync group, similar to a circular reference. This highly undesirable condition can potentially enter an infinite loop and consume enough resources to significantly degrade performance. Furthermore, you will pay fees for moving data into and out of SQL Database unnecessarily. You can avoid sync loops in a few ways:
Design your sync groups such that a loop cannot occur; that is, don’t let the same table be synchronized by two different sync groups.
Specify a sync direction such that a loop condition cannot exist.
Finally, be aware of the standard security measures taken by the SQL Data Sync service:
Data is encrypted whenever it is transmitted.
All SQL Database connection points use SQL authentication.
SQL Database and SQL Server connections are always encrypted.
Encrypted SQL Server connections are further secured using an agent key.
Summary
This chapter taught you all about SQL Data Sync, a Microsoft Azure service that provides automatic data replication across any number of SQL Databases hosted on Microsoft Azure as well as local SQL Server databases hosted on-premises.
We began by explaining the hub-and-spoke architecture upon which SQL Data Sync is based, and then described the variety of scenarios in which the service can be configured. This includes one-way replication from data mastered in SQL Server on-premises to SQL Databases on Microsoft Azure, one-way replication in the reverse direction to pull data populated in Microsoft Azure SQL Database down to an on-premises SQL Server database, and full bi-directional synchronization across SQL Databases hosted in multiple Microsoft Azure data centers and local SQL Server databases hosted in multiple on-premises locations.
With that foundation laid, you then created a sync group to synchronize between two Microsoft Azure SQL Databases in the cloud, learned about the two different conflict-resolution strategies (Hub Wins and Client Wins), and set up an automated schedule to keep the databases in sync on a regular basis. Finally, you installed and configured a sync agent and registered a local on-premises SQL Server database with the sync group to achieve complete two-way synchronization between the local SQL Server database and the SQL Databases in the cloud.