Pros and Cons of Running SQL Server On-premise vs Azure Cloud
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
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings