Follow up from my previous question SQL Server Identity Column in the Cloud:

Are there any general guidelines? Should a person use Clustered Index Identity or GUID in the cloud for SQL Server 2016? Our company many deploy into Cloud (Amazon AWS, Google Cloudcloud), in any type (PaaS, DBaas, VM), not sure which. 'Company Strategy is be able to deploy anywhere.' We only utilize 5million (70GB) rows per year.

Note: If were ever horizontally elastically scale databases, we can manage identity columns (DB1 gets 1Trillion-2T, DB2 gets 2Trillion-3T) using range identities or location/storage ids. Guids have higher write slowness and fragmentation than Identity, but are mergable in horizontal scale databases. With Identity, 8 bit, Incremental so faster insertions. If using Guid, our API will generate Newid(), not NewSequentialId.

  • 1
    Hi SpiritTech985, I'd lean to use an integer column along with another integer referenced by a foreign key to a site table, but that may imply a lot of changes to the applications. Is there any reason why you cannot use NEWSEQUENTIALID() ? – Nicolas Souquet Sep 16 '17 at 16:49
  • we are currently in design stage so nothing implemented yet, if we use guid, it will be generated by API (business rule at company), newid, so we cannot utilize new sequentialid, plus guids have larger byte usage – SkyPool392 Sep 16 '17 at 16:51
  • Being in the cloud or not doesn't really change the answer to this. UNIQUEIDENTIFIER columns have many drawbacks, affecting storage, memory grants, fragmentation, etc. Using GUIDs as the clustering key (even with sequential IDs) is nearly always a bad idea – AMtwo Sep 17 '17 at 1:47
up vote 5 down vote accepted

Oh goodness, you have a lot of questions in here. I'll try to unpack them all.

Q: What is "SQL Server in the cloud"?

The term "the cloud" is too generic - you might as well be saying "SQL Server on the network." There are lots of different ways to deploy SQL Server in the cloud, some of which function exactly like on-premises servers (it's just that someone else is managing the server for you.)

If you can, try to avoid generic sweeping statements like "SQL Server in the cloud" because you won't get good answers. The more specific your question can be, the more relevant advice you can get. (Not to mention the less downvotes, ha ha ho ho.)

Q: Is there a universal rule for clustering keys?

Generally, you want your clustering keys to follow the SUN-E principles:

  • Static - fields that don't change, so you don't have to update your nonclustered indexes
  • Unique - so SQL Server doesn't have to add a uniqueifier behind the scenes
  • Narrow - so lots of data isn't copied into your nonclustered indexes
  • Ever-increasing - creating a hotspot at the end, guaranteeing that where you're inserting will usually be in memory (although this is one rule that folks violate on purpose during extremely heavy concurrency, like tens of thousands of inserts per second)

Identity fields match all 4 (SUN-E), while GUID fields only match the SU portions. One could argue that a single GUID is kinda narrow compared to, say, multiple GUIDs chained together or an NVARCHAR(250), but it's got nothin' on the narrowness of an INT.

Q: Do data modeling guidelines change in IaaS?

Infrastructure-as-a-Service providers like Amazon EC2, Google Compute Engine, and Microsoft Azure VMs are just VMs running on someone else's computer. Data modeling rules do not change here.

Q: Do data modeling guidelines change in PaaS?

Platform-as-a-Service providers are a little different.

Amazon RDS is just SQL Server hosted and managed by someone else, so no data modeling changes required there.

Microsoft Azure SQL DB isn't exactly "just SQL Server hosted and managed by someone else" - it has some strengths and weaknesses that differ from the traditional boxed product. However, if you're only building your app for a single database in Azure SQL DB (not scaling out to multiple databases), then the conventional rules apply.

Q: When does "the cloud" affect data modeling guidelines?

When you want to scale a single application across multiple databases and/or servers. This design pattern is called sharding, splitting a single identical table (or sets of tables) across hosts.

To be clear, you can do this same design pattern on-premises - it's just more trendy these days in the cloud because it's so much easier to spin up multiple servers or databases.

You noted that your database only uses 70GB per year - that's well below the numbers where I'd even remotely consider sharding.

Q: When sharding, what data modeling concerns will I have?

First, your application has to know which shard to hit in order to get the relevant data. (You don't want the app hitting the database to discover this data for every query - after all, you'd be right back up against the scalability limits of a single database server.)

You could shard by customer location - like one shard for North America, another for Europe, another for Asia. However, your customers may move between locations, or they may have locations spread across contents.

You could shard by customer name - like A, B, C, D - but you'll have some shards that are much hotter than others due to load. (Say one of your customers runs a World Cup sale - that server might fall over while the rest are idle.) And of course, customers can change names.

You could shard by activity date - like the date of sale - but of course today's shard will be ridiculously hot compared to the other shards.

By now, you're starting to see now why your question is so broad, and so difficult to answer. Your best bet to get an actionable, useful answer will be to provide as many specifics as you can. However, since you asked for a design for 70GB of data per year, keep it simple: stick with the SUN-E principles.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.