Long title this time…. I realize it is hard to keep track of the services updates as we release updates frequently to the Azure SQL Database service. For federations, in the July 2012 update, we have made some improvements to the federation reference data experience and eased some of the restrictions. I’ll talk about 3 important scenarios we enabled with the update;

· Replication of Reference Data In Federations using SQL Data Sync: One exciting news is that with the July 2012 update, you will also be able to use SQL Data Sync with federation members. SQL Data Sync does not have a native experience for Federations just yet but given federation members are just databases with their own database names, you can refer to them from the SQL Data Sync.

This has been quite a popular ask from many customers in the past: some customer would like to use SQL Data Sync to replicate all data in one federation member to another database/federation member in some other geography or server in the cloud, others wanted to consolidate data from many/all federation members into a single central database, even to a single SQL Server database on premise. With the recent update, these and many other topologies you can imagine with regular databases are all possible with data sync service for federation member databases… One other great use can be to use SQL Data Sync with Federations to replicate reference data across federation members.  You can details of the capability and its limitations as well as a sample walkthrough in this post.

Optimistic Concurrency Control with Reference Data: Timestamp data type is used as the based of implementing optimistic concurrency control for many of the modern apps. Developers build custom behavior to detect conflicting updates using the timestamp type. This is especially a key conflict detection mechanism for managing reference data in federations as the eventually consistent reference data received updates from many sources. Well, we have heard strong customer feedback around these restrictions and with the recent update to SQL Database removed the restriction on the timestamp data type in reference tables in federation members. You can now use timestamp or rowversion column type in reference tables.  

Here is a basic use case showing why timestamp can be a powerful tool especially for reference data that is replicated across multiple members; lets say you have a products reference table that you need to update and you use the data sync service or some script rollout in the background to do this update on all members. Also assume that your application is concurrently doing updates to the products table as part of its regular OLTP workload. How do you detect conflicts across these updates and avoid a lost-update problem? You can add the timestamp property to the products table and check to ensure the products row you are about to change has the identical timestamp value at the time you read the row. That ensures that this particular product you are about the update has not been updated in between the time you read and came back to update it…

Simplified Unique ID Generation for Reference Data with Identity Property: Another strong feedback came from customer on the identity property. With the update in July, we have removed the restrictions on identity property on reference tables in federation members. This should minimize some of the schema changes we require for migrating existing database schemas over to federations. One less thing to worry about…

Here is a quick example of the type of table that will be possible to create in federation members with the updated SQL Database. This used to be restricted in previous releases;

create table ref_table(c1 int primary key identity, c2 timestamp)
go
insert into ref_table(c2) values (default)
insert into ref_table(c2) values (default)
insert into ref_table(c2) values (default)

go
select * from ref_table

returns:

c1          c2
----------- ------------------
1           0x000000000000001A
2           0x000000000000001D
3           0x000000000000001E

I should note that identity and timestamp is still restricted on federated tables in federation members. The main restriction is due to the MERGE operation we would like to introduce in future that will be the opposite of SPLIT command. When datasets are merged, we expect to have conflicts in the identity values and timestamp values we generated previously in 2 separate federation members. We are working on improvements to improve the overall experience with timestamp and identity in future on federated tables. Until then, for generating unique IDs and optimistic concurrency management for federated tables, you can refer to this post on the topic for workaround and suggestions: ID Generation in Federations.

As always love to hear feedback on these experiences and opinions on Federations.

Enjoy