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

I suggest you to use special solution from Microsoft. SQL Server Data Tools for Visual Studio. This will add to new type of project to your Visual Studio templates and it's free to use (Maybe you even have it already installed).

This type of project will contain all DB schema and all procedures/functions/triggers.

Since it's project it could be a part of your solution and you can totaly store all DB history changes in version control system (Git, SNV, TFS etc.).

To apply changes to your 2nd DB there are sepaveral ways to do it. You can use .dacpac file approach. Or just 2 publish profiles. Or use source compare tool.

The main idea is to make changes in this project first and then apply to your actual DBs.

And of cource, if you have build server publish part of this solution can be a part of build process and you'll easily apply changes to 2 DBs.

Answer 2

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 3

One of possible (native) solution is to enable transactional replication (just for DDL changes)

https://learn.microsoft.com/en-us/sql/relational-databases/replication/publish/make-schema-changes-on-publication-databases

You have an option to have multiple databases (subscribers) and move the subscribers to another servers in the future (if required).

Other solution is to use some 3rd party tools, like PS+ApexSQL Diff: https://solutioncenter.apexsql.com/how-to-automatically-keep-two-sql-server-database-schemas-in-sync/

or SQL Delta

or SQL Compare (Red Gate)

Answer 4

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.

Answer 5

Since I do not really like triggers on the essence that they make changes without my knowledge, I would suggest to make a procedure initialize each database. So when you include some new code to your schema make sure to include it also into the initializing process. The replication should start always with this process.

Do you make changes on code, on both DB? If you have a master-slave relation you only need to initialize the slave DB. Otherwise, in case of master-master you should keep with some sort of versioning.