Although you can use SQL Server Management Studio or T-SQL to manage AlwaysOn, SQL Server Denali CTP 3 includes 25 cmdlet which together provide complete coverage for creating, confiiguring and administering the AlwaysOn database feature. In this post we’ll look at using Powershell to perform various management tasks for AlwayOn. Note: This blog post describes…
Tag Archive: sqlps
Denali SQLPS First Impressions
I’ve taken a few hours to try out Denali CTP 3 sqlps and noticed some welcome changes. The biggest change for sqlps is that it has been implemented as module and plain old Powershell host–It’s no longer mini-shell! SQLPS Host SQLPS is now regular Powershell host implemented as the familiar sqlps.exe. Prior versions of sqlps…
Making an Operations Manager Module
Operations Manager 2007 R2 ships with Operations Manager Shell which is simply a provider with associated cmdlets for working with System Center Operations Manager. The Operations Manager implementation of PowerShell uses a console file which limits reusability, however we can easily build our own Operations Manager Module… Creating the Module This is a technique I…
PBM and PowerShell
The best new management feature added to SQL Server 2008 is Policy Based Management or PBM. PBM allows DBAs to automate many of the traditional daily (or more frequent) checklists*. Although PBM is useable out-of-box there are several key features missing: Unable to capture results of policy evaluations against downward-level versions (2000 and 2005). The…
Making A SQLPS Module
If you’re working with PowerShell and SQL Server one of things you’ll want to to do is load the SQL Server 2008 provider and cmdlets into a regular PowerShell. Michiel Wories, the creator of SMO and sqlps, provides an initialization script in his blog post SQL Server PowerShell is Here! The script will load SQL…
Scripting Linked Servers
A co-worked asked me to look at a T-SQL script I had written 8 years ago for scripting out linked servers and linked server logins from SQL Server. The script wasn’t working as expected. I hadn’t seen the code in some time, but looking at it now the fact that it did not work wasn’t…
The Truth about SQLPS and PowerShell V2
With the release of SQL Server 2008 R2 there have been claims that sqlps is really PowerShell V1 under the covers or that sqlps is PowerShell V2 because it returns $psversiontable information. Although, technically, sqlps neither PowerShell V1 nor V2, the answer to this question is a little more complicated and a closer look into…
The T-SQL Hammer
The over-reliance on a familiar tool is best described with the quote, “if all you have is a hammer, everything looks like nail” and for database professionals this means using or sometimes misusing T-SQL. Whenever database administrators are presented with a scripting problem they instinctively reach for good-old-familiar T-SQL. And why not? In many cases…
Finding Invalid SQL Logins
As many of you know the system stored procedure sp_validatelogins is used for finding invalid logins. Although sp_validatelogins is useful there’s one problem – the output isn’t always accurate. You see when you add a a Windows account to SQL Server the SID as well as the domain (or computer name) slash account name are stored in…
Microsoft SQLPS Review
There have been several blog entries which about Microsoft’s sqlps.exe, the PowerShell host provided with SQL Server 2008, most of which have centered around the SQL Server product teams’ decision to implement a closed shell. It would seem after much negative feedback on the mini-shell there is at least is some acknowledgement by Microsoft this was a…
