0

If I want a daily copy/replication of my production database, I know I can copy, but what happens when the size grows to ~100 terabytes or more?

It doesn't seem logical to copy a db of that size everyday just to use for testing/QA.

Ideally I'd like a solution where - 1. just the changes (data) are copied (nightly) to the testing db, there by eliminating the overhead of copying a large db. 2. when I do push changes (column additions, keys, etc) to production then those changes get copied to the testing db as well.

Is there an Azure solution or setup for this?

chuckd
  • 13,460
  • 29
  • 152
  • 331

1 Answers1

2
  1. just the changes (data) are copied (nightly) to the testing db, there by eliminating the overhead of copying a large db. 2. when I do push changes (column additions, keys, etc) to production then those changes get copied to the testing db as well.

Please reference the document of SQL Data Sync. SQL Data Sync is a service built on Azure SQL Database that lets you synchronize the data you select bi-directionally across multiple SQL databases and SQL Server instances.

Data Sync is based around the concept of a Sync Group. A Sync Group is a group of databases that you want to synchronize.

Data Sync uses a hub and spoke topology to synchronize data. You define one of the databases in the sync group as the Hub Database. The rest of the databases are member databases. Sync occurs only between the Hub and individual members.

You can sync the data between hub database and member datatbase manually or automatically. Please see Tutorial: Set up SQL Data Sync between Azure SQL Database and SQL Server on-premises

Hope this helps.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23
  • Hi Leon. Quick question - can I sync databases between my production database in Azure and my sql database on my local machine? So that when I develop/test/etc I'm using my local db and not the one on Azure? And if yes, then I assume the whole db, regardless of size, will sit on my local machine? Even if it's 10TB in size! – chuckd Jul 25 '19 at 18:14
  • Yes, you can. But if you database is very large, Azure SQL database will auto scale up, you need to think about the cost monthly. – Leon Yue Jul 26 '19 at 00:52