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.