SQL Data Sync 101: What Is It and Why Should You Use it?

Windows Azure, Microsoft’s cloud computing platform, allows you to get your applications to the cloud.  Azure is a godsend for companies that want to leverage the benefits of using cloud computing along with its features.  One of these features is the SQL Data Sync.

Microsoft’s SQL Data Sync is a cloud-based Web service that allows you to regularly synchronize your data across different servers in various locations.  Because data is updated bi-directionally, you can confidently and easily share your SQL databases to different data centers.

All that without writing a single line of code!

To use SQL Data Sync, you would need to have at least SQL Server 2005 Service Pack 2, or better yet SQL Server 2008 R2 on a x86 or x64 machine running Windows Vista, Windows 7 or Windows 2008.  You would also need a SQL Database subscription or more.

SQL Data Sync works with different SQL data types.  But not all are supported.  Supported data types include:

Exact Numbers

  • bit
  • decimal
  • int
  • numeric
  • smallint
  • tinyint

Approximate Numbers

  • float
  • real

Date and Time

  • date
  • datetime2
  • datetime
  • datetimeoffset
  • time

Character Strings

  • char
  • varchar
  • text

Unicode Character Strings

  • nchar
  • nvarchar
  • ntext

Binary Strings

  • binary except binary(50)
  • varbinary except varbinary(5)
  • image

Spatial Data Types

  • geography
  • geometry

Other Data Types

  • sql_variant
  • table
  • uniqueidentifier
  • xml

All other data types in these categories are not supported.  Further, these data type categories are also not supported:

  • FileStream
  • CLR UDT
  • SQL UDT

Lastly, column properties such as Rowguicol, Filestream and Xml Schema Collection are not supported.

Benefits of Using SQL Data Sync

SQL Data Sync benefit

So why should you consider using SQL Data Sync? Here are some benefits:

  • Free up your IT. Synchronization is a pretty complicated process.  Using SQL Data Sync, however, you have everything you need as far as synchronizing your data goes.  You can customize it according to your needs and cost sensitivity.  This means that your IT personnel are free to do more pressing tasks for your business.
  • Save time. SQL Data Sync only transmits data that have been changed after the previous synchronization.  This helps ensure that your database and all of its copies are up to date without unnecessarily using up your resources or wasting your time to do so.
  • Disaster recovery made easy. With SQL Data Sync all your databases and data are up to date no matter where they are, whether they are in the same data center or in different data centers.  In the event that your business experiences a disaster, you can rest assured that all your data are safe and accessible, and that disaster recovery and business continuity will not be a big headache for you.
  • Faster response times. Because you have different copies of your database, you can let your end users connect with the database or application that is nearest to them.
  • More secure data. You will want to protect your private and confidential data at all cost.  It’s a good thing that SQL Data Sync allows you to do just that.  You do not need to worry about compromising your data because SQL Data Sync encrypts all of your confidential data such as service credentials, user credentials and configuration files.  It also transmits data over secure connections.  More than that, it offers four levels of authentication: client agent authentication, database access authentication, system component authentication, and portal access authentication.

Aside from using SSL for all communications, authentication happens at different points:

  • Local users are authenticated with Windows user security.
  • The SQL Data Sync gives the client agent a unique agent key.
  • Using the credentials and connection string that a user gives the physical database on premises, the client agent is again authenticated.
  • SQL Data Sync authenticates all connections between the various system components using certificates.

Windows Azure’s management portal authenticates users with Windows Azure’s subscription database and Windows Live ID.

Some Known Issues

SQL Data Sync may have some limitations that you should know about.  Some known limitations include:

  • Not a good backup alternative. If you are planning to use SQL Data Sync to back up and restore your databases, it might not be a good idea.  This is because it does not do version synchronizations.  So, it is not possible to restore your databases back to a certain period of time.  What’s more, it excludes stored procedures and some other SQL objects in the backup.  Also, when push comes to shove and you need to restore a SQL Data Sync copy, you will find that it is very slow.
  • Synchronization may fail in a number of scenarios, such as when primary key data is modified in a sync group database, or when you have two tables that have the same names, or when you use an XML string in your database and that string did not match the XML definition in your SQL server.  These are just three of the nine scenarios when synchronization can fail.
  • SQL Data Sync cannot handle concurrent operations such as simultaneously adding a sync group and a database.  You would have to wait for one action to finish before starting another.
  • Others. Microsoft has also been aware of various issues where there are no workarounds available yet. These include:
    • Failing to add SQL Database instances to a sync group.
    • Malfunctioning Client Agent Preview.
    • Not being able to support federated databases.
    • Provisioning failures.
    • The addition of a member database without an associated no sync group.
    • Wrong or inaccurate data changes cropping up during sync.

Talk the Talk: Some SQL Data Sync Terms You Should Know

Here are some terms and concepts you should know when you work with SQL Data Sync:

  1. Client Agent. This makes it possible to have a bi-directional communication between the SQL Server database on site and the SQL Database hub database.
  2. Dashboard. This is where you get an overview on the status of a particular SQL Data Sync server.
  3. Dataset. This is a collection of databases, columns, optional rows and tables that are synchronized as planned.
  4. Sync Group. These are the SQL Server databases and SQL database instances that you want to synchronize.
  5. Sync Job. A scheduled synchronization task that is added to the Sync Job Schedule.
  6. Sync Loop. An event where a sync group’s ongoing process triggers the sync of another sync group, which causes another sync in the original sync group.  This causes your synchronizations to eat up your system’s resources so much so that you may not even access the Windows Azure management portal page.
  7. Synchronization Conflict. This happens when modifications to the same piece of data are made in two or more database copies while syncing.

All in all, SQL Data Sync has advantages and benefits that may far outweigh its limitations. It is just a matter of realizing what your organization needs.

Comments