patternMinor
Using 100's of schemas in a database SQL2008R2?
Viewed 0 times
sql2008r2schemasdatabase100using
Problem
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.
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.
Solution
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.
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.
Context
StackExchange Database Administrators Q#13367, answer score: 4
Revisions (0)
No revisions yet.