Why it is necessary to understand the underlying infrastructure even though you're a DBA

I keep highlighting that it is necessary for DBAs to understand the underlying infrastructure as it helps when it comes to resolving issues. I was having connectivity issues with my SQL Server 2005 instance as sqlcmd and osql are both throwing this error

HResult 0xFFFFFFFF, Level 16, State 1SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

This blog post highlights how to troubleshoot connectivity issues with SQL Server and gave me an idea why I am getting the issue. The first thing I did was to run an ipconfig /flushdns to make sure I looking at the correct IP-hostname resolution from my DNS server. Then, I tried to PING the IP address of the SQL Server instance and it worked just fine (I usually add the -a switch on my PING command to include the hostname if it is indeed correct). I tried the reverse - PING the hostname - but my DNS server is returning a different IP for the same hostname. Lokks like the DNS entry for my SQL Server box is a bit screwed up. I logged on to the DNS server to validate and I was right. I modified the IP address of the A record pointing to my SQL Server box, ran another ipconfig /flushdns before doing another PING test to validate the change. Once everything is ok, I was able to connect to my SQL Server instance using sqlcmd and osql. It pays to understand a bit about something not directly related to your work sometimes

Start Windows Services using PowerShell

I start and stop services on my workstation every now and then for testing purposes (well, primarily to stop services I do not use to minimize surface area and free up available resources which are not being used). What I used to do was to run a NET START command listing all the services that are started and piping it to a text file. Next, I'd insert a NET STOP on each line in the list to call the NET STOP command stopping that particular service. Unfortunately, there are cases where I wanted to start a specific service that start with a specific name. An example of this is starting the SQL Server instances running on my laptop. I have like 4 instances which are SQL Server versions ranging from 2000 to 20008. Now, I happen to memorize the instance names of those SQL Server instances, making it easy for me to just run a NET STOP servicename. Although it would be rather easy, imagine doing that on a server with like 9 instances (not to mention memorizing the instance names). I wouldn't want to type the NET STOP command 9 times. Enter PowerShell

Here's a PowerShell script to start all SQL Server instances on the local computer. You can modify the parameters if you want to suit your needs. I've used this to start all the services related to Symantec on my machine as well

Get-Service where {$_.Name -like "MSSQL$*"} Start-Service

(NOTE: There's a pipe character "" between Get-Service cmdlet and Where and before the Start-Service cmdlet. It just does not display here)