SQL server multiple databases with same schema

Question

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

How can I maintain the schemas of multiple databases with respect to change management? Any change in one schema has to be automatically applied on all the other databases. Is there something I am missing? I looked at data sync but it seems to be solving another problem. In my case the schema is exactly the same and the data stored is different.

Answer 1

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

I currently use SSDT tools where I created a database project of the original schema. If there is any changes to one of the databases, i use schema compare in SSDT and update the database project. Then I follow the below steps to rollout the changes to other databases.

Step 1: Update the schema changes to database Project.

Step 2: Use MSBuild and Generate a deployment script by setting one of the databases as Target.

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

Answer 2

If you're always using Entity Framework migrations to make changes to the database structure, then you might be able to use the approach on the below. It depends a little on how your multiple databases are used in relation to the EF application.

• EF 4.3 Auto-Migrations with multiple DbContexts in one database
• EF Code First Migration with Multiple Database / DbContext

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

If you're interested in an automated process it might be worth taking a look at Deployment Manager from Red Gate (full disclose I work for Red Gate).

This lets you take a database and from Visual Studio or SSMS turn it into a package that you can deploy to multiple servers and environments. The starter edition for up to 5 projects and 5 servers is free to use, and it deploys to Azure. It can deploy .NET web applications too, but you can just use it for the DB.

It's very good for ensuring that the same database schema is on all servers within an environment, and for propagating database changes through test/staging/prod in line with any application changes. You can also integrate it with source control and CI systems to automate database changes from Dev to Production