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.