0

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?

Alexey Ryazhskikh
  • 2,458
  • 4
  • 32
  • 51
  • 2
    This 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
  • 1
    Even 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 Answers4

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.

ChrisGPT was on strike
  • 127,765
  • 105
  • 273
  • 257
Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
0

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))

  1. Inserted demo data into the table
insert into hotcoldtable 
values (1,'yes')
,(2,'no')
,(3,'yes')
,(4,'yes')
,(5,'no')
,(6,'no')
,(7,'yes')
  1. 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.

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

enter image description here

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

enter image description here

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

enter image description here

  1. Set the below settings.

enter image description here

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

enter image description here

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

  1. 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.

enter image description here

Utkarsh Pal
  • 4,079
  • 1
  • 5
  • 14
0

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

Lenroy Yeung
  • 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
0

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.

Udit Jindal
  • 151
  • 8