Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 6 down vote favorite

We are in SSAS type of environment with 100's of clients.

Currently each client gets a database and we have 200 per instance on SQL Server 2008R2. This presents limitations with snapmanager being able to do backups as the recommended number is 35 databases per instance (netapp).

One of the solutions we are looking into is to consolidate the databases - collecting say 150 of them into one database using separate schemas.

Has anybody done anything like this?
Are there limitations on the number of schemas per database, or performance issues vs using separate databases that we should be aware of?

Thank you for your answers.

The reason we need Snapmanager is because that is what we currently use for our DR replication and their recommendation is 35 databases per instance because otherwise it might take longer than an hour to backups/transfer the data which breaks our 1 hour recovery requirement.

But according to them if we were to have 1 database(or under 35) per instance they should be able to make it no issues.

So that is why we were looking potentially into schemas but that would mean most likely at least 100 schemas per database.

share|improve this question
4  
One thing to consider for certain is how to segregate your data for fault tolerance and recovery. If you have dozens of clients in one database and something goes wrong with one client's data, how will you restore just that portion? Someone else with more experience might suggest whether having one filegroup per schema would be viable--since this would let you back up each schema independently. There also could be regulatory or compliance concerns. What if you get subpoenaed for some data--will you have to turn over all clients' data in the same database? – ErikE Feb 17 '12 at 22:26
1  
Why do you need netapp / snapmanager for your backups? In addition to ErikE's points you may also be legally required to keep some or all customers' data separate. Keeping them in separate databases also allows you to very easily move a customer to their own server or different storage should they hog resources. Combining them may ease your issues with netapp but they will just shift your problems to other areas. – Aaron Bertrand Feb 17 '12 at 23:27
@Aaron. We are relying on Netapp for DR, so we use Snapmanager for replication and since they are having issues doing snapshots within an hour (because they claim we exceed the recommended number of databases) we need to come up wih a differnt solution. Also number of cutomers keeps on growing so adding a new DB per customer may not be an ideal scenario and doing a multi-tenancy database at this point may not be doable right now. So that is why we have decided to look into multi schema. Any suggestions? – Michael Guberman Feb 21 '12 at 18:20

migrated from serverfault.com Feb 17 '12 at 22:09

2 Answers

up vote 4 down vote

One problem you'll have is with SSMS. I have a client with a lot of objects in a single database, and if we expand the Tables node in Object Explorer, SSMS dies.

As well as this, I suspect your code might need to be changed. Either you need to rely on the default schema for every connection, or else you need to change every query. Hopefully you've been using two-part naming so far, so every dbo.reference will need to change. Far from ideal.

You still have a lot of flexibility over filegroups, which is good, and having a single database might even help avoid disk head movement on the transaction log.

I doubt performance will be affected much either way. But the code changes required could be a huge frustration. And if you need to rely on the default schema, there are extra locks (LCK_M_X) that need to be taken out to work out which objects you're referring to.

I'd be really hesitant to redesign that much, but it really depends on your priorities.

share|improve this answer
up vote 3 down vote

I'm betting that if you made this change (which I wouldn't recommend) that you'd end up with NetApp telling you that they still can't replicate all that data within an hour. The reason being that basically the same number of blocks on disk will be changing so it'll still take the same amount of time to replicate the data.

What NetApp should have told you was "You need to configure the server so that XXX Gigs of data or less are being changed per hour, and no more" where XXX is some number based on your bandwidth between the sites, amount of data that needs to be transferred, etc.

Picking some random number of databases (such as 35 in your case) is a useless number to recommend. As I can easily create a single database which has 1 TB of data change per hour, so unless the network link to DR can support 1 TB of data change per hour the number of databases on the instance is meaningless.

share|improve this answer

Your Answer

 
discard

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