SQL server databases are one of the most popular and widely used relational databases in the enterprise world. Its use ranges from critical Line of Business Applications (LOB) to Data Warehousing. Some of the most popular SQL databases are MSSQL, MySQL and PostgreSQL.
In the first entry in this series, we introduced some of the challenges to running a DB in the cloud. In this post, we will look at some of the major parameters to consider when running SQL in the cloud and how NetApp Cloud Volumes ONTAP can ensure you get the most of your SQL deployment. The next post of this series will cover Oracle database in the cloud.
SQL: Relational Challenges
Structured Query Language (or SQL) is the basis for developing and handling relational database management systems (RDBMS). SQL or RDBMS databases can handle complex business requirements due to their advanced analytical queries and by allowing users to write mission-critical procedural codes to handle complex business functions. As such, SQL databases are at the core of any business for storage and management of business-critical data. In this section, we will explore different challenges faced with using SQL in the cloud.
Deployment Model, Licensing, and Cost Savings
Using SQL in the cloud means one of two options: running a DBaaS or an SQL instance using public cloud resources.
A DBaaS (Database-as-a-Service) is a PaaS (Platform-as-a-Service) model where the cloud provider manages the database and offers the service in a multi-tenant model. This model ensures users can focus on important tasks like the application development lifecycle rather than worry about repetitive administrative tasks like scaling, patch management, provisioning, and monitoring. Amazon RDS for SQL and Azure SQL Database are two very good examples of this model. Another big advantage of this model is that the licensing fees are baked into the offering, so you don’t have to purchase or manage SQL licenses separately. However, the DBaaS model has drawbacks. For example, Amazon RDS for Microsoft SQL Server has limitations such as a maximum storage size of 16 TiB, a maximum of 30 databases for each DB instance, and no support for running SSRS.
If you want complete control over your SQL database and to avoid limitations such as master database nomenclature, SQL edition, user rights, and database size, deploying your own SQL DB instance on IaaS blocks would be the right model for you. This sort of cloud-based database deployment gives you the same control and feel of an on-premises deployment but at the scale and convenience of the public cloud. In this model, you pay for what you use and when you use it. Having said that, you can also use your own licenses in the BYOL model to enable great cost benefits. For example, use of Microsoft SQL Volume Licenses with Software Assurance on Azure virtual machines provides you with cost savings of almost 30% and also gives you a free, passive, secondary replica for high availability. Furthermore, you can save more money by shutting down the virtual machines hosting the SQL instances if your workloads are not running continuously. Another great feature is the use of reserved instances, provided by both AWS and Azure, where a billing credit against your managed SQL instances can shave off about 69% and 33% respectively off your monthly bill. You can save further on Azure for up to 30% using the SQL Hybrid Benefit.
SQL databases are very resource-intensive and may need to scale exponentially. In a traditional on-premises environment, this equates to more compute and storage hardware, which in turn translates to more OPEX and underutilized resources. The public cloud’s vast infrastructure ensures that you can automatically scale up and scale down resources without significant rise in expenditure. To make this easier, the public cloud providers offer automatic scaling technologies that can be used to scale resources horizontally or vertically.
Azure, for example, provides auto-scaling technologies in Elastic Pools and Database Sharding via Elastic Tools. This allows you to assign a shared pool of resources for several database instances and scale out or scale up resources as demand increases. AWS also allows vertical and horizontal scaling of RDS instances via the use of push-button compute scaling, storage increase, and adding read-replicas.
SLA-Backed Availability and Disaster Recovery
High availability and disaster recovery are empirical to business continuity. Public cloud providers like Azure and AWS have cloud resources in various geographically-dispersed regions all over the world. Each region may house one or more availability zones (AZ), which are isolated physical locations with independent power, cooling, and networking. This allows users to have SQL DB instances replicated to different Availability Zones or Regions to protect against failures or natural disaster.
Along with providing an SLA of 99.99% and RPO of zero, Azure offers single zone or multiple zone redundancy on its Premium Tier Database, which makes use of quorum-sets and Always On technology. This ensures that the SQL database is more resilient to even the most catastrophic datacenter outages. Amazon RDS also provides Multi-AZ deployments where it utilizes Amazon’s failover technologies for PostgreSQL, MySQL, and SQL mirroring technologies for Microsoft SQL Server. This ensures that during a catastrophic outage, database workloads can fail over to the replicas in as little as 60-120 seconds.
Data Protection, Backup and Recovery: How to Backup SQL Databases
There are several options for how to backup SQL database automatically. One of the major advantages of the DBaaS model, is the built-in feature for automated SQL database backups based on a schedule and retention period. Cloud-based databases can rely on application-consistent snapshots to form a backup that is up to date and reliable.
Amazon RDS does not charge any additional fee for backup storage up to 100% for all the DB instances in that region. The automated backup can be scheduled, and the retention period can be specified between 1 and 35 days. Amazon RDS creates a storage volume snapshot of the DB instance and provides the provision to store the backup data in a different region for DR purposes. Azure SQL takes automated backups and stores them in geo-redundant storage at no additional charge. The retention period is based on the service tier and the schedule can be modified to fit into recovery objectives.
Other ways to achieve SQL cloud backup for a cloud-base SQL database would be to use SQL native backup and create snapshots of the underlying storage. It is also important to ensure that the backup is application-consistent especially when the database instance is stored on IaaS instances.
Database Migration and Data Sync
Another important consideration while moving your databases to the public cloud would be the ease of database migration to cloud storage with the help of the cloud provider tools. Both Azure and AWS offers specialized database migration tools, namely AWS Database Migration Service and, for database migration to Azure, Azure Database Migration Service, which offer automated migrations with minimal downtime. Native backup/restore options like exporting a .bak file and using SQL replication technologies can also be employed for the SQL database migration process.
Using Cloud Volumes ONTAP for SQL Cloud Databases
NetApp Cloud Volumes ONTAP is the enterprise-class solution for managing database in the cloud, filling the gap between your cloud-based SQL database capabilities and the public cloud resources it runs on.
Cloud Volumes ONTAP gives users capabilities to meet all the challenges of SQL databases in the cloud, including ensuring business continuity with zero-RPO, <60-second RTO high availability failover and failback processes, data protection with cost-effective, consistent, and application-aware snapshots, easy and secure data replication, migration, and synchronization with SnapMirror® data replication, hybrid and multicloud support, and full integrability with RESTful API calls. FlexClone® data cloning technology allows you to create zero-capacity database clones that you can create in zero time to speed up dev/test processes and only for the cost of the changed data. You also get automation, monitoring, and scheduling controls in Cloud Volume ONTAP’s Cloud Manager.
With Cloud Volumes ONTAP, which is built on Azure storage or AWS storage, you get full control over how you provision storage. The main benefit is cost-cutting storage efficiencies such as data tiering between object and block storage, data deduplication and compression, and thin provisioning your allocated storage. Combined, these features can reduce the costs of storing your databases by as much as 70%.
In this SQL entry in our cloud-based database series, we’ve seen that although the DBaaS model for using SQL in the cloud can result in significant cost savings, it is important to consider the limitations before taking the jump. NetApp Cloud Volumes ensures that you can have a reliable and highly available SQL solution with very low TCO. In the first entry in this series, we introduced some of the challenges to running a DB in the cloud.
The next post of this series will cover Oracle database in the cloud.