Why moving ColdFusion datasources to a new database server matters

A Datasource Migration is more than a connection string change. It touches application availability, Data integrity, Performance, Security (TLS), and often enables upgrades to modern database engines and drivers. Handling it methodically reduces downtime, avoids subtle bugs around data types or collations, and sets you up for predictable future cutovers.

---

Prerequisites / Before You Start

• Inventory and documentation
  • List all ColdFusion DSNs (names, database type, host/port, database name, credentials, pooling settings).
  • Identify where DSNs are used: Application.cfc (this.datasource), cfquery tags, Scheduled tasks, gateways, reports, ORM (Hibernate), and any custom frameworks.
  • Note advanced settings: isolation level, fetch size, blob/clob handling, query timeout, login timeout, and Character encoding.
• Version and compatibility checks
  • Adobe ColdFusion version (e.g., 2016/2018/2021/2023) and its bundled JDBC drivers (DataDirect/Open drivers).
  • Target database engine/version (SQL Server, MySQL/MariaDB, PostgreSQL, Oracle) and compatibility level.
  • Driver requirements for TLS 1.2/1.3 and server-side Features (e.g., Azure SQL requires encrypt=true).
• Access and Security
  • New database server IP/DNS, firewall openings, network routes, VPN/peering.
  • Service accounts with least privilege; confirm required roles (read/write, DDL, stored procedure execute).
  • TLS certificates for the DB server and a truststore strategy.
  • Secrets storage plan (environment variables, vault, or encrypted secrets in ColdFusion).
• Backups and rollback
  • Full database backups and tested restores.
  • ColdFusion Administrator Configuration backup (CAR package or cfconfig export).
  • Rollback plan that includes reverting DSNs or DNS aliases.
• Environment readiness
  • A staging environment mirroring production.
  • Sufficient Maintenance window and communication to stakeholders.
  • Monitoring and logging in place (ColdFusion Server monitor, FusionReactor, DB monitoring).
• Data Migration approach
  • Chosen method: backup/restore, dump/restore, replication, or cutover with read-only freeze.
  • Validation scripts and Performance baselines.

---

Step-by-step Migration guide

1) Inventory datasources and usage

• In ColdFusion Administrator: Data & Services > Data Sources. Export a list and take screenshots of each DSN’s settings.
• Export Admin Configuration:
  • CAR archive (Packaging & Deployment).
  • Or cfconfig (recommended for Automation):

Using CommandBox + cfconfig

cfconfig export to=cfconfig-backup.json

• Use the Administrator API to list DSNs (example):

admin = createObject(“component”,”cfide.adminapi.administrator”);
admin.login(“yourAdminPassword”);
ds = createObject(“component”,”cfide.adminapi.datasource”);
dsnList = ds.getDatasources();
writeDump(dsnList);

• Search codebase for DSN references:
  • Application.cfc: look for this.datasource or this.datasources.
  • cfquery tags: datasource=”SomeDSN”.
  • ORM settings: this.ormsettings.datasource.

Tip: adopt a DNS alias for the DB host (e.g., db-read.app.local, db-write.app.local) to decouple connections from underlying server changes.

---

2) Prepare the new database server

• Create databases and schemas; set collation/encoding/timezone to match the old system unless intentionally changing.
• Create users and grant least-privilege roles. Ensure execute rights for stored procedures and select on required views.
• Enable required Features: case sensitivity, compatibility level (SQL Server), search/index extensions, timezone settings.
• Network security:
  • Open firewall ports (e.g., 1433 SQL Server, 3306 MySQL/MariaDB, 5432 PostgreSQL, 1521 Oracle).
  • Whitelist application/server IPs and confirm egress rules.
• TLS/SSL:
  • Install server certificate signed by a trusted CA.
  • Obtain the root/intermediate CA chain.
  • Plan truststore import on the ColdFusion side.

---

3) Migrate data

Choose the method aligned with your DB engine and downtime budget.

• MySQL/MariaDB example:

Dump from old

mysqldump –single-transaction –routines –triggers –events \
-h old-db -u backupuser -p –databases appdb > appdb.sql

Restore to new

mysql -h new-db -u root -p < appdb.sql

• PostgreSQL example:

Dump

pg_dump -h old-db -U backupuser -Fc -d appdb -f appdb.dump

Restore

pg_restore -h new-db -U admin -d appdb -c appdb.dump

• SQL Server example (T-SQL):

— On old server
BACKUP DATABASE [AppDB] TO DISK=’\share\AppDB_full.bak’ WITH INIT, COMPRESSION;

— On new server
RESTORE DATABASE [AppDB] FROM DISK=’\share\AppDB_full.bak’
WITH MOVE ‘AppDB’ TO ‘D:\MSSQL\Data\AppDB.mdf’,
MOVE ‘AppDB_log’ TO ‘D:\MSSQL\Log\AppDB_log.ldf’,
RECOVERY, REPLACE;

• Oracle example: use Data Pump (expdp/impdp) and validate synonyms, grants, and packages.

After restore:

• Recreate any linked servers, extensions, sequences, and scheduled jobs inside the DB.
• Run validation queries to count rows and compare checksums for critical tables.
• If using replication or log shipping for a near-zero-downtime cutover, establish it now.

---

4) Validate connectivity and TLS from the ColdFusion host

• Import DB certificate chain into a Java truststore used by ColdFusion:

Prefer a dedicated truststore rather than editing ${JAVA_HOME}/lib/security/cacerts

keytool -importcert -keystore /opt/cfusion/keystores/db-truststore.jks \
-storepass changeit -alias newdb -file /tmp/newdb-chain.crt

• Add JVM args for ColdFusion (cfusion/bin/jvm.config or CF Administrator > JVM Settings):
  -Djavax.net.ssl.trustStore=/opt/cfusion/keystores/db-truststore.jks
  -Djavax.net.ssl.trustStorePassword=changeit

• Confirm network paths using telnet or Test-NetConnection from the app server to the new DB host/port.

---

5) Update driver strategy and connection strings

• Prefer the bundled DataDirect or vendor JDBC drivers compatible with your CF version.
• For advanced features (cloud DBs, modern TLS), ensure driver version alignment.

Driver URL examples:

• SQL Server:

jdbc:sqlserver://db-new.example.com:1433;databaseName=AppDB;encrypt=true;trustServerCertificate=false;loginTimeout=15;

• MySQL 8 / MariaDB:

jdbc:mysql://db-new.example.com:3306/appdb?useSSL=true&requireSSL=true&verifyServerCertificate=true&serverTimezone=UTC&allowPublicKeyRetrieval=false

• PostgreSQL:

jdbc:postgresql://db-new.example.com:5432/appdb?sslmode=verify-full&loginTimeout=15

• Oracle:

jdbc:oracle:thin:@//db-new.example.com:1521/APPDB_SERVICE

Consider using JNDI datasources managed by Tomcat for consistent pooling and externalization, then connect via ColdFusion as “Other.”

Tomcat example (context.xml):

<Resource name=”jdbc/AppDB” auth=”Container” type=”javax.sql.DataSource”
driverClassName=”com.microsoft.sqlserver.jdbc.SQLServerDriver”
url=”jdbc:sqlserver://db-new:1433;databaseName=AppDB;encrypt=true;trustServerCertificate=false;”
username=”cfuser” password=”**”
maxTotal=”50″ maxIdle=”25″ maxWaitMillis=”10000″ validationQuery=”SELECT 1″/>

In ColdFusion, configure a datasource of type “Other” with the JNDI name: java:comp/env/jdbc/AppDB.

---

6) Change the DSNs in ColdFusion Administrator

• For each DSN:
  • Edit host, port, DB name, and security properties (SSL).
  • Verify pooling, login timeout, and max connections.
  • Set “Maintain Connections” and “Enable Timeout” appropriately.
  • Press “Verify All Connections.” Fix errors immediately.

Automate with cfconfig (recommended for repeatability):

Example cfconfig JSON fragment

{
“datasources”: {
“AppDSN”: {
“class”: “com.microsoft.sqlserver.jdbc.SQLServerDriver”,
“connectionString”: “jdbc:sqlserver://db-new:1433;databaseName=AppDB;encrypt=true;trustServerCertificate=false;”,
“username”: “cfuser”,
“password”: “ENV:CF_DSN_APPDSN_PASSWORD”,
“advanced”: {
“maxpooledstatements”: 50,
“loginTimeout”: 15,
“queryTimeout”: 60
}
}
}
}

Apply it:

cfconfig import from=cfconfig-new.json

Or use the Admin API (example for SQL Server):

admin = createObject(“component”,”cfide.adminapi.administrator”);
admin.login(“yourAdminPassword”);
ds = createObject(“component”,”cfide.adminapi.datasource”);
ds.setMSSQL(
name=”AppDSN”,
host=”db-new.example.com”,
database=”AppDB”,
port=”1433″,
username=”cfuser”,
password=”********”,
encrypted=true,
sendStringParametersAsUnicode=true,
loginTimeout=15,
queryTimeout=60
);

Note: function names differ by DB (setMySQL, setPostgreSQL, setOracle, etc.) and CF version; adjust parameters accordingly.

---

7) Update application configuration and code references

• If using a default datasource in Application.cfc:

component {
this.name = “MyApp”;
this.datasource = “AppDSN”;
this.sessionManagement = true;
}

• If DSN names have changed, update code where cfquery references datasource=”OldDSN”.
• Confirm ORM config if using Hibernate:

this.ormEnabled = true;
this.ormsettings = { dialect=”MicrosoftSQLServer”, datasource=”AppDSN”, flushAtRequestEnd=false };

• Use cfqueryparam for type safety and to avoid implicit conversions that may differ across DB versions.
• Review stored procedure calls, resultset handling, and return codes.

---

8) Test in staging with production-like data

• Run smoke tests: login, critical workflows, reports, batch jobs.
• Load-test key endpoints to compare latency, throughput, and connection pool behavior.
• Review logs for SQL exceptions, deadlocks, lock timeouts, and Slow queries.
• Validate timezones and date formats; sample assertions:
  • Count of critical tables matches.
  • Checksums/hashes for selected tables.
  • Edge-case queries for nulls, Unicode, and decimals.

---

9) Plan and execute the cutover

• Freeze writes on the old DB or schedule Maintenance mode.
• Stop queues/Scheduled tasks that modify data.
• Take a final incremental backup/replication sync; promote the new server to primary.
• Switch DSNs to the new server (or flip DNS alias).
• Warm up the application: preload caches, run a health-check query:

SELECT 1 as ok

• Monitor error logs and DB metrics closely for the first hours.
• If issues arise beyond the acceptable threshold, execute the rollback plan (revert DSNs/DNS and restore delta changes if needed).

---

Risks, Common Issues, and How to Avoid Them

• Driver/version mismatches
  • Symptom: Authentication or TLS failures.
  • Mitigation: verify driver compatibility with DB version; use official vendor drivers where required; test encrypt/sslmode parameters.
• Collation and case sensitivity changes
  • Symptom: queries behave differently, unique index violations.
  • Mitigation: match old collation; update queries to be explicit; normalize data before migration.
• Timezone and datetime drift
  • Symptom: off-by-hours dates, DST anomalies.
  • Mitigation: standardize on UTC storage; set serverTimezone/DateStyle; test boundary dates.
• Data type differences
  • Symptom: truncation, rounding, numeric overflow.
  • Mitigation: review schema; adjust cfqueryparam cfsqltype; validate decimals, Unicode, and large text/blob fields.
• Stored procedures and resultset handling
  • Symptom: “result set not returned,” parameter binding errors.
  • Mitigation: validate procedure signatures, return types; update cfstoredproc and cfprocparam usage.
• Connection pooling and stale connections
  • Symptom: intermittent failures post-cutover.
  • Mitigation: restart ColdFusion service after cutover; enable validation query; tune pool sizes.
• Security blocks
  • Symptom: connection timeout due to firewall or network ACLs.
  • Mitigation: pre-approve firewall rules; test connectivity with telnet/openssl.
• Performance regressions
  • Symptom: slower queries After migration.
  • Mitigation: refresh statistics, rebuild indexes, verify execution plans, add missing indexes, tune driver options (sendStringParametersAsUnicode for SQL Server can impact plan selection).
• Unicode handling
  • Symptom: garbled characters.
  • Mitigation: use NVARCHAR/UTF-8; ensure client encoding options; confirm JDBC connection string parameters for Unicode.

---

Post-migration validation steps

• Connectivity and health
  • Verify all DSNs in ColdFusion Administrator show “OK.”
  • Run a simple SELECT 1 on each DSN via cfquery.
  • Review ColdFusion application.log and exception.log for errors.
• Data integrity
  • Compare row counts for key tables between old and new.
  • Validate sample business records end-to-end in the app.
  • Confirm sequences/identity columns increment as expected.
• Performance and load
  • Monitor CPU/IO on DB and application servers.
  • Review slow query logs or Query Store/pg_stat_statements.
  • Validate connection pool usage; watch for saturation or timeouts.
• Features and jobs
  • Ensure scheduled tasks run and use the correct DSN.
  • Confirm report generation, exports, and background jobs.
  • Validate stored procedures and triggers fire correctly.
• Security
  • Confirm TLS encryption in use (e.g., sys.dm_exec_connections encrypt_option = TRUE for SQL Server).
  • Validate least-privilege permissions for service accounts.
• Housekeeping
  • Remove old credentials and firewall rules that are no longer needed.
  • Update runbooks, Architecture diagrams, and secrets managers.
  • Keep a copy of the final cfconfig/CAR export for reproducibility.

---

Practical reference: quick configuration snippets

• Default datasource in Application.cfc:

component {
this.name = “MyApp”;
this.datasource = “AppDSN”; // points to new server
}

• cfquery with parameters:

SELECT id, email
FROM users
WHERE id =

• TLS truststore JVM arguments (in jvm.config):

-Djavax.net.ssl.trustStore=/opt/cfusion/keystores/db-truststore.jks
-Djavax.net.ssl.trustStorePassword=changeit

• Validation query for pools: use “SELECT 1” (SQL Server/PostgreSQL), “SELECT 1 FROM DUAL” (Oracle), or “SELECT 1” (MySQL).

---

Optimization tips

• Use a DNS alias for the DB host to simplify future migrations.
• Externalize credentials: environment variables, vault providers, or cfconfig with ENV: references.
• Enable query caching judiciously in ColdFusion for read-heavy data that rarely changes.
• Leverage JNDI for standardized pooling and DevOps-friendly centralization.
• Establish a recurring CF Administrator auto-export or cfconfig snapshot for drift detection.

---

Minimal-downtime cutover approach (optional pattern)

• Establish replication/log shipping from old to new.
• Sync continuously while QA runs on the new instance using a snapshot.
• Schedule a short freeze window:
  • Stop writes, run final sync.
  • Flip DSNs or DNS alias.
  • Restart ColdFusion to clear stale connections.
  • Post-cutover validation and monitoring.
• Keep the old server read-only for a short fallback window.

---

Troubleshooting Checklist

• Test a raw JDBC connection from a Java tool to rule out application-level issues.
• Temporarily disable connection pooling to isolate driver vs. pool errors.
• Increase logging for SQL exceptions (and consider parameter logging in a safe, masked manner).
• Check DB Server logs for Authentication and TLS errors.
• Validate that the JVM used by ColdFusion matches your truststore configuration (embedded JRE vs. system JRE).

---

Example: migrating via cfconfig end-to-end

1. Export current configuration:

cfconfig export to=cfconfig-old.json

2. Edit datasources in cfconfig-old.json to point to the new host, and save as cfconfig-new.json.

3. Import to staging and validate:

cfconfig import from=cfconfig-new.json

4. Once validated, run the same import on production during the cutover window, then restart ColdFusion:

service coldfusion restart

5. Verify connections and run your application smoke tests.

---

Change management and documentation

• Record driver versions, connection strings, and JVM arguments applied.
• Note database-level configuration (collation, timezone, compatibility level).
• Attach test results, row counts, and performance baselines.
• Communicate completion to stakeholders with any known caveats.

---

FAQ

How can I avoid editing every cfquery when the DSN name changes?

Use a default datasource in Application.cfc (this.datasource = “AppDSN”) so most cfquery tags can omit the datasource attribute. Alternatively, preserve the original DSN name and only change its target host.

Is it better to use JNDI or native ColdFusion datasources?

Both are supported. JNDI offers centralized pooling in the servlet container (Tomcat) and easier externalization for DevOps, while native CF datasources are simpler to set up via the ColdFusion Administrator. Choose based on your ops model and tooling.

What is the safest way to handle database passwords during migration?

Avoid hardcoding. Use secrets managers or environment variables. With cfconfig, reference ENV:VAR_NAME for passwords and inject at runtime. Ensure file permissions restrict access to configuration files.

Why are my date values off by several hours after the move?

A mismatch in timezones or driver parameters is likely. Enforce UTC storage where possible, set serverTimezone/DateStyle in JDBC URLs, and verify both DB and application server timezones. Test DST boundary dates explicitly.

Do I need to restart ColdFusion after changing datasources?

Not always, but it’s recommended for a cutover to clear stale pooled connections and ensure new TLS/truststore settings take effect. If you updated JVM arguments (trustStore), a restart is required.