SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 IIS Using Named Pipes to SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mfemenel
Professor Frink

USA
1421 Posts

Posted - 10/28/2002 :  13:55:13  Show Profile  Visit mfemenel's Homepage  Reply with Quote
this is a round about sql server related connection. Our web server is using named pipes to connect to sql. It's crushing our performance on the sql server because each connection is being authenticated. My question is, does anyone know how to change IIS from named pipes to tcp/ip so I can eliminate this as one of our many performance issues? Like I said, sql related in a round about way. Thanks!

Mike
"oh, that monkey is going to pay"

1fred
Posting Yak Master

Canada
158 Posts

Posted - 10/28/2002 :  14:07:08  Show Profile  Reply with Quote
Under your microsoft sql server group in programs, select client network utility, and just leave TCP/IP in your enable protocol text box. You can leave both but put tcp/ip first if you want it the be the default

Go to Top of Page

mfemenel
Professor Frink

USA
1421 Posts

Posted - 10/28/2002 :  14:40:33  Show Profile  Visit mfemenel's Homepage  Reply with Quote
probably should have mentioned that I already had the sql side set up correctly, but every connection from the web site is still coming through as Named Pipes. TCP/IP is the first default, named pipes, the second. I could disable named pipes completely on the sql server, but my fear is that if I do this, everyone is going to shut down because TCP/IP was first choice and the server didn't choose it.

Mike
"oh, that monkey is going to pay"
Go to Top of Page

jasper_smith
SQL Server MVP & SQLTeam MVY

United Kingdom
846 Posts

Posted - 10/28/2002 :  14:46:16  Show Profile  Visit jasper_smith's Homepage  Reply with Quote
Change it using the client Network utility ON the web server.
Also this can be set in the ADO connection string

Network Library=DBMSSOCN

HOWTO: Set the SQL Server Network Library in an ADO Connection String
http://support.microsoft.com/default.aspx?scid=KB EN-US Q238949


HTH
Jasper Smith
Go to Top of Page

mfemenel
Professor Frink

USA
1421 Posts

Posted - 10/28/2002 :  15:11:09  Show Profile  Visit mfemenel's Homepage  Reply with Quote
You may already be a winner.

Mike
"oh, that monkey is going to pay"
Go to Top of Page

ksw
Starting Member

24 Posts

Posted - 10/28/2002 :  17:50:02  Show Profile  Reply with Quote
Mike,
Could you give a little more info on the performance issue you're trying to fix? I have a machine (well 4 actually) that has both IIS & SQL Server on it. I've been tasked with improving performance. What's the difference between Named Pipes & TCP/IP with regards to performance? Does your system setup impact if you should or shouldn't make this change??
I'm having a hard time finding info on performance between these two systems so any info you could provide would be greatly appreciated.

Thanks,
KSW


Go to Top of Page

jasper_smith
SQL Server MVP & SQLTeam MVY

United Kingdom
846 Posts

Posted - 10/28/2002 :  18:33:22  Show Profile  Visit jasper_smith's Homepage  Reply with Quote
Not regarding this situation specifically but TCP/IP sockets is a less "chatty" protocol than Named Pipes, there's less comunication between cient and server. It has to be said that this is generally not noticeable on a 100MB LAN, it can become apparent on a WAN. Have a look at "Named Pipes vs. TCP/IP Sockets" in BOL




HTH
Jasper Smith
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 10/30/2002 :  14:20:58  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
quote:
I have a machine (well 4 actually) that has both IIS & SQL Server on it.



KSW, I'd give serious consideration to separating your SQL Server instances to a stand-alone machine and not run SQL on the same box as IIS. That way you can configure SQL to maximum capability on the machine, and it won't be slowed down by a bunch of IIS page requests that are trying to be processed at the same time.

Go to Top of Page

ksw
Starting Member

24 Posts

Posted - 10/31/2002 :  18:02:08  Show Profile  Reply with Quote
Jasper, thanks for the info. I'll look that up.

AjarnMark, you're the second person that's told me that. Do you have any references I can consult about this, books, websites, whatever?

These machines were set up roughly 2 years ago and then there was just one machine so they (these systems are for another department) didn't have any other option but to have both IIS & SQL Server on it. Then as they got new machines to handle the new websites, they just copied the same setup since it seemed to be working. Now we have 3 machines outside our firewall and all talk to the 4th that's inside the firewall. I was asked a month or two ago to help improve the performance of the systems since I'm the DBA on the other SQL Servers that our company uses. I don't know anything about IIS and haven't been able to find any info on the two working together either on the same machine or different ones.

I'd really appreciate any help or info that anyone could give.

Thanks
KSW

Go to Top of Page

jasper_smith
SQL Server MVP & SQLTeam MVY

United Kingdom
846 Posts

Posted - 10/31/2002 :  18:57:07  Show Profile  Visit jasper_smith's Homepage  Reply with Quote
I have to admit I misread the question, I assumed you were talking about IIS and SQL on separate boxes. By default, local connections between an application and SQL Server 2000 on the same server use the Shared Memory Net-Library which is very fast.

The issue with running any application (including IIS) on the same box as SQL is simply a matter of resource contention, mainly CPU and Memory in the case of IIS. Basically you cannot maximise the potential of your server if it's resources are not dedicated to SQL.
Depending on the spec of the server and the load on SQL it can be an option but in an ideal world, SQL Servers should be dedicated to SQL.

You can check what net library is being used by looking at the net_library column of master.dbo.sysprocesses.

HTH
Jasper Smith

Edited by - jasper_smith on 10/31/2002 18:58:15
Go to Top of Page

ksw
Starting Member

24 Posts

Posted - 11/01/2002 :  12:32:02  Show Profile  Reply with Quote
quote:
By default, local connections between an application and SQL Server 2000 on the same server use the Shared Memory Net-Library which is very fast.


Actually, we're running SQL Server 7.0 on Windows NT 4 SP6 on 3 boxes. One box has 7.0 with Windows 2000 SP2. Does that make a difference?

--KSW

Go to Top of Page

ksw
Starting Member

24 Posts

Posted - 11/01/2002 :  12:46:08  Show Profile  Reply with Quote
Well I checked our connections, and when IIS talks to the SQL Server on the same box, it's using Named Pipes(SSNMPN70.DLL). When it talks to a different SQL Server inside our firewall (from outside the firewall) it is using TCP/IP (SSMSSO70.DLL).

--KSW

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000