Migration - Upgrades

How to Update Datasource Drivers During Migration

by Aaron Longnion
11 min read
Contents show

Introduction

Upgrading Database connectivity libraries is one of the most impactful parts of an application Migration. Datasource drivers (JDBC, ODBC, .NET providers) sit on the hot path between your code and the database engine. Newer versions bring Performance improvements, Security patches (TLS, hostname verification), compatibility for Managed services, and deprecations that can break older configurations. Treating the driver update as a structured Migration reduces outages and avoids subtle data issues such as timezone drift, precision loss, or transaction behavior changes.

Prerequisites / Before You Start

  • Backups and recovery
    • Full backup of databases; snapshot if on cloud-Managed services (AWS RDS, Azure Database, Cloud SQL).
    • Export current application configs: connection strings, datasources, pooling settings, ORM configs.
    • Version control the current driver dependencies (Maven/Gradle requirements, NuGet packages, Python requirements.txt, Node.js package.json).
  • Version inventory
    • Current database engine and patch level (e.g., PostgreSQL 13.12, Oracle 19c RU).
    • Current driver versions (JDBC/ODBC/.NET) per app and per environment.
    • Java/JDK version or runtime version (.NET runtime, Python, Node) and application server/container (Tomcat, WildFly/JBoss EAP, WebLogic, WebSphere, Spring Boot, Docker base image).
  • Target compatibility
    • Target driver versions recommended by the database vendor for your engine version.
    • Supported TLS versions and cipher suites; FIPS mode if applicable.
    • Authentication method changes (e.g., SCRAM-SHA-256 for PostgreSQL, Azure AD Integrated for SQL Server, Kerberos/GSSAPI).
  • Dependencies and environment
    • Connection pool library versions (HikariCP, c3p0, DBCP2).
    • ORM versions (Hibernate, JPA, MyBatis, Entity Framework, Sequelize, SQLAlchemy).
    • ODBC manager versions on OS (unixODBC iODBC), and bitness alignment (32-bit vs 64-bit).
    • Certificates and PKI: truststore/keystore, CA roots, server cert SANs, mTLS requirements.
  • Testing and rollout plan
    • Staging environment mirroring production.
    • Load testing plan and baseline Performance metrics.
    • Feature flags or config toggles to switch datasources.
    • Rollback plan: how to revert to old drivers quickly.
    • Maintenance window, stakeholder communication plan.
  • Security/Compliance
    • Verify driver signatures/checksums.
    • License implications of new drivers.
    • Secrets storage method (vault/KMS) and rotation plan.
See also  How to Migrate ColdFusion Admin Settings to New Server

Quick reference: Common Driver Targets and URLs

Database Typical JDBC Artifact Min Java Example JDBC URL
PostgreSQL org.postgresql:postgresql:42.7.x 8+ jdbc:postgresql://host:5432/db?sslmode=verify-full
MySQL com.mysql:mysql-connector-j:8.4.x 8+ jdbc:mysql://host:3306/db?sslMode=VERIFY_IDENTITY
MariaDB org.mariadb.jdbc:mariadb-java-client:3.4.x 8+ jdbc:mariadb://host:3306/db?useSsl=true
SQL Server com.microsoft.sqlserver:mssql-jdbc:12.6.x 8+ jdbc:sqlserver://host:1433;databaseName=db;encrypt=true;trustServerCertificate=false
Oracle com.oracle.database.jdbc:ojdbc11:23.4.0.24.x 11+ jdbc:oracle:thin:@//host:1521/service
DB2 com.ibm.db2:jcc:11.5.x 8+ jdbc:db2://host:50000/db:sslConnection=true;

Note: Choose exact versions per vendor support matrix and your runtime.

Step-by-Step Migration guide

1) Inventory and Baseline

  • Enumerate every datasource, driver, and consumer service.
  • Capture production metrics: connection pool utilization, average/95th percentile latency, error rates, and CPU/memory profile under typical load.
  • Save current Configuration and connection strings (including hidden defaults).

Example (export a Tomcat context.xml):

cp /opt/tomcat/conf/context.xml ./artifacts/context.xml.backup

2) Select Target Driver Versions and Read Release Notes

  • Use vendor compatibility matrices to pick the newest supported driver for your DB engine and runtime.
  • Read release notes for:
    • Connection string parameter changes.
    • Default behavior changes (autocommit, SSL defaults, time zone handling, string types).
    • Deprecated settings and renamed classes.
    • Security hardening (hostname verification, TLS min version).

Pro tip: Track “known issues” and “breaking changes” sections and pin them to your change ticket.

3) Prepare Certificates and Security Artifacts

  • Export server CA chain; ensure SANs include the hostname used in JDBC/ODBC URLs.
  • Build or update Java truststore:

keytool -importcert -file server-ca.pem -alias db-ca -keystore truststore.jks -storepass changeit

  • For mTLS, add client keypair to keystore and configure driver parameters (e.g., sslkey/sslcert for PostgreSQL, or clientCertificate for SQL Server on .NET).

4) Update Dependencies in Your Build

  • Maven (JDBC example):
    xml


    org.postgresql
    postgresql
    42.7.4

  • Gradle:

dependencies {
implementation “com.mysql:mysql-connector-j:8.4.0”
}

  • .NET (Package Manager Console):

Install-Package Microsoft.Data.SqlClient -Version 5.2.0

  • Python:

pip install “psycopg[binary]==3.2.1”

  • Node.js:

npm install pg@8.12.0

Commit changes and tag the build.

5) Update Application Server or Container Placement

  • For Tomcat, drop new JARs in $CATALINA_HOME/lib and remove old ones. For WildFly/JBoss, update driver module.
  • For WebLogic/WebSphere, use the admin console and follow vendor doc for driver replacement.
  • In Docker images, pin explicit driver versions and rebuild:

FROM eclipse-temurin:17-jre
COPY libs/postgresql-42.7.4.jar /app/lib/

6) Adjust Datasource Configuration and Connection Strings

  • Validate the new URL Syntax and parameters.
  • Enable secure defaults:
    • PostgreSQL: sslmode=verify-full
    • MySQL: sslMode=VERIFY_IDENTITY
    • SQL Server: encrypt=true;trustServerCertificate=false
  • Revisit time zone and locale:
    • Use serverTimezone=UTC (MySQL) or setTimeZone=true/app-level TZ configuration.
  • Tweak pool settings if defaults changed (HikariCP maxLifetime, leakDetectionThreshold).

Examples:

Tomcat context.xml:
xml
<Resource name=”jdbc/MainDS” auth=”Container”
type=”javax.sql.DataSource” factory=”org.apache.tomcat.jdbc.pool.DataSourceFactory”
driverClassName=”org.postgresql.Driver”
url=”jdbc:postgresql://db.example.com:5432/appdb?sslmode=verify-full”
username=”appuser” password=”${env.DB_PASSWORD}”
maxActive=”50″ maxIdle=”10″ minIdle=”5″ maxWait=”10000″
testOnBorrow=”true” validationQuery=”SELECT 1″/>

Spring Boot application.yml:
yaml
spring:
datasource:
url: jdbc:mysql://db.internal:3306/app?sslMode=VERIFY_IDENTITY&serverTimezone=UTC
username: appuser
password: ${DB_PASSWORD}
jpa:
properties:
Hibernate.jdbc.time_zone: UTC
open-in-view: false
datasource.hikari:
maximum-pool-size: 40
max-lifetime: 170000
connection-timeout: 10000

ODBC (unixODBC) odbcinst.ini:

[PostgreSQL ANSI]
Driver=/usr/lib/psqlodbcw.so
Setup=/usr/lib/libodbcpsqlS.so
UsageCount=1

7) Authentication and IAM Integration

  • If moving to managed services:
    • AWS RDS IAM Auth for MySQL/PostgreSQL requires token-based passwords and sslmode=verify-full.
    • Azure SQL may use Active Directory (MSI/Managed Identity).
  • Test token refresh flows before production.

8) Test Connectivity and Basic Queries

psql “host=db.example.com port=5432 dbname=appdb user=appuser sslmode=verify-full”
mysql –ssl-mode=VERIFY_IDENTITY -h db.example.com -u appuser -p
sqlcmd -S tcp:db.example.com,1433 -d appdb -G

  • Verify driver at runtime (Java snippet):
    java
    Driver d = DriverManager.getDriver(“jdbc:postgresql:///”);
    System.out.println(d.getMajorVersion() + “.” + d.getMinorVersion());

  • Validate time zone expectations:
    sql
    SELECT CURRENT_TIMESTAMP, EXTRACT(TIMEZONE FROM CURRENT_TIMESTAMP);

9) Run Automated Tests and Migrate Incrementally

  • Unit and Integration tests with real DB container or test instance.

  • CI example (GitHub Actions) matrix:
    yaml
    strategy:
    matrix:
    driver: [postgresql-42.7.4, mysql-connector-j-8.4.0]
    steps:

    • uses: actions/checkout@v4
    • run: ./gradlew test -PjdbcDriver=${{ matrix.driver }}

  • Deploy to staging; run load tests matching peak P95.

  • Canary release to a subset of traffic using a feature flag or two datasources side-by-side.

See also  How to Migrate ColdFusion Sessions During Server Switch

10) Review ORM and SQL Compatibility

  • Hibernate dialect mapping may change; verify correct dialect string for the new driver/DB version.
  • Check for SQL deprecations or behavior changes:
    • MySQL: zero-dates handling, ANSI_QUOTES, sql_mode.
    • PostgreSQL: timestamp rounding, bytea hex format.
    • Oracle: fetch size behavior, default ROW PREFETCH.
  • Adjust fetch size and prepared statement caching to optimize latency.

11) Reassess Transaction Semantics and XA

  • If using XA transactions across services, ensure the driver’s XADataSource implementation matches app server expectations.
  • Validate autocommit defaults and transaction isolation level (READ COMMITTED vs REPEATABLE READ).
  • Add explicit setTransactionIsolation if needed to avoid relying on defaults.

12) Observability and Logging

  • Enable driver-level logging for the migration window:
    • PostgreSQL JDBC: loggerLevel=TRACE; loggerFile=pgjdbc.log
    • SQL Server: set applicationIntent and logging per provider guidelines.
  • Capture pool metrics (HikariCP Micrometer) and DB wait events or wait stats.
  • Dashboards: connection errors, SSL errors, timeouts, 4xx/5xx, query latency.

13) Rollout to Production with Guardrails

Rollback example:

  • Keep two images: app:driver-old and app:driver-new; blue/green switch via Deployment manifest.

14) Performance tuning After the Switch

  • Tune:
    • socketTimeout / loginTimeout
    • tcpKeepAlive
    • prepared statement cache (e.g., prepareThreshold for PostgreSQL)
    • defaultFetchSize for large result sets
  • Compare performance baselines; adjust pool max/min and timeouts to maintain SLOs.

Risks, Common Issues, and How to Avoid Them

  • SSL/TLS pitfalls
    • Problem: Handshake failures, “certificate verify failed,” hostname mismatch.
    • Avoidance: Use verify-full/VERIFY_IDENTITY, ensure SANs match, load CA to truststore, disable trustServerCertificate.
  • Authentication changes
    • Problem: Transition to SCRAM-SHA-256 or AD Integrated breaks password auth.
    • Avoidance: Align server auth methods; pretest IAM token flows; ensure clocks synced (NTP) for token validity.
  • Timezone and locale drift
    • Problem: Timestamp off by hours; inconsistent daylight saving behavior.
    • Avoidance: Force UTC at driver or ORM; test roundtrips; check serverTimezone parameter.
  • Default behavior changes
    • Problem: Autocommit or read-only defaults differ; query timeout behavior changes.
    • Avoidance: Set explicit values in configuration; add integration tests asserting behavior.
  • Parameter and URL changes
    • Problem: Deprecated options (e.g., useSSL vs sslMode); renamed parameters.
    • Avoidance: Replace deprecated flags; validate final effective configuration via driver logs.
  • Numeric and string handling
    • Problem: BigDecimal precision, Unicode handling changes.
    • Avoidance: Verify schema types; add tests for edge cases; set sendStringParametersAsUnicode where relevant.
  • XA and transaction manager incompatibilities
    • Problem: Stuck transactions or heuristic outcomes.
    • Avoidance: Validate XADataSource support for the exact driver and app server; consider local transactions if possible.
  • ODBC bitness mismatch
    • Problem: 32-bit app with 64-bit driver (or vice versa) fails to load.
    • Avoidance: Match bitness; use the correct ODBC Administrator (Windows: C:\Windows\SysWOW64\odbcad32.exe for 32-bit).
  • Unsigned or tampered drivers
    • Problem: Supply chain risk.
    • Avoidance: Verify checksums/signatures; source from official repositories; lock hashes in dependency management.

Post-Migration Checklist

  • Connectivity
    • Application starts cleanly with no ClassNotFound or NoSuchMethod errors.
    • Health checks pass; connection pool warms successfully.
  • Security
    • TLS enforced; certificate chain validated; hostname verification enabled.
    • No plaintext credentials in logs; secrets sourced from secure store.
  • Functionality
    • All CRUD paths tested; complex queries and stored procedures executed.
    • File uploads/downloads involving BLOB/CLOB verified.
    • Time-sensitive operations produce correct timestamps.
  • Performance
    • Latency and throughput at or better than baseline.
    • Pool saturation monitored; no connection leaks.
    • GC and memory profiles unchanged or improved.
  • Reliability
    • Failover/failback tested (read replica promotion, primary switch).
    • Network interruptions recover gracefully; retry policies behave correctly.
  • Observability
    • Metrics dashboards green; no persistent driver or SSL warnings.
    • Log levels reset from trace/debug to info/warn.
  • Documentation
    • Update runbooks, diagrams, and dependency manifests with new versions.
    • Record the migration date, versions, and any configuration deltas.
See also  How to Migrate from ColdFusion to Lucee (Open Source)

Practical Examples and Snippets

Validate SSL Mode is Enforced (PostgreSQL)

SHOW ssl;
SELECT ssl_is_used();

(ssl_is_used() is available via extensions or check pg_stat_ssl on server side.)

Example: MSSQL Connection String with AAD

jdbc:sqlserver://db.example.database.windows.net:1433;
databaseName=appdb;
encrypt=true;trustServerCertificate=false;
authentication=ActiveDirectoryManagedIdentity;

Example: Tuning Prepared Statements (PostgreSQL JDBC)

jdbc:postgresql://…/appdb?prepareThreshold=3&binaryTransfer=true

Example: Linux CA Trust for MySQL Connector/J

sudo cp rds-combined-ca-bundle.pem /usr/local/share/ca-certificates/rds-ca.crt
sudo update-ca-certificates

Advanced Considerations

Multi-tenant and Read/Write Splitting

  • Some drivers support ApplicationIntent=ReadOnly (SQL Server) or load balancers for read replicas.
  • Validate transaction consistency and replication lag impact.

Feature Flags for Zero-Downtime

  • Implement a dynamic datasource routing bean (Spring) or a configuration toggle to switch driver/URL at runtime.
  • Gradually shift percentage of traffic; measure and roll back if needed.

Data access Layer Abstractions

  • Abstract connection details behind a repository/DAO interface.
  • Enables swapping drivers with fewer code changes and safer testing.

Minimal Downtime Strategy (Illustrative Plan)

  1. Deploy new code with both drivers packaged but old driver active by config.
  2. Warm new datasource connection in background (no traffic).
  3. Shift 5-10% traffic to new datasource via flag; observe metrics for 30-60 minutes.
  4. Ramp to 50%; continue observation.
  5. Complete cutover; keep ability to revert via config for at least 24-48 hours.

Sample Troubleshooting Commands

  • Validate loading of JDBC driver classes at runtime:
    bash
    jar tf app.jar | grep postgresql

  • Test ODBC DSN on Linux:

isql -v MyDSN appuser ‘secret’

  • Packet capture to confirm TLS:

tshark -f “tcp port 5432” -Y “ssl || tls”

  • Check connection pool state (HikariCP):

/metrics | grep hikari

Change Management Artifacts to Capture

  • Change request including new versions, risk assessment, and rollback.
  • Evidence of vendor release notes reviewed.
  • Test results from staging with baseline Comparison.
  • Approvals from DBAs, Security, and Application owners.

Small but Important Defaults to Revisit

  • Query timeout: set a sane default (e.g., 30s) rather than unlimited.
  • Connection timeout: match expected network performance (e.g., 10s).
  • MaxLifetime vs DB idle timeout to avoid mid-query closure.
  • TCP keepalive within platform defaults to traverse load balancers and NAT.
  • Statement caching and fetch size based on query patterns.

FAQ

What’s the difference between a datasource and a driver?

A driver is the client library (JDBC/ODBC/.NET) that knows how to speak the database protocol. A datasource is the configuration object your application uses to obtain connections, usually wrapping the driver along with pooling, timeouts, and credentials.

Do I need to upgrade my ORM or connection pool when I change drivers?

Often yes. New drivers may require newer ORM dialects or pool Features. Align Hibernate/JPA and HikariCP/DBCP2 versions with the driver’s supported range to avoid subtle incompatibilities and to benefit from performance improvements.

How can I roll back safely if something goes wrong?

Keep the old driver available behind a feature flag or as a separate Deployment image. Ensure configuration toggles can switch URLs and classpaths without rebuilding. Maintain database backups and snapshots, and document the exact steps to restore drivers and configs.

How do I handle 32-bit vs 64-bit ODBC on Windows?

Match the bitness of the driver with the application process. Use C:\Windows\System32\odbcad32.exe for 64-bit and C:\Windows\SysWOW64\odbcad32.exe for 32-bit DSNs. Mixing bitness will prevent the driver from loading.

Can I test SSL and hostname verification before production?

Yes. Use a staging environment with production-like certificates. Set sslmode=verify-full (PostgreSQL) or sslMode=VERIFY_IDENTITY (MySQL) and confirm that changing the hostname or certificate breaks the connection as expected, then restore correct settings to pass.

About the author

Aaron Longnion

Aaron Longnion

Hey there! I'm Aaron Longnion — an Internet technologist, web software engineer, and ColdFusion expert with more than 24 years of experience. Over the years, I've had the privilege of working with some of the most exciting and fast-growing companies out there, including lynda.com, HomeAway, landsofamerica.com (CoStar Group), and Adobe.com.

I'm a full-stack developer at heart, but what really drives me is designing and building internet architectures that are highly scalable, cost-effective, and fault-tolerant — solutions built to handle rapid growth and stay ahead of the curve.

View all posts

You may also like