How to archive an on-premise sql server database of about eight terabytes to azure

Question

We have an on-premise sql server database of about eight terabytes that we are trying to archive to Azure. How can I go about it?

This archive needs to be accessible because we might need to pull data from it in the future.

Answer 1

There are multiple options that you can take for a scenario like this. You can simply backup and restore your data to a SQL Server on an Azure VM. You can then stop the VM so you do not pay for compute costs and only pay for Storage until you need it.

You can partition your current database and move those unnecessary partitions to another server within Azure and follow the same steps above. However this option allows you to only move the archive data as opposed to all data. You can then delete the data from your on-prem system.

Another option may be to implement an archival process and store your archive data as parquet files within an Azure Data Lake. This will provide you with a cheap scalable storage option and you can easily access the data for cheap using Serverless SQL Pools within Synapse Analtytics.

You also may be able to utilize Stretch database to help move some of the cold data to Azure and off of your on-prem transactional system, but it is all still accessible when needed.

https://azure.microsoft.com/en-us/products/sql-server-stretch-database#:~:text=SQL%20Server%20Stretch%20Database%20lets,times%20without%20breaking%20the%20bank.

Hope this helps.