雪花与Azure SQL用于存档存储
我们在Azure SQL实例DB中有一个表。每个月约有15 m的记录在该桌子中填充。我们正在考虑每60天后进行档案过程来存档数据。
目前，我们认为存档数据将仅用于调试目的。这可能需要一些索引，因为它可能包含很多数据。但是，这是为了将来，我们可能希望将其保留在我们的整体决定中。
我们有2个目标数据库商店的选项。雪花或天蓝色SQL。 我研究了将数据存储在雪花或Azure SQL中的费用。似乎这取决于不同的因素。而且我觉得这可能最终会得到相同的成本。
我们还在考虑利用ADF移动数据（如果我们将雪花作为档案数据库作为档案数据库，则从Azure SQL转移到雪花）。 或者只需使用Azure功能，该功能将每天运行以将数据从一个表移动到Azure SQL中的另一个档案表。
我们正在考虑在2年后清除/删除数据存档商店的数据。
数据档案存储的使用频率将非常小，因为它包含超过60天的数据。但是，让我们期待一个月一次。
关于成本和未来用法和整体工作的数据存储的任何建议将受到高度赞赏吗？
We have a table in Azure SQL instance db. Around 15 M records gets populated in that table every month. We are thinking to have archival process to archive the data after every 60 days.
For now, we are thinking archived data will just be used for debugging purpose. This might need some indexing because it may contain lot of data. However, this is for future, we may want to keep that in our overall decision.
We have 2 options for destination db store. Snowflake or Azure SQL.
I have looked at the cost to store the data in snowflake or azure sql. Seems it depends on different factors. And I feel it might end up with same cost.
We are also thinking to leverage ADF to move data (move from Azure SQL to Snowflake if we go with snowflake as destination db for archival).
Or just use Azure Functions which will run daily to move data from one table to other archival table in azure sql.
We are thinking to purge/delete the data from data archive store after 2 years.
Frequency of Data archival store usage will be very minimal as it contains data older than 60 days. But, let's expect it once in a month.
Any suggestions for data store in terms of cost and future usage and overall work would be highly appreciated?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您可以尝试两种方法。
您可以在数据库中创建
存储过程，以删除超过2岁的数据。使用（adf）存储过程活动每月或安排的基础。要将60天历史的数据存储在存档中，您可以创建数据库的BACPAC文件并将其存储在Azure Blob存储中。您可以使用访问层 Blob存储的功能，该功能使您可以将数据存储在热/酷/存档访问层中。
如果您只需要一个月访问一次，则可以使用存档访问层。
如果需要快速恢复数据，则可以将BLOB容器从Cool/Archive更新为HOT，并使用ADF将数据从BACPAC文件复制到数据库。
There are 2 approaches which you can try.
You can create a
Stored Procedurewithin your database to delete the data which is older than 2 years. Trigger the stored procedure via Azure Data Factory using (ADF)Stored Procedure Activityon a monthly or adhoc scheduled basis.To store the 60 days old data in archive, you can create a BACPAC file of your database and store it in Azure Blob Storage. You can use access tier feature of Blob Storage which allows you to store data in Hot/Cool/Archive access tier.
You can use archive access tier if you just need to access it once in a month.
In case, if required to restore the data quickly, you can update the Blob container from cool/archive to hot and use ADF to copy data from BACPAC file to database.