Migrating Microsoft SQL Servers on EC2 to AWS cloud

Is your organisation looking to migrate its legacy Microsoft applications and databases to the cloud? If so, you should think about options that can:   

• preserve continuity of service,   
• facilitate compatibility, &    
• preserve your investment for modernisation in an adjacent project.   

With these criteria in mind, Amazon’s EC2 service and Serverless frameworks offer an excellent solution.   

This article is a 6-step guide to modern deployment of Microsoft SQL Server on EC2 with AWS, specifically around Microsoft SQL Server 2014 or later.    

When it comes to Microsoft SQL Server, it’s essential to ensure that the data is secure and resilient whilst remaining highly available with deep insights so that no transactions are lost or corrupted.    

Versent prefers to use AWS to modernise and migrate data to modern databases. Amazon’s EC2 service and Serverless services (Step Functions, Lambda and Simple Notification Services) allow smooth deployment of scalable, automated, and clustered solutions at a competitive cost, with improved performance and stability.      

In this blog, we’ll assume that we’re attempting a migration from an existing legacy Microsoft SQL Server (running version 2008 R2) on-premises and that the requirements for success are security, reliability and availability during migration and go-live.      

Step 1: Compatibility      

Versent begins each database migration by assessing the right technology for the job. In some cases, the recommended feature-rich managed service available (in this example, AWS RDS, Relational Database Services) may not be compatible due to modules, plugins or access requirement demands for the underlying operating system, which is not available as part of the AWS RDS ecosystem.   

It’s important to note that AWS supports multiple types of Microsoft SQL Server on EC2, so deploying SQL Server Databases on EC2 requires additional configuration effort before proceeding.   

During initial compatibility discovery, Versent analyses the following:         

• Mirroring and Analysis Services with Library Packages or Solutions   
• Access Use Mapping   
• Database size   
• Database engine type   
• Database version and edition   
• User mapping   
• SQL Roles   
• Networking criticality heat mapping   
• Third-party or binary modules access to SQL engine services  

Understanding how these components operate from the operating system into but not limited to the database engine is the primary objective in the initial discovery and compatibility validation.   

Step 2: Preparation, planning and deployment      

When creating the AWS Microsoft SQL Server cluster on EC2, it’s vital that the preparation and planning are tabled first, with all the necessary components agreed upfront. It’s crucial to ensure that configuration considerations don’t create unnecessary work that may cause service disruption in the future.   

Versent examines the following considerations in the preparation and planning phase:   

• Cluster composition and bandwidth expectations   
• Data consumption and YOY growth (performance and capacity planning)   
• Concurrency demands during highest service periods   
• Data sensitivity   
• Backup type, depth, and retention   
• Automation jobs and management   
• Insights and performance reporting   
• Authentication and Authorisation methods   
• Reverse Proxy Ingress access   
• Database configuration with common assets   
• Active Directory Integration   

During the preparation and planning phase, new environments have the following distinct steps for deployment and are the building blocks of ensuring a Microsoft SQL Server on EC2 is deployed in an automated and repeatable fashion.  

Image below: preparation – security service provisioning

Versent always recommends that the creation of services is programmatic and templated. In this step, we’re assuming the workload account is already fully provisioned and set up with the AWS Account guardrails that are usually deployed with a Control Tower (like-type) service. These security services, such as Key Management Service (KMS) Keys and Identity Access Management (IAM) Roles, will be used for all subsequent resources to be deployed with the highest degree of restriction and encryption.   

Image below: preparation – pipeline deployment

Ensuring that the ability to produce a resource in AWS is traceable and repeatable, Versent begins major workload deployments with the assistance of Serverless Frameworks, in this case, an SAM (Serverless Application Management) package that provisions Step Functions, Parameters, SNS Topics and CodePipeline.    

Image below: SAM package

Image below: preparation – custom parameters and secrets

Versent makes sure that the validation process is carefully completed regarding the custom and unique configuration the cluster will incorporate during the deployment/automation procedures. The resources created for successful deployment are:   

• Internal Cluster Naming and References   
• Cluster Secrets   
• Active Directory Secrets   
• Volume Specs   
• Backup Retention   
• Naming standard demands   
• Location of crucial items such as file shares, folder paths for MSSQL files, backup paths and configuration paths  

Image below: deployment – execute deployment of SQL cluster on EC2

At this point, it should be noted that the deployment intersects with Active Directory, DNS, networking components and data. Therefore, Versent executes the pipeline and resources in tandem with the customer to ensure that the cluster configuration achieves the desired outcome.   

Step 3: Data migration      

The data migration process has six main parts:      

1. Exporting existing user and system access logins and profiles   
2. Database schema overview and profile   
3. Database backup export, send to S3, validate security   
4. Database restore into an always-on configuration   
5. Modify database generation schema mode   
6. Go live and management, and monitoring   

To complete a data migration with Microsoft SQL on EC2, Versent performs the restoration steps with the use of AWS Step Functions, AWS SNS Topics and PowerShell for SQL Server.   
Versent continues a zero-touch handling protocol with database restores. The content of a database may be sensitive, strict, or tiered data that would warrant prevention, following industry security principles of “access of least privilege”.  

Step 4: Data validation & dry runs   

It’s important to note that when databases are restored from an existing environment that data preservation and stability is paramount. During the Post–Deployment phases, Versent always performs multiple data validation exercises to reduce the risk of any data loss, performs rehearsals of cluster failovers and validates the data imported is accessible under all circumstances.   

Step 5: Go Live   

Versent follows the steps below for migration of environments to Microsoft SQL on EC2:   

• Stop all user and application services   
• Drop all non-critical services to the database engine   
• Initiate a backup, export files to S3   
• Trigger database AlwaysOn Cluster Instance Creation (PowerShell for SQL)   
• Trigger database restore procedure (SNS Topic via CLI)   
• Validate cluster access and service endpoints   
• Update application connection strings   
• Restart application services   

Step 6: Verification         

After performing the deployment of a Microsoft SQL Server on EC2 and migrating a dataset, the final and most crucial step is to ensure that the configuration assumptions, settings and stability is rigorously tested above and beyond expectations of production services and usage. This can be achieved with pilot tests, high volume queries and forcible cluster failovers by stopping an EC2 node manually.   

The results should mirror the expectations set in preparation and planning when performing these actions. If the service stress tests are satisfactory, a full Go Live execution for Production can be redone with confidence and precision.  

Takeaways         

A few key learnings we’ve discovered through migrating Microsoft SQL Server to EC2 are:    

• Versent has seen that legacy Microsoft SQL Servers are often bespoke and that custom modules are installed requiring raw SQL Server engine access. It’s important to cater to this in technology solution choices to continue to operate as expected once migrated to AWS.   
• Repeat validation and rehearsals of the environments day-to-day usage is critical in ensuring long term success. Stress testing all dimensions of the cluster’s specifications and the built-in automation for the cluster failovers and management cannot be ignored.   
• Microsoft SQL Server’s AlwaysOn Clustering services manage sensitive data for environments, automate the controls of its uptime with failover and provide enterprise-grade transaction management. 

With the above architecture and deployment procedures incorporated as an example, it is genuinely possible to run legacy modules and custom binaries for Microsoft SQL Server on AWS.   

With Versent’s support and best of breed technology, any customer can achieve outstanding results.    

Want to learn more about cloud data migration?      
Get the right advice from a Versent expert.