SQL Server multiple databases with the same schema

Question

I have a solution that is distributed across multiple Azure SQL databases. These databases have the same data schema, and I generate edmx from one of them.

How can I support multiple database schemas in relation to change management? Any change in one schema should automatically apply to all other databases. Is there something I'm missing? I looked at data synchronization, but it seems to be a solution to another problem. In my case, the scheme is exactly the same, but the stored data is different.

Answer 1

This can be achieved using SSDT (Addin for VS) and automatic deployment tools (I use Final Builder).

I am currently using SSDT tools, where I created a source schema database project. If there is any change in one of the databases, I use schema comparison in SSDT and update the database design. Then I follow the instructions below to change changes in other databases.

Step 1: Update the schema changes in the database project.

Step 2. Use MSBuild and create a deployment script by installing one of the databases as Target.

Step 3. Run the generated script across all databases using any automated deployment tools.

Answer 2

If you always use Entity Framework migrations to make changes to the database structure, you can use this approach below. It depends a little on how your multiple databases are used in relation to the EF application.

• EF 4.3 Automatic migration with multiple DbContexts in one database
• First migration of EF code with multiple databases / DbContext

Or you can use EF migrations to generate a script that can be run on each server manually.

If you are interested in an automated process, it might be worth taking a look at the Deployment Manager from the Red Gate (I fully disclose that I work for the Red Gate).

This allows you to take a database, and from Visual Studio or SSMS turn it into a package that can be deployed to multiple servers and environments. The starter version for 5 projects and 5 servers is free to use, and it is deployed to Azure. It can also use .NET web applications, but you can just use it for a DB.

This is very good for ensuring that the same database schema resides on all servers in the environment, as well as for propagating database changes through test / staging / prod according to any application changes. You can also integrate it with version control and CI systems to automate database changes from Dev to Production