How can I synchronize a live SQL Server 2005 server with a dev box?

views:

answers:

How can I synchronize a live SQL Server 2005 server with a dev box?

We have a development box which is inside our network and a web server farm outside.

What's the best way to keep the development database synchronized with the Live Server's [changing] Database and yet still keep it secure?

Are there 3rd party tools that would facilitate this?

Are SQL Server's built-in synchronization features good enough or do they require opening up my network to an unacceptable level?

How can we get this down to a "one-button" operation?

+1 A:

Yes, of course there are plenty of well established third-party tools to do this (SQL Server itself doesn't offer very much really) - the best are:

Red-Gate SQL Compare (for your structures) and SQL Data Compare (for data)
ApexSQL's SQL Diff (for your structures) and SQL Data Diff (for data)

marc_s 2010-07-22 18:32:32

Are either of these products relatively painless to work with once set up? Ideally, we'd want to make it as painless as possible. Also, what are the security implications? That's the main issue for our Network Admin. He doesn't like opening any holes between the inside and the outside. Incidentally, I believe we have some red-gate software already.

Atømix 2010-07-22 21:17:17

@dl-_-lb: with Red-gate, you define the source and destination once (can be a live server, or a backup file even), you pick your tables and db objects you want to synchronize, and you store all that in a profile file. Once you have that, you can call SQL Compare / SQL Data Compare from e.g. the command line and have it execute that synchronization you've set up again, or you can double-click on those profile files in Windows Explorer and have them run. So yes, I'd say, it's quite painless after an initial setup

marc_s 2010-07-22 21:21:24

+1 A:

Comparing live/dev databases with a compare tool is a pain for anything more than a few tens of MBs

By the time it's compared tables starting "z" your foreign keys are buggered up with tables starting "a".

Options I've seen/used:

DB mirroring with no failover
Log shipping to dev server
Regular Custom FTP of backups/restore on dev

My preferred option is number 3: you have a regular daily snapshot of what you had last night (for example). On top of that, you verify your backups too and have a reference copy. Mirroring/log shipping simply replicates corruption.

gbn 2010-07-22 18:58:09

Is this only a problem on a database that is changing a lot? We can lock down the admin tool while replicating.

Atømix 2010-07-22 21:13:14

if you can lock down the DB, then you can probably use SMO via c# to copy data

gbn 2010-07-23 05:03:35

ansaurus

tags:

views:

answers:

How can I synchronize a live SQL Server 2005 server with a dev box?

related questions