Are you a SQL Server DBA wanting to Learn Windows PowerShell?

I've been working on PowerShell for quite some time but mostly for systems administration tasks. Since Microsoft has decided to make PowerShell as a common engineering criteria for all server applications being released, every SQL Server DBA needs to know at least what it is and what they can do with it.

This article is a first in this series of introducing Windows PowerShell to SQL Server DBAs. Go check it out

Trying to attach an MDF file to SQL Server 2005?

While this is not recommended especially when you're a bit paranoid about disaster recovery and data integrity, I still find a lot of people doing it. I guess a lot of independent software vendors don't understand the underlying concepts behind the database engine. While it may have worked in SQL Server 2000 using Enterprise Manager, trying to attach an MDF file in SQL Server 2005 Management Studio will fail. To work around this, you can use the sp_attach_single_file_db system stored procedure. This will attach the MDF file and create an LDF file as well. This will only work if your database only has one datafile. Using this if your database has multipe datafiles won't work. You do have to run the DBCC CHECKDB command to validate your database

I'd still prefer the backup/restore approach. Nothing beats having a peace of mind

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)