Tuesday, 23 September 2014

Update the IIS bindings on all Servers in a SharePoint Farm using PowerShell Remoting

I blogged the other day about using PowerShell Remoting to add a SQL alias to multiple servers in a SharePoint Farm. Adding a SQL Alias to all the Servers in a SharePoint Farm using PowerShell and Remoting.

This post follows that one in theme. The migration project I'm working on at the moment required a change to the IIS bindings for one of the web applications. We needed to a add a default binding that would catch all requests other-wise not resolved, on port 443.

The binding in IIS looks like this: *:443:

I used the PowerShell Remoting technique described in the blog above to run a PowerShell script block on all of the 15 SharePoint servers in the farm. This is fast, and ensures the settings get applied consistently!

The key to this task, is to ensure you load the IIS PowerShell module in each script block. Each "remote session" is like openning a new PowerShell console; you must remember to load any additional modules or add-ins that are need by your script.

To the code.

1. Stop the default website (if it's running)
2. Add the default (catch-all) binding for SSL (port 443). In the example, we're adding that binding to the Yarletto IIS web application (not to be confused a SharePoint web application).
3. Remove the existing bindings on the IIS web application.

The first part of the script takes care of credentials, stopping the default website (if it's started) and adding the new binding.

# Add the SharePoint Snap-in            
Add-PSSnapin Microsoft.SharePoint.PowerShell            
# Create a credential object - this is needed to authenticate to the             
# remote server of each PowerShell Remote session.            
$account = Read-Host -Prompt "Enter the farm account";            
$password =  Read-Host -Prompt "Enter the farm account password" -AsSecureString
$credentials = New-Object System.Management.Automation.PsCredential($account,$password);
# Get a list of servers in the SharePoint farm. Then filter the list to servers
# that actually have the SharePoint binaries installed (omit DB and email servers)
$farm = Get-SPFarm            
$servers = $farm.Servers | ?{$_.Role -eq "Application"} | Select Name,Role            
# Send the list of servers to a for-each cmdlet - the alias is "%".             
$servers | %{             
 # For each loop, create a new PowerShell Remote Session            
 # Pass in the credential object to authenticate the remote session            
    $rs = New-PSSession -ComputerName $_.Name -Credential $credentials;            
    Write-Host "Updating bindings on"$_.Name -f green;            
 # Invoke a PowerShell Script block in the remote session,             
 # using the Invoke-Command cmdlet.            
 # Invoke-Command will run the script block in the session passed             
 # into the Session parameter.            
 # At the end of the script block, notice that parameters have been             
 # passed into the script block            
 # using the -ArgumentList paramater.            
    Invoke-Command -Session $rs -Script {             
        param($hostname = "", $iisWebAppname = "")            
        Write-Host "Working on"$env:COMPUTERNAME;             
        Write-Host "Hostname:"$hostname                    
        Write-Host "IIS APP name"$iisWebAppname;            
        # Load the IIS Web Administration module, to get access to             
        # the IIS PowerShell cmdlet's            
        Import-Module "WebAdministration"             
        # Get the default IIS web site            
        $ws =  Get-Website "Default Web Site"            
        # If the website is running, stop it.            
        if($ws.state -eq "Started"){            
            Write-Host "Stopping default website." -f DarkMagenta            
            Sleep 1;            
         # Use the Get-WebBinding cmdlet to search for an existing             
         # instance of the web binding we want to add            
        $b = $null            
        $b = Get-WebBinding | ?{$_.bindingInformation -eq "*:443:"}            
        # If the web binding doesn't already exist, then create it!            
        if($b -eq $null)            
            Write-host "Adding binding" -f DarkYellow            
            New-WebBinding -Name $iisWebAppname -Protocol https -Port 443 -IPAddress "*" -HostHeader $hostname                    
    } -ArgumentList "","Yarletto"              
    # Finally, make sure you close the Remote session.            
    Remove-PSSession -Session $rs;                

The second part of the script removes the old bindings. This could be scripted more efficiently (as a function), but sometimes you just need to create a script in the shortest possible amount of time!

# Send the list of servers to a for-each cmdlet - the alias is "%".             
$servers  | %{            
    # For each loop, create a new PowerShell Remote Session            
    # Pass in the credential object to authenticate the remote session                
    $rs = New-PSSession -ComputerName $_.Name -Credential $credentials;            
    Write-Host "Updating bindings on"$_.Name -f green;            
    # Invoke a script block using the remote session (as above)            
    Invoke-Command -Session $rs -Script {             
        param($hostname = "", $iisWebAppname = "")            
        Write-Host "Working on"$env:COMPUTERNAME;             
        Write-Host "Hostname:"$hostname                    
        Write-Host "IIS APP name"$iisWebAppname;            
        # Remember to load the IIS Web Administration module            
        Import-Module "WebAdministration"            
        # Check the IIS Binding exists            
        $b = $null            
        $b = Get-WebBinding -HostHeader $hostname -Port 443            
        # If the binding exists, delete it!            
        if($b -ne $null)            
            Write-host "Removing binding" -f DarkYellow            
            Remove-WebBinding -BindingInformation $b.bindingInformation                    
    } -ArgumentList "yarletto.com.au","Yarletto"              
    # Finally, remember to close the session once you're finished with it!            
    Remove-PSSession -Session $rs;                

Wednesday, 17 September 2014

Adding a SQL Alias to all the Servers in a SharePoint Farm using PowerShell and Remoting

Today I needed to add a new SQL alias to all the servers in a large SharePoint Farm.

There are 15 servers in this farm (not including the SQL clusters). So I didn't want to logon to each server in the farm to add the alias manually.

I thought it would be great if I could just run a single PowerShell script on one of the SharePoint servers that added the alias to all the servers in the farm.

That's where PowerShell Remoting comes to the rescue! I can execute a script on multiple servers from a single server!

Here's how.

Cooking Time:
5 mins

1 x Script to execute
1 x Credential (requires administrative permissions)
A handful of Servers

1. Create a credential object

$account = Read-Host -Prompt "Enter the farm account";            
$password =  Read-Host -Prompt "Enter the farm account password" -AsSecureString
$credentials = New-Object System.Management.Automation.PsCredential($account,$password);

2. Get all the "SharePoint" servers in the farm (a.k.a any server that has the SharePoint binaries installed on it).

I'm filtering the list of servers in the farm based on the Role = Application. This ensures we don't get SQL Servers and email servers.

$farm = Get-SPFarm            
$servers = $farm.Servers | ?{$_.Role -eq "Application"} | Select Name

3. Store the SQL Alias information in some variables

$aliasname = "HR"             
$sqlserver = "sqlserver\hrinstancename"            
$tcpalias = "DBMSSOCN," + $sqlserver

4. Pipe the list of servers to the Foreach-Object (%) cmdlet, and let the magic begin!

In each loop of the for-each block, create a new PSSession, using the server name and the credential object created earlier, to connect to the remote server.

Once you have the new remote PSSession, use the Invoke-Command cmdlet to run the PowerShell script in the remote session.  Pass the $aliasname and $tcpalias variables to Invoke-Command, so that they can be used in the script block.

The PowerShell for actually adding the aliases is a slightly modified version of a script from the guys at Habanero Consulting

Finally, remember to close the PSSession at the end of the block

$servers | %{                
    $rs = New-PSSession -ComputerName $_.Name -Credential $credentials;            
    Write-Host "Adding SQL Aliases to"$_.Name -f green;            
    Invoke-Command -Session $rs -Script {             
  param($AliasName = "", $TCPAlias = "")            
        Write-Host "Working on"$env:COMPUTERNAME;
        $x86 = "HKLM:\Software\Microsoft\MSSQLServer\Client\ConnectTo"            
        $x64 = "HKLM:\Software\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo"
        if ((test-path -path $x86) -ne $True){write-host "$x86 doesn't exist";New-Item $x86}            
        if ((test-path -path $x64) -ne $True){write-host "$x64 doesn't exist";New-Item $x64}           
        $p = $null;            
        $p = Get-ItemProperty -Path $x86 -Name $AliasName -ErrorAction:SilentlyContinue
        if($p -eq $null){Write-Host "creating x86 alias" -f Yellow; New-ItemProperty -Path $x86 -Name $AliasName -PropertyType String -Value $TCPAlias}            
        $p = $null;            
        $p = Get-ItemProperty -Path $x64 -Name $AliasName -ErrorAction:SilentlyContinue            
        if($p -eq $null){Write-Host "creating x64 alias" -f Yellow;New-ItemProperty -Path $x64 -Name $AliasName -PropertyType String -Value $TCPAlias}            
    Remove-PSSession -Session $rs;                
} -ArgumentList $aliasname,$tcpalias

And that's it! It's as easy as that!
Kaaaaa PoW!

Monday, 15 September 2014

Deleting Orphaned SharePoint Databases

When you delete a Service Application, but not the data, databases are left behind. Should you want to clean up references to these databases later, here's how.

Use the Get-SPDatabase cmdlet to list all of the databases the Farm knows about. The output of this command is verbose, so pipe it to Format-List and select a subset of the properties.

Get-SPDatabase | FT Name,Exists

Notice how the IsAttachedToFarm and ExistsInFarm properties don't report true or false for most of the service applications?

It's not a problem! You can use the Exists property (on each database) to filter that list to just databases that SharePoint "thinks" don't exist.

$dbs = Get-SPDatabase            
$dbs | ?{$_.Exists -eq $false} | %{Write-Host "DB"$_.Name"does not exist." -f red}


$dbs | %{Write-Host "Database"$_.Name;if($_.Exists){Write-Host "DB Exists." -f Green}else{Write-Host "DB does not exist." -f red}}

To remove these database references, call the Delete() method, and then the Unprovision() method, on each database.

For Example:

$dbs = Get-SPDatabase            
$dbs | ?{$_.Exists -eq $false}  | %{Write-Host "DB"$_.Name"does not exist. Deleting and cleaning up references." -f red; $_.Delete();$_.Unprovision()}

Thursday, 11 September 2014

Filtering a SharePoint List View by Document Approval status

Just a quick one on creating SharePoint list views that filter results based on a workflow status column.

Scenario: SharePoint 2013, Nintex Workflow 2013, Document Library with a workflow attached (that runs on the documents).

The workflow status is recorded in the Document Approval column (static name, Document). 

The values of this column can be retrieved using PowerShell. In this example, I'm using CSOM to access the field values, by getting the field, and looking at the SchemaXml property:

$SourceWebUrl = "http://some.site.com/sites/fud"            
$SourceListName = "Project Documents";            
$account = Read-Host -Prompt "Enter the account to use to query pages";            
$password =  Read-Host -Prompt "Enter the password to use to query pages" -AsSecureString
$credentials = New-Object System.Management.Automation.PsCredential($Account,$Password);

Add-Type -Path "C:\Temp\Microsoft.SharePoint.Client.dll";            
Add-Type -Path "C:\Temp\Microsoft.SharePoint.Client.Runtime.dll";            
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SourceWebUrl)            
$ctx.Credentials = $credentials            
$w = $ctx.Web            
$l = $w.Lists.GetByTitle($SourceListName)            
$fields = $l.Fields            
#Get the document approval field and check the SchemaXxml property            
$da = $fields.GetByInternalNameOrTitle("Document Approval")            

The values are:

<Field DisplayName="Document Approval" Type="WorkflowStatus" Required="FALSE" ID="{e7cfcdf7-6990-4a20-835c-83d64fbaf87a}" SourceID="{a777c58e-b89b-4f82-8c08-36721dd8ceeb}" StaticName="Document" Name="Document" ColName="nvarchar16" RowOrdinal="0" Version="154" WorkflowStatusURL="_layouts/15/WrkStat.aspx" ReadOnly="TRUE">
        <CHOICE>Failed on Start</CHOICE>
        <CHOICE>In Progress</CHOICE>
        <CHOICE>Error Occurred</CHOICE>
        <CHOICE>Failed on Start (retrying)</CHOICE>
        <CHOICE>Error Occurred (retrying)</CHOICE>     

To use these values in a List View, open your list view (or create a new list view) in SharePoint Designer. Then create a CAML query that filters on this field, using the (zero based) index of the field values to specify the field value to filter on. The field type needs to be Integer. 

For example, the following CAML query filters all documents that the current user has authored, that have a workflow status of Starting or In Progress;

                <FieldRef Name="Author"/>
                <Value Type="Integer">
                    <UserID Type="Integer"/>
                    <FieldRef Name="Document"/>
                    <Value Type="Integer">0</Value>
                    <FieldRef Name="Document"/>
                    <Value Type="Integer">2</Value>

Tuesday, 9 September 2014

Styling the UI Bootstrap (AngularJS) Datepicker in SharePoint

I'm working on a SharePoint project at the moment using AngularJS to build a kind of SPA (Single Page Application).

There are a number of controls I need on the page, one them being a calendar / date picker. The client wanted the same experience as the SharePoint datepicker. I looked at several options for a datepicker control, and settled on using the datepicker directive included with the UI Bootstrap.

UI Bootstrap contains a number of directives for UI components, written in AngularJS by the AngularUI team, including a datepicker.

UI Bootstrap components can be downloaded here: UI Bootstrap

This datepicker works nicely, and it's a native AngularJS directive. However, the styling uses CSS classes from Bootstrap CSS. This isn't a bad thing; I like Bootstrap CSS a lot. But if I attach Bootstrap CSS to SharePoint without modification, the CSS will impact the styling of the SharePoint site.

My solution was to extract the styles used by the calendar control, slightly modify them to make the selectors more specific, and then add them to my solutions CSS file.

I modified the styles to:
* Remove the glyphicons and replace them with images or text (to make the control like more like the SharePoint calendar control).
* Added an additional selector to many of the CSS classes, to ensure the styles only targeted the datepicker.
* Make the datepicker slightly smaller (smaller buttons, fonts, etc). This made it a little more like the SharePoint OOTB calendar (only it's better!)

The end result looks like this:
When expanded, it looks like this:

I used the developer tools in Google Chrome to inspect the classes the datepicker uses, and then extract them out into a separate file. I then wrapped the datepicker in a parent DIV, to which I applied a CSS class to use to target the datepicker with new styles from Bootstrap.

The HTML markup for the control (I'm using the highlighted CSS class to enable me to specifically target the datepicker with the Bootstrap CSS):

<div class="ebcs-calendar">
    <p class="input-group ebcscontrol-long-calendar">
        <input type="text" class="form-control ebcscontrol-date-input" datepicker-popup="{{rec.calendar.format}}" name="Endorser1DueDate" ng-model="rec.item.eBriefEndorser1DueDate" is-open="rec.calendar.calendars['Endorser1DueDate']" min-date="rec.calendar.minDate" max-date="'2030-12-31'" datepicker-options="rec.calendar.dateOptions" ng-required="true" close-text="Close" />
        <span class="input-group-btn">
            <button type="button" class="btn btn-default ecbs-calendar-button" ng-click="rec.calendar.open($event, 'Endorser1DueDate')"><i class="glyphicon glyphicon-calendar"></i></button>

The CSS Styles (I've highlighted the CSS selector used to specifically target the datepicker with the Bootstrap CSS).

Note: You can download the cut-down version of the Bootstrap CSS I used here: http://gallery.technet.microsoft.com/Modified-for-using-the-69281fe6

<style type="text/css">
div.ebcs-calendar {
    font-family: "Helvetica Neue",Helvetica,Arial,sans-serif;
    font-size: 14px;
    line-height: 1.42857143;
    color: #333;
    background-color: #fff;
div.ebcs-calendar button,
div.ebcs-calendar input,
div.ebcs-calendar optgroup,
div.ebcs-calendar select,
div.ebcs-calendar textarea {
    color: inherit;
    font: inherit;
    margin: 0;
div.ebcs-calendar input[type="button"],
div.ebcs-calendar input[type="submit"],
div.ebcs-calendar input[type="reset"],
div.ebcs-calendar > button {
    align-items: flex-start;
    text-align: center;
    cursor: default;
    color: buttontext;
    padding: 2px 6px 3px;
    border: 2px outset buttonface;
    border-image-source: initial;
    border-image-slice: initial;
    border-image-width: initial;
    border-image-outset: initial;
    border-image-repeat: initial;
    background-color: buttonface;
    box-sizing: border-box;

The snippet of styles below is where I've replaced the glyphicons with the default SharePoint calendar image, and some text for the backwards / forwards text.

<style type="text/css"
.glyphicon-calendar:before {
    content: " ";
    background-image: url('/_layouts/15/images/calendar_25.gif');
.glyphicon-chevron-left:before {
    content: "<";
    font-weight: 900;
    font-style: oblique;
.glyphicon-chevron-right:before {
    content: ">";
    font-weight: 900;
    font-style: oblique;
    background: url("/_layouts/15/images/calendar_25.gif") no-repeat scroll 0 0 transparent;
    border: 0;
    background: url("/_layouts/15/images/calendar_25.gif") no-repeat scroll 0 0 transparent;
    -webkit-box-shadow: none;
    box-shadow: none;

Monday, 8 September 2014

Kill all of the Processes Accessing a Microsoft SQL Server Database

I had an issue today trying to take a Microsoft SQL Server database offline (for a SharePoint Search Service). It was taking a very long time, and I was getting impatient!

Because there were still connections to the database, the command to take it offline line wouldn't complete. What a pain!

Time to kill all of the processes with open connections to the database. I'd previously taken the SharePoint Search Service offline, so I wasn't worried about forcefully closing connections.

I used sp_who2 to return a list of processes accessing the Search Service database (called SvcApp_Search_Admin). I saved this to a temporary table (to filter and group the results), and then used a cursor to kill all the processes associated with the database.

DROP TABLE #sp_who2
CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),
      Login  VARCHAR(255),HostName  VARCHAR(255),
      BlkBy  VARCHAR(255),DBName  VARCHAR(255),
      Command VARCHAR(255),CPUTime INT,
      DiskIO INT,LastBatch VARCHAR(255),
      ProgramName VARCHAR(255),SPID2 INT,
INSERT INTO #sp_who2 EXEC sp_who2
Select * from #sp_who2 where DBName = 'SvcApp_Search_Admin'
Select SPID from #sp_who2 where DBName = 'SvcApp_Search_Admin' Group By SPID
DECLARE @dbname NVARCHAR(1000)
DECLARE c CURSOR FOR Select distinct(SPID) as Int,DBName from #sp_who2 where DBName = 'SvcApp_Search_Admin'
FETCH NEXT FROM c INTO @spid,@dbname
    PRINT ' Killing Connection to: ' + @dbname + ' with ID ' + CAST(@spid as nvarchar)
    declare @sqls nvarchar(50)=''
    SELECT @sqls = 'kill ' + CAST(@spid as varchar(20))+';'
    FETCH NEXT FROM c INTO @spid,@dbname

I borrowed the idea of saving the results from sp_who2 into a temporary table from SQLMatters, here: http://www.sqlmatters.com/Articles/sp_who2%20-%20filtering%20and%20sorting%20the%20results.aspx