Moving a project server Database and pwa site collection

Project Server 2013 database is a resource intensive database becuase of having Published, Draft, Reporting and Archive Tables.  Although Microsoft had distributed them to 4 databases in past but decided to stick with one in 2013.  For high project server usage Microsoft Recommended us to move Project Server Database and PWA Site collection database to a seperate SQL Server and Seperate Drive. 

PWA Site Collection can be moved to new Content Database using Move-SPSite cmdlet.  It guess it is simple process but The PWA Database move can be done using User interface if you are a Farm admin but process is somehow resulted in access denied for me.  I am putting it down.

  • Backup the PWA Database and Restore using SQL Server management studio on 2nd SQL Box.
  • Open Central Administration –> Manage Service Application –> Project Server Service Application
  • Click on down arrow next to PWA instance and Click Edit (Note you must have DBO on PWA DB to edit)
  • Change SQL Server name and click OK.
  • Browse the PWA Site.

You can also use PowerShell. 

  • Dismount the PWA Database that will automatically dismount the instance
  • Dismount the PWA Instance
$PSA = Get-SPServiceApplication | Where{$_.TypeName –Eq “Project Server Service Application”} #Change the name if required.

Dismount-SPProjectDatabase –Name ProjectWebAppDB –ServiceApplication $PSA

or Dismont the PWA instance

#If you have a single Instance
Get-SPProjectWebInstance | Dismount-SPProjectWebInstance

#if you have multiple instances
Dismount-SPProjectWebInstance -SiteCollection ""

#You can also use Get-SPProjectWebInstance and then get the ID to remove it.

then you can

Add-PSSnapin Microsoft.SharePoint.PowerShell -ea 0 
Mount-SPProjectDatabase -Name ProjectWebAppDB -DatabaseServer ProdSQL2 -WebApplication
Mount-SPProjectWebInstance -DatabaseName ProjectWebAppDB  -DatabaseServer ProdSQL2 -SiteCollection
Get-SPProjectWebInstance | Select Url, DatabaseName, Id

Browse and Test the Site. 


Make sure you update any Reporting Services or Excel Reporting data connections on your BI Sites.  Better to publish them again.

Loading SharePoint Snapin on all PowerShell Consoles and PowerShell ISE by Default


While working on SharePoint Servers we have to run PowerShell script a lot of times and most of the time we use SharePoint Management Shell. The biggest reason to use it, is off course the Loading of SharePoint Snapin by default. It is also a fact that SharePoint management shell is limited for many reasons. Very small editing window, quick editing is not enabled, Copy Paste is difficult and there is no Intellisence or help available while we type the cmdlets and select parameters. It also does not allow us to save the script so we have to write from scratch most of the time. Although we can modify the PowerShell console settings on per user basis but it will require lot of work from everyone.

To solve this problem we can add SharePoint Snapin to load on all servers in the Farm by default. This can be done right after Farm build process and will improve efficiency of SharePoint support team.


1. Generate PowerShell Profile file using script.

2. Copy SharePoint Snapin load script into the file.

3. Test PowerShell ISE and PowerShell Console.

Configuring PowerShell to Load SharePoint Snapin

1. Click Start à All Programs à Accessories à Windows PowerShell à PowerShell ISE

2. Open PowerShell ISE and type the following script to generate a Profile for all users.

If (!(Test-Path $profile.AllUsersAllHosts))


New-Item –Type File –Path $profile.AllUsersAllHosts –Force


Powershell_ise $profile.AllUsersAllHosts

3. Press F5.


4. This will open profile.ps1 file in PowerShell ISE.

5. Type the following.

$ver = $host | select version

if ($ver.Version.Major -gt 1) {$host.Runspace.ThreadOptions = “ReuseThread”}

if ((Get-PSSnapin “Microsoft.SharePoint.PowerShell” –ErrorAction

SilentlyContinue) -eq $null) {

Add-PSSnapin “Microsoft.SharePoint.PowerShell”


6. Save and Close PowerShell ISE

Testing PowerShell ISE and Windows PowerShell

1. Click Start à All Programs à Accessories à Windows PowerShell à PowerShell ISE

2. Type Get-SP


3. Click on Windows PowerShell ICON next to Start Button to open PowerShell Console.

4. Type Get-SP and press Tab key to see Auto completion of SharePoint cmdlets. Press tab few times to browse other cmdlets.

5. Type Get-SPSite and press enter to see if SharePoint Site collection are listed. (If you have rights)


This process has to be done on all SharePoint servers in the Farm after we build the SharePoint Farm and add servers. The profile.ps1 must not be created using Notepad or windows explorer as rights for that file are not copied correctly.

Installing and Configuring Workflow Manager Farm

Step 1 – Installation

Download and Installation Process

Go to

Click Download and install. Type Workflow in Search Box

Click Add on Workflow Manager and Workflow Manager Client.

Click Install

Choose Appropriate Option

Wait for Installation to Finish

Click Continue

Click Finish

Workflow Management Configuration

  1. Farm Management Database
    1. Enter SQL Server Instance
    2. Add Database prefix and click Test Connection.
  2. Configure Instance Management Database
    1. Enter SQL Server Instance
    2. Add Database prefix and click Test Connection.

Scroll Down from Right scroll.

  1. Resource Management Database
    1. Enter SQL Server Instance
    2. Add Database prefix and click Test Connection.
  2. Configure Service Account (Must be a domain – Non Farm Account)
    1. Enter user ID
    2. Enter Password

Scroll Down from Right scroll.

  1. Enter and confirm Certificate Generation key.
  2. Configure Ports. (Leave as Default)
  3. Check “Allow Workflow Management over http on this computer” for Development server only.

  1. Click Right Arrow à to Go to Service Bus Configuration

Service Bus Configuration

  1. Farm Management Database
    1. Enter SQL Server Instance
    2. Add Database prefix
    3. Click Test Connection.
  2. Configure Gateway Database
    1. Enter SQL Server Instance
    2. Add Database prefix
    3. Click Test Connection.

Scroll Down from Right scroll.

  1. Message Container Database
    1. Enter SQL Server Instance
    2. Add Database prefix
    3. Click Test Connection.
  2. Check Use the Same Service Account Credential as Provided by Workflow Manager
  3. Check Use the same Certificate generation key as provided by workflow manager

  1. Leave REST as Default or Change if needed.

Scroll Down from Right scroll and Click à

Wait for Summary Page to confirm.

Registering Workflow Manager in SharePoint

Start SharePoint Management Shell

$Site = “”

$WorkflowHostUri = “‘”

#(12291 for http and 12290 for HTTPS)

Register-SPWorkflowService -SPSite $Site –WorkflowHostUri $WorkflowHostUri -AllowOAuthHttp

#Use –Force to overwrite existing configuration

Verify 2013 Workflow Configuration

  1. Install and open SharePoint Designer 2013.
  2. Open SharePoint Site
  3. Click on Workflows à List Workflow à Select a List e.g. Documents.
  4. SharePoint 2013 Workflow Must be visible in Platform Type.

  1. Workflow Service Application Proxy will appear in Manage Service Application.

Installing and Configuring Workflow Manager – 2nd Server

Adding 2nd Server to the Workflow Farm

Perform Workflow Manager Installation as per installation guide. Do no create a new Farm.

Perform the following steps on the 2nd Server

Click Start à All Programs à Workflow Manager à Workflow Manager Configuration

  1. Click on Join an Existing Farm.

  1. Workflow Manager Farm Management Database
  • Enter SQL Server Instance
  • Database name
  • Click Test Connection
  1. Service Bus Farm Management Database
  • Enter SQL Server Instance
  • Database name
  • Click Test Connection

Click à


  1. Enter Service Account Password.
  2. Enter Certificate Generation Key (That was setup in the Farm Configuration)
  3. Check Allow Workflow Management over HTTP on this computer (For Dev Only)

Click à


  1. Check “Use the same service account credentials as provided for workflow manager”
  2. Check “Use the same Certificate generation key as provided by Workflow manager”

Click à


9 – Click Confirm Button.



Confirm the Workflow Manager Farm Configuration

Start SharePoint Management Shell



Make sure Workflow front end and backend services are running on both server.

Note: If you have already registered workflow Manager you must run the command again with –Force attribute.

SharePoint 2013 Troubleshooting and Diagnostic Options for End Users and Remote Developers

SharePoint 2013 Developer Dashboard


The Developer Dashboard is an instrumentation framework introduced in Microsoft SharePoint Foundation 2010. Similar in concept to ASP.NET page tracing, it provides diagnostic information that can help a developer or system administrator troubleshoot problems with page components that would otherwise be very difficult to isolate.


SharePoint 2013 introduced a newly build and improved Developer Dashboard. It’s a fantastic improvement to its predecessor and contains a huge number of improvements. Some of them as listed below.

No Longer Linked to Site or Web

One of the most notable ones is that it’s no longer a control on your page which only shows you information about the current request. It’s now a separate window which shows you all requests since you started the developer dashboard session. We can update the window to load requests that were executed after the windows is loaded.

Performance Improvements and Usage service application

As compared to SharePoint 2010 version of Developer Dashboard, it now runs outside of SharePoint site, it does not impact site or web performance directly or indirectly. Developer Dashboard uses SharePoint 2013 Usage and Health Data Collection Service Application. To use all features of Developer Dashboard, Usage and Health Data Collection Service must be enabled.

Built in ULS Window

Developer Dashboard now shows the SharePoint ULS Logs. The logs are filtered based on current selected request. You can search for Correlation ID using Search by Browser feature but there is not feature to do any other filter.

Note: As some of the backend or long running operations cannot be captured by ULS Window. The Great old ULSViewer is still needed to explore further.

Not Part of Master Page

DD in SharePoint 2013 is not part of your master page. It is now a separate window that can executed from anywhere using a hyperlink.

Support for Customization

Developer Dashboard can be customized because it is now a layout page. We can add JavaScript/jQuery to show or hide tabs and other stuff.

No On-Demand Option

SharePoint 2010 DD had On-Demand switch that allows developers to enable when they need. But due to external nature of the tool, On-Demand feature is no detracted. It can be either turned on or off.

Enable and Disable Developer Dashboard

Developer Dashboard can be enabled using PowerShell or SharePoint Object Model. It is disabled by Default. It is enabled globally and cannot be disabled from site or web level.

Enable SharePoint Developer Dashboard

svc = [Microsoft.SharePoint.Administration.SPWebService]::ContentService

$dds = $svc.DeveloperDashboardSettings

$dds.DisplayLevel = “On”


Disable SharePoint Developer Dashboard

svc = [Microsoft.SharePoint.Administration.SPWebService]::ContentService

$dds = $svc.DeveloperDashboardSettings

$dds.DisplayLevel = “Off”



Developer Dashboard Walkthrough

Can be enabled by Icon on the right side of the screen.


Blank Screen


The window can be refreshed by clicking on refresh ICON.

Scope Tab

Scopes windows will show hierarchal information about method execution and time in milliseconds. Developers can review and improvement the code based on this window.

Let’s Select a Request


Although the above list shows out of the box calls in SharePoint code but custom code can also be added using SPMonitoredScope class, to allow developers to designate portions of their code so that they can monitor usage statistics in the Unified Logging Service (ULS) logs and the Developer Dashboard.


using (new SPMonitoredScope(“My Scope Name”))






ULS Tab shows the related ULS messages directly into the developer Dashboard window. The Logs are detailed but get filtered by the selected request.

There is no way to easily Search or filter by Correlation ID. Browser Search does not work all the time. So scroll is the only way most of the time.


Shows the query Execution time allow you to download plan that can be executed in SQL Server Management Studio.

Some other tabs like Scenarios, Animation and MDS can be used to see performance of Page JavaScript calls.

Using Business Connectivity Service Display SharePoint 2013 ULS Logs

Business connectivity Services allow us to bring external data to SharePoint. We can use External Content Type, External List and Business Data Web Parts to display the data. Data can be filter and sorted. Because SharePoint generates very high number of ULS entries, it is recommended to use this solution in NON-Production environments only.

Enable Diagnostic Data Provider: Trace Log

In order to get started with the solution, we must first enable the Diagnostic Data Provider: Trace Log timer job. This timer job writes the ULS Log entries into the WSSLogging database and creates a view called ULSTraceLog that can be used as the basis for a BCS solution. Follow these steps to enable the job:

  1. Open Central Administration
  2. Click Monitoring à Review Job Definitions àDiagnostic Data Provider: Trace Log
  3. Change the Frequency of timer job if needed.
  4. Click the Enable button
  5. Click the Run Now button to execute the Job.

Let’s Open SQL Server Management Studio to verify the View.

Configuring a Secure Store Application to Logging

  1. Open Central Administration
  2. Under Application Management à Click on Manage Service Application à Secure Store Service
  3. From Ribbon Click New
    1. Type Target Application ID
    2. Type Display Name
    3. Type Contact Email.
    4. Choose Target Application Type as Group

    1. Click Next
  4. Click Next

  5. In Target Application Administrators textbox, Type Farm Account or other required accounts.
  6. In Members type “Everyone”. Click OK

  7. We have set credentials that will be used for all users to connect to Database as Single Sign on.

  1. Replace Contoso\administrator with Farm account or service application account.

Configuring Database Permissions for WSS_Logging

Before you can create the External Content Types against the view, you must grant yourself permissions to the WSS_Logging database. You should also give permissions to the account that you are using to run the BDC Service Application and the account being used for the Central Administration Application Pool.

Make Sure SPfarm and svc_spsvcapps has permissions on WSS_Logging DB.

Creating External Content Type

You have two options to create External Content Type.

  1. Re-use an Existing BDCM File provided by ECS
  2. Create a new External Content Type using SharePoint Designer

Reusing an Existing BDCM File

Making Required Changes per Farm

  1. Get ULSLogging.bdcm file and open it in notepad.
  2. Under LobSystemInstance Properties section
    1. Set “RdbConnection Data Source” property value to your SQL Server Instance name e.g. SP2013Dev
    2. Set “SsoApplicationId” to Secure Store Application Id created in above step.

  1. Under Entity à AccessControlList à Set Principal (Contoso\administrator) to Farm account (Or your personal account). (Replace 7 instances of Principal from contoso\administrator to your farm or personal account.
  2. Under Actions à Action à Change Url from to your site url.
  3. Save and Close the File.

Uploading BDCM File to Business Connectivity Service Application

  1. Open Central Administration
  2. Under Application Management à Click on Manage Service Application à Business Data Connectivity (BDC)
  3. Click Import from Ribbon à Browse and Select the file. Check Permissiong check box and click Import.

  1. Fix any Issues in Import process. Click OK When Done.
  2. Set Object Permission if needed.

Creating a New ECT

If you want to create new External Content Type for ULS Loging you have to following the following steps.

  1. Start SharePoint Designer and Open the SharePoint Site where you want to create the external content type.
  2. From Left Sections à Click the External Content Types object.
  3. Now click “External Content Type” Button on the ribbon

  1. Name the new ECT “ECSWSSLogging”.

  1. Now click “Operations Design View” on the ribbon.

  1. Click the Add Connection button. Choose Data Source Type as SQL Server and Click OK.

  1. Provide the SQL Server connection Details
    1. Enter Database Server
    2. Enter Database Name and Click OK.

  1. From Data Source Explorer à Expand the new WSS_Logging, Expand the Views folder and locate the ULSTraceLog view. Select this view.

  1. Right-click the ULSTraceLog view and select “New Read List Operation” from the context menu.

  1. When the Wizard starts, name the new operations “RecentLogEntries”, change the display name if necessary and click Next.

  1. On the Next Screen, click the “Add Filter Parameter” button.
  2. Change the Properties to refer to RowID and set a default value of 100.

  1. Now click the “Click to Add” hyperlink. We will add a Limit filter to restrict how many rows are returned from logging database.

  1. Select filter type as Limit and Filter Field as RowId and click OK.

  1. Set Default Value to 100. Click Next

  1. On the next screen, select the RowID field. Then check the “Map to Identifier” box.

  1. Then uncheck the “Required” box.
  2. Click Finish. The default view of the list is completed.

  1. We will create a view that is filtered by Correlation ID so that events are easier to find. Select the ULSTraceLog view again. Right-click the view and select “New Read List Operation” from the context menu.
  2. When the Wizard starts, name the new operations “LogEntriesByCorrelationID” and click Next.

  1. On the Next Screen, click the “Add Filter Parameter” button.
  2. Select the CorrelationId field as the Data Source Element.
  3. Now click the “Click to Add” link to define the filter.
  4. Define a new Comparison Filter that can be used to filter out events that do not match the Correlation ID.

  1. Click OK
  2. Add a Filter Parameter to Limit the number of rows (Step 21)

  1. Then Click Next
  2. On the next screen, select the RowID field. Then check the “Map to Identifier” box. Then uncheck the “Required” box. (Step 16)
  3. Click Finish. The filtered view of the list is completed.
  4. Now you’ll create an operation to show the details of an individual log entry. Right-click the view and select “New Read Item Operation” from the context menu.

  1. Type Operation Name and display name and Click Next
  2. Choose Row ID and Select Map to Identifier then Click Next

  1. Select RowId and Set Map to Identifier and uncheck required. Click Next

  1. Select File à Save from the menu.

  1. From SharePoint Designer breadcrumb navigation Click on ECSWSSLoging

  1. Click On WSS_Logging next to External System.

  1. From Authentication Mode Drop down select Impersonated Windows Identity à Type Secure Store Application id Create above.

  1. Click Yes on property change dialog.

  1. Close SharePoint Designer

Creating External List on SharePoint Site.

  1. Open your SharePoint Site.
  2. Click Site Contents à Add an App
  3. Select External List
  4. Type List Name
  5. Type External Content Type and click confirm ICON or Select from List and click Create.

  1. Go to your List.

Using BDC Web Part to display Logs for a Correlation ID

  1. Go to your SharePoint Site.
  2. Click on Site Settings (Actions) Icon and choose Add a Page à Type Page Name and click Create.

  1. Click Insert Ribbon à Web Part à Click on Business Data Category à Business Data List à Click Add

  1. Click on “Open the tool Pane” à In Web Part Properties type content type name and from View Select “Get Log Entries by Correlation ID View”. Click Apply then
  2. Click on Page ribbon and click Save.
  3. Get a Correlation ID either from SQL Server or any ULS Log from past 10 to 15 minutes ago.

  1. Type and ID and click Retrieve Items.