Tags: PowerBuilder, unification
A financial service software provider needed to upgrade their customer facing applications to SQL Server while keeping their Sybase ASE database. Our Sybase SQL Server Application Unification process allowed them to use SQL Server as a new database engine. Using SQL Server allowed our client to meet regulations that required high availability features such as Always On along with Transparent Data Encryption to secure their financial data.
We came up with a way to create an identical version of their existing Sybase ASE database running in SQL Server environment and performed some code unification that allowed the Power Builder front-end application to interact with both databases. Thus, the customer’s system now meets the rigorous high availability and encryption industry standards.
The following video shows how we used this innovative Sybase SQL Server application unification approach to meet our customers requirements.
Continue reading to discover more details on this project. Make sure to check out our new Jumpstart for SSMA offer!
Technical approach for Sybase SQL Server application unification
We approached this task in 3 steps:
- Migration of the source Sybase ASE database to SQL Server
- Upgrade of the SQL code in the application and in databases
- Application remediation to interact with both databases
After database migration, we received two separate code branches. The customer wanted a way to unify them in one code branch to simplify further development and support processes.
For example, we created new user functions to call them in the same way in any database environment. We then verified that they return the result in the identical format.
We needed to pay attention to the original application which included a lot of source code (functions, procedures, etc.). The number one challenge was upgrading the existing application because we had to analyze each and every fragment of SQL code there and unify it to support both databases.
Performing the database migration for the Sybase SQL Server application unification approach
But let’s get back to the first step of the project. We migrated the source database migration with the help of SQL Server Migration Assistant for Sybase (SSMA). Unfortunately, the automatic schema conversion didn’t include the code unification, that we had to perform.
So, we had to perform a big part of database migration process manually. In fact, we used 3 different approaches to unify the database code:
- Simplification;
- Emulation; and
- Duplication.
Let’s talk about these steps in details.
Code simplification
Sybase ASE and SQL Server databases have different syntax, but that they both support the ANSI SQL standard. So, if we need to create unified code to execute it both in Sybase ASE and SQL Server, we have to simplify it and lead it to the ANSI SQL standard.
We’ve made the code review to determine the functions and procedures that needed updates to meet the ANSI SQL standard. Thus, we completed the code simplification step. Consider the following code example:
1 2 | SELECT LastName, FirstName, Address FROM Persons WHERE Address = NULL |
Sybase ASE supports using the equal sign in the WHERE clause of SELECT statements, but this is not the case for SQL Server. At the same time, the ANSI SQL standard allows using ‘IS’ instead of the equal sign. So, we changed the Sybase ASE source code in the following way:
1 2 | SELECT LastName, FirstName, Address FROM Persons WHERE Address IS NULL |
Now we can execute this code in any database environment, whether it will be Sybase ASE or SQL Server.
Nonetheless, we didn’t affect the large part of code objects on this step. So, we still had to emulate or duplicate them on the next steps.
Also, performing the code review, we’ve eliminated the code fragments, which contained the deprecated features of SQL Server. This action allows avoiding problems with code executing in future.
Code emulation
The hardest part of the code unification process relates to code emulation.
Various functions may have the similar names in Sybase ASE and in SQL Server, but they require different call parameters or may use the different format to return the result. So, we had to wrap them up into the user functions, that will emulate the behavior of the original database functions but at the same time will have the similar syntax in both databases.
Then, after emulating the code on both database environments, we will call these newly created user functions in the application instead of the original Sybase ASE function.
Consider the following example of the application code:
1 | INSERT INTO t_guiddata VALUES (@newid, 1) |
To emulate the same procedure in the Sybase ASE database, we’ve used the following code:
1 2 3 4 5 6 7 | CREATE PROCEDURE get_newid @optionflag INT, @newid VARCHAR(36) output AS BEGIN SET @newid = newid(@optionflag ) END |
And here’s how we emulated the same code in SQL Server:
1 2 3 4 5 6 7 8 9 10 11 12 13 | @optionflag INT, @newid VARCHAR(36) output AS BEGIN SET @newid = CASE @optionflag WHEN 0 THEN REPLACE(CONVERT(VARCHAR(36), newid()), '-','') WHEN 1 THEN CONVERT(VARCHAR(36), newid()) ELSE NULL END END |
In the end, we can use the unified piece of code in the application to execute the procedure both in Sybase ASE and in SQL Server:
1 2 3 | DECLARE @newid VARCHAR(36) EXEC get_newid 1, @newid output INSERT INTO t_guiddata VALUES (@newid) |
As you can see, we performed the code emulation not only in the newly created SQL Server database but in the original Sybase ASE database as well.
Code duplication
For some rare instances, we could not perform the code simplification or the code unification approach. In this case, we duplicated the executed SQL code depending on the database environment.
Thus, we had to determine the database environment prior to executing the query. We’ve used the “if @@version like” command to determine the database environment, and then added the query with the corresponding syntax. Check out the following application code fragment:
1 2 3 4 | IF @@version LIKE 'Adaptive Server Enterprise%' EXEC ('SELECT * FROM TTI (INDEX TTII) READPAST') IF @@version LIKE 'Microsoft SQL Server%' EXEC ('SELECT * FROM TTI WITH (INDEX (TTII), READPAST)') |
Actually, the code duplication is the easiest way to perform the application code unification, but in this case, the client application requires quite a few system resources.
On the final stage, we’ve upgraded the existing PowerBuilder application by adding the unified code, so the application could interact both with Sybase ASE and SQL Server databases.
Benefits of the Sybase SQL Server application unification approach
When we finished the application remediation, we connected it to the newly created SQL Server database and deployed it into production. So, now the customer got the system, consisting of 2 databases and one application, interacting with both databases.
Please note that the new version of the application kept the same interface and features, as the original version. So, we completed the system upgrade, but nothing changed for the end users. Also, we provided the development guides to the developers of the PowerBuilder application. With our Sybase SQL Server application unification solution, they can easily upgrade the application using one version of their source code in the future.
Feel free to contact DB Best today to see if Sybase SQL Server application unification is right for you.
