Deploy Multi-Tenant Web App to Azure Elastic Database Pool
This post will explore how to deploy a multi-tenant web application to Microsoft Azure and use an Elastic database pool to manage per-tenant databases.
Database per-tenant (multi-db) architecture is not the only way to achieve multi-tenancy. It’s possible to design a multi-tenant application that stores all tenant data in one shared database. Each approach offers its own advantages and disadvantages and it’s up to you to decide which is the right strategy in your case..
Using the Nano ASP.NET Boilerplate CLI tool, you can instantly generate new multi-tenant web applications with single-database or per-tenant database multi-tenancy. There’s also a single-tenant deployment option if you don’t need multi-tenant features.
In this guide, we’ll generate an app with per-tenant (multidb) multi-tenant architecture and deploy it to Azure. We’ll extend the code to work with the Azure SDK and set up Azure resources so that our app can create new databases programmatically. All this will be done by adding just a few lines of code to the default Nano multi-db template code.
If you don’t have the Nano Boilerplate or are coming from the blog tutorials part 1 and part 2 about multi-tenancy, you can still follow along. The code covered is quite similar. Download the GitHub to follow along:
Download the finished version of Part 2 Completed: Simple Multi-Tenant App with Per-Tenant DB
Or if you prefer, the finished version of that: Part 3 Completed: Multi-Tenant App with Azure Elastic DB
You can grab the postman collection for that simple multi-tenant app here.
Before we get started, let’s take a look at why a database per tenant architecture might be a favorable option.
Why Per-Tenant Database
When building a Software as a Service (SaaS) application, opting for a per-tenant database approach can provide several significant advantages. Firstly, it enhances data isolation and security, as each tenant’s data is stored separately. This minimizes the risk of data breaches and simplifies compliance with data protection regulations.
A per-tenant database allows for more granular performance tuning and resource allocation. You can optimize the database settings based on the specific needs and usage patterns of each tenant, leading to better overall application performance. Elastic database pools handle most performance concerns automatically.
Having tenant databases separate facilitates easier data backup and recovery. In the event of data corruption or loss, only the affected tenant’s database needs to be restored, reducing downtime and impact on other tenants.
A per-tenant database architecture can also simplify scaling. As your SaaS application grows, you can distribute the load more effectively by scaling databases independently, ensuring consistent performance across all tenants. Azure elastic database pools provide nice functionality in this department.
So if you are willing tolerate a bit more complexity, a per-tenant database strategy can offer quite a few advantages when building SaaS applications.
Create a new Multi Tenant Application
Either from the command line with dotnet new nano or by creating a new project in Visual Studio, create a new multi-tenant application with per-tenant databases. Leave the multi-tenancy option to default (multidb).
dotnet new nano -n NanoAzureThe default connection string will be set for a local SQL Server instance with the name of the database being the same name as the project. You can edit this in appsettings.json
What needs to change?
At this point, we have a multi-tenant app that will create new databases for new tenants on a local SQL server host. There’s just one issue… this only works locally.
As soon as we publish our code to a cloud platform like Azure, we’ll get errors whenever we try to create a new tenant with an isolated database. There’s no code that communicates with Azure, directing it to create a new database. Luckily though, once we install the necessary Azure SDK NuGet packages, doing so is surprisingly easy.
There are some arrangements to be made in the Azure platform though before we get coding.
Azure Elastic Database Pool
We need to have an Azure Elastic Database pool. The procedure to create one is outlined in the next steps, but first lets see why elastic database pools are so useful in a multi-database multi-tenant scenario.
How Elastic Pools Can Help (Our Solution)
While the per-tenant database approach offers numerous benefits, it also introduces some significant challenges, particularly in terms of cost and management. As the number of tenants grows, so does the number of databases. This can lead to increased costs for database storage and management, as well as potential performance issues. Managing a large number of databases can become complex and time-consuming, requiring additional resources and potentially leading to inefficiencies.
Azure offers a solution to these challenges through Elastic Pools. Elastic Pools allow you to manage and scale multiple databases that have varying and unpredictable usage patterns. Here’s how Elastic Pools can help manage costs and enhance performance:
- Cost Efficiency: Elastic Pools provide a cost-effective way to manage multiple databases by sharing resources among them. Instead of provisioning each database individually, you can allocate a shared pool of resources (e.g., DTUs or vCores) to be used by all databases within the pool. This can significantly reduce costs, especially for databases with intermittent or unpredictable workloads.
- Performance Optimization: By sharing resources, Elastic Pools ensures that databases get the necessary resources when they need them. This dynamic allocation of resources helps in maintaining consistent performance across all databases, even under fluctuating workloads. Databases in the pool can automatically scale within the allocated resources, ensuring optimal performance.
- Simplified Management: Elastic Pools simplify the management of multiple databases by centralizing resource allocation and monitoring. Administrators can easily manage and monitor the performance and resource usage of all databases within the pool from a single interface. This reduces the complexity and overhead associated with managing a large number of individual databases.
- Scalability: As your application grows, Elastic Pools make it easier to scale your database infrastructure. You can adjust the size of the pool or add more resources as needed, without having to manage each database individually. This ensures that your application can scale efficiently to accommodate more tenants.
By leveraging Elastic Pools, you can effectively manage the costs and performance challenges associated with a per-tenant database architecture, ensuring a scalable and efficient solution for your SaaS application.
Set up an Azure Elastic Database pool
You will need to have an Azure account with a Hosting plan for the rest of the steps.
In the Azure portal resource explorer, select the Database Server that hosts SQL Databases, and select New Elastic Pool.
Azure’s default Compute + Storage settings are ambitious, so be sure to adjust the pool performance to suit your preference and budget. The basic service tier with the lowest DTU setting will be fine for this learning project. The cost is about $70 for a pool that can support up to 100 databases. Compare that to about $5 for a basic 5-DTU single database.
Give your Elastic Pool a name and wait for the creation of the pool to complete. You can access it from the resource explorer when the process is finished.
Within your new SQL elastic pool, you can create a new database to serve as the primary database for your new App Service. Create a database with a new name.
Deploying The Application to Microsoft Azure
There are several ways you can publish an application to Azure, but we’ll do this quickly using Visual Studio 2022. Be sure to sign in to your Azure account.
Publish Profile
To create a publish profile in Visual Studio 2022, right-click NanoAzure.WebApi in the solution explorer. From the context menu, choose Publish.
Select Azure for the target and choose Azure App Service. We’ll create a new App Service for this project so click Create New and give it a name.
Select your hosting plan and resource group. Create, then select the new App Service and click Finish. Now we have a new Publish Profile.
Connect an Azure SQL Database to the application
In the publish profile, go to the Service Dependencies section, click on the menu, and select Connect. In the connection window, choose Azure SQL Database and click Next. This will take you to a list of all the available databases in your Azure subscription.
Select the new database we created in the elastic pool. Leave the database connection string name as is and provide the connection username and password. Save the connection string in the Azure App Settings (default) and click finish. This will configure your App Service application to use the new database.
Publishing the Application to Azure
Now we can publish our application by clicking on Publish.
Visual Studio will build the project and then publish it to Azure. When that finishes, we have now deployed our application on Azure and are using a database within an elastic pool.
The next thing we need to do is to give this App Service the permissions necessary to create new databases in our Azure subscription. We can do that by adding a role assignment as shown in the next step.
Within the Azure portal, you can move databases into and out of an elastic pool. For example, if you had already published your application to an existing database, you can move that database to the elastic pool.
Assign Azure Role Assignment to App Service
In the next key step, we’re going to grant our application the permission to create a new database in the specified elastic pool within our resource group. To do that, we need to add a new role assignment.
Start by accessing the application service from the resource explorer. Then, go to Settings and select Identity. In the System Assigned tab, click on the Azure role assignments button.
Next, click on Add role assignment. We need to grant the App Service enough scope and capability to be able to create databases within our Azure service. For this example we’ll choose Resource Group for Scope, and select the group that contains our elastic pool and database server. You’ll need to select the Azure subscription, set Contributor for the role and save your changes.
It may take a few minutes for the new role assignment to appear on the Azure role assignment page.
Refactoring the Application Code
At this point, the Azure infrastructure is set up to support multiple databases in an elastic pool, and our app has the role authority necessary to manage database resources within our Azure subscription.
It’s time now to get back to our solution to refactor our code. We’ll start by adding in a few new configuration variables in the appsettings.json related to our Azure service. Let’s return to Visual Studio to do this.
Edit the Application Configuration file
Locate the application configuration file appsettings.json in the WebApi project and add these Azure configurations that we can access later in the code to authenticate our application to Azure.
"Azure": {
"ElasticPoolName": "Your_Elastic_Pool_Name",
"ResourceGroupName": "Your_Resource_Name",
"SqlServerName": "Your_SqlServer_Name",
"SubscriptionId": "Your_Azure_Subscription_Name"
}Add Azure Resource Management SDK to the Infrastructure project
Installing the Azure SDK packages will allow us to interact with Azure resources programmatically. We need to add the following NuGet packages to the infrastructure project in our solution. Right-click on the infrastructure project and select manage NuGet packages, or you can install these via the command line
From .NET CLI
dotnet add package Azure.ResourceManager
dotnet add package Azure.ResourceManager.SqlFrom Package Manager
NuGet\Install-Package Azure.ResourceManager
NuGet\Install-Package Azure.ResourceManager.Sql
Tenant management service
With the Azure SDK NuGet packages added, the solution now can use the Azure commands. We’ll refactor the tenant management service so that when we create a new tenant, our application will also create a new database for the tenant within the Azure SQL Elastic Pool.
Import namespaces
Open up TenantManagementService. Start by importing the various Azure using statements we need at the top:
using Azure.Core;
using Azure.Identity;
using Azure.ResourceManager;
using Azure.ResourceManager.Sql;Check for environment
Since it would be nice to have the create tenant database process work both locally and on production, we’ll create a conditional check for the environment. By injecting the IWebHostEnvironment Interface in the TenantManagementService, we can check the web hosting environment of our application.
private readonly IWebHostEnvironment _environment;
public TenantManagementService(BaseDbContext baseDbContext, IConfiguration configuration, IServiceProvider serviceProvider, UserManager<ApplicationUser> userManager, IWebHostEnvironment environment)
{
_baseDbContext = baseDbContext;
_configuration = configuration;
_serviceProvider = serviceProvider;
_userManager = userManager;
_environment = environment; // add the environment
}Refactor the SaveTenant Method
Finally, we can add the code that creates new databases in the Azure cloud.
In the Save Tenant method, after checking if the request.hasIsolatedDatabase value is true, we’ll add our new code. Note that we need to use a fully qualified name whenever we use Response since the Azure SDK also contains a class named Response.
If the environment is development, the existing local SQL Server code will run. If the environment is production, we’ll initialize the Azure resources and create a new database in the elastic pool.
The complete code is as follows:
public async Task<Application.Common.Wrapper.Response<Tenant>> SaveTenant(CreateTenantRequest request)
{
string organizationSlug = NanoHelpers.ToUrlSlug(request.Id);
bool tenantExists = await _baseDbContext.Tenants.AnyAsync(x => x.Id == organizationSlug);
if (tenantExists) // check if tenant exists
{
return Application.Common.Wrapper.Response<Tenant>.Fail("Tenant already exists");
}
string connectionString = _configuration.GetConnectionString("DefaultConnection");
SqlConnectionStringBuilder builder = new(connectionString);
string mainDatabaseName = builder.InitialCatalog; // retrieve the database name
string tenantDbName = mainDatabaseName + "-" + organizationSlug;
builder.InitialCatalog = tenantDbName; // set new database name
string modifiedConnectionString = builder.ConnectionString; // create new connection string
Tenant tenant = new() // create a new tenant entity
{
Id = request.Id,
Name = request.Name,
ConnectionString = request.HasIsolatedDatabase ? modifiedConnectionString : null,
CreatedOn = DateTime.UtcNow,
IsActive = true
};
try
{
_ = await _baseDbContext.Tenants.AddAsync(tenant);
ApplicationUser user = new()
{
UserName = request.AdminEmail + "." + tenant.Id, // username must be unique: Email + TenantId
FirstName = "Default",
LastName = "Admin",
Email = request.AdminEmail,
EmailConfirmed = true,
TenantId = tenant.Id
};
IdentityResult
result = await _userManager.CreateAsync(user,
request.Password); // create a default admin user upon tenant creation
if (result.Succeeded) // success -- add to admin role
{
if (request.HasIsolatedDatabase) // create new db and run any appDb migrations (this section can be removed if you don't plan to use per-tenant databases)
{
try
{
// Our Refactoring Start Here
IServiceScope scopeTenant = _serviceProvider.CreateScope();
ApplicationDbContext dbContext =
scopeTenant.ServiceProvider.GetRequiredService<ApplicationDbContext>();
dbContext.Database.SetConnectionString(modifiedConnectionString);
if (_environment.IsDevelopment()) // Create a new database in the local SQL Server instance when app is running in development mode
{
if ((await dbContext.Database.GetPendingMigrationsAsync()).Any())
{
Console.ForegroundColor = ConsoleColor.Blue;
Console.WriteLine(
$"Applying ApplicationDB Migrations for New '{tenant.Id}' tenant.");
Console.ResetColor();
await dbContext.Database.MigrateAsync();
}
}
else // Create a new database in the Elastic Pool in Azure SQL Server when app is running in production mode
{
// Initializing needed Azure Resources
IConfigurationSection azureConfig = _configuration.GetSection("Azure");
DefaultAzureCredential credential = new();
ArmClient armClient = new(credential, azureConfig["SubscriptionId"]);
Azure.Response<SqlServerResource> sqlServer = await armClient.GetResourceGroupResource(
new ResourceIdentifier(
$"/subscriptions/{azureConfig["SubscriptionId"]}/resourceGroups/{azureConfig["ResourceGroupName"]}"))
.GetAsync()
.Result
.Value.GetSqlServerAsync(azureConfig["SqlServerName"]);
// Create a new database in the Elastic Pool
ArmOperation<SqlDatabaseResource> databaseResponse = await sqlServer.Value
.GetSqlDatabases().CreateOrUpdateAsync(WaitUntil.Completed,
tenantDbName,
new SqlDatabaseData(new AzureLocation(sqlServer.Value.Data.Location))
{
ElasticPoolId =
(await sqlServer.Value.GetElasticPoolAsync(
azureConfig["ElasticPoolName"]))
.Value.Data.Id
});
// apply migrations to the new database
await dbContext.Database.MigrateAsync();
if (databaseResponse.Value.Data.Id == null)
{
return Application.Common.Wrapper.Response<Tenant>.Fail(
$"Error creating isolated database for tenant {tenant.Id}");
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
_ = await _userManager.AddToRoleAsync(user, "admin"); // identity save changes to baseDb
_ = await _baseDbContext.SaveChangesAsync(); // tenant save changes to baseDb
return Application.Common.Wrapper.Response<Tenant>.Success(tenant);
}
else // fail -- return error messages
{
List<string> messages = new();
foreach (IdentityError error in result.Errors)
{
messages.Add(error.Description);
}
return Application.Common.Wrapper.Response<Tenant>.Fail(messages);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}The code to programmatically create Azure database resources will first look at our appconfig.json file to get any necessary variables. Using our credentials, we get a sql server object to work with from Azure.
In the next line, we’ll use this sql server object to create a new database for the tenant. We’ll await this operation and the next one where we apply all pending migrations.
Creating a new database on Azure will take several seconds, so you might want to provide some progress indication for the user.
With this code in place our application will now create new tenant databases in our Azure elastic pool!
Whenever we create new migrations, the AddAndMigrateTenantDatabases extension will loop through all the tenant connection strings and apply the changes for each tenant when the application starts up (deployments).
Testing Multi-Tenant Operations in Azure with Postman
Now let’s publish our changes to Azure and test creating a new tenant with Postman.
Remember to set hasIsolatedDatabase to true. From the API response, we can see that we have successfully created a new tenant with an isolated database in our Azure SQL Elastic Pool.
The connection string will be for the new tenant database
In Conclusion
As you can see it’s not so difficult to manage multiple databases on an Azure elastic database pool. With only a few additions to the Nano Boilerplate code, our app now creates new tenant databases programmatically in an Azure elastic database pool.