In order for ColdFusion to handle database failures that failover to another database, ColdFusion must be aware of the alternate database servers. This can be configured using a JDBC connection to the database and specifying the AlternateServers in the JDBC URL. Below outlines how to configure the Data Source connection in ColdFusion.
Minimal Requirements: JRun 4 Updater 6 or higher
Data Source Name: xxx
Driver: Other
CF Data Source Name: xxx
JDBC URL: jdbc:macromedia:sqlserver://xxx.xxx.xxx.xxx:1433;databaseName=xxx;SelectMethod=direct;
sendStringParametersAsUnicode=false;MaxPooledStatements=1000;AlternateServers=(xxx.xxx.xxx:1433,xxx.xxx.xxx.xxx:1433);ConnectionRetryCount=xx;ConnectionRetryDelay=xx
Driver Class: SQL Server 2000/2005
User Name: xxx
Password: xxx
Description: [optional]
Advanced Settings:
Maintain Connections:
JDBC URL Explained:
- jdbc:macromedia:sqlserver://
- This specifies the driver and initializes the connection string.
- xxx.xxx.xxx.xxx
- The IP Address of the Primary Database Server
- 1433
- The port to the Database Server, typically 1433 for SQL Server, but could be different.
- databaseName
- The name of the database on the server.
- SelectMethod
- Determines whether database cursors are used for Select statements.
- Available methods are:
- direct
- cursor
- Changing to cursor can negatively affect performance.
- Available methods are:
- Determines whether database cursors are used for Select statements.
- sendStringParametersAsUnicode
- Determines whether string parameters are sent to the server in Unicode or in the default encoding of the database server.
- MaxPooledStatements
- The maximum number of cached prepared statements created by the application.
- AlternateServers
- A comma delimited list of IP/Port combinations that make up the alternate servers. This must be in parenthesis as shown.
- ConnectionRetryCount
- How many times to try and connect to each server in the list, starting with the Primary Server listed in the connection string.
- A typical setting is 2.
- How many times to try and connect to each server in the list, starting with the Primary Server listed in the connection string.
- ConnectionRetryDelay
- The number of seconds before retrying a connection
- A typical setting is 5.
- The number of seconds before retrying a connection
You also don’t have to use the full JDBC connection string if you don’t want to. You can simply add the AlternateServers string into the “Connection String” field in the Datasource screen. Makes it super easy to add failover to an existing Datasource with zero down time. AlternateServers=({server ip}\{instance name}:{port})
Thanks Doug & Daniel. I’d been looking at how this can be achieved without having to write complicated application code to handle it or dynamically changing the mirrored server’s IP or servername. Can’t believe it’s this simple. :-)
Just out of curiosity, why “Maintain Database Connections = No”? I’ve always the JDBC driver with Maintain = Yes. i.e. jdbc:seefusion:jdbcwrapper:{jdbc:macromedia:sqlserver://XXX.XXX.XXX.XXX:1433; databaseName=XXXX; SelectMethod=direct; sendStringParametersAsUnicode=false; MaxPooledStatements=1000}; And then Advanced Settings: Maintain DB Connections = Yes