Problems with having SQL Server Identity Column in the Cloud

All we need is an easy explanation of the problem, so here it is.

Our company is utilizing Identity(1,1) clustered primary index in SQL Server 2016 Enterprise, and will eventually deploy into the Cloud (Amazon AWS, Google Cloud).

  1. Is there any issues utilizing Identity Column in Azure SQL? If were ever horizontally elastically scale databases, we can manage identity columns using range identities or location/storage ids,

  2. Are there any issues deploying Microsoft SQL server Identity Columns into
    Cloud in general (Google or Amazon AWS Cloud)? We heard of identity numeric gaps as in any SQL Server Environment, and how often will they occur in cloud?

  3. How about Amazon AWS?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

  1. No issues with IDENTITY or SEQUENCE in Azure SQL Database, or SQL Server on Azure VMs.

  2. If you move to a federated design, you should not use the IDENTITY or SEQUENCE value as the only key of any federated table. If your key is something like (TenantId, SequenceVal), then you can have duplicate SequenceVal, but still allow the rows to be globally unique. Otherwise you can use ranges, as you propose, or somehow interleave the values.

Method 2

3) There are no issues utilizing clustered Identity index for Amazon AWS, I contacted Amazon cloud technical support.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply