Differences between Azure SQL Database and SQL Server
Azure SQL Database is a PaaS offering and therefore some of its features differ from the on-premises SQL Server. Some of the important features that differ are as follows:
Backup and Restore
Conventional database backup and restore statements aren't supported. Backups are automatically scheduled and start within a few minutes of the database provisioning. Backups are consistent, transaction-wise, which means that you can do a point-in-time restore.
There is no additional cost for backup storage until it goes beyond 200% of the provisioned database storage.
You can reduce the backup retention period to manage backup storage costs. You can also use the long-term retention period feature to store backups in the Azure vault for a much lower cost for a longer duration.
Apart from automatic backups, you can also export the Azure SQL Database bacpac or dacpac file to Azure storage.
Recovery Model
The default recovery model of an Azure SQL database is FULL and it can't be modified to any other recovery model as in on-premises recovery models.
The recovery model is set when the master database is created, meaning when an Azure SQL server is provisioned, the recovery model can't be modified because the master database is read-only.
To view the recovery model of an Azure SQL database, execute the following query:
SELECT name, recovery_model_desc FROM sys.databases;
Note
You can use either of the two methods discussed earlier in the lesson to run the query – the Azure portal or SSMS.
You should get the following output:
Figure 1.29: Recovery model of an SQL database
SQL Server Agent
Azure SQL Server doesn't have SQL Server Agent, which is used to schedule jobs and send success/failure notifications. However, you can use the following workarounds:
- Create a SQL Agent job on an on-premise SQL server or on an Azure SQL VM SQL Agent to connect and run on the Azure SQL database.
- Azure Automation allows users to schedule jobs in Microsoft Azure to automate manual tasks. This topic is covered in detail later in the book.
- Elastic Database Jobs is an Azure Cloud service that allows the scheduled execution of ad hoc tasks. This topic is covered in detail later in the book.
- Use PowerShell to automate a task and schedule PowerShell script execution with Windows Scheduler, on-premises, or Azure SQL VM SQL Agent.
Change Data Capture
Change Data Capture (CDC) allows you to capture data modifications to CDC-enabled databases and tables. The CDC feature is important in incremental load scenarios, such as incrementally inserting changed data to the data warehouse from an OLTP environment. The CDC requires SQL Server Agent, and therefore isn't available in Azure SQL Database. However, you can use the temporal table, SSIS, or Azure Data Factory to implement CDC.
Auditing
The auditing features, such as C2 auditing, system health extended events, SQL default trace, and anything that writes alerts or issues into event logs or SQL error logs, aren't available. This is because it's a PaaS offering and we don't have access to or control of event logs or error logs.
However, there is an auditing and threat-detection feature available out of the box for Azure SQL Database.
Mirroring
You can't enable mirroring between two Azure SQL databases, but you can configure Azure SQL Database as a mirror server. You can also set up a readable secondary for an Azure SQL database, which is better than mirroring.
Table Partitioning
Table partitioning using a partition scheme and partition function is allowed in Azure SQL Database; however, because of the PaaS nature of the SQL database, all partitions should be created on a primary filegroup. You won't get a performance improvement by having partitions on different disks (spindles); however, you will get a performance improvement with partition elimination.
Replication
Conventional replication techniques, such as snapshot, transactional, and merge replication, can't be done between two Azure SQL databases. However, an Azure SQL database can be a subscriber to an on-premise or Azure VM SQL Server.
However, this too has limitations. It supports one-way transactional replication, not peer-to-peer or bi-directional replication; it supports only push subscription.
Note that you should have SQL Server 2012 or above at on-premises. Replication and distribution agents can't be configured on Azure SQL Database.
Multi-Part Names
Three-part names (databasename.schemaname.tablename) are only limited to tempdb, wherein you access a temp table as tempdb.dbo.#temp. For example, if there is a temporary table, say #temp1, then you can run the following query to select all the values from #temp1:
SELECT * FROM tempdb.dbo.#temp1
You can't access the tables in different SQL databases in Azure on the same Azure SQL server using three-part names. Four-part (ServerName.DatabaseName.SchemaName.TableName) names aren't allowed at all.
You can use an elastic query to access tables from different databases from an Azure SQL server. Elastic queries are covered in detail later in the book. You can access objects in different schemas in the same Azure SQL database using two-part (Schemaname.Tablename) names.
To explore other T-SQL differences, visit https://docs.microsoft.com/en-us/azure/sql-database/sql-database-transact-sql-information.
Unsupported Features
Some features not supported by Azure SQL Database or Azure SQL Server are:
- SQL Browser Service: SQL Browser is a Windows service and provides instance and post information to incoming connection requests. This isn't required because Azure SQL Server listens to port 1433 only.
- Filestream: Azure SQL Database doesn't support FileStream or filetable, just because of the PaaS nature of the service. There is a workaround to use Azure Storage; however, that would require a re-work on the application and the database side.
- Common Language Runtime (SQL CLR): SQL CLR allows users to write programmable database objects such as stored procedures, functions, and triggers in managed code. This provides a significant performance improvement in some scenarios. SQL CLR was initially supported and then the support was removed due to concerns about security issues.
- Resource Governor: Resource Governor allows you to throttle/limit resources (CPU, memory, and I/O) for different SQL Server workloads. This feature is not available in Azure SQL Database.
Azure SQL Database comes with different service tiers, each suitable for different workloads. You should evaluate the performance tier your application workload will fit into and accordingly provision the database for that performance tier.
- Global Temporary Tables: Global temporary tables are defined by ## and are accessible across all sessions. These are not supported in Azure SQL Database. Local temporary tables are allowed. Global temporary tables created with ## are accessible across all sessions for a particular database. For example, a global temporary table created in database DB1 will be accessible to all sessions connecting to database DB1 only.
- Log Shipping: Log shipping is the process of taking log backups on a primary server and copying and restoring them on a secondary server. Log shipping is commonly used as a high-availability or disaster-recovery solution, or to migrate a database from one SQL instance to another. Log shipping isn't supported by Azure SQL Database.
- SQL Trace and Profiler: SQL Trace and Profiler can't be used to trace events on Azure SQL Server. As of now, there isn't any direct alternate other than using DMVs, monitoring using the Azure portal, and extended events.
- Trace Flags: Trace flags are special switches used to enable or disable a particular SQL Server functionality. These are not available in Azure SQL Server.
- System Stored Procedures: Azure SQL Database doesn't support all the system stored procedures supported in the on-premises SQL Server. System procedures such as sp_addmessage, sp_helpuser, and sp_configure aren't supported. In a nutshell, procedures related to features unsupported in Azure SQL Database aren't supported.
- USE Statement: The USE statement is used to switch from one database context to another. This isn't supported in Azure SQL Database.