Invoke-Command – Remote Powershell – Variables

Often have some issues figuring out just exactly how to do remote pssessions, passing variables, performing different stuff. I found, what I believe is the best solution for script based remote powershell for more complex operations. The below example is remote session against an SQL server, using loading some pssnapins using sqlcmd to set some database options. I use this example, because I find it a good demonstration on how and what is possible. The first example connections to basic server. The second example to an Exchange server, as the powershell connection string is different.

#– Basic server remote powershell with variables

$Session = New-PSSession -Computername $SQLServer -Authentication Kerberos
$SQLOptions = {param([Parameter(Mandatory=$true)]$Database,[Parameter(Mandatory=$true)]$DatabaseLog,[Parameter(Mandatory=$true)]$Instance)
	Add-PSSnapin sqlserverprovidersnapin100
	Add-PSSnapin sqlservercmdletsnapin100
	Invoke-SQLcmd -Query "USE master;ALTER DATABASE $Database SET RECOVERY FULL, AUTO_UPDATE_STATISTICS ON;" -ServerInstance "$Instance"
	Invoke-SQLcmd -Query "USE master;ALTER DATABASE $Database MODIFY FILE (NAME= $Database,FILEGROWTH= 150MB, MAXSIZE= UNLIMITED);" -ServerInstance "$Instance"
	Invoke-SQLcmd -Query "USE master;ALTER DATABASE $Database MODIFY FILE (NAME= $DatabaseLog,FILEGROWTH= 150MB, MAXSIZE= UNLIMITED);" -ServerInstance "$Instance"
	}

	Invoke-Command -ArgumentList $Database, $DatabaseLog, $Instance -Session $Session -ScriptBlock $SQLOptions
	Remove-PSSession $Session

#– Exchange remote powershell with variables. Difference in Session string.

	
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri http://"$MailServer"/PowerShell/ -Authentication Kerberos
$SendSettings = {param([Parameter(Mandatory=$true)]$MailServer,[Parameter(Mandatory=$true)]$Domain
	new-SendConnector -Name "$Domain" -Usage "Custom" -AddressSpaces "SMTP:$Domain;1" -IsScopedConnector $false -DNSRoutingEnabled $true -UseExternalDNSServersEnabled $false -SourceTransportServers '$MailServer'
	new-AcceptedDomain -Name "$Domain" -DomainName "$Domain" -DomainType 'ExternalRelay'
	}
Invoke-Command $Session -Argumentlist $Mailserver, $Domain -Scriptblock $SendSettings | Out-Null
Remove-PSSession $Session

Crawling of Content Sources Paused – SharePoint Search – Resume Programatically

Some backup applications might cause your crawl to pause, or you might be running low on resources and they are paused. Either way, making sure they are resumed at some point is always nice. A few lines to make sure that all Paused content sources are Resumed.

#-- Get the Search Service Application
$searchapp = Get-SPEnterpriseSearchServiceApplication 'Search'

#-- Get the default content sources
$ContentSources = Get-SPEnterpriseSearchCrawlContentSource -SearchApplication $searchapp

#-- Resume Crawl on all content sources Paused
Foreach ($cs in $ContentSources){$cs.ResumeCrawl()}

There are a few other methods to call on the content source object, the link here is for reference.

http://msdn.microsoft.com/en-us/library/aa679491%28v=office.12%29.aspx

Check and Start – Services Automatic and not running

Had some issues in a virtual environment that we often rollback and rebuild. Before deploying the build, we wanted to be sure that all automatic services in fact were running. The below scripts does just that.
Just change the server(s) located on $Servers.

This script will attempt to start all services that are automatic, some services will stop immediately after, that is normal.
#– Automatic Service started verification.

$Servers = '<Server01>','<Server02>'

Foreach ($Server in $Servers) {
	$Services= Get-WmiObject Win32_Service -ComputerName $Server | Where-Object { $_.StartMode -eq 'Auto' -and $_.State -ne 'Running' }
	Foreach ($service in $Services) {
		$service.StartService() | Out-null
		}
	}
write-host "Automatic services started"

CRM – Performance – AsyncOperationBase Table

Quite a while back had some issues upgrading a CRM instance. It turned out the amount of ASync operations during the lifetime of the CRM application had generated quite a lot of entries. (millions). For some reason I had a hard time find the root cause of this, which turned out to be a lot more general than just relating to an upgrade. I rarely see CRM installations where a clean up job is created, so most CRM installations will actually suffer from large amount of async operations stored.

Have a look at your AsyncOperationBase table and see how many entries are there. You might be surprised.

Below are the SQL lines I ran/set up as a maintenance plan in order to reduce the number. Depending on the number of records, it might be necessary to run the scripts a few times.

Code is from referenced KB article: http://support.microsoft.com/kb/968520/en-us

IF EXISTS (SELECT name from sys.indexes
                  WHERE name = N'CRM_AsyncOperation_CleanupCompleted')
      DROP Index AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted
GO
CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted
ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])
GO

while(1=1)
begin
 declare @DeleteRowCount int = 10000
 declare @rowsAffected int
 declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key)
 insert into @DeletedAsyncRowsTable(AsyncOperationId)
 Select top (@DeleteRowCount) AsyncOperationId from AsyncOperationBase
 where 
  OperationType in (1, 9, 12, 25, 27, 10) 
  AND StateCode = 3 
  AND StatusCode in (30, 32)
  /*AND CompletedOn <= DATEADD(mm, -1, GETDATE())*/
 
 select @rowsAffected = @@rowcount 
 delete poa from PrincipalObjectAccess poa 
   join WorkflowLogBase wlb on
    poa.ObjectId = wlb.WorkflowLogId
   join @DeletedAsyncRowsTable dart on
    wlb.AsyncOperationId = dart.AsyncOperationId
 delete WorkflowLogBase from WorkflowLogBase W, @DeletedAsyncRowsTable d
 where 
  W.AsyncOperationId = d.AsyncOperationId             
 delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d
 where 
  B.AsyncOperationId = d.AsyncOperationId
 delete WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d
 where 
  WS.AsyncOperationId = d.AsyncOperationID 
 delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d
 where 
  A.AsyncOperationId = d.AsyncOperationId
 /*If not calling from a SQL job, use the WAITFOR DELAY*/
 if(@DeleteRowCount > @rowsAffected)
  return
 else
  WAITFOR DELAY '00:00:02.000'
end

EDIT: 26-February-2015;
1: Support article code was updated for a more smooth execution.
2: Added required retention time on records of 1 month;