Compatability issues in MS SQL 2008 from 2005?

We're upgrading from MS SQL Server 2005 to 2008. Our website uses PHP on Red Hat Linux, with FreeTDS to access the database. In our testing of MS SQL 2008, our webserver(s) will have a temporary broken connection to the DB server saying that the user/pass has failed. The user/pass is not the issue, but that's what happens when the connection fails. This failure happens after about 10 or less clicks around the website. If you refresh the page a time or two, you can get the site to load again, but this failure will now occur on 3 of 4 clicks around the site. Restarting apache and memcache will put the server back at step 1, rinse and repeat.

FreeTDS Version: 0.64-6.e15.x86_x64

FreeTDS debug logs show the following when the error occurs:

dblib.c:3976:in dbsqlok()
token.c:2252:tds_client_msg: #20004: "Read from SQL server failed.".  Connection state is now 2.
util.c:119:Changing query state from PENDING to DEAD
dblib.c:3995:dbsqlok() marker is 0
util.c:119:Changing query state from DEAD to READING
dblib.c:907:dbcmd() bufsz = 25
dblib.c:4978:in dbfreebuf()
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 25
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 49
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 69
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 88
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 105
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 135
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 149
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 682
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 1122
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 1327
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 1738
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 2143
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 4101
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 6306
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 7081
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 7472
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 7780
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 8035
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 8317
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 8555
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 8769
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 11631
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 14242
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 17686
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 20879
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 21289
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 21430
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 21897
dblib.c:957:in dbsqlexec()
dblib.c:907:dbcmd() bufsz = 22360
dblib.c:957:in dbsqlexec()

We can't tell which side is terminating the connection, why, or what to change. Someone else claims to have a working server that will not have these problems with the DB server, but we cannot seem to replicate it (nor do we have conclusive proof that this is 100% functional). I'm working on getting a packet capture from the DB server for the time being.

Please advise and thanks in advance!
LVL 1
msCCareAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Suspect that you are using older version of your drivers and would recommend you to use latest Drivers either ODBC or JDBC or PHP drivers from here which supports both SQL Server 2005 and 2008:

http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
msCCareAuthor Commented:
rrjegan17,

Just so I know we're on the same page:
DB server is windows server 2003 with MS SQL 2008
Web server is RedHat Linux

With my position, the DB servers are a "look don't touch" item. I have to assume that the DB Server is good to go and the web server is what needs updating unless I can do a test to show otherwise.

I'm hesitant to just upgrade to the latest version of FreeTDS because we used a custom RPM to install it and I would have to compile it from scratch to upgrade - most likely breaking things in the process.

Do you have any tests / checks I can do to gather more information?
0
msCCareAuthor Commented:
rrjegan17,

As a follow-up, my packet capture is pointing to corrupt TCP sessions: bad checksums cause continuous retries and eventually the DB server moves onto the next segment, but the web server never got the ack for the previous segment and it calls for a reset. I'm seeking approval to go about installing some of these feature packs. Will report back.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

msCCareAuthor Commented:
I've pointed the web server to another DB server running MS SQL 2008, but this one has the native client feature installed. The site won't crash on me if I'm already logged in, but to start a new session I get a new error:

Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option

I did some quick googling and I did not have the options some people said I would have in order to enable CLR. I feel like I'm really really close to having this work, how do I enable CLR on the SQL Server?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Applying Latest drivers might help sometimes with the corrupted data packets..
Yet another reason for your corrupted packets might be due to corrupted Network drivers. ( Do check it out and apply any latest patches for network drivers if available)

Reason for why I am asking you to check the above two things is that I had personally faced issues out of those..

And do check whether the TCP port configured to listen for SQL Server or web server is not shared across some other applications since that might also cause corrupted data packets.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option

Just run this code in SSMS to enable CLR..

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Make sure that you have sysadmin privileges to run the above successfully.
0
msCCareAuthor Commented:
Victory! It works perfectly!

Thanks so much for your help. Here are the steps I had to take to get this up and running:

- Install SQL Server Native Client: http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en
- Enable CLR:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Two very simple steps, but it was a lot of work to narrow it down to that. The biggest help for me was the packet capture at the database server, it really told me what was happening to cause a generic yet catastrophic error.
0
msCCareAuthor Commented:
I have previously had no exposure to database administration, so the terms and reasoning was lost to me at first, but some extra reading helped me to understand what I was being told to do. After that, the solutions seem all too clear, but it took multiple resources.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Thought you would be knowing the fundamentals of SQL Server and gave you the guidelines alone without explaining it in depth..
If you would have mentioned that you are a beginner, then would have explained in much more detail..
Kindly let other experts about your level in the question so that any one can guide you accordingly..

And glad to see you researched out those set of things and got it resolved.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.