Industry Best Practices in Batch Processing

Figure 1 - Best practices in batch processing

Open Source Directory Services and Spring Integrations (Apache Directory Service / Open LDAP / Spring LDAP integration / Apache Directory Studio)

Figure 1 - Secure LDAP Client Server Communication

Directory Service

Directory service is a database that stores information about Organization's users, user groups, assets, departments, user login details including usernames and passwords. This maintain single high available service for  all authentication and authorization. This is very critical system for Organization as it maintain Organizational Structure including Personally Identifiable data.

Open Source Options for Directory Services

There are many Free and Open Source Directory Services and they have their own pros and cons. Below are the most famous Directory Services available for FOSS community. 

1st Option - Linux installation of Apache Directory Services with Apache Directory Studio

          Apache Directory Studio comes with Directory Service with UI for managing Directory Structure

Step 1 : Download https://directory.apache.org/studio/ 

         Step 2 : Extract it and run using below command

                       ./ApacheDirectoryStudio

Change Default Passwords

Step 1 :   First add new Apache Directory Server as below

                   

 

           

Step 2 :   Right click and Open Configurations

You can change LDAP ports , Enable Kerberos as preferred.

Also notice that Admin user's default DC ( Domain Component ) and OU ( Organizational Unit details )

dc=example,dc=com

Step 3 :   Then start Apache Directory Server as below

                Right click on Server and Start

 

 

          

Step 4: Create LDAP connection. Use Secure LDAP port

Right click on Connection -> New Connection -> Use Secure LDAP port -> Select Encryption Method as SSL

View, Verify and Accept certificates

    

     

Step 5: Create LDAP connection. Use Secure LDAP port

Check Admin Account. Use bind user details and password as below. Verify Password and Finish the setup

Bind User : uid=admin,ou=system

Password  : secret

Step 6: Enable Change Password Options

Go to connections -> Select the connection -> Change Edit options from Default to Replace

Step 7: Change Admin Password

Select Admin  user from the Directory Tree -> Double click on password -> Add new password

Select SHA encryption type

Create Users with Password Security

Step 1 : Right click on users and add new Entry. Select inetOrgPerson from drop down and proceed

Step 2 : Add Unique ID for User and enter common name , surname and other user details

Step 3 : Add password for the user. Make sure to encrypt with strong password

Spring Integration with LDAP

Do no use Admin user credential for LDAP integration. Instead you have to use separate account like below

Step 1: Install Secure LDAP certificate in to JRE cacerts using below steps

Generate Certificate

echo -n | openssl s_client -connect localhost:10636 | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > ldapserver.pem

Backup JRE cacert file

cd <JRE HOME>/lib/security

cp cacerts cacerts-original-18-Jan-2023

Create Binary file of Certificate

openssl x509 -outform der -in ldapserver.pem -out ldapserver.der

Import Certificate File to JRE Keystore

keytool -import -alias local-apache-ds -keystore cacerts -file certificate.der

Step 2 : Go to the newly created account and right click on properties.

Find below properties.

DN  : uid=saminda,ou=users,ou=system

URL : ldaps://localhost:10636/uid=saminda,ou=users,ou=system           

Step 3: Use above values for Spring LDAP bean configurations as below

<security:authentication-manager>

<security:authentication-provider ref="LDAPProvider"/>

</security:authentication-manager>

<beans:bean id="LDAPProvider"

class="org.springframework.security.ldap.authentication.LdapAuthenticationProvider">

<beans:constructor-arg>

<beans:bean

class="org.springframework.security.ldap.authentication.BindAuthenticator">

<beans:constructor-arg ref="contextSource"/>

<beans:property name="userSearch" ref="userSearch"/>

</beans:bean>

</beans:constructor-arg>

<beans:constructor-arg ref="grantedAuthoritiesPopulator"/>

</beans:bean>

<beans:bean id="userSearch" class="com.mycompany.CustomLdapUserSearch">

<beans:constructor-arg value="ou=users,ou=system"/>

<beans:constructor-arg value="(objectClass=inetOrgPerson)"/>

<beans:constructor-arg ref="contextSource"/>

<beans:property name="searchSubtree" value="true"/>

</beans:bean>

<beans:bean id="contextSource"

class="org.springframework.security.ldap.DefaultSpringSecurityContextSource">

<beans:constructor-arg value="ldaps://localhost:10636"/>

<beans:property name="userDn" value="uid=saminda,ou=users,ou=system" />

<beans:property name="password" value="XXXXXXX" />

</beans:bean>

LDAP Spring Error Handling

