September 30, 2011 at 4:27 am
I have multiple clients that need access to a common database and they have their own separate tables - so I plan to use shared database and separate schema. Between using separate stored procedures and building dynamic SQL with schema name based on the user - which one is better based on performance?
My application has two components - component 1 is common to all clients. So, I also have some stored procedures and tables that would be common. component2 would access their own tables (I plan to keep the names of the tables same to make it easier for the application to access them) - but in separate schema. So while accessing the common stored procedures and tables, do I need to add dbo.tablename or dbo.stored procedure name for those.
Need some input to figure out if I am going in the right direction with this.
Kindly give your thoughts on this.
September 30, 2011 at 8:26 am
Some others may weigh in with their opinions but I think you will find it easier to have a separate database for each client in the long run. From the way you are describing this your entire system would have to run off dynamic sql. That could become very tedious to maintain as the system gets larger and more complicated. Dynamic sql is really powerful but it can also be very slow if not managed well. It is also a lot more difficult to test/debug. Think about how difficult researching a reported bug might be if you need to peruse the data.
You could even keep the common data in a common database. This type of architecture has its drawbacks too as you have to update stored procs and such for every client database when a change is made but this I think would be less of a problem in the long run.
Just my 2¢.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 3, 2011 at 12:43 am
Either you go with multiple schemas or multiple databases the DEV effort would be the same. However I see few manageability benefits in both the approaches.
If you choose one database & multiple schemas, it would be easy for DBA to maintain application DB. As all the database objects reside in same database, one DB backup / restore operation would be sufficient.
On the other hand, choosing multiple databases has its own advantage which Sean has already mentioned. In long run the maintenance of stored procedures would be a pain, if they increase in numbers.
October 3, 2011 at 1:00 pm
I wouldn't do multiple schemas. My preference would be multiple databases which could include one database for the common data. Synonyms could make referencing the objects in the common database easier to manage. My main reason for this is, if one client has a problem or deletes some data, you'd have to bring down all your clients in a shared database to restore while the multiple database scenario removes this.
There are always trade-offs when working in a multi-tenant situation.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply