Home Services Partners Company
Making the impossible easy: failover for any application!

Making the impossible easy: failover for any application!

July 17, 2009 0 Comments

Anyone writing a modern day database application needs to think about high availability. The real problem is that it is complicated and expensive to code. For example, you could setup Oracle’s RAC environment ($$) but you’ll be faced with writing a whole lot of complicated, Oracle-specific code to support Oracle TAF. Additionally, if you have an existing application using a non-RAC replication solution TAF won’t work (TAF requires a RAC setup). Your other option is to simply use the DataDirect driver with your code and not have to worry about using Oracle RAC or Oracle TAF! I’m sure you’re leery and want to see it in action. Along those lines I wrote some code so you can demo the technology using a small Java program.

In this scenario, a company is writing a web application to browse through golf courses here in North Carolina. The country clubs that the company is working for expect a seamless experience for their users (i.e. low tolerance). The data is replicated on two different servers and users get to browse through the course catalog (page per course), and each page displays course information from the database. The developer wants to ensure that the application doesn’t experience any errors or hiccups while a user is browsing the catalog. They want to make sure that if the database connection to the primary server fails while fetching course 5, the user gets course 6 when he hits the next button – not an error!

In order to run the demo yourself, there are a few things that you’ll need:

  • The application code is (here). The marketing guys want to know if you’re interested, so fill out the form – it’s not long, I promise!!
  • The DataDirect Snoop application available here.
  • A free evaluation download of the DataDirect Connect drivers, located here.

Nobody likes to start off with some code and have to come up with the database preparation themselves, so we’ve made it really simple. The demo code has a simple setupDatabases() method to do just that. However, instead of executing a bunch of insert statements we’ve included a csv file that contains all the data needed for the example. We then take the csv file and bulk load it into the Oracle database (this is really fast – especially for large resultSets!). Loading a table this way is just a few lines of code:

[cc lang="java"] // Load the data into the table from a csv file System.out.print("Loading table from bulk data file..."); com.ddtek.jdbc.extensions.DDBulkLoad bulkLoader = com.ddtek.jdbc.extensions.DDBulkLoadFactory.getInstance(con1); bulkLoader.setTableName(tableName); [/cc]

This is much better than the for loop or other many-lined insert (think a bunch of PreparedStatements)and takes the hassle of moving tables from one database to another. However, this demo is really all about failover, so if you’re interested in learning more about bulk loading large data sets then you can learn more in our DataDirect JDBC User’s Guide and Reference available here.

Now that we’ve setup our primary database and replicated the data on our alternate database (using bulk load) we are ready to test failover. If you pull the cable from the back of the machine or actually crash the primary server you may get undesirable responses from co-workers or your IT department, so let’s get creative! The snoop utility we provide to customers is specifically designed to help our support engineers gather data about the wire level traffic between the driver and server; however, it can be used to simulate a database failure too. To accomplish this, we only need to start the snoop program and have it sit between the application and primary server. This way we can just kill the window running snoop and it effectively terminates the connection with the primary server. Let’s go ahead and set the snoop utility to listen to port 1521 on our local machine, connecting to our primary server:

image

Next we have to set our connection URL such that the primary connection goes through the snoop listener on our local machine so when we close the snoop window it effectively simulates a connection failure. Here is what our URL will look like:

[cc lang="java"] static String URL1 = "jdbc:datadirect:oracle://nc-jdavis:1521;databaseName=CP31;user=test01;password=test01;"; // Connect to the database Connection connection = DriverManager.getConnection(URL1 + "failoverMode=select;failoverPreconnect=true;" + "AlternateServers=(nc-lnx02:1521)"); [/cc] Note that the primary server is to my local machine (nc-jdavis) which goes through snoop to nc-lnx08 (in the snoop window). In the URL we add on our failover options to indicate that nc-linux02 is the alternate server and we want the select option for failoverMode. Setting failoverMode=select indicates to the driver that we want to failover seamlessly while going through the data – i.e. “Make it look like I never got disconnected!”. Additionally, we set a small performance option, failoverPreConnect, which causes the driver to connect to both the primary and alternate server during the first connect. This saves our application from incurring the cost of connecting during the failover process. It isn’t much, but at runtime every bit counts!

So, let’s take a look at the code that displays the results:

