Sponsored Feature If you are still hosting Microsoft SQL Server in-house, it might be time to give your database administrator some time off to focus on things that add more value to the business than routine management. It certainly seems that more companies are flocking to managed databases in the cloud, according to Gartner. In 2017, on-prem database revenues dwarfed managed database revenue. In 2021, they were equal in size.

Amazon Web Services (AWS) has supported SQL Server via its Amazon Relational Database Service (Amazon RDS) since 2012. So why might an end user organization consider making the move to a managed cloud service for SQL Server, and what is involved in the migration? 65kg Packing Machine

The appeal of managed services

Oluropo Rufus Ayodele, lead data architect at AWS Premier Consulting Partner Onica, believes that a managed approach to databases removes much of the heavy lifting from users. Database administrators do not have to spend as much time supervising things like backups, provisioning and patches which are all handled automatically, for example.

Managed services also offer built-in security by encrypting data at rest and in transit. An on-prem SQL Server can handle encrypted connections but requires some configuration, while a managed service provides this as an out-of-the-box option.

Managed databases offer built-in backup functionality, configurable via the console, which removes another task from the DBA's to-do list. Customers can reduce the likelihood that they will have to restore from those backups thanks to built-in redundancy in the form of Multiple Availability Zones (Multi-AZ) and replicas. RDS users can configure these simply because they are part of the AWS infrastructure, making it easier to keep databases up and running.

"With on-prem databases you have to schedule downtime," Ayodele explains. "However, with RDS you have minimal downtime since the standby instance fails over fast and then becomes the new primary server, after its maintenance is completed."

Customers can also monitor their Amazon RDS for SQL Server instances using built-in tools such as CloudWatch, Performance Insight and Enhanced Monitoring rather than setting up and configuring third-party monitoring tools.

Finally, managed services offer easier provisioning, Ayodele says. Customers can automate their provisioning to easily scale their database usage and accommodate workload fluctuations.

Moving to RDS also offers license savings. For new projects that will require additional SQL Server cores, customers can benefit from the RDS pay-as-you-go model including Microsoft licensing, which represents an up-front cost saving.

The sky is the limit

The service offers flexible capacity and scaling options to suit different workloads. Customers can enjoy up to 128 vCPUs and 4096 gigabytes (GiB) of RAM per instance with RDS for SQL Server. Memory per instance can be scaled to the capabilities of the operating system, and CPU power can be increased. RDS for SQL Server can handle databases with up to 16 tebibytes (TiB) of storage. Customers can also decouple the storage from the CPU scaling, enabling them to right-size each for their specific workloads without paying for extra resources that they do not need.

Unlike on-prem systems, scaling the database generally does not always necessarily involve downtime. AWS Auto Scaling allows the customer to scale up (but not down) without taking the system offline. But if they want to provision a larger instance size – from M5.xlarge to M5.2xlarge for example - there is still a small amount of disruption.

So, while DBAs do not have to provision extra memory and CPUs to cope with spikes in storage demand, they should plan for a minor period of downtime when provisioning to get more CPU and memory. Customers can schedule these events in their maintenance window to minimize the impact.

RDS for SQL Server supports parallelism just as it would in an on-prem environment. It uses all cores with no redundancy to optimize its use of CPU resources. While batch mode queries are limited to two degrees of parallelism on RDS for SQL Server Standard Edition, the Enterprise Edition can use all of the cores, enabling it to boost performance considerably on larger queries.

Switching SQL Server deployments to RDS has enabled Onica to bring customers significant cost advantages. One company in the advertising and publishing industry was already running SQL Server on an AWS EC2 instance for example but wanted to modernize by moving to a managed service. "They moved to RDS because they were growing," explains Ayodele, but they wanted to be as economical as possible.

Onica worked with the client to build a pay-as-you-go RDS option. This enabled the customer to optimize its cloud usage by selectively running a replica in the Asia-Pacific region, which fielded some queries during APAC hours. A script enabled that replica only during those hours, switching it off during European hours when most of its queries came from the west.

Many customers, like the advertising and publishing company, will experience improved efficiency gains and flexibility to scale up and down various workloads, with no or minimal downtime, translating to higher profitability over the long term, Onica says

