1

Is there a way to in a CI\CD scenario where I can push database changes (Schema or Data) to my SQL Azure database?

Brandon Michael Hunter
  • 1,179
  • 3
  • 20
  • 48
  • 1
    A dacpac file created with SQL Server Data Tools (SQL Server project template in VS) can be used in a deployment pipeline. Pre and Post scripts are often used to push data. – Crowcoder Feb 04 '18 at 12:54
  • To add to the comment by @Crowcoder, [SSDT download](https://learn.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt). – Dan Guzman Feb 04 '18 at 12:58
  • You can also use other tools such as ReadyRoll and SQL Source Control in combination with deployment tools such as TeamCity, Visual Studio, Octopus and others. They all support Azure SQL Database. – Grant Fritchey Feb 04 '18 at 16:15

1 Answers1

2

Yes there is. And how you do so depends on the technology that you choose to push those changes. Here's some of the more popular tooling support:

  • Deploy changes via a DACPAC from a Visual Studio Database Project. You can set a Database Project to build a DACPAC, then publish the DACPAC to your server using a variety of tools. I know that it can be done through Visual Studio Team Services Build and Release pipelines for CI. You can use the Azure SQL Database Deployment task to do so.

  • Deploy using Entity Framework Code First Migrations. You can script migrate.exe into just about any CI tool. I like this one best for two reasons. First, you explicitly script EXACTLY what you want EF to do during an upgrade. You can also script the addition/transformation of data. Second, there is support for rollback, although you have to define the steps taken during rollback.

I find EF Code First Migrations easiest for working with Azure SQL DB because it doesn't force your production database to exactly match the DACPAC definition. This is REALLY IMPORTANT if you've enabled automatic performance tuning for your database.

Consider the following situation: you enable automatic performance tuning, and Azure realizes that you need an index on a table with about a bajillion records. It applies the index. This new index escapes your notice and you DON'T include it in your Database Project definition. The next time you deploy your DACPAC, it's going to roll that index back. And probably spike the *@&# out of your DTUs, causing a nasty slowdown and/or queuing of queries. This isn't a problem with Code First migrations.

Rob Reagan
  • 7,313
  • 3
  • 20
  • 49