Pros and Cons of Running SQL Server On-premise vs Azure Cloud

SQLServerF1

Cloud solutions has been growing in popularity of recent with new offerings and more features added in different offerings and reduce or eliminating many traditional and operational overheads of maintaining hardware, servers, OS, Network, SQL Servers, patching, backups, High availability, Disaster recovery, etc. Depending on the type of offering choose, the cost will be impacted and features available. At present, SQL Server instances or databases can be hosted on different environments which include running SQL Server on physical hardware on-premise in client(our own) data center or running SQL Server on virtual machine guest system running on-premise or running SQL Server on Azure VMs(IaaS) or SQL Azure database as service (Paas).

There are many advantages as well as disadvantages of running SQL Server on above mentioned environments, with each one with their own pros and cons which are listed below.
Pros of running SQL Server on Azure
– Running SQL Server on Azure VMs will reduce the cost of hardware maintenance as it will be taken care by Microsoft, thus reduces the load on the data centers and reduces overall maintenance and costs.
– Depending on the type of Azure offering chooses, different administrative or operational costs are reduced. For example, using SQL Azure database as service PaaS will reduce the administrative tasks like Setting up and maintenance of hardware, Installation of SQL Server, maintenance tasks like patching, backups, High availability, etc are taken care by Microsoft. In Infrastructure as service offering IaaS, hardware costs are reduced.
– One of the important advantages of Azure VM and SQL Azure database as service are the flexibility of scaling up or down based on the requirement. For example, it is a common requirement for a test server with similar configuration of production specifications, which can be easily setup on Azure and once testing is complete, we can release or shutdown the Azure database, thus reduces the cost of maintaining the server outside of testing time, which can be significant. Another example, where we may need to scale up the performance of the server like shopping portal during thanks giving day or during festivals when there is high demand for the portal, Azure allows to quickly upscale the performance.

Cons of running SQL Server in Azure
– Although Azure provides cost effective cloud solution, but has its own disadvantages too. On SQL Azure database as service, there is not much control on the features or operations like patching, backups, etc.
– Also, another major concern about running SQL Server on Azure is security. Applications which store critical client data which comes under PII or finance related data, many organizations are not comfortable storing them on third party data centers where they do not have much control.
– The SQL Server instance databases main purpose is to store the data and allow it to be retrieved when required, so there is lot of dependency on the application and other services. So, it is important to consider where other dependent or related applications are running. For example, if applications and other services are running on-premise and SQL database on azure can introduce additional latency and any network issues can cause frustration to users, so any critical applications which are running on-premise may take advantage of SQL Server running on-premise too.
– Also, it is possible that some times the performance of SQL Server on azure is not predictable or consistent, because the SQL Server will be sharing the same hardware resources with other SQL Server instances or other applications, so heavy load from all servers or applications can lead to some resource saturation, thus causing bottlenecks at times, so performance is not always predictable on the azure.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Differences Between SQL Azure Database and SQL Server in Azure VM

SQLServerF1

Microsoft is making huge investments in cloud technology and is betting big for future growth in this area. Microsoft has started providing cloud services for various products, applications, Operating System or hardware support. Based on the type of service, Microsoft has made available different offerings which include Infrastructure as a Service (IaaS), Platform as a Service (PaaS), and Software as a Service (SaaS). Microsoft cloud support many applications or products out of which SQL Server is one of the Microsoft product which is supported on cloud by Microsoft. There are two different kind of offerings provided by Microsoft for SQL Server, which are Infrastructure as a Service (IaaS), Platform as a Service (PaaS).

Among these two services Infrastructure as a Service (IaaS), Platform as a Service (PaaS) offered by Microsoft, we a DBA or management, we need to understand what best suits our requirement and based on that we can choose the offering. It is important to understand the differences between these two offerings available for SQL Server, so that we can make an informed decision. Each offering for SQL Server from Microsoft has its own advantages and disadvantages interms if features, cost, High Availability, Disaster recovery, which operations tasks are taken care by Microsoft, etc. Below are some of the differences between SQL Azure SQL Database (PaaS) and SQL Server in Azure VM (IaaS).

SQL Azure database as service is best suited for new applications designed or optimized for cloud solutions or which depend on other cloud technologies, so that they can be used together. Developers building software-as-a-service (SaaS) applications can use Azure SQL Database to provide flexibility to support both explosive growth and profitable business models. On the other hand with Infrastructure as a Service (IaaS) is for applications which are already existing ones and cannot be changed easily and need to be migrated on to cloud without much changes to be made. Applications which are dependent on other on-premise resources are best suited for Infrastructure as a Service (IaaS) compared to SQL Azure database as service.

SQL Azure database as service does not support all features available in SQL Server and has many limitations for each feature, many of the features cannot be controlled by DBAs or developers and there is not much control for DBAs for operational tasks like patching, backups, HA or DR. Infrastructure as a Service (IaaS) is just like on-premise SQL Server instance on which DBAss or developers can make configuration changes, performs backups/restores, use required technologies for HA and DR purposes, etc. There are some limitations with SQL Azure database as service like max database size supported as of 2015 is only 500 GB, but this limitation does not apply for Infrastructure as a Service (IaaS). Another limitation include, SQL Azure database as service does not allow resources to be accessed from Azure to on-premise. Infrastructure as a Service (IaaS) does not have any such limitations.

In Infrastructure as a Service (IaaS), hardware, its maintenance costs are eliminated as it is taken care by Microsoft. SQL Azure database as service also eliminates the costs mentioned before, in addition to that, in SQL Azure database as service also eliminates maintenance and administration efforts and costs of patching, backups, HA, DR, etc. in SQL Azure database as service, there are different services available to choose for High Availability and disaster recovery where Point in Time Restore, Geo-Restore, and Geo-Replication to increase can be chosen to increase business continuity. In Infrastructure as a Service (IaaS), the High availability and disaster recovery is to be taken care by DBAs and we can choose any technology of our choice and we are responsible for administering, monitoring and fixing any problems.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings