June 5, 2009 at 1:28 pm
This will seem like a strange post, but I am looking for articles, documents, briefs, etc. on why you should not have multiple copies of a database on multiple servers. This database stores very sensitive data. The IS Dept. want to reduce this number for the sake of security, development, and efficiency. The department that owns this data disagrees and has always dictated what happens with this database. They think IS is trying to take over their system. We simple want to improve the setup and security of this system's database.
Currently, there are 10 copies of the database other than production. The production server has 3 copies (including production), staging server has 3 copies, development server has 3 copies, 1 copy on warehouse server, and 1 copy on warehouse development server. That is a total of 11 copies! ....and they can't explain why they need all of these.
As the company DBA this troubles me greatly. I am currently looking for articles, etc. to support my argument for reducing the number of copies of this databases. Any website, whitepages, etc. would be greatly appreciated. I want to show as many different sources as I can that support my argument.
Thank you.
June 5, 2009 at 2:58 pm
three production copies?... all in the same server?
Do all of them hold the same data?
How do users choose to which database connect?
Are you kidding? 😀
_____________________________________
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.June 8, 2009 at 6:41 am
Yes....3 production databases on the same server. The users can only connect to one and then the backup of that one is restored to the others. For some reason this is how this department has always done it.
As far as I can tell they want to be their own mini IS department. However, we are responsible if something goes to hell.
June 8, 2009 at 6:54 am
You can mask the sensitive data in the non-production environment and that should make everyone happy (IS and Users)
June 8, 2009 at 7:05 am
Erin (6/8/2009)
Yes....3 production databases on the same server. The users can only connect to one and then the backup of that one is restored to the others. For some reason this is how this department has always done it.As far as I can tell they want to be their own mini IS department. However, we are responsible if something goes to hell.
If they only connect to one of the databases in production I am at a loss to see what the other two are for. Are they under the impression a backup means having another copy of the database on-line?. If so an explanation of how the backup\restore process is used for recovery purposes, or how a standby database should be on a different server.....................
---------------------------------------------------------------------
June 8, 2009 at 7:21 am
It's not unusual for another copy of the production database to be placed in the production environment as part of the nightly backup routine, then this second copy be used for heavier reporting / ad hoc queries where the previous day's data is enough. This is unusual when there is a data warehouse available though...
I can't think of a reason why two extra copies of the production db be placed on the production environment however... Seems like a bit of a waste of space to me. Not to mention the overhead of restoring these every night if that's an issue in your shop.
As mentioned previously though, desensitising (sanitising, whatever you want to call it) the data in anything but the prod db's will keep security tight.
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]
June 8, 2009 at 7:40 am
3 copies of the db on the same server just doesn't make any sense. If they're worried about corruption or loss, then all three on the server are exposed the same way. If they're worried about excess load, they're putting all three into the same load environment. If they're sweating backups, all the eggs are in a single basket so to speak.
I know how operations like this work (having to deal with more than a few of them myself). You just have to point out the fallacy of their logic AND provide easy alternatives.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply