On-Premises vs Cloud: Which is the Best Choice for Hosting SQL Server Databases?

When it comes to choosing between hosting SQL Server databases on-premises, in Amazon Web Services (AWS) SQL Server RDS, or in Azure SQL, there are several factors that a Database Administrator (DBA) must consider. When evaluating cost savings, be sure to consider the underlying design quality of your database(s). If a database is poorly designed, it can be surprisingly more expensive to keep in the cloud than you anticipated. In this technical blog post, we will explore the pros and cons of each option from a DBA perspective.

On-Premises SQL Server

On-Premises SQL Server Pros

1. Full Control: With an on-premises SQL Server, DBAs have complete control over the hardware and software used to run the database. This level of control enables them to optimize the database environment for the specific needs of their organization. It also enables DBAs to respond more quickly and mitigate a wide range of operational challenges. Tasks such as automated backups can easily be set up and monitored.
2. Lower Latency: Hosting SQL Server on-premises eliminates the latency that is associated with cloud-based solutions. This is particularly important for applications that require low latency, such as real-time applications.
3. Customization: An on-premises SQL Server provides more customization options than a cloud-based solution. DBAs can implement unique configurations and solutions tailored to the specific needs of an application.

On-Premises SQL Server Cons

1. High Cost: Hosting SQL Server is expensive whether On-Prem or the Cloud. The cost of hardware, software licenses, maintenance, and upgrades can quickly add up. In addition, DBAs must spend time maintaining the infrastructure.
2. Disaster Recovery: Implementing a High Availability plan for SQL Server can be complex and costly. It requires redundancy and additional resources to ensure continual data availability.
3. Maintenance: SQL Server requires ongoing maintenance, including updates, patches, and backups. This can be time-consuming and costly for DBAs, but much of it can be easily automated.

AWS SQL Server RDS

AWS SQL Server RDS Pros

1. Low Cost: AWS SQL Server RDS is a cost-effective solution for hosting SQL Server databases. The pricing model is pay-as-you-go, which means that you only pay for the resources you use.
2. Scalability: AWS SQL Server RDS is highly scalable. As your database grows, you can easily add more resources to support it.
3. Automated Backups: AWS SQL Server RDS provides automated backups, which eliminates the need for DBAs to perform manual backups. This ensures that your data is always protected.
4. Flexible Configuration: AWS SQL Server RDS provides flexible configuration options, including instance size, storage type, and backup retention period.

AWS SQL Server RDS Cons

1. Limited Control: With AWS SQL Server RDS, DBAs have limited control over the infrastructure. This can be a challenge for organizations that require a high level of customization.
2. Security: While AWS SQL Server RDS is generally secure, there are still some security concerns. For example, AWS requires users to store their database credentials in a web-based console, which can be vulnerable to attacks.
3. Latency: AWS SQL Server RDS can suffer from latency issues, particularly for applications that require low latency.
4. Vendor Lock-In: AWS SQL Server RDS is a proprietary service, which means that it can be difficult to migrate away from if needed.

Azure SQL

Azure SQL Pros

1. Scalability: Azure SQL is highly scalable. As your database grows, or if the database is suffering from poor design, you can easily add more resources to support it.
2. Automated Backups: Azure SQL provides automated backups, which eliminates the need for DBAs to perform manual backups. This ensures that your data is always protected.
3. Integration: Azure SQL integrates seamlessly with other paid Microsoft Azure services, making it an ideal solution for organizations that are paying for other Microsoft services.
4. Low Latency: Azure SQL provides low latency for Azure based applications that require it. This is because Azure SQL can be configured to run in the same region as the application, reducing latency. Additionally, enhanced performance options are available with upcharge.
5. High Availability: Azure SQL provides high availability options, including geo-replication, failover groups, and automatic failover. Implementing a disaster recovery plan for SQL Server can be complex and costly. It requires redundancy and additional resources to ensure continual data availability. Costs are simply added on to your monthly subscription.
6. Low Cost: Like AWS SQL Server RDS, Azure SQL is a cost-effective solution for small shops wishing to host well designed SQL Server databases. The pricing model is pay-as-you-go, which means that you only pay for the resources you use or add-on.

Azure SQL Cons

1. Limited Control: With Azure SQL, DBAs have limited control over the infrastructure. This can be a challenge for organizations that require a high level of customization. Especially for those organizations that require answers as to “Why” something happened. Many times these reasons are buried where only the hosting providers can see.
2. Latency: Azure SQL can suffer from latency issues, particularly for non-Azure based applications that require low latency.
3. Cost: Azure SQL can be more expensive than AWS SQL Server RDS. The pricing model is based on the amount of resources used, which can quickly add up for large or poorly designed databases.
4. Vendor Lock-In: Azure SQL is a proprietary service, which means that it can be difficult to migrate away from if needed. 
5. Migration: Migrating to Azure SQL can be challenging, particularly for organizations that are used to running less than optimally designed databases in SQL Server on-premises.

Conclusion

From a DBA perspective, the decision to host SQL Server databases on-premises, in AWS SQL Server RDS, or in Azure SQL depends on several factors. Each option has its own set of pros and cons. On-premises SQL Server provides full control and customization but can be expensive and challenging to scale. AWS SQL Server RDS and Azure SQL are both cost-effective and highly scalable, but DBAs have limited control over the infrastructure, and the databases must be very well designed. Ultimately, the choice depends on the specific needs of the organization. It is recommended that DBAs evaluate the requirements and the current state of their databases before making a decision.

Check out our SQL Server Virtual-DBA Services or contact us for any questions you may have.