In recent times, companies are focusing more on adopting innovative technologies and are keen to find new, cost-effective solutions. However, old and unsupported technology can act as a barrier in adopting novel solutions due to the risk of breaking the legacy systems which were built with immense time, money and effort.
Transitioning from legacy to new applications comes with challenges such as:
- operational downtime
- inadequate security
- slow and inefficient performance
- inability to support growing business needs
- inability to support latest technology integrations
- lack of support for maintenance.
However, the advantages of modernising legacy applications can sometimes outweigh the disadvantages. The key advantages of optimising monolithic architecture of old applications with modern cloud-based solutions are scalability, agility, better performance, security and efficiency.
Databases are one of the most critical and complex components for modernising legacy application’s technology stack. PwC India has a mature methodology for designing the legacy database modernisation and in overcoming technical and operational issues associated with modernising legacy databases to be able to leverage cloud-native services. “PwC is an AWS Premier Tier GSI Partner” and, the firm also leverages its partnership with AWS to utilise the relevant tools and techniques in this process.
One of the approaches PwC India takes for modernising databases is by adopting fully managed database solutions such as AWS relational database services (RDS) with an open-source engine like PostgreSQL. This helps to reduce the maintenance effort and licensing cost. This approach also increases the reliability and availability of the application. It also enhances the security and reduces the vulnerability as the cloud managed services patches can be applied automatically.
This blog aims to discuss an innovative approach for database modernisation wherein Microsoft technology applications with Microsoft SQL server databases as backend can be modernised to cloud in an agile and cost-effective manner.
The common approach for migrating Microsoft technology applications to cloud is to migrate the databases and hosted applications in two steps. First, the SQL Server database is migrated to an open source database to save associated license costs. One of a common target databases is Aurora PostgreSQL. After database migration, compatibility issues are fixed and then the application code is refactored as per PostgreSQL PL/pgSQL procedural language issues. This approach requires a lot of code refactoring and, therefore, requires a great amount of time and effort and may break the functionality of the application.
To address these challenges, PwC India identified an approach for database modernisation to Amazon Aurora as a target. This approach combines PwC’s database cloud modernisation methodology with a new service from AWS called ‘Babelfish for Aurora PostgreSQL’. This service enables Amazon Aurora running on PostgreSQL to understand commands from applications written for Microsoft SQL Server. It also allows the user to run legacy application Microsoft T-SQL code directly on PostgreSQL database engine with minimal modifications.
Introduction to Amazon Aurora
Amazon Aurora (Aurora) is a fully managed relational database engine (RDBMS) that’s fully compatible with MySQL and PostgreSQL. Aurora gives you the performance and availability of commercial-grade databases at one-tenth the cost. This cloud-native database platform includes high-performance features including serverless options, auto-scaling storage, and the ability to have up to 15 low-latency read replicas and to run globally distributed applications.
Introduction to Babelfish
Babelfish for Aurora PostgreSQL is a new capability for Amazon Aurora PostgreSQL – compatible edition that enables Aurora to understand commands from applications written for a Microsoft SQL server.
Babelfish is designed to provide an additional endpoint for a PostgreSQL database cluster to understand the SQL server wire-level protocol and commonly used SQL server statements (T-SQL). This allows client applications that use the tabular data stream (TDS) wire protocol to connect natively to the TDS listener port on PostgreSQL. It can accept incoming connections from one application using Babelfish TDS and another application’s native PostgreSQL connection at the same time.1
A PostgreSQL database that is running Babelfish listens for connections on two (configurable) TCP ports, in two dialects:
- SQL Server dialect (T-SQL)
- PostgreSQL dialect (PL/pgSQL)
Once the TDS handler has accepted and processed a request coming in on the TDS port, the request is passed on to a PostgreSQL backend. The backend passes the request using a custom parser which has been modified to understand the SQL server dialect.
Babelfish allows the application to continue using T-SQL (Microsoft SQL Server’s proprietary SQL dialect). It also supports native PostgreSQL connections so that the application can be modernized at the same time as the database if the customer wishes to do so.
In this way, Babelfish for Aurora PostgreSQL reduces the technical issues of moving to a different database engine. Customers gain better scalability and save the cost by migrating to Amazon Aurora, without all the refactoring work that traditionally is needed.
The next section illustrates how PwC uses this approach in its migration strategy which helps users to reduce migration time. This benefit enables organisations to consider legacy applications for database engine change and adopt the cloud.
PwC’s approach for MSSQL applications modernisation to AWS cloud with Babelfish
PwC conducts the assessment using the decision tree method shown below to analyse the dependency of database engine and take the decision whether it can include the application for modernisation or not.
PwC uses Babelfish for Amazon for compatibility assessment with the Babelfish Compass tool. With Babelfish Compass, we analyse the T-SQL SQL/DDL scripts for compatibility with Babelfish. Babelfish Compass identifies the SQL features that are not supported by the current version of Babelfish and creates an assessment report on the SQL/DDL. This analysis helps to take a go/no-go decision about whether it makes sense to consider starting a migration project from SQL server to Babelfish and to estimate the efforts required for the migration.
Sample Babelfish Compass report
By doing a small rewriting of the unsupported code, we can avoid rewriting substantial portions of the application. This helps the team in conducting the migration quickly which saves their time and resources and helps them focus on new innovations.
To perform a PoC, PwC creates the environment and copies data using data migration services like DMS or export/import.
Next, Amazon Aurora cluster is set-up and Babelfish is configured to build the database environment. The database schema DDL is then applied using T-SQL just like any SQL server database to create the application’s database objects. Then one of the following approaches is used to migrate data into the Aurora Babelfish database.
- Migration approach 1: AWS DMS with a Babelfish endpoint as target: In this approach, AWS Database Migration Service (AWS DMS) is set up with the Babelfish endpoint for the Aurora cluster as the target endpoint.
- Migration approach 2: Migrate with an Amazon Aurora PostgreSQL endpoint as target (minimal downtime): In this approach, the writer endpoint for the Amazon Aurora cluster is configured as the target endpoint for AWS DMS, and the data is migrated directly to the PostgreSQL tables.
- Migration approach 3: Export and import using the following SQL Server tools:
- SQL Server Integration Services (SSIS) with Babelfish-enabled databases as targets for data migration, but it must be self-hosted or hosted on an Amazon Elastic Compute Cloud (Amazon EC2) instance.
- SSMS Import/Export Wizard for Babelfish versions 2.1.0 and later.
- BCP Utility – Babelfish now supports the data migration using the BCP client and the bcp utility now supports E-flag (for identity columns) and -b flag (for batching inserts).
Application testing
After successfully creating an environment for PoC, we perform application sanity checks and functional validations. Then we move to the next step for migration and modernisation once the application PoC is completed successfully.
Following a successful PoC, PwC collaborates with the customer to migrate and modernise the production application to AWS and helps the customer on their path to future modernisations to the cloud for activities such as modernisation of the application or microservices architecture.
Conclusion
PwC India focuses on transforming applications as per business needs by helping migrate legacy applications to the cloud using Babelfish for Aurora PostgreSQL.
Some of the benefits of this approach for migration are:
- old and out of support applications can be moderniszed to cloud
- customers can eliminate the need to rewrite T-SQL, rewrite app code and switch drivers for MSSQL based application for PostgreSQL DB
- customers can minimise the time and effort spent on testing and cloud
- it helps in saving the high Microsoft SQL Server license cost by moving to open source and reduce carbon footprint
- it enhances the security through automated patching.
Sources
