Replicating Azure SQL DB Instances on the fly for SaaS apps

September 03, 2020

If you are building a multi-tenant application on Azure, you may have to spin up Azure Sql instances on the fly when onboarding new tenants. I felt this would be a fairly common use case, but to my surprise, I failed to find any articles relating to this exact topic. After going through a number of different articles, I am compiling my learnings here with the hope that someone else will find this post useful.

First off, you need an Azure SDK, and not just any SDK, a ‘management’ SDK. You will find it [here] (https://www.nuget.org/packages/Microsoft.WindowsAzure.Management.Sql/)

The first task, as you may have guessed, is Authentication.

var authContext = new AuthenticationContext(<authority>); //"https://login.microsoftonline.com/"
var credential = new ClientCredential(<appId>, <appSecret>);
var authResult = await authContext.AcquireTokenAsync(<resource>, credential); //“https://management.azure.com/”
var token = new TokenCredentials(authResult.AccessToken, "Bearer");

Once you receive an authentication token, you can move to the next step: instantiating a SqlManagementClient.

SqlManagementClient mgmtClient = new SqlManagementClient(credentials);
mgmtClient.SubscriptionId = <subscriptionId>

I have a database already set up which contains some common data which all tenants need access to. The objective is to replicate this database every time a new tenant is added to the platform. In order to achieve this, we will need to provide an identifier for the existing database along with the resource group, server, service tier, and Sku information for the new Database to be created.

client.Databases.CreateOrUpdate(resourceGroup, server, dbName, new Database(
                location: <location>, //centralus
                sku: new Sku(<skuName>, <tier>), //S0 , Standard
                createMode: "Copy",
                <sourceDatabaseId>)) // /subscriptions/.../resourceGroups/.../providers/Microsoft.Sql/servers/.../databases/<DB name>

The CreateOrUpdate method will return a Database from which you can retrieve the database name or resource ID as required.

And there you have it, how easy was that ? Took me a good few hours to figure it out 😅