5

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?

Beep beep
  • 1,843
  • 2
  • 18
  • 33
  • 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 08 '10 at 05:45
  • 1
    @Farseeker There are benefits to having multiple databases. The client systems are more secure and are independently recoverable. – Sam Sep 08 '10 at 15:45
  • 2
    Thousands of customers but only enough budget for one license? – John Gardeniers Sep 08 '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. – Beep beep Sep 09 '10 at 03:57
  • are usage patterns such that all 5,000 clients try to access their db's during the same period? eg. end of month, or is this spread out evenly over the month? if it's the former then a 'cloud' solution may be of benefit – Nick Kavadias Sep 09 '10 at 04:37
  • Nick - it's spread out pretty evenly over the month, never more than ~200 databases will be open at once. We cannot host this in the cloud for regulatory reasons (it's all financial transactions), unless you are referring to something different. – Beep beep Sep 09 '10 at 04:52
  • Can you give us a rough budget to work with? I don't know if you're talking 10k or 100k or what for hardware, software, etc. – gravyface Sep 14 '10 at 00:10
  • @gravyface - sorry, I thought it was obvious from the "we don't have the budget for the full SQL Standard on more than 1 server". Our budget is ~$10,000. – Beep beep Sep 14 '10 at 00:58
  • $10k? And that includes hardware and all software licensing? And you have 100 employees? Either you're making this up as you go along, or perhaps you should be focusing your efforts at careers.serverfault.com. – gravyface Sep 14 '10 at 01:12
  • @gravyface - we already own a couple servers, and could buy additional used ones if needed, but this wasn't a question about buying hardware/software ... it was a question about performance. – Beep beep Sep 14 '10 at 14:12
  • Right, but performance comes at a price -- and 10k isn't going to get you very far if you truly want a maintainable, 1k+ database infrastructure, especially if you're dismissing open source alternatives like MySQL or Postgres. – gravyface Sep 14 '10 at 14:50
  • 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. – Beep beep Sep 16 '10 at 23:17
  • @gravyface, as a follow-up, we ended up buying a $5k server + $5k sql server standard license. Working great so far on a few thousand clients. – Beep beep Jan 12 '12 at 01:00

7 Answers7

3

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.

Sam
  • 1,990
  • 1
  • 14
  • 21
  • 1
    If he can't afford more than one copy of Standard, he sure as hell can't afford Enterprise. – mfinni Sep 08 '10 at 17:42
  • So he shouldn't be made aware of features which will improve stability? – Sam Sep 08 '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 08 '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) – Beep beep Sep 09 '10 at 04:03
  • It is free. Check Biztalk - you mst likely qualify for it. – TomTom Sep 13 '10 at 22:23
2

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.

Remus Rusanu
  • 8,253
  • 1
  • 19
  • 22
  • This isn't hosting. It's a web application that connects to a database per client. – Beep beep Sep 09 '10 at 03: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 09 '10 at 21:54
2

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

SQL3D
  • 670
  • 1
  • 6
  • 11
1

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.

SqlACID
  • 2,166
  • 18
  • 18
  • 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 08 '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. – Beep beep Sep 09 '10 at 03:59
1

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.

SQL3D
  • 670
  • 1
  • 6
  • 11
1

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 https://stackoverflow.com/questions/719869/how-to-spread-tempdb-over-multiple-files

Community
  • 1
Paul D'Ambra
  • 1,072
  • 1
  • 13
  • 22
0

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).

wolfgangsz
  • 8,767
  • 3
  • 29
  • 34
  • 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). – Beep beep Sep 09 '10 at 04: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 09 '10 at 08:18
  • Our revenues are quite high, but we don't have high profits. It's pretty typical in our industry. – Beep beep Sep 09 '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. – Beep beep Sep 11 '10 at 19:31
  • @Jess: You are a service provider. You fit perfectly the definition. SPLA does not say "web hoster", it says "service provider". And using the proper program from MS you could possibly even get the licenses for free for some years (check bizspark). SPLA is totally relevant - without SPLA you actually grant illegal access to your application. – TomTom Sep 11 '10 at 19:57
  • @TomTom - I don't follow. We provide a single point of entry to a single application. This is a web application. It has multiple databases for ease of backups/restores, performance, security, and legal reasons. All databases are the same, and are treated exactly as if they were in one big database. Almost all databases will be hosted in one location, those that are not will be Express or Compact. From my understanding, our options for licensing are either SQL Server Express, or a single CPU version of SQL Server Standard or Enterprise. BizSpark isn't an option due to our sales volume. – Beep beep Sep 11 '10 at 20:04
  • Regarding my prior "I don't follow", I mean - why would it be illegal access if we purchased a per-processor license? According to Microsoft's site, SPLA's per month charges look rather expensive compared to just buying the full thing (unless we used the web edition of SQL Server, which doesn't seem much better than Express). When I spoke with Microsoft a few months back, they suggested we buy copies, that's why I'm rather confused. – Beep beep Sep 11 '10 at 20:18
  • Ok, maybe I'll call back MS. Their licensing is FRUSTRATING to say the least. I came here to ask about performance, and now I'm pulled back in the fifth circle of licensing hell. =) – Beep beep Sep 11 '10 at 20:45
  • SPLA licensing is not expensive - just use the CORRECT license. You subcontract access to your application out, so this is service provising. You will find that the normal license does NOT ALLOW THAT. My SPLA licensing price list has SQL Server 1 proc at 216,99 EUR per processor. That is not expensive. I see standard at USD 71xx + 1800 for SA, which is about 9000 USD = about 7000 EUR. And you don't need an upfront investment. And you can upgrade / change licensing every month depending on needs. Your licensing HELL is the most provider friendly licensing I have ever seen from a vendor. – TomTom Sep 12 '10 at 02:57