I am going to put together a list of command line interface (CLI) commands that has helped me over the years in troubleshooting SQL Server virtual/physical machines, especially when we bring in a new SQL VMs in our existing environment and want to make sure that it works and connected to every necessary part of the network.
It is one of my favorite command. It displays variety of information using given switches. Displays active TCP connections, ports on which the computer is listening, Ethernet statistics, the IP routing table, IPv4 statistics (for the IP, ICMP, TCP, and UDP protocols), and IPv6 statistics
netstat –an Netstat with this switch will display all ports a machine is listening including TCP & UDP. It you use –f switch it will display human readable foreign names.
netstat -an | find “1433” SQL Server default TCP port. You can see if it is listening or not on this port. This diagnose can help you if any other vm or machine is not able to reach out to the machine you are troubleshooting.
netstat -an |find “LISTENING” it will display list of all ports your machine is currently listening on .
Displays all current TCP/IP network configuration values and refreshes Dynamic Host Configuration Protocol (DHCP) and Domain Name System (DNS) settings. Used without parameters, ipconfig displays the IP address, subnet mask, and default gateway for all adapters. You can use it with /all switch to display all settings. It can help you when you are trying to find gateway and DNS information or trying to flush DNS.
- Ipconfig /displaydns
- Ipconfig /flushdns
It is old school command line utility and becoming rate in this modern era. One of the frequent use to test the connectivity of a certain SQL server instance from a remote or client machine in order to verify that it can reach out to your production/non-production SQL Server or Web server .
The telnet commands allow you to communicate with a remote computer that is using the Telnet protocol. You can run telnet without parameters in order to enter the telnet context, indicated by the Telnet prompt (telnet>). A common use in DBA world is
telnet prodserver 1433
Upon success, you should see the telnet screen, which determines successfully connectivity to your prodserver.
It traces and print route to a specific internet request. More in detail, this diagnostic utility determines the route to a destination by sending ICMP echo packets (with TTL values) to the destination. TTL is effectively a hop counter. When reaches zero, the router sends an ICMP “Time Exceeded” It helps when you want to find out that where is the problem when troubleshooting a time out request.
It will display information that you can use and determine how request routed.
Most used and famous command among senior operational and production DBAs is mstsc (Microsoft terminal service I believe they have rename to Microsoft Remote desktop services moving forward Windows 2012). It creates connections to Remote Desktop Session Host (RD Session Host) servers or other remote computers; here is very common example to open mstsc session to testserver in full screen mode.
mstsc /v testserver /f
In misalliance, I keep tree and route print commands handy they have their own uses but to good to know about these time to go on google J