SQL script file output using SQLPS

To automate tasks I often need to execute one or more script files (*.sql). Usually I have used sqlcmd.exe, but I would like to convert to sqlps, as it seems to be the future of SQL Server automation according to Microsoft. That is at least my personal guess...

A test SQL script (SqlScript.sql) like this tests three ways of output. That is standard output by SELECT, event output by PRINT and error output by RAISERROR.
SET NOCOUNT ON;
SELECT @@VERSION AS [sql_version];
GO
PRINT N':: Hello world from PRINT';
GO
SELECT ':: Hello world from SELECT' AS [hello];
GO
RAISERROR( N':: Hello world from RAISERROR', 18, 0 ) WITH NOWAIT;
GO
SELECT ':: End of script.' AS [script_end];

The calling PowerShell script file is
& sqlcmd.exe -S "Titanium" -E -r1 -i "SqlScript.sql" > SqlScript.log 2>&1

After execution by SQL Server PowerShell (sqlps.exe) the log file is
sql_version                                                                                                                                                                                                                                                                                                
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
    Mar 29 2009 10:11:52
    Copyright (c) 1988-2008 Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.1 (Build 7600: )
                                                                                                             
SQLCMD.EXE : :: Hello world from PRINT
At H:\Script Library\sqlps\Invoke-SqlCmd.ps1:14 char:2
+ & <<<<  sqlcmd.exe -S "Titanium" -E -r1 -i "SqlScript_1.sql" > SqlScript_1.log 2>&1
    + CategoryInfo          : NotSpecified: (:: Hello world from PRINT:String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError

hello                    
--------------------------
:: Hello world from SELECT
Msg 50000, Level 18, State 1, Server TITANIUM, Line 2
:: Hello world from RAISERROR

script_end      
-----------------
:: End of script.

As the PRINT statement is a event, the placement of the PRINT output can vary.

Okay, I kind of cheated by still using sqlcmd.exe.
But this way I get the wanted output in the log file and in the right order. Also I get the features of PowerShell that I find much more powerfull than sqlcmd.exe or Windows Shell (cmd.exe).

Generate sqlcmd statements

I have a change implemented by several (10+) T-SQL script files in sizes from about 200 KB to 739 MB. Executing a 739 MB T-SQL script in Microsoft SQL Server Management Studio (SSMS) will fail, and typing several sqlcmd statements manually will be tedious and faulty.
By placing alle the T-SQL script files in one folder and using a single PowerShell statement all the sqlcmd statements can be created nice and quick:
ls *.sql | %{"`n@ECHO $($_.BaseName)...`nsqlcmd.exe -E -S %DbInstanceName% -i `"$($_.Name)`" -o `"$($_.BaseName).log`" -r1 -b`n@ECHO :: ErrorLevel = %ERRORLEVEL%`n@ECHO."}
The generated sqlcmd statements I can then copy into a Windows Shell script file (.cmd) where the variable DbInstanceName is defined by
@SET DbInstanceName="sandbox.sqladmin.lan\SSDB01"
It is on purpose that the Windows Shell script continues on error - in this specific case.

The logfile defined by the parameter -o in each sqlcmd statement holds output as results (e.g. SELECT), events (e.g. PRINT) and errors (e.g. RAISERROR).
This is verified by using a T-SQL test script
SELECT @@VERSION AS [sql_version];
GO
PRINT N':: Hello from PRINT';
GO
RAISERROR( N':: Hello from RAISERROR', 18, 0 );
and calling this from a sqlcmd statement generated by the PowerShell statement above.

SQL Server Agent schedule owner

When a database mirroring session is created using Microsoft SQL Server Management Studio (SSMS) the job "Database Mirroring Monitor Job" is created automatic. But if you create the database mirroring session by script the job is not created. This is described in more details tin the MSDN Library article "Monitoring Database Mirroring".

A database instance had a mirroring session running against one host, and the mirroring host was recycled. Then the database mirroring sessions were recreated against another host. This time by script.
No Problem I thought.
The job "Database Mirroring Monitor Job" was scripted out in SSMS and then created on the mirroring database instance. It then turns out that the jobs was not created exact as on the principal database instance.

The owner of the SQL Agent schedule "Database Mirroring Monitor Schedule" was NULL on the mirroring installation where is was "sa" on the principal installation.
This does not show in SSMS windows or by calling the procedure msdb.dbo.sp_help_schedule. But the table msdb.dbo.sysschedules holds the SID of the schedule owner.
SELECT [msdb].[dbo].[sysschedules].[name],[msdb].[dbo].[sysschedules].[owner_sid]
FROM msdb.dbo.[sysschedules]
WHERE [msdb].[dbo].[sysschedules].[name]=N'Database Mirroring Monitor Schedule';

I could not find a way to alter the owner of a given schedule in SSMS. The procedure msdb.dbo.sp_update_schedule does the trick.

EXECUTE [msdb].[dbo].[sp_update_schedule]
@name = N'Database Mirroring Monitor Schedule',
@owner_login_name = N'sa';

You could also do it quick and dirty.

UPDATE [msdb].[dbo].[sysschedules]
SET [owner_sid]=0x01
WHERE [sysschedules].[name]=N'Database Mirroring Monitor Schedule';

Not nice!

Now the schedules are identical and I can disregard the finding.

SQL Server consolidation

This Technical White Paper was published by Microsoft March 2009:
SQL Server Consolidation at Microsoft
The document is rather interesting as it in a structured way describes what we as concerned and dedicated DBAs thinkabout when we have a spare moment...
I like the Servers Ratings based on CPU usage (p 7), as it is a simple and yet effective way of rating the existing SQL Server installations. The existing monitoring tools in the infrastructure such as Microsoft System Center Operations Manager (SCOM) or Idera SQL Diagnostics Manager (SQLdm) will have the historic data needed to rate the existing SQL Server installations.
The three consolidation approaches (p 7) are also usefull. I only miss an approach like "Network Consolidation", to handle a needed segregation of duties and environments and still manage the entire enterprise SQL Server platform.
In general the half hour spend reading the paper was worth it.

Copy SQL login

In my daily surroundings we have a lot of mirrored SQL2005 databases, and to be ready to fail over we do a daily compare of the instances in each mirroring setup.
Among other things we compare the logins, and is a SQL login missing on the mirror instance, it must be created. It could be nice to have a single statement to do this...
The obstacle has been that the password hash is type varbinary(max), but using my favorite internet search engine inspired a solution.

The basic statement is
DECLARE @login_name sysname;
SET @login_name = N'Siegfried';
DECLARE @pw_hash varbinary(max);
SELECT @pw_hash = [password_hash] FROM master.sys.sql_logins WHERE [name]=@login_name;
SELECT 'CREATE LOGIN ['+@login_name+'] WITH PASSWORD=0x'+ CAST('' as xml).value('xs:hexBinary(sql:variable("@pw_hash"))','varchar(max)') + ' HASHED;' AS [DCL];
The value of the variable @login_name must be entered manual, but this is the basic statement...

Discussion
The statement could be implemented in a single function (UDF), procedure (USP) or a complete procedure.
This depends on the given database infrastructure. Among other things the remoting possibilities must be take in consideration.

Reference
The inspiration is the blog entry at "SQL Server Engine Tips":
http://blogs.msdn.com/sqltips/archive/2008/07/02/converting-from-hex-string-to-varbinary-and-vice-versa.aspx
Also the documentation of [sys].[sql_logins] at MSDN Library:
http://msdn.microsoft.com/en-us/library/ms174355(SQL.90).aspx