Designing Reliable and Cost-Effective SQL Server Backups to Azure

Introduction

Backups are one of those things we rarely think about—until the moment we need them the most.

In a recent scenario, I had to design a solution to move and retain large SQL Server backups (around 2TB) from on-premises to Azure for a short duration. What initially seemed simple quickly evolved into a design problem involving cost, performance, automation, and reliability.

This post walks through the thinking, decisions, and lessons learned.

The Problem

  • Multiple SQL Server databases
  • One large database (~2TB)
  • Daily backups required for 2 weeks
  • Fast restore capability needed
  • Cost-sensitive solution

This wasn’t just backup—it was controlled, intentional data movement.

The Questions That Matter

This is where the real engineering begins:

  • Do we need Hot, Cool, or Archive storage?
  • What if restore is needed immediately?
  • Should we copy .bak files or backup directly?
  • How do we automate safely?
  • What happens if authentication expires?
  • How do we handle large databases efficiently?

These questions shape architecture more than tools do.

The Approach

After evaluating options, the solution became clear:

  • Use native SQL Server Backup-to-URL
  • Store in Azure Blob Storage (Cool tier)
  • Automate with SQL Server Agent
  • Use SAS-based authentication
  • Optimize large DB backups with striping

Implementation

🔹 Azure Setup


  • Storage Account → Standard + LRS
  • Private Blob Container
  • SAS Token with:
    • Write
    • Create
    • List

SQL Credential

CREATE CREDENTIAL [https://<storageaccount>.blob.core.windows.net/<container>]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS_TOKEN>';

For SQL server 2014
SQL Server 2014 was built before "Shared Access Signatures" (SAS) were fully integrated into the engine's backup logic. Unlike later versions (2016+), SQL Server 2014 generally expects you to use your Storage Account Access Key and a slightly different syntax.
Here is how to fix the "Specified without a Credential" error for your specific version:
1. Change the Credential to use the Access Key
Instead of a SAS token, go to the Azure Portal, navigate to your Storage Account > Access Keys, and copy Key1.
Then, recreate your credential like this:
-- Replace 'YourAccountName' with the actual name of your storage account
-- Replace 'YourAccessKey' with the long key from the portal
CREATE CREDENTIAL [MyAzureCredential] 
WITH IDENTITY = 'YourAccountName', 
SECRET = 'YourAccessKey';

Note: In SQL 2014, the IDENTITY is your account name, not 'Shared Access Signature'.
2. Explicitly Reference the Credential in the Backup Command
In SQL 2014, the engine doesn't always "auto-match" the URL to the credential. You must tell it which one to use using the WITH CREDENTIAL clause:

BACKUP DATABASE [YourDatabaseName]
TO URL = 'https://youraccount.blob.core.windows.net/yourcontainer/yourbackup.bak'
WITH CREDENTIAL = 'MyAzureCredential', 
STATS = 10;

Backup Command for later versions

BACKUP DATABASE YourDatabase
TO URL = 'https://<storageaccount>.blob.core.windows.net/<container>/YourDatabase.bak'
WITH COMPRESSION, STATS = 10;

Handling Large Databases

For large databases (~2TB):

BACKUP DATABASE BigDB
TO 
  URL = '..._1.bak',
  URL = '..._2.bak',
  URL = '..._3.bak',
  URL = '..._4.bak'
WITH COMPRESSION, STATS = 10;

Improves speed
Reduces bottlenecks

Challenges & Lessons

This is where things get real:

  • Credential mismatch → Msg 3289
  • SAS token formatting (? issue)
  • Azure AD vs SAS confusion
  • Network throughput limitations
  • Large DB performance tuning
  • A single slash in a credential name can stop everything.

 Cost Optimization

  • Cool tier → balanced choice
  • Archive tier → avoided (slow restore)
  • Lifecycle policy → auto delete after retention

Result: low cost + high readiness

Key Takeaways

  • Design backups for restore, not storage
  • Automation reduces operational risk
  • Cost optimization must not impact recovery
  • Test restore, not just backup
  • Small misconfigurations can cause major failures
Jay

Comments

Post a Comment

Popular posts from this blog

ASM Integrity check failed with PRCT-1225 and PRCT-1011 errors while creating database using DBCA on Exadata 3 node RAC

Image
  Error: The error message PRCT-1225: failed to verify Oracle Automatic Storage Management (Oracle ASM) user credentials using the command 'asmcmd credverify' indicates that the asmcmd credverify command failed to verify the Oracle ASM user credentials. This can happen for a number of reasons, including: The user credentials are incorrect. The user credentials file is corrupted. The user does not have the necessary permissions to run the asmcmd credverify command. There is a problem with the Oracle ASM instance. To troubleshoot this error, you can try the following: Make sure that the user credentials are correct. You can do this by trying to log in to the Oracle ASM instance using the same credentials. Check the user credentials file for errors. You can do this by using a text editor to open the file and inspect the contents. Make sure that the user has the necessary permissions to run the asmcmd credverify command. You can do this by checking the user's permissi...
Read more

Lock Tables in MariaDB

Whether or not you need to lock tables while backing up MariaDB depends on a few factors, including the type of backup you are performing and the tools you are using. If you are performing a full backup , you should lock the tables to ensure that the backup is consistent. This is because a full backup copies all of the data in the database, including any data that is currently being modified by transactions. If you do not lock the tables, it is possible that the backup could contain inconsistent data. If you are performing a differential backup or an incremental backup , you may not need to lock the tables. Differential and incremental backups only copy the data that has changed since the previous backup. This means that the data is already consistent, so there is no need to lock the tables. However, some backup tools may require you to lock the tables, even if you are performing a differential or incremental backup. This is because these tools may need to modify the database in some ...
Read more

Life is beautiful

Image
Life brings unexpected tragedies that are beyond our control. It's heartbreaking to witness people facing circumstances they never chose. We react with tears and inevitably wonder, "What if that happens to me?" As humans, we naturally focus on the positive and avoid dwelling on such thoughts—and that's healthy. But this perspective has shaped how I approach life: Am I prepared for these scenarios? Are my loved ones protected if something goes wrong? If I were to die, I couldn't be there to tell them to stay strong—they'd have to face that grief alone. But I can give them financial security through proper planning, wills, and trusts. These are tangible ways to care for them even when I'm gone. Beyond that, what else can I control? I don't waste energy worrying about an uncertain past or future. Instead, I choose to spread peace, truly listen to others, and share genuine smiles. I embrace the principle of living fully while letting others do the same. ...
Read more