In this blog post we are going to describe how to configure your Azure environment to take native backups of your Azure SQL Managed Instance to your blob storage in a secure fashion.
If you are interested in other posts on how-to discover different aspects of SQL MI - please visit thehttp://aka.ms/sqlmi-howto, which serves as a placeholder for the series.
Introduction
Azure SQL Managed Instance is a feature-rich, platform-as-a-service SQL Server. It stands out from its sibling Azure SQL offerings in that it blends near-100% Transact-SQL compatibility with a powerful managed experience, online scaling, intelligence, Azure integration, and strong service level objectives. This approach of traditional-made-modern is what recommends Azure SQL Managed Instance as a prime candidate to consider when you migrate your legacy workloads to the cloud, modernize the way they operate, and aim to reduce your total cost of operation through cloud efficiencies of scale.
Such migration and modernization projects aren’t simple, though. Far from it – us folks who are directly involved in building Azure SQL Managed Instance are acutely aware of the difficulties our customers face when they consider the alluvial strata of on-premises code, connections, and configurations. This is why we constantly focus on aiding such transitions, aiming to make hybrid configurations and migrations of entire systems not just possible, but successful and ultimately enabling.
Thanks to its built-in backups, Azure SQL Managed Instance already covers most business continuity and disaster recovery scenarios. We take secure backups to a remote blob storage that is not accessible to anything or anyone except that Azure SQL Managed Instance, and those backups can only be accessed for the purpose of recovering information by the authorized users. Our backups ensure that customers can recover to any needed point in time with the help of PITR (Point In Time Restore) backups or to a certain snapshot in time for the LTR backups (Long Term Retention). There are still, however, valid and important scenarios in which a user-initiated backup command comes in handy. Such cases include regulatory purposes, dev/test, analytics and a bunch more.
In this article we'll show you how to set up your environment to take secure native SQL backups from Azure SQL Managed Instance to Azure blob storage.We shall be using the current latest & greatest available features of Azure and SQL Managed Instance, namely: managed identities, private link, and service endpoint policies. If you haven’t used them or aren’t sure how they interplay with Azure SQL Managed Instance, here’s an opportunity to figure it out as we go along!
TL;DR
If you only want to get the gist of the article, the procedure we'll perform consists of the following:
- Use managed identity to authorize your Azure SQL Managed Instance to write to the blob storage.
- Set up a private endpoint to establish connectivity from Azure SQL Managed Instance to blob storage.
- Close off public access on the blob storage account.
- Apply a service endpoint policy on the Azure SQL Managed Instance's subnet to prevent data exfiltration.
The Setup
As a quick refresher and to establish how we name things, this is our setup:
Diagram 1. Virtual network with one subnet containing a SQL Managed Instance, and an Azure Storage account with a backup container.
The resources we’ll be working with are:
- One Azure SQL Managed Instance named [mysqsqlmi01].
- One Azure Storage account named [mystorage01].
- One container in mystorage01 named [mybackup01].
Match these to your resources and fire up Azure portal. All set? Let’s go!
1. Authorize your Azure SQL Managed Instance to write to the storage account
Shared access signature (SAS) tokens and why we won’t use them
Traditionally, backups from Azure SQL Managed Instance to Azure Storage are done via Shared Access Signature (SAS) tokens. A SAS token is a string of characters that grants its bearer a scope- and time-limited right to access Azure Storage. SAS tokens are easy to use, but they suffer from several drawbacks. Notably, whoever issued the SAS token has no control over the identity of the bearer. Anyone with a valid SAS can obtain access to the resource. This makes SAS tokens a potential security flaw, for example, if they leak to GitHub in a source or configuration file. Another downside to SAS tokens is that, once issued, they cannot be revoked until they expire. For these reasons SAS tokens are typically issued when needed with a very limited scope and duration; just enough to get the job done.
You’ll find plenty of examples on how to backup from Azure SQL Managed Instance to Azure Storage using SAS tokens; for example, Native database backup in Azure SQL Managed Instance. We won’t go down that road; instead…
Managed Identities all the way
Today, Azure SQL Managed Instance supports managed identities. A managed identity is akin to a “club membership”: any number of resources may be configured to identify with a given managed identity, and all resources are free to define the level of access they grant to its bearers.
Managed identities provide a flexible way of assigning and revoking access rights between Azure resources, and importantly, do not suffer from the drawbacks that plague SAS tokens.
There are two types of managed identities, and we can use either:
- System-assigned managed identityis associated with the resource itself and is unique to it. This identity is created together with Azure SQL Managed Resource (unless configured not to) and is deleted automatically together with its Azure SQL Managed Instance. System-assigned managed identity stands for “this one resource only”.
- User-assigned managed identities, by contrast, are created by Azure users and associated with resources as needed. One user-assigned managed identity can be associated with multiple resources; and each Azure service that accepts managed identities can be configured as to what access to grant to its bearers.
For the sake of simplicity, we will proceed using our Azure SQL Managed Instance’s system-assigned managed identity. Check that system-assigned managed identity is enabled on your Azure SQL Managed Instance’s Identity blade before we continue.
To assign contributor rights on the backup storage account to your Azure SQL Managed Instance:
- In Azure Portal, begin typing your storage account’s name and select it.
- In Access Control (IAM), Select “+ Add” and select “Role assignment”.
- In the list of roles, select “Storage Blob Data Contributor”. This will suffice.
- Under “Members”, set up “Assign access to” to “Managed identity”, then select “+ Select members”.
- Select your subscription. In the “Managed identity” box, under the System-assigned managed identity section, select Azure SQL Managed Instance.
- Choose your Azure SQL Managed Instance from the list and select Select.
- Select Review + assign twice.
Now, on the Azure SQL Managed Instance, do the following:
CREATE CREDENTIAL [https://mystorage01.blob.core.windows.net/mybackup01] WITH IDENTITY = 'Managed Identity';And that’s it! For our purposes we’re all set, as far as authorization goes. However, if you are looking for even more flexibility – say, multiple instances to multiple storage accounts – you can easily modify this tutorial to use user-assigned managed identities. You’ll find a wealth of information here:
- User-assigned managed identity in Azure AD for Azure SQL
- Manage user-assigned managed identities
We will use private link to create an endpoint in the same virtual network where Azure SQL Managed Instance is hosted. This endpoint will send all traffic directly to the Azure Storage container.
Create a hub subnet to hold the private endpoint
It is a good architectural practice to segregate resources in a network into subnets according to their purpose. In our case, we already have one subnet in the virtual network where Azure SQL Managed Instance is hosted. This subnet is typically named [ManagedInstance]. Azure SQL Managed Instance embraces this philosophy, so it is actually not possible to create a private endpoint in the subnet where one or more Azure SQL Managed Instances reside. Thus, let’s create a new subnet to hold the private endpoint for our storage account:
- In Azure Portal, begin typing the name of your Azure SQL Managed Instance and select it.
- Select the link shown under `Virtual network / subnet’.
- Select Subnets in the sidebar.
- Select + Subnet and name your subnet, e.g. hub-storage.
Create a private endpoint to the backup storage account
In Azure Portal, begin typing “private endpoints” and create a new endpoint with the following:
- Basics: select the resource group and instance details for the new private endpoint. Pick the region in which your Azure SQL Managed Instance resides.
- Resource: point to your backup storage account and select "blob" as the target sub-resource.
- Virtual Network: tell Azure to create this private endpoint in the "hub-storage" subnet we created earlier.
- DNS: this is a useful option that will automatically configure your virtual network to tunnel the traffic through the private endpoint. It does so by making the domain name of the storage account resolve to the private endpoint’s IP address instead of the account’s public endpoint. Other virtual networks as well as general Internet traffic won’t see this, so they’ll still have to go to the storage account’s public endpoint. Select this option and accept the defaults.
Diagram 2. We’ve added a private endpoint between our managed instance and the storage account. Our subnet’s blob traffic to mystorage01.blob.windows.net now traverses the private endpoint.
3.Tighten up the security
Okay, we got our connectivity and authorization in place, what more is there to do? Well, if you really want to tighten the bolts on your security plating (as well you should), then you should consider closing off all access on both the Azure SQL Managed Instance's and storage account's end.
Notice that the non-encrypted backups taken from Azure SQL Managed Instance can be restored on other Azure SQL Managed Instances. This alone is reason enough to prevent unauthorized access to the blob storage containing backups.
Secure your storage account against unauthorized access
Now that we have a public endpoint going, we can turn off the public endpoint on the storage account (storage account > Networking > Firewalls and virtual networks > Public network access: Disabled). All traffic of interest will be arriving through the private endpoint instead.
Secure your Azure SQL Managed Instance against data exfiltration
With our path to the backup location established 1-1 via private link, we should ensure that no other storage account can be targeted by a malicious user or an intruder. This will prevent any data exfiltration from taking place, accidental or otherwise. (It happens!)
- Deploy a service endpoint policy in the same region where yourAzure SQL Managed Instance is.
- In policy’s Policy definitions, add your backup storage account as the sole resource, and add an alias to /services/Azure/ManagedInstance below.
- Note that, strictly speaking, you can even use an empty resource group instead of the backup account. This will close off all traffic to Azure Storage except as established via private endpoint(s).
- Visit your Azure SQL Managed Instance’s subnet blade, enable service endpoints, and attach the service endpoint policy you’ve just created.
Diagram 3. Only the traffic from ManagedInstance successfully reach our storage account (via private endpoint). All other sources of traffic to the storage account will be ignored.
Note that a restrictive service endpoint policy will never prevent Azure SQL Managed Instances from their regular operation. This includes regular backups and the access to database files in the General Purpose service tier. More information on how service endpoint policies apply to Azure SQL Managed Instance can be found at Configure service endpoint policies for Azure SQL Managed Instance.
Set things up on the Azure SQL Managed Instance
Okay, we’re ready to try backing up our database(s):
BACKUP DATABASE [myDatabase] TO URL = 'https://mystorage01.blob.core.windows.net/mybackup01/backup.bak' WITH COPY_ONLY, COMPRESSION, BLOCKSIZE=65536, MAXTRANSFERSIZE=4194304Keep in mind that you cannot backup a database encrypted with service-managed Transparent Data Encryption (TDE). If your database is encrypted using customer-managed TDE, also known as Bring-Your-Own-Key (BYOK), then you may want to refer to the following article for more information: Transparent data encryption for SQL Managed Instance.
If you run into other issues or want to learn more about backups, take a look at: SQL Server Backup to URL for Microsoft Azure Blob Storage.
That’s it! We hope you’ve found this tutorial useful and informative. Did it work for you? Drop us a line below, we read everything
Tell us what you think and how we can make our articles better!
FAQs
How to take secure, on-demand backups on SQL Managed Instance? ›
In the Azure portal, select your managed instance and then select Backups. On the Available backups tab, select the database for which you want to see available backups. Select Manage. In the Manage backups pane, review the available backups.
How do I backup a managed SQL Server instance? ›In the Azure portal, select your managed instance and then select Backups. On the Available backups tab, select the database for which you want to see available backups. Select Manage. In the Manage backups pane, review the available backups.
What can you use to provide protection for Azure SQL managed instance? ›- Limit access using firewall rules.
- Use authentication mechanisms that require identity.
- Use authorization with role-based memberships and permissions.
- Enable security features.
Up to 280, unless the instance storage size or Azure Premium Disk storage allocation space limit has been reached. 32,767 files per database, unless the instance storage size limit has been reached. Maximum size of each data file is 8 TB. Use at least two data files for databases larger than 8 TB.
How to backup master database SQL Server? ›Expand Databases, and either select a user database or expand System Databases and select a system database. Right-click the database that you wish to backup, point to Tasks, and then select Back Up....
What is the backup frequency of SQL managed instance? ›Backup frequency
Azure SQL Managed Instance creates: Full backups every week. Differential backups every 12 to 24 hours. Transaction log backups every 10 minutes.
- On the vault item dashboard, under Protected Item, select Backup Item.
- From Backup Management Type, select Azure Virtual Machine. ...
- Select a VM and select Backup Now to create an on-demand backup. ...
- In the Retain Backup Till field, specify a date for the backup to be retained.
- Launch SQL Server Management Studio (SSMS) and connect to your SQL Server instance.
- Expand the Databases node in Object Explorer.
- Right-click the database, hover over Tasks, and select Back up....
- Under Destination, confirm that the path for your backup is correct.
In the Azure portal, navigate to your server and then select Backups. Select the Retention policies tab to modify your backup retention settings. On the Retention policies tab, select the database(s) on which you want to set or modify long-term backup retention policies. Unselected databases will not be affected.
What is the difference between SQL Server and managed instance? ›SQL Managed Instance provides support for instance-scoped features enabling easy migration of existing applications, as well as sharing resources among databases. Whereas, SQL Server on Azure VMs provide DBAs with an experience most similar to the on-premises environment they're familiar with.
What is the advantage of SQL managed instance in Azure? ›
SQL Managed Instance provides additional security isolation from other tenants on the Azure platform. Security isolation includes: Native virtual network implementation and connectivity to your on-premises environment using Azure ExpressRoute or VPN Gateway.
How do I enable TDE in SQL managed instance? ›To configure TDE through the Azure portal, you must be connected as the Azure Owner, Contributor, or SQL Security Manager. Enable and disable TDE on the database level. For Azure SQL Managed Instance use Transact-SQL (T-SQL) to turn TDE on and off on a database.
Which of the following is not supported by SQL managed instance? ›Procedures and Triggers are not supported in the Azure SQL Managed Instance.
Which feature is not supported by Azure SQL Managed instance? ›| Feature | Azure SQL Database | Azure SQL Managed Instance |
|---|---|---|
| Cross-database transactions | No | Yes, within the instance. See Linked server differences for cross-instance queries. |
| Database mail - DbMail | No | Yes |
| Database mirroring | No | No |
| Database snapshots | No | No |
Managed instance groups maintain high availability of your applications by proactively keeping your instances available. A MIG automatically repairs failed instances by recreating them. You might also want to repair instances when an application freezes, crashes, or runs out of memory.
Should I backup master model and Msdb? ›The system databases that you must always back up include msdb, master, and model. If any database uses replication on the server instance, there is a distribution system database that you must also back up.
What are the different types of backups in SQL Server? ›SQL Server Backup Types. Microsoft SQL Server supports five types of backup: full, differential, transaction log, tail log, and copy-only backup.
Can we take log backup of master database? ›Very much so, yes! In the event that the master database becomes corrupt or if the server crashes and the master database needs to be recovered, a backup will be the only way to do so.
How to improve backup performance in SQL Server? ›- Introduction to SQL Server Backup Performance. Backing up your SQL Server databases is an essential part of any database maintenance plan. ...
- Use Faster Storage. ...
- Compress the Backup. ...
- Use Multiple Backup Files. ...
- Schedule Backups During Off-Peak Hours. ...
- Use Partial Backups. ...
- Example Backup Script. ...
- Conclusion.
In SSMS, right-click on the database, go to tasks and select the option backup. Over here is the destination where you can provide multiple locations where you want to take backup. On the screen, I have provided the same directory but you can also select another directory as well. Well, that's it.
How to optimize SQL Server memory? ›
- In Object Explorer, right-click a server and select Properties.
- Select the Memory page of the Server Properties window. ...
- In Server memory options, enter desired numbers for Minimum server memory and Maximum server memory.
Use Azure Backup to protect data from deletion and ransomware by isolating backup data from original data and through accidental delete protection and multifactor authentication. Achieve low recovery-point objective (RPO) and recovery-time objective (RTO) targets for any mission-critical workload in your organisation.
What is the minimum RPO for Azure backup? ›What is the minimum RPO and RTO for VM backups in Azure Backup? RPO: The minimum RPO is 1 day or 24 hours when you're using Standard policy. If you use Enhanced policy, the minimum RPO is 4 hours.
What is the highest frequency that you can use to back up Azure VM? ›You can back up DPM up to twice a day. You can set the scheduling policy to daily, weekly, monthly, and yearly. You back up Azure VMs once a day.
How to take SQL Server database backup automatically daily? ›In Task Scheduler, right-click on Task Schedule Library and click on Create Basic task…. Enter the name for the new task (for example: SQLBackup) and click Next. Select Daily for the Task Trigger and click Next. Set the recurrence to one day and click Next.
How to take all jobs backup in SQL Server? ›- Open SQL Server Management Studio.
- Expand SQL Server Agent, and expand Jobs.
- Right-click the job you want to create a backup script for, and then select Script Job as.
- Select CREATE To or DROP To, then select New Query Editor Window, File, or Clipboard to select a destination for the script.
Open SQL Server Management Studio. Expand Security, and expand the list of Logins. Right-click the login you want to create a backup script for, and then select Script Login as. Select CREATE To, and then select one of New Query Editor Window, File, or Clipboard to select a destination for the script.
How do I auto protect Azure SQL backup? ›To enable auto-protection: In Items to backup, select the instance for which you want to enable auto-protection. Select the drop-down list under AUTOPROTECT, choose ON, and then select OK.
How do I restore SQL backup to Azure SQL managed instance? ›To recover a database by using the Azure portal, open the managed instance's overview page and select Backups. Choose to show Deleted backups, and then select Restore next to the deleted backup you want to recover to open the Create Azure SQL Managed Database page.
Where are Azure SQL Server backups stored? ›By default, Azure SQL Database stores backups in geo-redundant storage blobs that are replicated to a paired region. Geo-redundancy helps protect against outages that affect backup storage in the primary region.
What are the two types of authentication supported by Azure SQL Database and SQL Managed instance? ›
Authentication. Authentication is the process of proving the user is who they claim to be. Azure SQL Database and SQL Managed Instance support SQL authentication and Azure AD authentication. SQL Managed instance additionally supports Windows Authentication for Azure AD principals.
Is Azure SQL Managed instance PaaS or SaaS? ›As per the document from Microsoft at https://learn.microsoft.com/en-us/learn/modules/azure-database-fundamentals/azure-sql-managed-instance, the Azure SQL database and Azure SQL Managed Instance are PaaS.
What's the difference between Azure SQL Database and Azure SQL managed instance? ›Azure SQL managed instance provides integration of native virtual network while azure SQL database enables the access of restricted virtual network by using endpoints of Vnet.
What is general purpose in Azure SQL managed instance? ›The General Purpose service tier is the default service tier in Azure SQL Managed Instance designed for most of generic workloads. If you need a fully managed database engine with a default SLA and storage latency between 5 and 10 ms, the General Purpose tier is the option for you.
Which Azure instance is best for SQL Server? ›The Ebdsv5-series VMs offer the best price-performance for SQL Server workloads running on Azure virtual machines and we strongly recommend them for most of your production SQL Server workloads.
What is the difference between Azure SQL Managed instance and Cosmos DB? ›Azure SQL is based on SQL Server engine, you can easily migrate applications and continue to use the tools, languages, and resources that you're familiar with. Azure Cosmos DB is used for web, mobile, gaming, and IoT application that needs to handle massive amounts of data, reads, and writes at a global scale.
Are Azure managed disks encrypted? ›Most Azure managed disks are encrypted with Azure Storage encryption, which uses server-side encryption (SSE) to protect your data and to help you meet your organizational security and compliance commitments.
What type of encryption is TDE? ›Transparent data encryption (TDE) encrypts SQL Server, Azure SQL Database, and Azure Synapse Analytics data files. This encryption is known as encrypting data at rest. To help secure a user database, you can take precautions like: Designing a secure system.
How to check TDE progress in SQL Server? ›How to monitor TDE Progress: SQL Server keeps track of the encryption progress and we can pull that information by querying sys. dm_database_encryption_keys. Particularly 'Percent_Complete' and 'encryption_state' are the two columns which are required to understand the progress of TDE.
How do I backup a managed SQL instance? ›In the Azure portal, select your managed instance and then select Backups. On the Available backups tab, select the database for which you want to see available backups. Select Manage. In the Manage backups pane, review the available backups.
Does SQL managed instance support Windows authentication? ›
There are two phases to set up Windows Authentication for Azure SQL Managed Instance using Azure Active Directory (Azure AD) and Kerberos. One-time infrastructure setup. Synchronize Active Directory (AD) and Azure AD, if this hasn't already been done. Enable the modern interactive authentication flow, when available.
How do I configure public endpoint in Azure SQL Managed Instance? ›Open the resource group with the managed instance, and select the SQL managed instance that you want to configure public endpoint on. On the Security settings, select the Virtual network tab. In the Virtual network configuration page, select Enable and then the Save icon to update the configuration.
What is the difference between Hyperscale and managed instance? ›Hyperscale does not support automatic failover while Azure Managed instance does support it. You do not have point-in-time database restore with Hyperscale but you do have that with Azure Managed instances. Memory-optimized table variables have limited support on Hyperscale but have full support on Managed Instance.
What data security options are available in Azure SQL DB? ›- Limit access using firewall rules.
- Use authentication mechanisms that require identity.
- Use authorization with role-based memberships and permissions.
- Enable security features.
SQL Managed Instance has two default limits: limit on the number of subnets you can use and a limit on the number of vCores you can provision. Limits vary across the subscription types and regions. For the list of regional resource limitations by subscription type, see table from Regional resource limitation.
What is the maximum databases per Azure SQL Managed instance? ›The limit of 100 databases per SQL Managed Instance is a hard limit that cannot be changed.
What is the difference between general purpose and business critical in SQL managed instance? ›General Purpose is a budget-friendly tier designed for most workloads with common performance and availability features. Business Critical tier is designed for performance-sensitive workloads with higher availability features.
How do I copy a database from SQL managed instance? ›Copy or move database
To do so: Go to your managed instance in the Azure portal. Under Data management, select Databases. Select one or more databases, and then select either the Copy or Move option at the top of the pane.
SQL Server Managed Backup to Microsoft Azure schedules, performs, and maintains the backups. SQL Server Managed Backup to Microsoft Azure can be configured at the database level or at the SQL Server instance level. When configuring at the instance level, any new databases are also backed up automatically.
How do I backup and restore a master database in SQL Server? ›- Start the server instance in single-user mode. ...
- Connect to SQL Server using SQLCMD from another Command Prompt window. ...
- To restore a full database backup of master, use the following RESTORE DATABASETransact-SQL statement:
Where is SQL Server Management Studio backup file? ›
The default backup directory is C:\Program Files\Microsoft SQL Server\MSSQL. n\MSSQL\Backup, where n is the number of the server instance.
What is the best way to copy database from one SQL Server to another? ›On either the source or destination SQL Server instance, launch the Copy Database Wizard in SQL Server Management Studio from Object Explorer and expand Databases. Then right-click a database, point to Tasks, and then select Copy Database.
How do I export data from SQL Server Management? ›- In SQL Server Management Studio, connect to an instance of the SQL Server Database Engine.
- Expand Databases.
- Right-click a database.
- Point to Tasks.
- Click one of the following options. Import Data. Export Data.
- Using the Copy Database Wizard. You can use the Copy Database Wizard to copy or move databases between servers or to upgrade a SQL Server database to a later version. ...
- Restoring a database backup. ...
- Using the Generate Scripts Wizard to publish databases.
A backup of data in a complete database (a database backup), a partial database (a partial backup), or a set of data files or filegroups (a file backup).
What is the best backup strategy in SQL Server? ›A transaction log (T-log) backup is the most granular backup type in SQL Server because it backs up the transaction log which only contains the modifications made to the SQL Server database since the last transaction log backup. It's effectively an incremental backup.
Should I backup master SQL Server? ›Back up master as often as necessary to protect the data sufficiently for your business needs. We recommend a regular backup schedule, which you can supplement with an additional backup after a substantial update. The template for all databases that are created on the instance of SQL Server.
How to rebuild master database in SQL Server without backup? ›- Launch SQL Server Configuration Manager.
- Choose the SQL Server Services option to check the currently running services.
- Select a running service and right-click on it. ...
- Repeat the process for all running services that you want to stop.
The msdb database file (msdb. mdf) and msdb log files (msdb. ldf) are located in the Program Files\Microsoft SQL Server\Mssql\Data directory. Due to the amount of configuration information stored in the msdb database, the database should be routinely backed up.
How do I check SQL Management Studio backup status? ›Open SSMS, right click on a database then select Tasks > Back Up. A screen similar to the below image will open. After you select all of the backup options and click OK, you can monitor the progress on the lower left side of the GUI as shown in the below image. This will give you an idea of the status of the backup.
How to verify backup file in SQL Server? ›
To verify a database snapshot before a revert operation, you can run DBCC CHECKDB. With snapshot backups, RESTORE VERIFYONLY confirms the existence of the snapshots in the locations specified in the backup file. Snapshot backups are a new feature in SQL Server 2016 (13. x).
How to change backup location in SQL Server Management Studio? ›Right-click the SQL Server instance and select Properties. Select the Database Settings section. At the bottom of this window, you should see the Backup default location. Browse to the new Backup location and then close.