October 3, 2008 at 5:19 pm
Hi All
This is a question in regards to giving developers production data on the development environment.
I guess all of us who have the DBA role on the production side, have faced the situation when issues arise and developers tells you that they could not have forseen the issue, since data in the development server was out dated.
I'm looking for tips and ideas for how to give prod data to the developers in a controlled way, without giving them full read access to the production databases. With the potential that they can create crazy select's that could kill my prod server.
My requirements / wishes are the following: (some are contradictory and they are in no way listed in any particular order of priority)
Data available locally in the dev domain (separate AD domain)
Real time data or data not older than 24 hours.
Transfer of data only, so no stored procedures, views UDT's etc.
Low performance impact for prod system, especially during nine to five business hours.
No limitations for prod system, i.e if replication is used objects cannot be modified without halting the replication.
Flexibility for schema / data changes in the dev environment
As low disk space cost as possible.
Low administrative overhead of the solution.
Flexible solution (should cope with DB's from 50 MB to 150GB)
Should handle both 2000 & 2005 platforms
Some of the options I have thought about are:
Replication (done to a _PRD database on the dev server):
Pros:
Gives real time data.
Low impact on prod system in for example transactional repl.
Can be configured for data only
Cons:
Put limits on the prod system by schema changes (as far as I know, have not touched replication for several years).
Double disk space usage.
Impact in prod time, even though it might be low.
Scriptet SSIS / DTS transfer (done to a _PRD database on the dev server):
Pros:
If done in the night does not hurt performance office hours
Data only
Easy management if using dynamic transfer scripts.
Cons:
Full copy every day, problematic for large databases.
Problematic for 24/7 systems with limited maintenance windows.
Causes heavy network traffic.
Double disk space usage.
Database Mirror:
Pros:
Low maintenance
Low performance impact
Good for large databases
Cons:
Not data only
Double disk space usage.
Only available on 2005
If db name have to be the same not really good.
So what are you guys doing in regards to your development environments? Like us ad hoc backup and restore on demand, any of the above or something completely different. Would like to hear your comments and input, thanks in advance.
//SUN
October 3, 2008 at 6:06 pm
I don't personnally know how to do it, but the folks at my old shop did it quite easily. Apparently, certain SANs come with the ability to make snapshot clones of the database... every night at midnight, the system would take down the reporting server for about 10 minutes. At the end of that time, a 500GB perfect copy of the production database was available for immediate use on the reporting server. Just change the name "reporting server" to "development server", and you might be all set.
Just keep in mind... any database that get's copied over will destroy anything new in the "reporting server". You can add non-volitle databases to the reporting server that won't get copied over.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 4, 2008 at 9:46 am
Hi Jeff
We have the same functionality in our SAN, but this would of cause overwrite any code changes currently deployed. So a too invasive approach in my view.
//SUN
October 4, 2008 at 12:16 pm
Ummm.... if you make a "sandbox" database, it won't get overwritten. The key is having access to the production data via a "reporting" database... if you create synonyms or passthrough views in the sandbox to tables in the other databases, you won't even have to change the code when you promote it to production.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply