Overflow of database volume to multiple DB servers

I am looking to understand generally how SQL, MySQL and Oracle maintain identity field uniqueness where the database overflows the capacity of a single physical or virtual database server.  If I have a "bigint" ID field that is the identity field and start running into a volume or performance issues, what happens?

Any guidance or pointers to articles on this would be appreciated.

Who is Participating?
You can use a GUID. It generally includes some identifier of the host or hardware that it's running on to guarantee uniqueness across systems. It's not a bigint anymore, though - generally some kind of string.
codequestAuthor Commented:
Thanks for the input.  I think I get the general idea about GUIDs.  I may have not phrased my question well, being essentially a newbie in this area.  So...

The application I'm focusing on is in ASP.NET.  The data is highly compartmentalized, i.e. consists of many multi-table data sets, ranging from approximately 1MB to 10 MB each, organized under a subscriber ID, with no relationship between subscribers (or maybe they would be related through some "company" table at some point;  I'm making allowances for that).  Only about 100KB of each subscriber data set would be active at any one time, and each of these 100KB units might be getting an average of 1 write per second.  Reads would be almost entirely from cache memory;  full 100KB loads to cache would happen only once an hour or so.    Few higher volume reads would occur other than for application maintenance.

Suppose I'm hosting this application on some large DB infrastructure, such as provided by a service like AWS or Azure.   I'm assuming that long before I run out of bigint identity values for subscriber ID, at some point I might run into performance and capacity problem running off a single copy of an .mdf file, and have to transfer operation to multiple .mdf files, using a variety of strategies.  

I'm assuming that for such services, the size of the single .mdf file, and number of accesses that could occur against that file without performance degradation, could be very large, perhaps supporting thousands of subscribers (10GB, 1000s of writes per second).

In this framework, it seems like my alternatives are:
A) make DB design choices now about problems that I might run into if (should I be so fortunate) the application has hundreds of thousands of subscribers (1TB, 100,000s of writes per second).  Like for example (I now invent things) make the subscriber ID a GUID and maybe add it to all the tables in a subscriber data set, not always as  foreign key, but as a means of physical partitioning.   Maybe do the same for Company ID.


B)  Use bigint for my subscriber identity ID, let the AWS/Azure type service give me as much volume and performance scaling as possible on a single .mdf file, and hope that I can pay for the more complex DB redesign to meet scaling needs when I hit the capacity issues later.

In either case the application logic for handling multiple .mdf files would be written later.

So may the real question is, how difficult is it going to be for a standing start newbie like myself to do an effective job on alternative A?

Which reduces to, what are some sources for learning more about alternative A?

Because right now this is the only major stumbling block in proceeding with my detailed DB design.  So I'm sore tempted to put it off if I can get away with it.  However, depending on how difficult and important it is, I'll bite the bullet and do the best I can with it.

Any guidance on this would be appreciated.

Surendra NathTechnology LeadCommented:
OK, there are few pointers I can help you with...

1) Instead of GUID, if you are already using SQL Server 2012 or Oracle, start using Sequence every where....
     A sequence is an object which will give a unique number for your application code when requested and it will not generate the number again.

oracle : http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_6014.htm
sql server : http://technet.microsoft.com/en-us/library/ff878091.aspx

2) once you create the sequence, then use the data paritioning in SQL Server to get multiple .MDF files.
codequestAuthor Commented:
Thanks for the input.   At this point I'm pretty sure I need to study the question of "DB design for scaling" in more detail.

Any pointers to articles on this subject, or even the key search terms, would be appreciated.
codequestAuthor Commented:
I've discovered that this is a vast and complex subject, so I'm going to close this out with points for pointers.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.