nib SQL Server migration, optimisation into AWS Resiliency

Overview

nib Group is a trusted international health partner, providing health and medical insurance to over 1.5 million Australian and New Zealand residents  and health insurance to more than 180,000 international students and workers in Australia.

nib Group is also Australia’s third largest travel insurer and a global distributor of travel insurance through nib Travel, providing financial protection and assurance to travellers wherever they are in the world.

Background

Cloud adoption was established as part of nib’s strategic direction and the organisation has been on a multi-year cloud adoption journey gaining the transformational benefits of various cloud technologies. The cloud-native AWS platform dubbed “Red Queen Platform” has underpinned nib’s digital transformation and enablement using well-understood secure and resilient cloud architecture patterns.

nib has recently accelerated the cloud-native adoption program to achieve both the financial efficiency benefits associated with decommissioning infrastructure and to also gain the innovation and high availability benefits of using modern cloud-native services.

The Challenge

Critical Microsoft SQL Server workloads constituted a large portion of the workloads that needed to be migrated from VMC to AWS native. The SQL Server workloads presented numerous challenges that had to be addressed as part of the migration activity.  

nib were running a number of Microsoft SQL workloads in AWS VMC that were experiencing the following challenges:

• High operational cost due to VMware licensing for a large number of hosts.
• Limited resource capacity due to a limited amount of hosts.
• Microsoft servers required updating to the latest server edition.
• High availability required to meet regulatory requirements
• Risk of manual errors during deployments and patching

The Solution

nib engaged Mantel Group to assist with migrating Microsoft SQL workloads to AWS. The solution involved:

Building new EC2 servers using Terraform to host the workloads.

• Updated and hardened Microsoft server operating system used.
• Updated version of Microsoft SQL edition used
• Applying the correct SQL Server License type for the workload
• Deploying EC2 servers in locked down VPC database tiers, to increase security posture.
• Resource capacity reviewed and improved  using the latest EC2 instance types.
• Following Microsoft and AWS best practices deployment to optimise Microsoft SQL performance.
• Automated installation of Microsoft SQL to meet customer requirements.

Building new IAM roles and security groups for workloads using Terraform.

• IAM roles follow best practice of least privileged and only allow AWS SSM access.
• User access provided through AWS SSM
• Security groups locked down to NIBs private address range.

Enabling operating system patching using AWS SSM

• Solution accounted for clusters to gracefully failover a node before rebooting.

Migrating database workloads to new hosts and updating client connections details.

• Building a buildkite CICD pipeline to deploy AWS resources using Terraform to multiple environments.
• Building a backup solution using AWS backups, enabling backup tiers to meet the business’s different backup and retention policies.  This solution also provides a bunker implementation for disaster recovery.
• Self-healing achieved through AWS’ services configured to be deployed to all Multi-AZs
• Disaster recovery and recommend RTO and RPO tiers to meet SLA’s
• Monitoring of the deployed applications and infrastructure is done using native AWS tooling, specifically AWS CloudWatch Metrics and X-Ray
• Uplifting the security for the AWS platform by deploying AWS Inspector, Guarduty, Config and SSM.
• Implementing a budget across workload accounts using AWS budgets. This is to ensure accounts do not exceed the set budget.
• EBS encryption

TempDB on local Ephemeral SSD drives

• By using Terraform, all infrastructure is managed as code. This allowed for development practices to be followed with a trunk based branching strategy, peer code reviews, code validation, protected branches and automated deployments to different environments.

AWS Technologies Used:

• EC2
• VPC
• IAM
• Security groups
• SSM
• Inspector
• Guarduty
• Config
• Backup
• AWS budgets

The Results

The SQL Server workloads were migrated, updated and hardened achieving numerous benefits:

• A more cost-efficient SQL Server environment through decommissioning databases that were no longer needed and applying the minimum licence for the features needed.
• An increase in performance based on higher-grade EC2 instances
• Consistent and streamlined deployments resulting in fewer errors and faster time to market through incorporating deployment automation into the SQL Server environment
• High availability applications that meet the targeted Recovery Point Objective SLA’s