<beans:bean id="authenticationFailureHandlerException"

class="org.springframework.security.web.authentication.ExceptionMappingAuthenticationFailureHandler">

<beans:property name="exceptionMappings">

<beans:props>

<beans:prop key="org.springframework.security.authentication.BadCredentialsException">/?error=Invalid user name or password. Please re-enter.</beans:prop>

<beans:prop key="org.springframework.security.authentication.CredentialsExpiredException">/?error=Your credentials has been expired</beans:prop>

<beans:prop key="org.springframework.security.authentication.LockedException">/?error=Your account has been locked</beans:prop>

            <beans:prop key="org.springframework.security.authentication.DisabledException">/?error=Sorry unable to login with this account, please contact the administrator</beans:prop>

</beans:props>

</beans:property>

</beans:bean>

LDAP Performance Factors

Make sure to retrieve only required information by by constructing exact query

Enable caching with cache evict period in LDAP trees

Better to query the subset of tree hierarchy rather than entire tree hierarchy. 

Correct LDAP tree indexes can increase search performance

Enforce LDAP query timeouts

Security Concerns in LDAP integrations

1. Vulnerable to Man in the Middle Attack

2. Enforce Mandatory SSL/TLS3 communication

3. Server side request validation

4. IP whitelisting and Jump server integration

5. Strong 2 Factor Authentication before comes to LDAP communication

6. Mandatory LDAP binding before proceed with LDAP operations

7. Enforce default security including password encryption

8. LDAP server must be located in secured network

9. Avoid on-prem LDAP deployments as most of Security Threats initiated inside Organizations

10. Make sure to change JDK keystore default passwords

2nd Option - Linux installation of OpenLDAP Services

          Note : No need to install 2 type of directory services. Below is for the experiment only.

          Step 1 : Installation Command

                       sudo apt-get update

                       sudo apt install slapd ldap-utils

Step 2: Check service status 

systemctl enable slapd

sudo systemctl start slapd

systemctl status slapd

 

Step 3: Change Default Admin Passwords

Use below command and enter relevant details as below.

sudo dpkg-reconfigure slapd

Step 4: You can download any available LDAP admin portal that supports Open LDAP 

             Follow similar steps we followed in Apache Directory Services with Apache Directory Studio

Step 5 : You can generate OpenLDAP account passwords using below command

              sudo slappasswd 

Step 6: Install php ldap admin and you can manage Open LDAP tree hierarchy 

https://phpldapadmin.sourceforge.net/wiki/index.php/Main_Page 

Industry Best Practices - DB migrations ( Oracle to EDB Migration )

Moving from Oracle is more crucial when it comes to corporate strategies like reducing license fees, align with open source strategies, cloud readiness and concern related to vendors. 

 

Oracle and EDB strengths and weaknesses.

 

Figure 1 - Comparison between EDB and Oracle 

Oracle to EDB - Cost / Benefit Analysis ( As of Year Oct/2022 )

Benefis	Reason
Very low annual licenses fees - 70% to 80% OPEX reduction compared to Oracle exadata platform	Investment cost for single Oracle's Exadata CPU Core will be around 45000 USD per year. Oralce exadata has minimum of 32 cores.   Minimum Investment cost per year for Oracle = 45000 x 32 = 1440000 USD Minimum investment cost per year for EDB= 3000 x 12 = 48000 USD Oracle table partitioning is under licenses fees EDB table partitioning is based on free of charge
Minimum cost for support lifecycle	Oracle support service will be around  20% per core. EDB support services is lesser than Oracle
Code refactoring and  performance tuneup opportunities.	Oracle is extremely powerful RDBMS in terms of security , performances, high availability. It is well established database over decade. EDB will perform well under proper code optimization.
Open-source Architecture	Born Opensource
Support on cloud migration	Cost of cloud migration is extremely lesser than Oracle cloud migration

 Table 1 : EDB migration cost benefit analysis

  

Below are some recommended steps that can be used in Oracle DB migration to EDB Postgress. 

Step 1 : Analyze the Technical depth and gap of the current Oracle Database

It is very important to do a technical depth and gap analysis of existing Database. This provides clear view of the complexity level of the DB migration and it will help you to do proper activity planning.

Key points : 

 1. Some tables can be excluded as they are created due to historical reasons, created due to complain handling and no longer used.

2. Identification of exact code bases / modules are very important

3 All business logic developed in DB level required to be implemented in App level. The DB functionality need to be de-coupled from the application level.

4. Understanding all the customer touch-points and how the data is represented in DB level is very important

