June 8, 2009 at 4:00 pm
Hi Guys,
Thinking of designing a Core Database and a DB each for a customer. There a web site that serves the customers. All the non-customer specific data will be tracked in Core DB and customer specific DB will be tracked in their individual DB.
At this point, we do not want to re-write SPs for each customer.
So, I am thinking of Dynamically passing the DB name and builing the SQL on the fly inside the SP and executing.
My question is, Is it a good practice? What are the Performance & Maintenance impacts?
Any comments are welcome.
Thanks a lot in advance.
June 8, 2009 at 4:32 pm
Why not put everyone in the same DB and key everything on customer?
If you have a lot of customers, the management overhead of the design you are proposing becomes pretty heavy. I've seen it work, but the DBAs spent a lot of time writing automation scripts that propogated changes to multiple customer databases, monitored backups, and gathered performance statistics for tuning purposes.
June 8, 2009 at 5:20 pm
Reddy B (6/8/2009)
So, I am thinking of Dynamically passing the DB name and builing the SQL on the fly inside the SP and executing.My question is, Is it a good practice? What are the Performance & Maintenance impacts?
No, do not do this. I am a big supporter of dynamic SQL but this is a poor use for it. There are some performance issues, but they are minor compared to the maintenance and security issues:
Maintenance: harder to read, harder to edit, much harder to debug and troubleshoot.
Security: First, unless constructed very carefully, dynamic SQL procedures are easy targets for SQL Injection attacks (and most folks do not know how to construct them correctly). Secondly, cross-database security is a PITA with dynamic SQL. SQL Server is going to throw obstacles in front of your every step of the way. And most of the easy ways to get around these obstacles will just increase your exposure to Injection attacks.
At this point, we do not want to re-write SPs for each customer.
This is the decision that's pushing you in the wrong direction. DO write SP's for each customer/database. Just do NOT re-write or write custom SPs for each one.
There are several different, relatively easy ways to create and maintain a set of identical stored procedures across multiple databases. This approach is much more performant, maintainable and WAY more secure.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 8, 2009 at 5:33 pm
David Webb (6/8/2009)
Why not put everyone in the same DB and key everything on customer?If you have a lot of customers, the management overhead of the design you are proposing becomes pretty heavy. I've seen it work, but the DBAs spent a lot of time writing automation scripts that propogated changes to multiple customer databases, monitored backups, and gathered performance statistics for tuning purposes.
I assume that you are talking about Shared Schema, Multi-tenancy. I have written a couple of articles at my blog about multi-tenant applications and DB design here and here.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 8, 2009 at 5:44 pm
Yes, and your articles do a wonderful job of outlining the necessary structure and processing requirements. Those are far easier to live with than the requirements around keeping multiple databases in sync. and doing impact analysis on code that isn't materialized except at run time. (Imagine the mess when your customers don't want to upgrade at the same time...)
My experience with this was on a very large (for the time) database of money managers (for pension funds) and we tried the OPs idea but quickly gave it up as a maintenance nightmare. This was on SQL Server 4.2 on OS/2, way back when I had hair.
June 8, 2009 at 6:23 pm
There are good reasons to go both ways (any of the three ways, actually), it depends on you needs and goals. The advantage of the multiple DB approach is that you can manage them separately. The disadvantage is that to some extent you have to manage them separately. And this is the essential trade-off that you face with the DB design of multi-tenant applications: flexibility vs. maintenance overhead.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 8, 2009 at 8:03 pm
Thanks a lot, Barry. I really appreciate all of your help. You blog pointed me to the right direction.
Great Job.
-B
June 8, 2009 at 8:04 pm
Thanks a lot, David. I really appreciate your time.
- B
June 8, 2009 at 9:39 pm
Reddy B (6/8/2009)
Thanks a lot, Barry. I really appreciate all of your help. You blog pointed me to the right direction.Great Job.
-B
Enquiring minds want to know... which direction did you take?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 9, 2009 at 12:15 am
Reddy B (6/8/2009)
Thanks a lot, Barry. I really appreciate all of your help. You blog pointed me to the right direction.Great Job.
-B
Glad we could help. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 9, 2009 at 6:41 am
Did not decide the final option yet.
However thinking of, Multi Tenency DBs in one Schema.
Here is what I am thinking - Please be brutal on criticism. I want real good feedback... 🙂
Planning to create a DB for each Customer
Going to Have Three Basic tables - that serves all the customers (This will be in the Main DB.)
One with ALL Customer information along with their DB Name
Second with Customer's User Login/Password information
Third, Common table with List of Values for States etc.
Once the user logs in, based on the user id, we will create dynamic ODBC connection in Web.Config for the session to get Customer Specific data.
What do you think of this? Please give me feedback on this.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply