Category Archives: Powershell

Setting up a Single-box SharePoint 2013 Virtual Machine.

First thing first, below are some files which you would need in order to setup your own SP2013 Environment. VMware Workstation 9 – here SharePoint 2013 – here SQL 2012 – here Windows 2012 Server – here Visual Studio 2012 – here Here we go.

VMWare Workstation

  1. Install VMWare Workstation on to your local PC, Ensure that your local PC has got at least 8 gb RAM. You would need to allocate some of the RAM to your 2012 server later.
  2. Once completed the Workstation installation (i would not go through how you install software as it is simple), create a “New virtual machine“, choose Typical installation, PLEASE select option “I will install the operating system later“, else you will encounter error later. Then click Next until the vm files are created in your document folder “Documents\Virtual Machines\Windows Server 2012”
  3. Before powering up your vm, please map your Windows Server 2012 ISO file to boot up and install.

Windows Server 2012

  1. There is nothing much crucial to explain here. Just follow the setup wizard.
  2. Once completed, you will be asked to enter the administrator password bla bla bla.
  3. Remember to rename your windows name to something meaningful.. NOT Something like win-is2xx92243d which is totally making no sense. To configure this, go to Server Manager > Local Server > Click Computer Name > Change > Rename your server and click OK.Configure Windows 2012 Computer Name
  4. Restart your computer.
  5. Next, it is always good to set your Server IP address. Though this is not actually required for a Single Box Setup. But good to learn? =) Configure Server IP Windows 2012 1 then Configure Server IP Windows 2012 2
  6. Next is to setup Active Director Domain Services (AD DS), this is required for you to create services account for sharepoint and sql later on. Note! dcpromo.exe is deprecated for windows 2012. Sadly.
  7. Kindly go to Server Manager > Dashboard > Add Roles and Features
  8. Select Role-based or Feature-based installation
  9. Select your server from the Server Pool list and click “Next”
  10. Check on “Active Director Domain Services” check box, a prompt up will be displayed. Click “Add Feature”.
  11. Now, click NEXT all the way down until the installation completed.

Promote Windows 2012 to a Domain Controller

  1. Once your server 2012 is installed with AD DS, you have to promote your server to a domain controller.
  2. In the Server Manager, you may notice this Alert icon. Click on it and click on “promote this server to a domain controller”. Configure Domain Controller Windows 2012 1
  3. In the “Active Director Domain Services Configuration Wizard”, Select Add a new forest, put your favorite name. Configure Domain Controller Windows 2012 1
  4. Click Next, leave the rest default and specify your DSRM Password Configure Domain Controller Windows 2012 2
  5. Click Next all the way down until you see “Install” button. Kindly ignore those warning message. Click Install. Reboot and  you are done with DC Promo.

Service Account

  1. Now that your DC is up, you would need FEW accounts to setup your SharePoint 2013 environment. Note that i did not mention how many account required, because, ultimately, it depends on how segregated you want your farm to be. For Single Box solution and Less Error Prone. You may only need 3 accounts.
    1. Setup user account
    2. Server farm account or database access account
    3. SQL Server service account.
  2. Refer here  and here for the detail account.
  3. Open Run (Windows + R) then enter “dsa.msc” to open Active Director Users and Computers.
  4. Right Click  your Domain and add a new OU (this is my usual practice to park my sp account into an OU) Configure Service Account 1
  5. Add those 3 accounts.
    • spsetup
    • spfarm
    • sqlservice

SQL 2012

  1. Map your “SQLServer2012SP1-FullSlipstream-ENU-x64.iso” file to vm
  2. Run the ISO file in your VM, Select Installation Tab on the left and click New SQL Server stand-alone installation or add features to an existing installation.
  3. Click OK after the Setup Support Rules is completed, enter your product key (if you don’t have, use evaluation =D ), click OK. Include SQL Product Updates. Click OK~ These are pretty boring. Configure SQL 2012 2
  4. Next, select Role Mode. For evaluation purpose, i select All features with default.
  5. Name your SQL Instance
  6. Specify your services account. In my case, i use “SQL Server service account” that i have created previously. Configure SQL 2012 4
  7. Specify the Admin Account using the same SQL Server service account. account. (Well, this is for evaluation, you can still opt to use other account) Configure SQL 2012 5
  8. Analysis Configuration – Specify the same service account.
  9. Distributed Replay Controller – Specify the same service account and the Controller Name as your Server Name
  10. Click NEXT, NEXT, NEXT to install.. go get a coffee and come back after 30 mins…
  11. ..
  12. OK~ Next thing is to setup permission for SharePoint Setup Account. Base on the article above. You have to grant the account (In my case “spsetup”) DBCreator and SecurityAdmin permission.
  13. At this time, your server only allow Windows Authentication mode to access to your Database Engine. Please kindly Grant the SQLService account with Local Administrator right temporary. Log Off and switch to this account.
  14. Open your SQL Management Studio, Login via Windows Authentication, Right click on the “Login” node and “New Login”
    Configure SQL 2012 5.5
  15. Select the SharePoint Setup Account “spsetup” and go to Server Roles. Check the 2 server roles “dbcreator” and “securityadmin”
    Configure SQL 2012 6
  16. Click OK to proceed.
  17. For SharePoint 2013, there is one additional step is to change the Max Degree of parallelism to 1. Go to Database Engine, Right Click and Select Property. Under Advanced panel. Change the value to 1.
    Configure SQL 2012 7
  18. Click OK to proceed. Once this is completed, you may Switch User back to SharePoint Setup Account now (via Alt + Del + Insert) Note: Remove the SQLService account from local admin group once you are done with setting up the permissions.

SharePoint 2013

  1. Map your SP2013 iso file to your virtual machine (if you haven’t)
  2. Go into your vm and install SP2013 pre-requisites. The next few steps are for Offline Pre-requisite installation.
  3. Run Powershell with Administrator rights. Ensure you have executed the following command before Set-ExecutionPolicy RemoteSigned
  4. Run the following command. Make sure the path is where you store the pre-requisite files. Also, copy the “prerequisiteinstaller.exe” from your SP2013 iso to the same folder where your script is located.
    $SharePoint2013Path = "C:\Prerequisite"
    Start-Process "$SharePoint2013Path\PrerequisiteInstaller.exe" –ArgumentList "/SQLNCli:$SharePoint2013Path\PrerequisiteInstallerFiles\sqlncli.msi /IDFX:$SharePoint2013Path\PrerequisiteInstallerFiles\Windows6.1-KB974405-x64.msu /IDFX11:$SharePoint2013Path\PrerequisiteInstallerFiles\MicrosoftIdentityExtensions-64.msi /Sync:$SharePoint2013Path\PrerequisiteInstallerFiles\Synchronization.msi /AppFabric:$SharePoint2013Path\PrerequisiteInstallerFiles\WindowsServerAppFabricSetup_x64.exe /KB2671763:$SharePoint2013Path\PrerequisiteInstallerFiles\AppFabric1.1-RTM-KB2671763-x64-ENU.exe /MSIPCClient:$SharePoint2013Path\PrerequisiteInstallerFiles\setup_msipc_x64.msi /WCFDataServices:$SharePoint2013Path\PrerequisiteInstallerFiles\WcfDataServices.exe"
    
  5. After running the script, i encountered the following error “The tool was unable to install Application Server Role, Web Server (IIS) Role
  6. After 2 rounds of rebooting. The pre-requisite installation is finally done.
  7. Open the SharePoint Installer Splash Screen and hit Install SharePoint Server.
  8. Put in the Evaluation Product Key. NQTMW-K63MQ-39G6H-B2CH9-FRDWJ
  9. Click Next  until the installation is completed (yawn..)
  10. Once the installation is completed, leave the check box default “Run SharePoint Products Configuration Wizard”. Close your installation wizard.
  11. In the SharePoint Configuration Wizard, click Next.
  12. System will prompt you to stop the 3 services account, IIS, SP Admin Service, SP Timer Service, click Yes to continue.
  13. Select “Create a new server farm” and click OK.
  14. At this point, you may want to setup an ALIAS for SQL connection. (Good practice!)
  15. Fire Up “Run” and enter “cliconfg”.
  16. Under Alias tab, enter “sql” name and select “TCP/IP” and your sql server name (basically the same server since we are setting up a single box server.)
    Configure SharePoint 1
  17. Click OK and exit the CliConfg.
  18. Back to the SharePoint Product Configuration Wizard, enter the ALIAS that you just created as the SQL name.
  19. Next, specify the Login ID for “Server farm account or database access account” created earlier. (In my case “spfarm”).
    Configure SharePoint 2
  20. Specify the passphrase and click Next
  21. Specify the Central Admin port number and the authentication mode. Use NTLM for simplicity.
    Configure SharePoint 3
  22. Click Next to start configuring. Again you can go get some drink and come back like 20 mins time
  23. Tada~
    Configure SharePoint 4
  24. If you open your Task Manager, you will noticed that there is one new windows services running “AppFabric Service” which took you 300++mb of RAM. You may actually reduce the RAM usage to make room for other services. Refer here on how to reduce the AppFabric Memory Usage
  25. Once you close the Product Configuration Wizard, IE will be fired up to performance Configuration. Select all services if you like. Also, you may use a separate service account for the services that you intend to add. I would not recommend turning on Search Service Application as this will take up A LOT OF YOUR MEMORY!!!! Please note. Do it only if you have a lot of RAM in your VM Host.
  26. The Configuration may take quite some time. It happened to me before that it stuck forever. In case the screen doesn’t refresh or whatsoever, try to open the Central Admin again. The Services will still be created at the backend.

Configure SharePoint 5

Create Site Collection

  1. Once the Central Admin is done, you may proceed to create a Site Collection for your primary Web Application – 80
  2. Click on “Create Site Collection” under Application Management tab.
  3. Ensure the Web Application is selected correctly. Put in the Name and select your Site Template
    Configure SharePoint 6
  4. Specify the Primary Site Collection Admin – AHCHENG\spsetup
  5. Click OK!!! and Welcome to SharePoint 2013!

 

Note: If Newsfeed or MySite is having this “We’re almost ready!” error, please kindly check here to add your server as part of cache cluster

Configuring SharePoint Calendar Overlay using Powershell

When comes to deployment, my always first rule is to automate the deployment steps.

You could always use site backup and restore for deployment. But that is not my preference, always. Simply because you can’t do this every time when you hit a problem or when you need to reconfigure the whole site.

Well, after some tracing on the Calendar list object class. I found the CalendarSetting property of calendar list.

Unfortunately, there isn’t a fully integrated API for you to configure SP calendar overlay. You have to parse the calendar setting in XML structure yourself. But that really isn’t bad.

You simply have to follow the structure as shown…

<AggregationCalendars>
 <AggregationCalendar Id='%%RANDOM GUID%%' Type='SharePoint' Name='%%VIEW NAME%%' Description='' Color='2' AlwaysShow='True'
 CalendarUrl='%%Calendar URL%%'>
  <Settings WebUrl='%%WEB URL%%' ListId='%%LIST ID%%' ViewId='%%VIEW ID%%' ListFormUrl='%%DISPLAY FORM URL%%' />
 </AggregationCalendar>
</AggregationCalendars>

Where

%%RANDOM GUID%% : GUID e.g. “{76018083-3ed1-4a4c-b332-734106113448}”
%%VIEW NAME%%: View Name
%%Calendar URL%%: URL to the calendar that you want to overlay. e.g. “/Lists/My Calendar/calendar.aspx”
%%WEB URL%%: URL of your SharePoint site.
%%LIST ID%%: Calendar List ID that you want to overlay.
%%VIEW ID%%: View ID of the Calendar that you want to overlay.
%%DISPLAY FORM URL%%: Display Form URL of the Calendar e.g. “/Lists/My Calendar/DispForm.aspx”

Note the Color attribute for  AggregationCalendar, this attribute takes in the index number of the colour choices (you can refer to the SharePoint Calendar Overlay page for this)

Adding Powershell Script inside XML

At times, you will require to read your data in XML format using Powershell Script. Possible reason to do that is such that you can amend the XML file without editing your original Powershell Script. That’s provided that the standardized Powershell Script can fulfill your requirements.

To cater for More flexibility, you can actually insert Powershell Script within your XML. Here is how you can perform that.

