SQL Azure Federations and the Atomic Unit Identity

Question

I've started work on my first Azure application, and I'm learning a lot as I go. One of the features I discovered recently was Federations in SQL Azure, essentially the SQL Azure sharding implementation so we can scale horizontally.

My project started using SQL Server, and was already largely grouped by user Profile, so I decided that makes the most sense to federate on. I've created the federation, including all of the child tables with one snag - Identity is not supported. I get why it's not supported, what I'm not sure on is what best to replace it with. This seems like a huge problem that someone else must have solved, but I haven't been able to find much.

I could just use UniqueIdentifier, but I read that can be a pain to split on. I'm also not too sure of what other performance issues I could run into using a GUID as my Primary Key for federated tables.

I'm using this with Entity Framework, but haven't got to the point of making that federation friendly yet. From what I can tell, it's not much more complicated than executing some code to select your federation before writing your LINQ query, but I'll cross that bridge when I get to it.

For the moment, I have no idea how best to actually add items to my federation, because there is no good solution to generating an identity.

Any advice would be greatly appreciated.

Answer 1

So I can't talk to the EF question. But I can't comment on the idea of using Uniqueidentifier as your key type. This, in my mind, is the best choice. UniqueIdentifier is actually very easy to split on... the reason people think it's hard is they forget what a UniqueIdentifier is. The GUID that we all know and love is a Hex representation of a 128 bit integer. This means that we can use standard Integer operations with it and thus it's actually as easy to work with as the Int (aut number) you know and love.

While it's not specifically about SQL Azure federations (it's about Windows Azure Storage) this blog post of mine on using the GUID type for sharding should give you all you need to know.

http://www.syringe.net.nz/CommentView,guid,cebe3e19-85e6-4d5b-bc24-afb6f66aaeb1.aspx

Answer 2

I'm using the GUID when using the SQL Azure Federation, it's almost the best choice when data sharding. Assuming if you are using Identity in many federation members this will cause the duplicate of your primary value. When you need to merge the data back, or archive, how do you deal with these records.

People thought the GUID is low performance when data insert, especially if we use it as a clustered index. But I never met this problem. Or I should say, there are many tuning places we can do rather than this one.