Thursday, September 08, 2011

Setting the Driver Straight


Been working on setting up a new environment for SAP IDM 7.1 SP5 (yes I know 6 is available). This installation is notable for me since I am configuring it around:
  • Windows Server 2008R2 64Bit
  • Microsoft SQL Server 2008R2 64Bit

So it's my first pure 64 Bit system.  I'm pretty excited.  So I got the OS installed no problem.  Got SQL installed.  (gotta say, 2008R2 is probably the smoothest SQL install I've done since SQL Server 7.  Very much a fan) Got the IDM DesignTime and Runtime installs completed with no problems.  Installed JAVA 1.6 and Installed the 3.0 SQL Server drivers.  Then the skies began to darken...

Kept getting driver not found errors when I went to test the dispatcher.  VERY Frustrating! Tried regenerating the dispatcher scripts, tweaking the Java configuration from MMC, even took a look a look at the dispatcher's .prop file and jdbcdefs.xml.  Tweaking some of these shed some light on the issue, but did not bring complete success. That's when I started rethinking things and went back to basics.

First I hit up Google and did some searching on SQL Server 2008, JDBC drivers and related topics.  Based on this I decided to roll back to the 2.0 SQL Server drivers and recreate the JDBC connection string.  This time I installed the SQL Server 2005 option instead and I got a little closer.  It seems that when one is using Java 1.6, the level 4 driver (sqljdbc4.jar) is required rather than the level 3 driver (sqljdbc.jar.)


What kept throwing me off in this is that I was reading the options, I read them too literally. What the SQL Server 2005 option should read is SQL Server 2005 and later… The connection string syntax and the driver name that is called see,different starting with 2005. 

I was able to confirm the level 3/4 driver issue  when troubleshooting between the configuration on my various sandbox systems. My latest one is running 1.6 with sqljdbc4.jar and one of my older systems is using Java 1.5.  

So to wrap it up, what I've found is this:
  • Use the Microsoft  SQL Server 2.0 drivers  DO NOT use the SQL Server 3.0 drivers
  • When building the JAVA connection string for an IDM Configuration DO NOT use Microsoft SQL Server option, rather, use Microsoft SQL Server 2005
  • If you are using Java 1.5, you’ll need to use the sqljdbc.jar
  • If you are using Java 1.6, you’ll need to use the sqljdbc4.jar
That's about it for the moment.  I still need to install a trial version of NetWeaver to get the sandbox up and running.  


No comments: