如何在CI/CD场景下向SQL Azure数据库推送数据库变更？

Hey there! Let's walk through the practical ways to push both schema and data changes to SQL Azure within a CI/CD pipeline—there are several reliable options depending on your team's tech stack and workflow preferences.

If you're already working with Microsoft tools, this is the most seamless approach:

• Set up an SSDT Project: Import your SQL Azure database schema into a SQL Server Data Tools (SSDT) project. This project acts as the single source of truth for your database structure (tables, views, stored procedures, etc.).
• Handle Data Changes: For static data (like enum values or default configs), add post-deployment scripts to your SSDT project, or directly embed data into table definitions by importing static rows.
• CI Build Phase: Build the SSDT project to generate a .dacpac package—this file contains the desired state of your target database.
• CD Deployment Phase: Use Azure DevOps' Azure SQL Database Deployment task (or run SqlPackage.exe directly via command line) to deploy the .dacpac to SQL Azure. The tool automatically compares the current database state to the dacpac, generates a safe change script, and supports:
  • Previewing changes with SqlPackage.exe /Action:DeployReport
  • Transactional deployment (auto-rollback if any step fails)

These cross-platform tools are perfect for teams preferring version-controlled, incremental changes:

Flyway #

• Initialize Your Project: Configure Flyway with your SQL Azure connection details (via flyway.conf or environment variables).
• Write Versioned Scripts: Name scripts following Flyway's convention (e.g., V1__Create_Users_Table.sql, V2__Insert_Default_Roles.sql, U1__Update_User_Email_Field.sql for upgrades).
• CI/CD Execution: Run flyway migrate in your pipeline—Flyway tracks executed scripts in an auto-created flyway_schema_history table, ensuring each script runs exactly once. For data changes, use idempotent statements like MERGE or INSERT ... WHERE NOT EXISTS to avoid duplicates.

Liquibase #

• Define Changes: Use XML/YAML/JSON (declarative) or SQL scripts to describe schema and data changes. Liquibase supports rollback scripts out of the box, which is great for risk mitigation.
• Pipeline Integration: Execute liquibase update in your CI/CD workflow to apply changes. Like Flyway, it tracks the state of your database in a dedicated changelog table.

For smaller projects or teams that prefer full control over scripts:

• Store Scripts in Repo: Organize schema and data change scripts in your code repository (e.g., ./scripts/schema/Create_Orders_Table.sql, ./scripts/data/Populate_Product_Catalog.sql).
• Execute in Pipeline:
  • Use PowerShell's Invoke-SqlCmd:

    Invoke-SqlCmd -ServerInstance "your-sql-azure-server.database.windows.net" -Database "your-db" -Username "ci-cd-service-account" -Password "$(DB_PASSWORD)" -InputFile "./scripts/schema/Create_Orders_Table.sql"
    

  • Or Azure CLI:

    az sql db execute-query --resource-group your-resource-group --server your-server --name your-db --file ./scripts/data/Populate_Product_Catalog.sql
    

• Critical Note: Ensure all scripts are idempotent (e.g., use IF NOT EXISTS for table creation, MERGE for data inserts) so they can be safely rerun without causing errors.

Key Best Practices for CI/CD with SQL Azure #

• Isolate Environments: Use separate SQL Azure instances for dev, test, and production. Deploy to test first, validate changes, then promote to production.
• Least Privilege: Assign minimal permissions to your CI/CD service account (e.g., db_ddladmin for schema changes + db_datawriter for data changes) instead of using db_owner.
• Pre-Deployment Backups: Automate SQL Azure database backups before deployment (via az sql db backup create) to enable quick recovery if something goes wrong.
• Preview Changes: Always run a dry-run or preview step before production deployment to catch unexpected changes early.

Hopefully these options give you a clear path forward. If you're deep in the Microsoft ecosystem, SSDT + Azure DevOps is the most integrated choice. For cross-database flexibility or incremental change tracking, Flyway/Liquibase are excellent alternatives.

内容的提问来源于stack exchange，提问作者Brandon Michael Hunter