Supporting Customers as They Migrate Microsoft SQL Server Workloads to AWS

A guest post by Datavail, an AWS Partner Network (APN) Consulting Partner and an AWS Microsoft Workloads Competency Partner specializing in database solutions.

Through the AWS Competency Program, we’ve been validated by AWS for our proven expertise in offering applications and database solutions built on the Microsoft SQL Server platform on AWS. This includes data management and warehousing, business analytics, structured and unstructured database integration and operation, and ensuring a high degree of security and regulatory compliance.

In this post, we would like to walk you through some of the benefits we have seen customers gain by running Microsoft SQL Server workloads on AWS. We will then provide a couple of real-life examples to illustrate our process for helping customers migrate SQL workloads to AWS.

Benefits of running Microsoft SQL Server on AWS

Amazon Relational Database Service (Amazon RDS) provides a fully managed database service that helps a customer instantly launch and configure a database server for immediate use. The following are just a couple of features that make Amazon RDS for Microsoft SQL Server very attractive:

Automated backups
Point-in-time recovery
Implement High Availability through a multi-availability zone (AZ) deployment.

The alternative to running Amazon RDS SQL Server instances is to run Microsoft SQL Server on an Amazon Elastic Compute Cloud (Amazon EC2) instance. This option can help you leverage the various instances and storage types offered by AWS. Additional benefits include:

Full control: Complete control over settings and configurations just like on-premises instances.
Elastic and on-demand: You can increase or decrease capacity in minutes, commission one of hundreds of instances automatically, and power on and off instances as you need to meet demand.
Reliability and security: Amazon EC2 Service Level Agreement (SLA) commitment is 99.95% availability for each AWS Region. Plus, AWS has data centers and network architectures to meet the requirements of the most security-sensitive organizations, which means that most, if not all, of your security compliance requirements will be met.
High availability plus performance plus disaster recovery: Yes, it can be done! With careful planning and architectural design, your Microsoft SQL Server instances can meet high availability, performance, and disaster recovery requirements at the same time. For example, you can configure a Microsoft SQL Server AlwaysOn Availability Group with one primary node for production workload, one secondary node for read-only routing to offload reporting requirements, and a third node for disaster recovery.

Datavail’s Process for Migrating Microsoft SQL Server to AWS

When clients seek assistance with migrating their complex environments to AWS, we initiate a detailed discussion to best understand the timeline, requirements, and steps involved in the migration process. We conduct an initial assessment, prepare a migration plan, and then meet with the client to review the details, challenges, and steps involved. We also help clients determine the optimal approach- Amazon RDS or Amazon EC2- to meet their SAQL Server needs.

Case study: Migrating Sony DADC NMS to AWS

Sony DADC NMS New Media Solutions (NMS) specializes in the delivery of digital media from the content provider to the end user (the content consumer). The company’s core mission is to deliver best-in-breed, secure, and innovative supply-chain solutions for their customers throughout the world. Users of Sony DADC NMS include many major motion picture studios, television broadcasters, radio broadcasters, online content portals, music labels, game companies, and software providers.

The company’s database environment is inherently complex. Its content is multi-source and multi-channel, and can consist of structured and unstructured data, including text, images, audio, and video. According to Sony, its content management system “provides a predictable, scalable, and flexible approach to more quickly and efficiently deliver media services to audiences,” and “significantly reduces security exposure by ensuring the lowest number of possible touchpoints of valuable assets.”

Sony DADC NMS Moves to Cloud as Ven.ue

In 2016, Sony DADC decided to make its content available in the cloud under the name “Ven.ue.” Datavail, which has DBAs with deep expertise in AWS Cloud technologies, was contracted by Sony to implement the Microsoft SQL Server-related tasks, including planning, testing, and executing.

A task list was prepared for Datavail with several milestones. The following steps were included in the migration process:

Installing and configuring Microsoft SQL Server 2014
Provisioning AlwaysOn Availability Groups
Migrating the database
Establishing a performance baseline
Conducting AlwaysOn Availability Group failover testing
Completing the final migration

Offloading the reporting workload from the production database proved to be one of Sony DADC’s biggest challenges. We addressed these challenges by using AlwaysOn Availability Groups and read-only routing. Sony DADC NMS and Datavail conducted weekly meetings and work sessions to discuss any open items, roadblocks, and the next action items for this project.

Our expertise with Microsoft SQL Server and AWS helped Sony DADC NMS accurately scope, plan, troubleshoot, and execute each task within this project. Additionally, as a result of this project, we can now support customers using Sony DADC NMS who want to migrate to AWS, and can also support AWS customers interested in the benefits of Sony DADC NMS as a content warehouse solution.

Case study: Teamwork at Mammoth Mountain Ski Area

Mammoth Mountain Ski Area turned to Datavail and AWS to meet their high availability needs for the SQL Server deployment and to offload reporting functions to AWS. Datavail replicated their databases from an on-premises server to AWS for reporting and high availability. The publisher and distributor reside on an on-premises Microsoft SQL Server instance, and the subscriber is located on SQL Server instances running on EC2 instances. To further increase redundancy and availability, there’s an additional SQL Server instance running on Amazon EC2 as a warm standby server for the subscriber instance. This unique setup enables high availability as well as off-loading the reporting needs to the subscriber running on an Amazon EC2 instance.

Off-loading reporting minimizes the contention and performance impacts on the production OLTP server. In addition, the SQL Server instance running on an Amazon EC2 instance serves as a warm standby server for disaster recovery.

Architecting and supporting the hybrid model of SQL Server instances running on-premises and on AWS is a team sport. The systems, network, and DBA teams must work very closely together to ensure a successful outcome. Communication and collaboration between the systems, application, and DBA teams are paramount. For example, service pack and hot fix rollouts and any maintenance activities require all teams to be on the same page on timing, so they can notify the business units in advance, coordinate on technical tasks and responsibilities, and schedule interval updates to the business stakeholders.

Case study: Moving a major education provider to AWS

This case study involves the migration of Microsoft SQL Server to AWS for an international education provider. The company works with high schools, colleges, and universities all over the world. The company’s rapid growth presented unique challenges such as over-provisioning Microsoft SQL Server instances, insufficient monitoring and alerts, and inadequate or non-existent disaster recovery and high availability solutions.

The education company had 12 data centers they wanted to consolidate. The IT team was spending too much time dealing with alerts and troubleshooting problems to enable them to focus on IT as a competitive asset, and had created an infrastructure to handle peak loads that was unused most of the time.

Checklist for Microsoft SQL migration to AWS

We assessed the company’s system and helped put the following migration plan into motion:

Provisioning EC2 instances
Provisioning Amazon RDS instances for Microsoft SQL Server, Oracle, MySQL, and PostgreSQL
Provisioning Auto Scaling and load balancing for Amazon EC2 instances
Implementing redundancy at every stage to reduce single points of failure
Implementing best practices in scalability and security
Configuring Amazon CloudWatch to help collect and track usage metrics and manage alarms
Configuring Amazon Route 53 to move traffic to nearest Availability Zone

The result was a significant consolidation and integration of their IT infrastructure. By using Amazon RDS, the onsite DBA team can focus more on business-critical aspects of their databases and less on day-to-day enhancements. Elastic Load Balancing allows the company to move away from expensive and complicated load balancers. The company can optimize its resources by right-sizing its instances when utilization rates fall.

Case study: A fresh approach to database administration

The following example involves a multinational producer and marketer of fresh fruit and vegetables. When your inventory can spoil in a matter of moments—whether it’s left too long in the field or not sold to retailers in time—every second matters.

The company was having problems with their data business. Their Microsoft SQL Server system performed poorly in part because they had no overnight support. They also lacked a testing environment for engineering a solution.

After the assessment, the Datavail team set up a test environment, migrated Microsoft SQL Server to Amazon RDS, and decommissioned the test environment when it was no longer needed. We integrated 24x7x365 monitoring and aligned processes with best practices.

Data was successfully migrated to AWS during scheduled maintenance windows. Alerts and tickets fell as database performance improved. Monitoring and managed services ensured that the company’s DBAs could sleep soundly at night and not have to react to alerts that were already addressed by Datavail DBAs when they arrive at work every day.

Conclusion: Moving SQL to AWS

If you are familiar with Microsoft SQL Server but new to AWS, Datavail’s experts can help assess, plan, and execute your move to the cloud. If you are familiar with AWS and want the benefits of running Microsoft SQL Server in that environment, our experts can help assess, install, test, and build out a system to handle your needs. We have experts on both services who can make the transition smooth and safe.

Datavail is a specialized IT services company focused on data management with solutions in BI/DW, analytics, database administration, custom application development, and enterprise applications. We provide both professional and managed services delivered via our global delivery model, focused on Microsoft, Oracle, and other leading technologies.

AWS Partner Network (APN) Blog