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 |
|
Soulweaver
Starting Member
18 Posts |
 Posted - 2002-10-10 : 15:32:46
|
| A proposal was raised by our esteemed management that to solve our multiple server synchronization issues and to lower licensing costs it would be a fantastic idea not to consolidate all the databases onto one instance of sqlserver, but rather , consolidate all the databases into ONE database.This would be done using the owner prefix of the application to identify tables within this database.Instinct tells me this would be a rather bad idea, nightmares in administration, security and maintenance not withstanding. However, management feels this isn't a strong enough argument not to go the route of the single database.Does anyone have any concrete evidence / ideas / articles to justify our resistence to this somewhat bizarre idea. ( i can almost feel a lot of flaming coming ;)-----------------------Black Holes exist where God divided by Zero----------------------- |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-10 : 15:52:41
|
quote:
Instinct tells me this would be a rather bad idea, nightmares in administration, security and maintenance not withstanding
Your instincts are pretty sound, IMHO. The security issue alone is enough to sink this, unless absolutely every user is allowed to have absolutely unfettered access to the entire database. If not, you'll spend more time handling user and role administration than developing the system. Not to mention that if the end users/clients are supposed to manage users, you'll have to add an entire raft of bullet-proof tools for them to use.Also, if there needs to be any maintenance releases on stored procedures, tables, views, etc., it will all go FUBAR the second some developer forgets to include the owner name when they use the CREATE or ALTER statement...and someone WILL forget. This could very easily lose data, possibly forever.Also, if you want to backup the various modules individually, you'll have to go through extensive filegroup maintenance in order to separate the tables from one another. The additional overhead of this is also enough to scotch the idea. Granted, if this is not an issue then it will simplify things like backups and restores, but make it almost impossible to tune performance when the tables get big. Not to mention completely destroying the possibility of useful and effective log backup strategies.Overall, I agree with you that it's a really bad idea. Assuming SQL Team's reputation is not enough to convince them  you should force THEM to justify why it's better to have a single DB, and use the above mentioned issues as a guideline; make them tell you how they are going to get around them. |
 |
|
|
jeroena
Yak Posting Veteran
76 Posts |
Posted - 2002-10-10 : 16:22:50
|
*slap*  Sounds like some member in your management team is a former Oracle developer... Oracle works a little bit like that, but has features to support it, like tablespaces and such.If this is the case, explain that SQL Server architecture is slightly different from Oracle's.  What on earth is wrong with multiple databases on one SQL Server instance?Good luck, Jeroen. |
|
|
|
SKIBUM
Starting Member
32 Posts |
Posted - 2002-10-10 : 16:55:27
|
I agree with Jeroen. It DOES sound like this is right out of the Oracle Universe. SQL was designed to allow multiple databases for the purpose of separating applications. Good Luck.  |
|
|
|
Soulweaver
Starting Member
18 Posts |
Posted - 2002-10-11 : 03:46:53
|
| Thanks Guys, and yes, you are right when it comes to prior DBMS's, the main focus there used to be DB2 and Oracle, and yes, both has tablespaces.I have played with filegroups, and it IS a nightmare, especially to keep everything in sync.Again thanks, They should know (doesn't everybody?) that SQLTEAM is da bomb! ;)-----------------------Black Holes exist where God divided by Zero----------------------- |
|
|
|
|
|
|
|
|
Multiple Applications' Tables in One Database