locked
On-Premise SQL SERVER 2016 Database backup to Azure Storage Account as Blob Type= Block Blob and NOT Page Blob? RRS feed

  • Question

  • Hi All,
    Recently I was working on Data Warehouse Disaster Recover. Our SQL server 2016 is on-premise and on VM. I need to take backup of 15-16 Databases ans store them out of VM. Size of these databases were around 1 TB every day and was planning to store minimum 5 days of backup so roughly was looking for 6-7 TB space and it should be outside of VM. After checking with infra, found that with current setup, they can't arrange 6-7 TB of space outside of VM. 

    I started exploring Azure Blob Storage to store our backup on Azure Cloud and found this thread: https://www.mssqltips.com/sqlservertip/4900/perform-onpremises-sql-server-database-backups-using-maintenance-plans-to-azure-blob-storage/ and https://sqlbak.com/blog/sql-server-backup-to-url

    I followed steps from above thread and was able to take the backup and store on Azure Storage account as Page Blob and not Block Blob (Conclusion:If you use SQL Server Maintenance Plans to take the backup to URL then you are forced to use Azure Storage account Key to create Credentials under Security and use this to connect to Storage account then your backup file will be stored as Page Blob and we have no control to change this to Block Blob: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-2017. To Store your backup as Block Blob, you must use SAS (Shared Access Signature) but then you can't use SQL Server Maintenance Plans with SAS.)

    Next step was to Cleanup the 5 days Older Backup Files. 1st I thought to use Azure Blob "Lifecycle Management" but then I realized it works only with Block Blob and not with Page Blob. Then I used PowerShell Script to cleanup the older .bak Page Blob files: https://social.msdn.microsoft.com/Forums/en-US/df73b5a3-d27b-45ee-b440-c1bed99f9b4e/how-to-automate-bak-delete-from-azure-blob?forum=sqlnetfx


    After doing above all, everything was running perfectly and after 7 days, I had 5 days active backup and 2 days Soft Delete backup. I thought of calculating cost for storing 7-8 TB as page blob every month and cost surprised me. It was going to cost me 800-900 AUD every month which was very very high compare to Block Blob: https://docs.microsoft.com/en-us/answers/questions/39483/calculating-price-for-page-blob-in-azure-storage-a.html 

    Next I started looking for options to store my Backup files as Block Blob and not as page Blob. After spending 1-2 days I found that It is possible to take backup using Shared Access Signature but not going to work with SQL Server Maintenance Plans. This link helped me to write SQL Code to take Backup using SAS and store it as Block Blob: https://blog.sqlauthority.com/2018/07/17/sql-server-backup-to-url-script-to-generate-credential-and-backup-using-shared-access-signature-sas/. Also remember this works with SQL SERVER 2016. I have one Server which is SQL Server 2014 and It is not working on 2014 version so with 2014 version you have only option to use Storage Account Key and that will be Page Blob and not the Block Blob. https://blog.sqlauthority.com/2017/06/22/sql-server-unable-restore-url-specified-url-points-block-blob-backup-restore-operations-block-blobs-not-permitted/
    Note: I generated Ad-hoc SAS with following settings: Initially I did not check "Service" Under "Allowed resource types" and I was getting error "Azure Storage Explorer - Inadequate resource type access"


    So 1st I created a Credentials using SAS under Security in SQL Server:
    https://stuart-moore.com/creating-azure-blob-storage-account-for-sql-server-backup-and-restore-via-the-portal/
    CREATE CREDENTIAL [https://<storageaccount>.blob.core.windows.net/<container>]
    WITH IDENTITY='SHARED ACCESS SIGNATURE'
     , SECRET = 'SAS token'
    So in our example that would be:

    view sourceprint?
    CREATE CREDENTIAL [https://dbatoolslas.blob.core.windows.net/sql]
    WITH IDENTITY='SHARED ACCESS SIGNATURE'
     , SECRET = 'sv=2018-03-28&amp;ss=b&amp;srt=c&amp;sp=rwdlac&amp;se=2019-04-03T17:20:25Z&amp;st=2019-04-03T09:20:25Z&amp;spr=https&amp;sig=PLpxNQCW%2FftHsC2NFgR3f4UUSIGGOtRRPLyLG5G90Ak%3D'

    Common mistakes when creating this type of credetial are:

     1. leaving a trail space on the URL in the name
     2. Not removing the ? at the start of the SAS token
     3. Case insensitivity in the SAS token
     4. Not setting the IDENTITY value correctly, it must be SHARED ACCESS SIGNATURE

    2nd created a Job using below query and scheduled it.
    IF OBJECT_ID('tempdb..#ListOfDatabases') IS NOT NULL
        DROP TABLE #ListOfDatabases

    SELECT D.name AS SysDatabaseName, 
           CAST(SUM((F.size * 8) / 1024) AS VARCHAR(26)) + ' MB' AS DBSize, 
           ROW_NUMBER() OVER(ORDER BY SUM((F.size * 8) / 1024)) AS RowNumber
    INTO #ListOfDatabases
    FROM sys.master_files F
         INNER JOIN sys.databases D ON D.database_id = F.database_id
    WHERE D.name IN ('master','msdb')
    GROUP BY D.name
    ORDER BY RowNumber


    DECLARE 
    @Date AS NVARCHAR(25), 
    @TSQL AS NVARCHAR(MAX), 
    @ContainerName AS NVARCHAR(MAX), 
    @StorageAccountName AS VARCHAR(MAX), 
    @SASKey AS VARCHAR(MAX), 
    @DatabaseName AS SYSNAME, 
    @InitialValue INT, 
    @MaxValue INT

    SELECT @Date = FORMAT(GETDATE(), 'yyyy_MM_dd_hh_mm_ss_tt')
    SELECT @StorageAccountName = 'Your storage Account' --- Find this from Azure Portal
    SELECT @ContainerName = 'Your Blob Container' --- Find this from Azure Portal
    SELECT @InitialValue = 1
    SELECT @MaxValue = MAX(RowNumber) FROM #ListOfDatabases

    WHILE @InitialValue <= @MaxValue
        BEGIN
            SELECT @DatabaseName = SysDatabaseName FROM #ListOfDatabases WHERE RowNumber = @InitialValue
            SELECT @TSQL = 'BACKUP DATABASE [' + @DatabaseName + '] TO '
            SELECT @TSQL+='URL = N''https://' + @StorageAccountName + '.blob.core.windows.net/' + @ContainerName + '/' + @DatabaseName + '_full_backup_' + @Date + '.bak'''
            SELECT @TSQL+=' WITH COMPRESSION, CHECKSUM, NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10'
            SET @InitialValue = @InitialValue + 1
            EXECUTE (@TSQL)
        END

    3rd, created Lifecycle Management to delete any Block Blob files older than 5 Days. There is a catch with Lifecycle Management. What happens if your backup is keep failing and there is no new file and older file you keep deleting. So It is better to use PowerShell Script and  Delete only when there is a new file.  https://social.msdn.microsoft.com/Forums/en-US/df73b5a3-d27b-45ee-b440-c1bed99f9b4e/how-to-automate-bak-delete-from-azure-blob?forum=sqlnetfx

    4th, Was taking a backup of Lager Databases (400 GB) and failed with following error: 

    "Write on "" failed: 1117(The request could not be performed because of an I/O device error.) [SQLSTATE 42000] (Error 3202)  BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).

    " and here is the FIX: https://docs.microsoft.com/en-us/archive/blogs/sqlcat/backing-up-a-vldb-to-azure-blob-storage Conclusion: If using Backup to URL to create striped backups of large databases (over 48 GB per stripe), specify MAXTRANSFERSIZE = 4194304 and BLOCKSIZE = 65536 in the BACKUP statement.

    Replace SELECT @TSQL+=' WITH COMPRESSION, CHECKSUM, NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10' with  SELECT @TSQL+=' WITH COMPRESSION, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536, CHECKSUM, NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10' in query mentioned a 2nd step. 


    https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-2017
    Backup to block blob vs. page blob
    There are two types of blobs that can be stored in the Microsoft Azure Blob storage service: block and page blobs. SQL Server backup can use either blob type depending upon the Transact-SQL syntax used: If the storage key is used in the credential, page blob will be used; if the Shared Access Signature is used, block blob will be used.

    Backup to block blob is only available in SQL Server 2016 or later version. Backup to block blob instead of page blob if you are running SQL Server 2016 or later. The main reasons are:

    Shared Access Signature is a safer way to authorize blob access compared to storage key.
    You can backup to multiple block blobs to get better backup and restore performance, and support larger database backup.
    Block blob is cheaper than page blob.
    Customers that need to backup to page blobs via a proxy server will need to use backuptourl.exe.
    Backup of a large database to blob storage is subject to the limitations listed in Managed instance T-SQL differences, limitations, and known issues.

    If the database is too large, either:

    Use backup compression or
    Backup to multiple block blobs


    Conclusion: If you use SQL Server Maintenance Plans to take the backup to URL then you are forced to use Azure Storage account Key to create Credentials under Security and use this to connect to Storage account then your backup file will be stored as Page Blob and we have no control to change this to Block Blob: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-2017. To Store your backup as Block Blob, you must use SAS (Shared Access Signature) but then you can't use SQL Server Maintenance Plans with SAS and you need to use T-SQL to take the backup

    If you want to Convert Page Blob to Block Blob and Vice-Versa then follow this link: https://www.rossmc.co.uk/2020/04/26/ConvertPageBlobToArchive.html

    Question: Anyone has better ideas, I am open for discussion. 


    Thanks Shiven:) If Answer is Helpful, Please Vote








    Thursday, June 25, 2020 6:57 AM

Answers

  • Hi Shievendoo Kumar Dubey,

    You are so kind to share your experience to us.

    Conclusion: If you use SQL Server Maintenance Plans to take the backup to URL then you are forced to use Azure Storage account Key to create Credentials under Security and use this to connect to Storage account then your backup file will be stored as Page Blob and we have no control to change this to Block Blob: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-2017. To Store your backup as Block Blob, you must use SAS (Shared Access Signature) but then you can't use SQL Server Maintenance Plans with SAS and you need to use T-SQL to take the backup

    And the conclusion is great , that is very helpful for us.

    BR,

    MIAOYUXI

    Appreciate your feedback. Would be great if can vote for this thread. Thank you. 

    Thanks Shiven:) If Answer is Helpful, Please Vote


    Friday, June 26, 2020 5:22 AM

All replies

  • Hi Shievendoo Kumar Dubey,

    You are so kind to share your experience to us.

    Conclusion: If you use SQL Server Maintenance Plans to take the backup to URL then you are forced to use Azure Storage account Key to create Credentials under Security and use this to connect to Storage account then your backup file will be stored as Page Blob and we have no control to change this to Block Blob: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-2017. To Store your backup as Block Blob, you must use SAS (Shared Access Signature) but then you can't use SQL Server Maintenance Plans with SAS and you need to use T-SQL to take the backup

    And the conclusion is great , that is very helpful for us.

    BR,

    MIAOYUXI

    Friday, June 26, 2020 5:10 AM
  • Hi Shievendoo Kumar Dubey,

    You are so kind to share your experience to us.

    Conclusion: If you use SQL Server Maintenance Plans to take the backup to URL then you are forced to use Azure Storage account Key to create Credentials under Security and use this to connect to Storage account then your backup file will be stored as Page Blob and we have no control to change this to Block Blob: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-2017. To Store your backup as Block Blob, you must use SAS (Shared Access Signature) but then you can't use SQL Server Maintenance Plans with SAS and you need to use T-SQL to take the backup

    And the conclusion is great , that is very helpful for us.

    BR,

    MIAOYUXI

    Appreciate your feedback. Would be great if can vote for this thread. Thank you. 

    Thanks Shiven:) If Answer is Helpful, Please Vote


    Friday, June 26, 2020 5:22 AM
  • Hi  Shivendoo Kumar Dubey,

    Thanks for your shareing.

    You can mark your own reply as Answer and that will be helpful for us.

    BR,

    MIAOYUXI

    Monday, June 29, 2020 7:47 AM