October 3, 2011 at 11:35 pm
I have many clients for the same application that I am developing. Cost of creating multiple databases, hosting, backup/maintenance etc is overwhelming initially. So I am thinking of using Multiple Schemas and Same database. There is another database which hosts some common information (this is separate). DB1 will have common db. and DB2 will have schema1, schema2, schema3---scheman for all the clients. I plan to have same schema structure for all clients(same table names, same stored procedure namess).
Idea behind separate schema is so that each client will have data isolation and restoring the data would be easier (something that will behave almost like separate database...sort of! )
1)I'm using .net - is there a way I can configure schema name as a part of connection string.
2)Is there any downside to creating multiple sql users (1 for each schema) and setting up the default schema name. So when from the webapp when a user logs in, I will accordingly decide wihch SQL User login is to be used to connect to the DB, use that sql login for connecting, and it would automatically connect to the default schema associated with that sql login. This way I can avoid using Schema names inside the stored procedure.
3)If I need to add schema name inside the stored procedure,
eg. Select * from Schema1.table1
that would make it dynamic sql - correct? Please help! I am trying to avoid passing schema names as a parameter to the stored procedure and thinking of duplicating stored procedures for each schema for this reason. Am I going in the right direction with this -- Kindly help me!
Thanks
October 3, 2011 at 11:53 pm
Your biggest issue is going to be restoring a single clients information if only one client has a data lose issue. Be sure you have thought this through and how you will deal with individual client data recoveries. You don't want to take down other clients that are working correctly.
If each client connects through your web application with a different user, yes each user can have its own default schema.
October 4, 2011 at 2:50 am
1)I'm using .net - is there a way I can configure schema name as a part of connection string.
Dev:: I am not sure on this but if you have separate login accounts for your customers it should not be an issue.
2)Is there any downside to creating multiple sql users (1 for each schema) and setting up the default schema name. So when from the webapp when a user logs in, I will accordingly decide which SQL User login is to be used to connect to the DB, use that SQL login for connecting, and it would automatically connect to the default schema associated with that sql login. This way I can avoid using Schema names inside the stored procedure.
Dev:: Off course no issues (except User Access Management). You may create multiple users (with default schema). BUT you may map only one login with one and only one db user.
3)If I need to add schema name inside the stored procedure,
eg. Select * from Schema1.table1
Dev:: I don’t think it's required. For Instance, you have two users (and logins and schemas) ‘A’ & ‘B’. You also have a common schema ‘C’. A table ‘x’ is there in all the schemas. When user ‘A’ queries for table ‘x’ he will get data from table ‘A.x’ by default. Wherever you need to access common tables you may use qualified names 'C.x'.
October 4, 2011 at 7:00 am
~Dev~ (10/4/2011)
1)I'm using .net - is there a way I can configure schema name as a part of connection string.Dev:: I am not sure on this but if you have separate login accounts for your customers it should not be an issue.
2)Is there any downside to creating multiple sql users (1 for each schema) and setting up the default schema name. So when from the webapp when a user logs in, I will accordingly decide which SQL User login is to be used to connect to the DB, use that SQL login for connecting, and it would automatically connect to the default schema associated with that sql login. This way I can avoid using Schema names inside the stored procedure.
Dev:: Off course no issues (except User Access Management). You may create multiple users (with default schema). BUT you may map only one login with one and only one db user.
3)If I need to add schema name inside the stored procedure,
eg. Select * from Schema1.table1
Dev:: I don’t think it's required. For Instance, you have two users (and logins and schemas) ‘A’ & ‘B’. You also have a common schema ‘C’. A table ‘x’ is there in all the schemas. When user ‘A’ queries for table ‘x’ he will get data from table ‘A.x’ by default. Wherever you need to access common tables you may use qualified names 'C.x'.
There is no problem setting up multiple sql logins and users mapped to those logins with their own default schemas.
Yes, you could leave off the schema for the table names in the stored procedures. Only problem you will have is that you will not get the benefit of chaced plan reuse which could affe3ct performance of your application as each executioon of the procedures would force a recompile.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply