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.
Best Answer
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.