How we automated SQL Server to Java migration and speeded up 4 times This article is dedicated to SQL Server modernization based on a project carried out by Ispirer for a large company operating in financial consulting. The customer harnessed the power of a SQL Server database to efficiently handle, process, access, and oversee their clients' financial information. The client aimed at migrating SQL Server to a cloud while optimizing system architecture and maintenance costs. Microsoft The Ispirer team offered to transfer business logic to Java because the database is used in OLTP mode. We will carefully analyze the subject and review the client's project, which involves converting SQL Server business rules to Java. Additionally, we will investigate the reasons behind companies opting to migrate SQL code to an application layer. This will involve a comprehensive examination of the entire process. To optimize the costs of cloud infrastructure, the client decided to migrate tables and data to PostgreSQL. Since we specialize in database and application migration, the customer turned to us with the following tasks: Moving 815000 LOC of business logic to the application layer in Java. Migrating 300 GB of data and 3000 tables from Microsoft SQL Server to PostgreSQL. Owing to the substantial scale of the project, the client endeavored to minimize migration expenses by implementing automation. To optimize the efficiency of the Ispirer Toolkit, we determined that its customization should be conducted in advance. This approach enabled us to deliver a tool to the client's team, with a T-SCL to Java conversion rate of approximately 90%. Now let’s dive deeper to the migration of tables and data. From SQL Server to PostgreSQL: migration of data and tables Let's consider the reasons why the customer chose to migrate from an on-premises SQL Server to the cloud. This transition includes a number of undeniable advantages: Cost Savings. One of the primary driving factors behind the migration from SQL Server to PostgreSQL in the cloud is cost savings. The client used to find it expensive to keep SQL Server databases on-site. This was due to the need for special equipment, software licenses, and skilled database administrators. PostgreSQL, being an open-source database, offers a cost-effective alternative. Customers can save money by using the cloud. They only have to pay for what they use, instead of making big upfront payments. Additionally, they can spend less on operations. Scalability. Cloud computing can scale more easily than on-premises infrastructure to serve greater workloads and more users. For an on-prem system to scale with the needs of a business, organizations had to acquire and install physical servers, software licenses, storage, and network equipment to scale out business services in conventional IT settings. On the cloud, most of these resources are available instantly within a few clicks and can be auto-scaled up and down depending on the resources needed. PostgreSQL in the cloud provides a high level of scalability, allowing our client to easily adjust resources based on demand. Security. Embracing cloud technology offers notable security advantages thanks to the advanced identity controls, access management, and authentication systems supplied by cloud providers. Often cloud providers have better security standards than in-house IT teams or local systems, making the data environment safer. Strong encryption in the cloud reduces the chance of data breaches. It includes layered security, good key management, and secure access controls, which help businesses control user access effectively. Furthermore, cloud providers rigorously oversee physical access, implementing measures such as anonymity, replication, and encryption to fortify data protection. By 2025, approximately are predicted to transition from physical data centers to cloud services. This shift is driven by the enhanced security benefits provided by the cloud. 80% of businesses Moving from SQL Server to PostgreSQL in the cloud requires careful planning and execution, with specific considerations in mind. Based on the client’s project, our team has highlighted the following steps for modernizing SQL Server: Schema and Data Transformation. Data should be transformed accurately to match PostgreSQL's requirements. This may involve handling nuances like date formats and character encoding. Constraints and Triggers. Understanding the distinctions in the usage of constraints and triggers in the two databases is crucial. It is necessary to make the necessary modifications accordingly. In addition, the functionality of triggers can be moved into the application. However, this task is far from simple, so it’s essential to weigh the pros and cons. Performance Optimization. Optimization of the migration process allows to minimize downtime and data transfer time. It’s important to utilize parallelization, optimize network bandwidth, and invest in powerful hardware for efficient migration Data Validation and Testing. Rigorous validation of the migrated data is required to guarantee data integrity and functionality. Extensive testing ensures that applications work seamlessly with the new PostgreSQL database. Security and Permissions. Security settings, user accounts, and permissions in PostgreSQL should be configured to match the original SQL Server setup, ensuring a seamless transition. Why move Business logic to Application Layer? In the past, our customers used stored procedures for their business logic, thinking it would improve performance. But let’s be honest, SQL language may not be the optimal choice for housing the business logic when compared to the application layer. In fact, SQL only queries or modifies the data in a database. Here many can throw rotten tomatoes at us, because SQL language is good at performing complex joins, filtering, and sorting in order to get exactly the data you need out of a query and nothing more. Then why change anything and bring the business logic to the application level? The question seems logical. Let's answer it in more detail. Below we have outlined 4 main reasons why you should seriously think about transferring business logic to the application. The client’s decision to move business logic to the application layer was driven by the following reasons: Scalability For scalability, storing business logic at the application level is the best option. Why? Because, in general, it’s substantially easier to scale your application server resources than it is to scale your database server resources. This is almost universally acknowledged. For most web apps, adding more servers is usually easy when there's a lot of traffic to handle. However, the value of extra application servers diminishes unless your database can also scale to accommodate the augmented demand. Scaling a database server is considerably more challenging than simply adding application servers. Maintainability Storing business logic in a database can create maintainability challenges. Modifying stored procedures can disrupt many applications, limit extensibility, and make it challenging to follow the "Don't Repeat Yourself" (DRY) principle. SQL code that exceeds 100 lines often poses complexities and troubleshooting difficulties. Separating business logic into the application tier can facilitate new team members' entry and provides a more intuitive platform for refactoring. Ease of development SQL is a poor choice for encoding the business rules of your system. It's not flexible and we can't depend on it to represent complex models because it can't create proper abstractions. This limitation is the key reason to avoid using it for business logic. It's not about tooling or support, it's about SQL's inability to create a simple and expressive domain model, in contrast to object-oriented and functional design, which offer more opportunities. Reusability In software development, reusing code is an efficient way to save time and costs when adapting existing code for new tasks. Object-Oriented Programming (OOP) is a method that facilitates code recycling, making it suitable for various applications. However, SQL, commonly used in databases, offers limited flexibility for code reuse. Options include using "views" and "stored procedures," though the latter may lead to an abundance of parameters. To ensure the right choice for your project, exploring each method thoroughly is essential. Converting Transact-SQL to Java Converting Transact-SQL to Java involves various essential considerations. The process includes mapping SQL data types to their Java equivalents, ensuring accurate data representation. It also encompasses translating SQL queries into Java code, where Java relies on libraries like JDBC or Hibernate to handle database interactions. Moreover, certain ESQL features lack direct equivalents in Java, potentially giving the impression that automatic conversion is inefficient. During the customization phase of our project, we were able to create a number of conversion solutions that enhanced the automation rate. These solutions, initially believed to be impossible to automate, ultimately proved successful. Let’s delve into the details of some of them. Converting an INSERT statement in combination with a SCOPE_IDENTITY() to get the last identity value inserted into an identity column. Source: ALTER PROCEDURE example1 AS BEGIN Declare @idBit int Declare @c int Insert Into tab (c) Values (@c) Set @idBit = SCOPE_IDENTITY() End Target: @Service public class Example1 implements IExample1 { @Autowired private JdbcTemplate jdbcTemplate; private static final org.slf4j.Logger LOGGER = org.slf4j.LoggerFactory.getLogger(Example1.class); @Override public Integer spExample1() throws SQLException, Exception { Integer mStatus = 0; KeyHolder keyHolder = new GeneratedKeyHolder(); try { Integer idBit = null; Integer c = null; { final Integer tmpC = c; jdbcTemplate.update(connection-> { PreparedStatement ps = connection.prepareStatement("Insert Into tab(c) \r\n" + " Values(?)", new String[] { "" }); ps.setInt( 1, tmpC); return ps; }, keyHolder); } idBit = Tsqlutils.<Integer > strToNum(keyHolder.getKey().toString(), Integer.class); return mStatus; } catch (Exception e) { LOGGER.error(String.valueOf(e)); mStatus = -1; return mStatus; } } } Conversion of procedure with multi Result Sets. Source: ALTER PROCEDURE [returnSeveralResultSet] @p1 int, @p2 varchar(50) AS Begin select cob_ft, lower(buzon) from tab1 where cob_id = @p1 and cob_ft = @p2 select dep_ft, lower(fiton) from tab2 END Target: @Service public class Returnseveralresultset implements IReturnseveralresultset { @Autowired private JdbcTemplate jdbcTemplate; private static final org.slf4j.Logger LOGGER = org.slf4j.LoggerFactory.getLogger(Returnseveralresultset.class); private Integer errorCode = 0; private String sqlState = ""; @Override public Map<String, Object> spReturnseveralresultset(Integer p1, String p2) throws Exception { Integer mStatus = 0; Map<String, Object> outData = new HashMap<>(); List<SqlRowSet> outRsList = new ArrayList<>(); SqlRowSet rowSet; try { rowSet = jdbcTemplate.queryForRowSet("select cob_ft, lower(buzon) from tab1 \r\n" + " where cob_id = ? and cob_ft = ?", p1, p2); outRsList.add(rowSet); rowSet = jdbcTemplate.queryForRowSet("select dep_ft, lower(fiton) from tab2"); outRsList.add(rowSet); return outData; } catch (Exception e) { LOGGER.error(String.valueOf(e)); mStatus = -1; return outData; } finally { outData.put("status", mStatus); outData.put("rsList", outRsList); } } } Conversion of DATEDIFF method. Since Java has no direct equivalents, the Ispirer team developed an equivalent to this method that casts String to Timestamp without an explicitly specified format. It makes the code result uncluttered and easier to read. The example below demonstrates how it is used. Source: create procedure datediffFn as declare @var1 int set @var1 = DATEDIFF(dd, '1999-01-01', '2000-02-01') set @var1 = DATEDIFF(mm, getdate(), '2000-02-01') set @var1 = DATEDIFF(week, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); Target: public Integer spDatedifffn() throws Exception { Integer mStatus = 0; try { Integer var1 = null; var1 = Tsqlutils.datediff("dd", Tsqlutils.toTimestamp("1999-01-01"), Tsqlutils.toTimestamp("2000-02-01")); var1 = Tsqlutils.datediff("mm", new Timestamp(new java.util.Date().getTime()), Tsqlutils.toTimestamp("2000-02-01")); var1 = Tsqlutils.datediff("week", Tsqlutils.toTimestamp("2005-12-31 23:59:59.9999999"), Tsqlutils.toTimestamp("2006-01-01 00:00:00.0000000")); return mStatus; } catch (Exception e) { LOGGER.error(String.valueOf(e)); mStatus = -1; return mStatus; } } Conversion of sp_send_dbmail method which sends an e-mail message to the specified recipients. For this purpose, a class called MailService was developed. This method makes it possible to send emails with detailed specifications, including recipients (TO), recipients of a copy (CC), recipients in a blind carbon copy (BCC), the subject of the letter, the main text, attachments, and more. To keep the main code uncluttered, our team placed the method in a separate class. Source: create PROCEDURE spSendDbmail AS BEGIN EXEC msdb.dbo.sp_send_dbmail @profile_name = 'New DB Ispirer Profile', @recipients = 'user1@ispirer.com', @body = '<h1>This is actual message embedded in HTML tags</h1>', @subject = 'Automated Success Message' , @file_attachments = 'C:\Temp\Attached.txt', @body_format='HTML', @copy_recipients = 'user2@ispirer.com', @blind_copy_recipients = 'user3@ispirer.com'; END Target: import java.util.*; import com.ispirer.mssql.mail.MailService; public class Spsenddbmail { private static final org.slf4j.Logger LOGGER = org.slf4j.LoggerFactory.getLogger(Spsenddbmail.class); public Integer spSpsenddbmail() throws Exception { Integer mStatus = 0; try { MailService.send("New DB Ispirer Profile", "Automated Success Message", "<h1>This is actual message embedded in HTML tags</h1>", "user1@ispirer.com", "user2@ispirer.com", "user3@ispirer.com", "C:\\Temp\\Attached.txt", "HTML"); return mStatus; } catch (Exception e) { LOGGER.error(String.valueOf(e)); mStatus = -1; return mStatus; } } } The Ispirer team has developed an XMLUtils class for converting xml Data type and its methods, which are used to get any information from XML data stored in an XML variable. An example implementation of the method: Source: create procedure workWithXml AS begin declare @result bit, @myDoc XML, @myStr varchar(1000), @ProdID INT SET @myDoc = '<Root> <ProductDescription ProductID="1" ProductName="Road Bike"> <Features> <Warranty>1 year parts and labor</Warranty> <Maintenance>3 year parts and labor extended maintenance is available</Maintenance> </Features> </ProductDescription> </Root>' SET @result = @myDoc.exist('(/Root/ProductDescription/@ProductID)[1]') SET @myStr = cast(@myDoc.query('/Root/ProductDescription/Features') as varchar(max)) SET @ProdID = @myDoc.value('(/Root/ProductDescription/@ProductID)[1]', 'int' ) end; Target: import java.util.*; import com.ispirer.mssql.xml.XMLUtils; public class Workwithxml { private static final org.slf4j.Logger LOGGER = org.slf4j.LoggerFactory.getLogger(Workwithxml.class); public Integer spWorkwithxml() throws Exception { Integer mStatus = 0; try { Boolean result = null; String myDoc = null; String myStr = null; Integer prodID = null; myDoc = "<Root> " + "<ProductDescription ProductID=\"1\" ProductName=\"Road Bike\"> " + "<Features> " + "<Warranty>1 year parts and labor</Warranty> " + "<Maintenance>3 year parts and labor extended maintenance is available</Maintenance> " + "</Features> " + "</ProductDescription> " + " </Root>"; result = XMLUtils.exist(myDoc, "(/Root/ProductDescription/@ProductID)[1]"); myStr = XMLUtils.query(myDoc, "/Root/ProductDescription/Features"); prodID = XMLUtils.<Integer > value(myDoc, "(/Root/ProductDescription/@ProductID)[1]", Integer.class); return mStatus; } catch (Exception e) { LOGGER.error(String.valueOf(e)); mStatus = -1; return mStatus; } } } Thanks to our customization efforts, our team has successfully developed a range of techniques to automate the transition from T-SQL to Java. This has significantly slashed the overall migration time for the entire project, allowing us to accelerate migration 4 times compared to potential manual migration. Our Toolkit customization not only expedited the transition but also enhanced the project's overall efficiency, showcasing the valuable impact of our customization initiatives. The methods specified in the examples are provided to the client along with the conversion results. Conclusion In conclusion, transitioning business logic from Transact-SQL to Java is a multifaceted process that requires a comprehensive understanding of both languages and their distinct features. In this article we thoroughly explored the migration of business logic to an app layer, and provided valuable insights for those who are planning such a transition. The case of our client proves that such modernization projects can be automated which significantly saves time and effort during migration. Our client's project serves as a compelling testament to the remarkable potential of automation. It demonstrates that there are modernization facets where automation can successfully streamline processes that may initially appear beyond its capabilities. Ultimately, embracing the migration from is a strategic move for organizations seeking greater flexibility, cross-platform compatibility, and scalability. While it presents its challenges, the benefits in terms of portability, performance, and adaptability to modern software architectures make this transition a worthwhile endeavor for those looking to modernize their database systems and applications. Transact-SQL to Java
