Hey everyone, Geon here again with another SQL Something post!
This time we are going to look really quickly at modifying a connection string to have a failover partner. What this means is if you have a database setup that includes Mirroring (circa SQL Server 2008), then you should make sure your applications take advantage of it. Your applications (when configured correctly) can automatically failover to the secondary DB when the current DB becomes unavailable.
Which is pretty cool by itself, but also very useful as it minimizes downtime and any manual intervention needed to point the apps to the failover DB.
And it is really simple to do.
"Musings from a DBA in training." - Geon Bell
Showing posts with label Connections. Show all posts
Showing posts with label Connections. Show all posts
Wednesday, 8 April 2015
Monday, 30 June 2014
Mini Post: Creating and Configuring a UDL file
Good day everyone! And welcome back to SQL Something!
Here I am at the end of the month again, trying to fit in a couple last minute posts. Said I wouldn't do this again. Hopefully I spread them out a bit better next month.
Anyway, without any more delay let's look at creating a UDL file.
Firstly a UDL (Universal Data Link) file allows persons to test connectivity to OLE DB Providers. The OLE DB Provider we are interested in, of course, is the one for SQL Server. This is very useful to quickly test credentials on a machine that does not have SQL Server Management Studio installed, but needs access to a SQL Server instance (example testing SQL Server access from an application server to a SQL Server).
Here I am at the end of the month again, trying to fit in a couple last minute posts. Said I wouldn't do this again. Hopefully I spread them out a bit better next month.
Anyway, without any more delay let's look at creating a UDL file.
Firstly a UDL (Universal Data Link) file allows persons to test connectivity to OLE DB Providers. The OLE DB Provider we are interested in, of course, is the one for SQL Server. This is very useful to quickly test credentials on a machine that does not have SQL Server Management Studio installed, but needs access to a SQL Server instance (example testing SQL Server access from an application server to a SQL Server).
Friday, 21 March 2014
Mini Post: Getting A List of Connections By IP Address
Hello and welcome back to SQL Something!
I was looking at how to view current connections the other day (much like this past blog post) when I think I saw someone mention checking SQL connections by IP address. I thought this would be pretty cool if it was possible, and after some light searching I found this nice, succinct post by Glenn Berry.
I won't post the query here, but suffice to say it does the job very well.
It uses fields found in the sys.dm_exec_connections and sys.dm_exec_sessions views in order to display the client_net_address (the IP address), the host_name and the login_name as well as a count of session_id per login which provides a connection count per login. Awesome sauce.
Finally, there is a second query in the original post by Mr. Berry that gives you strictly a count by login name using only the sys.dm_exec_connections, which is neat (though honestly I'm not too sure how useful; If you can think of a great way the second query can be used, please let me know by commenting).
DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
I was looking at how to view current connections the other day (much like this past blog post) when I think I saw someone mention checking SQL connections by IP address. I thought this would be pretty cool if it was possible, and after some light searching I found this nice, succinct post by Glenn Berry.
I won't post the query here, but suffice to say it does the job very well.
It uses fields found in the sys.dm_exec_connections and sys.dm_exec_sessions views in order to display the client_net_address (the IP address), the host_name and the login_name as well as a count of session_id per login which provides a connection count per login. Awesome sauce.
Finally, there is a second query in the original post by Mr. Berry that gives you strictly a count by login name using only the sys.dm_exec_connections, which is neat (though honestly I'm not too sure how useful; If you can think of a great way the second query can be used, please let me know by commenting).
DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
Tuesday, 4 June 2013
Mini Post: Using sys.dm_exec_connections to Get Info About Current Connections to SQL Server
Hey guys! Another bite sized mini post here!
Today we take a quick look at sys.dm_exec_connections (2012 equivelent here) to get a little info on what/how sessions are connected to our instance.
As stated in the links sys.dm_exec_connections provides server level info on SQL Server connections. Using the below for example:
SELECT top 50 session_id, auth_scheme, connect_time, client_net_address
FROM sys.dm_exec_connections
order by session_id
Would result in the below:
The client_net_address, I find particularly useful as it shows the host address of the client that is connected to instance. Nice. Auth_scheme is also nice as it shows the 'how' of the connection (is it a SQL login etc).
DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
Today we take a quick look at sys.dm_exec_connections (2012 equivelent here) to get a little info on what/how sessions are connected to our instance.
As stated in the links sys.dm_exec_connections provides server level info on SQL Server connections. Using the below for example:
SELECT top 50 session_id, auth_scheme, connect_time, client_net_address
FROM sys.dm_exec_connections
order by session_id
Would result in the below:
 |
| Fig. 1: Results of querying sys.dm_exec_connections |
The client_net_address, I find particularly useful as it shows the host address of the client that is connected to instance. Nice. Auth_scheme is also nice as it shows the 'how' of the connection (is it a SQL login etc).
DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
Subscribe to:
Posts (Atom)