Wednesday, August 17, 2016

Unable to properly communicate with the workflow service

SharePoint 2013

It has been a long time since my last blog post. I have been busy. :-) Well I am back again and thought I would share my latest discovery on the SharePoint 2013 planet.

Problem:
User clicks on List Item - Workflow, and receives error:


Unable to properly communicate with the workflow service.

The server which hosts the Workflow Manager doesn't reveal any errors in the Event Log, ULS Logs or the IIS Logs, but on other WFE servers the error pop's up in the ULS log:

UserAgent not available, file operations may not be optimized.
    at Microsoft.SharePoint.SPFileStreamManager.CreateCobaltStreamContainer(SPFileStreamStore spfs, ILockBytes ilb, Boolean copyOnFirstWrite, Boolean disposeIlb) ......

Failed to retrieve the workflow instance because the connection timed out with exception: System.TimeoutException: The HTTP request has timed out after 20000 milliseconds. ---> System.Net.WebException: The request was aborted: The request was canceled. 
    at System.Net.HttpWebRequest.EndGetResponse(IAsyncResult asyncResult)  ......



You would not say it has something to do with the BlobCache, well I didn't. Anyway, to resolve:

Solution:

Open Powershell and run the following commands:


Add-PSSnapin microsoft.sharepoint.powershell -ErrorAction SilentlyContinue 


$webApp = Get-SPWebApplication "

[Microsoft.SharePoint.Publishing.PublishingCache]::FlushBlobCache($webApp)

*AND* remember to do an IISRESET on all your servers.

Thanks to David:
http://davidmsterling.blogspot.co.za/2015/02/sharepoint-uls-error-useragent-not.html

Tuesday, August 12, 2014

Powershell and SQL for getting Database info

Part of monitoring a SharePoint farm is to make sure there is enough space available for the content databases.

If your organization is anything like mine, segregation of duties are very important. Meaning the SQL DBA's do the SQL stuff, and we the SharePoint Admin, do the SharePoint stuff. As a SharePoint Administrator, I must say, I need to know a bit more about the SQL part of the SharePoint Farm.

So, I don't have access to the SQL Server, and I do not really need it. I just need to pull some stats and that is it. I used PowerShell and SQL Queries to get the info I need.

The info I require is:
 - All Databases on the server

 - Stats specific to each database: Size of DB, used space, and free space available. On which Drive the database files reside as well as the Log files.

The method I chose works in my environment and I am sure there are tools and other/better ways to do the same thing.



1. The PowerShell Session would need to be started with an account access to the SQL Server instance.
2. I want to save the info to CSV files for analysis in Excel.





##########################################################################
# Login to farm with Service Account
##########################################################################





$credential = New-Object System.Management.Automation.PsCredential("YourServiceAccount", (ConvertTo-SecureString "yourPassword" -AsPlainText -Force))




start-process powershell.exe -Credential $credential -NoNewWindow -ArgumentList "Start-Process powershell.exe .\SQLStats.ps1 -verb runas"









sqlFunctions.ps1
function Invoke-SQL {
      param(
      [string] $dataSource = ".\SQLEXPRESS",
      [string] $database = "MasterData",
      [string] $sqlCommand = $(throw "Please specify a query."),
      [bool] $export = $false,
      [string] $filename = "data.csv"
      )


      $connectionString =     "Data Source=$dataSource; " +
                              "Integrated Security=SSPI; " +
                              "Initial Catalog=$database"

      $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
      $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
      $connection.Open()

      $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
      $dataset = New-Object System.Data.DataSet
      $adapter.Fill($dataSet) | Out-Null

      $connection.Close()
      $dataSet.Tables

      if($export)
      {
            Remove-Item $filename
            foreach($table in $dataset.Tables)
            {
                  #$table | export-csv $filename -notypeinformation -append
                  $table | ConvertTo-Csv -NoTypeInformation `
                         | select -Skip 1 `
                         | Out-File $filename -Append
            }
      }

}


#I added these queries as functions. Not the right way, but anyway:

#This SQL Query runs a Stored Procedure on the SQL Server for each DB

function getDBStatsQuery()
{
      $query = "exec sp_msforeachdb
      'use [?];
      select DB_NAME() AS DbName,
      physical_name,
      CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) AS Status,
      CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')) AS Recovery,
      CONVERT(varchar(20),Type) AS Type,
      SUM(size)/128.0 AS File_Size_MB,
      SUM(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB,
      SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB
      from sys.database_files group by type, physical_name' "
      $query
}




#This SQL Query runs a Stored Procedure to get the available free space for all Drives on the SQL Server

function getOSDriveQuery()
{
      $query = "EXEC master..xp_fixeddrives"
      $query
}







SQLStats.ps1

# Script to get Database and Log sizes

# Have to run script with user that has correct permissions on SQL
Write-host "Running as: " ([Security.Principal.WindowsIdentity]::GetCurrent().Name)



#Include SQL Functions
. .\sqlFunctions.ps1





### Start of Queries
$dataSource = "sqlsever/instance" #SQL Server and Instance
$database = "Master" #Database
$outDataFileName = "infoData.csv"
$outDriveFileName = "infoSQLDrives.csv"





#Run Query
Write-host "Invoking SQL Query to retrieve Database stats..."
$results = $(Invoke-SQL -DataSource $dataSource -Database $database -SQLCommand $(getDBStatsQuery) -Export $true -FileName $outDataFileName )





#Get Drive Space available on Server
Write-host "Invoking SQL Query to retrieve OS Drives free space..."
$drives = $(Invoke-Sql -DataSource $dataSource -Database $database -sqlCommand $(getOSDriveQuery) -Export $true -FileName $outDriveFileName)




Write-host "Done..."





This PowerShell Script creates two CSV Files. One for the Databases and one for the Drives on the SQL Server.


Hope it helps someone.

Thursday, July 24, 2014

Azure - Deleting VNet

Trying to delete a Virtual Network in Azure

Powershell command:
Note this removes all Virtual Networks from your active Azure Subscription
Remove-AzureVNetConfig

But then:
Remove-AzureVNetConfig : BadRequest : Cannot delete or modify virtual network while in use 'xxxxx'.

I do not have Virtual Machines as I removed them all. But then I noticed the VNetwork has a Gateway assigned to it.

So remove the gateway before removing the VNetwork.


Delete the Gateway


By the way, the Powershell command for deleting a VNet Gateway:
Remove-AzureVNetGateway -VNetName "SPAutoVNet"


Push!

This blog just received a serious push into the future. I have been busy for the past 3 or so months with SharePoint 2013 and Azure Cloud Services.


Leaving the old technology behind and going forward, where I have not been yet!


While I believe Cloud technology has a serious place in the future (as it currently does), it has room for improvement.


On this blog I will post about my stumbles and falls with SharePoint 2013 and Azure.


Here we (or I) go!