SQL Server : Replicate Schema changes to another database

Question

I am working on a requirement in ASP.NET Web API which needs two identical databases.

I have created two identical databases on the same server. Let's say one is for the development and another for testing.

I want to replicate all schema (like stored procedure, view & table) changes instantly from one another database to the other.

I am not worried about the data replication, I just need to make sure both the databases have exactly the same schema definition.

I have tried generating a schema from one database and updating the other one periodically. But now, I want to replicate changes instantly to the other database. So when I update a stored procedure or view in the development database, the same changes should also be applied to the testing database immediately.

I Requests you to tell me if it's possible or if there's another approach to achieve this

Answer 1

To me, this does not seem like a good idea.

If you make a change in the development environment that breaks the system, you would immediately break the testing environment too. This would be bad.

If you make a code change in the development environment, and make the database changes to support it, if the database change is immediately replicated, you would have code and database that were out of sync, and again, could conceivably break the test environment.

A better approach would be to use a version control system, to batch your changes to data and code together, and use a manual system, or continuous integration, to deploy them together to the test environment.

You can use Visual Studio schema compare to store database structure changes as part of your project

Answer 2

Instead of automatically syncing changes done on one database to another, use some kind of source control for your database and deploy the changes to both environments manually (or automatically).

Source control have all kinds of positive impacts and should become a habit anyway. Even for your databases.

I suggest using Liquibase due to it being free open source and quite flexible.