Azure SQL Database vs SQL Server: Deployment, Security, and Performance

Azure SQL Database and SQL Server are two popular database options, but they have some key differences when it comes to deployment, security, and performance.

Azure SQL Database is a managed cloud-based database service that allows you to deploy databases quickly and easily, without the need for manual provisioning or patching.

Deployment is a breeze with Azure SQL Database, as it supports auto-scaling and automated backups.

This means you can quickly scale up or down to meet changing demands, without worrying about manual configuration or downtime.

SQL Server, on the other hand, requires manual deployment and configuration, which can be time-consuming and prone to errors.

In terms of security, Azure SQL Database has robust features like encryption at rest and in transit, as well as network isolation and access control.

These features help protect your data from unauthorized access and ensure compliance with regulatory requirements.

SQL Server also has robust security features, but they require manual configuration and maintenance, which can be a challenge for some users.

Both Azure SQL Database and SQL Server offer automated patching and backups, which can be scheduled to run at specific times or based on specific events.

Azure SQL Database requires a managed instance, which can be created in just a few clicks, whereas SQL Server requires a separate instance to be set up and configured.

Azure SQL Database's managed instance provides automatic configuration and management of the underlying infrastructure, which can save time and reduce errors.

You have two main deployment models to choose from when using the Azure platform for SQL databases: single database and elastic pool.

A single database at Azure SQL is essentially a contained SQL Server database hosted in the cloud, isolated from other databases and managed via a server.

You can scale up or down resources allocated to a single database as needed.

The single database deployment model is suitable for a cloud application that requires a single data source.

An elastic pool, on the other hand, allows multiple databases to share resources managed via a logical server.

You can move a single database into an elastic pool or remove it whenever you need to.

The elastic pool is ideal for several databases that require resources to work effectively.

Resource requirements for each database are measured in DTUs for single databases and eDTUs for elastic database pools.

The elastic pool receives a set number of eDTUs for a fixed price, and you pay for it as a single whole, not for each separate database.

Within the pool, an individual database can consume more eDTUs if the load grows, and you won't be charged for unused eDTUs if the load is less or absent.

Azure SQL Managed Instance has automatic backups, so you can create full database COPY_ONLY backups. This feature allows you to back up your database without affecting ongoing transactions.

You can back up an instance database only to an Azure Blob storage account. Most of the general WITH options are supported.

To authenticate when backing up or restoring a database to/from an Azure storage, you can use either managed identity or shared access signature (SAS). Using Access keys for these scenarios isn't supported.

With a SQL Managed Instance, you can back up an instance database to a backup with up to 32 stripes, which is enough for databases up to 4 TB if backup compression is used.

The maximum backup stripe size by using the BACKUP command in SQL Managed Instance is 195 GB, which is the maximum blob size.

Failover groups can be a bit tricky, especially when it comes to system databases. System databases aren't replicated to the secondary instance in a failover group.

This means that scenarios that depend on objects from the system databases are impossible on the secondary instance unless the objects are manually created on the secondary.

Azure takes data safety seriously, with a Firewall that prevents unauthorized access to databases and virtual network rules that only allow requests from selected subnets.

It also tracks malicious activities that threaten database safety and sends alerts to customers.

With Advanced Threat Protection, you can receive alerts and email notifications about suspicious activities, SQL attacks, abnormal database access, and query patterns.

Azure takes data safety seriously, with a Firewall that prevents unauthorized access to databases and virtual network rules that only allow requests from selected subnets.

This means your data is protected from malicious activities that could threaten its safety.

The Firewall is just one part of Azure's security measures, which also include tracking and alerting customers to potential threats.

You can receive email notifications about suspicious activities, SQL attacks, abnormal database access, and query patterns.

This advanced threat protection helps you stay on top of potential security risks and take action before they become major problems.

Auditing is a crucial aspect of security and compliance in Microsoft Azure SQL, and it's essential to understand the key differences between auditing in Azure SQL, SQL Server, and SQL Managed Instance.

Auditing works at the server level in SQL Managed Instance, with .xel log files stored in Azure Blob storage.

With Azure SQL Database, auditing works at the database level, also storing .xel log files in Azure Blob storage.

In contrast, auditing in SQL Server, whether on-premises or in virtual machines, works at the server level, with events stored on the file system or Windows event logs.

If you're using SQL Managed Instance, you'll need to use Azure Blob storage targets for XEvent auditing, as file and Windows logs aren't supported.

To create an audit in SQL Managed Instance, you'll need to use the new TO URL syntax to specify the URL of the Azure Blob storage container where the .xel files are placed.

The CREATE AUDIT syntax has changed for auditing to Azure Blob storage, and the syntax TO FILE isn't supported because SQL Managed Instance can't access Windows file shares.

Here are the key differences in the CREATE AUDIT syntax for auditing to Azure Blob storage:

Performance monitoring is a critical aspect of database management, and Azure SQL Database has it covered. Azure SQL Database monitors CPU and IO according to the service tier.

You can view the Query Performance Insights to see how your database is performing. An AI is used to troubleshoot issues and help you maximize database performance.

Azure SQL Database provides recommendations on performance optimization, and you can configure the implementation automatically.

In Azure SQL Database, you have some limitations to consider when it comes to database configuration. Multiple log files aren't supported.

The General Purpose service tier has some specific limitations, such as a limit of 280 files per instance, which means a maximum of 280 files per database. This includes both data and log files. The Business Critical tier, on the other hand, supports 32,767 files per database.

Each file in Azure SQL Database is placed in Azure Blob storage, and IO and throughput per file depend on the size of each individual file.

You can purchase Azure SQL Database under two main models: vCore and DTU. The vCore model is also known as serverless, allowing you to specify the hardware characteristics you want in the cloud.

The vCore model represents logical CPU, giving you flexibility and control over your resources. It's available in Standard, Premium, and Hyperscale service tiers, with prices depending on your specific requirements.

The DTU-based model provides a preconfigured bundle of computing and storage, making it simpler to set up. It's available in Basic, Standard, and Premium service tiers, with the Standard and Premium tiers allowing you to add more storage.

Each database under the DTU model gets a granted level of resources, making performance predictable regardless of other databases. This model is ideal for users who want preconfigured resources.

The vCore model requires more effort from the customer, but it provides more flexibility and control over scaling and pausing databases. Microsoft recommends the vCore-based model for customers, but you can choose the option that suits you best.

Azure SQL Database offers several options to suit different needs. You can purchase the database under the vCore and DTU models.

The vCore model is also known as serverless, allowing you to specify the hardware characteristics you want in the cloud. This option lets you transfer your on-premise requirements to the cloud, ensuring you have the required environment to work with databases efficiently.

The vCore-based model is available in Standard, Premium, and Hyperscale service tiers. The DTU-based model is available in Basic, Standard, and Premium service tiers.

The vCore model provides more flexibility and control, but requires more effort from the customer's side. Microsoft recommends the vCore-based model for customers, but you can choose the option that suits you best.

Here are the service tiers available in Azure SQL Database:

The choice of service tier depends on your business requirements for storage and performance. The billing is hourly for any service tier, and you can change a type of the service tier at any moment from the Azure portal or by executing a dedicated SQL query.

Collation is an important aspect of database configuration. The default instance collation is SQL_Latin1_General_CP1_CI_AS.

You can specify a different collation as a creation parameter. This is useful if you need to support a specific language or character set.

When you're setting up a database, one important thing to consider is the compatibility level. This determines how the database interacts with newer versions of the system.

The supported compatibility levels are 100, 110, 120, 130, 140, 150, and 160.

You'll notice that compatibility levels below 100 aren't supported, so make sure you're using one of these levels to avoid any issues.

The default compatibility level for new databases is 150, which is a good starting point. However, if you're restoring a database, the compatibility level will remain unchanged if it was 100 or above.

Here's a quick rundown of the supported compatibility levels:

When creating a database, there are some limitations to be aware of. The CREATE DATABASE statement doesn't allow you to define files and filegroups.

One of the most notable limitations is the absence of support for memory-optimized filegroups and files. However, don't worry, you can still achieve this by using the XTP filegroup and file that's automatically added.

The CONTAINMENT option is not supported, so you'll need to find an alternative. A workaround is to use ALTER DATABASE after CREATE DATABASE to set database options.

You won't be able to use the FOR ATTACH option either, so plan accordingly. The AS SNAPSHOT OF option is also not supported, so make sure to check the documentation for alternative solutions.

Here are the limitations in a concise list:

When working with large datasets, you'll often need to import data from external sources into your SQL Managed Instance. Bulk Insert and OpenRowset are two powerful tools that can help you do just that.

You can't access file shares and Windows folders directly from SQL Managed Instance, so you'll need to import files from Azure Blob storage instead.

To do this, you'll need to use the DATASOURCE parameter in your BULK INSERT command. This tells SQL Managed Instance where to find the file you're trying to import.

Similarly, when using the OPENROWSET function to read the content of a file from Azure Blob storage, you'll also need to specify the DATASOURCE.

Here are the key points to keep in mind when using Bulk Insert and OpenRowset:

DBCC is a powerful tool in SQL Server, but it's essential to understand its limitations in SQL Managed Instance.

DBCC statements that are enabled in SQL Server aren't supported in SQL Managed Instance.

Some global Trace flags are supported, but session-level Trace flags are not.

DBCC TRACEOFF and DBCC TRACEON work with the limited number of global trace-flags.

If you need to use DBCC CHECKDB, be aware that options REPAIR_ALLOW_DATA_LOSS, REPAIR_FAST, and REPAIR_REBUILD are not allowed because the database can't be set in SINGLE_USER mode.

Potential database corruption is handled by the Azure support team. Contact them if you suspect any issues.

To summarize, here are the DBCC limitations to keep in mind:

Distributed transactions are a powerful feature in Azure SQL Managed Instance, allowing for complex transactions across multiple instances.

T-SQL and .NET based distributed transactions across managed instances are generally available, making it easy to implement across your existing codebase.

Other scenarios, such as XA transactions, are supported with DTC for Azure SQL Managed Instance, which is currently in public preview.

This means you can experiment with more advanced distributed transactions, but keep in mind the preview status and plan accordingly.

DTC for Azure SQL Managed Instance provides a robust way to manage distributed transactions, giving you more flexibility in your application design.

External libraries are supported in Azure SQL Managed Instance, but only in a limited public preview.

This means that you can use external libraries like R and Python to enhance your machine learning capabilities, but you'll need to keep an eye on any updates or changes to this feature.

In-database R and Python external libraries are supported in limited public preview. See Machine Learning Services in Azure SQL Managed Instance (preview).

This limited preview suggests that the feature is still being tested and refined, so it's essential to weigh the benefits against any potential risks or limitations.

Replication is a key feature in SQL configuration, and it's essential to understand the different types supported. Snapshot and Bi-directional replication types are supported, but Merge replication, Peer-to-peer replication, and updatable subscriptions aren't.

If you need to set up transactional replication, it's available for SQL Managed Instance, but with some constraints. You'll need to follow specific tutorials for configuration.

Some database options are set or overridden when replication is enabled and can't be changed later. Transport security is supported, but dialog security isn't.

Here are the replication types that are supported:

For more information on transactional replication, check out the tutorials on replication between a SQL MI publisher and subscriber, or between a SQL MI publisher, distributor, and SQL Server subscriber.

In SQL Managed Instance, SERVERPROPERTY('EngineEdition') returns a unique value of 8, which identifies the instance.

SERVERPROPERTY('InstanceName') returns NULL because the concept of instance doesn't apply to SQL Managed Instance.

The @@SERVERNAME function returns a full DNS "connectable" name, such as my-managed-instance.wcus17662feb9ce98.database.windows.net.

You can also use SYS.SERVERS to get the full DNS "connectable" name, which includes properties like "name" and "data_source".

The @@SERVICENAME function returns NULL because the concept of service doesn't apply to SQL Managed Instance.

SUSER_ID is supported and returns NULL if the Microsoft Entra login isn't in sys.syslogins.

Here's a quick rundown of the server properties you can use: