Use PowerShell to Administer SQL Server 2008
Summary: Microsoft MVP Sean McCown shows how to use Windows PowerShell to administer remote SQL Servers using easy commands.
Hey, Scripting Guy! I am an extremely busy database administrator (DBA), and at a recent SQL Saturday event, I heard several people talking about Windows PowerShell. I did not, however, attend any of the Windows PowerShell sessions because there were other sessions more directly related to my job. I am now wondering if I made a mistake in my session selection. Would it be good for a DBA to learn Windows PowerShell?
— JR
Hello JR,
Microsoft Scripting Guy Ed Wilson here. The short answer to your question is YES! I think that everyone who uses Microsoft Windows XP and later should be familiar with Windows PowerShell, whether they are IT pros or not. In fact, I receive many email queries at scripter@microsoft.com that come from accountants, high school teachers, and others who simply want to know how to automate some annoying time-consuming procedure. Many times, their questions are simple ones about renaming folders or files in bulk, but other times they ask more complex questions involving the Microsoft Office automation model. There has been a great deal of interest recently from SQL DBAs. I am going to pass your question to Microsoft MVP Sean McCown, also known as @MidnightDBA on Twitter.
Sean McCown holds a double MCITP in SQL Server 2008 for both administration and development. He is also a SQL Server MVP with more than 15 years of experience in databases. Sean is a contributing editor with InfoWorld Magazine and a frequent contributor to many community sites and forums.
Sean is also founder and co-owner of the renowned website MidnightDBA.com where he records free SQL Server training videos and co-hosts the popular web show, DBAs@Midnight.
Sean has also created content for TrainingSpot.com and TrainSignal, and he moderates the Petri IT Knowledgebase SQL forums. Sean also speaks at various SQLSaturday events and is a board member of his local user group, NTSSUG.
Now, without further ado, here’s Sean.
As DBAs, we quite often are focused on using either one technology or another, and we lose sight of the fact that what we are actually trying to do is solve a problem. Though Windows PowerShell is still a new technology for DBAs, I am already seeing this happen. I am going to talk about a strategy for using Windows PowerShell and T-SQL together to maximize the strengths of each one as well as yours. You do not have to use one technology or the other—you can use both.
Let us go ahead and look at a specific scenario so that you can get a good idea of what I am talking about. Your task is simple: You have a server with 200 databases (DBs), and you need to give user read-only rights to all of them. You do have a choice to make. Are you going to cycle through the DBs one by one in SSMS, use Windows PowerShell and Server Management Objects (SMO), use T-SQL, or use T-SQL and Windows PowerShell?
Clearly using SSMS to cycle through all of the DBs to add the user manually is not an option. If you dedicated your time to the task, it would still take you all day, if you were lucky.
Using Windows PowerShell and SMO is probably a good choice for this kind of task—that is, if you readily know how to do it. I am not an SMO master, so I do not know off the top of my head how to add a user to db_datareader using SMO. I would have to look it up. I suspect most of you are in the same boat; therefore, if you know SMO that well, go for it, but I am going to choose another way.
Using T-SQL is another fine solution, if you have already got it written. The problem is not finding the syntax to add the user; the problem is managing the cursor and the dynamic SQL. If you already have this kind of cursor written, this can be a fine choice and you will have your task complete within just a couple minutes. If you do not have it already written, however, it comes down to your level of experience with this type of thing in terms of how fast you can bang it out.
However, I am sure that most of you already know how to add a user to db_datareader to a single database using T-SQL. In fact, the syntax is painfully simple:
EXEC sp_addrolemember ‘db_datareader’, ‘sean’ Of course, you need to add the user to the database first. The complete script is here:
EXEC sp_grantdbaccess ‘sean’, ‘sean’
GO
EXEC sp_addrolemember ‘db_datareader’, ‘sean’ This is the exact T-SQL you would use to perform this task on a single database on your server. But we need to do it on 200 databases. This is where Windows PowerShell steps in. In this case (and in most cases), Windows PowerShell is going to be merely a delivery mechanism for your T-SQL. Let us look at the Windows PowerShell code that will make this happen:
Dir | %{$DBName = $_.Name; Invoke-sqlcmd -ServerInstance localhost -Database $DBName -InputFile “C:Users.sql”; $DBName;}
First, let us talk about how to get where you need to be. In the SQL Server Management Studio (SSMS), right-click the database folder and click Start PowerShell in the shortcuts menu. Then type the code shown earlier.
Now let me explain some of the code even though I believe most of it will be self-explanatory. Dir pulls a list of databases. % is an alias for foreach. So in that first section, you’re saying get a list of all the databases, and for each one of them, run the code inside the curly brackets. Now, inside the brackets you have $DBName = $_.Name. This is setting a variable to the current database name in the loop. Name is just one of the properties you could get. The semicolon is a command separator just as in T-SQL. Invoke-sqlcmd is the cmdlet for connecting to SQL Server, and its options are easy enough to follow so there is no need for me to go into them. Notice though that I chose to put the code into a file. I did that because there is more than one statement. I could have done it inline and separated them with semicolons. Then that last part, $DBName, just prints the database name to the screen. I like this because if you do not have it, you just get a blinking cursor while the script is running. I like to see things moving along, so I print the current database to the screen.
Remember when I chose to put the T-SQL command into a file? That’s because if I want to save this code as a script, I now have a cursor that will run whatever is in that file on every database on that server. Just by changing the –ServerInstance parameter, I can do anything I want to all the databases on any server I like. It is very powerful. You cannot do that with a T-SQL cursor. You have to alter it for each task you want to perform. Therefore, you have written a script here that you can reuse many times for things that were never foreseen.
That brings up another point. One of the reasons Windows PowerShell is such a fabulous administrative tool for DBAs is because it allows you to take care of those ad hoc requests. With many of the other methods I mentioned, the easiest requests have you writing code for 20 minutes and troubleshooting nuances in dynamic-SQL or SMO. However, Windows PowerShell code is so short that you can take something that you almost never do and bang it out on not only dozens of databases, but even across dozens of servers with surprisingly little typing. The syntax is easy enough that with a little practice you should not have a hard time remembering it either. Perhaps next time I will reverse the scenario and add 200 users to a single database.
JR, that is all there is to using Windows PowerShell to administer a Microsoft SQL Server. This also concludes Guest Blogger Week. Join us tomorrow for Quick-Hits Friday.
We invite you to follow us on Twitter and Facebook. If you have any questions, send email to us at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.
Ed Wilson and Craig Liebendorfer, Scripting Guys
Light
Dark
0 comments