How to export SQL Database directly to blob storage programmatically

Question

I need to programmatically backup/export a SQL Database (either in Azure, or a compatible-one on-prem) to Azure Storage, and restore it to another SQL Database. I would like to use only NuGet packages for code dependencies, since I cannot guarantee that either the build or production servers will have the Azure SDK installed. I cannot find any code examples for something that I assume would be a common action. The closest I found was this:

https://blog.hompus.nl/2013/03/13/backup-your-azure-sql-database-to-blob-storage-using-code/

But, this code exports to a local bacpac file (requiring RoleEnvironment, an SDK-only object). I would think there should be a way to directly export to Blob Storage, without the intermediary file. One thought was to create a Stream, and then run:

services.ExportBacpac(stream, "dbnameToBackup")

And then write the stream to storage; however a Memory Stream wouldn't work--this could be a massive database (100-200 GB).

What would be a better way to do this?

Answer 1

Based on my test, the sql Microsoft Azure SQL Management Library 0.51.0-prerelease support directly export the sql database .bacpac file to the azure storage.

We could using sqlManagementClient.ImportExport.Export(resourceGroup, azureSqlServer, azureSqlDatabase,exportRequestParameters) to export the .bacpac file the azure storage.

But we couldn't find ImportExport in the lastest version of Microsoft Azure SQL Management Library SDK. So we could only use sql Microsoft Azure SQL Management Library 0.51.0-prerelease SDK.

More details about how to use sql Microsoft Azure SQL Management Library to export the sql backup to azure blob storage, you could refer to below steps and codes.

Prerequisites:

Registry an App in Azure AD and create service principle for it. More detail steps about how to registry app and get access token please refer to document.

Details codes:

Notice: Replace the clientId,tenantId,secretKey,subscriptionId with your registered azure AD information. Replace the azureSqlDatabase，resourceGroup，azureSqlServer，adminLogin，adminPassword，storageKey，storageAccount with your own sql database and storage.

static void Main(string[] args)
{

    var subscriptionId = "xxxxxxxx";
    var clientId = "xxxxxxxxx";
    var tenantId = "xxxxxxxx";
    var secretKey = "xxxxx";
    var azureSqlDatabase = "data base name";
    var resourceGroup = "Resource Group name";
    var azureSqlServer = "xxxxxxx"; //testsqlserver 
    var adminLogin = "user";
    var adminPassword = "password";
    var storageKey = "storage key";
    var storageAccount = "storage account";
    var baseStorageUri = $"https://{storageAccount}.blob.core.windows.net/brandotest/";//with container name endwith "/"
    var backName = azureSqlDatabase + "-" + $"{DateTime.UtcNow:yyyyMMddHHmm}" + ".bacpac";  //back up sql file name
    var backupUrl = baseStorageUri + backName;
    ImportExportOperationStatusResponse exportStatus = new ImportExportOperationStatusResponse();
    try
    {
        ExportRequestParameters exportRequestParameters = new ExportRequestParameters
        {
            AdministratorLogin = adminLogin,
            AdministratorLoginPassword = adminPassword,
            StorageKey = storageKey,
            StorageKeyType = "StorageAccessKey",
            StorageUri = new Uri(backupUrl)
        };

        SqlManagementClient sqlManagementClient = new SqlManagementClient(new Microsoft.Azure.TokenCloudCredentials(subscriptionId, GetAccessToken(tenantId, clientId, secretKey)));
        var export = sqlManagementClient.ImportExport.Export(resourceGroup, azureSqlServer, azureSqlDatabase,
                        exportRequestParameters); //do export operation

        while (exportStatus.Status != Microsoft.Azure.OperationStatus.Succeeded) // until operation successed
        {
            Thread.Sleep(1000 * 60);
            exportStatus = sqlManagementClient.ImportExport.GetImportExportOperationStatus(export.OperationStatusLink);
        }

        Console.WriteLine($"Export DataBase {azureSqlDatabase} to Storage {storageAccount} Succesfully");
    }

    catch (Exception exception)
    {

        //todo

    }

}

private static string GetAccessToken(string tenantId, string clientId, string secretKey)
{
    var authenticationContext = new AuthenticationContext($"https://login.windows.net/{tenantId}");
    var credential = new ClientCredential(clientId, secretKey);
    var result = authenticationContext.AcquireTokenAsync("https://management.core.windows.net/",
        credential);

    if (result == null)
    {
        throw new InvalidOperationException("Failed to obtain the JWT token");
    }

    var token = result.Result.AccessToken;
    return token;
}

Result like this:

1.Send request to tell sql server start exporting to azure blob storage

2.Continue sending request to monitor the database exported operation status.

3.Finish exported operation.

Answer 2

Here's an idea:

Pass the stream to the .ExportBacPac method but hold a reference to it on a different thread where you regularly empty and reset the stream so that there's no memory overflow. I'm assuming here, that Dac does not have any means to access the stream while it is being filled.

The thing you have to take care of yourself though is thread safety - MemoryStreams are not thread safe by default. So you'd have to write your own locking mechanisms around .Position and .CopyTo. I've not tested this, but if you handle locking correctly I'd assume the .ExportBacPac method won't throw any errors while the other thread accesses the stream.

Here's a very simple example as pseudo-code just outlining my idea:

ThreadSafeStream stream = new ThreadSafeStream();

Task task = new Task(async (exitToken) => {
    MemoryStream partialStream = new MemoryStream();

    // Check if backup completed
    if (...) 
    {
         exitToken.Trigger();
    }

    stream.CopyToThreadSafe(partialStream);
    stream.PositionThreadSafe = 0;

    AzureService.UploadToStorage(partialStream);

    await Task.Delay(500); // Play around with this - it shouldn't take too long to copy the stream
});

services.ExportBacpac(stream, "dbnameToBackup");

await TimerService.RunTaskPeriodicallyAsync(task, 500);