CODEWITHSUNDEEP
azureazure-sql-database
chuckd
chuckd

Reputation: 14540

Creating a test or stage database from a production database in Azure

I'm not sure how to create a database to use as a test/stage database from another database in Azure. It seems like the copy button in the Azure portal is gone from old tutorials I've read online.

I've created a database that will be used as the production db. Now I want to create another database that's exactly the same, and everyday at midnight I'd like to copy the production database tables, data, etc to the copy, so that's it's an exact copy, so that in the morning I can come in and work from the test/stage (copied) database and make changes (add data, delete data, change tables, etc) and then when ready update the production db.

Are there any good tutorial or additional ways to accomplish this?

What are the prescribes ways to do this?

Should I have some local copy on my machine or should I have two db's (prod, test) in Azure?

Any advice here would be much appreciated!

Upvotes: 1

Views: 2501

Answers (2)

Alberto Morillo
Alberto Morillo

Reputation: 15648

You can have two databases on Azure SQL Database. One will be your Production database and the other one your test/stage database. You can use Azure Automation and PowerShell to refresh (drop/create) your test/stage database whenever you want this to happen (every day, once a week, etc.)

1.Check if COPY database already exists - if so delete it

 Get-AzureRmSqlDatabase -ResourceGroupName $ResourceGroupName -ServerName $SqlServerName -DatabaseName $databaseCopyName
 Remove-AzureRmSqlDatabase -ResourceGroupName $ResourceGroupName -ServerName $SqlServerName
 -DatabaseName $databaseCopyName `
 -Force

2.Create COPY database

 New-AzureRmSqlDatabaseCopy -ResourceGroupName rg-resourcegroupname -ServerName sql-servername
 -Tags @{key="value"} -DatabaseName sqldb-databasename
 -CopyResourceGroupName rg-resourcegroupname -CopyServerName sql-servername
 -CopyDatabaseName sqldb-databasename-copy

Upvotes: 0

Leon Yue
Leon Yue

Reputation: 16411

You want to sync the data between production database and test/stage database every day. Getting the data from the production db, and test in the test/stage db, then update to the production db again. Am I right?

Azure SQL database Data Sync can help you achieve that.

Summary:

Data Sync is useful in cases where data needs to be kept up-to-date across several Azure SQL databases or SQL Server databases. Here are the main use cases for Data Sync:

  • Hybrid Data Synchronization: With Data Sync, you can keep data synchronized between your on-premises databases and Azure SQL databases to enable hybrid applications. This capability may appeal to customers who are considering moving to the cloud and would like to put some of their application in Azure.
  • Distributed Applications: In many cases, it's beneficial to separate different workloads across different databases. For example, if you have a large production database, but you also need to run a reporting or analytics workload on this data, it's helpful to have a second database for this additional workload. This approach minimizes the performance impact on your production workload. You can use Data Sync to keep these two databases synchronized.
  • Globally Distributed Applications: Many businesses span several regions and even several countries/regions. To minimize network latency, it's best to have your data in a region close to you. With Data Sync, you can easily keep databases in regions around the world synchronized.

The Data Sync supports the Sync Direction can be Hub to Member, or Member to Hub, or both.

You can create a database in Azure SQL database, and create an on-premise database with the same schema (for cost sake). Using the Azure SQL Database Data Sync to sync the data between the production db and on-premise db manual or automatic.

About how to create the Data Sync, please reference this official tutorial: Tutorial: Set up SQL Data Sync between Azure SQL Database and SQL Server on-premises

Hope this hleps

Upvotes: 2

Related Questions