I am working on a requirement in ASP.NET Web API which needs two identical databases.
我正在做一个ASP的需求。NET Web API，需要两个相同的数据库。
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
我请求你告诉我是否有可能或者是否有其他方法来实现这一点
5 个解决方案
#1
6
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
您可以将Visual Studio模式与存储数据库结构的更改作为项目的一部分进行比较
#2
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.
我建议使用Liquibase，因为它是免费的开源，而且非常灵活。
#3
3
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.
在两个DB上，是否对代码进行修改?如果您有一个主从关系，您只需要初始化从数据库。否则，如果是master-master的话，您应该使用某种版本控制。
#4
3
One of possible (native) solution is to enable transactional replication (just for DDL changes)
一种可能的(本机)解决方案是启用事务复制(仅针对DDL更改)
https://docs.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/
其他解决方案是使用一些第三方工具，如PS+ApexSQL Diff: https://solutioncenter.apexsql.com/how-to-automatically-keep-two-sql-server-database- schemasin -sync/
or SQL Delta
或SQLδ
or SQL Compare (Red Gate)
或SQL比较(Red Gate)
#5
3
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).
我建议你使用微软的特殊解决方案。用于Visual Studio的SQL Server数据工具。这将为您的Visual Studio模板添加新的项目类型，并且它是免费的(可能您已经安装了它)。
This type of project will contain all DB schema and all procedures/functions/triggers.
这种类型的项目将包含所有DB模式和所有过程/函数/触发器。
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.).
由于它是项目，它可能是您的解决方案的一部分，您可以在版本控制系统(Git、SNV、TFS等)中存储所有的DB历史更改。
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.
要对第二个DB应用更改，有不同的方法。您可以使用.dacpac文件方法。或者仅仅发布两个概要文件。或者使用源比较工具。
The main idea is to make changes in this project first and then apply to your actual DBs.
主要思想是先在这个项目中进行更改，然后应用到实际的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.
当然，如果您有build server publish部分解决方案可以作为构建过程的一部分，您可以很容易地将更改应用到2个DBs。
#1
6
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
您可以将Visual Studio模式与存储数据库结构的更改作为项目的一部分进行比较
#2
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.
我建议使用Liquibase，因为它是免费的开源，而且非常灵活。
#3
3
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.
在两个DB上，是否对代码进行修改?如果您有一个主从关系，您只需要初始化从数据库。否则，如果是master-master的话，您应该使用某种版本控制。
#4
3
One of possible (native) solution is to enable transactional replication (just for DDL changes)
一种可能的(本机)解决方案是启用事务复制(仅针对DDL更改)
https://docs.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/
其他解决方案是使用一些第三方工具，如PS+ApexSQL Diff: https://solutioncenter.apexsql.com/how-to-automatically-keep-two-sql-server-database- schemasin -sync/
or SQL Delta
或SQLδ
or SQL Compare (Red Gate)
或SQL比较(Red Gate)
#5
3
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).
我建议你使用微软的特殊解决方案。用于Visual Studio的SQL Server数据工具。这将为您的Visual Studio模板添加新的项目类型，并且它是免费的(可能您已经安装了它)。
This type of project will contain all DB schema and all procedures/functions/triggers.
这种类型的项目将包含所有DB模式和所有过程/函数/触发器。
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.).
由于它是项目，它可能是您的解决方案的一部分，您可以在版本控制系统(Git、SNV、TFS等)中存储所有的DB历史更改。
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.
要对第二个DB应用更改，有不同的方法。您可以使用.dacpac文件方法。或者仅仅发布两个概要文件。或者使用源比较工具。
The main idea is to make changes in this project first and then apply to your actual DBs.
主要思想是先在这个项目中进行更改，然后应用到实际的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.
当然，如果您有build server publish部分解决方案可以作为构建过程的一部分，您可以很容易地将更改应用到2个DBs。