在不同环境之间移动数据 [英] Moving data between different environments

问题描述

我面临以下问题的挑战，并且迫切需要可以帮助我找到适合我的环境的解决方案的人员.

问题描述:

在我的工作地点，我们有4个环境(DEV，TEST，QA和Live)，所有环境都是MSSQL.我们需要定期在这些环境之间移动数据，因为我们需要具有最新的相关数据才能开发，测试或允许进行质量检查.

目前，我们没有可以定期(可重复)移动相关表的自动化机制.这是在开发人员的基础上完成的.如果我们能够在周末自动执行此过程，则将为我们的开发人员节省大量时间，并减轻工作时间(网络，服务器)对资源的压力.

我们目前所拥有的:

我们有一个SDLC流程，可帮助我们确保仅在SDLC流程中的相关下一个DB上实现所需的DB模式更改.
•我们通过结合使用称为SQL Compare的工具和SVN来实现此目的.在SVN源(SVN中存在完整的数据库模式)之间进行比较，相关的DB
•数据库架构比较是自动完成的.此过程的输出是一个数据库脚本，其中包含应应用于此数据库的更改.
•我们有一个内部质量检查策略来检查数据库脚本，以确保将要实施的更改实际上是我们期望的.因此，应用更改是一个手动过程.

一些主要挑战/担忧:
•实时(源)数据库不到1TB
•某些表对环境敏感(在特定环境中具有到服务器的路径和链接)
•必须复制的核心表为+-250GB，换句话说，不必使用自动化过程复制所有表.
•强调硬件资源，磁盘访问，网络复制时间以及完成此过程所需的时间.

当前服务器\实例架构:
•Live DB具有一个实例，并托管在一台服务器上
•DEV，TEST和QA是托管在一台服务器上的三个单独的实例

所需的解决方案:
我需要一些可以使上述过程自动化的方法，并且几乎不需要人工干预.

谢谢

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

推荐答案

SQL Server复制，镜像，SSIS?

SQL Server Replication, Mirroring, SSIS?

这篇关于在不同环境之间移动数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！