When you should and shouldn't use PowerShell in SQL Server

PowerShell is cool, but it's not always appropriate. Here's a quick list to help you decide when to use it

PowerShell is one of the coolest new things to come out of Microsoft in a long while, and it’s going to do more for server admin than anything else. That doesn’t mean, though, that it’s always the technology for every task.

[ Cut straight to the key news for technology development and IT management with our once-a-day summary of the top tech news. Subscribe to the InfoWorld Daily newsletter. ]

In fact, even though it’s really cool, I choose to use other methods depending on the task. Don’t get me wrong -- I use PowerShell all the time and I’m at the point now where I’m wondering how we ever got along without it. But I wanted to give you guys a quick list to help you decide whether PowerShell is the right tool for the job. This isn’t comprehensive or set in stone. It is, however, a good starting point to show you the types of tasks that make sense for PowerShell.

  • Use PowerShell when doing ops on multiple databases either on a single server or multiple servers. This is really where PowerShell shines.
  • Use PowerShell a group of servers when their registration properties are different from what you would ordinarily keep, or when it’s changing constantly. In other words, if you have certain server groups registered in SSMS but need to perform a set of tasks that span multiple groups, PowerShell is the tool for the job. You can even have complex groupings if you keep all of your servers in a a control table with grouping columns. Then you just query for the groups you need and you’re fine.
  • Use PowerShell when you have many scripts to deploy.
  • Use PowerShell if you have a hard time remembering the SQL cursor syntax or think it’s just too much trouble to manage for simple ops, such as adding a new account to many databases or to many schemas.
  • Use PowerShell to script objects on a schedule or in a special order.
  • Use PowerShell if you need to switch between windows and SQL commands or even share data between the two. It’s much easier in PowerShell.
  • Use SSMS groups to do the same thing at the server level to many servers, such as adding a new sa login or, even better, dropping a login.
  • You can use SSMS for things that require cursors and dynamic SQL as long as you already have it worked out. The cursor and dynamic SQL code for many things can get pretty hairy, but if you already have it written, there’s no reason not to use it.

And Oprah.

Watch my free SQL Server Tutorials at:

http://MidnightDBA.ITBookworm.com

Read my book reviews at:

www.ITBookworm.com

Blog Author of:

DBA Rant – http://dbarant.blogspot.com

Follow my Twitter: MidnightDBA

From CIO: 8 Free Online Courses to Grow Your Tech Skills
Join the discussion
Be the first to comment on this article. Our Commenting Policies