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

 

Best Practices to run SQL Server on Amazon RDS

SQLServerF1

Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the cloud. It provides cost-efficient, resizeable capacity for an industry-standard relational database and manages common database administration tasks. Although Amazon RDS is great for many applications to store its data, but has many limitations where many features cannot be used due to restricted permissions at file system level and on registry and OS level.

Although running SQL Server on Amazon reduces cost to great affect, but has its own limitations too and need to review them carefully before moving data to Amazon RDS.

– Always Better to choose Multi-Availability zone deployment to have high availability, as we never know when the instance may go down, so to avoid or reduce the amount of down time, having high availability using Multi-Availability zone deployment will be helpful.
– Plan thoroughly in advance about the CPU, Memory and Storage requirements, changing these later may be complex. Storage cannot be changed once instance is created.
– Choose the proper server level settings,as they can be modified using Parameter Group and can be some what difficult to change and may be required instance to be restarted for few settings changes to apply.
– Patch levels are often controlled by Amazon RDS and may have some known issues due to older version of patches available and can impact your application due to known issues.
– Always choose the database files with proper initial size and auto-growth at higher sizes to avoid growing of files during application usage, this reducing the time and improving performance.
– If you are using Multi-Availability zone deployment, note the amount of time taken for the synchronization, as this is synchronous and thus the time for sync can have significant impact on you data access from application.

There may be other best practices, and following them will help running your databases on Amazon RDS seemless and provide cost effective solution replacing on-premise SQL Server databases. Not considering these limitations can significantly cause timeouts or performance issues and can make you feel frustrated for moving to Amazon RDS. Proper planning and understanding if your application works well on Amazon RDS will decide how the application will behave in future. Also, as time the amount of data can increase and can have significant impact on performance, so it is important to consider server configuration for future data growth and user usage.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2012
SQL Server 2008 R2

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

 

DBA Tasks Offloaded After Moving to AmazonRDS or SQLAzure

SQLServerF1

Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the cloud. It provides cost-efficient, resizeable capacity for an industry-standard relational database and manages common database administration tasks. Although Amazon RDS is great for many applications to store its data, but has many limitations where many features cannot be used due to restricted permissions at file system level and on registry and OS level.

For On-Premise SQL Server instances, DBAs and System Admins are responsible for many tasks which may be day to day or some scheduled tasks like patching, maintenance etc. Amazon RDS or SQL Azure can offload many such tasks which SQL Server DBA or System Admin has to take care and thus can reduce the load on the DBAs on large environments with many servers and applications. Instead of spending time on repeated things, they can focus more on other important DBA activities like performance turning, health checks, etc.

Some of the important activities or tasks offloaded from DBAs or System Admins are listed below. These are not the only ones, but there are others too.
Patching – Patching of SQL Server instances or Operating System, drivers, etc have been one of the core responsibilities of the DBAs or System Administrators and often are repeatable and does not require smart work instead requires some hard work on working during off hours to reduce the impact on the users using the application. With Amazon RDS, these patching is taken care by Amazon team and we only have to specify the window on when the patching needs to take place. However this limits the control on what patches can be applied, as every patch we want will not be applied, as they need to certify which patch needs to be applied and tested to work smoothly.

Upgrade to Higher Version – Upgrading on-premise SQL Server is considered one of the major project and requires lot of planning and time to implement and test. With Amazon RDS, the implementation of upgrade and any issues are taken care by Amazon and we can just focus on testing our application and fixing the appliation compatibility issues.

Backups – Backups are taken care by Amazon and we just need to specify the time when backups can be performed and how many days backups are required to be retained. Point to note is that the number of backups to be retained is directly proportional to the increase in storage price to keep the backups. By default recovery model used in SIMPLE and cannot be changed to simple, instead we need to choose retention period as 0, meaning we do not need any backups. Thus, for databases which need backups will have full and transaction log backups and mostly we can recover our data to as early as 5 minutes before the crash.

High Availability – Amazon provides support for High Availability and it is important to choose the high availability feature, as this is third party vendor and on data centre which we have no control or details about, so we never know if there will be any issues or outages. For high availability, RDS can automatically setup a database mirroring secondary on another Availability Zone often in another Amazon data centre in the same region.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2012
SQL Server 2008 R2
SQL Server 2014

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

 

Limitations of Amazon RDS SQL Server 2012

SQLServerF1

Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the cloud. It provides cost-efficient, resizeable capacity for an industry-standard relational database and manages common database administration tasks. Although Amazon RDS is great for many applications to store its data, but has many limitations where many features cannot be used due to restricted permissions at file system level and on registry and OS level. Some of the important restricted features are mentioned below.

– Only SQL Authentication Logins are allowed. Windows authentication is not supported, so applications are to be changed to only use SQL Server authentication.
– Amazon RDS only provides support for core database engine of SQL Server, but does not include SSRS, SSAS, SSIS, etc. If application requires these services, then they can be implemented on on premise server and can access data from Amazon RDS.
– Maintenance plans are not supported, so we would need to use SQL Scripts to implement the maintenance tasks like backups, Index and statistics maintenance, Integrity checks, etc. There are already many of these scripts available like Ola Hellengren scripts. Using scripts also provides flexibility of dealing with these maintenance tasks.

– Linked Server access is limited and only can be configure and used from Amazon RDS to outside SQL Server instances over internet or other Amazon RDS instances.
– SQL Agent role is limited, which causes issues where one user with SQL Agent role cannot see jobs created by another user with SQL Agent role. So workaround is to use only one account for job management.
– In Amazon RDS, during the initial creation of instance, we choose the storage and this storage cannot be changed later. There are no workarounds and will need to create new instance with required storage design and then have to migrate data from old instance to new instance.
– Replication can be setup, but is is limited. We can only configure Amazon RDS instance as a subscriber. We cannot use Amazon RDS instance as publisher/distributor. We cannot use pull subscription too.

Some of the other features which are not supported by Amazon RDS SQL Server 2012 or lower include below.

Database Mail
Service Broker
Database Log Shipping
Change Data Capture (CDC) – Consider using Change Tracking as an alternative to CDC.
Additional T-SQL endpoints
Performance Data Collector
Distribution Transaction Coordinator (MSDTC)
WCF Data Services
FILESTREAM support
Policy-Based Management
SQL Server Audit
BULK INSERT and OPENROWSET(BULK…) features. These must be run from client-based server storage.
Data Quality Services
Instant file initialization
Always On (2012 Enterprise Edition)
File tables
Server level triggers

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

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