We currently have a product in which each client has their own Database. We adjust the connection when a user for that client logs into the system. This system has continued to grow and a good pace, but we have come to a point where failover is taking too long.
Refactoring the Database to handle multiple sites in a single database is not an option because of the time it would take to make the change. So, we are looking for another way in which this could be handle. One idea is to take multiple clients and place them in a single database using a schema to seperate them. (ex. Client A = server1.db1.schema1, Client B = server1.db1.schema2, etc).
Is there another option that would be better, or what kind of performance can we expect if we follow this path? Or, is there a way to decrease the failover time (it appears the problem is the startup of the database on the failover server)?
Thad
You are right that having a large number of databases will increase your fail-over time in a fail-over cluster.
Since you mentioned schemas, you must be on SQL Server 2005. For fail-over time, database mirroring is much faster than fail-over clustering. Database mirroring does require twice the storage space, and the licensing situation is not good compared to an Active/Passive cluster. You would have to mirror each database individually.
Are you failing-over for maintenance, rolling upgrades, etc.? Maintaining and adding dozens of schema in a database would be cumbersome and prone to error. It also has some performance drawbacks, since if you don't properly schema qualify a SP name (from code), or a table name (in an SP), you will have name resolution problems and cache misses.
Depending on the numbers of clients you are talking about, I would think about getting another cluster to split the load.
|||Currently the failover system is done for disaster recovery scenarios. As for performance. All our inline sql from the client class generate sql similar to
Code Snippet
SELECT [Schema].[Table].[Field] FROM [Schema].[Table]While a stored procedure is set is setup so it exists in each schema, but the schema name is not used in the sproc body. This is the same method we would use with Views and Functions.
Code Snippet
CREATE PROCEDURE [Schema].[ProcedureName](@.Param INT) ASBEGIN
SELECT * FROM [Table]
END
Would setting up the schema in this way cause these performance drawbacks. I am thinking that maintaining multiple schema will be as error prone as setting up the single db for each client.
Currently we are talking about approx 1000+ clients.
|||If your client code can pick up the schema name dynamically from a config file or something, then having a schema for each client as opposed to a database for each client will probably be not much different from a maintenance and configuration perspective.
There may be some negative effects on your buffer cache and procedure cache from this approach. Instead of having one large, normalized table that holds data for all your clients in a single database, you will have one table for each client. You will have multiple copies of the same SP (for different schemas) in your procedure cache.
|||Currently the client already selects the Database so we would just need to add a schema selector to the data selection. So all that is straight forward. And yes we are duplicating the entire database inside a schema.As for the procedure and data cache, which I am not completely familar with, would it not be the same as multiple database?
I can that this is not the best solution, but I think it may be the best solution given the time and other hardware constraints we have. So, it appears that there are little differences in performance and maintenance that we already deal with.
