1
Answer

Deploying Database to Azure

Photo of Lokesh Rajana

Lokesh Rajana
7y
746
1
Hi,
 
I am hosting one application in Azure. I want to maintain my database also in Azure.
 
can you please clear me about following things.
 
1.How to push the database changes from one environment to another? for example UAT to Production
2.Will the schema and data both will be versioned?
3.How do we get the version history?
4. Database replication for reporting server - Running huge reports on the replica server and serve the reports asynchronously without affecting the performance.
 
How can I achieve above things from Azure. please help me out of this.

Answers (1)

1
Photo of Amira Bedhiafi
325 5k 653.9k Mar 22

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.
Next Recommended Forum