5

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.

Improve this question
5
  • 2
    I haven't use it with Azure, but in a previous life I made great use of tools like Red-Gate Multi-Script and SQLFarms Combine to deploy changes to many, many databases sharing a common schema. Commented Aug 31, 2013 at 18:37
  • Multi-script works with Azure. Commented Sep 11, 2013 at 8:54
  • If you want to do this programatically, other than using a tool as suggested by Aaron, you can look at Django migrations for python - docs.djangoproject.com/en/dev/topics/migrations Or hibernate for Java docs.jboss.org/hibernate/orm/3.3/reference/en/html/… Commented Dec 10, 2013 at 21:40
  • I'm having the same problem and I would like to hear what solution you decided to use. I'm using a local database as mastervisual studio database project, do a database compare with azure and local take the script and uses c# to push it to all my azure db's. Fast and dirty and sooooo not what I want Commented Dec 20, 2013 at 8:03
  • Hi Archlight - You might be interested in the Deployment Manager tool mentioned in the answer below stackoverflow.com/questions/18551548/… Commented Dec 23, 2013 at 10:56

3 Answers 3

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

You can also use the SqlPackage.exe command line tool to deploy to each database using the .dacpac file generated when building an SSDT project. The benefit is that it checks the state of each database, so if any of them were out of sync with the others it would still ensure the DB ends up in the correct state. This is a nice alternative to using MSBulid.
1

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.

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

Improve this answer

Comments

0

There is tool called Airbyte, It is used to sync(scheduled/automated) and duplicating one DB instance(Complete Database or only selected) across various Database servers.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.