Azure SQL Data Sync¶

In situations where heavy querying of an Azure archive database is needed it can be beneficial to sync the SQL data to an on-premise database rather than querying the Azure database. To support this the Azure SQL Data Sync functionality can be used. This is available in the Azure portal under the database as the 'Sync to other databases' option.

The sync process is managed by Azure and works by installing an agent on the on-premise machine that connects to the Azure database. A Sync Metadata Database is then used to facilitate the sync process. Once connected a period can be selected for how often the sync process should run or it can be triggered manually. When choosing this sync period consider how often the reporting data is generated, our advice is to not sync faster than this since there will be no data to sync. A list of tables and columns can be selected to be included in the sync. The ARC_Audit and the ARC_Snapshot tables should not be included in the sync process (see the notes below). Consult the Microsoft documentation for information on how to enable Azure SQL Data Sync.

Schema changes are not synced as part of the sync process. When upgrades make changes to the archive database schema ensure that those same changes are applied to the on-premise database to be able to sync them. The database upgrade scripts are included with the installer and can be used to form the basis of the changes needed. If the on-premise schema does not match the sync schema the sync process will start to fail but the scheduling system will continue to work as usual. Consult the Microsoft documentation for more information including recommendations on automatically syncing schema changes.