University of Nebraska-Omaha’s ITD Lab migrates to Amazon Aurora with Babelfish, reducing database costs

The IT for Development research program at the Department of Information Systems and Quantitative Analysis at the University of Nebraska at Omaha (ITD Lab at UNO) created an application website using a SQL Server database. This application, called ETeams, connects small businesses and microenterprises to student-led research teams to help them solve business problems using technology and training interventions.

The ETeams program uses a relational database to diagnose the problems small businesses face when implementing technology. Once a diagnosis is made, alternative solutions to the problem can be identified through the database. The business owner selects a solution, and the researchers then use the tools connected to the databases and work with the business owner to develop the solution. The database contains more than 200 records of such technology training interventions and is used to analyze data on outcomes.

The ITD Lab at UNO previously migrated the ETeams application to Amazon Web Services (AWS) because they did not want to manage the underlying infrastructure. With a limited staff, they could focus more on building new functionality in the ETeams application and less on performing mundane maintenance tasks. While the application was performing well, the ITD Lab Director at UNO wanted to lower the monthly costs. This blog post explores an alternative to an SQL Server database that is just as performant but at a lower cost. The ITD Lab at UNO analyzed their application to see if they could take advantage of this alternative.

Challenges of collecting data in the field

Microenterprises are very small businesses where the business owner uses IT solutions to solve their business problems. There are several challenges to collecting and analyzing data while carrying out technology and training interventions to support the growth of microenterprises, including:

1. Researchers collect data in the field
2. Researchers store the collected data in a new record for that microenterprise
3. Researchers correlate this new data to existing data
4. Finally, the relational database needs to support the researchers when they offer alternative solutions and train the business owner to implement these solutions

This means that the system needs to be responsive while some researchers are collecting data from multiple sources and other researchers are working with the business owners. Besides data collection, the system also allows researchers to enter a diagnosis, identify and select alternative solutions to the problem, and record interventions and outcomes. The Association for Information Systems (AIS) eLibrary website further documents this process. Additional citations can be found within the ITD Lab at UNO Micro Enterprises and Small Businesses program.

When the ETeams application was first migrated to AWS, it used the Amazon Relational Database Service (Amazon RDS) for SQL Server Standard Edition for its database. Amazon RDS for SQL Server was configured as a single database instance with pay-as-you-go pricing.

Cost quickly became a factor, as the SQL Server license was the most expensive part of the application. When the ITD Lab Director at UNO asked about reducing the costs, their AWS solutions architect recommended they consider using Amazon Aurora Serverless PostgreSQL-Compatible Edition with Babelfish integration. Switching the database to Aurora has reduced database costs for the ITD Lab at UNO by more than 87 percent.

The ITD Lab at UNO originally selected a db.m5.large instance (2 vCPU, 8 GiB memory) for RDS for SQL Server Standard Edition at a cost of $0.977 per hour. This translates to $703.44 in a 30-day month. With Aurora Serverless, capacity scales automatically with demand. As usage of the ETeams application increased, resources were scaled up automatically to meet the demand. And as usage subsided, resources were automatically scaled back down. Pricing is based on Aurora capacity units (ACUs) at $0.12 per hour. The ITD Lab at UNO’s ETeams database consumes an average of 24.78 ACU per day, which translates to $89.21 in a 30-day month—an 87.3 percent reduction in cost.

Solution overview

The Babelfish integration enables an Amazon Aurora PostgreSQL-Compatible database cluster to accept database connections from SQL Server clients. With Babelfish, applications that were originally built for SQL Server can work directly with Aurora PostgreSQL. Few code changes are required compared to a traditional migration and database drivers don’t need to be changed. For more information about migrating, see the Migrating a SQL Server database to Babelfish for Aurora PostgreSQL documentation.

Figure 1. These native protocols can be used to connect to Amazon Aurora.

The ETeams application previously connected directly to the RDS for SQL Server database using port 1433. Babelfish accesses the Aurora PostgreSQL database using its native port. Shown in Figure 1, both SQL dialects are available through their native protocols by default at the following ports:

• SQL Server dialect (T-SQL), clients connect to port 1433.
• PostgreSQL dialect (PL/pgSQL), clients connect to port 5432.

The ETeams application, although still connecting on port 1433, now connects to Aurora PostgreSQL using the Babelfish endpoint.

Prerequisites

To implement this solution, an AWS account is required. Experience with RDS for SQL Server and Aurora PostgreSQL is also recommended.

Solution walkthrough: Migrate to Amazon Aurora with Babelfish to reduce database costs

Creating a Babelfish for Aurora PostgreSQL DB cluster is the first step in enabling Babelfish integration.

After the cluster is created, the Babelfish Compass tool is used to create a SQL Server to Babelfish feasibility assessment. A data definition language (DDL) report is extracted from the original SQL Server database. Babelfish Compass uses this data to generate a compatibility assessment report for the SQL Server code.

The Deep dive into Babelfish Compass blog post provides detailed instructions for installing the Compass tool.

Compass assessment and mitigation

The Babelfish Compass tool assessment identifies unsupported features and other areas that require review.

Figure 2. Screenshot of a Babelfish Compass assessment summary. This summary shows statistics of the analyzed SQL and the total count of objects that need review.

For the ETeams database, the assessment report identified only a single unsupported feature with high complexity. As detailed in the assessment report in the following Figure 3, the unsupported features of low and medium complexity are recurrences of a few commands. This is also true for the items to review. As a result, this database was not difficult to migrate.

Figure 3. Screenshot of the Babelfish Compass assessment of unsupported features. In this example, there are four unsupported features that require review.

These items were reviewed in order of importance:

1. High-complexity unsupported features

These must be addressed first. There was only one high-complexity feature to resolve, and it related to adding a member to the db_owner role. Since PostgreSQL handles user role permission differently, this code was removed from the stored procedure and the db_owner role was manually assigned to the application user ID.

2. Medium-complexity unsupported features

The ETeams database had 33 uses of the ALTER database command and one use of the UNPIVOT command used within code.

The ALTER database commands are specific to SQL Server and are not required for PostgreSQL. These commands were simply commented out of the code.

The UNPIVOT and PIVOT commands are also SQL Server specific syntax. Since PostgreSQL uses different syntax, this issue was resolved using a mix of COUNT and CASE statements that produce the same result.

The Microsoft SQL Server to Amazon Aurora PostgreSQL migration playbook documentation provides more examples of migration techniques.

3. Low-complexity unsupported features

The sp_addextendedproperty feature was called 42 times in various stored procedures. After discussing with the application owners, these were ignored because they were not required in Aurora PostgreSQL settings.

Figure 4. Screenshot of the Babelfish Compass assessment of features marked “Review Manually.” The assessment identified only one stored procedure that required a manual review.

4. Features marked as “Review Manually”

For ETeams, there were some full-text stored procedures used for database maintenance work. These were no longer needed with Babelfish and were removed.

Figure 5. Screenshot of Babelfish Compass assessment of features marked “Review Semantics.” The assessment identified semantics that required review for three procedure types.

5. Features marked as “Review Semantics”

The clustered index creation and schema binding features were called out in the assessment. However, since PostgreSQL has a different way of creating indices and does not use CLUSTERED or NONCLUSTERED key words, they were simply removed.

Once the changes were made, a follow-up Compass assessment validated that the unsupported features had been removed from the DDL. The resulting DDL was migrated to the new database cluster, and data from the original SQL Server database was loaded.

The ETeams application connected to the Babelfish endpoint using a new user ID and password for Amazon Aurora PostgreSQL.

Testing the new application verified a successful migration.

Conclusion

After a successful migration from SQL Server to Amazon Aurora PostgreSQL-Compatible Edition with Babelfish integration, the ITD Lab at UNO was able to reduce their monthly database costs by more than 87 percent. This allowed them to provide a responsive application at a much lower cost. And, the migration did not require extensive code changes. To see if your application is a good candidate to use Babelfish integration, reach out to your AWS solutions architect or contact AWS today.