Note that this required a minor change in your Powershell Script. (Not to worry, you only need to edit this once and for all).


$xml = (Get-Content .\yourxml.xml)

$expression = $xml.Scripts.script

#Optionally you can put a foreach loop to run each of your script

Invoke-Expression $expression

Below is the simple xml of mine

<scripts>
 <script>   Write-Host "Halo";   Write-Host "Testing";
 </script>
</scripts>
The output looks like this
Adding Powershell script into XML

SPListItem.Delete Best Practise – SharePoint SPList

Today, My Client has a new requirement to delete certain records from a SharePoint Custom List which contains 80+ k of items. First thing came into my head is to run a powershell script to delete the item based on a certain filter condition. (In my case, it is to delete item with column “Application” equals to “Event”)

I drafted this powershell script and ran with Administrator rights.


$web = Get-SPWeb <SharePoint URL>

$listName = "LIST NAME HERE"

$list = $web.Lists[$listName]

$items = $list.Items | Where {$_["Application" ] -match "Event"}

foreach($item in $items)

{

Write-Host "Deleting " $item.ID

$item.Delete()

}

Well, with the huge number of list item in the list, one SPListItem took me 40+ second to delete! That’s ridiculous. And what if i have 10k record to be deleted? It would take up 400k seconds (111 hours) to complete!

After few minutes of googling, some suggested to use CAML Query and limit the number of query by using RowLimit = 1000

This doesn’t really help as i really want to delete them ALL at once!

The better way to delete the items is to change the way you query the data especially when comes to retrieving the List item. Here it goes


$web = Get-SPWeb <SharePoint URL>

$listName = "LIST NAME HERE"

$list = $web.Lists[$listName]

$query = New-Object Microsoft.SharePoint.SPQuery

$query.RowLimit = 999999

$query.Query = "<Where><Eq><FieldRef Name='Application'/><Value Type='Text'>Event</Value></Eq></Where>"

$items = $list.GetItems($query)

foreach($item in $items){

Write-Host "Deleting" $item.ID

$list.GetItemById($item.ID).Delete()

}

Woosh!!

the script completed in 30+ mins!

Simple Web Services call using PowerShell

At times, you will be requiring to test your web service if it is working.
To do that, simply fire up your PowerShell and run the following scripts

$cre = Get-Credential
#system will prompt for login and password
#This would be a standard NTLM authentication
$proxy = New-WebServiceProxy -URI <YOUR WEB SERVICE URL HERE> -Credential $cre

Enter $proxy to check if the object is added

To Check the Operation parameters, Enter $proxy.AddAction (for example)

FAST Search Keyword Query with Scope using Powershell

Just wanted to share the following script of mine. Hope it helps.


#Parameters

#-scope : The search scope that you defined in Central Admin

#-keyword: Keyword to search.

Param
(
[string]$scope = $null,
[string]$keyword = $null
)

if($keyword -eq "")
{
Write-Host "You must key in the Keyword"
exit
}

$proxy = Get-SPEnterpriseSearchServiceApplicationProxy
$query = New-Object Microsoft.Office.Server.Search.Query.KeywordQuery($proxy)
$query.QueryText = $keyword
if($scope -ne $null)
{
$query.HiddenConstraints = "scope:`"" +$scope + "`""
}

#Add any properties you would like to return here
$query.SelectProperties.Add("title") | Out-Null
$query.SelectProperties.Add("url") | Out-Null

$query.ResultTypes = [Microsoft.Office.Server.Search.Query.ResultType]::RelevantResults
$query.RowLimit = 100
$query.ResultsProvider = [Microsoft.Office.Server.Search.Query.SearchProvider]::FASTSearch

Write-Host "Executing keyword search..."
$results = $query.Execute()

$relevants = $results["RelevantResults"]
#Or you can use [Microsoft.Office.Server.Search.Query.ResultType]::RelevantResults instead
Write-Host "Total " $relevants.TotalRows " results"
Write-Host ""
$relevants.Table

Do note that this script is trying to make FAST Search query. If you are using SharePoint Enterprise Search, please change the ResultsProvider to SharePointSearch

Note also this is not FQL, i tried using “$query.EnableFQL = $true” and the scope didn’t work.

Event ID 3760 – SharePoint Foundation – SQL Database not found

Exception


SQL Database 'YOUR DATABASE_FOLLOWED BY GUID' on SQL Server instance 'YOUR INSTANCE' not found. 
Additional error information from SQL Server is included below.

Cannot open database "YOUR DATABASE_FOLLOWED BY GUID" requested by the login. The login failed.
Login failed for user 'YOUR USER ACCOUNT'.

After checking the SQL, i noticed that the database is not even exist in my database server. I suspect that the previous deleted service application jobs did not clean up properly. To get rid of this 3760 Critical error. Simply Execute the following powershell script.

Note: This script will delete all your non-existing databases. Please do a backup before executing it

Add-PSSnapin Microsoft.SharePoint.Powershell
$dbs = Get-SPDatabase | Where {$_.Exists -eq $false}
$dbs | ForEach-Object { $_.Delete()}

The error is now gone =)

SharePoint Calendar Connect to Outlook is not working. Events not synchronized

I came across this funny issue when my client is trying to download/synchronize SharePoint calendar events to his MS Outlook.

Connect to Outlook Ribbon Button

The calendar instance is added successfully into his Outlook. However, all the events are not populated and the following message shown in the outlook status bar.

Outlook Send/Receive Error

Resolution

Execute the following script via SharePoint Powershell. Kindly change the calendarName to your calendar name.


#Here we go.

$web = Get-SPWeb http://yoursite

$calendarName = "My Calendar"

$calendar = $web.Lists[$calendarName];

$calendar.ExcludeFromOfflineClient = 0;

$calendar.Update()

This script simply enable the calendar to be available for offline client download.

Optionally, you can go to your Calendar’s List Setting > Advanced Setting and turn “Offline Client Availability ” on.

Offline Client Availability

Go back to your Outlook, Hit “Shift” + F9 to refresh the calendar.

TADA~

SharePoint Calendar on MS Outlook

Turn on/off/ondemand Developer Dashboard of SharePoint 2010 using Powershell

Honestly, I personally prefer using Powershell than the old traditional Command Prompts. (no offense to those CMD lovers =X ). It will come to this day where you are asked or required to trace the performance of your SharePoint site. Fortunately, SharePoint 2010 comes with OOTB Developer Dashboard which allows you to track the response times of each of your module within a page.

It is not turned on by default, and here is the tricks to turn it on.

Simply execute the following Powershell Script.

$dds = [Microsoft.SharePoint.Administration.SPWebService]::ContentService.DeveloperDashboardSettings
$dds.DisplayLevel = "On";
#"Off" to turn it off, or "OnDemand" to turn it on demand mode.
$dds.Update()

If you turn it on as “OnDemand” mode, you shall see an icon  Sharepoint 2010 Developer Dashboardon the top right hand corner of your SharePoint page if you didn’t modify your master page. [To be specific, the Sharepoint:DeveloperDashboardLauncher web control]

Add SPUser to SPGroup using Powershell

Adding user to SharePoint group manually is a painful process. Not to mention if you have tons of users using your SharePoint site.  To make life easier, I actually came out with a simple powershell script and XML (where you store the user mappings) to automate the process.


$webUrl = Read-Host "Enter SharePoint Web URL"
if($webUrl -eq "")
{
 $webUrl = "[Your default http]";
}
$web = Get-SPWeb $webUrl
$xml = (Get-Content .\PumpUser.xml)
#Put your xml (in this case 'PumpUser.xml') in the same folder where you place your powershell script
foreach($group in $xml.Groups.Group)
{
 $title = $group.Title
 $spGroup = $web.SiteGroups[$title]
 foreach($user in $group.Users.User)
 {
  $temp = $web.EnsureUser($user)
  $spGroup.AddUser($temp)
  Write-Host "Added "$temp" to "$spGroup.Name
 }
}

The xml goes like this.

<?xml version="1.0" encoding="UTF-8"?>
<Groups>
 <Group>
  <Title>Administrator Group</Title>
  <Users>
   <User>userLogin1</User>
   <User>userLogin2</User>
  </Users>
 </Group>
</Groups>

Simple and easy =)