Why PowerShell and SharePoint 2010 Are So Cool

Anybody who has read one of my posts knows that I am a huge PowerShell geek, I tweet about it, I blog about it, I talk about it SO much at my client that my contacts at said client make me add a dollar to the “PowerShell Jar” whenever I use the word “PowerShell.” 🙂

People who don’t ‘touch’ SharePoint may not completely understand why PowerShell and SharePoint are so cool, so I wanted to put together a quick, fairly non-technical post about why they are so cool…

  1. PowerShell allows SharePoint Developers and IT Pros to automate tasks that in the past would’ve required a LOT of scripting – however, we can do it much, much quicker and easier in PowerShell.
  2. PowerShell allows SharePoint IT Pros access to something that in the past we haven’t had easy access to – the SharePoint API or Object Model. (Note: I’m not a developer so if I use a term incorrectly, forgive me. :))

#1 is fairly obvious, but #2 is really where the power comes in.  There are times when an IT Pro is administering SharePoint and he or she needs to do something that just isn’t possible in the UI or through the standard command interfaces.  Having the ability to call methods from the SP Object Model in PowerShell takes away a lot of headaches!

Example: I need to forcefully delete a service application but can’t do so because I blew away the database.  Remove-SPServiceApplication won’t work at this point because that’s the graceful way to do it and I can’t communicate with the service application database.


$app = Get-SPServiceApplication -Identity

That’s right, two quick lines of code and I’ve now deleted the old service application that I couldn’t delete using Remove-SPServiceApplication or through the Central Administration interface.

For other examples of using PowerShell and the Object Model to accomplish tasks that in prior (non-PowerShell-enabled) versions were not possible without code – see one of my other recent posts…


Connect to External Content Type (ECT) using Impersonated Identity

While this might not be the best idea for exposing LOB data, there may be times when you want all authenticated users to have the ability to have read access to an External List. This can be accomplished fairly easy, using out-of-the-box tools and menus – no code needed.
Here’s how (Note: I’m assuming you already have working BDC and Secure Store Service Applications):
1. Create a new Secure Store Target Application, call it whatever you want – but make sure you choose Group as the type.
2. In the Target App contextual menu, use Set Credentials to apply the credentials you wish to use.
3. Create a new External Content Type (or edit an existing) – in the External System settings, enter the DB Server, DB Name and choose Connect with Impersonated Windows Identity. Enter the name of your Target Application in the Secure Store Application ID field.
4. Create an External List using your new External Content Type. It should work for anyone who has permissions to the SPWeb.
It’s really as simple as that, and I’m mainly blogging this because as simple as it is – I continue to forget a step here and there. 🙂

Create a PerformancePoint Service Application Database without GUIDs

There are many resources out there for creating the majority of SharePoint 2010 Service Applications using PowerShell. This gives you more control in terms of defining clean database names. One specific application that I have yet to find a legitimate answer for is the PerformancePoint Service Application.

Some people have suggestions on how to rename the database in SQL Server and then run a Set-SPPerformancePointServiceApplication command in Powershell. While this seems accurate (and in reality is close), it doesn’t rename the MDF or LDF files.

One of many possible solutions is to do the following in PowerShell:

$performancePointSAName = "PerformancePoint Service" $saAppPoolName = "SharePoint Web Services" 
$newDb = "SP2010_SA_PerformancePoint_DB" 
New-SPPerformancePointServiceApplication -Name $performancePointSAName -ApplicationPool $saAppPoolName 
New-SPPerformancePointServiceApplicationProxy -Default -Name "$performancePointSAName Proxy" -ServiceApplication $performancePointSAName 
Get-SPServiceInstance | where-object {$_.TypeName -eq "PerformancePoint Service"} | Start-SPServiceInstance

This will give you a working PerformancePoint application with a GUID. Next, in SQL Management Studio; backup the existing PerformancePoint database to a .bak file.

Create a new database with a clean name such as “SP2010_SA_PerformancePoint_DB”, restoring from your .bak file. Next, back in the SharePoint Management Shell; run the following command:

Set-SPPerformancePointServiceApplication -Identity $performancePointSAName -SettingsDatabase $newDb