Sql-server – How to setup an TCP/IP proxy with traffic interception, for MS SQL traffic in this case

There's really no reason NOT to use SQL Server's native backup functionality. It's great, understands transaction logs, and gives you most of the functionality you need. (Third party SQL Server Backup solutions are great because they work with the APIs that Microsoft has exposed, and usually provide encryption and compression support - and compression not only saves disk space, but typically reduces backup and restore times.)

So yeah... I'd recommend doing backups WITH SQL Server.

And make sure to do REGULAR transaction log backups. (I'd recommend doing them every 15 minutes or less - regular log file backups help keep your log file lean/mean 1 and for some reason end-users are always grumpy when the server crashes and they have to redo all of their work over the last x hours since the last logfile or full/differential backup.) Then, for redundancy purposes, use robocopy, syncback, file system replication or something to move copies of those backups to another location to protect against hardware/disk crashes or fires in the data center and so on.

Feel free to check out these two free videos for more info and ideas:

http://www.sqlservervideos.com/video/backup-options

http://www.sqlservervideos.com/video/sqlbackup-best-practices/

[1] [SQL Server Magazine: Maximing Storage Performance]3

If you have the SPID of the process in question (from using something like Activity Monitor or sp_who or sp_who2), then you can perform this query on the server:

SELECT * 
FROM sys.dm_exec_connections 
WHERE session_id = (SPID);

replacing (SPID) with the actual SPID of your client process. Look at the net_transport column, that will tell you which protocol that process is using to communicate with SQL Server. If all you are interested in is the protocol, just use SELECT net_transport instead of SELECT *.

You could also filter your query by other columns, such as client_net_address which, for TCP connections, will be the IP address of your client machine. You could send this query via ADO.NET interfaces. SQL SMO might also have something.

Here's the MSDN reference page for sys.dm_exec_connections. Note that you will need VIEW SERVER STATE rights on the server to see more than just your current session.

You can also specify the transport protocol in the server directive of your connection string using the following syntax:

server=[protocol]:servername

Examples:

server=tcp:mysqlinstance1 (for TCP/IP)
server=np:mysqlinstance1 (for named pipes)
server=np:\\mysqlinstance1\pipe\pipename (for a specific pipe)