SQL Server Advent Calendar 9 – Management with Powershell

  • Article

Day 9 of my virtual advent calendar, about stuff I like in SQL Server 2008..

Many SQL Server databases are managed by part time or accidental DBA’s, who have a whole bunch of other duties possibly including networking, active directory, SharePoint and Exchange.   As well as learning about what these all do part time DBA’s also have to get to grips with the different tools used to manage them. 

This is one of the many areas addressed by Microsoft’s new scripting language, Powershell.  Bizarrely from the company that bought you wizards and tries to get you to do everything in  a slick UI, this initially looks like a throw back to the days of DOS or the Unix command shells. This is not as odd as it might seem, because although UIs are good for ‘do once’ operations,  they are not nearly so good for repeating tasks particularly if these tasks take arguments like server name, database name, today’s date etc.

Powershell allows you to create scripts where everything you use is an object using all the power of WMI and .Net, to directly control Windows 2008, Vista, SQL Server 2008 and Exchange (SharePoint will come in Office 14). What does that mean for the DBA?

  • Manageability and repeatability
  • Unified scripting to encompass database management tasks with file operations, security permissions.
  • Using other new tools in SQL Server 2008, like scripting evaluation of policies to a table across multiple servers
  • No more reliance on VB Script and odd looking batch jobs
  • A huge amount of resources like codeplex, real books, TechNet, and the Powershell User Group run by Richard Siddaway 

A good place to start is this https://www.microsoft.com/emea/spotlight/sessionh.aspx?videoid=984blogcast from Dan Jones on the SQL Server Management team followed by this introductory Powershell guide on Codeplex

Finally while Powershell is built in to SQL Server 2008, you can retrofit Powershell onto older versions of SQL Server (back to 2000) using this kit on Codeplex.

Technorati Tags: SQL Server,powershell