Richard Siddaway's Blog : PowerShell and SQL Server

SQL Server books

RichardSiddaway — Fri, 06 Nov 2009 15:55:01 GMT

A couple of SQL Server books that I would recommend having been involved with both of them.

First up is SQL Server 2008 Administration in Action by Rod College (Manning - http://www.manning.com/colledge/) which I reviewed several times before publication. It covers SQL Server from sizing and installation, through configuration and day-to-day administration. Lots of good best practice information written in a easy to read style.

The second is SQL Server MVP Deep Dives (Manning - http://www.manning.com/nielsen/) to which I contributed a chapter on PowerShell and SQL Server. This book has contributions from 53 MVPs (mainly SQL Server) and is edited by Paul Nielsen, Kalen Delaney, Greg Low, Adam Machanic, Paul S. Randal, and Kimberly L. Tripp. The royalties from the book go to War Child International - (www.warchild.org)

Two good books to add to your SQL Server library

Technorati Tags: Books,SQL Server

SQL Server autoclose

RichardSiddaway — Thu, 25 Jun 2009 12:06:03 GMT

Buck Woody blogged about SQL Server autoclose and how it should be set to being off - http://blogs.msdn.com/buckwoody/archive/2009/06/24/sql-server-best-practices-autoclose-should-be-off.aspx

I’m doing a lot with SQL Server and PowerShell at the moment and it started me thinking that I could easily set this property in PowerShell.

            001              
002               
003               
004               
005               
006               
007               
008               
009               
010               
          $Server = New-Object Microsoft.SqlServer.Management.Smo.Server("SQL08")               
$Server.databases | select Name, Autoclose               
foreach ($db in $Server.Databases){               
    if (!$db.IsSystemObject){               
        $db.Autoclose = $false               
        $db.Alter()               
    }               
}               
$Server = New-Object Microsoft.SqlServer.Management.Smo.Server("SQL08")               
$Server.databases | select Name, Autoclose 
       
 

Create a server object and display the databases, selecting name and the autoclose property. Its boolean so we get true or false back.

We can iterate through the database collection. Test if the database is a system database and set autoclose to false (we don’t want autoclose to happen). A call to Alter() saves the change.

Finally we can recreate the server object and redisplay the autoclose property.

Technorati Tags: PowerShell,SQL Server

PowerShell for DBAs

Richard Siddaway's Blog — Sun, 19 Apr 2009 19:29:07 GMT

Chad has a very interesting post on “The Value Proposition of PowerShell to DBAs” - http://chadwickmiller.spaces.live.com/blog/cns!EA42395138308430!347.entry where he discusses the results of a poll of DBAs regarding PowerShell. On initial reading it...(read more)

SQLPSX 1.5

Richard Siddaway's Blog — Sat, 28 Mar 2009 20:17:42 GMT

If you use PowerShell and SQL Server you should at the very least look at SQLPSX which is a big library of PowerShell functionality. Version 1.5 has just been released. See http://chadwickmiller.spaces.live.com/blog/cns!EA42395138308430!315.entry for...(read more)

Compare Database Schemas

Richard Siddaway's Blog — Fri, 13 Feb 2009 09:48:03 GMT

One of the great things about PowerShell is the strength of the PowerShell community. This is shown by the number of people sharing their PowerShell scripts. A good example is the script to compare the schemas of two SQL Server databases that Steven has...(read more)

SQL Server scripts

Richard Siddaway's Blog — Thu, 15 Jan 2009 18:54:50 GMT

Idera have published a set of PowerShell example scripts for working with SQL Server. They can be downloaded (free) from www.idera.com Check the bottom of the products menu for PowerShell scripts. They give a good introduction to working with Powershell...(read more)

Loading SQL Server Provider

Richard Siddaway's Blog — Fri, 21 Nov 2008 12:41:55 GMT

Last night at the User Group meeting I showed a script to load the required assemblies and snapins that enabled the SQL Server 2008 PowerShell provider to be accessible through a normal PowerShell session. In response to requests at the meeting the script...(read more)

SQL Server PowerShell provider

Richard Siddaway's Blog — Tue, 21 Oct 2008 07:27:12 GMT

I have been looking at PowerShell in SQL Server again recently as I'm working on chapters involving PowerShell and SQL Server for a couple of books. I had had a vague understanding that the PowerShell provider could access remote machines but had...(read more)

PowerShell reading SQL Server logs

Richard Siddaway's Blog — Fri, 26 Sep 2008 20:22:54 GMT

Thanks to Dmitry - http://dmitrysotnikov.wordpress.com/2008/09/26/more-sql-cmdlets/ - and Jacob - http://mspowershell.blogspot.com/2008/09/read-transactionlog-of-sql-server-from.html - for pointing out the PowerShell cmdlets in Quest's SQL Lite Speed...(read more)

PowerShell scripts for SQL Server

Richard Siddaway's Blog — Thu, 11 Sep 2008 18:41:52 GMT

I recently mentioned the PowerShell scripts for SQL Server on codeplex - http://richardsiddaway.spaces.live.com/blog/cns!43CFA46A74CF3E96!1602.entry There has been a new release so now you can work with Tables, Stored Procedures, Views, User Defined Data...(read more)

SQL Server 2008 RTM

Richard Siddaway's Blog — Thu, 07 Aug 2008 07:28:41 GMT

SQL Server 2008 has RTM'd. It should be available for download from TechNet\MSDN. PowerShell included. Share this post : Technorati Tags: PowerShell , SQL Server 2008...(read more)

PowerShell and SQL Server

Richard Siddaway's Blog — Mon, 14 Jul 2008 18:13:08 GMT

Michiel Wories provides a look at some of the thinking behind PowerShell in SQL Server and future plans - http://blogs.msdn.com/mwories/archive/2008/06/25/what-no-cmdlets-sql-server-powershell.aspx Share this post : Technorati Tags: PowerShell , SQL Server...(read more)