?
Solved

Compatability issues in MS SQL 2008 from 2005?

Posted on 2010-04-04
9
Medium Priority
?
845 Views
Last Modified: 2013-12-12
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!
0
Comment
Question by:msCCare
  • 5
  • 4
9 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 1500 total points
ID: 29728571
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
 
LVL 1

Author Comment

by:msCCare
ID: 29729985
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
 
LVL 1

Author Comment

by:msCCare
ID: 29734142
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:msCCare
ID: 29739752
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 29740220
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
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 1500 total points
ID: 29740521
>> 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
 
LVL 1

Author Comment

by:msCCare
ID: 29744320
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
 
LVL 1

Author Closing Comment

by:msCCare
ID: 31710804
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 29781797
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

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question