Deploying Database to Azure

Hi Lokesh! Great questions — Let’s tackle your questions one by one with Azure-native solutions and best practices:

1. How to push the database changes from one environment to another (e.g., UAT to Production)?

There are several ways to do this:

Option A: SQL Server Data Tools (SSDT) or Visual Studio

• Use Database Projects to define schema.
• Use Publish Profile to deploy changes to different environments.
• Generate a DACPAC (Data-tier Application Package) and deploy using:
  • SQLPackage CLI
  • Azure DevOps Release Pipeline

Option B: Azure DevOps Pipelines

• Automate CI/CD for your database using tools like:
  • Dacpac deployments
  • Flyway or Liquibase (for version-controlled migrations)
  • Azure SQL Database Deployment task in DevOps

Option C: Manual Tools (for small teams)

• Export and import BACPAC files from UAT and import into Production.
• Use Schema Compare in SSMS or Visual Studio.

2. Will the schema and data both be versioned?

Not automatically. By default:

• Schema versioning is supported with tools like:
  • DACPAC, Flyway, Liquibase, or Entity Framework Migrations
• Data versioning (especially for reference/static data):
  • You need to manually include seed/reference data scripts in your deployments.
  • Tools like SQL Data Generator or writing your own seed scripts can help.

For full data versioning (think git-like history), you would need a more advanced data lineage solution, which is rare unless using a full-fledged data platform.

3. How do we get the version history?

• Schema History:

  • Use source control ( GitHub/Azure Repos) to track changes to migration scripts or database projects.
  • Tools like Flyway automatically maintain a schema_history table in the database.

• Deployments History:

  • In Azure DevOps, you can track every pipeline run and deployment.
  • For manual changes, consider using Extended Properties in SQL Server to track version numbers.

4. Database replication for reporting server (Read-only replica)?

Yes, Azure supports read replicas to offload reporting workloads:

Azure SQL Database (PaaS):

• Use Active Geo-Replication (premium tier)
  • Allows you to create read-only secondary databases in the same or different region.
  • These replicas can be used for reporting purposes.

Azure SQL Managed Instance:

• Use Auto-failover groups or Transactional replication (like on-premises SQL Server)
• You can set up read-scale out replicas.

Azure Synapse / Azure Data Factory:

• If reports are very heavy, offload the data to a dedicated analytics platform ( Synapse) via ETL or ELT pipelines.