[cc lang="java"] while (results.next()) { for (int i=1; i <= numCols ; i++) { System.out.print("'" + results.getString(i) + "'t"); } } [/cc]

You will immediately notice that this looks like a standard loop iterating through the results and printing them to the screen. So, how do you know that you failed over successfully? Easy – check the warnings object which will indicate when the failover happens.

[cc lang="java"] /** * This block of code checks for warnings and prints them out. * With this enabled you can see the warning that indicates * that the driver failed over successfully. Without this * printout you don't see anything differently as the failover * process is seamless to the application. */ SQLWarning warning = stmt.getWarnings(); if (warning != null) { System.out.println (); System.out.println ("Error code=" + warning.getErrorCode()); System.out.println("SQL state=" + warning.getSQLState()); System.out.println(warning.getMessage()); stmt.clearWarnings(); System.out.println(); } [/cc]

Why don’t we show something that indicates the failover? We do not want to have to change our code to add failover client side – we want it to work with our middleware out of the box (i.e. – no client code changes). Additionally, if the application was being developed using a packaged application framework (think Hibernate or Cognos) then you can’t change the application code which makes using our failover mechanism easy to incorporate in any application architecture.

Now run the application and notice the output is formatted for easy reading. You see the successful connection information for the server as well as the rows of golf course information:

Connected with Oracle 4.0.027003 (040303.014800) to Oracle 11.1.0.0.0 Database Server Information ---------------------------------------------------------- ServerName: Oracle SeverMajorVersion: 11 ServerMinorVersion: 1 ID(ID) COURSENAME(COURSENAME) ADDRESS(ADDRESS) WEBSITE(WEBSITE) ========================================================================================================== '1' 'Devils Ridge' '5107 Linksland Drive, Holly Springs, NC 27540' 'www.clubcorp.com' '2' 'Finley Golf Course-UNC' '222 Finley Golf Course Rd, Chapel Hill, NC' 'tarheelblue.cstv.com' '3' 'Hillandale Golf Course' '1600 Hillandale Rd, Durham, NC' 'www.hillandalegolf.com' '4' 'Prestonwood Country Club' '300 Prestonwood Pkwy, Cary, NC' 'www.prestonwoodcc.com' '5' 'Pinehurst' '1 Carolina Vista Drive, Pinehurst, NC' 'www.pinehurst.com' '6' 'Lochmere Golf Club' '2511 Kildaire Farm Rd, Cary, NC' 'http://www.thegolfcourses.net/golfcourses/NC/5581.htm' Error code=0 SQL state=01000 [DataDirect][Oracle JDBC Driver]Your connection has been terminated. However, you have been successfully connected to the next available alternate server: nc-lnx02:1521;. '7' 'MacGregor Downs Country Club' '430 Saint Andrews Ln, Cary, NC' 'http://www.thegolfcourses.net/golfcourses/NC/5582.htm' '8' 'Forest Creek Golf Club' '200 Meyer Farm Dr, Pinehurst, NC ' 'http://www.thegolfcourses.net/golfcourses/NC/15017.htm' '9' 'Rock Creek Country Club' '2201 Country Club Rd, Jacksonville, NC ' 'http://www.thegolfcourses.net/golfcourses/NC/5714.htm' '10' 'Jacksonville Country Club' '2201 Country Club Rd, Jacksonville, NC' 'http://www.thegolfcourses.net/golfcourses/NC/5713.htm' -= Successful Completion =-

Putting the logic of failover in the application is tedious and expensive. Let the middleware worry about the failover and repositioning logic so you can focus on satisfying the country club owners! Making sure that the application failover can handle connection failures in a standard way is key to ensuring the stability and uptime required by your customers.

For more on how the DataDirect Connect for JDBC drivers implement failover, go here.

Here is a PDF version of this Blog posting: testing-connection-failover.

Jesse Davis

Jesse Davis

As Senior Director of Research & Development, Jesse is responsible for the daily operations, product development initiatives and forward looking research for Progress DataDirect. Jesse has spent nearly 20 years creating enterprise data products and has served as an expert on several industry standards including JDBC, J2EE, DRDA and OData. Jesse holds a bachelor of science degree in Computer Engineering from North Carolina State university.

Read next SAS ODBC Access to Salesforce, SQL Server and Marketo
Comments
Comments are disabled in preview mode.