Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Overflow of database volume to multiple DB servers

Posted on 2013-12-27
Medium Priority
Last Modified: 2013-12-31
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.

Question by:codequest
  • 3
LVL 33

Accepted Solution

snoyes_jw earned 1000 total points
ID: 39742872
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.

Author Comment

ID: 39743067
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.

LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 1000 total points
ID: 39743364
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.

Author Comment

ID: 39743776
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.

Author Comment

ID: 39749308
I've discovered that this is a vast and complex subject, so I'm going to close this out with points for pointers.


Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question