Intro

Azure SQL Database is a fully managed platform as a service (PaaS) database engine that handles most of the database management functions such as upgrading, patching, backups, and monitoring without user involvement. Azure SQL Database is always running on the latest stable version of the SQL Server database engine and patched OS with 99.99% availability

Documentation

• Azure SQL documentation

• Pricing info

• Features comparison: Azure SQL Database and Azure SQL Managed Instance

• A good explanation of Azure SQL Database, Azure Managed Instance and SQL Server On Azure VM: What is Azure SQL?

• How to configure the portal Query Editor: Quickstart: Use the Azure portal's query editor (preview) to query an Azure SQL Database

Tips and Tidbits

• Azure SQL Database provides the following deployment options for a database:

  • Single database

•  represents a fully managed, isolated database. You might use this option if you have modern cloud applications and microservices that need a single reliable data source. A single database is similar to a contained database in the SQL Server database engine.

• An Azure SQL server can have up to 5000 SQL databases associated to it.

• Elastic pool is a collection of single databases with a shared set of resources, such as CPU or memory.

  • Single databases can be moved into and out of an elastic pool.

  • You can configure resources for the pool based either on

    • the DTU-based purchasing model

      • This model is based on a bundled measure of compute, storage, and I/O resources. Compute sizes are expressed in DTUs for single databases and in elastic database transaction units (eDTUs) for elastic pools.

    • OR

    • the vCore-based purchasing mode

      • This model allows you to independently choose compute and storage resources.

      • The vCore-based purchasing model also allows you to use Azure Hybrid Benefit for SQL Server to save costs.

      • This is code if you have volume license with mobility.

      • vCore-based purchasing model is available for both Azure SQL Database and Azure SQL Managed Instance.

  • The databases in an elastic pool are on a single server and share a set number of resources at a set price

    • You cannot add databases from different servers into the same pool

  • Elastic pools help you manage and scale multiple databases in Azure SQL Database

• You can create a single database in the provisioned or serverless compute tier.

  • A provisioned database is pre-allocated a fixed amount of compute resources, including CPU and memory, and uses one of two purchasing models. 

• Azure SQL Database Managed Instance, which is a platform-as-a-service (PaaS) SQL database deployment.

  • It is near 100% compatible with on-premises SQL Server editions 

  • Bulk insert operations are limited to importing from Azure Blob storage only.

  • Operations that use the BULK INSERT or OPENROWSET commands must include a DATASOURCE parameter that identifies the data source.

  • A managed instance cannot access file shares or Windows folders.

• A managed SQL server instance or Azure SQL database cannot run xp_cmdshell when the stored procedure is invoked.

  • If you need this functionality, then you need to run SQL server in a VM

SQL Migration

• Azure Database Migration Guides

Open

Migration toolsl

We recommend the following migration tools:

The following table lists alternative migration tools:

• Overview of Data Migration Assistant

• The Data Migration Assistant (DMA) migrates SQL sources to SQL destinations.

• DMA allows you to move your schema, data, and uncontained objects from your source server to your target server

• DMA recommends performance and reliability improvements for your target environment and allows you to move your schema, data, and uncontained objects from your source server to your target server

• Migrate an on-premises SQL Server instance to a modern SQL Server instance hosted on-premises or on an Azure virtual machine (VM) that is accessible from your on-premises network

• Tutorial: Migrate SQL Server to Azure SQL Database using DMS

• For large migrations (in terms of number and size of databases), we recommend that you use the Azure Database Migration Service, which can migrate databases at scale.

SQL Server database migration to Azure SQL Database

• SQL Server database migration to Azure SQL Database

• There are two primary methods for migrating a SQL Server 2005 or later database to Azure SQL Database.

  • The first method is simpler but requires some, possibly substantial, downtime during the migration.

  • The second method is more complex, but substantially eliminates downtime during the migration.

  • In both cases, you need to ensure that the source database is compatible with Azure SQL Database using the Data Migration Assistant (DMA)

    • SQL Database is approaching feature parity with SQL Server, other than issues related to server-level and cross-database operations.

    • Databases and applications that rely on partially supported or unsupported functions need some re-engineering to fix these incompatibilities before the SQL Server database can be migrated

Method 1: Migration with downtime during the migration

Open

1. Assess the database for compatibility by using the latest version of the Data Migration Assistant (DMA).

2. Prepare any necessary fixes as Transact-SQL scripts.

3. Make a transactionally consistent copy of the source database being migrated or halt new transactions from occurring in the source database while migration is occurring. Methods to accomplish this latter option include disabling client connectivity or creating a database snapshot. After migration, you may be able to use transactional replication to update the migrated databases with changes that occur after the cutoff point for the migration. See Migrate using Transactional Migration.

4. Deploy the Transact-SQL scripts to apply the fixes to the database copy.

5. Migrate the database copy to a new database in Azure SQL Database by using the Data Migration Assistant.

Method 2: Use Transactional Replication

When you can't afford to remove your SQL Server database from production while the migration is occurring, you can use SQL Server transactional replication as your migration solution. To use this method, the source database must meet the requirements for transactional replication and be compatible for Azure SQL Database. 

To use this solution, you configure your database in Azure SQL Database as a subscriber to the SQL Server instance that you wish to migrate. The transactional replication distributor synchronizes data from the database to be synchronized (the publisher) while new transactions continue occur.

Migration overview: SQL Server to Azure SQL Managed Instance

• Migration overview: SQL Server to Azure SQL Managed Instance

• Azure SQL Managed Instance is a recommended target option for SQL Server workloads that require a fully managed service without having to manage virtual machines or their operating systems.

• SQL Managed Instance enables you to move your on-premises applications to Azure with minimal application or database changes. It offers complete isolation of your instances with native virtual network support.

• One of the key benefits of migrating your SQL Server databases to SQL Managed Instance is that you can choose to migrate the entire instance or just a subset of individual databases. 

• This tutorial has you download SQL Server 2016 and restore a DB from backup. It then uses Database Migration Service to move it to Azure:

  • Tutorial: Migrate SQL Server to an Azure SQL Managed Instance online using DMS

Service Tiers

• Service tiers in the DTU-based purchase model

• Resource limits for single databases using the DTU purchasing model - Azure SQL Database

• The premium tier P11 and P15 go up to 4TB of storage.

  • For more than 4TB use the Hyperscale Tier which supports for up to 100 TB of database size.

    • Hyperscale Tier supports one read-write replica and 4 read-only replicas

• Hyperscale service tier

  • The Hyperscale service tier in Azure SQL Database is the newest service tier in the vCore-based purchasing model.

  • This service tier is a highly scalable storage and compute performance tier that leverages the Azure architecture to scale out the storage and compute resources for an Azure SQL Database substantially beyond the limits available for the General Purpose and Business Critical service tiers.

  • Support for up to 100 TB of database size

  • Nearly instantaneous database backups (based on file snapshots stored in Azure Blob storage) regardless of size with no IO impact on compute resources

  • Fast database restores (based on file snapshots) in minutes rather than hours or days (not a size of data operation)

  • Higher overall performance due to higher transaction log throughput and faster transaction commit times regardless of data volumes

  • Rapid scale out - you can provision one or more read-only replicas for offloading your read workload and for use as hot-standbys

  • Hyperscale Tier supports one read-write replica and 4 read-only replicas

• Azure SQL Database serverless

• Serverless is a compute tier for single databases in Azure SQL Database that automatically scales compute based on workload demand and bills for the amount of compute used per second. T

• The serverless compute tier also automatically pauses databases during inactive periods when only storage is billed and automatically resumes databases when activity returns.

• The cost for a serverless database is the summation of the compute cost and storage cost.

• When compute usage is between the min and max limits configured, the compute cost is based on vCore and memory used.

• When compute usage is below the min limits configured, the compute cost is based on the min vCores and min memory configured.

• When the database is paused, the compute cost is zero and only storage costs are incurred.

• The storage cost is determined in the same way as in the provisioned compute tier.

• Azure Hybrid Benefit (AHB) and reserved capacity discounts do not apply to the serverless compute tier.

•  

Backup And Retention Periods

• Automated backups - Azure SQL Database & Azure SQL Managed Instance

• Both SQL Database and SQL Managed Instance use SQL Server technology to create full backups every week, differential backups every 12-24 hours, and transaction log backups every 5 to 10 minutes.

  • The frequency of transaction log backups is based on the compute size and the amount of database activity.

• Azure SQL Database, Azure SQL Managed Instance and Azure Synapse all have a retention is 7 days by default.

  • Azure SQL Database, Azure SQL Managed Instance retention can also be configured for up to 35 days (irrespective to license tier)

  • Long Term retention can be setup for up to 10 yrs for Azure SQL Database, Azure SQL Managed Instances (irrespective to license tier).

• The retention periods are available for all DB tiers and vcore plans.

• Long-term retention - Azure SQL Database and Azure SQL Managed Instance

• Many applications have regulatory, compliance, or other business purposes that require you to retain database backups beyond the 7-35 days provided by Azure SQL Database and Azure SQL Managed Instance automatic backups.

• By using the long-term retention (LTR) feature, you can store specified SQL Database and SQL Managed Instance full backups in Azure Blob storage with configured redundancy for up to 10 years.

• LTR backups can then be restored as a new database.

• If an LTR policy is configured, these backups are copied to different blobs for long-term storage.

  • The copy is a background job that has no performance impact on the database workload.

  • The LTR policy for each database in SQL Database can also specify how frequently the LTR backups are created.

Security

• An overview of Azure SQL Database and SQL Managed Instance security capabilities

• Transparent data encryption (TDE) for SQL Database, SQL Managed Instance, and Azure Synapse Analytics adds a layer of security to help protect data at rest from unauthorized or offline access to raw files or backups. 

  • all newly created databases are encrypted by default and the database encryption key is protected by a built-in server certificate.

  • Certificate maintenance and rotation are managed by the service and require no input from the user.

  • Customers who prefer to take control of the encryption keys can manage the keys in Azure Key Vault.

  • Bring Your Own Key (BYOK) support for Transparent Data Encryption (TDE) allows customers to take ownership of key management and rotation using Azure Key Vault

• Transparent data encryption or always encrypted?

  • Transparent Data Encryption (TDE) is used by default to encrypt database data and backups while at rest.

  • Supports DTU-based pricing.

• Always Encrypted is a feature designed to protect sensitive data stored in specific database columns from access (for example, credit card numbers, national identification numbers, or data on a need to know basis).

  • This includes database administrators or other privileged users who are authorized to access the database to perform management tasks, but have no business need to access the particular data in the encrypted columns.

  • The data is always encrypted, which means the encrypted data is decrypted only for processing by client applications with access to the encryption key.

  • The encryption key is never exposed to SQL Database or SQL Managed Instance and can be stored either in the Windows Certificate Store or in Azure Key Vault

• Always Encrypted

• Always Encrypted is a feature designed to protect sensitive data, such as credit card numbers or national identification numbers, stored in Azure SQL Database or SQL Server databases.

• Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine (SQL Database or SQL Server).

  • Encryption keys can be stored in Azure Vaults and Windows Certificate Store

• Always Encrypted allows you to identify sensitive data columns before DB is deployed to production

• As a result, Always Encrypted provides a separation between those who own the data and can view it, and those who manage the data but should have no access. 

• Data stored in the database is protected even if the entire machine is compromised, for example by malware.

• Always Encrypted leverages client-side encryption: a database driver inside an application transparently encrypts data, before sending the data to the database.

  • Similarly, the driver decrypts encrypted data retrieved in query results.

  • the data used in computations, in memory of the SQL Server process remains encrypted.

    • As a result, Always Encrypted protects the data from attacks that involve scanning the memory of the SQL Server process or extracting the data from a memory dump file.

    • Unlike TDE, this is only partially transparent to applications.

      • Although the client driver transparently encrypts and decrypts data, the application may need to be changed to adhere to requirements/limitations of Always Encrypted.

      • For example, Always Encrypted only supports very limited operations on encrypted database columns. 

• Develop applications using Always Encrypted with secure enclaves

• Selecting Deterministic or Randomized Encryption for Always Encrypted

  • The Database Engine never operates on plaintext data stored in encrypted columns, but it still supports some queries on encrypted data, depending on the encryption type for the column.

  • Always Encrypted supports two types of encryption: randomized encryption and deterministic encryption.

    • Deterministic encryption always generates the same encrypted value for any given plain text value.

      • Using deterministic encryption allows point lookups, equality joins, grouping and indexing on encrypted columns.

      • However, it may also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column, especially if there's a small set of possible encrypted values, such as True/False, or North/South/East/West region.

      • Deterministic encryption must use a column collation with a binary2 sort order for character columns.

  • Randomized encryption uses a method that encrypts data in a less predictable manner.

    • Randomized encryption is more secure, but prevents searching, grouping, indexing, and joining on encrypted columns.

Dynamic Data Masking

• Dynamic Data Masking

• Dynamic data masking (DDM) limits sensitive data exposure by masking it to non-privileged users.

• It can be used to greatly simplify the design and coding of security in your application.

• Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to specify how much sensitive data to reveal with minimal impact on the application layer.

• DDM can be configured on designated database fields to hide sensitive data in the result sets of queries.

• With DDM the data in the database is not changed.

• DDM is easy to use with existing applications, since masking rules are applied in the query results.

  • Many applications can mask sensitive data without modifying existing queries.

• Dynamic Data Masking

• Dynamic Data Masking Overview

  • With DDM the data in the database is not changed.

  • DDM is easy to use with existing applications, since masking rules are applied in the query results.

  • Many applications can mask sensitive data without modifying existing queries.

• Use Simple Transact-SQL commands to define and manage masks.

  • ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()')

• Configure and manage Azure AD authentication with Azure SQL

• You set up a dynamic data masking policy in the Azure portal by selecting the Dynamic Data Masking blade under Security in your SQL Database configuration pane.

• This feature cannot be set using portal for SQL Managed Instance.

• The Set-AzSqlDatabaseDataMaskingPolicy cmdlet sets the data masking policy for an Azure SQL database.

  • To use this cmdlet, use the ResourceGroupName, ServerName, and DatabaseName parameters to identify the database.

  • You can set the DataMaskingState parameter to specify whether data masking operations are enabled or disabled.

• The Set-AzSqlDatabaseDataMaskingRule cmdlet sets a data masking rule for an Azure SQL database.

  • To use the cmdlet, provide the ResourceGroupName, ServerName, DatabaseName, and RuleId parameters to identify the rule.

  • Set-AzSqlDatabaseDataMaskingRule -DatabaseName "Weyland" -SchemaName "dbo" -TableName "Customers" -ColumnName "email_address" -MaskingFunction "email "

Costs

• Save costs for resources with reserved capacity - Azure SQL Database & SQL Managed Instance

• Save money with Azure SQL Database and SQL Managed Instance by committing to a reservation for compute resources compared to pay-as-you-go prices.

• With reserved capacity, you make a commitment for SQL Database and/or SQL Managed Instance use for a period of one or three years to get a significant discount on the compute costs.

• To purchase reserved capacity, you need to specify the Azure region, deployment type, performance tier, and term.

• You do not need to assign the reservation to a specific database or managed instance.

  • Matching existing deployments that are already running or ones that are newly deployed automatically get the benefit.

Azure SQL Stretch Database

• Stretch Database

• Stretch Database migrates your cold data transparently and securely to the Microsoft Azure cloud.

• Stretch warm and cold transactional data dynamically from SQL Server to Microsoft Azure with SQL Server Stretch Database.

• Unlike typical cold data storage, your data is always online and available to query.

• You can provide longer data retention timelines without breaking the bank for large tables like Customer Order History.

  • Benefit from the low cost of Azure rather than scaling expensive, on-premises storage.

• Access your SQL Server data seamlessly regardless of whether it's on-premises or stretched to the cloud.

  • You set the policy that determines where data is stored, and SQL Server handles the data movement in the background.

  • The entire table is always online and queryable.

  • And, Stretch Database doesn't require any changes to existing queries or applications - the location of the data is completely transparent to the application.

     

• After you enable Stretch Database for a SQL Server instance and a database, and select at least one table, Stretch Database silently begins to migrate your cold data to Azure.

• If you store cold data in a separate table, you can migrate the entire table.

• If your table contains both hot and cold data, you can specify a filter function to select the rows to migrate.

• You don't have to change existing queries and client apps. 

  • You continue to have seamless access to both local and remote data, even during data migration.

  • There is a small amount of latency for remote queries, but you only encounter this latency when you query the cold data.

• Stretch Database ensures that no data is lost if a failure occurs during migration.

• It also has retry logic to handle connection issues that may occur during migration. A dynamic management view provides the status of migration.

Temporal Tables

• Getting started with temporal tables in Azure SQL Database and Azure SQL Managed Instance

• Temporal tables are a programmability feature of Azure SQL Database and Azure SQL Managed Instance that allows you to track and analyze the full history of changes in your data, without the need for custom coding.

• Temporal tables keep data closely related to time context so that stored facts can be interpreted as valid only within the specific period.

• This property of temporal tables allows for efficient time-based analysis and getting insights from data evolution

Azure SQL Server Integration Services (SSIS)

• To run SSIS packages, you need "Azure-SQL Server Integration services Integration runtime" https://www.youtube.com/watch?v=weiHOeje-QA min 3:07.

• To connect without VPN or Express Route, you need to install a self-hosted integration runtime that acts as a proxy (as seen on video).

• Create an Azure-SSIS integration runtime in Azure Data Factory

  • This article provides steps for provisioning an Azure-SQL Server Integration Services (SSIS) integration runtime (IR) in Azure Data Factory (ADF). An Azure-SSIS IR supports:

    • Running packages deployed into SSIS catalog (SSISDB) hosted by Azure SQL Database server/Managed Instance (Project Deployment Model)

    • Running packages deployed into file system, Azure Files, or SQL Server database (MSDB) hosted by Azure SQL Managed Instance (Package Deployment Model)

SQL Database Managed Instance

• What is Azure SQL Managed Instance?

• Using the fully automated Azure Data Migration Service, customers can lift and shift their existing SQL Server instance to SQL Managed Instance, which offers compatibility with SQL Server and complete isolation of customer instances with native VNet support.

• With Software Assurance, you can exchange your existing licenses for discounted rates on SQL Managed Instance using the Azure Hybrid Benefit for SQL Server

• Gives you near 100% compatibility with your on-prem server

• Note that managed instances support SQL Agent and Database Email.

• Only vcore purchasing model available (no DTU).

• Transparent data encryption encrypts DB disk data at rest, as well as backups

• Data protected with automated backups

• Customer configurable backup retention period

• A SQL Managed Instance can't access file shares and Windows folders

• Supports up to 4TB DB.

• Does not provide read-write replica of the database UNLESS the primary base fails and then one of the read-only replicas is turned into read-write.

• Common Language Runtime Integration

• Microsoft SQL Server and Azure SQL Managed Instance enable you to implement some of the functionalities with .NET languages using the native common language runtime (CLR) integration as SQL Server server-side modules (procedures, functions, and triggers).

• The CLR supplies managed code with services such as cross-language integration, code access security, object lifetime management, and debugging and profiling support.

• For SQL Server users and application developers, CLR integration means that you can now write stored procedures, triggers, user-defined types, user-defined functions (scalar and table valued), and user-defined aggregate functions using any .NET Framework language, including Microsoft Visual Basic .NET and Microsoft Visual C#. 

• Connectivity architecture for Azure SQL Managed Instance

• SQL Managed Instance is placed inside the Azure virtual network and the subnet that's dedicated to managed instances. This deployment provides:

  • A secure private IP address.

  • The ability to connect an on-premises network to SQL Managed Instance.

  • The ability to connect SQL Managed Instance to a linked server or another on-premises data store.

  • The ability to connect SQL Managed Instance to Azure resources.

• SQL Managed Instance is a platform as a service (PaaS) offering. 

• Because Azure is responsible for management, customers can't access the SQL Managed Instance virtual cluster machines through Remote Desktop Protocol (RDP).

• To check the trustworthiness of communicating parties, SQL Managed Instance constantly verifies these certificates through certificate revocation lists.

  • If the certificates are revoked, SQL Managed Instance closes the connections to protect the data.

• SQL Managed Instance is a set of service components.

  • These components are hosted on a dedicated set of isolated virtual machines that run inside the customer's virtual network subnet. These machines form a virtual cluster.

• A virtual cluster can host multiple managed instances.

  • If needed, the cluster automatically expands or contracts when the customer changes the number of provisioned instances in the subnet.

• Customer applications can connect to SQL Managed Instance and can query and update databases inside the virtual network, peered virtual network, or network connected by VPN or Azure ExpressRoute.

• This network must use an endpoint and a private IP address.

Note the SKU parameter

Elastic Pools

• Elastic pools enable the developer to purchase resources for a pool shared by multiple databases to accommodate unpredictable periods of usage by individual databases.Within the pool, individual databases are given the flexibility to auto-scale within set parameters. Under heavy load, a database can consume more resources to meet demand. 

• Elastic Pool Overview: Elastic pools help you manage and scale multiple databases in Azure SQL Database

• The databases in an elastic pool are on a single server and share a set number of resources at a set price. 

  • You can move a database from one elastic pool to another if the pool is in the same Azure SQL server.

  • You can create multiple pools on a server, but you can't add databases from different servers into the same pool.

• Multiple databases with persistent medium-high utilization should not be placed in the same elastic pool.

• Elastic Pools are supported only for Azure SQL Database single instance and not for managed instances.

Failover

• Use auto-failover groups to enable transparent and coordinated geo-failover of multiple databases

  • Tutorial: Add an Azure SQL Database to an autofailover group

  • Tutorial: Add an Azure SQL Database elastic pool to a failover group

• The auto-failover groups feature allows you to manage the replication and failover of a group of databases on a server or all databases in a managed instance to another region.

• Auto-failover groups support geo-replication of all databases in the group to only one secondary server or instance in a different region. 

• When configuring a failover group, ensure that authentication and network access on the secondary is set up to function correctly after geo-failover, when the geo-secondary becomes the new primary.

• To do auto-failover, you must have primary and secondary servers in different regions.

  • If you want to failover databases in an elastic pool, then the secondary server must have the same pool name.

  • You can also failover a database that is not part of a pool to a secondary server.
    
    

• You can put several single databases on the same server into the same failover group.

  • If you add a single database to the failover group, it automatically creates a secondary database using the same edition and compute size on secondary server.

    • You specified that server when the failover group was created.

• For two managed instances to participate in a failover group, there must be either ExpressRoute or a gateway configured between the virtual networks of the two managed instances to allow network communication.

  • You create the two VPN gateways and connect them.

  • Global virtual network peering is supported for SQL managed instances created in empty subnets after 9/2020

    • Enabling geo-replication between managed instance virtual networks

  • I don’t believe Azure Private Link can be used to accomplish the connectivity needed above.

  • Connectivity architecture for Azure SQL Managed Instance

Distributed Transactions

•  "distributed transactions" and "elastic database transactions" are considered synonyms and will be used interchangeably.

• Elastic database transactions for Azure SQL Database (Preview) and Azure SQL Managed Instance allow you to run transactions that span several databases.

• Both SQL Database and SQL Managed Instance support client-side development experiences in C# and .NET.

• A server-side experience (code written in stored procedures or server-side scripts) using Transact-SQL is available for SQL Managed Instance only.

OLTP

• OLTP (On-line Transaction Processing) is involved in the operation of a particular system.

• OLTP is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE).

• The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second.

• In OLTP database there is detailed and current data, and schema used to store transactional databases is the entity model (usually 3NF). 

• Premium tier supports in-memory OLTP

• OLAP (On-line Analytical Processing) deals with Historical Data or Archival Data.

• OLAP is characterized by relatively low volume of transactions.

• Queries are often very complex and involve aggregations.

• For OLAP systems a response time is an effectiveness measure.

  • OLAP applications are widely used by Data Mining techniques.

  • In OLAP database there is aggregated, historical data, stored in multi-dimensional schemas (usually star schema).

Query Performance

• Query Performance Insight for Azure SQL Database

• Query Performance Insight provides intelligent query analysis for single and pooled databases.

• It helps identify the top resource consuming and long-running queries in your workload.

  • This helps you find the queries to optimize to improve overall workload performance and efficiently use the resource that you are paying for.

• Deeper insight into your databases resource (DTU) consumption

• Details on top database queries by CPU, duration, and execution count (potential tuning candidates for performance improvements)

• The ability to drill down into details of a query, to view the query text and history of resource utilization

• Annotations that show performance recommendations from database advisors

• Query Performance Insight requires that Query Store is active on your database. It's automatically enabled for all databases in Azure SQL Database by default. 

• Long-running queries have the greatest potential for locking resources longer, blocking other users, and limiting scalability.

  • They're also the best candidates for optimization.

    • For more information, see Understand and resolve Azure SQL blocking problems.

    • in SQL Server, blocking occurs when one session holds a lock on a specific resource and a second SPID attempts to acquire a conflicting lock type on the same resource. 

    • For a description of locking, lock hints, and transaction isolation levels, see the following articles:

      • Locking in the Database Engine

      • Customizing Locking and Row Versioning

      • Lock Modes

      • Lock Compatibility

      • Transactions

Azure SQL Analytics

• Configure streaming export of Azure SQL Database and SQL Managed Instance diagnostic telemetry

• Azure SQL Database and Azure SQL Managed Instance metrics and resource logs that are streamed into a Log Analytics workspace can be consumed by Azure SQL Analytics.

• Azure SQL Analytics is a cloud solution that monitors the performance of single databases, elastic pools and pooled databases, and managed instances and instance databases at scale and across multiple subscriptions.

• It can help you collect and visualize performance metrics, and it has built-in intelligence for performance troubleshooting.

Database Auditing

• Auditing for Azure SQL Database and Azure Synapse Analytics

• Auditing for Azure SQL Database and Azure Synapse Analytics tracks database events and writes them to an audit log in your Azure storage account, Log Analytics workspace, or Event Hubs.

• Requires a general-purpose v2 storage account.

  • If you have a general-purpose v1 or blob storage account, upgrade to a general-purpose v2 storage account. For more information, see Types of storage accounts

  • Premium storage is currently not supported.

  • Hierarchical namespace for Azure Data Lake Storage Gen2 storage account is currently not supported.

  • Write audit to a storage account behind VNet and firewall

• Audit logs are written to Append Blobs in an Azure Blob storage on your Azure subscription

• Be sure that the event hub is in the same region as your database and server.

• Storage account blobs usedas the destination must be in the same region as your database and server

• With geo-replicated databases, when you enable auditing on the primary database the secondary database will have an identical auditing policy. 

Automatic Tuning

• Automatic tuning in Azure SQL Database and Azure SQL Managed Instance

• Azure SQL Database and Azure SQL Managed Instance automatic tuning provides peak performance and stable workloads through continuous performance tuning based on AI and machine learning.

• Automatic tuning is a fully managed intelligent performance service that uses built-in intelligence to continuously monitor queries executed on a database, and it automatically improves their performance.

  • This is achieved through dynamically adapting a database to changing workloads and applying tuning recommendations.

  • Automatic tuning learns horizontally from all databases on Azure through AI and it dynamically improves its tuning actions. 
    
    

Creating A SQL Database From Portal

Create the server and assign it an administrator

Some Errors

This is caused by too many open connections to your DB.