Post reply

Archive to the Lake

  • August 7, 2023 at 12:00 am

    #4239561

    Comments posted to this topic are about the item Archive to the Lake

  • August 7, 2023 at 1:31 pm

    #4254939

    very informative and helpful article

  • August 9, 2023 at 6:15 pm

    #4257040

    We considered using the Stretch Database feature a while back, but the service was too costly. This isn't documented by Microsoft, but Stretch Database (I have deduced) actually uses Azure Synapse Analytics (SQL Data Warehouse) to store the data on the backend, and that's why the service is so expensive.

    Another option, which we have not tried but from what I've read could work, is to create an archive database (ie: SalesArchive) and mount the data files in Azure blob storage. The tables can be compressed (possibly ColumnStore?). The advantage of this is that the data is seamlessly integrated in SQL Server.

    This is possible, but if anyone has actually implemented this, I'd like to hear how well it works in terms of technical snags and usability.

    CREATE CREDENTIAL [https://salesarchive.blob.core.windows.net/data] 
    WITH IDENTITY='SHARED ACCESS SIGNATURE', 
    SECRET = '<your SAS key>' 

    CREATE DATABASE SalesArchive 
    ON 
    ( NAME = salesarhive_dat, 
    FILENAME = 'https://salesarhive.blob.core.windows.net/data/salesarhive.mdf' ) 
    LOG ON 
    ( NAME = salesarhive_log, 
    FILENAME = 'https://salesarhive.blob.core.windows.net/data/salesarhive.ldf')

    https://learn.microsoft.com/en-us/sql/relational-databases/databases/sql-server-data-files-in-microsoft-azure

     

     

     

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • August 9, 2023 at 6:25 pm

    #4257043

    Stretch was crazy expensive. Not remotely viable, and deprecated now.

    I think the lake is different, where you use cheap, text (blog) storage.

  • August 14, 2023 at 4:19 pm

    #4261031

    I have only a little experience with these matters, but I am very wary of solutions that involve taking well structured data in relational models and throwing them into files for processing.  I don't see how that can be more efficiently (and thus cheaply) worked with.  It seems the solution is always just to throw more compute power at it, which ends up costing far more than was originally estimated when designing the solution.

    Be still, and know that I am God - Psalm 46:10

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply