New DBA in the cloud – Managing Azure SQL Database after migration

Applies to: Azure SQL Database

Migrating from a self-managed environment to a PaaS like Azure SQL Database can be complex. This article highlights the key capabilities of Azure SQL Database for single and pooled databases, helping you keep applications available, performant, secure, and resilient.

Core characteristics of Azure SQL Database include:

• Database monitoring with the Azure portal
• Business continuity and disaster recovery (BCDR)
• Security and compliance
• Intelligent database monitoring and maintenance
• Data movement

Monitor databases using the Azure portal

For Azure Monitor metrics and alerts, including recommended alert rules, see Monitor Azure SQL Database with metrics and alerts. For more information about service tiers, see DTU-based purchasing model overview and vCore-based purchasing model.

You can configure alerts on the performance metrics. Select the Add alert button in the Metric window. Follow the wizard to configure your alert. You can alert if the metrics exceed a certain threshold or if the metric falls below a certain threshold.

For example, if you expect the workload on your database to grow, you can choose to configure an email alert whenever your database reaches 80% on any of the performance metrics. You can use this as an early warning to figure out when you might have to switch to the next highest compute size.

The performance metrics can also help you determine if you're able to downgrade to a lower compute size. However, be aware of workloads that spike or fluctuate before making the decision to move to a lower compute size.

Business continuity and disaster recovery (BCDR)

Business continuity and disaster recovery abilities enable you to continue your business if a disaster occurs. The disaster could be a database level event (for example, someone mistakenly drops a crucial table) or a data-center level event (regional catastrophe, for example a tsunami).

How do I create and manage backups on SQL Database?

Azure SQL Database automatically backs up databases for you. The platform takes a full backup every week, differential backup every few hours and a log backup every 5 minutes to ensure the disaster recovery is efficient, and data loss is minimal. The first full backup happens as soon as you create a database. These backups are available to you for a certain period called the retention period, which varies according to the service tier you choose. You can restore to any point in time within this retention period using Point in Time Recovery (PITR).

In addition, the Long-term retention backups feature allows you to keep your backup files for up to 10 years, and restore data from these backups at any point within that period. Database backups are kept in geo-replicated storage to provide resilience from regional catastrophe. You can also restore these backups in any Azure region at any point of time within the retention period. For more information, see Business continuity in Azure SQL Database.

How do I ensure business continuity in the event of a datacenter-level disaster or regional catastrophe?

Your database backups are stored in geo-replicated storage to ensure that, during a regional disaster, you can restore the backup to another Azure region. This is called geo-restore. For more information and timing of geo-restores, see Geo-restore for Azure SQL Database.

For mission-critical databases, Azure SQL Database offers active geo-replication, which creates a geo-replicated secondary copy of your original database in another region. For example, if your database is initially hosted in Azure West US region and you want regional disaster resilience, create an active geo replica of the database in West US to East US. When calamity strikes on West US, you can fail over to the East US region.

In addition to active geo-replication, failover groups help you to manage replication and failover of a group of databases. You can create a failover group that contains multiple databases in the same or different regions. You can then initiate a failover of all databases in the failover group to the secondary region. For more information, see Failover groups overview & best practices (Azure SQL Database).

To achieve resiliency for datacenter or availability zone failures, ensure zone redundancy is enabled for the database or elastic pool.

Actively monitor your application for a disaster and initiate a failover to the secondary. You can create up to four such active geo-replicas in different Azure regions. It gets even better. You can also access these secondary active geo-replicas for read-only access. This helps to reduce latency for a geo-distributed application scenario.

What does disaster recovery look like with SQL Database?

Configuration and management of disaster recovery can be done with just a few steps in Azure SQL Database when you use active geo-replication or failover groups. You still have to monitor the application and its database for any regional disaster and fail over to the secondary region to restore business continuity.

For more information, see Azure SQL Database Disaster Recovery 101.

Security and compliance

Security within SQL Database is available at the database level and at the platform level. You can control and provide optimal security for your application as follows:

• Identity & authentication (SQL authentication and authentication with Microsoft Entra ID).
• Monitoring activity (Auditing and threat detection).
• Protecting actual data (Transparent data encryption [TDE] and Always Encrypted).
• Controlling Access to sensitive and privileged data (Row-level security and Dynamic data masking).

Microsoft Defender for Cloud offers centralized security management across workloads running in Azure, on-premises, and in other clouds. You can view whether essential SQL Database protection such as Auditing and Transparent data encryption [TDE] are configured on all resources, and create policies based on your own requirements.

What user authentication methods are offered in SQL Database?

There are two authentication methods offered in SQL Database:

• Microsoft Entra authentication
• SQL authentication

Windows authentication isn't supported. Microsoft Entra ID is a centralized identity and access management service. Microsoft Entra ID provides single sign-on (SSO) access to the personnel in your organization. What this means is that the credentials are shared across Azure services for easier authentication.

Microsoft Entra ID supports multifactor authentication, and can easily be integrated with Windows Server Active Directory. This also allows SQL Database and Azure Synapse Analytics to offer multifactor authentication and guest user accounts within a Microsoft Entra domain. If you already use Active Directory on-premises, you can federate it with Microsoft Entra ID to extend your directory to Azure.

SQL authentication supports only username and password to authenticate users to any database on a given server.

How do I limit or control connectivity access to my database?

There are multiple techniques at your disposal that you could use to attain optimal connectivity organization for your application.

• Firewall Rules
• Virtual network service endpoints
• Reserved IPs

Firewall

By default, all connections to databases inside the server are disallowed, except (optionally) connections coming in from other Azure Services. With a firewall rule, you can open access to your server only to entities (for example, a developer machine) that you approve of, by allowing that computer's IP address through the firewall. It also allows you to specify a range of IPs that you would want to allow access to the server. For example, developer machine IP addresses in your organization can be added at once by specifying a range in the Firewall settings page.

You can create firewall rules at the server level or at the database level. Server level IP firewall rules can either be created using the Azure portal or with SSMS. For more information about how to set a server-level and database-level firewall rule, see Create IP firewall rules in SQL Database.

Service endpoints

By default, your database is configured to Allow Azure services and resources to access this server, which means any Virtual Machine in Azure might attempt to connect to your database. These attempts still have to be authenticated. If you don't want your database to be accessible by any Azure IPs, you can disable Allow Azure services and resources to access this server. Additionally, you can configure Virtual network service endpoints.

Service endpoints allow you to expose your critical Azure resources only to your own private virtual network in Azure. This option eliminates public access to your resources. The traffic between your virtual network to Azure stays on the Azure backbone network. Without service endpoints, you get forced-tunneling packet routing. Your virtual network forces the internet traffic to your organization and the Azure Service traffic to go over the same route. With service endpoints, you can optimize this since the packets flow straight from your virtual network to the service on Azure backbone network.

Reserved IPs

Another option is to provision reserved IPs for your VMs, and add those specific VM IP addresses in the server firewall settings. By assigning reserved IPs, you save the trouble of having to update the firewall rules with changing IP addresses.

What port do I connect to SQL Database on?

SQL Database communicates over port 1433. To connect from within a corporate network, you have to add an outbound rule in the firewall settings of your organization. As a guideline, avoid exposing port 1433 outside the Azure boundary.

How can I monitor and regulate activity on my server and database in SQL Database?

SQL Database Auditing

Azure SQL Database Auditing records database events and writes them into an audit log file in your Azure Storage Account. Auditing is especially useful if you intend to gain insight into potential security and policy violations, maintain regulatory compliance, etc. It allows you to define and configure certain categories of events that you think need auditing and based on that you can get preconfigured reports and a dashboard to get an overview of events occurring on your database.

You can apply these auditing policies either at the database level or at the server level. For more information, Enable SQL Database Auditing.

Threat detection

With threat detection, you get the ability to act upon security or policy violations discovered by auditing. You don't need to be a security expert to address potential threats or violations in your system. Threat detection also has some built-in capabilities like SQL injection detection, which is a quite common way of attacking a database application. Threat detection runs multiple sets of algorithms that detect potential vulnerabilities and SQL injection attacks, and anomalous database access patterns (such as access from an unusual location or by an unfamiliar principal).

Security officers or other designated administrators receive an email notification if a threat is detected on the database. Each notification provides details of the suspicious activity and recommendations on how to further investigate and mitigate the threat. To learn how to turn on Threat detection, see Enable threat detection.

How do I protect my data in general on SQL Database?

Encryption provides a strong mechanism to protect and secure your sensitive data from intruders. Your encrypted data is of no use to the intruder without the decryption key. Thus, it adds an extra layer of protection on top of the existing layers of security built in SQL Database. There are two aspects to protecting your data in SQL Database:

• Your data at-rest in the data and log files
• Your data in-flight

In SQL Database, by default, your data at rest in the data and log files on the storage subsystem is completely and always encrypted via Transparent data encryption [TDE]. Your backups are also encrypted. With TDE, there are no changes required on your application side that is accessing this data. The encryption and decryption happen transparently; hence the name.

For protecting your sensitive data in-flight and at rest, SQL Database provides a feature called Always Encrypted. Always Encrypted is a form of client-side encryption that encrypts sensitive columns in your database (so they're in ciphertext to database administrators and unauthorized users). The server receives the encrypted data to begin with.

The key for Always Encrypted is also stored on the client side, so only authorized clients can decrypt the sensitive columns. The server and data administrators can't see the sensitive data since the encryption keys are stored on the client. Always Encrypted encrypts sensitive columns in the table end to end, from unauthorized clients to the physical disk.

Always Encrypted supports equality comparisons, so DBAs can continue to query encrypted columns as part of their SQL commands. Always Encrypted can be used with a variety of key store options, such as Azure Key Vault, Windows certificate store, and local hardware security modules.

How can I limit access to sensitive data in my database?

Every application has sensitive data in the database that needs to be protected from being visible to everyone. Certain personnel within the organization need to view this data, however others shouldn't be able to view this data. In such cases, your sensitive data either needs to be masked, or not be exposed at all. SQL Database offers two such approaches to prevent unauthorized users from being able to view sensitive data:

• Dynamic data masking is a data masking feature that enables you to limit sensitive data exposure by masking it to non-privileged users on the application layer. You define a masking rule that can create a masking pattern (for example, to only show last four digits of a national ID SSN: XXX-XX-0000 and mask most of it with the X character) and identify which users are to be excluded from the masking rule. The masking happens on-the-fly and there are various masking functions available for various data categories. Dynamic data masking allows you to automatically detect sensitive data in your database and apply masking to it.

• Row-level security enables you to control access at the row level. Meaning, certain rows in a database table based on the user executing the query (group membership or execution context) are hidden. The access restriction is done on the database tier instead of in an application tier, to simplify your app logic. You start by creating a filter predicate, filtering out rows that aren't exposed and the security policy next defining who has access to these rows. Finally, the end user runs their query and, depending on the user's privilege, they either view those restricted rows or are unable to see them at all.

How do I manage encryption keys in the cloud?

There are key management options for both Always Encrypted (client-side encryption) and Transparent data encryption (encryption at rest). It's recommended that you regularly rotate encryption keys. The rotation frequency should align with both your internal organization regulations and compliance requirements.

Transparent data encryption (TDE)

There's a two-key hierarchy in TDE – the data in each user database is encrypted by a symmetric AES-256 database-unique database encryption key (DEK), which in turn is encrypted by a server-unique asymmetric RSA 2048 master key. The master key can be managed either:

• Automatically by Azure SQL Database
• Or by you using Azure Key Vault as the key store

By default, the master key for TDE is managed by Azure SQL Database. If your organization would like control over the master key, you can use Azure Key Vault as the key store. By using Azure Key Vault, your organization assumes control over key provisioning, rotation, and permission controls. Rotation or switching the type of a TDE master key is fast, as it only re-encrypts the DEK. For organizations with separation of roles between security and data management, a security admin could provision the key material for the TDE master key in Azure Key Vault and provide an Azure Key Vault key identifier to the database administrator to use for encryption at rest on a server. The Key Vault is designed such that Microsoft doesn't see or extract any encryption keys. You also get a centralized management of keys for your organization.

Always Encrypted

There's also a two-key hierarchy in Always Encrypted - a column of sensitive data is encrypted by an AES 256-column encryption key (CEK), which in turn is encrypted by a column master key (CMK). The client drivers provided for Always Encrypted have no limitations on the length of CMKs. The encrypted value of the CEK is stored on the database, and the CMK is stored in a trusted key store, such as Windows Certificate Store, Azure Key Vault, or a hardware security module.

• Both the CEK and CMK can be rotated.

• CEK rotation is a size of data operation and can be time-intensive depending on the size of the tables containing the encrypted columns. Hence, it's prudent to plan CEK rotations accordingly.

• CMK rotation, however, doesn't interfere with database performance, and can be done with separated roles.

The following diagram shows the key store options for the column master keys in Always Encrypted:

How can I optimize and secure the traffic between my organization and SQL Database?

The network traffic between your organization and SQL Database is generally routed over the public network. However, you can optimize this path and make it more secure with Azure ExpressRoute. ExpressRoute extends your corporate network into the Azure platform over a private connection. By doing so, you don't go over the public Internet. You also get higher security, reliability, and routing optimization that translates to lower network latencies and faster speeds than you would normally experience going over the public internet. If you're planning on transferring a significant chunk of data between your organization and Azure, using ExpressRoute can yield cost benefits. You can choose from three different connectivity models for the connection from your organization to Azure:

• Cloud Exchange Colocation
• Any-to-any
• Point-to-Point

ExpressRoute also allows you to burst up to 2x the bandwidth limit you purchase for no extra charge. It's also possible to configure cross region connectivity using ExpressRoute. For a list of ExpressRoute connectivity providers, see ExpressRoute Partners and Peering Locations. The following articles describe Express Route in more detail:

• Introduction on Express Route
• Prerequisites
• Workflows

Is SQL Database compliant with any regulatory requirements, and how does that help with my own organization's compliance?

SQL Database is compliant with a range of regulatory compliancies. To view the latest set of compliancies that have been met by SQL Database, visit the Microsoft Trust Center and review the compliancies that are important to your organization to see if SQL Database is included under the compliant Azure services. Although SQL Database is certified as a compliant service, it aids in the compliance of your organization's service but doesn't automatically guarantee it.

Intelligent database monitoring and maintenance after migration

Once you migrate your database to SQL Database, you should monitor your database (for example, check how the resource utilization is like or DBCC checks) and perform regular maintenance (for example, rebuild or reorganize indexes, statistics, etc.). SQL Database uses the historical trends and recorded metrics and statistics to proactively help you monitor and maintain your database, so that your application runs optimally always. In some cases, Azure SQL Database can automatically perform maintenance tasks depending on your configuration setup. There are three facets to monitoring your database in SQL Database:

• Performance monitoring and optimization
• Security optimization
• Cost optimization

Performance monitoring and optimization

With Query Performance Insights, you can get tailored recommendations for your database workload so that your applications can keep running at an optimal level. You can also set it up so that these recommendations get applied automatically and you don't have to bother performing maintenance tasks. With SQL Database Advisor, you can automatically implement index recommendations based on your workload. This is called Auto-Tuning. The recommendations evolve as your application workload changes to provide you with the most relevant suggestions. You also get the option to manually review these recommendations and apply them at your discretion.

Security optimization

SQL Database provides actionable security recommendations to help you secure your data and threat detection for identifying and investigating suspicious database activities that can pose a potential thread to the database. Vulnerability assessment is a database scanning and reporting service that allows you to monitor the security state of your databases at scale and identify security risks and drift from a security baseline defined by you. After every scan, a customized list of actionable steps and remediation scripts is provided, and an assessment report that can be used to help meet compliance requirements.

With Microsoft Defender for Cloud, you identify the security recommendations across the board and quickly apply them.

Cost optimization

Azure SQL platform analyzes the utilization history across the databases in a server to evaluate and recommend cost-optimization options for you. This analysis usually takes a few weeks of activity to analyze and build up actionable recommendations.

You might receive banner notifications in your Azure SQL server of cost recommendations. For more information, see Elastic pools help you manage and scale multiple databases in Azure SQL Database, and Plan and manage costs for Azure SQL Database.

How do I monitor the performance and resource utilization in SQL Database?

You can monitor performance and resource utilization in SQL Database using the following methods:

Database watcher

Database watcher collects in-depth workload monitoring data to give you a detailed view of database performance, configuration, and health. Dashboards in the Azure portal provide a single-pane-of-glass view of your Azure SQL estate and a detailed view of each monitored resource. Data is collected into a central data store in your Azure subscription. You can query, analyze, export, visualize collected data, and integrate it with downstream systems.

For more information about database watcher, see the following articles:

• Monitor Azure SQL workloads with database watcher (preview)
• Quickstart: Create a watcher to monitor Azure SQL (preview)
• Create and configure a watcher (preview)
• Database watcher data collection and datasets (preview)
• Analyze database watcher monitoring data (preview)
• Database watcher FAQ

Azure portal

The Azure portal shows a database's utilization by selecting the database and selecting the chart in the Overview pane. You can modify the chart to show multiple metrics, including CPU percentage, DTU percentage, Data IO percentage, Sessions percentage, and Database size percentage.

From this chart, you can also configure alerts by resource. These alerts allow you to respond to resource conditions with an email, write to an HTTPS/HTTP endpoint or perform an action. For more information, see Create alerts for Azure SQL Database and Azure Synapse Analytics using the Azure portal.

Dynamic management views

You can query the sys.dm_db_resource_stats dynamic management view to return resource consumption statistics history from the last hour and the sys.resource_stats system catalog view to return history for the last 14 days.

Query performance insight

Query performance insight allows you to see a history of the top resource-consuming queries and long-running queries for a specific database. You can quickly identify TOP queries by resource utilization, duration, and frequency of execution. You can track queries and detect regression. This feature requires Query Store to be enabled and active for the database.

I notice performance issues: How does my SQL Database troubleshooting methodology differ from SQL Server?

A major portion of the troubleshooting techniques you would use for diagnosing query and database performance issues remain the same: the same database engine powers the cloud. Azure SQL Database can help you troubleshoot and diagnose performance issues even more easily. It can also perform some of these corrective actions on your behalf and in some cases, proactively fix them automatically.

Your approach toward troubleshooting performance issues can significantly benefit by using intelligent features such as Query Performance Insight (QPI) and Database Advisor in conjunction and so the difference in methodology differs in that respect – you no longer need to do the manual work of grinding out the essential details that might help you troubleshoot the issue at hand. The platform does the hard work for you. One example of that is QPI. With QPI, you can drill all the way down to the query level and look at the historical trends and figure out when exactly the query regressed. The Database Advisor gives you recommendations on things that might help you improve your overall performance in general, such as missing indexes, dropping indexes, parameterizing your queries, etc.

With performance troubleshooting, it's important to identify whether it's just the application or the database backing it, that's affecting your application performance. Often the performance problem lies in the application layer. It could be the architecture or the data access pattern. For example, consider you have a chatty application that's sensitive to network latency. In this case, your application suffers because there would be many short requests going back and forth ("chatty") between the application and the server and on a congested network, and these roundtrips add up fast. To improve the performance in this case, you can use Batch Queries, which help to reduce roundtrip latency and improve your application's performance.

Additionally, if you notice a degradation in the overall performance of your database, you can monitor the sys.dm_db_resource_stats and sys.resource_stats dynamic management views in order to understand CPU, IO, and memory consumption. Your performance might be affected if your database is starved of resources. You might need to change the compute size and/or service tier based on the growing and shrinking workload demands.

For a comprehensive set of recommendations for tuning performance issues, see Tune your database.

How do I ensure I am using the appropriate service tier and compute size?

SQL Database offers two different purchasing models: the older DTU model and the more adaptable vCore purchasing model. For more information, see Compare vCore and DTU-based purchasing models of Azure SQL Database.

You can monitor your query and database resource consumption in either purchasing model. For more information, see Monitor and performance tuning. Should you find that your queries/databases are consistently running hot, you can consider scaling up to a higher compute size. Similarly, if you don't seem to use the resources as much during peak hours, consider scaling down from the current compute size. You could consider using Azure Automation to scale your SQL databases on a schedule.

If you have a SaaS app pattern or a database consolidation scenario, consider using an Elastic pool for cost optimization. Elastic pool is a great way to achieve database consolidation and cost-optimization. For more information about managing multiple databases using elastic pool, see Manage pools and databases.

How often do I need to run database integrity checks for my database?

SQL Database can handle certain classes of data corruption automatically and without any data loss. These built-in techniques are used by the service when the need arises. Your database backups across the service are regularly tested by restoring them and running DBCC CHECKDB on them. If there are issues, SQL Database proactively addresses them.

Automatic page repair is used for fixing pages that are corrupt or have data integrity issues. The database pages are always verified with the default CHECKSUM setting that verifies the integrity of the page. SQL Database proactively monitors and reviews the data integrity of your database and addresses issues as they arise. You can optionally run your own integrity checks as needed. For more information, see Data Integrity in SQL Database.

Data movement after migration

How do I export and import data as BACPAC files from SQL Database using the Azure portal?

• Export: You can export your database in Azure SQL Database as a BACPAC file from the Azure portal:

  

• Import: You can also import data as a BACPAC file into your database in Azure SQL Database using the Azure portal:

  

How do I synchronize data between SQL Database and SQL Server?

You have several ways to achieve this:

• Data Sync: This feature helps you synchronize data bi-directionally between multiple SQL Server databases and SQL Database. To sync with SQL Server databases, you need to install and configure sync agent on a local computer or a virtual machine and open the outbound TCP port 1433.

• Transaction Replication: With transaction replication you can synchronize your data from a SQL Server database to Azure SQL Database with the SQL Server instance being the publisher and the Azure SQL Database being the subscriber. For now, only this setup is supported. For more information on how to migrate your data from a SQL Server database to Azure SQL with minimal downtime, see Use Transaction Replication

Related content

• Monitor and performance tuning in Azure SQL Database and Azure SQL Managed Instance