Modernising databases: securely migrating MS SQL Server to AWS cloud

The Microsoft SQL Server relational database management system supports business intelligence, transactional processing and analytics. As such, it’s a long-established pillar of database technology, but there are significant drawbacks with using Microsoft SQL Server in a traditional manner. As an on-premises service, SQL Server is expensive to license and maintain, and it offers relatively little in terms of scalability, reliability or iterative enhancement.    

Amazon Web Services – AWS – database facilities offer the opportunity to migrate your Microsoft SQL Server databases to a modern cloud-based environment. AWS database migration opens the door to a highly available, mature database solution that can manage high volumes of data activity, transactions and concurrency even during outages or maintenance.     

Time to modernise your enterprise databases?        

If your organisation is looking to evolve its Microsoft database services with a cloud-based solution, then AWS is the most flexible, sustainable option. With Versent’s help, you can make a swift, secure cloud transition, retaining all the features of your current SQL Server setup and gaining the benefits of cloud modernisation.   

This article is a guide to the features available with AWS RDS for Microsoft SQL Server, Amazon’s premium database service with which you can deploy a highly scalable, robust solution at a competitive cost.  (In this article, we’ll talk specifically in terms of the Relation Database Services for Microsoft SQL Server 2016 or later.)      

Your data must remain secure and uncorrupted during migration. You’ll also need your databases to remain highly available with no transactions lost. To this end, AWS offers multiple services to safely migrate your Microsoft SQL Server databases.      

For this scenario, we’ll assume that we’re migrating an existing Microsoft SQL Server running version 2012 on-premises and that the requirements for success are security, reliability and availability during migration and go-live.

Step 1: assessment, discovery & planning   

Versent begins every database migration by using discovery workshops and tools to define our customers’ technical and business requirements. The purpose of these workshops is to ensure that the database profile and its usage are correctly understood.   

It’s important to note that AWS Relational Database Services (RDS) supports multiple Microsoft SQL Server versions (major and minor builds). The selection of the appropriate database version and edition should align with the features and requirements of the original SQL Server application. For example, transparent asymmetric table encryption is only available on Microsoft SQL Server 2012 Enterprise and later. The choice of database engine type is not to be taken lightly. AWS doesn’t support automatic upgrades to new versions of SQL Server, but there are ways to safely upgrade manually with the right knowledge.    

During the discovery phase of migration, Versent experts analyse many aspects of your existing setup, including:         

• Frequency of data transmission      
• Concurrency threshold      
• Table structure   
• Database size   
• Database engine type   
• Database version and edition   
• User mapping   
• SQL roles   
• Table heat map   

There are five key elements to consider when planning a smooth migration for Microsoft SQL services:       

• Data preservation and replication   
• Application connectivity   
• Authentication types and delegation   
• Redundancy support (high availability, failover, disaster recovery & log shipping)   
• Backup purposes and frequency   

Defining how to achieve these objectives eliminates the risk of data loss, preserves stable service for users and protects applications connected for service.   

Step 2: deployment      

When creating a new AWS SQL Server cluster, it’s vital to enable database options aligned with your business objectives. Additionally, the configuration must follow cloud architecture best practices.     

Best practices during deployment are:      

• Data encryption in transit and at rest      
• Data transmission authenticated via access only (with SQL logins or Windows authentication mode)   
• Authentication using secure userpass, ensuring role-based access controls   
• Data archival retention period      
• Database options for enabling or disabling features specific to the application roles   
• Service availability and access from public or private endpoints     

Versent always recommends that the creation of services and environments is programmatic and templated to ensure that a code-driven state can be aligned with the AWS services deployed.    

Step 3: data migration      

The process of data migration to AWS has five main parts:      

• Exporting existing user and system access logins and profiles   
• Database schema overview and profile   
• Database backup export and preliminary restore   
• Setup of replication utility (AWS Database Migration Service & SQL Log Restore)   
• Go-live, management, and maintenance activities   

Versent uses AWS Database Migration Service to complete a data migration, which allows fast, efficient, secure migration between source and target environment with multiple types of database engines, including Microsoft SQL Server.     

The diagram below represents data migration from an on-premises setup to AWS RDS for Microsoft SQL Server via AWS DMS. Also illustrated are the private connectivity requirements of this process.    

Step 4: dry runs   

At this stage of your database migration, replication processes must capture all the data sets from your legacy environment.    

Versent recommends performing multiple data transfer dry run exercises to reduce risk and build migration confidence.  Severe problems can be avoided with appropriate rinse & repeat trial runs that deliver additional validation for application integration.   

Step 5: go-live   

Now that the discovery, planning and testing stages are complete, Versent follows these steps to successfully execute the migration of a database to AWS:   

• Stop all user and application services   
• Drop all non-critical services to the database engine   
• Initiate a backup to the target environment; either a full sync of the database or differential sync with AWS DMS  
• Execute any user configuration scripts on the target database engine   
• Update the connection strings for any user services or applications to the new database environment   

Step 6: verification         

After performing a database migration, the final and most important step is to verify that the data has transferred accurately. This can be achieved with table space verification queries, database procedure lookups, validation of schemas and associated roles, and checking that users are correctly configured for the data roles they require.   

When connecting to the new target environment, take care to ensure that the old database engine has been stopped or, better yet, shut down. This will ensure that if any user (client) or application malfunctions, it doesn’t begin adding data to the old environment whilst the new target environment operates.   

Takeaways         

Versent has successfully migrated Microsoft SQL Server to AWS RDS for many of our clients over the years. We’ve discovered a few key learnings during that time, which help us achieve optimum outcomes for all our customers.    

Microsoft SQL Server systems manage and hold crucial data and application state information, so they demand a robust solution for both availability and performance.   

Dry run testing is an immeasurably valuable protocol and can’t be overused. At Versent, we test rigorously and extensively before deploying all go-live activities, so our customers have confidence in the migration process.    

We’ve observed that legacy Microsoft SQL Server databases are usually not managed or maintained regularly enough despite, or sometimes because of their critical service status. Leveraging AWS RDS for Microsoft SQL Server allows you to unlock this paradoxical dilemma and easily update, upgrade, or migrate.   

Achieving optimum functionality with your enterprise database isn’t an unattainable goal; it’s simply a matter of implementing the right cloud environment. If you’re ready to evolve your databases, Versent has the knowledge and experience to make sure your migration goes off without a hitch.    

Want to learn more about the benefits of database modernisation?    
Reach out to a Versent expert and get the right advice for your business.