November 22, 2011 at 8:13 pm
Hi All,
Good Day!
I am planning to consolidate the data from different database applications like DB2,SQL Server,ORACLE. I was thinking what is the better approach?
Approach # 1
DB2 -> SQL Server DB1 (My Server)
ORACLE - > SQL Server DB2 (My Server)
SQL Server (From different server) - > SQL Server DB3 (My Server)
Approach # 2 - store the data in one database and introduce schema to each source data
DB2 -> SQL Server DB.schema1 (My Server)
ORACLE -> SQL Server DB.schema2 (My Server)
SQL Server (From different server) -> SQL Server DB.schema3 (My Server)
Please advise..
Thanks in Advance!
November 22, 2011 at 8:36 pm
It depends on how or if the data is related. If it is related and you want to enforce referential integrity and implement Foreign Keys which can only be done within the same database or if the data isn't dependent on the other sources. If the data is related and you need to enforce the relationships I would use schemas and put them in the same database.
November 22, 2011 at 8:46 pm
Thank you Bill for the response... But what do you think the disadvantages for the Approach # 2 aside from the schema maintenance?
November 22, 2011 at 11:20 pm
MIXKI (11/22/2011)
Thank you Bill for the response... But what do you think the disadvantages for the Approach # 2 aside from the schema maintenance?
Just to list a few...
•It would be a Big Database so Big backup / restore files & requires more time.
•Also if you require taking partial backup say Oracle DB backup. It would be difficult with schema approach (but possible).
•3 different DBs can be placed on 3 different disks. Better IO.
•It would give you an isolation level. Oracle DB user won’t have any (or limited) rights on SQL Server. It can be implemented at schema level as well but I prefer database.
•For some reason (say maintenance) if you require Oracle DB to be offline but users should be able to continue their work on other DBs. Separate DBs are better.
November 23, 2011 at 12:16 am
thank you thank you... now i am able to determine which is the better approach.
November 23, 2011 at 12:24 am
Klarence A. (11/23/2011)
thank you thank you... now i am able to determine which is the better approach.
Please don't jump on conclusion so fast. Bill has a valid point of 'referential integrity'. Evaluate your needs & recommendations together before taking your final call.
November 23, 2011 at 12:37 am
I will...thanks.. I am also gathering some answers to my questions from some websites.
November 23, 2011 at 4:00 am
Good points, but you can achieve the same with schemas as well.
Dev (11/22/2011)
•It would be a Big Database so Big backup / restore files & requires more time.
You can use filegroup backup/restore.
•Also if you require taking partial backup say Oracle DB backup. It would be difficult with schema approach (but possible).
•3 different DBs can be placed on 3 different disks. Better IO.
You can have data files on different disks.
•It would give you an isolation level. Oracle DB user won’t have any (or limited) rights on SQL Server. It can be implemented at schema level as well but I prefer database.
You can grant rights at schema level.
•For some reason (say maintenance) if you require Oracle DB to be offline but users should be able to continue their work on other DBs. Separate DBs are better.
You can take individual filegroups online/offline.
-- Gianluca Sartori
November 23, 2011 at 5:06 am
Gianluca Sartori (11/23/2011)
Good points, but you can achieve the same with schemas as well.Dev (11/22/2011)
•It would be a Big Database so Big backup / restore files & requires more time.You can use filegroup backup/restore.
•Also if you require taking partial backup say Oracle DB backup. It would be difficult with schema approach (but possible).
•3 different DBs can be placed on 3 different disks. Better IO.
You can have data files on different disks.
•It would give you an isolation level. Oracle DB user won’t have any (or limited) rights on SQL Server. It can be implemented at schema level as well but I prefer database.
You can grant rights at schema level.
•For some reason (say maintenance) if you require Oracle DB to be offline but users should be able to continue their work on other DBs. Separate DBs are better.
You can take individual filegroups online/offline.
I was expecting these... 😉
#1 You can use filegroup backup/restore.
Sure. If OP is comfortable with it, go ahead. Most of us are not comfortable & avoid it.
#2 You can have data files on different disks.
Sure. More stress on File Group Management. Most of us have Primary File Group ONLY. -- Lazy DBAs 😀
#3 You can grant rights at schema level.
Sure. You might need to create at least one user as DB Owner who will take overall responsibility of database, assuming from DB3 (SQL Server). Would you allow this user to drop oracle schema objects? It can be controlled (with DENY) but complex.
#4 You can take individual filegroups online/offline.
Same as #1.
November 23, 2011 at 6:16 am
If all the DB's belong to the same company why would I be worried about having one DBO that controls all the Schema? You can have one maintenance tasks that takes care of Back up and log shipping.
You have less DBA maintenance if you have one DB over 3 DB's.
Just my 2 cents
-Roy
November 23, 2011 at 6:19 am
Klarence A. (11/22/2011)
I am planning to consolidate the data from different database applications like DB2,SQL Server,ORACLE. I was thinking what is the better approach?Approach # 1
DB2 -> SQL Server DB1 (My Server)
ORACLE - > SQL Server DB2 (My Server)
SQL Server (From different server) - > SQL Server DB3 (My Server)
Approach # 2 - store the data in one database and introduce schema to each source data
DB2 -> SQL Server DB.schema1 (My Server)
ORACLE -> SQL Server DB.schema2 (My Server)
SQL Server (From different server) -> SQL Server DB.schema3 (My Server)
When consolidating databases I would take into consideration:
1- Backup/Recovery Strategy. Can't consolidate databases that have different Backup/Recovery Strategy.
2- Business Continuity Requirements. Can't consolidate databases that have different Business Continuity Requirements.
3- OLTP/DSS. Can't consolidate OLTP together with DSS e.g. Data Warehouse databases.
4- Patching Policies. Can't consolidate databases that require different Patching Policies.
Having said all of that, converting DB2 and Oracle to SQL Server would be a serious project to undertake and you don't want to add an extra level of complexity to it therefore I would keep the original database/schema structure.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 23, 2011 at 6:32 am
Having said all of that, converting DB2 and Oracle to SQL Server would be a serious project to undertake and you don't want to add an extra level of complexity to it therefore I would keep the original database/schema structure.
One more vote for separate DBs (if I read it correctly).
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply