Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We are building an application where each client will have their own database. None of the databases are particularly large (20MB to 400MB each), but there will be ~5,000 to start and at any one time 100 or so will be active.

Our team has been debating how best to set up the system. Clients only access their database once every 2 weeks (401k/finance processing), and only use it for 10-30 minutes at a time. Operations are evenly spread between reads/writes.

Half of our team feels that as a result we should spread the databases across multiple cheap servers and just use SQL Express ... they say that memory/caching wouldn't be as useful given the short period of time each database is used (we don't have the budget for the full SQL Standard on more than 1 server).

Is this the case? A higher memory limit is really the only advantage I see MSSQL Standard bringing us (we already have scripts for doing backups/restores, schema upgrades, migrating data, etc).

Update

I'm particularly interested in performance characteristics of multiple databases vs. one database. Wouldn't the end user experience be better hitting a single 200MB database than a 1TB database (even if both were well indexed)? This also means we can easily backup/restore single client databases very very fast, right? Would we need to tune SQL Server to better handle the 'thousands of databases' scenario?

share|improve this question
9  
I'm trying to say this politely... but you're discussion how to distribute thouseands of individual databases in your team, but did your team perhaps discuss any ways to avoid thousands of individual databases? This sounds like it will be an absolute nightmare for anyone involved. –  Mark Henderson Sep 8 '10 at 5:45
1  
@Farseeker There are benefits to having multiple databases. The client systems are more secure and are independently recoverable. –  Sam Sep 8 '10 at 15:45
2  
Thousands of customers but only enough budget for one license? –  John Gardeniers Sep 8 '10 at 19:30
    
Most of our customers are relatively small - we have under 100 employees and only a handful of servers. The reason for so many databases is security and the ability to easily allow clients to host their own databases. These are financial transactions we're dealing with, so security is definitely the #1 issue. –  Jess Sep 9 '10 at 3:57
1  
@gravyface - all of our tests with PostgreSQL showed that performance was ~50% of our MS SQL (using same configuration, databases, etc ... all SQL is ANSI SQL so no "tuning" was done). We would have ended up spending far more on hardware, so just went with MS SQL. There's no reason we can't change it though, we only have 2 or 3 stored procedures. –  Jess Sep 16 '10 at 23:17

7 Answers 7

up vote 3 down vote accepted

I'd throw it all on one server. Maintaining the several cheap sql express servers will be a pain. You can spread databases, logs and the temp database across different RAID disk arrays. You should consider moving your temp database to it's own array as it may be in use by all the databases at once.

Check out the 2008 resource governor to make sure no one user will bring the server to a crawl. It is only in the enterprise version though.

share|improve this answer
1  
If he can't afford more than one copy of Standard, he sure as hell can't afford Enterprise. –  mfinni Sep 8 '10 at 17:42
    
So he shouldn't be made aware of features which will improve stability? –  Sam Sep 8 '10 at 18:19
    
Not that he shouldn't be made aware of it, but it read like you were suggesting that as part of your solution, when he's said that's technology that he can't afford. –  mfinni Sep 8 '10 at 20:31
    
SQL Server Enterprise would be great, but is more expensive than our entire software/hardware budget for a year. We're a small company. BTW, I'm a she (Jess) –  Jess Sep 9 '10 at 4:03
    
It is free. Check Biztalk - you mst likely qualify for it. –  TomTom Sep 13 '10 at 22:23

If you offer hosting for multi-tenant client access you also have the option of volume licensing as a Service Provider, which can turn out to be significantly cheaper than buying per-CPU Standard license, and the cost can effectively pass on onto your service per-month pricing.

Deploying multiple SQL Express instances for hosting is actually against the recommendations.

Although is a bit outdated with regard to SQL 2008 and R2, I recommend reading the white paper SQL Server 2005 Deployment Guidance for Web Hosting Environments, the guidance applies to other hosting and multi-tenant environments, not only to Web hosting.

share|improve this answer
    
This isn't hosting. It's a web application that connects to a database per client. –  Jess Sep 9 '10 at 3:58
    
Web applications that connect to a database per client can be categorized as service provider, which is hosting (you host the client's database and can pass on the license cost to the client, based on SPLA terms). The term 'hosting' is more general that simple web hosting. –  Remus Rusanu Sep 9 '10 at 21:54

Jess,

Without knowing more in regards to your environment or usage patterns, I will say that you may see improved performance with each client having their own database (so thousands of smaller databases vs. 1 large database). You could potentially reduce the chances of table and row locking, as clients will only be hitting their own unique set of tables, versus sharing a set of tables. Disk I/O would still be a limiting factor however.

Also, security would be clearer, as each database would have its own unique set of permissions for each client. As you stated, backups and restores would be a lot faster with the smaller databases, but the setup and maintenance of these backup jobs would be extremely complex (but it sounds like you have already accounted for that).

If you have the hardware, I would highly recommend setting up different RAID arrays for your Data, Logs, and TempDB (as Sam has suggested). If you are using some kind of Direct Attach Storage or SAN, and you can afford extra arrays, you may even consider splitting up the actual files for your databases onto different arrays.

HTH, Dan

share|improve this answer
    
Thanks Dan, this was really helpful. –  Jess Sep 14 '10 at 0:59

Your biggest challenge will probably involve backups; if you go SQL Express, you have no Agent to run them, you'll need to rely on Windows Scheduled Tasks and some fancy scripting.

If you use SQL Std/Ent Edition, and you try to use the built-in maintenance plans to backup all databases, it will do them one at a time and could take a while. Same for log backups.

Don't even think about using mirroring with that many databases on a server.

I would lean towards more servers, with a well thought out failover strategy.

share|improve this answer
    
For backups, if he's using any reasonably-enterprise backup software, they can often have pre-execution tasks so that you can run SQL dumps prior to the tape run, no need for scheduled tasks or further scripting. The tape run won't fire until the script has completed. Definitely on-point about mirroring, even log-shipping would have a problem. With 64-bit and playing with the threads settings, it might be feasible but probably not pretty. –  mfinni Sep 8 '10 at 17:43
    
Actually, we have a service that runs the backups after a client submits their changes but before we process them. This also makes it REALLY simply if a client realizes they made a mistake ... they simply restore the database within a certain period of time, make their changes, and resubmit. –  Jess Sep 9 '10 at 3:59

Another consideration you should keep in mind is the peripheral costs of maintain thousands of SQL databases spread across "multiple cheap servers". With the increase in the number of servers, your rack space requirements increase, as well as your costs to power and cool your data center with all the additional hardware. Not to mention the increase in administrative costs/time in having to maintain multiple servers versus one server.

share|improve this answer
    
That's a really good point –  Jess Sep 9 '10 at 21:41

One thing to consider here is adding multiple files to tempdb and to db files spread across multiple disks. SQL can then spread read & write across the spindles... see http://stackoverflow.com/questions/719869/how-to-spread-tempdb-over-multiple-files

share|improve this answer

Please don't take this wrong. You may have good reasons for doing what you are doing. From where I am, I cannot see why anybody would want to have several thousand databases. It looks to me like there is a design flaw in your application and now you are looking for ways of overcoming it without fixing it. I would seriously recommend reconsidering the fundamental database design.

Having said this: If there are good (possibly legal) reasons to have so many databases, consider this: A decent MS SQL Server can easily handle several hundred small databases (500 x 400MB = 200GB of data, which is not a problem). That should generate more than enough income to not have to worry about additional SQL Server licenses. You could then split them simply by naming or any other method you choose.

Or, you might consider using MySQL, where licensing is not such a problem (although I realise that this isn't an option if you make heavy use of stored procedures).

share|improve this answer
1  
"More than enough income" - I wish it was that easy. Our margins are razor thin. With 5,000 clients, we don't even have the money to buy an Enterprise copy of SQL Server (~$20,000). We did a pretty extensive performance eval of databases, and found Oracle and MS-SQL blew away MySQL and PostgreSQL for our usage (heavy inserts, relatively complex queries). Our reasoning has been security, ease of development, ease of doing fast backups/restores, and legal (we deal with financial transactions). –  Jess Sep 9 '10 at 4:06
    
Hmm, I can see your reasons for independent database. However, are you sure your business model is viable? You have 5000 customers and you can hardly afford an Enterprise license? Doesn't sound like a great money spinner to me. –  wolfgangsz Sep 9 '10 at 8:18
    
Our revenues are quite high, but we don't have high profits. It's pretty typical in our industry. –  Jess Sep 9 '10 at 21:42
    
Check SPLA licensing - the licenses are a lot cheaper under SPLA. –  TomTom Sep 11 '10 at 15:16
    
@TomTom - We're not a hosting provider, and our clients are all accessing these databases through a single web application (so we only need 1 license). SPLA isn't really relevant. –  Jess Sep 11 '10 at 19:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.