Dealing with multiple products with the same database?

Question

Dealing with multiple products with the same database?

mda

Grasshopper

Points: 11
More actions
June 28, 2012 at 3:37 pm

#263087

Hi, I have an architectural question..
We are a healthcare company and working with various products (around 30 now). Currently each product has their own SQL Server Database with almost similar schemas except minor changes.
Now we are re-building the UI applications for these products and also would like to do the database changes too. We are thinking of having a One Unique Database(By adding ProductId column) for all the products instead of 30. So our question is if we go with a One Database, does it scale? How will be the performance? Is this a good approach? what are pros and cons?
Right now with 30 different databases, we are already feeling the pain, because any schema change, we need to do it on all 30 different databases which is really cumbersome. So please suggest any other alternate approaches too.
(One of my friend worked in Oracle for the similar situation, but in ORACLE, they have partitioned the databases with different schemas and logins for various products. Is there something similar in SQlServer?)
Appreciate your reply.
Thanks..

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

It is a big topic. Here is an architecture document Microsoft produced in 2006 that I can recommend to you to help familiarize yourself with the options. It is based on SQL 2005, however the concepts are still relevant today, even for SQL Server 2012:

Multi-Tenant Data Architecture

The summary is that SQL Server is built to scale up, not necessarily out. So with proper hardware provisioned, proper indexes and well-written SQL code SQL Server will be more than up to the task of hosting a single database that originated from your 30 separate databases.

To answer your question about Oracle schemas, SQL Server supports schemas, i.e. you can have 1 database with many schemas within it. However, that is not what a multi-tenant database means. If all your objects are currently in the dbo schema in your 30 databases then you could port all 30 of your databases into 30 different schemas into a single SQL Server database. But, you would still have 30 of the same table, one in each schema, so that does not help you solve your issue with having to modify the same table 30 times to make a system change, it just switches it from 30 table changes in 30 separate databases to 30 table changes in 30 different schemas in a single database.

SQL Server Schemas

In a multi-tenant database you will have 1 table to accept the data from 30 tables (one from each of your 30 databases). Then, in the multi-tenant setup you will access your data in that 1 table using the chosen key to interact with in that sessions. In your case that key sounds like it would be your product ID.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

mda (6/28/2012)
So our question is if we go with a One Database, does it scale?

Better than you can imagine.

How will be the performance?

As with any code, it depends on two things... how you design the database and how well the team writes code. Even a fire-breathing monster of a server can be brought to its knees by bad code. Size has little to do with it.

Is this a good approach?

Yes... consider the pain you're going through right now and then reduce that to at least 1/30th of the pain not only in coding, but in maintenance, as well.

what are pros and cons?

You are already keenly aware of both. The pros are that you'll get rid of most of the pain you're currently feeling. The cons are that you may be required to do something else because you'll have a lot of extra time on your hands once the change has been made.

Right now with 30 different databases, we are already feeling the pain, because any schema change, we need to do it on all 30 different databases which is really cumbersome. So please suggest any other alternate approaches too.
(One of my friend worked in Oracle for the similar situation, but in ORACLE, they have partitioned the databases with different schemas and logins for various products. Is there something similar in SQlServer?)

REALLY bad idea for this, IMHO. You'll be right back where you started with maintaining 30 differnet copies of code, 1 for each schema. You could overcome that with dynamic SQL but you've not seen real pain until you try to maintain Enterprise Level code that's all dynamic.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gullimeel Hall of Fame Points: 3871 More actions · Answer 3

(One of my friend worked in Oracle for the similar situation, but in ORACLE, they have partitioned the databases with different schemas and logins for various products. Is there something similar in SQlServer?)

It is same as 30 databases. The pain will be same.If you think the table sizes will be large then you might consider partitioning the tables based on productid and there will be around 30 partition in that case.

GulliMeel

Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]

mda Grasshopper Points: 11 More actions · Answer 4

Thanks opc, the Multi-Tenant Data Architecture is a useful architecture which explains all 3 scenarios.