Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
saffie
Starting Member
3 Posts |
 Posted - 2009-01-09 : 15:36:48
|
| Hi,Currently we have 5 databases in our SQL2000 environment, alle 5 databases are used by 1 application.We have 4 databases containing orders belonging to 4 different sales departments. The 4 databases have eaxactly the same tables/definitions. Only different datasets. And we have 1 database to maintain our shared data that syncronizes with the 4 order databases.Now you might wonder why we never combined these 4 datasets into 1 db?The reason for that is that these departments have a sort of local interface running + they are only entitled to see (and update) there own datasets.... … on top of this , we have 1 application that uses combined views , it needs to querry all 4 databases at the same time ....( currently solved with VIEWS making use of unions (SELECT * FORM DB1.ORDERS UNION SELECT * FROM DB2.ORDERS UNION etc...)Currently we are making the transition towards SQL2005 and we are wondering if we could use schemas to enhance the complexity and performance… idea's tips we didn't think about ...Thanks for you input ! |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-09 : 16:05:33
|
| Didn't undestand? Can you elaborate? |
 |
|
|
saffie
Starting Member
3 Posts |
Posted - 2009-01-09 : 16:25:48
|
| I changed the question, I do hope it is more clear !Tks! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-01-09 : 16:46:58
|
| It would be better to combine into one database and use views to restrict users to their part of the data.You are giving up some really important things with multiple databases, like referential integrity, and transaction integrity. As you have already noted, cross database queries are difficult.Think about how difficult things would be with your current design if you had 10 departments or 100.CODO ERGO SUM |
|
|
|
saffie
Starting Member
3 Posts |
Posted - 2009-01-09 : 17:13:42
|
| VIews to restrict acces sto data is not an option , since they need to update the data as well ... + do not forget they do have there local interface connected to their order db...Not exactly, referential and transactional integrity are not our concerns since all tables do exist in every single db , the transaction is always applicable to 1 database ONLY ...Also the referential data has been taken care off..., the 5th db copies the reference tables to each order db, so we have everywhere the same references...These 4 databases never interfere with each other ... only we do have 1 application that needs to read the 4 dbs and combine the 4 ordertables in 1 recordset ...(READ ONLY) this is for us the big challenge .... the rest works quite conveniently .... |
|
|
|
|
|
|
|
|
Transition to 2005 / Architecture issue