Determining where or how a connection is being made to SQL
-
I am planning a DB server migration. I need to migrate 4 SQL servers into a single SQL2008R2 cluster. I have migrate a couple DB's just fine. I shutdown the app, backup and restore the DB to the new cluster, point the ODBC connection on the server to the new location and I am done. However, I have a lot of app servers that don't have an ODBC connection setup, at least not in the Data Sources ODBC. How can I find where a particular server is making the connection?
I do a sp_who2 on the source DB server and I see the server name connected listed and I see a column for ProgramName. The program names are all over the map and I don't know if that is where I find out how/where the connection is coming from. For example I have one the says .Net SqlClient Data Provider. What this information how do I go about knowing where to make a configuration change on my app server to point to the new DB cluster?
Thank you.
-
It'll depend on how the application was written.
It's a best practice to have all applications on a web server share a single data connection configuration, often in a Data Access Layer. But I've seen plenty of violations of best practices, including having every database connection established with hard-coded connection strings.
You'll need to find out from the people responsible for the applications how they're connecting. It's possible (probable) that different ones connect in different ways.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
You can always check what IPs/Hosts are hitting your server using the DMVs.
For example:
SELECT dec.session_id ,dec.net_transport ,
dec.auth_scheme ,
dec.client_net_address ,
dec.client_tcp_port ,
DEs.host_name ,
des.program_name ,
des.client_interface_name ,
des.login_name
FROM sys.dm_exec_connections dec
INNER JOIN sys.dm_exec_sessions des ON dec.session_id = des.session_id
If you are shutting down your old servers you could always rename them first and create a DNS entry that points to the new server, that way you shouldn't have to update any connection strings.
-
Yeah listen to Nic man he's plays an ace with this dns idea -
http://www.sqlservercentral.com/Forums/Topic1237912-1550-1.aspx#bm1238078
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
Taking Nic's ideas a little further I want to build a history of connections being made to the server over time until we are ready to switch servers :
(we are left with one puzzle to solve - please see below!!)
USE MyDB
//----------------------------------
// Gather current connection information
//----------------------------------
SELECT ApplicationName = program_name COLLATE SQL_Latin1_General_CP1_CI_AS ,
/*CPU = SUM(cpu_time) ,
WaitTime = SUM(total_scheduled_time) ,
ElapsedTime = SUM(total_elapsed_time) ,
Reads = SUM(num_reads) ,
Writes = SUM(num_writes) ,*/
ConnectionCount = COUNT(1)
INTO #application_connect
FROM sys.dm_exec_connections con
LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = con.session_id
GROUP BY program_name
//----------------------------------
// Record any New applications found
//----------------------------------
INSERT INTO dbo.ApplicationConnections
( ApplicationName ,
ConnectionCount ,
LastConnected
)
SELECT ApplicationName ,
ConnectionCount ,
GETDATE()
FROM #application_connect AS appcon
WHERE NOT EXISTS ( SELECT 1
FROM ApplicationConnections
WHERE ApplicationName = appcon.ApplicationName )
//--------------------------------------------------------------------------------
// Applications allready recorded - just update the connected time and connection count
//--------------------------------------------------------------------------------
UPDATE dbo.ApplicationConnections
SET LastConnected = GETDATE() ,
ConnectionCount = b.ConnectionCount
FROM chall_ApplicationConnections a
INNER JOIN #application_connect b ON a.ApplicationName = b.ApplicationName
DROP TABLE #application_connect
SELECT * FROM ApplicationConnections
Thats ok, but now I need to work out how to determine the connection server name, I mean to say using Nic's idea we are going to create a DNS Alias and start updating connection strings to the alias name.
Lets say my alias name is MyDB.MyFirm.co.uk.
So the my question is "Is there anything in DMV that can tell us the server name that was used on connect?"
????????
If we can get this information then we can modify the above script and see if any clients are using the old direct server name connection.
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
host_name from sys.dm_exec_sessions doesn't give you what you need?
-
hostname returns the machine name of the client;
i think he wanted to know if the client resolved the server address, for example as 192.168.1.200 as MyDB.MyFirm.co.uk vs OldServerName vs oldServername.MyFirm.co.uk
i don't think that part is exposed on the SQL side, just the IP;
Servers can listen to more than one IP though, right?
can he add multiple IP addresses to his server, so that
192.168.1.200 is whatever was previously used, but you know that 192.168.1.201 is MyDB.MyFirm.co.uk , and start narrowing down who is connecting via the old IP?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Excellent idea!
In the past I've also run things through a load balancer and it shows the connection coming from there.
-
This sounds interesting Lowell,
I don't know how to add an IP address, but I think have seen you can get SQL to listen on multiple diffrent ports.
So would it be easy enough to create a DNS Alias directing to a port other than the default sql server port?
Whereabouts is the IP address stored in DMV - does it also store the port number ?
If so - we might be in business!!
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
Unfortunately you can't point DNS to a particular port, only to the server (which is where a load balancer would have come in handy). Although saying that you could specify the port in your connection string. That's probably not the best way to go though.
Adding another IP address would be the best way to go. I used to use this to manage client connections one way and admin connections another. That way I could put servers into and out of a pool quickly and easily behind a load balancer without having to have access to the F5 itself.
The local_net_Address and local_net_port from sys.dm_exec_connections will tell you the IP/Port being hit on the SQL side.
-
Well this looks marvelous/awesome ideas.
I just need to learn how to add an IP address and get SQL to listen to it..
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
C# Screw (1/19/2012)
This sounds interesting Lowell,I don't know how to add an IP address, but I think have seen you can get SQL to listen on multiple different ports.
So would it be easy enough to create a DNS Alias directing to a port other than the default sql server port?
Whereabouts is the IP address stored in DMV - does it also store the port number ?
If so - we might be in business!!
you don't need to fiddle with the ports at all. you want the default instance to handle All incoming DB traffic, and just need to be able to back trace who's connecting via the "old" connection, right? that would be easily accessible int eh DMV's that C# Screw is mentioning.
ports are only for when you have multiple instances ont eh same machine, which doesn't apply in this scenario, right?
here's a crappy screenshot form my dev machine, which gets the address form our DHCP server; you'd get with the network guys about which IP's to use, then your server would simply have two or more ip's with static values, and then make sure the network guys put in the FQDN for each IP.
SQL Automatically listens to ALL IP addresses automatically....that's the easy part.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I don't think the DNS Alias will work in this case. I am going from two stand alone SQL servers and two clustered SQL servers, to a new cluster. I can just switch the DNS name, because I have to go from SQLA to SQLCuster\Instance1. Correct me if I am wrong.
-
Is there a trick to using cut and paste on the T-SQL scripts people post on here? When I do cut/paste it makes one long line with all the text.
-
kurtwest (1/19/2012)
Is there a trick to using cut and paste on the T-SQL scripts people post on here? When I do cut/paste it makes one long line with all the text.IE9 cut and pastes crappy; it made me switch browsers exactly because of this issue.
if you are copying from Firefox, i know it pastes clean, not sure about Chrome.
google IE9 copy and paste formatting:
http://channel9.msdn.com/Forums/Coffeehouse/IE9-copy--paste-removes-line-returns
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply