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.