1

I have a database with a growth of more than 100 Gb per week, and 5 TB yearly.

Since this is financial data, we can't purge it. If we keep this data for at least 10 years, size will become 50 TB.

Please suggest how we can accomodate this amount of data in Azure VMs with limitation of 1 TB disc in Azure.

Thanks, Subhendu

user397401
  • 11
  • 2

2 Answers2

2

There are really two approaches you can take with this:

  1. Azure SQL Data Warehouse
  2. Azure SQL Elastic Database

Azure SQL data warehouse takes a similar approach to what you are doing now, using VM's running SQL but couples that with Azure Storage and a control layer to help you spread your data over multiple nodes and multiple storage locations. It also allows you to scale your compute as required by your query load.

Azure SQL Elastic Database instead uses Azure SQL PaaS service, rather than VM's, and works with sharding your data over multiple instances. It provides client libraries and a jobs engine to help you run queries over your multiple shards.

Sam Cogan
  • 38,158
  • 6
  • 77
  • 113
1

Sam Cogan's answer is spot on. But my other few suggestions would be:

  1. Use clustered columnstores. That 50TB may quickly turn into 2TB-10TB on disk once compressed.

  2. A GS5 VM (the largest at the moment) can have 64 disks of 1TB each attached and striped into a giant storage pool.

  3. If you choose the largest VM and mount the max number of disks then you could consider using SQL Data Files In Blob Storage in addition to SQL data files on disk. You could easily achieve 50TB that way.

GregGalloway
  • 373
  • 1
  • 6