Differences Between Azure SQL Server And On-Prem SQL Server?

In today’s article, we will explain what the differences are between Azure SQL Server and On-Prem SQL Server.

Azure SQL Server is a Paas product and therefore has features that differ from on-premise SQL Server.

Some of the important differentiating features are as follows.

Backup and Restore

The backup and restore commands you use in the on-premise environment are not supported in Azure SQL Server.

Backups are automatically scheduled and run within a few minutes of the database being created.

Backups are consistent, transaction-based, meaning you can go back at any point in time.

Point in time restoration feature is available.

In order to use this structure, you need to develop a backup strategy in the on-premise system.

It comes by default in Azure SQL Server.

In Azure SQL Server, the Point in time restoration feature defaults to 7 days but can be extended up to 35 days.

If you want to make long-term backups, you need to activate the Long Term retention (LTR) feature.

With LTR, you can host your database backups on Azure Blob Storage for up to 10 years, which is determined according to the legal duration of many applications.

In addition to automatic backup, you can import Azure SQL Database as a bacpac or dacpac file to Azure Blob Storage and import it from there to the on-premise side.

If you make a backup like this, you can also host it in an on-premise environment.

Recovery Model

The default recovery model of Azure SQL Database is FULL, and you cannot make the recovery model changes you made in the on-premise environment on Azure SQL Database.

You can use the query below to see the recovery model information of the Azure SQL database.

Result:

SQL Server Agent

Azure SQL Database does not have SQL Server Agent.

SQL Server Agent jobs that you perform and use to perform scheduled jobs via SQL Agent in On-Premise environments are not available in Azure SQL Server.

But don’t worry, the temporary solutions below will work for you.

-You can use Azure Automation.

-You can use Elastic Database Jobs.

-You can run the powershell script you prepared with the Windows scheduler.

Change Data Capture

Change Data Capture (CDC) is an On-Premise SQL Server feature that captures Insert, Update and delete operations performed on a table and allows you to transfer changes to the data to the system of your choice.

SQL Server Agent is required for CDC to work.

For this reason, it cannot be used in Azure SQL Server. But you can also do CDC work with SSIS and Azure Data Factory.

Auditing

You will need to access operating system resources in order to perform audit operations (event log, SQL Server Log, SQL Server Trace, System Health Extended events).

You cannot perform a C2 audit because Azure SQL Server is a Paas product and limits access to the resources needed.

However, advanced Audit and Threat Detection features are available on Azure SQL Server.

Mirroring

You cannot mirror two Azure SQL Server databases.

However, you can create a Mirror server for Azure SQL Server and use that server for reading purposes.

Table Partitioning

You cannot use the table partitioning feature on the Azure SQL Server side, which you can use to gain performance by moving large tables into different filegroups on the On-Premise side.

In fact, Azure SQL Server allows you to split tables using partition schema and partition function.

So, you can use table partitioning in Azure SQL Server, but since Azure SQL Server is a Paas service, it will want to host the database files on a single filegroup.

In short, you can do table partitioning on Azure SQL Server, but you cannot get the performance you get by applying it in an on-premise environment.

However, if you do it on a single filegroup, you can get a performance increase.

Replication

You cannot use the replication operations you use on the on-premise side between two Azure SQL Server databases.

However, you can configure it so that one side is on-premise and the other side is Azure SQL Server.

However, this has its limitations and Azure SQL Server will only support push subscription.

You can only use one-way replication, not double-sided replication, which you can use on the On-Premise side.

Multi-Part Names

You cannot use dbaname.schemaname.tablename, which you use on the On-Premise side, on the Azure SQL Server side.

This type of usage can only be used with the tempdb feature on the Azure SQL Server side.

You can experience usability by creating a temp table.

You can use elastic query to access tables in different databases from an Azure SQL Server.

Otherwise, you cannot continue your operations with a usage such as three names (dbaname.schemaname.tablename) or four names (server.dbaname.schemaname.tablename).

Features Not Supported by Azure SQL Server

Here are some features that are not supported by Azure SQL Server or Azure SQL Database.

SQL Server Browser: SQL Server Browser is a Windows service. It is a service that directs incoming clients when connecting to SQL Server. Azure SQL Server only supports port 1433. For this reason, SQL Server Browser is not supported.

Filestream: Azure SQL Server does not support Filestream or file hosting because it is a Paas service.

SQL CLR: Although it was initially supported by Azure SQL Server, it was removed due to security issues.

Resource Governor: This on-prem feature, which allows you to limit resources such as CPU, Memory, IO, is not needed on the Azure SQL Server side. Azure SQL Server is used with different service layers for different applications.

Global Temporary Table

Log Shipping

SQL Trace and SQL Profiler

Trace Flags

System Stored Procedure

USE