Microsoft Delivers Full Suite of SQL Server PowerShell Cmdlets

merrillaldrich — Sun, 01 Apr 2012 16:20:00 GMT

We’ve all been waiting several years for this, and finally it’s here! Coinciding (approximately) with the release of SQL Server 2012, a new Feature Pack has appeared on the Microsoft web site that adds a full suite of PowerShell cmdlets for DDL and other functions. This means that, at last, we can do things like fully-featured SQL deployment scripts without all the (severe) limitations of T-SQL, such as primitive use of variables, flow control, exception handling.

Taking a cue, finally, from the community project SQLPSX, the SQL Server team seems to have designed the new library with ease-of-use in mind, thankfully concealing a lot of the complexity of using raw SMO objects through a language like C# or PowerShell.

Here’s a teaser showing just a few of these new offerings:

New-Table –Schema dbo –Name Accounts –Columns $myColumns

Remove-Table –Schema dbo –Name Accounts

New-Index –Table $myTable –Columns $myIndexedColumns

Set-Index –Name dbo.Accounts.idxBar –Columns $myRevisedColumns

New-Procedure –Name Foo –ScriptBody $sqlcmd

The list goes on! This will make deployment of changes to existing databases so much easier. No more insanely complicated T-SQL scripts with brittle Dynamic SQL and hazardous error handling to manage complicated flow control like “Create this object if it doesn’t exist already.”

And all you admins aren’t left out in the cold either – check out the fresh cmdlets for managing SQL Agent jobs, and the incorporation of Agent into PBM!:

New-AgentJob –Name MyJob –Type PowerShell –ScriptBody c:\scripts\foo.ps1 –Schedule $MySchedule

Set-AgentJobSchedule –Name “Daily at 3:00 am” –StartTime “03:00” –Enable

Set-ServerProperty –MaxDop 4 –MaxMem 8000GB

I am SO excited, and this is amazing, but I haven’t had a lot of time to explore all the options. I hope to really dig in after reading everyone I else’s April Fool’s posts. Until then, you can download this feature pack yourself (probably) at:

http://www.microsoft.com/download/en/details.aspx?id=88254

Edit April 2: Please note if you come back to this page later, this was an April Fool's joke. There are a handful of new cmdlets in SQL Server 2012, but nowhere near this level of support.

Search results matching tags 'powershell' and 'April Fools'

Microsoft Delivers Full Suite of SQL Server PowerShell Cmdlets