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?
-
2This feels like an [XY Problem](http://xyproblem.info), if I am honest. Storing the *same* data in different tables, let alone different databases, is rarely a good idea. Also, if it's an Azure SQL Database not Azure SQL Server, which is different, (you've tagged both, which isn't helpful) then this really won't work; Azure SQL Database's are isolated databases. If it's (Azure) SQL Server, however, then perhaps partitioning would be a better option. – Thom A Sep 29 '21 at 11:15
-
@Larnu I mean databases located in different servers, with different pricing. – Alexey Ryazhskikh Sep 29 '21 at 11:36
-
1Even worse then. That'll make getting details of all your orders a real pain in the ... – Thom A Sep 29 '21 at 13:02
-
are you interested in any solution using T-SQL and SSMS? – Utkarsh Pal Sep 29 '21 at 13:37
-
Do you actually have a requirement to transparently query all the data in this table? Or are you happy to archive the data elsewhere and reference it late if need be? What specific issue are you having right now, is it performance? Storage cost? – Nick.Mc Sep 30 '21 at 03:37
-
I need transparently query data, but per order. So I don't need cross-database queries. I can write code for sharding and route requests manually, but my question is if there is some native SQL Azure feature or some third party for storing hot and cold data. – Alexey Ryazhskikh Sep 30 '21 at 05:38
4 Answers
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.

- 127,765
- 105
- 273
- 257

- 13,893
- 2
- 24
- 30
-
Elastic Database client library looks abandoned since 2019. I'm looking for a native solution. – Alexey Ryazhskikh Sep 29 '21 at 12:40
-
@Alexey no current native solution, sharding is the closest solution – Alberto Morillo Sep 29 '21 at 12:53
-
Elastic database does work. It just doesn't work with private endpoint. – Nick.Mc Sep 30 '21 at 04:34
-
That is elastic query. You will have to build everything with external sources and query performance maybe an issue – Alberto Morillo Sep 30 '21 at 12:19
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.
- Create a table
create table hotcoldtable (orderID int, IsOrderActive char(3))
- Inserted demo data into the table
insert into hotcoldtable
values (1,'yes')
,(2,'no')
,(3,'yes')
,(4,'yes')
,(5,'no')
,(6,'no')
,(7,'yes')
- 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.
- In SQL Server Management Studio (SSMS), login to your Azure SQL Server. Fill the details and click on Connect.
- Left click on database name where you have order tables and click on Generate Scripts...
- Select Select specific database objects and mark the objects for which you want to create script as shown in below image.
- Set the below settings.
- 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.
- 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.

- 4,079
- 1
- 5
- 14
-
-
it should work for large tables, just make sure target database is capable to store large amount of data. – Utkarsh Pal Sep 30 '21 at 05:52
-
You can't transparently query across Azure SQL databases without a lot of messing around – Nick.Mc Oct 11 '21 at 05:45
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

- 291
- 3
- 8
-
I'm talking about the same thing, but for Azure SQL. Stretch is on-prem SQL Server feature to save cold data to Azure, but you can't enable stretch for Azure SQL databases to save cold data to another Azure SQL – Alexey Ryazhskikh Sep 30 '21 at 05:31
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.

- 151
- 8