SQL Server Reference Guide

Although you can do most everything you need using Transact-SQL (T-SQL) commands or with the Common Language Runtime assemblies in SQL Server 2005, having a good grasp of scripting techniques is still essential to the Database Administrator (DBA). I've defined scripting as those technologies that you use outside of SQL Server to perform maintenance and other tasks not only in SQL Server but also for the operating system or other parts of your entire landscape. Since SQL Server runs on the Windows operating system, the DBA is often responsible for either the server, or at least for performing his or her own tasks required at the operating system level.

You can, with a little work, view the operating system from the SQL Server perspective. Using the xp_cmdshell extended stored procedure, SQL Agent tasks that can talk to the operating system, SQL Server Data Transformation Services (DTS) packages, SQL Server Integration Services (SSIS), or even the Common Language Runtime (CLR); you can get to the operating system files and services with various levels of capability. But there are reasons that these processes aren't always the best solution.

The xp_cmdshell, like all extended stored procedures, is run "out of process" from SQL Server. That means there could be stability issues if you run something in the operating system that steals excessive memory or processor time. Not only that, the xp_cmdshell is a gaping security hole, and you should either restrict its use or (in SQL Server 2005) remove it entirely. I don't recommend using it. The other issue with this stored procedure is that it can't communicate between SQL Server and the operating system. That means if you need to run a process in the operating system and wait for it to complete, you have to do a few gymnastics to make SQL Server understand the task has finished in the operating system.

The SQL Server Agent does a little better with these issues, and DTS and SSIS works well for operating system tasks — to a point. You run into issues with these tools because they can only communicate directly with what the operating system exposes. When you need to talk to the system Registry or the Windows Event Logs, there are no native commands available.

You have a lot of power in the CLR. Just about anything you can do with a programming language like C# or Visual Basic .NET is available to you in the CLR. However, many DBAs aren't developers. Sure, we can learn to toss around a little code, but when things get a little complicated, we simply don't have time to learn an entire programming language just to talk to the Windows Event Logs. You also have to be familiar with the safety levels in the CLR, and transmit that information to the environments where you use the assembly. Not only that, you need to develop the CLR and compile it in a full development software package. There are ways around this, but the whole process gets a bit sticky for the average DBA.

Another issue with all of these methods is that they view the world from the SQL Server perspective. Even when you can do simple operating system commands, it doesn't help if you have another database system such as Oracle or MySQL installed and you need to do the maintenance all at once. You really need to view the tasks from the operating system, since it's the common denominator.

Enter scripting, and in particular, PowerShell. I've explained the general benefits of scripting in another overview, and in this tutorial I'll explain a practical use of PowerShell specifically. That's how I'll approach the scripting tutorials, since I've found having a practical exercise where you can use the scripting technology is a great way to learn it. In another series of tutorials I explained how to use vbscript to access Data Management Objects (DMO) to script out databases and their objects. You can use those techniques to solve real-world problems like finding the differences between two systems.

In this tutorial series I'll explain how PowerShell works, and how you can use it to solve another problem. The issue we'll tackle in this series is to gather some system information, determine when a backup is needed, take, compress and ship the backup to another server, and then apply the backup to that server. Along the way we'll record each step in the Windows Event Logs so that the other system administrators can have a track record of our activities.

Just as I mentioned in the tutorial on vbscript, the scripting technology gives you a vehicle to other access methods. You can think of this process in layers. The first layer is the operating system. The second layer is the scripting language, or in the case of PowerShell, the scripting environment. The next layer is the access method you'll use to get to the database. There are lots of those, including Open Database Connectivity (ODBC), Data Management Objects (DMO), Active Database Objects (ADO), and in SQL Server you can use the SQL Server Native Client (SNAC) or Server Management Objects (SMO). Lots of acronyms represent lots of ways to access SQL Server and the operating system. Check out this diagram:

Figure 283

Notice at the bottom layer is the operating system. This might even be a variable for you, since you may have not only Windows but other operating systems in your environment. If this is true for you, then you may want to investigate a scripting technology that runs on all of them, such as Perl. I'll cover Perl in another tutorial.

At the next layer is the scripting technology. In the case of vbscript, java, Ruby or Perl, this is a programming environment that you "enter" and "leave" when the program is complete. That's where PowerShell shows its differences and its power. PowerShell isn't a programming language – it's an entirely new shell. A shell is an environment where operating system tasks run. If you're using a server operating system, then you're probably using the CMD.EXE shell. Shell environments run things like DIR, TYPE, and other familiar commands. PowerShell is a new environment that runs not only new commands, but also allows you to create text files that can run these commands in a very program-like fashion. That will be the focus of this series of articles.

Once inside the Programming language or Shell, you can run commands against the operating system, or any other "provider" that gives access to another system, such as SQL Server. SQL Server has several such providers and connection methods. To make things a little more confusing, connection methods like ODBC then provide the connections that things like ADO use to run commands to SQL Server.

In this tutorial I'll focus only on PowerShell, and we'll skip around a bit for how we connect to SQL Server. You should know that once you learn the procedures within PowerShell, you can use any connection method. In other words, the DMO methods you learn from the vbscript tutorial work equally well in Perl or PowerShell.

So our first order of business is to obtain PowerShell and install it. There's a different version for a few of the operating systems, but the download page lists them all. You can find that here:

http://www.microsoft.com/technet/scriptcenter/topics/msh/download.mspx

There are additional requirements for PowerShell, but the installer will detect them and alert you to any other downloads you need.

Once you've installed PowerShell, you'll get a new menu item called Windows PowerShell 1.0. Once in that menu, you'll find the PowerShell icon that starts the environment.

Again, you're in a shell, and as such you have access to all the commands that PowerShell contains. But what is that, exactly? What commands do you have access to?

To find out, you can certainly read the documentation that comes with PowerShell, which is quite good. In fact, I highly recommend that you do read it. You can also read fine tutorials like this one. Or, you can just ask PowerShell what you can do. Try it. Start PowerShell, and then type in this command:

get-help

You'll notice that it returns a whole list of information, and there towards the bottom it tells you that you can type get-command to return a list of commands that you can use. You can even type get-help and then the name of one of those commands, and find even more information.

You've probably already noticed the format for PowerShell. Most everything you type will be a verb, followed by a dash, followed by a noun.

Now there is far more information returned from the get-help or get-command than a single screen can normally display. To pause the screen, you can use an old trick from way back in the DOS days (That's Disk operating system, not Denial of Service) — the pipe. It's this symbol | , which should be near the ENTER key on most US keyboards. The pipe command sends the results of one command to another. In this case there's a command called MORE that outputs one screen of data at a time. Now try this:

get-command | more

You'll notice that the screen pauses after it gets full, and you can press a key to go to the next screenful.

So far you've seen two very key concepts within PowerShell. The first is the verb-noun syntax, and the second is the piping of once command to another. You're actually more than halfway there on learning PowerShell — sort of. While there are only a few more concepts we'll need to cover, it's stringing these things together that is a bit more complicated. Don't worry, we'll take this one concept at a time, and by the time we're done you'll have everything you need to know to build your own scripts.

Speaking of scripts, we haven't been using those yet. We've been using the command-mode of PowerShell, which means that as we type each command it runs immediately. The nice thing about PowerShell is that even though the commands run immediately, they haven't been forgotten by the shell. We'll come back to that concept in a moment.

Before we move on, let's cover one more concept. You're familiar with parameters from T-SQL. A parameter is simply a name that stands for something else. In PowerShell, parameters are very important, and they can stand for just about anything. Let's try it. Type the following command in PowerShell and press ENTER:

$test = "C:\WINDOWS\*.EXE"

You've now created a parameter called $test that holds the value C:\WINDOWS\*.EXE. You can prove that by typing the variable name and pressing ENTER:

$test

You'll see the contents displayed on the screen. While that variable might not seem useful at the moment, let's see it in action by typing the following command and pressing ENTER:

dir $test

The display you get is the result of the DIR command and the expanded contents of the variable — namely, the EXE files in the C:\WINDOWS directory.

Variables are far more powerful than just storing strings, however. In the rest of this series I'll explain how you can set variables to all kinds of things — even other commands.

But for now let's finish this tutorial by creating a script that runs a few commands on the server. In fact, let's just run the same commands we've been working with here.

Open NOTEPAD.EXE and enter the following commands in a new file:

cls
get-command | more
$test = "C:\WINDOWS\*.EXE"
$test
dir $test

Save the file on your hard drive (I make a directory called C:\batch, but that's just me) with the name TEST.PS1.

Now, in PowerShell just navigate to the directory where you saved the file and type TEST.PS1 and press ENTER.

You probably received an error. The reason is that PowerShell doesn't allow scripts to run by default. That's to keep bad scripts from harming your system. This is a great thing, since it shows that Microsoft is paying attention to security right out of the box. It's a feature called Execution Policy, and you can find out more about it by typing:

get-help set-executionpolicy

But we still need to be able to run scripts. There are several levels of security, which you can read about using the previous command, but for now we'll allow any locally-developed scripts to run, but restrict other scripts to those that have been digitally "signed" (more on that later). Just type the following command to do that:

Set-ExecutionPolicy RemoteSigned

Now you can run the TEST.PS1 file and all is well. This will serve as the introduction to our series. In the next few articles I'll develop the requirements for our sample process, and along the way I'll explain how you can use this powerful new tool to manage your enterprise from the command line.

InformIT Articles and Sample Chapters

For more information about Windows scripting, check out this book.

Online Resources

There is no better reference for Windows PowerShell than this one.