One way Synched Dev Database from Production Database

Question

I've got a scenario to implement in our environment of SQL Server Database. Now we have around 10+ different application which uses the same DB for their day to day activities. The strange thing is, along with the production tools, the dev apps also using the same DB which is very risky (Only recently they agreed that) after years of its working.

So what Im looking for is to have a solution with a new replica of the DB as DEV database which is always to be one way synch with the production DB. That means if any changes on the production DB (Schema or Data or anything) it should immediately reflected on the DEV DB. But if we have any changes on schema on DEV Db, it should not get auto synched to Production DB. It should synch only when we manually do that( But how, I have no idea with these synching)..

So how can we handle these situation wisely. Im very poor in these DBA activities. Sorry if this is a simple thing even for beginners.

Answer 1

You've got yourself one heck of a requirement, none of which I would ever support. Regardless, one solution I can think of is to enable transactional replication on the production side, which can replicate DML and DDL statements asynchronously. In our production environment, we are at 5 src latency. I would also double check documentation to ensure all DDL statements you need are supported.

To capture DDL statements on the development side, you can create DDL triggers to log the schema changes. As long as you can distinguish between a replicated change and development change, you can then extract the changes from the log table and apply then to production.

Answer 2

Does it have to be instant? As a dev it would drive me nuts to have the database I was testing on change as I was testing. Ask them if they ever test creating data? Does that data have a unique index? What happens if they create the same unique index that's already been created in production? Should you lose the data in dev that you're currently testing with, or should the production data not copy? What about the data that links to that unique index? If the answer is to not copy, then you now have a situation where the production and dev data is slightly different. If they can live with that change, then you can argue they don't need realtime data copied from live.

If your developers don't need to write any data, they only read it, then setup log shipping. If production can do log backups every minute, then with some snapshot magic you can bring the replica online for them to read within a minute or 2 of it happening on live.

If your developers are writing data, you could easily refresh their environment regularly using backup/restores. Do a backup of production and restore it to the dev environment. There will be an outage of the dev server as the database restores, and they'll lose any changes they had, but they'll get the production data and schema as it was at the time of backup.

If you have a SAN environment, your network guys should be able to do some magic with snapshots - take a snapshot of production, connect it to the test server, magic - you'll still lose any data/changes that the developers made.

Either solution will require the Devs being disconnected for a small amount of time, but it would be pretty simple to support. I don't see how you can have a useful test environment when the schema and data change so quickly.