My opportunity is focusing on an improvement project using SQL Server 2008 Express. The quantity of data we intend to store within our primary table will rapidly exceed the 4GB size limit of Express. We are able to buy ourselves a while with SQL Server 2008 R2, but eventually we'll exceed the 10GB limitation too.
They lead really wants to hear all available alternatives before buying licenses for Standard Edition. The expertise obtainable in our organization is SQL Server and Oracle, so using MySQL or PostgresSQL would be described as a last measure.
The only real alternative I'm able to think about is really a design in which the primary table is flat partitioned into separate, distinct databases. Additionally, there will be a central database to keep the data about in which the data was saved.
For instance, all the table data for 2008 could be saved in DB_2008, 2009 data in DB_2009, and so forth. The metadata table might seem 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 employed to determine the database location from the data for the saved methods. The majority of our code already uses parameterized, dynamic SQL, which means this wouldn't be hard to implement.
Has anybody ever carried this out before?
Can there be a recognised model for this kind of design or perhaps is it simply a terrible idea?
I recognize this does not address your question exactly, but in my opinion it's always more costly to compromise up an awful kludge such as this--think dollars each hour for development and maintenance, as well as the time you've lost developing features that actually matter--rather than buy a proven method to begin with.
EDITED: And why Standard edition rather than Workgroup? If Express satisfies your feature needs, same goes with Workgroup, and it is ~$3500 less expensive than Standard. Still, either is really a bargain in comparison to saddling yourself as referred to above -- doubly if you can license by CAL rather than by Processor. :-)
It is a horrible idea. IANAL, but you might still be breaking the SQL Server license despite this plan. Installed in most types of small print about "multiplexing" and whatnot.
Even when you have this to operate, you would likely encounter nasty performance and authentication issues, and looking after the information will be a discomfort. Presuming your designers aren't effective free of charge, purchasing permission could be cheaper.
Do not do it.
The only method I'd consider doing anything such as this is that if I had been guaranteed that
- reviews wouldn't mix individuals limitations, and
- when querying data, the consumer could be restricted to current year.
The truth is that individuals constraints rarely work. Consider someone wanting a one year report in march, in the previous march... You will need to aggregate the outcomes in code.
In the finish during the day you will spend much more in development time succeeding than the usual sql standard license can cost you. EDIT: I take that last sentence back: it'll cost you more to complete than the usual sql enterprise license would.
It is possible, the thing is it in data warehousing and document storage, but individuals are systems in which you rarely have to choose accross a boundary (year, etc). For a moment frequently need to utilize data from multiple partitions/databases it's most likely cheaper to purchase the license compared to the coding and support from the partitioned model.
Perhaps you have seen the net Edition of SQL Server? Should you squeeze into it's certification model, it will all the primary sql server stuff (does not do reflecting and should not server like a replication source, among another restrictions) but is extremely affordable.
There's no such factor as free software application! :) Even when you will get round the 10GB size limitation you still be restricted to the 1GB memory ceiling. Having a 10GB database that will in all probability seriously impair performance (unless of course your query workload is extremely small).
Observe that for those who have varbinary data inside your database you are able to store that outdoors from the 10GB limit by utilizing Filestream.