/Insights

Benefits of Hosting SQL Server On-Premises vs. On Azure

Some of us still remember those very first servers that were housed in massive data centers. Today’s on-premises SQL Servers have evolved with the times — they are now more functional, space conscious and efficient.

In recent years, Microsoft has been on the front lines of moving its computing and storage services toward cloud-based enterprises. This transition now includes cloud-based servers. One of its most recent introductions has been Azure SQL. In this blog, we will explore the differences between on-premises SQL Servers and Microsoft Azure SQL.

On-Prem vs. Cloud
On-Prem vs. Cloud

How SQL Azure and SQL Servers are Similar

Because the Azure SQL cloud-based service has been designed and built along the lines of SQL Servers, it shares some similarities with these on-premises entities. These common elements include compatibility and functionality.

Differences Between SQL Azure and SQL Servers

These servers also have many differences. The most fundamental difference is that SQL Azure is a multi-tenant resource. This means that a single database may host the databases of other customers and businesses, not just yours. As a result, many of the differences between these two platforms stem from this reality.

Other SQL Azure differences include architecture, data definition language (including CLR, table partitioning and extended stored procedures as well as clustered indexes and data types) and differences in data manipulation language. In addition, Azure SQL does not support certain CRUD commands, has deployment differences and has some unsupported features that may be important to you.

SQL Server Architecture Versus Azure SQL

Azure SQL has been designed with simplified application authentication and communication in mind. Unlike the on-premises SQL Server, which requires communication between the application over a LAN using Tabular Data Stream (TDS) protocol over TCP/IP or HTTP, Azure SQL uses only TCP IP protocol for communication. This explicit communication reaches the Azure Gateway, passing through your firewall via an internet connection. At Azure’s Gateway, SQL Azure’s firewall allows only the IP addresses that the SQL Azure’s customer has specifically defined for access, making the connection to the backend data node using the Gateway as a proxy. Because of this, communications occur through SSL and enter only through port 1433.

SQL Azure Data Definition Languages (DDL) Differences: CLR, Extended Storage, Clustered Indexes, XML

Because of SQL Azure’s multi-tenant infrastructure, it does not support CLR. This difference has been set up to protect the users from unintentionally (or intentionally) using other customers’ CLR articles. SQL Azure does not support either extended stored procedures (stored in DLLs) or table partitioning. In addition, if you decide that SQL Azure cloud-based server service is right for you, you’ll need to plan on building clustered indexes for any tables that currently don’t have them. In SQL Azure, clustered indexes are necessary for all tables. If a clustered index does not exist, the INSERT operations will fail. SQL Azure also does not support XML indexing or typed XML.

Failover Clustering, Database Mirroring and DML

SQL Azure’s high availability provisions means that its database is replicated two different times in two different nodes. If the primary node should fail, one of the two replicas would take over. Differences in DML used to perform basic CRUD (create, update, delete) operations are also different. SQL Azure does not support:

  • REMOTE
  • PAGLOCK, MAXDOP is always 1
  • Full-text searches using “CONTAINS” and “FREETEXT”
  • BULK INSERT
  • ROW set functions like FREETEXTTABLE, OPENQUERY, OPENXML or CONTAINSTABLE

Deployment Architecture and Unsupported Features

Unlike the physical SQL Server, the simplified, logical SQL Azure has three entities: server, database and subscription. It does not support certain features like master data services, data auditing, resource governor, data compression, FileStream, PolyBase, semantic search or trace flags — to name a few.

Why Choose Azure SQL?

With its differences and limitations taken into consideration, Microsoft SQL Azure is still a cloud-based server subscription program that looks toward the future. This means that it is ideal if you are planning for your business infrastructure to be able to adapt and evolve with the times. Administration and management are hassle-free, and in addition, it has a high availability, features economical scalability and uses read-access geo-redundant (RA-GRS) for geo-redundancy.


Trusted Tech Team: A Microsoft Gold Partner

Trusted Tech Team knows the ins and outs of SQL Server and SQL Azure. Do you need more information about which platform is right for you? Contact one of our Licensing Engineers today for a free consultation.

Subscribe to the Trusted Tech Team Blog

Get the latest posts delivered right to your inbox

Trusted Tech Team

Trusted Tech Team

The Source

Read More