Multi-Tenant Database Backups

Kennie Nybo Pontoppidan thinks about multi-tenant databases in Azure and how you might back them up:

Backup-restore is not directly supported by standard methods in SQL Server/Azure SQL database. One possible way to backup a tenant could be to have a script, which could bcp data to text files. Restore could similarly be a script, which could bcp from txt files to tables in the destination database. Both scripts could be auto-generated from tenant metadata. If the schema for a tenant has 100 tables, the number of tables in a database in this model grows quickly, and the administrative cost of maintaining scripts and tenant metadata could be high. As a side note, no query execution plans can be reused across tenants, since table names are different.

Thinking about customers which share schema, tables, etc. but need to be handled differently requires some additional effort; pretty much all of the tools around SQL Server assume that you care about things at the table, filegroup, or database level.

Related Posts

Azure Databricks Security

Tristan Robinson looks at what’s currently available in terms of security on Azure Databricks: You’ll notice that as part of this I’m retrieving the secrets/GUIDS I need for the connection from somewhere else – namely the Databricks-backed secrets store. This avoids exposing those secrets in plain text in your notebook – again this would not […]

Read More

Data Lake Organization Tips

Melissa Coates has some great advice for people working with data lakes: Q: Partitioning by date is common. Where should the dates go in the folder hierarchy? Almost always, you will want the dates to be at the end of the folder path. This is because we often need to set security at specific folder […]

Read More

Categories

December 2016
MTWTFSS
« Nov Jan »
 1234
567891011
12131415161718
19202122232425
262728293031