![]() ![]() Using data for business decisions is not a new concept, but making it happen is getting increasingly hard: the volumes of data are growing at an unprecedented pace, and the data sources are often scattered in more and more operational databases. Being able to effectively analyze data generated by various systems that power the business, and to plan next steps based on this analysis is an important advantage to be leveraged. ![]() In current age, making informed business decisions has become a norm for any person or business striving to be successful. Scenario: Exports for analytics purposes avoiding ETL setups A “master” database (not belonging to any specific tenant) could export the relevant data into the commonly used storage, for tenants to consume. in case of multi-tenant application setups). You can't do inserts into them.ĭata virtualization with CETAS - a diagram showing how data is exported using CETAS and queried from the same export location through Data VirtualizationĪ derivative scenario to this may be: using Azure storage to distribute a single shared data set to be used by multiple consumers. Important to know when planning for this scenario: external tables created via CETAS are read only. And we could go a step further and create a view that makes a union of the external table and the remaining data in the local table – almost making it transparent to our application workloads, that parts of the underlying data have changed location. In such a way, it can still be queried using T-SQL from remote location. We could then decide to remove it from database’s local storage. Since we can’t completely delete it from our database, we could export it to a cheaper storage (Azure Blob Storage or Azure Data Lake Gen 2) and create an external table on top of it. This data may only be occasionally requested by the database’s client, yet it holds a lot of space. A couple of example scenarios: sales orders from a few years ago audit or application logs, etc. There are many situations where your operational database may grow large, containing data that is rarely accessed anymore. Scenario: Offloading "cold" data into external storage Exporting local data from SQL Managed Instance into storage accounts on Azure, to be picked up by analytics solutions such as Azure Synapse Analytics – useful in cases when quick exports are needed and setting up an ETL would be too cumbersome.Offloading the data from local storage of SQL Managed Instance to cheaper external storage, to reduce costs, when offloaded data is expected to be rarely used and not updated going forward.There are two main scenarios facilitated by this functionality: With Create External Table As Select (CETAS) functionality for Azure SQL Managed Instance, Generally available as of 18 th May 2023, you can now export local data from your Managed Instance as Parquet or CSV files to Azure Storage accounts and query this data as an External Table. The familiar concept of Data Virtualization in SQL Managed Instance offers capability to read remote Parquet or CSV files through OPENROWSET or External Tables, which you could join with local data. ![]()
0 Comments
Leave a Reply. |