5. Understanding the DB object sizes along with the corporate data cleanup policy. This will help to reduced the final cut-over downtime. 

6. Obtain the current transaction load per hour / peek time from Operations team.

7. Below is a sample template can be used to do technical gap and depth analysis.

Oracle Object Types	In Use / Can be removed ?	Related Code-base	Logic Coupled with DB level	Data Attribution	Object size and Data cleanup possibility	Data Types Incompatibility	Current Throughput in peek time
Tables
Indexes
Triggers
Procedures
Packages
Sequences
DB Links
Views

Table 2 - Sample Table for Technical Gap and Depth analysis

Step 2 : Benchmark the current Oracle system performance

Key points :

1. Idea behind the benchmark is not to put the migrated system below the benchmark level. 

2. Setup the live like system in staging environment. Application level and DB level configurations ( db pooling / application threads / cpu / memory / db hardware/ application hardware ) should be similar / known portion of live environment.

3. Know the current user flows  / longest user flows / highest resource consumed user flows and prepare enough test data sets.

4. Know the current system load and how it has distributed in each and every hour of the day

5. Know the peek time / peek time load of the system

6. It is very important to do a Benchmark of the all modules in same time with the real situation

7. Setup a APM tool ( Application Performance Monitoring Tool ) to identify bottlenecks / performance issues of the current system

8. Involvement of the DB team during benchmark will be an advantage as they can provide their view points.

 9. Use additional REST API layer if it requires to execute business logic which are not directly calls via APIs.

10. Benchmark process need to be done with 3 phases. They are performance test, stress test and endurance test. 

11. Simulate the performance test with concurrent user groups that is expected in real situation.

12. Analyze the benchmark reports against the current throughput figures given by Ops Team.

13. If there are any major mismatch, that need to be discussed with Operations Team and Business Team.

14. Finalized the benchmark figures.

Step 3 : Cleanup Oracle DB

Key Points :

1. Main idea behind this step is to reduce the overall cut-over activity time during EDB migration. Also it improves the DB performance and reduce disk usage

2. Align with the client's data purging policy and data backup policy

3. Backup the staging DB to first as it will take time get live system backup

4. Prepare the DB cleanup script. This can include deleting old partitions in transactions tables, history tables, temporary tables

4. Re-indexing will be required after removing partitions

5. Proper functional test round will be required before the live cut-over.

6. Minor changes to the applications may be applicable like db pool optimizations, framework upgrade with minor versions. Also we can address any findings from APM tools in this stage

7. Do a performance test, stress test and endurance test and make sure results are better than benchmark results.

8. Prepare the detailed cut-over activity plan for DB cleanup as there will be a downtime requirement. Align with the client's data purging policy and data backup policy.

Step 4 : Create EDB instance from Oracle DB

Key Points :

1. DB Admin team has to attend this task.

2. Disk type should be optimized for DISK I/Os. Not for REST APIs. DISK should be dedicated one and not the shared one among other apps.

3. Network interface of the DB should not be a shared one.

4. Memory  / Disk space and CPU allocation must be adequate for the DB performance.

5. Other necessary configuration like MAX supporting DB sessions must be adequate. 

6. Once DB is prepared, Cross verification script need to be prepared. This is to compare Oracle DB objects and EDB objects and their data arrangement. 

7. Cross verification of Table row count is mandatory.

Step 5 : Application Module Changes

 

Key Points :

1. Start from the Core module changes.

2. Make sure to upgrade DB pooling libraries. HikaryCP DB Pooling has better performance.

3. Use optimized DB connectivity parameters. Consultation from DB Administrators will be very important.

4. Enforce Idle timeout. Higher Idle connection will reduce the DB performance

5. Maximum supportive session count will be given by DBA team. Make sure to utilize it among all applications effectively.

6. Enforce auto commit to minimize row locks.

7. Remove all test validation queries . Every query require utilize DB session.

8. Enable application level cache with evict time when it reads static data / master data

9. Avoid frequent insert and updates on same table record.

10. Avoid code duplication, specially avoid duplicate DB calls.

11. Use paginated queries rather than reading entire datasets.

12. Use batch insert / updates rather than individual inserts / updates. 

13. Use SQL JOINS to reduce query overhead

14. Create right indexes and enable auto table partitions.

15. Analyze query execution plans.

16. Make sure to use High Available IP.

17. Use concurrency to improve throughput

18. Use asynchronous methods if applicable. Ex: Notifications

19. Avoid DB links and synchronized the relevant data sets via reliable messaging. Ex : Kafka

