PowerShell to submit SQLServicePack job to multiple Server

Max Trinidad at The PowerShell Front had an interesting post that I’ve been meaning to link to - “QuickBlog – Use PowerShell to submit SQLServicePack job to multiple Server”

 I just confirmed that’s possible to submit an unattended SQL ServicePack installer as a job using PowerShell Remoting. I’m using my Hyper-V Virtual Domain I ran the script from a Windows 7 VM and submitted one job to two servers. I could see the the jobs processing on the server Task Manager.

But, it was tedious? If you’re a newbie maybe it’s a little over your head but not impossible. That’s the intention of PowerShell Remoting, to be able to do these things like this, and I’m just scratching the surface. Please, Take advantage of these features in PS V2.0, and more enhancements has been done in PS v3.0 with the inclusion of Workflows.

Still, you will need to use PowerShell “Enable-PSRemoting -force” in all the servers. I know, this may be an issue but you need to configure it on all the servers in order to take advantage of PowerShell Remoting. Now, I’m creating session on each computer so I can run as jobs, and your credential is Important to be included. All this is done from your desktop, no more running to the server room.

(read more)

• #SQL
• #PowerShell
• #PowerSE
• #PowerWF

Windows Azure Node PowerShell Primer

WindowsAzure.com has a nice primer on using PowerShell with Windows Azure Node.js.

What Is Windows Azure PowerShell for Node.js

Windows Azure PowerShell for Node.js provides a command-line environment for developing and deploying Node applications for Windows Azure through a few Windows PowerShell cmdlets.

The following tasks are supported:

• Import publishing settings to enable you to deploy services in Windows Azure.
• Generate configuration files and a sample application for a Node hosted service. Create a Windows Azure service that contains web roles and worker roles.
• Test your service locally using the Windows Azure compute emulator.
• Deploy your service to the Windows Azure staging or production environment.
• Scale and update services in Windows Azure.
• Enable and disable remote access to service role instances.
• Start, stop, and remove services.

If you are using Azure, this is worth a read.

• #SQL
• #Azure
• #powershell
• #powerwf
• #PowerSE

SQL and PowerShell

SQL Server Central [registration required], has a blog post on Johan Bijnens journey from being apathetic about PowerShell to becoming a PowerShell advocate for SQL Server.

If you’re involved with SQL Server, you will have noticed a lot of commotion around Powershell, as people push for it in the context of SQLServer. A good friend and inspiration of mine, Allen White (MVP/sqlblog/@SQLRunr) who I met at SQLPass 2003, is currently one of the people teaching us- a group of SQLServer admins, operational DBA, and developers, how Powershell really can help to ease our lives.

At first I was kind of wary about the new kid in town called Powershell; with regards to Windows (7/2008) as well as towards SQLServer, which is more my turf. The reason I didn’t initially learn Powershell V1.0 is that I’d been bitten by the economy coming to a halt in 2008. The company I work for decided to go fully into survival mode and all upgrades were postponed.

However, nowadays we are installing SQL2008R2 as well as Windows2008R2, so the need to upgrade ourselves to be able to support these two domains is essential. Another reason why I couldn’t withhold my curiosity is that we are also making progress with other software monitoring systems, and these tools also have Powershell features incorporated, or they have APIs to be used by Powershell.

(read more)

• #SQL
• #powershell
• #powerwf
• #PowerSE

Disabling File Growth on SQL Server

The other day I posted about a SQL Server Monitoring Script that monitors data and log growth.  The author of the PowerShell script - Colin Smith - wrote a followup article that discusses how to completely disable file growth using PowerShell.

One would think that in SMO you would have an autogrow flag that could be turned on and off like there is in the GUI. Well this is not the case. So if you take my script from http://itknowledgeexchange.techtarget.com/dba/powershell-script-to-change-data-and-log-files-that-are-growing-by-percent/
and make a little bit of a change then the autogrow will be turned off.
Just set the Growth to 0 and most important is to change the growthtype to none:

$file.growth = 0
$file.growthtype = “none”
$file.alter()

(read more)

• #SQL
• #powershell
• #PowerSE
• #powerwf

PowerShell for SQL Server FAQs

Michael Otey at SQL Server Magazine has an article on PowerShell for SQL Server FAQ’s. 

Windows PowerShell is quickly becoming Microsoft’s management standard for all of its server products. However, PowerShell has never really taken off in the SQL Server community.

There’s no denying that PowerShell is more complex than Windows Shell scripting or T-SQL for that matter. And who needs yet another thing to learn? However, PowerShell offers advantages such as multi-server management and advanced .NET integration capabilities that aren’t present in Windows Shell scripting and T-SQL.

I know we get many of the questions he list about PowerShell integration with SQL as well.  The two biggies are:

• How can I execute a SQL command
• Can I use PowerShell to list databases

If you are a SQL admin and new to PowerShell, jump over for a quick look.

• #SQL
• #powershell
• #PowerSE
• #powerwf

Monitoring SQL Data And Log Growth

Found and article at IT Knowledge Exchange that monitors SQL server data and log file growth using PowerShell.

In my new shop we try to grow our data and log files by a specific amount and not by a percentage. Our team lead ran into a database that had not been set up properly and wanted someone to check all our databases to verify that they have been set up correctly. Since I am the new guy and still eager to impress I said I would take that task. So it took me about 30 minutes to complete the task but now it will only take me a few seconds to execute a powershell script.This script grabs a list of all our SQL Servers from a table in a database that we have the tracks this information. So the script will get the list and then loop through them all and report on the databases that have data and or log files set to grow by percent. here is the code.

(read more)

It might be interesting to tweak this script a bit, place it in PowerWF, and create a SCOM management pack to do the monitoring.

• #SQL
• #powershell
• #powerwf
• #PowerSE
• #SCOM

Detect Alignment With PowerShell*

YouDidWhatWithSQL.com popped up on my radar with a recent post by Rhys on using PowerShell to check Disk Alignment.  Rhys sums up the need for checking alignment and provides the PowerShell script to do it.

Disk alignment has been well discussed on the web and the methods to check this always seem to use wmic or DISKPART. I’ve always loathed wmi so here’s a few lines of Powershell that achieves the same thing;

$sqlserver = “sqlinstance”;
# Get disk partitions
$partitions = Get-WmiObject -ComputerName $sqlserver -Class Win32_DiskPartition;
$partitions | Select-Object -Property DeviceId, Name, Description, BootPartition, PrimaryPartition, Index, Size, BlockSize, StartingOffset | Format-Table -AutoSize;

(read more)

It was a little funny to see “I’ve always loathed wmi” followed by the PowerShell cmdlet “Get-WmiObject” – but I understand what the author meant – PowerShell makes working with WMI and .Net objects much easier.

* Aparently this is the PowerShell equivalent of a Detect Evil or other Detect Alignment Spell.

• #SQL
• #powershell
• #powerwf
• #PowerSE

PowerShell Primer for SQL Admins

Cindy Gross has a nice PowerShell primer for SQL Server administrators.  The article consist of prerequisites, basics on PowerShell and its syntax, as well as links to many other articles on PowerShell and PowerShell with SQL server.  It also has a handful of download links. 

• #powershell
• #SQL
• #PowerSE
• #powerwf

PowerShell Remoting to Execute SQL Commands

Diana Moldovan at MSSQL Tips has a short blog post on using PowerShell Remoting to execute SQL Commands against a SQL Server.  Diana explains how to set up PowerShell remoting and start a session.  The most interesting part of the article was showing how to use Invoke-Command and Invoke-SQLCmd.

Here is the code I’ve run on the calling machine:

Invoke-Command -ComputerName DEV2008-2 -ScriptBlock{
$qry = “SELECT SERVERPROPERTY(‘ServerName’) AS ServerName,
   SERVERPROPERTY(‘ProductVersion’) AS ProductVersion,
   SERVERPROPERTY(‘ProductLevel’) AS ProductLevel,
   SERVERPROPERTY(‘Edition’) AS Edition,
   SERVERPROPERTY(‘EngineEdition’) AS EngineEdition;”
Invoke-Sqlcmd -Query $qry} -ConfigurationName SQLSession

I’m using Invoke-Command in order to run Invoke-Sqlcmd on the target machine DEV2008-2. The ComputerName parameter specifies the machine on which I run the code. The ScriptBlock contains the commands I’m running on DEV2008-2, in this case an Invoke-Sqlcmd

…

Since I do not specify any server instance, the query will be executed against the default instance of the SQL Server installed on DEV2008-2. Remember that I’m working in a domain environment. I connect to the DEV2008-2 instance under my domain account. Notice that I’m using the session configuration registered above on the target machine.

…

By default, the output contains the name of the remote machine, given by the the PSComputerName property. You can use Invoke-Command with the -HideComputerName parameter to hide it

• #powershell
• #PowerSE
• #SQL

SQL Server Remoting with PowerShell

Max Trinidad has written another fine post.  This time Max discusses SQL Server Remoting with PowerShell V2.  In the post, Max explains how the SQLPS Module lets you connect to any SQL server you have access to without having to configure PowerShell Remoting.

When you use the SQLPS Module it automatically let you connect to any of your SQL Servers on your network.  So, YES!! As long as you have permission to manage these servers, SQLPS already gives you the ability to remotely connect to other machines.  There’s no need to setup the Remoting with  Enable-PSRemoting or Set-WSManQuickConfig commands.

Max provides several useful examples.

## SAMPLE 1 - Navigating
Import-Module SQLPS -DisableNameChecking
   
Get-PSDrive SQL*
   
cd SQLServer:\SQL\Your-Machine-name\Default  #or
 
cd SQLServer:\SQL\Your-Machine-name\Your-InstanceName
   
Dir Databases -force

##SAMPLE 2 - Using SQL Management Objects (SMO) 
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server
   
$MySQL | Get-member | Out-GridView
   
$MySQL.Information
   
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server “Your-Machine-name”  #or

$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server “Your-Machine-name\Your-Instancename”

$SQLInstanceName = “Your-Machine-name\InstanceName”
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server $SQLInstanceName
$MySQL.Databases[$SourcedbName].tables | sort -desc dataspaceused | select Parent, `
 @{Label=”DisplayName”;Expression={$_.Schema+”.”+$_.Name};} | `
 sort DisplayName;

## SAMPLE 3 - Execute SQL

Invoke-Sqlcmd -Query “SELECT GETDATE() AS TimeOfQuery;” -ServerInstance “.”  #or
 
$SaveResult = Invoke-Sqlcmd -Query “SELECT * FROM [AdventureWorks].[dbo].[AWBuildVersion];” -ServerInstance “Your-Machine-name\InstanceName”
 
$SaveResult

For a full explanation of these scripts, visit Max’s blog.

• #SQL
• #powershell
• #powerwf
• #PowerSE