Click here to Skip to main content
13,086,265 members (75,877 online)
Rate this:
Please Sign up or sign in to vote.
See more:
I am challenged with the following problem and in desperate need of someone who can assist me in finding a solution, that will work in my environment.

Problem description:

We have 4 environments (DEV, TEST, QA and Live) at my place of work, all MSSQL. We need to move data between these environments on a regular bases, as we need to have recent and relevant data available in order to develop, test or allow QA to take place.

At the moment we do not have a automated mechanism that can move the relevant tables on a regular (repeatable) basis. This is being done on a developer by developer basis. If we are able to automate this process over weekends, it would save our developers a huge amount of time and additionally alleviate pressure on resources during work hours (Network, servers).

What we do have in place currently:

We have an SDLC process that assists us in ensuring that only the required DB schema changes gets implemented on the relevant next DB in the SDLC process.
•We achieve this by making use of a tool called SQL Compare in conjunction with SVN. A comparison is done between SVN source (Completed database schema exists in SVN) end relevant DB
•DB schema comparison is done automatically. The output of this process is a DB script that contains the changes which should be applied to this DB.
•We have an internal QA policy to check the DB script in order to insure that the changes which will be implemented are in fact what we are expecting it to be. Thus applying the changes is a manual process.

Some of the main challenges/concerns:
•The Live (Source) DB is just under 1TB
•There are certain tables that is environment sensitive (Has paths and links to servers in the particular environment)
•Core tables that must to be copied are +- 250GB, in other words it is not necessary to copy all tables using the automated process.
•Stress on the hardware resources, Disk access, Network copy time, time that it takes in order to complete this process.

Current server\instance architecture:
•Live DB has a single instance and is hosted on one server
•DEV, TEST and QA are three separate instances hosted on one server

Required solution:
I need something that can automate the process mentioned above with as little to no manual intervention as possible.


Posted 3-Jan-11 1:16am
thatraja 3-Jan-11 12:41pm
Really a good question, please keep it up. 5
BTW wait for the answers, surely you will get quickly. cheers.

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

SQL Server Replication, Mirroring, SSIS?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web04 | 2.8.170813.1 | Last Updated 3 Jan 2011
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100