Change SSL Certificate on Office Web App Server 2013

We are just starting to build a Pilot Office 2013 environment and I used the incorrect SSL certificate when provisioning the new Farm.  To change the SSL Certificate use the following powershell command:

Set-OfficeWebAppsFarm -CertificateName "<New Cert Name>"

You need to restart each office Office Web App Server in the farm after this

Deleting SharePoint AuditData

As most we have a auditing requirements for when we need to do forensic excercises.  Unfortunatly we turned auditing on with no real thought.  We are now in the situation that we have hundreds of millions of rows of AuditData.  We have attempted to delete this data using the standard powershell methods (SPSite.Audit.DeleteEntries(SomeDate), however we have found this method causes outages as is causing locks at the database.  We have attempted deleting a tiny amount of data (last hour) this does not cause database locks but it would take a year to delete all data.

We have been in contact with Microsoft Premier support and they have given us approval to Truncate the AuditData table, which will save our problems.  You must contact premier support to get specific approval your self or your SharePoint environment will be un-supported.  Below is a script that will backup the database and Truncate the AuditData table for you. 

It goes with out saying but make sure you test this in a non-production enviornment!

DECLARE @DatabaseName VARCHAR(254)
DECLARE @Path VARCHAR(1000)
DECLARE @Truncate VARCHAR(1000)

SET @DatabaseName = 'SP_PP'
SET @Path = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SP2010\MSSQL\Backup\'
SET @Truncate = 'TRUNCATE TABLE AuditData'

--Configure Path
IF (SUBSTRING(@Path, LEN(@Path), 1) != '\')
BEGIN
 SET @Path += '\'
END
SET @Path += @DatabaseName + '-Truncate.bak'

--Backup Database
BEGIN TRY
 BACKUP DATABASE @DatabaseName TO  DISK = @Path WITH NOFORMAT,
 NOINIT, 
 NAME = N'TODEL-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
 PRINT 'Backup has Completed'
END TRY
BEGIN CATCH
 PRINT 'Backup Failed Stopping.................'
 RETURN            
END CATCH
BEGIN TRY
 EXEC ('USE [' + @DatabaseName + '] ; EXEC sp_executesql N''' + @Truncate +'''')
 PRINT 'Truncate Success'               
END TRY
BEGIN CATCH
 PRINT 'Truncate Failed........................'               
END CATCH

SharePoint Email Tester (SPUtility.sendmail)

Formatting email using SPUtility.SendMail is a bit of a nightmare, most development VM's do not have exchange installed on them so I have written a little console application that you can run in an integration environment or production environment (not recommended) and test your email formatting.   You could also use this for testing email to exchange integration is working it is a lot quicker than telneting to port 25 and manually constructing an email to exchange.  How it works:

1. Unzip the files
2. Open up the EmailContent.xml
3. Update the to / from and body etc.
4. Run the exe following the instructions on the screen (1 to send email and 2 to create a new xml file in case you delete it)

To put line feeds in the body of your mail message do the following in the xml file <![CDATA[<br/>]]>

You can download the exe from here and the source code from here

Let me know if you have any issues happy to help out.

User Profile Sync Issues

I just came across an issue in one of our development environments with User Profile Sync. UPS can be the bane of a SharePoint guy's existence if you run into issue with it.  I have spent quite a bit of time with Microsoft Premier Support working on UPS issues they are very well versed in resolving the issues with UPS.  I highly recommend reading this article on the UPS infrastructure it has completely changed since the MOSS 2007 days http://technet.microsoft.com/en-us/library/gg188041.aspx.  Here are my top tips for resolving UPS issues:

Patch your SharePoint Farm
UPS RTM had some "interesting features" which have most likely caused the issue your are currently having the June 2011 Cumulative Update resolved a substantial amount of the issues.  Patch to a minimum of June 2011!

Re-Provision the FIM configuration
This will fix 95% of User Profile Sync issues something will get corrupt with the connection between SharePoint and FIM.  Stopping the User Profile Synchronization Service de-provisions the FIM configuration and starting it re-provisions it.  It does a substantial amount of configuration that is why it takes so long.

Generally the following procedure will resolve the issue:

1. Stop the User Profile Synchronization Service (Central Administration --> Manage Services on server)
2. Wait until the service has completely stopped!!
3. iisreset
4. Start the User Profile Synchronization Service (Central Administration --> Manage Services on server)
5. iisreset

The iisresets appear to be needed due to the ability to reference the Microsoft.ResourceManagement dlls in the GAC.

If this does not resolve your issue you may need an extended outage:

1. Stop the User Profile Synchronization Service (Central Administration --> Manage Services on server)
2. Stop the User Profile Service 
3. Wait for an hour (I have seen configuration changes occur after the services stop)
4. Restart the server 
5. Start the User Profile Service 
6. Start the User Profile Synchronization Service and the User Profile Service
7. Wait for an hour (I have seen configuration changes occur after the services start as well)
8. Restart the server

Some of the error messages I have seen that this will resolve are:
System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.ResourceManagement, Version=4.0.2450.34, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.  

File Not Found

Disable All Content Deployment Jobs

Just a quick script drop we have around 20 content deployment jobs that require disabling and enabling when we are service packing / upgrading.  This number is increasing constantly and will eventually be quite large.  Here is a one liner to disable and re-enable your content deployment jobs, I have two ps1 files on the server (Turn Off Content Deployment Jobs and Turn On Content Deployment Jobs):

Enable
Get-SPContentDeploymentJob | ForEach-Object {$_.IsEnabled = $true; $_.Update()}


Disable
Get-SPContentDeploymentJob | ForEach-Object {$_.IsEnabled = $false; $_.Update()}

Automated SSL Certificate Import (Certutil)

Just a quick script drop.  You can use this script to import SSL certificates.  It will import all the the PFX files in the directory to your COMPUTER\Personal\ Store.  Which is the required store for your SSL Certificates.  It does not import intermediate or CA certificates.  I might add that functionality one day.  Any questions post a comment below:

1. Create a Directory called "SSL" (or something like that)
2. Drop all your PFX files into the SSL folder
3. Create a Batch File "InstallCert.bat" 
4. Run a command prompt as administrator
5. Change Directory to your batch file and run it

Batch File Contents


set CURDIR=%CD%
for /f "usebackq delims=|" %%f in (`dir /b "%CURDIR%" ^| findstr /i pfx`) do certutil.exe -f -p <PFX File Password> -importpfx "%CURDIR%\%%f"
pause

SharePoint 2010 Automated Patcher (Alpha Release)

Here is an alpha version of the source code.  There is still a lot of work to do one this one but it is a starting point.  I used AutoSPInstaller as the foundation for the launching http://autospinstaller.codeplex.com/.

USAGE:

1. Download SharePoint Foundation SP1 / SharePoint Server SP1 / Office Web Apps SP1 and put them in the SP directory
2. Download the latest cumulative update and put it in the CU directory
3. Copy files to all SharePoint Servers
4. Open Command Prompt as an administrator
5. Run Launcher.bat on each SharePoint Server
6. Run Products and Configuration Wizard on each server
7. Done

This is the end to end procedure that we follow here:

SharePoint Service Pack / Cumulative Update Procedure

The following procedure outlines the SharePoint patching process. This is appropriate for both Service Packs and Cumulative Updates. This does not include Windows Service Packs or Hot Fixes.

The procedure has been developed for a farm that has two Web Front End Servers and one Application Server. This procedure can be scaled up to suit a larger farm.

This procedure is to be used for the following SharePoint and Related Services Patches:

• SharePoint Foundation
• SharePoint Server
• Office Web Applications

SharePoint Patches will be packaged and installed via the command line. 

Upgrading a SharePoint Farm is a two phase process which consists of:

Phase	Description
Update Phase	During the update phase the farm can continue to be in production with no downtime. The update process consists of installing the binaries for the next version of SharePoint on each SharePoint server
Upgrade Phase	During the upgrade phase the SharePoint farm must be taken off line as concurrent connections during an upgrade can cause locks thus causing upgrade failures. During the Upgrade Phase the SharePoint Configuration database and Content databases are upgraded to the latest version.

Before proceeding with this procedure verify that the following conditions are true:

• All the Web Front End Servers are load balanced and are in the rotation of the TMG Load Balancer
• All the farm servers are operating correctly
• All the databases are active and operating correctly
• SQL Database backups are present from the previous evening

Do not proceed with the upgrade if any of the preceding conditions are not true. Resolve all issues before you continue.

Update Phase

The following illustration shows the sequence of steps that are required to install the update on the farm.

1. Copy installation package to all SharePoint Servers (this will be done ahead of time)
2. Drain Stop PNWB1 from rotation in the TMG Load Balancer
          a. Check to validate that all users have been relocated to PNWB2 by checking the Web Service Current Connections performance monitor variable
          b. Shutdown PNWB1
          c. Snapshot PNWB1
          d. Start PNWB1
3. Run Command Prompt as an administrator, Browse to SharePoint Patcher Location and Run Launcher.bat on PNWB1
          a. Monitor the Upgrade Status until it completes successfully
          b. Verify Upgrade
          c. Test to verify the server is operational
4. Bring PNWB1 back into rotation in the TMG Load Balancer
5. Drain stop PNWB2 from rotation in the TMG Load Balancer
          a. Check to validate that all users have been relocated to PNWB2 by checking the Web Service Current Connections performance monitor variable
          b. Shutdown PNWB2
          b. Snapshot PNWB2
          c. Start PNWB2
6. Run Command Prompt as an administrator, Browse to SharePoint Patcher Location and Run Launcher.bat on PNWB1PNWB2
          a. Monitor the Upgrade Status until it completes successfully
          b. Verify Upgrade
          c. Test to verify the server is operational
7. Bring PNWB2 back into rotation in the TMG Load Balancer
          a. Snapshot PNAP1
8. Run upgrade.bat on PNAP1
          a. Monitor the Upgrade Status until it completes successfully
b. Verify Upgrade
          c. Test to verify the server is operational
9. At this point all SharePoint Servers have been upgraded to the latest binaries

Upgrade Phase

The following illustration shows the sequence of steps that are required to finish the patching process by upgrading the farm servers. During this process, the sites that are being upgraded will not be available to users.

1. Remove both PNWB1 and PNWB2 from rotation in the TMG Load Balancer

2. Run SharePoint Products Configuration Wizard on the Central Administration Server PNAP1

3. Run SharePoint Products Configuration Wizard on PNWB1

4. Run SharePoint Products Configuration Wizard on PNWB2

5. Bring PNWB2 and PNWB2 back into rotation in the TMG Load Balancer

Upon completion of User Acceptance Testing remove the Snapshots created in the Update Phase