AZURESQLDBA - Comparison of SQL Managed Instance and Azure SQL Database

 

Comparison of SQL Managed Instance and Azure SQL Database

SQL Managed Instance

1.      Compatibility: Near 100% compatibility with SQL Server, making it ideal for lift-and-shift migrations

2.      High Availability: Built-in high availability with a 99.99% SLA, including automated backups and geo-replication

3.      Security: Advanced security features like Always Encrypted, Dynamic Data Masking, Row-Level Security, and Advanced Threat Protection

4.      Resource Governor: Allows you to manage and limit resources like CPU, memory, and I/O for different workloads

5.      SQL CLR: Supports SQL CLR, enabling you to write programmable database objects in managed code

6.      Networking: Native virtual network (VNet) implementation for enhanced security and isolation

7.      System Stored Procedures: Supports nearly all system stored procedures

8.      USE Statement: Supports the USE statement for switching databases

.

Azure SQL Database

1.      Scalability: Offers dynamic scalability with options to scale up or down based on workload needs

2.      High Availability: Built-in high availability with a 99.99% SLA, including automated backups and geo-replication

3.      Security: Advanced security features like Always Encrypted, Dynamic Data Masking, Row-Level Security, and Advanced Threat Protection

4.      Automatic Tuning: Features like automatic indexing and query performance tuning to optimize database performance

5.      Multi-Model Capabilities: Supports JSON, XML, and graph data processing

6.      Managed Service: Fully managed service with automated patching, updates, and backups

2

.

7.      Global Temporary Tables: Does not support global temporary tables (##)

8.      USE Statement: Does not support the USE statement for switching databases

Key Differences

·        Compatibility: SQL Managed Instance offers near 100% compatibility with SQL Server, while Azure SQL Database has some limitations in feature support

·        Resource Management: SQL Managed Instance supports Resource Governor and SQL CLR, which are not available in Azure SQL Database

·        Networking: SQL Managed Instance uses native VNet implementation, providing enhanced security and isolation compared to Azure SQL Database

·        System Stored Procedures: SQL Managed Instance supports nearly all system stored procedures, whereas Azure SQL Database does not support procedures related to unsupported features

 

 

 

 

Name	Azure SQL Database	SQL Managed Instance
Backup	Automatic Backups: Backups are automatically scheduled and start within minutes of provisioning. They are transaction-consistent, allowing point-in-time restores. Backup Storage Costs: No additional cost for backup storage until it exceeds 100% of the database size. You can manage costs by adjusting the backup retention period. Long-Term Retention: Store backups in a separate Azure blob container for up to 10 years at a lower cost. Export Options: You can export the database to a bacpac or dacpac file and store it in Azure Storage.	Automatic Backups: Backups are automatically created and retained for a specified period. Native COPY_ONLY backups to Azure blob containers are supported. Backup Storage Costs: Backup storage is free, with an equal amount of storage reserved for the instance, regardless of the retention period. Long-Term Retention: Currently in limited preview, this feature allows storing backups for extended periods, available for EA and CSP subscriptions.
Recovery model	Default Recovery Model: The default recovery model is FULL and cannot be changed. This is different from on-premises SQL Server, where you can modify the recovery model. Master Database: The recovery model is set when the master database is created, and since the master database is read-only, it cannot be modified. Viewing Recovery Model: To view the recovery model of an Azure SQL Database, you can run the following query:	Default Recovery Model: The default recovery model is FULL and cannot be changed, similar to Azure SQL Database. Viewing Recovery Model: To view the recovery model of an SQL Managed Instance, you can run the same query:
SQL Server Agent	No SQL Server Agent: SQL Database does not include SQL Server Agent for scheduling jobs and sending notifications. Workarounds: SQL Agent Job on On-Premises or Azure VM: Create an SQL Agent job on an on-premises SQL Server or an Azure VM to connect and run on the SQL Database. Azure Automation: Schedule jobs in Microsoft Azure to automate manual tasks. Elastic Jobs: Use Elastic Jobs to schedule the execution of ad hoc tasks. PowerShell: Automate tasks using PowerShell and schedule script execution with Windows Scheduler, either on-premises or on an Azure VM.	SQL Server Agent: Managed Instance includes SQL Server Agent, allowing you to schedule jobs similarly to on-premises SQL Server. Limitations: Some actions, such as enabling and disabling SQL Server Agent, are not allowed. The SQL Server Agent process is always in the running state.
Change Data Capture (CDC) Purpose: CDC captures data modifications to CDC-enabled databases and tables, which is crucial for incremental load scenarios, such as updating a data warehouse from an OLTP environment. Requirement: CDC requires SQL Server Agent.	No CDC Support: CDC is not available because SQL Server Agent is not supported. Alternatives: Temporal Tables: Use temporal tables to track changes over time. SQL Server Integration Services (SSIS): Implement CDC using SSIS packages. Azure Data Factory: Use ADF to orchestrate and implement CDC-like functionality.	CDC Support: CDC is supported because SQL Managed Instance includes SQL Server Agent.
Audit Logs Availability: Audit logs are available for both SQL Database and SQL Managed Instance. Differences from On-Premises: Unlike on-premises SQL Server, file system-level access is not granted in PaaS environments.	Audit Log Storage: Audit logs need to be captured on Azure Blob Storage due to the lack of file system-level access.	Audit Log Storage: Audit logs need to be captured on Azure Blob Storage due to the lack of file system-level access.
Mirroring	No Mirroring: Mirroring is not supported between two SQL Databases. Readable Secondary: You can set up a readable secondary, which allows you to have a secondary database that can be used for read-only operations.	No Mirroring: Mirroring is also not supported between SQL Managed Instance databases. Failover Group: You can set up a failover group, which provides automatic failover and load balancing for read-only workloads, offering a more robust solution than traditional mirroring.
Table partitioning	Partitioning: Table partitioning using partition schemes and partition functions is allowed. Primary Filegroup: All partitions must be created on the primary filegroup due to the PaaS nature of SQL Database. Performance: While you won't get performance improvements from having partitions on different disks, you will benefit from partition elimination, which can improve query performance.	Partitioning: Partitions can be created with different filegroups and files for each partition. Performance: This allows for better performance by having multiple files per database, leveraging different disks for improved I/O operations.
Replication	Replication Limitations: 1. No support for snapshot, transactional, or merge replication between two Azure SQL Databases. 2. Can be a subscriber to an on-premises or Azure VM SQL Server instance. 3. Supports one-way transactional replication (push subscription only). Requires SQL Server 2012 or above for on-premises. 4. Replication and distribution agents can't be configured on SQL Database.	Replication Capabilities: 1. Supports snapshot, transactional, and bi-directional transactional replication. 2. Does not support merge replication, peer-to-peer replication, or updatable subscriptions. 3. Publisher and distributor need to be configured on both SQL Managed Instance and on-premises SQL Server.
Multi-part names	Three-part names (databasename.schemaname.tablename) are limited to tempdb for accessing temporary tables. Example: SELECT * FROM tempdb.dbo.#temp1 s (databasename.schemaname.tablename) Four-part names (ServerName.DatabaseName.SchemaName.TableName) are not allowed. (ServerName.DatabaseName.SchemaName.TableName) Elastic queries can be used to access tables from different databases on the same Azure SQL Server. Two-part names (Schemaname.Tablename) can be used to access objects in different schemas within the same Azure SQL Database.(Schemaname.Tablename)	Supports multi-part names and cross-database queries.
SQL Server Browser	SQL Server Browser is a Windows service that provides instance and post information to incoming connection requests. This isn't required because SQL Database and SQL Managed Instance listen to port 1433 only.	SQL Server Browser is a Windows service that provides instance and post information to incoming connection requests. This isn't required because SQL Database and SQL Managed Instance listen to port 1433 only.
FileStream	The PaaS nature of SQL Database and SQL Managed Instance means they don't support FileStream or FileTable. Using Azure Storage as a workaround does indeed require some reworking of your application and database. Here are some steps to help you with this transition: Evaluate Dependencies: Identify all parts of your application that rely on FileStream/FileTable. Plan the Migration: Create a detailed plan for migrating data to Azure Storage. This might involve setting up new tables to store file metadata. Modify the Application: Update your application code to interact with Azure Storage using Azure SDKs or REST APIs. Test the Changes: Conduct thorough testing to ensure that the new setup works correctly and performs well. Monitor and Optimize: After migration, keep an eye on performance and make any necessary adjustments.	The PaaS nature of SQL Database and SQL Managed Instance means they don't support FileStream or FileTable. Using Azure Storage as a workaround does indeed require some reworking of your application and database. Here are some steps to help you with this transition: Evaluate Dependencies: Identify all parts of your application that rely on FileStream/FileTable. Plan the Migration: Create a detailed plan for migrating data to Azure Storage. This might involve setting up new tables to store file metadata. Modify the Application: Update your application code to interact with Azure Storage using Azure SDKs or REST APIs. Test the Changes: Conduct thorough testing to ensure that the new setup works correctly and performs well. Monitor and Optimize: After migration, keep an eye on performance and make any necessary adjustments.
Common Language Runtime (SQL CLR)	this feature is not available in SQL Database.	SQL Managed Instance supports SQL CLR, enabling users to write programmable database objects like stored procedures, functions, and triggers in managed code. This can lead to significant performance improvements in certain scenarios.
Resource Governor	This feature is not available in SQL Database.SQL Database Service Tiers: SQL Database offers various service tiers, each designed for different workloads: Basic: Suitable for small applications with light workloads. Standard: Ideal for business applications with moderate workloads. Premium: Designed for high-performance applications with heavy workloads. Hyperscale: Best for applications requiring rapid scaling and large storage.	SQL Managed Instance supports Resource Governor, which allows you to manage and limit resources like CPU, memory, and I/O for different SQL Server workloads. Evaluating Performance Tiers To choose the right performance tier for your application: Analyze Workload: Understand the resource requirements of your application. Test Performance: Use performance testing to determine the best tier. Monitor Usage: Continuously monitor resource usage and adjust the tier as needed.
Global temporary tables	Local Temporary Tables: Supported. Global Temporary Tables: Not supported.	SQL Managed Instance supports both local and global instance-scoped temporary tables: Local Temporary Tables Syntax: Defined with a single # (e.g., #TempTable). Scope: Accessible only within the session that created them. Global Temporary Tables Syntax: Defined with ## (e.g., ##GlobalTempTable). Scope: Accessible across all sessions within the same database. For example, a global temporary table created in database DB1 will be accessible to all sessions connecting to DB1.
Log shipping	Log shipping involves taking log backups on a primary server and copying and restoring them on a secondary server. It's often used for high availability, disaster recovery, or database migration. However, SQL Database and SQL Managed Instance do not support log shipping. Instead, they offer built-in high availability and business continuity features: SQL Database and SQL Managed Instance High Availability Features Automated Backups: Both services provide automated backups with point-in-time restore capabilities. Geo-Replication: Allows you to create readable replicas in different regions for disaster recovery. Failover Groups: Enables automatic failover of multiple databases in case of a regional outage. Always On Availability Groups: Supported in SQL Managed Instance for high availability and disaster recovery.	Log shipping involves taking log backups on a primary server and copying and restoring them on a secondary server. It's often used for high availability, disaster recovery, or database migration. However, SQL Database and SQL Managed Instance do not support log shipping. Instead, they offer built-in high availability and business continuity features: SQL Database and SQL Managed Instance High Availability Features Automated Backups: Both services provide automated backups with point-in-time restore capabilities. Geo-Replication: Allows you to create readable replicas in different regions for disaster recovery. Failover Groups: Enables automatic failover of multiple databases in case of a regional outage. Always On Availability Groups: Supported in SQL Managed Instance for high availability and disaster recovery.
SQL Trace and Profiler	SQL Trace and Profiler are not available for SQL Database.For SQL Database, you can use the following alternatives: Dynamic Management Views (DMVs): These provide insights into the health and performance of your database. Azure Portal Monitoring: Offers various monitoring tools and dashboards to track performance and diagnose issues. Extended Events: A lightweight performance monitoring system that can capture detailed information about SQL Server events.	SQL Profiler is supported on SQL Managed Instance, allowing you to trace and analyze SQL Server events.
Trace flags	SQL Database does not support trace flags. Instead, you can use other monitoring and diagnostic tools like: 1. Dynamic Management Views (DMVs) 2. Extended Events 3. Azure Portal Monitoring	SQL Managed Instance supports a limited set of global trace flags, which are special switches used to enable or disable specific SQL Server functionalities. These trace flags can be crucial for diagnosing performance issues or altering server behavior.                                Supported Global Trace Flags on SQL Managed Instance Some of the supported global trace flags include: 460: Enables detailed deadlock information. 2301: Enables advanced optimizations. 2389 and 2390: Enable cardinality estimation enhancements. 2453: Improves performance for certain types of queries. 2467: Enables memory grant feedback. 7471: Enables batch mode on rowstore. 8207: Enables parallel index build. 9389: Enables query store hints. 10316: Enables lightweight query profiling. 11024: Enables adaptive joins1.
System stored procedures	SQL Database does not support all system stored procedures, especially those related to features that are not available in SQL Database.                                     Unsupported System Stored Procedures in SQL Database Some examples include: sp_addmessage: Used to add a new user-defined message to the sys.messages catalog view. sp_helpuser: Provides information about database users. sp_configure: Used to display or change global configuration settings.	SQL Managed Instance supports nearly all system stored procedures, providing a high level of compatibility with on-premises SQL Server.                                             Supported System Stored Procedures in SQL Managed Instance SQL Managed Instance supports these and many other system stored procedures, making it a more suitable option for applications that rely heavily on SQL Server features.
The USE statement	it is not supported in SQL Database. In SQL Database, you need to establish a new connection to switch databases, as each connection is tied to a specific database.	The USE statement, which allows you to switch from one database context to another, is supported in SQL Managed Instance.