Written by Chandra sekhar Pathivada, Sr. Database Specialist Solutions Architect, AWS and Jagdish Mirani, Sr. Product Marketing Manager, AWS
Microsoft SQL Server is a relational database service that supports transactional processing and analytics. With a 30-plus year history, SQL Server has been a mainstay at many organizations. Now, however, open-source technologies like PostgreSQL, have become increasingly popular for customers. More and more customers are migrating to them from overpriced, restrictive commercial databases that lock them in. With an increasing array of choices, the same organizations are looking for lower cost options, including low-cost, cloud-native, fully managed database services like Amazon Aurora PostgreSQL-Compatible Edition, as this lowers total cost of ownership while providing the desired performance.
In addition to an immediate cost advantage, Aurora PostgreSQL provides differentiated features, and fosters agility and innovation by offloading the heavy lifting that typically accompanies the management and maintenance of databases.
Before going deeper into Aurora PostgreSQL, let’s take a quick look at the choices SQL Server customers have with Amazon Web Services (AWS):
- Lift and shift SQL Server to run on Amazon Elastic Compute Cloud (Amazon EC2): this option provides the benefit of offloading the maintenance of hardware to AWS. However, maintenance of SQL Server is still left to the customer.
- Migrate to Amazon Relational Database Service (Amazon RDS) Custom for SQL Server: this option migrates customers to a partially managed environment. Customers still have control over many aspects of the configuration. This is often the best option when the database is supporting the unique configuration requirements of packaged applications like Microsoft Dynamics or SharePoint.
- Migrate to Amazon RDS for SQL Server: this option migrates customers to a fully managed SQL Server database, relieving them from managing both the hardware and the software.
- Migrate to Amazon Aurora PostgreSQL-Compatible Edition with minimal changes to your applications: this migration involves a change to your database from SQL Server to PostgreSQL. However, you can do this without making any major changes to your application, with Babelfish for Aurora PostgreSQL, an open-source component which enables Aurora to understand commands from applications written for Microsoft SQL Server.
- Full migration of both, your applications and your database to Aurora PostgreSQL: this option adds an additional effort related to making changes to your applications so that they use Aurora PostgreSQL as the target database.
Lift and shift (option #1) is the easiest, but it doesn’t deliver on the promise of a fully managed database, with the automation of routine database maintenance tasks. Migrating to Amazon RDS (options #2 and #3) have the benefit of being relatively simple homogenous migrations, and are good options for those who want to remain on SQL Server with the added benefit of a fully managed database.
This article is focused on option #4, migrating to Aurora PostgreSQL. We highlight the benefits of switching to an open-source database designed for unparalleled high performance and availability at global scale. With the use of Babelfish, Aurora PostgreSQL now understands dialect that Microsoft SQL Server uses and supports the same communications protocol, so your apps that were originally written for SQL Server can now work with Aurora with minimal or no code changes. As a result, the effort required to modify and move applications running on SQL Server to Aurora is minimal or eliminated, leading to faster, lower-risk, and more cost-effective migrations. An IDC technology spotlight report describes how cloud database migrations are on a steady path, with 63% of survey respondents saying they are moving to the cloud. The report highlights Babelfish for enabling a smooth migration as well as providing a much quicker time to value in migration from SQL Server to Aurora PostgreSQL.
Why Migrate to Amazon Aurora PostgreSQL
Aurora is a modern relational database service that is designed for unparalleled high performance and availability at global scale with full MySQL and PostgreSQL compatibility. Aurora offers a range of developer tools for building serverless and machine learning-driven applications with the security, availability, and reliability of commercial-grade databases, at 1/10th the cost.
Here are several advantages that differentiate Aurora PostgreSQL from commercial database providers.
- Cost savings: As a managed service with a pay per use cost model, Aurora reduces costs related to the maintenance and operations of your database hardware and software. Customers can choose On-Demand Instances and pay for the database by the hour with no long-term commitments or upfront fees, or choose Reserved Instances for additional savings. Alternatively, Amazon Aurora Serverless v2 automatically starts up, shuts down, and scales capacity up or down based on your application's needs and you pay only for capacity consumed.
- Extensibility: PostgreSQL is highly extensible with the power of extensions. Using extensions, customers can add new database features including the extensions developed by AWS to integrate with other services like AWS Simple Storage Service (S3). For example, using the AWS_S3 extension, you can copy data from Aurora PostgreSQL to a S3 bucket directly by using a SQL command. As of today, Aurora PostgreSQL supports over 70 PostgreSQL extensions.
- No checkpoints and full-page writes: Amazon Aurora uses a log-structured distributed storage system and doesn’t need copying of pages to the buffer for writes. This results in an increase in performance compared to commercial relational database engines which are heavily based on checkpoints.
- Efficient, shared storage architecture: Aurora uses shared storage where all writer and reader instances connect to same distributed storage using writer and reader endpoints. The reader endpoints provide load-balancing support for read-only connections to the database cluster. A reader instance can be rapidly created directly from the shared storage without replication.
- Storage Auto Scaling: Amazon Aurora PostgreSQL is highly scalable; the size of your database volume automatically increases as your storage needs grow. Your volume expands in increments of 10 GB up to a maximum of 128 TB. You don't need to provision excess storage for your database to handle future growth. In addition, it scales out by adding up to 15 read replicas. Even though Aurora cluster volume can scale up in size to many tebibytes, you are only charged for the space that you use in the volume. Auto Scaling of read replicas enables the Aurora database cluster to handle sudden increases in connectivity or workload. When the connectivity or workload decreases, Aurora Auto Scaling removes unnecessary read replicas so you don't end up paying for unused provisioned database instances.
- High availability, durability, and disaster recovery: Each 10 GB chunk of your database volume is replicated six ways across three Availability Zones with Aurora. Aurora storage is fault-tolerant, transparently handling the loss of up to two copies of data without affecting database write availability and up to three copies without affecting read availability. You don’t need to schedule any backups, Aurora takes continuous backups to a S3 bucket without any performance impact during these backups. High availability can be achieved by placing a reader instance on a different availability zone. In case the primary instance fails, a reader instance in a different availability zone can be promoted to serve as the new primary. You can even extend the availability across regions using Aurora Global Database which uses storage level replication to replicate the data between the regions with up to 200K writes/second and low replica lag—typically less than 1 second under heavy workloads. For applications with critical Recovery Point Objective (RPO) requirements, you define the maximum RPO that you want to allow. If RPO lag in all secondary regions exceeds the limit, Aurora pauses writes until at least one region catches up.
- Fast database cloning: Easily create copies of an Aurora cluster to build development, staging, analytics, or testing environments in minutes, as cloning uses a copy-on-write protocol which is quick and cost-effective.
- Machine learning: Aurora offers machine learning capabilities directly from the database enabling you to add ML-based predictions to your applications via the familiar SQL programming language. With a simple, optimized, and secure integration between Aurora and AWS machine learning services, you have access to a wide selection of ML algorithms without having to build custom integrations or move data around.
- Accelerate migrations using Babelfish: Using Babelfish for Aurora PostgreSQL you can migrate your applications that use Microsoft SQL Server to a Babelfish enabled Aurora cluster without any code change. Babelfish for Aurora can understand T-SQL using TDS protocol, as well as extend PostgreSQL to understand commonly used T-SQL commands used by SQL Server.
How to Migrate from SQL Server to Aurora PostgreSQL
We provide a handful of tools to ease the migration process. AWS Schema Conversion Tool (AWS SCT) automatically converts SQL Server database objects, including views, stored procedures, functions, and triggers to Aurora PostgreSQL with an option to mark the objects which need manual conversion to complete the migration. AWS SCT in conjunction with AWS Database Migration Service (AWS DMS), helps to continuously migrate the databases from SQL Server to PostgreSQL, keeping the source databases operational during the migration, which minimizes the downtime for the applications that rely on the database.
AWS also offers other programs and services, ranging from AWS Professional Services which taps into the deep expertise of tenured professional for migration assistance to Database Migration Accelerator (DMA), where for a fixed fee, a team of AWS professionals handles the conversion of both the database and application for you.
Conclusion
Organizations can achieve immediate cost benefits, avoiding restrictive licensing terms and proprietary lock-in features by migrating workloads to managed database services built on open-source licensing engines such as Aurora for PostgreSQL. This makes a compelling case for migration along with the benefits and advantages we highlight in this article. AWS offers different paths and tools for your migration journey, such as the AWS SCT, AWS DMS, and Professional Services, which make conversions to PostgreSQL easier, and Babelfish, which allows Aurora to understand the SQL Server TDS protocol and language.
Amazon Aurora is the result of an unwavering, focused investment strategy. Our roadmap has been, and will continue to be driven by customer requests. We look forward to supporting you in your commitment to innovate with our commitment to innovate.