Database Migration from MS SQL Server On-prem to a MSSQL-Centrally Managed DB Engine

Database Migration from MS SQL Server On-prem to a MSSQL-Centrally Managed DB Engine

Executive Summary

Our client is the leading personal systems and printing company having a robust plan for venturing into innovative technologies such as 3D printing and novel computing experiences. The client has an ASP .NET and MS SQL-based on-prem application running on IIS and using Windows 2012 SE servers. The client is undergoing a large data center exit program. As a part of the program, all applications are being analyzed and are either being rehosted or re-platformed and refactored. Preference as a part of application discovery is re-platform and retire. The application used MS SQL DB for transactions and will be migrated to MS SQL on an EC2 instance on AWS.

Business Challenges

The customer is in the process of exiting the data center. This has to be accomplished in a limited timeframe. While exiting the data center is a priority, the customer would like to make sure that they are not porting their technical debt to AWS. Many internal business-critical applications are running on this data center. The ask from the customer was to analyze each application, understand its business use cases, and document the underlying technology stack as well as the release management process. This information is then used to build a forward-looking cloud-ready architecture that will allow for refactoring of the application source code and re-platforming of the underlying infrastructure to use cloud-native services, thereby improving efficiency in management, make application hosting cost-effective and get performance that can scale as per business needs. Additionally, refactoring and re-platforming the solution will allow for periodic upgrades and reduce any technical debt on-prem. Migrating the database to more cloud-ready databases was a part of this business challenge. Our team defined an architecture pattern that will allow for re-platforming of the DB and enable the customer on a performant, non-licensed, HA, and natively managed AWS data management service that will suffice their core technical requirements of exiting the database and migrating/modernizing their applications to the cloud.

Our Solution

The Sincera architecture team recommends migrating out of MS SQL to MS SQL hosted on the EC2 database.  The customer’s GDBA (Global Database Administration) team has approved and manages a few possible databases on the cloud. They have successfully built databases to support mission-critical workloads n EC2. The team is still testing RDS for different business requirements,  but it strongly feels that they can build common databases on EC2 and provide both multi-AZ and multi-region replication in a cost-effective manner.  The team currently supports Oracle, MariaDB, MSSQL, and Postgres on EC2 instances. Based on the target databases, that GDBA provisions and supports, the decision to port to MS SQL on EC2 instances were made.

With the limited number of data tables in the schema and all CRUD operations happening through stored procedures, this migration is a low to medium-complex data migration that keeps the database migration standard and allows the customer to use their existing MS licenses. It is hosted on EC2s which is a multi-AZ HA service that can create snapshots and backups for read operations tied to running reports and analytics. It also allows them for point-in-time recovery of the databases.

Impact/ Key Benefits to the Client

  • Completes failover scenarios in one minute instead of 30 minutes – The client saved time by taking advantage of Amazon EBS backup and restore applied to MSSQL. Moving to AWS helped them automate the provisioning of these databases using IAC pipelines, that has been specifically designed for mission-critical applications.
  • Self but centrally managed DBs by a centralized team also saves time for the application database administrators (DBAs) to learn and manage AWS components. Their DBAs have freed up at least 15% of their simple DB management time from database-support activities, including server administration, and backups/snapshot support.
  • Monitoring and metrics: The client can now view key operational metrics in AWS Management Console, including compute/memory/storage capacity utilization, I/O activity, and instance connections.
  • Isolation and security: As a  shared and centrally managed service, the solution provided a high level of security for the client’s MSSQL database.