A homogeneous migration between an on-prem version of SQL Server and the RDS equivalent can actually be relatively easy, says Ayodele. The only change necessary is an alteration of the system schema. RDS has built-in stored procedures for management purposes that are not in the on-prem SQL Server engine. So, customers can simply migrate the database itself to avoid corrupting the RDS system schema.

The next step will be to use native tools or AWS Database Migration Service (AWS DMS) to port the data across from the source to the destination. With AWS DMS, the source database remains operational during this process to minimize downtime. DMS can use change data capture (CDC) technology to keep track of ongoing changes in the source database during migration. Once the migration is done, the final steps will be to run the RDS version as a replica and then switch over to the RDS primary database instance when ready.

There are some best practices that customers should follow when migrating, Ayodele says. "The speed of the network connection is important," he says. "You must capture that early in the project." Nothing stymies a data migration project like a thin pipe that creates a data bottleneck.

Another critical step is undertaking adequate performance testing to ensure that the destination system performs as planned. Onica typically samples data from the client's on-prem or EC2-based SQL Server system collected over the prior six months to understand its memory usage and speed. Then they write code to get that information, taking samples that reflect different production scenarios over time, such as constant and spiky input/output (I/O).

Onica uses that sample data to inform the configuration of the RDS system. Once they have spun up the cloud environment they will run some simple benchmarks to assess its performance. This design and testing procedures help the customer to optimize the configuration before they migrate all of the data across. "This helps us to avoid mistakes such as over-sizing the CPUs," Ayodele explains.

The start is just the beginning

When should customers get started on migrating SQL Server into RDS? For some, the best time was yesterday, and the next best time is today. Events such as end of support (EOS) or upcoming license expiration can trigger these decisions.

For example, SQL Server 2012 and 2012/R2 reached their end of support in July 2022, which will leave some Microsoft customers without security updates, non-security updates, bug fixes, or technical support. They can get Extended Security Updates (ESUs) until 2025, but that still leaves them without mainstream non-security updates. If their SQL Server usage is critical to their business, it might make more sense to modernize now with a cloud deployment.

SQL Server 2019 will reach the end of its mainstream support in January 2025 so any organization with an on-prem SQL Service license that expires before then might want to consider making the jump now rather than renewing later.

SQL Server license is included in the cost of RDS for SQL Server under its license-included model (Standard, Enterprise, Express, Web) which saves customers from having to worry about the licensing terms altogether. With no commitments or upfront fees, customers can pay on-demand or choose the reserved instance for one- or three- year term at a discounted rate for additional savings.

Do not leave it too late, warns Ayodele. Having ample time to migrate gives customers the opportunity to re-evaluate their data infrastructure and look for optimization opportunities, he points out.

There are also opportunities to split the relational database architecture and put some of it into NoSQL options, he adds. "Most of the NoSQL databases are serverless, and we have architects who can integrate those for you."

Thinking ahead about migrating to RDS for SQL Server also gives customers the time needed to secure leadership buy-in.

A typical Onica discovery process begins by working with a customers' technical staff and conducting a migration readiness assessment to understand its current infrastructure. They then conduct a technical cost of ownership analysis to understand parameters such as how many servers the customer is likely to use and the volume of data they will process.

"Senior technical managers get involved towards the tail end of this work and examine the report," Ayodele says. "That prompts more questions about what's driving the project."

A foundation for further modernization

After customers have made the jump to RDS for SQL Server, they can take advantage of the other services that AWS has to offer, modernizing their architecture still further, says Ayodele. "A common option is the artificial intelligence (AI) machine learning services," he says. These include capabilities like image and video recognition. He gives an example of a company wanting to ensure that its advertisements only play alongside appropriate videos. "Video recognition can help you do that," he says.

Other applications for machine learning include predictive analytics, which SQL Server for RDS customers can use via advanced R and Python integration. On the infrastructure side, the ability to run on GPUs and offer full parallelism enables the service to scale with clients' needs, Ayodele concludes.

Migrating on-prem SQL Server to managed Amazon RDS services need not be difficult, and it can present some significant cost and operational benefits. Nevertheless, some companies might still find it overwhelming or may require help from experienced practitioners.

A cloud-native consultancy services partner like Onica can help you make the leap, says Ayodele, ensuring that you pack properly for the journey and make full use of the cloud when you arrive.

The Register - Independent news and views for the tech community. Part of Situation Publishing

Wrapping Machines Biting the hand that feeds IT © 1998–2022