Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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.
 
Thanks

Posted 3-Jan-11 1:16am
J-Cod3r573
Comments
thatraja at 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
good
Please Sign up or sign in to vote.

Solution 1

SQL Server Replication, Mirroring, SSIS?
  Permalink  

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

  Print Answers RSS
0 Maciej Los 589
1 OriginalGriff 339
2 CPallini 205
3 RyanDev 165
4 Sergey Alexandrovich Kryukov 160
0 OriginalGriff 5,689
1 Sergey Alexandrovich Kryukov 4,783
2 Peter Leow 2,954
3 Maciej Los 2,819
4 DamithSL 2,465


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