SQL Server 2008 PowerShell Cmdlets + /n software PowerShell Server v2 = Execute SQL Queries Anywhere.
If you don’t already have the SQL cmdlets for PowerShell, you’ll need to download and install SQL Server 2008. A warning for those of you running on x64 machines - you’ll have to export the SQL Server keys in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1 (there should be 2 in PowerShellSnapIns and 1 in ShellIds) and reimport them to Wow6432Node.
If you don’t already have /n software’s PowerShell Server V2, you’ll need to install that and run it on the same machine on which the SQL cmdlets are installed. This server can run as an application or as a service, it doesn’t matter which.
Once you have this setup, you can go to anyplace that has SSH capabilities and open a secure connection to the PowerShell Server. There are SSH clients nearly everywhere: Windows, Linux, Cell Phones, Websites, you name it. Once connected to the PowerShell Server, just send the command.
I created some scripts that I can call (so I don’t have to type so much if I am on a mobile device or something) that issue the actual sql cmdlet commands themselves. One of them is called sql-getsearches.ps1, and it just tells SQL Server’s invoke-sqlcmd cmdlet to run a stored procedure for me that gives me back the results I want:
$query = "EXEC [table1].[dbo].[sp_LANCE_SEARCH]"
invoke-sqlcmd -query $query -ServerInstance myserver -U myusername -P password
Note: The use of invoke-sqlcmd requires add-pssnapin SqlServerCmdletSnapin100, which I include in my PowerShell Server specific profile (nsoftware.PowerShell_profile.ps1).
If anybody has any questions about getting the SQL Server 2008 cmdlets installed or using the PowerShell Server, feel free to shoot me and email or send me a tweet.