PASS Summit: SQL Azure Sync Services Preview and Management Portal Demo

My (@rogerjenn) Quentin Clark at PASS Summit: SQL Azure Reporting Services and Data Sync CTPs Available from Azure Portal post of 10/13/2011 (updated 10/14/2011) described setting up the SQL Azure Sync Services Preview with the updated Windows Azure Management Portal, which is repeated with additional details below. The Preview and Portal update were announced by Quentin Clark in his 10/13/2011 keynote session.

Update 11/4/2011: Microsoft confirmed my SQL Azure Data Sync Preview Fails to Sync Local Database bug report. See end of post.

Update 10/18/2011: The first two passes at this process failed at step 27 due to a serialization error. The problem was caused by my installation of the .NET Framework 4.5 preview with the Visual Studio 11 Developer Preview, which Microsoft released at the \\BUILD Conference in mid September. It installs .NET Framework v4.5.40805 and sets it as the default version as shown here:

See end of post for more details.

Getting Ready to Set Up SQL Azure Sync Services

Testing SQL Azure Sync Services requires a Windows Azure Platform subscription with at least one Web database, a local SQL Server 2008 R2 [Express] sample database (Northwind for this example), and migrating a copy of the database to SQL Azure before you define a Sync Group. The Northwind Orders tables’ datetime fields have 13 years added to bring the date ranges from 2008 to 2011.

To comply with critical constraints:

• Order Details tables have been renamed to OrderDetails, because the Preview doesn’t support syncing tables with spaces.
• The Employees table’s ReportsTo column is removed because it creates a circular synchronization problem.

The Microsoft TechNet Wiki has a SQL Azure Data Sync - Known Issues topic covers issues that are known to the SQL Azure Data Sync team but for which there are no workarounds.

Note: If you don’t have a subscription with an unused database benefit, you can obtain a free 30-day pass here. The TechNet Wiki’s SQL Azure Data Sync - How to Get Started article provides detailed instructions for obtaining a description and setting up a new SQL Azure server and database.

Create your SQL Azure server in the North Central US region, which is the only US region that currently supports Data Sync. West Europe is the only European region. I used George Huey’s SQL Azure Migration Wizard as described in my Using the SQL Azure Migration Wizard v3.3.3 with the AdventureWorksLT2008R2 Sample Database post of 7/18/2010 except for the source database. SQL MigWiz v3.7.7 is current.

Note: You must have SQL Server 2008 R2 [Express] SP1 installed to use SQL MigWiz v3.7.7.

The flow diagram at the upper right is from the TechNet Wiki’s SQL Azure Data Sync - Create a Sync Group article (updated 10/13/2011.)

Setting up the SQL Azure Sync Services Preview

1. Navigate to the Windows Azure Management Portal at https://windows.azure.com/. Log in with your Live ID, select the subscription (OakLeaf Azure MSDN Subscription for this example) and click the Data Sync navigation button to activate the Provision Data Sync Preview Server button:

2. Click the Provision Data Sync Preview Server button to open the Terms of Use dialog and mark the I Agree check box:

3. Click Next to open the Select a Subscription dialog and choose a subscription from the list, if necessary:

4. Click Next to open the Select a Region dialog and choose from North Central US or West Europe, the two locations presently offering the service:

5. Click Finish to return to the Management Portal and click the Sync between On-Premise and SQL Azure button to start the provisioning Wizard. Type a unique name for the SyncGroup:

Note: The SyncGroup name has been applied in the preceding screen capture by clicking a right-pointing arrow.

Add the On-Premises SQL Server Database and Client Sync Agent

6. Click the Click to Add a SQL Server Database to open the Add a SQL Server Database to the Sync Group dialog and mark the Add a New SQL Server Database to the Sync Group option:

7. Accept the default Bi-Directional Sync Direction in the list, click Next to open the Add a New SQL Server Database dialog, and select the Install a New Agent option because this is the first Client Sync Agent to be installed:

8. Click Next to open the Install a New Agent dialog:

9. Click the Download button to open the SQL Azure Data Sync Agent Preview download page:

10. Click the Download button for the LocalAgentSetup.msi installer file to set up a new Agent on your local computer, which must be the machine on which the SQL Server instance you intend to sync is installed or able to connect to it:

11. Click Next, accept the EULA and click Next to open the account dialog. Type a User Name and Password for an account that can reach the SQL Azure Data Sync Service:

12. Click Next to open the Select Installation Folder dialog, accept the default C:\Program Files (x86)\Microsoft SQL Azure Data Sync\ location unless you have a reason to do otherwise, click Next to open the Confirm Installation dialog, and click Next to install the agent, which might take a minute or two to display the Installation Complete dialog:

 

13. Type a name for the Sync Agent In the Install a New Agent dialog’s Agent Name dialog, which enables the Generate Agent Key dialog:

14. Click the Generate Agent Key button to generate a Base64-encode agent key and click the Copy button to copy the key to the clipboard:

15. Paste the Agent key to Notepad or OneNote and save it in a safe place.

Add a SQL Server Database to the Sync Group

16. Click the Install a New Agent dialog’s Next button to open the Add a SQL Server Database to the Sync Group dialog and perform Step 1:

17. Choose Start | All Programs | Microsoft SQL Azure Data Sync | Microsoft SQL Azure Data Sync Agent Preview to open the eponymous dialog:

 

18. Click the Submit Agent Key button to open an Agent Key input box and paste the Agent Key in the text box:

19. Click OK to return to the SQL Azure Data Sync Dialog with the Register Database button enabled. Click it to open the SQL Server Configuration dialog, and type the SQL Server instance and database names:

Note: You must have a valid SSL certificate for the instance’s machine if you mark the Enable SSL Connection with SQL Server check box.

20. Click Test Connection, observe the Connection Succeeded message, and click Save to added it to the Sync Group’s Databases list:

21. Click the Ping Sync Service button, observe the Succeeded message, return to the Add a SQL Server Database to the Sync Group dialog and click the Get Database List button to display the Agent name and enable selection of the COMPUTERNAME\InstanceName - DatabaseName in Step 3’s list:

22. Click Finish to dismiss the dialog and return to the Sync Group dialog with the SQL Server database add in the Not Deployed state:

Add the SQL Azure Database as a Sync Hub and Sync Target

23. Click the Click to Add a SQL Azure Database as the Sync Hub button to open the Add a SQL Azure Database to the Sync Group dialog. Select the SQL Azure Server Name and Database Name from the list, and accept the previously entered administrative User ID and password for the database:

24. Click OK to add the SQL Azure Database as the Sync Hub:

25. Accept the default Sync Schedule of Every 30 Minutes in the right-hand pane unless you have a reason to do otherwise (the allowable range is every 5 minutes to one month), select Client Wins as the Conflict Resolution policy if you want to accept last update wins (otherwise select Hub Wins to accept first update wins).

Tip: The Microsoft TechNet Wiki’s SQL Azure Data Sync - Configure a Sync Group topic describes configuration options and important Critical Constraints on columns included in the synchronization process.

Define the Dataset for Synchronization

26. Click Edit Dataset to open the Define Dataset for Synchronization dialog. Mark Select All tables and columns (primary key columns are selected by default and cannot be excluded from synchronization):

Note: If you want to partially synchronize tables, click the Row Filtering button to apply filtering criteria.

Deploy the Sync Group and Start Initial Synchronization

27. Click OK to close the dialog and return to the Sync Group window, which shortly displays the SQL Azure and SQL Server databases as provisioned (Good status) and the date and time of the last sync operation:

28. Opening either database in SQL Server Management Studio 2008 R2 [Express] displays a set of Data Sysc Services Tracking tables added to a new DataSync schema:

29. You can manually initiate a sync session by selecting the Sync Group in the navigation pane and clicking the Sync Now button:

Problem with Local SQL Server Database Sync

The following Topology diagram indicates that SQL Server synchronization has failed:

30. A common reason for synchronization failure is that the local SQL Server Database is stopped. To verify the local server is running (or, if not, start it), right click the desktop computer icon, choose Manage to open the Computer Management dialog, expand the Services and Applications node and its SQL Services subnode, and verify that the SQL Server [Express] Service is running:

If SQL Server [SQLEXPRESS] isn’t running, right-click the SQL Server node and choose Start to start the service.

31. If SQL Server [SQLEXPRESS] is running, click the Log Viewer button to open the eponymous dialog: If you see message(s) containing the word “deserialize,” you probably have .NET Fx 4.5 installed, as noted at the beginning of this post:

The full message is:

Sync failed with exception There was an error while trying to deserialize parameter http://www.microsoft.com/DataSyncService/NTierSyncSerivce/2011/05:GetBatchInfoResult. Please see InnerException for more details.

Inner exception message: Type 'Microsoft.Synchronization.ChangeBatch' is an invalid collection type since it does not have a default constructor.

Note: You must view the SQL Azure Management Portal in 1,280 x 1,024 resolution to make the elipsis (…) symbol at right of the message visible. Click the ellipsis symbol to open a message box with the full test of the error.

Update 11/4/2011: The Microsoft Connect Team sent me the following message on 11/4/2011:

Microsoft Connect - SQL Server: Feedback item 695390 Comment or Request for Information, SQL Azure Data Sync Preview Fails to Sync

Greetings from Microsoft Connect! This notification was generated for feedback item: SQL Azure Data Sync Preview Fails to Sync Local Database which you submitted at the Microsoft Connect site. Thanks for reporting this issue. With your help we tracked down the issue to a bug with .NET 4.5. We've worked with that team to ensure the problem is addressed in a future release. We'll close
this issue once we've verified the fix and compatibility with the SQL Azure Data Sync agent.

Regards, Mark

32. Run Regedit.exe, navigate to the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\NET Framework Setup\NDP\v4\Full\Version. If the Version is 4.5…, use Control Panel to uninstall the Visual Studio 11 Developer Preview.

Note: Uninstalling Visual Studio 11 Developer Preview doesn’t remove .NET Fx 4.5. You must manually remove the following components also:

1. Microsoft .NET Framework 4.5 Extended Developer Preview
2. Microsoft .NET Framework 4.5 Developer Preview Multi-Targeting Pack
3. Microsoft .NET Framework 4.5 Client Profile Developer Preview

Doing so removes .NET Fx v4.0, which reduces the Version to 3.5.30729.5420:

33. Reinstall .NET Fx v4.0 for x86 or x64 from the Microsoft .NET Framework 4 (Web Installer) page’s dotNetFx40_Full_Setup.exe, which set the v4.0 version as follows:

34. Reboot your computer after making the changes. You might need to remove and add your SQL Server database in the Topology frame.

Credits: Thanks to Liam Cavanagh, previously the product manager for SQL Azure Data Sync, and Sid ? whose presently a Data Sync PM for the solution to my problem.

---

Technorati Tags: SQL Azure,SQL Azure Data Sync,SQL Azure Data Sync Preview,SQL Server 2008 R2,SQL Servder R2 Express,PASS Summit 2011,Cloud Computing,Cloud Databases