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开发者_C百科 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?

---

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)

---

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.