20. Enable and limit the thread pool size. Make sure total thread count should not reach Max support DB session count.

21. Use APM tool to analyze the thread life cycle. There should be enough runnable threads in order to run the code. Make sure there are no blocking / long waiting threads in thread pool.

 

 

 

 

 

 

 

 

 22. Use APM tool to analyze JVM memory and JVM CPU. Make sure heap memory doesn't exceeded allocated level.

 

 

 

 

 

23.  Use APM tool to identify the time consumption in logic steps.

 

24. Decouple Application logic from DB layer. Implement separate application modules for DB jobs, DB packages, DB procedures

25. Use JMeter to perform the load test in development environment and monitor the system using APM tool.

25. Enforce timeouts for 3rd party API integrations.

Step 6 : QA Verification, Load Test, Stress Test, Endurance Test and Code Optimizations

This phase will evaluate how the changes  and improvments are affected on the system modules and testing team will carryout functional testing and performance testing. 

Key Points : 

1. Performance test need to be carried out for the all scenarios at once. 

2. Individual performance test doesn't provide correct figures of the system performance

3. Start the load test from the benchmark figures. Idea behind benchmark is not to put the migrated system below the benchmark level.

4. Concurrent user thread groups need to be increase every cycle of the load test.

5. Involve DB Administrators to monitor the DB. They can monitor CPU /Memory/ Idle sessions / Max reached sessions, Queries with least performance

6. Make sure APM tool in place to monitor application level thread life cycles, JVM memory, JVM CPU, Time taken in each step of APIs.

7. Align with Client's API performance. Ex: 95th percentile , 99th percentile

8. First perform the load test for 2 hours, 4 hours and 6 hours.

9. Analyze the initial performance test results along with APM tool findings.

10.  Get the view points from DB team regarding the load test.

11. Development team has to attend relevant performance improvements.

API	2 Hours Load Test   Throughput	99th Percentile	4 Hours Load Test Throughput	99th Percentile	6 Hours Load Test Throughput	99th Percentile
API 1
API 2
API N

Table 3 - Sample Table to keep load test result in each cycle.

 

12. Simulate DB overhead and perform the load test. 

13. DB overhead can be done by performing a DB backup on same disk.

14. Enable DB audit options on all select / inserts/ update / delete operations during the load test.

Step 7 : Detail Cut-over Activity Plan

Key Points :

1. Make sure to create cut-over activity plan in a granular level.

2. Early communication to all stakeholders those who are affected this migration is very important. Specially the teams who has involved with Business Intelligence / Revenue Assurances / Admin Portals. They may have to do changes from their end as well.

3. Make sure the Rollback plan is approved by the business. DB team has to get the responsibility of migrating additional data from EDB to Oracle if the rollback activity take place.

4. Cut-over day activity planning and rehearsal is very important as we require to reduce overall system downtime.  

5. Post migration testing preparation is very important with proper data sets.

6. Each cut-over activity need to be assigned with responsible person, target date and its dependent activities.

7. Post migration activity need to be planned with Application Performance Monitoring as well as DB performance monitoring.

8. Sample template for cut-over activity plan. There may be hundreds of activities in Activity plan

Phase	Responsible Person	Status	Dependency	Target Date Time	Start Time	End Time
Pre-Activities
Migration Activities
Rollback Plan
Post Migration Activities

Table 4 : Sample Cut-Over Migration Activity Plan

Step 8 : Testing in Go-Live DB Just before the live migrations.

 

Key Points : 

This step is very important as it will be the final test round just before the migration. 

It is good to have a final test round in staging environment just before the live cut-over.

All table cross checks need to be done in this phase. Oracle and EDB table row counts need to be cross checked.

All sequences need to be updated with next values. This is a responsibility of DB Admins.

Step 9 : Migration Day Live Testing

Key Points :

All Core Modules / Core APIs need to be tested as the first step. 

All Customer Touch Points need to be tested just after the testing Core Modules

Any issues need to be fixed ASAP.

All back office activities / report generations can be tested after above verification.

Step 10 : Rollback decision

Testing team has to present the test results

Business and Operations Team has to get the direction of rollback.

Step 10 : Post Launch Monitoring

 

Key Points :

1. Application Performance Monitoring is very crucial after the DB migration. Thread Life Cycles / JVM CPU / JVM Memories need to be monitored

2. Database Performance Monitoring is need to be done parallel to the application monitoring. Idle DB sessions / Max reached DB session counts / Slow queries and Etc.

3. Monitor the system in peek hours in each day.

4. Better to monitor the system for several weeks if you haven't observed any immediate failures after the migration.