SQL 2005 Cluster + FreeTDS + UnixODBC

Question

SQL 2005 Cluster + FreeTDS + UnixODBC

Kirill 'Big K' Katsnelson

SSC Enthusiast

Points: 125
More actions
May 24, 2006 at 4:59 am

#190697

SQL 2005 Cluster + FreeTDS + UnixODBC: Is this configuration a road to a trouble?
A client has a few Web servers on Linux and is using FreeTDS and UnixODBC to connect to the database server, SQL2K on a sngle node. We are planning a server upgrade, and strongly considering a failover cluster, Windows Server 2003 and SQL 2005, Active/Active. Is this configuration:
1. Going to work
2. Troublesome
3. Crazy
4. All of the above.
Any practical advice would be appreciated too. I have never installed a SQL cluster (although I am planning to play with one made out of two cheap nothing and one SCSI disk). I turned the net inside out trying to find answer to this question (you noticed? I am sorry, that was me...)
Thank you guys in advance!
—kkm
kkm@pobox.com
May 29, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

You should still be able to connect to the SQL Server using that setup if you've made successful connections to SQL Server 7.0 and 2000 servers. One of the things you can do is setup a non-clustered install of the database engine and see if you're able to get things connecting. That'd save you the trouble of trying to troubleshoot how to make it connect and dealing with the cluster.

K. Brian Kelley
@kbriankelley

zootie Say Hey Kid Points: 712 More actions · Answer 2

I agree with bkelley. Setting up a standalone is a good way to work out any issues before adding clustering.

I'm not familiar with FreeTDS and UnixODBC, so I don't know if this applies. One area that could be a problem when connecting to a clustered SQL server is that you usually have to connect to a named instance (rather than to the default port). Some SQL clients (like some ODBC implementations) are not aware of named instances, and you have to specify a port (and configure your SQL instance so it always runs on that port).

On a cluster, there can only be 1 SQL instance that runs on the default port. All other instances have to be named instances on different ports. On an active/active configuration, you'd normally would have 2 SQL instances (and only one can be default)...

That being said, SQL is one of your nicer cluster applications, and it works very well. Give it a try on a controller environment (you can always setup a cluster with VMWare, just be mindful that it will be slow)

Other alternatives include SQL 2005's mirroring and SQL 2000 transactional replication...

Kirill 'Big K' Katsnelson SSC Enthusiast Points: 125 More actions · Answer 3

Site Owners: « the original poster will appreciate any thoughts you have! »

Very much indeed, and thanks to everyone who have responded!

bkelley: « You should still be able to connect to the SQL Server using that setup if you've made successful connections to SQL Server 7.0 and 2000 servers. »

Yes, as far as I understand, they have already confirmed that connection to SQL Server 2005 over TDS works as good as it does with the current SQL 2000 server.

zootie: « you usually have to connect to a named instance (rather than to the default port). Some SQL clients (like some ODBC implementations) are not aware of named instances »

I suspected that; from the ...From 500 ft article, I learned that even MDAC 2.6 has had problems with named instances. I only hope that even if named instance connections are unsupported in the current FreeTDS, it must not be very intricate a hack to add this support, especially a private one (so that, e.g. hard-coding instsnce name is not a crime )

Am I missing something obvious (or not very) that I would trip over if attempting to implement a quick-and-dirty fix? I could not find time to study the sources, unfortunately, so this remains, at the moment, an unelaborated and unfounded guess.

zootie: « you can always setup a cluster with VMWare »

h0ly wh00tsy!!! How can I do that? I thought that the SQL servers in a cluster needed access to common volumes, and also that VMWare did not support such a configuration. Oh please please tell me I was wrong!

—kkm
kkm@pobox.com

zootie Say Hey Kid Points: 712 More actions · Answer 4

http://www.vmware.com/support/gsx3/doc/ha_configs_clusterbox_gsx.html

You can see VMWare instructions for GSX 3.2 for a "cluster in a box", they should apply to Workstation and to VMWare Server beta.

http://techrepublic.com.com/5100-1035_11-1058354.html

http://techrepublic.com.com/5100-1035_11-5034801.html

Step by step instructions for the cluster, and then to install SQL on it.

http://cs.thefoleyhouse.co.uk/blogs/karl/archive/2005/04/27/447.aspx

http://www.vmware.com/community/thread.jspa?forumID=26&threadID=11160&messageID=141313

Some differences between VMWare Workstation 4.5.2 and 5 (it might have been during the beta)

There are other sources, just search google for "VMWare Cluster", and do some searches in VMWare's forums

(Am I the only one that hates this BBS SW? In Opera it has some problems, and in IE, it drops my posts)

Kirill 'Big K' Katsnelson SSC Enthusiast Points: 125 More actions · Answer 5

zootie, you are a genius! I googled up all references just having sent my last reply, but the truth is I would never think of buiding a cluster of virtual machines; that is even more puzzling since I am running an instance of GSX Server, for all my test setups, for a few years! I planned to build a hardware cluster out of old motherboards and a couple of SCSI drives, and man did not I think all the way “if only I could build the cluster in VMWare as I get therefreshly installed machines!"

I have GSX server 3.1; my first attempt to build the cluster ended in a complete fiasco: both the quorum and shared data disks got trashed when I tried to failover the newlyset up and pathced SQL server. I made an error, but I cannot say whether it wasgrave enough to destroy even the quorum drive during the next failover. Specifically, I failed over the empty cluster, with only the cluster group, quorum drive, name and the IP address, but with the shared drive “connected” to the “bus,” and unassigned.

I decided to do next attempt with the VMware Virtual Server beta (RC, precisely ) instead, set it up on an XP workstation, and had the A–P cluster running in 3 hours! There is an additional setting required for VServer, for this beta release only:diskLib.dataCacheMaxSize="0" (but misspelled in the PDF manual, so google for dataCaceMaxSize.) Since the cluster is a half of the target A–A cluster, I installed a named, non-default instance of SQL server: RINK on avirtual server RINK. Thus, sqlcmd -S RINK\RINK -E -Q"" succeeds.

Next I build FreeTDS 0.63 on a Linux box out of a source distro with ./configure --with-tdsver=4.2, since MS SQL servers do not support TDS 5.0, the default of FreeTDS, and FreeTDS does not implement TDS 7.0 or 8.0 (it is also possible that SQL 2005 uses yetanother new verison, which must be numbered 9.0 then).

FreeTDS does not support integrated authentication, of course. It works over TCP/IP, and I do not think that it supports any other transport. I fixed the server port, hic, 1113, by blanking out “TCP Dynamic Port” configuration setting for all IPnand IPAll, and “TCP Port” for all IPn, and setting it to 1113 for the only entry, “TCP Port” under IPAll. Restart server by bringing its resource offline then online in the Cluster Manager.

Next, I connected to the server from the Linux box:

  % ./tsql -H RINK -p 1113 -U sa -P sasa
  locale is "en_US.UTF-8"
  locale charset is "UTF-8"
  1> use master
  2> go
  1> select name, filename from sysdatabases
  2> go
  namefilename
  masterS:\MsSqlData\MSSQL.1\MSSQL\DATA\master.mdf
  tempdbS:\MsSqlData\MSSQL.1\MSSQL\DATA\tempdb.mdf
  modelS:\MsSqlData\MSSQL.1\MSSQL\DATA\model.mdf
  msdbS:\MsSqlData\MSSQL.1\MSSQL\DATA\MSDBData.mdf
  MINITSTS:\MsSqlData\MSSQL.1\MSSQL\DATA\MINITST+D.mdf
  1> ^D

Finally, I failed the SQL server over to another node. As expected, RINK still resolved into the address of the virtual SQL server, and the latter became responsive on the same fixed port, so the above test repeated literally.

zootie:« Am I the only one that hates this BBS SW? In Opera it has some problems, and in IE, it drops my posts »

I habitually dislike web message boards. Once upon a time, we had NNTP. NNTP is very vell suited for public discussions; it is lightweight, and capable of MIME-encapsulation, so that it can deliver everything e-mail can — text, picture, HTML, whatever. It provides threading of messages, and unique in the world, like GUIDs are, message identifiers.

NNTP standard did not provide for search of the server, but there are XSEARCH extensions on some current servers, and the extension XOVER has became a de facto standard. Even avatars are supported using the X-Face: header!

NNTP transmissions are archived by Google, adding on the Déjà View archive. Web boards cannot be archived as logically: they have no robot-readable markers of temporal order, neither those of threading/references.

Anyhow, later, we contracted a habit of scribbling at the web boards. Due to its heavy formatting requirements, one needs 20–100 times more hardware for a discussion board than for an NNTP server for the same client load (the same number of client reading posts at same tempo.) Eeeek... And NNTP news readers are oh so easy to navigate! You have new articles marked, you navigate with one keystroke down the thread, you can place flags on a message, and so on and on. Web boards require a mouse. I can continue the rant till I run out of this Web page...

But the most important point, many people see the problem in that, and therefore you have “semantic web,” an amorphous yet idea of separate content and presentation. And we are covering web boards with scribble, discussing how to separate presentation from contents in a net-wide discussion vehicle? Anyone with a better way to kill time, please?

We do not want a better technology. We want a new technology. Even when it is obviously, patently worse. For a reason unknown, we equate new with better, however completely unfounded in logic. In my pessimistic time, I think that we are doomed to dwell among ugly, unwieldy, pitiful mutants of technology — but never seen before, and existing because of the sole virtue of having been never seen before!

—kkm
kkm@pobox.com

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 6

I have GSX server 3.1; my first attempt to build the cluster ended in a complete fiasco: both the quorum and shared data disks got trashed when I tried to failover the newlyset up and pathced SQL server. I made an error, but I cannot say whether it wasgrave enough to destroy even the quorum drive during the next failover. Specifically, I failed over the empty cluster, with only the cluster group, quorum drive, name and the IP address, but with the shared drive “connected” to the “bus,” and unassigned.

Hrm, I've never had any issues building out clusters on VMware GSX Server. There are additional settings that are required to make the shared storage usable by both sides. I wonder what caused the issue.

K. Brian Kelley
@kbriankelley