June 15, 2006 at 9:39 am
I'm not sure if there is the right place to post this question, but ...
We currently have a production server that developers have access to. I want to cut that off and create a testbox for them to play with. What is the best way to do this, SQL replication ? I want to copy (every sunday night, or on demand), all data off Prod to Test. But keep their views, SPs, tables all intact on the testbox.
Is there a place someone could point me into, or give a strategy ?
June 15, 2006 at 5:44 pm
I take it from your post that the developers have views, SPs and tables that are not related to any applications that use the database - or have I misread what you've written?
If I have misread it, then backup and restore is an option, though you would need to create some scheduled tasks to automate the work.
Replication is a valid option, as is a DTS package that simply overwrites existing data in the test system.
If it is a test system, that should only be used for testing and there should be scripts available to apply changes as they come out of development.
Actually sounds to me like what you require is a development database for the devs to work against, as well as a test database for the independent testing of changes made by the devs.
June 15, 2006 at 7:05 pm
I'll ask the obvious question: do the developers need access to the production data? The database objects, that I can understand. But I'm always leary about giving access to production data in a non-production environment. Also, if they do need access, how often do they need it refreshed, if at all?
Replication is an option, as Steve indicates, but there are some issues inherent in that (see Books Online for where things can trip you up). If it's needed on a recurring basis, a DTS package that would be scheduled, as Steve suggest, seems like a feasible option, so long as it runs when the database isn't under heavy use (say on the weekends in the middle of the night).
K. Brian Kelley
@kbriankelley
June 16, 2006 at 7:27 am
Well this is kind of a kicker. There is really only one person doing the SQL (me). lol. But we have a mainframe guy and another VB guy that like to think they know what they are doing and mess around with the data and create views, etc... I want them off the production sql, so I'm setting up a 'playground' for them. The MF guy will play with the views and data to come up with reports to pass onto me, so I can do them officially and make sure indexes are being used, etc. It would be nice to have three systems in the future (Prod, Dev, Test), but for now I’m going to be very happy with two.
I came by a good site http://www.databasejournal.com/features/mssql/article.php/10894_2174031_1
Basically I’m thinking of doing a backup on the weekend and restoring them to the testbox. So the testbox databases get wiped out clean. In the testbox, I’ll setup a new database that is their 'play' area that won't get disturbed by the backups. So they won't loss any of their views and other stuff. And I like how there is an encryption step, which I can do on any column. (of course the mainframe guy can just look in the mainframe if he wanted to see something, but still a good idea to have in the long run)
I played with replication, but didn't like how the whole snapshot was working (if you add more tables, change indexed, etc), so I scrapped that plan.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply