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 can be done, you see it in data warehousing and document storage, but those are systems where you rarely need to select accross a boundary (year, etc). If you will often need to work with data from multiple partitions/databases it's probably cheaper to buy the license than do the coding and support of the partitioned model.

Answer 3

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.

Answer 4

The only way I would consider doing anything like this is if I was guaranteed that

1. reports wouldn't cross those boundaries, and
2. when querying data, the user would be limited to current year.

The reality is that those constraints rarely work. Consider someone wanting a 12 month report in march, from the previous march... You'll have to aggregate the results in code.

At the end of the day you are going to spend a LOT more in development time making this work than a sql standard license will cost you. EDIT: I take that last sentence back: it will cost more to do than a sql enterprise license would.

Answer 5

Have you seen the Web Edition of SQL Server? If you fit into it's licensing model, it does all of the main sql server stuff (doesn't do mirroring and can't server as a replication source, among some other limitations) but is very affordable.

Answer 6

There is no such thing as free software! :) Even if you can get around the 10GB size limitation you will still be limited by the 1GB memory ceiling. With a 10GB database that will most likely seriously impair performance (unless your query workload is very small).

Note that if you have varbinary data in your database you can store that outside of the 10GB limit by using Filestream.