Can you bypass the size limit of SQL Server Express by stacking databases?

Question

My company is working on a development project using SQL Server 2008 Express. The amount of data we plan to store in our main table will quickly exceed the 4GB size limit of Express. We can buy ourselves some time with SQL Server 2008 R2, but eventually we will surpass the 10GB limitation as well.

The team lead wants to hear all available options before purchasing licenses for Standard Edition. The expertise available in our company is SQL Server and Oracle, so using MySQL or PostgresSQL would be considered a last resort.

The only alternative I can think of is a design where the main table is horizontally partitioned into separate, distinct databases. In addition, there would be a central database to store the information about where the data was stored.

For example, all of the table data for 2008 would be stored in DB_2008, 2009 data in DB_2009, and so on. The metadata table might look like this:

PKStartDate  PKEndDate   DBName
-----------  ----------  ----------
2008-01-01   2008-12-31  DB_2008
2009-01-01   2009-12-31  DB_2009
2010-01-01   2010-12-31  DB_2010

This table would be used to determine the database location of the data for our stored procedures. Most of our code already uses parameterized, dynamic SQL, so this would not be difficult to implement.

Has anyone ever done this before?

Is there an established model for this type of design or is it just a horrible idea?

Answer 1

I realize this doesn't address your question exactly, but in my experience it's always more expensive to hack up a nasty kludge like this--think dollars per hour for development and maintenance, plus the time you've lost developing features that really matter--than to buy the right tools in the first place.

EDITED: And why Standard edition instead of Workgroup? If Express satisfies your feature requirements, so will Workgroup, and it's ~$3500 cheaper than Standard. Still, either is a bargain compared to saddling yourself as described above -- doubly so if you can license by CAL instead of by Processor. :-)

Answer 2

It's a horrible idea. IANAL, but you may still be violating the SQL Server license even with this scheme. They put in all sorts of fine print about "multiplexing" and whatnot.

Even if you got this to work, you could very well run into nasty performance and authentication issues, and maintaining the data would be a pain. Assuming your developers don't work for free, buying a license would be cheaper.

Don't do it.