Published on September 24, 2019 by Rakesh Gujar
Cost effective ways to archive huge amount of data in PAAS Azure SQL Db.
We recently helped a long-time SQL customer move completely from on-premise database environment to an Azure cloud environment, with their main applications in PAAS DB.
While the migration to the cloud was successful, we ran into an issue trying to move their data from on premises to Azure PAAS SQL database. Because they were running an older version of SQL Server prior to the migration, we had to use BCP to get data.
However, this customer was only utilizing data from the last two or three years, but had terabits of old, unused data sitting in storage and costing them thousands of dollars in disc space.
We had to come up with a creative solution to save them some money, so we suggested taking any data older than three years and putting it on a BLOB storage and including a external table in their SQL Server.
While this strategy might cause a slight slowdown in SQL Server queries, it was only going to impact queries related to the old data which was going unused anyway. Conversely, queries in the newer data (less than three years old) would become exponentially faster because there would be much less data to search through.
We explained to the customer that this method was like taking all the junk in your attic that you never touch and putting it into a storage unit. So, if you want to use it, it’ll take you a little longer to access it, but it would be safely stored away and ready when you need it, while also freeing up space in your house.
Optimize your data while staying cost effective.