How to store hot and cold data with Azure SQL

Question

I have a huge order table in Azure SQL. I have one boolean field "IsOrderActive" to separate hot and cold orders. Is it possible to automatically transfer cold data to a separate database with Azure SQL?

Answer 1

Database sharding seems like a possible solution for the scenario where cold orders can be put on Azure Serverless databases that have auto-pause and auto-resume capabilities where you can save when they are not in use, only paying for storage used. Azure SQL Database provides a good number of tools here to support sharding.

Answer 2

Are you referring to SQL Server Stretch Database to Azure? Check this out https://www.mssqltips.com/sqlservertip/5526/how-to-setup-and-use-a-sql-server-stretch-database

Answer 3

One way to accomplish required task is to divide the order table into two using T-SQL command then transfer the table with cold data in different database (different server) using SSMS.

Please follow the repro steps done by me.

1. Create a table

create table hotcoldtable (orderID int, IsOrderActive char(3))

2. Inserted demo data into the table

insert into hotcoldtable 
values (1,'yes')
,(2,'no')
,(3,'yes')
,(4,'yes')
,(5,'no')
,(6,'no')
,(7,'yes')

3. Divide the table into cold and hot data tables using below commands

cold data table - select OrderID, IsOrderActive into coldtable from hotcoldtable where IsOrderActive = 'no'

hot data table - select OrderID, IsOrderActive into coldtable from hotcoldtable where IsOrderActive = 'yes'

You can see two new tables in your database.

4. In SQL Server Management Studio (SSMS), login to your Azure SQL Server. Fill the details and click on Connect.

5. Left click on database name where you have order tables and click on Generate Scripts...

6. Select Select specific database objects and mark the objects for which you want to create script as shown in below image.

7. Set the below settings.

8. Review the details and click on Next. This will generate your script.

Go to the location where your script got saved. Open the file in any editor and copy the script.

9. Now in Azure Portal, go to the database where you want to transfer the cold data table. Go the the Query Editor and paste the copied script in the white space. Run the script and you will get the tables in this database as shown below.

Answer 4

If you are interested in saving space by archiving the cold data, you can use two separate tables in the same or different databases. The thing to note is you should use columnstore index for the archive(cold) table. Depending upon your data, you should be able to achieve between 30%-60% data compression.

However, this can't be done without running some queries. But it can be automated using Azure workbooks.

I built a similar kind of functionality that helped me save 58% space in Azure SQL database. Please comment if this is something you feel might help. I can share more details about this.