Ramblings of a SharePoint guy

SharePoint 2007 Upgrade – Identifying Customizations

2013-06-11T18:07:00Z

During an upgrade from SharePoint 2007 to 2010, knowing where custom webparts and features are deployed can be useful for targeted testing and mitigation. Here's is some TSQL to help identify where those sites are.

First, from SharePoint 2007, execute the PowerShell:

ststadm -o enumallwebs -includewebparts -includefeatures -databasename WSS_Content_YourDBName > sp2007EnumWebsOutput.txt

This command essentially lists every web in your database and the features and webparts it's using. Compare that list with a known list of out-of-box (OOB) features and the deltas will be the customizations. I wrapped that up into a TSQL command the creates a nice pivot table with webs and customizations. To use it, copy the sp2007EnumWebsOut.txt to an instance of SQL Server and execute the attached TSQL (attached). The result with be 3 SQL Results with the source of the features listed (OOB, Fab40 or Custom). Copy and paste those to Excel and you can do whatever filtering and sorting you need to identify the sites.

SSL Sites with Hostnames in SharePoint 2010 and SharePoint 2013

2013-06-07T13:53:03Z

As you implement SharePoint in your security-conscious enterprise, you'll run into a scalability issue: IIS 7.5 (Windows 2008 R2) requires dedicated IP addresses for each SSL site on each Web Front End. Remember that SAML claims require end-to-end SSL – if you're using SAML, you're affected!

Why is this a problem? Consider the most basic SharePoint architecture with a single Web Application:

This architecture uses 4 IP address: 1 for the VIP and 3 for the Web Front Ends. Next, you've followed the Best practices for My Sites article, and deployed MySites to a dedicated Web Application and you require SSL. Your architecture is now:

The IP footprint has grown from 4 to 8. If we added another web application requiring SSL, this would need a total of 12 IP addresses and there-in lines our scalability problem: In SharePoint on IIS 7.5 (Windows 2008 R2), the traditional IP address footprint is calculated with the formula

(Number of Web Servers + Number of Network Load Balancer Devices) * Number of Web Applications

A bigger, hypothetical network of 5 servers, 3 web applications and 1 NLB, your IP footprint would be a whopping 18 IP Addresses! In a non-SSL environment, you would just configure hostnames, but when you try to select the certificate in IIS 7.5, the hostname header gets grayed out:

So, what do we do? NetSH.exe! With NetSH, we can tell the machine to "encrypt traffic on <x> port with <y> certificate". Combine it with a wildcard certificate and all IIS traffic will be encrypted with the port, but you can still use hostnames. To execute, from an elevated command prompt, execute:

Certutil –store My

Location the applicable certificate on the output and notate the hash value (we're going to need it!). My hash is b063314fab008a45b5a1afb0f0dee45b59eb2584

Now execute the NETSH command:

netsh http add sslcert ipport=0.0.0.0:443

certhash=b063314fab008a45b5a1afb0f0dee45b59eb2584

appid={00112233-4455-6677-8899-AABBCCDDEEFF}

The command's breakdown:

add sslcert is self-explanatory

ipport=0.0.0.0:443

is telling NetSH the command applies to "all traffic from this machine on port 443"

certhash=b063314fab008a45b5a1afb0f0dee45b59eb2584

is the applicable certificate

appid={00112233-4455-6677-8899-AABBCCDDEEFF}

is an arbitrary GUID for identification. Pick anything you want for this value!

Or to summarize "Encrypt all traffic on port 443 with this cert".

Now we can create a web application in SharePoint, choosing an applicable host header and port 443:

Create your site collection and browse it. Pull up the Certificate and wala! You've had the certificate applied (Note, I did NOT go into IIS and choose the certificate!).

This approach only works if you have one-and-only-one certificate per server, usually in an extranet/intranet environment. In a hosting environment, this may not be applicable unless all your sites are sharing an SSL certificate.

Question: Isn't this Server Name Indication, newly available in Windows 2012 / IIS8?

Answer: No. SNI is a many (certificates)-to-many (sites) solution. IIS7.5 cannot do SNI, but SNI can fulfill the same user scenario of this approach. If you're running Windows 2012, use SNI. If you're on Windows 2008 R2, use the above approach.

Question #2: I'm using dedicated IPs and want to switch to this approach. How do I do it?

Answer: If you have sites currently using 443, netsh will give you an error. To fix it, you need to remove all your 443 bindings and re-add them without certificates. If you use the IIS Admin GUI to do it:

Change the current port 443 binding to a new port (4433 for example)
Create a new binding on 443 with the specified hostname (but do not select a certificate!)
Delete the 4433 binding and proceed with your netsh commands

As an alternative, you could use APPCMD or PowerShell to do the same thing; the end result is what's needed: no bindings on 443 with specified certificates.

SharePoint 2010 Architecture and Disaster Recovery – when to use UPRE and when to log ship

2013-03-07T18:21:06Z

The SharePoint 2010 Database types and descriptions TechNet article was recently updated (March 5^th, 2013) to allow log shipping the Profile and Social databases for the User Profile Service. This change aligns SharePoint 2010 to match the similar TechNet article for SharePoint 2013. To understand the change, it's important to understand the tight correlation between Managed Metadata Service (MMS), User Profile Service (UPS), Content Databases and how User Profile Replication Engine (UPRE) works. Our goal architecture will be a Hot Standby Farm as discussed here and here. In the Hot Standby environment, we're log shipping the Content and MMS databases with matching timings (Backup, Copy and Restore Agent Jobs all happen at the same time)

Obligatory warning…here comes the deep dive!

There are several fields in users' profile that leverage the MMS service. Out of box, these include the Past Projects, Skills and Schools fields:

These are free text fields and are used for a better search and social experience. Leveraging UPS and MMS, users can identify all users with a particular skill set, or past alumni. You can see all the terms that have been created by going to the Term Store Management:

In the database, we can see the terms association across the MMS and UPS Profile databases:

Note: Do not run these on your production environment; they are only here to show the closely related the IDs and Terms across the databases. Even with NOLOCK, it can affect the farm and put you in an unsupported state. I crashed my MMS Service while getting these screenshots because of these queries

We can see the same tight association when we use an MMS column in a list in the content databases:

And in the database:

The Managed Metadata Service application uses the GUID and TermID fields to provide all MMS functionality. The column value is added for the end user to view without the need of another join on the list item. Keeping the Managed Metadata Service's Database, the Content Database and the User Profile's Profile Database in sync is required for proper functionality of the environment.

If databases get out of sync, you'll have mixed results when using any MMS driven fields. Some of the symptoms you'll see are: the MMS fields are read/only (it's relying on the column value to populate for the end user instead of using the ID), all MMS driven functionality will cease (workflows, content type logic, etc.). Search will start acting up when crawling users, social tagging/notes will fail intermittently and other issues will manifest.

The real problem is that you won't know this is happening until you try and use the secondary farm! Think about that for a moment: your primary farm is offline, the secondary farm is brought online and large components of the farms are inoperable. THAT will not be a fun day.

<< Obligatory warning over >>

The UPRE Gap: (Spoiler – UPRE is unreliable if the MMS Database is Read/Only)

The key to a viable standby farm requires that the Profile Database, the Managed Metadata Database and the Content database are in sync. Until recently, the only way to accomplish a standby farm was to log ship the Content and MMS databases, but use UPRE for the Profiles. This kept the Content and MMS databases in sync, but Profiles (UPS) was in a constant state of flux. Consider these scenarios:

Scenario: MMS log is applied during UPRE run
1. Outcome: all updates for that run would fail as UPRE would not be able to access the MMS Database.
Scenario: a user adds a new MMS term to their profile and UPRE ran before the MMS log ship
1. UPRE will try to add the MMS term at the destination farm and fail due to the database being in Read-Only (because of log shipping)

Traditionally, the answer has included some kind of timing solution and ignoring the UPRE errors that occur in the second scenario. But this isn't a true hot standby; a large portion of your farm (profiles) are in a state of flux, you can't be sure of their current state. If the hot standby has an RTO/RPO of less than an hour, but you've implemented a timing solution (for example, UPRE run every 24 hours and MMS log updates are paused) then you've already missed your RTO/RPO SLAs by your own design!

How to choose when to UPRE and when to Log Ship:

The decision to UPRE or to Log Ship the Profile and Social databases is influenced by several factors: Volume of Social Data, RPO/RTO, Disaster Recovery you're leveraging (Hot/Warm/Cold). To guide the decision, we have to ask several questions:

How are we using the secondary farm?
1. Is it a Hot Standby? Or another active farm that needs Profile information?
(Assuming it's a Hot Standby) What is the RPO and RTO times for the secondary farm?
(Assuming it's a Hot Standby) Is there a difference in timing between UPRE and Log Shipping?
1. Can both UPRE and Log Shipping complete within the RPO and RTO window?
How critical are User Profiles? Are they deemed critical enough to have the same uptime and availability as the content databases?
What is the state of the secondary farm's MMS Database (read-write or read-only)?

Answering these questions will quickly favor UPRE or Log Shipping. Consider these two scenarios:

Scenario A:

Consider you have 2 regional farms deployed, one in London and one in Seattle. Both farms are serving content to users, but Profiles from Seattle Farm are needed at the London Farm. The answers to our questions are as follows:

How are we using the secondary farm? Another active Farm that needs Profile information
(Assuming it's a Hot Standby) What is the RPO and RTO times for the secondary farm? N/A – it's not a Hot Standby
(Assuming it's a Hot Standby) Is there a difference in timing between UPRE and Log Shipping? N/A – it's not a Hot Standby
1. Can both UPRE and Log Shipping complete within the RPO and RTO window? N/A – it's not a Hot Standby
How critical are User Profiles? Are they deemed critical enough to have the same uptime and availability as the content databases? No
What is the state of the secondary farm's MMS Database (read-write or read-only)? Read-Write

Since the London Farm's MMS database is Read/Write and is an active farm requiring profile information from Seattle Farm, UPRE would be our choice. UPRE would be able to add terms to the MMS Database and would not be susceptible to disconnection of a log restore of the MMS database.

Note: this is NOT a DR/Hot-Standby solution. This is merely two farms that hold separate information, but the London Farm needs the Seattle Farm's profiles for a business need.

Scenario B:

Consider the same 2 regional farms deployment, a farm in London and a farm in Seattle. All users access the Seattle Farm; the London farm is used if the Seattle farm is unavailable due to maintenance or downtime. The answers to our questions are as follows:

How are we using the secondary farm? Hot-Standby
(Assuming it's a Hot Standby) What is the RPO and RTO times for the secondary farm? 45 minutes and 30 minutes
(Assuming it's a Hot Standby) Is there a difference in timing between UPRE and Log Shipping? No difference in timing between UPRE and Log Shipping
1. Can both UPRE and Log Shipping complete within the RPO and RTO window? Yes
How critical are User Profiles? Are they deemed critical enough to have the same uptime and availability as the content databases? Yes
What is the state of the secondary farm's MMS Database (read-write or read-only)? Read-Only

Since the London Farm's MMS database is Read/Only and is Hot-Standby of the Seattle Farm, Log Shipping the Profile and Social databases would be our choice. Log Shipping allows us to have a consistent point-in-time on the secondary farm without being affected by the database disconnects that would plague UPRE in this same scenario.

Note: this is a DR/Hot-Standby solution. Seattle Farm is log shipping all the content, MMS and UPS databases to the London farm.

Note: it's critical that your databases (Content, MMS, Profile and Social) are all in sync in this scenario. Make sure the timing on your SQL Agent Jobs all match!

Is there ever a time to use UPRE instead of Log Shipping in a Hot Standby?

Absolutely! Here are a few scenarios where UPRE could be chosen instead of Log Shipping:

UPRE gives us the flexibility to pick and choose specific profile fields to synchronize and Log Shipping is all or nothing. If there was a business or security need to reduce the properties to a smaller subset, then UPRE would be a better decision.
If we are space constrained on the secondary farm, UPRE would be a better decision; log shipping requires more space on the secondary farm to hold the backup of the transaction log.
If the 3 SQL Agent Jobs associated with Log Shipping exceed the RTO/RPO SLAs, UPRE would be the better decision
- This can happen if you are heavily using SharePoint's social features.

Summary:

With the option to Log Ship the Profile and Social databases, we have greater flexibility to meet the business' needs to provide a reliable and consistent user experience. Choosing when to Log Ship and when to UPRE, as well as the pros and cons of each, is critical to ensure your architecture is working as expected.

Find all list items with multiple values used

2012-07-26T17:28:00Z

I was working with Russ today and an interesting question came up: If a user had a list with a multivalue column, how do we find all the list items with more than 1 value selected?

After much trial and error, LINQ was the simplest to implement. Here's the list we used:

Testcol is a multi value column and thus our expected result was to return the Recipe #2 and Recipe #4 items. Returning #3 or #1 was a fail.

On the backend, SharePoint splits it's values with a #; combination. We can see this using the u2u CAML builder:

So the resultant code was a LINQ query with a split on the ;# values:

using (SPSite oSite = new SPSite("http://VM-SP2010/"))

{

using (SPWeb oWeb = oSite.RootWeb)

{
SPList oList = oWeb.Lists["TestList"];

List<SPListItem> items = (from l in oList.Items.OfType<SPListItem>() where l["Testcol"].ToString().Split(new[] { ";#" }, StringSplitOptions.None).Length > 3 select l).ToList<SPListItem>();

foreach (SPListItem item in items)

{

Console.WriteLine(item.DisplayName);

}

Console.WriteLine("All done!");

Console.ReadLine();

And the final result:

Hyper-V Slow Host Network - How to Fix

2012-06-11T17:50:00Z

Last week I installed the latest version of Windows 8 RP; I was really excited to see Hyper-V and a desktop OS together. It's been running great, but I noticed a peculiar issue: whenever my VM-DC virtual machine (which hosts AD, DNS and DHCP) was running, my host DNS requests would creep and every request added 5 seconds. I suspected my VM-DC machine was getting the request first and rejecting it (VM-DC has no internet access), but I couldn't figure out why.

To validate, I started WireShark and made an nslookup DNS request. Sure enough, VM-DC got the request first, returned back a bad hostname and then my normal DNS server got the request. From there, I went to make sure my DNS entry didn't get into my External NIC configuration:

Ethernet adapter vEthernet (External LAN):

   Connection-specific DNS Suffix . :
   Description . . . . . . . . . . . : Hyper-V Virtual Ethernet Adapter #2
   Physical Address. . . . . . . . . : D0-67-E5-38-18-8C
   DHCP Enabled. . . . . . . . . . . : Yes
   Autoconfiguration Enabled . . . . : Yes
   Link-local IPv6 Address . . . . . : fe80::c0c1:ae76:bdbe:5c9e%19(Preferred)
   IPv4 Address. . . . . . . . . . . : 192.168.10.1(Preferred)
   Subnet Mask . . . . . . . . . . . : 255.255.255.0
   Lease Obtained. . . . . . . . . . : Monday, June 11, 2012 12:09:00 PM
   Lease Expires . . . . . . . . . . : Tuesday, June 19, 2012 12:08:59 PM
   Default Gateway . . . . . . . . . : 192.168.10.254
   DHCP Server . . . . . . . . . . . : 192.168.10.254
   DHCPv6 IAID . . . . . . . . . . . : 399534053
   DHCPv6 Client DUID. . . . . . . . : 00-01-00-01-17-5A-B2-7C-D0-67-E5-38-18-8C
   DNS Servers . . . . . . . . . . . : 192.168.10.254
   NetBIOS over Tcpip. . . . . . . . : Enabled

Ethernet adapter vEthernet (External WiFi):

   Media State . . . . . . . . . . . : Media disconnected
   Connection-specific DNS Suffix . : gateway.2wire.net
   Description . . . . . . . . . . . : Hyper-V Virtual Ethernet Adapter #3
   Physical Address. . . . . . . . . : 00-24-D7-C8-20-34
   DHCP Enabled. . . . . . . . . . . : Yes
   Autoconfiguration Enabled . . . . : Yes

Ethernet adapter vEthernet (Internal LAN):

   Connection-specific DNS Suffix . : VM.local
   Description . . . . . . . . . . . : Hyper-V Virtual Ethernet Adapter #4
   Physical Address. . . . . . . . . : 00-15-5D-01-4C-00
   DHCP Enabled. . . . . . . . . . . : Yes
   Autoconfiguration Enabled . . . . : Yes
   Link-local IPv6 Address . . . . . : fe80::2c1d:8047:d6a:f39b%21(Preferred)
   IPv4 Address. . . . . . . . . . . : 192.168.250.101(Preferred)
   Subnet Mask . . . . . . . . . . . : 255.255.255.0
   Lease Obtained. . . . . . . . . . : Monday, June 11, 2012 1:19:24 PM
   Lease Expires . . . . . . . . . . : Tuesday, June 19, 2012 1:19:23 PM
   Default Gateway . . . . . . . . . :
   DHCP Server . . . . . . . . . . . : 192.168.250.1
   DHCPv6 IAID . . . . . . . . . . . : 805311837
   DHCPv6 Client DUID. . . . . . . . : 00-01-00-01-17-5A-B2-7C-D0-67-E5-38-18-8C
   DNS Servers . . . . . . . . . . . : 192.168.250.1
   NetBIOS over Tcpip. . . . . . . . : Enabled

As you can see, VM-DC (192.168.250.1) is definitely not on my External LAN NIC. So why is it still getting the initial DNS request? Next I went into the Network Adapters, hit ALT, looked at the binding settings and EUREKA: my Internal LAN NIC was listed before my External LAN NIC. A quick reorder to give my External LAN priority and solved the DNS latency problem. Now my host browses the internet as fast with and without my VM-DC booted.

BEFORE:

AFTER:

I'm not sure what set my Internal NIC with higher priority than my External NIC, but if it happens to you, this'll help!

Database Attach breaks Alerts and the fix!

2012-01-03T20:17:26Z

I got a query from a colleague about Alerts and went to test it. To my surprise, my test Alerts weren't working and after some troubleshooting I discovered it was due to my upgrade.

The fix is pretty quick - just download the txt file, rename to PS1 and call it with the WebApp url. It does little move than change the title of all alerts and changes it back. This triggers the alert to be rewritten and handled correctly in 2010.

For my example, my 2007 DB was attached and upgraded to http://VM-SP2010A, so my call was:

.\AlertFix.ps1 http://VM-SP2010A

HTH

Post June 2011 CU Move-SPProfileManagedMetadataProperty BUG - and the workaround

2011-11-22T16:38:00Z

If you're upgrading from SharePoint 2010 and following the post upgrade steps from Perform post-upgrade steps for a database attach upgrade (SharePoint Server 2010), you'll need to upgrade your multi string values from your old 2007 SSP to 2010 MMS.

Let's have a little background on why we have this. Here's a screenshot of the old values in the Responsibilities Field:

In previous 2007, I'd have to free text search for "SharePoint 2007" and again for "Migration" if I wanted to find this user. If I wanted to search for both, our results could get skewed as users could have those terms in different order. Is it possible? Yes. Is it easy? No. New with 2010 is the Managed Metadata service which eliminates this problem. With the individual fields set to metadata, we enable the search refiners in SharePoint 2010 and the order is no longer an issue. Is it simpler? Absolutely.

While upgrading, there is a Powershell script to migrate the flat text field of the SSP 2007 to MMS in your new SharePoint 2010 environment called Move-SPProfileManagedMetadataProperty. Unfortunately, if you are post June CU, there's a bug where you'll get this error message:

Move-SPProfileManagedMetadataProperty -ProfileServiceApplicationProxy <GUID> -Identity <Name of Field> -AvailableForTagging -TermSetName Responsibility
Move-SPProfileManagedMetadataProperty : UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
At line:1 char:38
+ Move-SPProfileManagedMetadataProperty <<<< -ProfileServiceApplicationProxy 9
1740c45-b8c2-4ade-85bb-344f2a4eca47 -Identity SPS-Responsibility -AvailableForT
agging -TermSetName Responsibility
    + CategoryInfo          : InvalidData: (Microsoft.Offic...ProfileProperty:
   SPCmdletMigrate...ProfileProperty) [Move-SPProfileManagedMetadataProperty]
, SqlException
    + FullyQualifiedErrorId : Microsoft.Office.Server.UserProfiles.PowerShell.
   SPCmdletMigrateDataForOneTaxonomicProfileProperty

If you BING search, you'll find that the Quoted Identifier is used to isolate quotes (") and apostrophes (') and this flag helps protect against SQL Injection attacks in SharePoint. You'll also find (as stated here http://social.technet.microsoft.com/Forums/en-US/sharepoint2010setup/thread/6fdb0ca3-5ac5-45ab-8663-af536e6dae24) that modifying the database works. THIS PUTS YOU IN AN UNSUPPORTED STATE AND SHOULD NOT BE DONE WITHOUT MICROSOFT SUPPORT APPROVING THE CHANGE.

"Ok Ryan, so what's the fix?" In short: we get around the Move-SPProfileManagedMetadataProperty bug by not using it! Enter the User Profile Replication Engine (from the SharePoint Administration Toolkit: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=20022). The User Profile Replication Engine (or UPRE) has a -DoUpgrade switch which migrates the 2007 strings to 2010 MMS. To make this work, you must have the old 2007 farm and new 2010 farm both online. My old 2007 MySite Host is http://vm-moss:10378/ and my new 2010 MySite Host is http://vm-sp2010:13965/

Here's the walkthrough (Note: my SharePoint 2010 environment is called VM-SP2010. My SharePoint 2007 environment is called VM-MOSS):

1) Install the SharePoint Administration Toolkit

2) Create an Managed Metadata Service (MMS) and User Profile Service (UPS) in the same proxy group (the same group is how the UPS knows which MMS to utilize)

3) Assign the proxy group to your 2010 MySite Host Web Application. You can see here that my Web App is "SharePoint 2007MySites - 13965" and I have the MMS and UPS assisgned

4) Make yourself an administrator and full control to the MMS and UPS at _admin/ServiceApplications.aspx

5) Start the UPRE and type in this Powershell command:

get-spprofilepropertycollection -source <2007 MySite Host URL> | start-spprofileservicefullreplication -source <2007 MySite Host URL> -destination <2010 MySite Host URL> -MaxNumberOfThreads 3 -DoUpgrade

So, for my environment, I used:

get-spprofilepropertycollection -source http://vm-moss:10378/ | start-spprofileservicefullreplication -source http://vm-moss:10378/ -destination http://vm-sp2010:13965/ -MaxNumberOfThreads 3 -DoUpgrade

And hit enter. Wait for it to start enumerating profiles to insure you don't have any errors. A full replication takes quite a while (1000 users on my environment took about 5 minutes).

6) Once complete, verify the multi-value property has been updated correctly by viewing a profile. In this case, we'll use Administrator.

It works!

And there you have it. If you have custom properties in 2007, you'll need to have the same custom properties in 2010. The easiest way to do this is backup and restore your SSP database to 2010, then use it as the name of your Profile DB when you create your UPS. That'll upgrade the DB to 2010 and bring your properties over. From there,run a full import with UPRE following the steps above.

Problems with the UPRE need to be troubleshooted and then a recovery run. Check out http://technet.microsoft.com/en-us/library/cc663011.aspx for steps to do that.

Hope this helps someone out there. Any problems, let me know!

Changing the Application Pool on a Web Application the right way

2011-08-31T14:17:00Z

I was working a farm today that had 10 web applications, all with their own application pool. As we know from http://technet.microsoft.com/en-us/library/cc262787.aspx#WebserverAppServer , the recommended limit is 10 application pools per WFEs and with the addition of the Web Services App pool, the CA app pool and others, we were over our limit. Our performance was suffering and we needed a way to consolidate the web application app pools.

Before SharePoint, we'd just open IIS Manager and update the application pool. But now that's not the correct way: SharePoint doesn't know anything about the change. Imagine in 2 years from now when you add another WFE - SharePoint would think that we have 10 application pools and would provision them all on the new WFE correctly. Then you'd have to go in and change them. What a maintenance nightmare this would be!

To do it correctly, we'll leverage this PowerShell snippet:

$sourceWebAppPool = (Get-SPWebApplication < URL a webapp who’s application pool you want to use>).ApplicationPool

$webApp = Get-SPWebApplication < URL of the web application you want to change>

$webApp.ApplicationPool = $sourceWebAppPool

$webApp.ProvisionGlobally()

$webApp.Update()

iisreset

Here's an example:

In my environment, I have 2 web applications: //VM-SP2010:26927 and //VM-SP2010:4150. I want :26972 to use the same application pool as :4150. So my PS is:

$sourceWebAppPool = (Get-SPWebApplication http://VM-SP2010:4150).ApplicationPool

$webApp = Get-SPWebApplication http://VM-SP2010:26927

$webApp.ApplicationPool = $sourceWebAppPool

$webApp.ProvisionGlobally()

$webApp.Update()

IISReset

Before:

And After:

Now the change is saved in the config DB and any new WFEs we add will have the correct settings.

HTH

Virtualization, the SAN and why one big RAID 5 array is wrong.

2011-08-23T16:04:00Z

Today I've had no less than 3 ad hoc conversations about disk sharing for VMs. This isn't about RAID5 versus RAID10, specific performance requirements for SharePoint, or cache for simultaneous read/write requests. This is simply aimed at giving SharePoint admins the knowledge to take to the SAN admins to insure a successful SharePoint deployment via best practices. Our focus is on SQL server in this scenario, but the concepts are applicable. Ready...let's dive in.

First of all, some definitions:

Term	Definition
SAN	Acronym for Storage Area Network. It’s a Physical device used to extend storage space. The SAN is the parent device for all disk IO
Enclosure	Each SAN is made up of enclosures. It’s a physical part of the SAN and is the device holding all the hard drives. Normally, a set of fiber channels, iSCSI channels, HBAs and other miscellaneous hardware is attached to each enclosure.
Hard drive	Regular hard drive you’re familiar with. You know - that spinny thing (or if you're really good, not spinny thing)
RAID Array	Normally RAID 5 when we discuss SANs, a RAID Array is a grouping of Hard drive (all within the same enclosure) to provide fault tolerance. In the event a hard drive goes down, no loss of data is experienced. In a RAID 5, your disk size is measured by: Size of Drives * (Number of Drives - 1). Minimum number of disks for RAID5 is 3 – 2 for the data and one for the parity.
LUN	Acronym for Logical Unit Number. A LUN is logical section of a RAID array and is the actual drive letter that is exposed to Windows.
IOPS	Acronym for Input/Output Operations per Second. IOPS is a measurement of the performance of a disk (or Array). To calculate IOPS, we can use http://blogs.technet.com/b/cotw/archive/2009/03/18/analyzing-storage-performance.aspx, or a much easier method: SQLIO.
SQLIO	A disk benchmarking utility which gives results in IOPS for different loads. http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=20163
Bandwidth	The theoretical maximum of a given resource without any additional load. Imagine bandwidth as a 4 lane highway without traffic with a 70mph speed limit. As law abiding citizens, we can drive up to 70mph all the time. To go 10 miles will take 8.5 minutes every time because we’re always traveling 70 mph.
Throughput	The actual maximum of a given resource with additional load factored in. Imagine throughput as a 4 lane high WITH traffic with a 70mph speed limit. But because of congestion the actual speed we can travel varies from 45mph to 70mph. It’s never the exact same.

Now every SAN administrator has some brochure, or PDF, or something from there SAN vendor that says: For peak performance, create one RAID array of all the hard drives in the enclosure. I'm sure there's some balloon that says "To minimize waste", or "To load balance across multiple hard drives is a good thing!" But this is wrong. But before you go and blow away all your LUNs and RAID arrays, let's examine a scenario:

Let’s assume that we have a SAN, with 4 enclosures. Each enclosure is capable of holding 10 hard drives, and we decide to fill it with 100GB drives each rated at 100 IOPs each. Total possible space is 1TB and our bandwidth is 1,000 IOPS per Enclosure. To maximize our investment (thereby minimizing waste), we follow our SAN
vendor’s recommendation and create one big RAID 5 array and lose 1 disk to the parity calculation. So our available space is 900GB and our bandwidth is 1,000 IOPs.

Next, we decide to deploy SharePoint 2010 via HyperV with all disks on SAN. Our server architecture is 3 servers, 1 SQL, 1 APP and 1 WFE. We decide our drive needs are:

Server	Disk Description	Requirements
SQL	OS Drive	100GB and 50 IOPs
	Data Files	100GB and 250 IOPs
	Transaction Logs	100GB and 250 IOPs
WFE	OS Drive	100GB and 50 IOPs
APP	OS Drive	100GB and 50 IOPs

We send this to the SAN admins and the SAN admin says to themselves: “Self, enclosure #1 has a 900GB capacity and 1,000 IOPs. SharePoint 2010 needs 500GB and 650 IOPS.” And would promptly carve up the RAID 5 array of enclosure #1 into 5 different LUNs: 3 for SQL, 1 for WFE and 1 for APP.

Here’s where the problem arises. A hard drive only has one armature and uses it to read and write, but it can’t do both simultaneously. If the hard drive is writing, and we request a read for some file, then the read gets queued until the disk I/O completes. RAID is a double edged sword and the root of our problem: when using any kind of RAID, the data is dispersed amongst all the drives. On one hand, RAID is a huge performance boost because if we have 20 bits to write and 10 disks to use, then each disk only has to write 2 bits. On the other hand, if we have 20 bits to write, and 10 bits to read, the reading bits will have to wait because all the drives are used for the writes. Now granted, this read and write happens EXTREMELY fast, but the pause is still present and when we're talking about operations per second, they add up quickly.

Now, accordingly to SQL best practices, we split our Data file and Transaction logs to different disks to alleviate this queuing. The transaction logs are very write heavy (1000 writes: 1 read or more). The Data file is very ready heavy (1 write: 500 reads or so). As far as SQL in concerned, they’re on separate disks because we put them on separate LUNs. BUT WAIT: remember that all the LUNS are on one RAID array, AND that when you read or write from a RAID array you utilize all the disks. Thus you haven’t actually split your data files and transaction logs – they’re on the same disks. Now stack on top of that the write heavy statistics of your APP server, and the read heavy load of your WFE, and our throughput plummets. While still under our bandwidth of 1,000 IOPS for the enclosure, the SQL LUN can't write while the WFE LUN is reading and vice versa.

So we’ve identified the underlying problem. What’s the solution? Let’s revisit our server needs:

Server	Disk Description	Requirements
SQL	OS Drive	100GB and 50 IOPs
	Data Files	100GB and 250 IOPs
	Transaction Logs	100GB and 250 IOPs
WFE	OS Drive	100GB and 50 IOPs
APP	OS Drive	100GB and 50 IOPs

RAID 5 is a minimum of 3 drives: 2 data drives and a parity drive. SQL best practices is to break up the OS, Data and Transaction logs onto separate drives. How do we follow best practices and leverage the SAN?

One possible solution would be to breakup into 3 RAID5 volumes like so (note our wasted space is now 300GB instead of 100GB):

RAID 5 volume	Disks	Available space and bandwidth
#1	1-4	300GB and 4,000 IOPs
#2	5-7	200GB and 3,000 IOPs
#3	8-10	200GB and 3,000 IOPs
Total		700GB and 10,000 IOPs

SQL	OS Drive	100GB and 50 IOPs	100GB from RAID5 volume #1
	Data Files	100GB and 250 IOPs	200GB from RAID5 volume #2
	Transaction Logs	100GB and 250 IOPs	200GB from RAID5 volume #3
WFE	OS Drive	100GB and 50 IOPs	100GB from RAID5 volume #1
APP	OS Drive	100GB and 50 IOPs	100GB from RAID5 volume #1

In the proposed solution, we are sacrificing 200GB of disks, but we’re gaining the additional performance of splitting our data files and transactions logs, and SQL is capable of leveraging the entire 3000 IOPS for each LUN. The OS disks are still sharing a RAID array (just like in the rejected solution) so we’re not making the problem any worse, but since we moved the data and transaction to dedicated spindles, we gain performance by reducing the overall load.

Lastly, I’ve fudged the numbers a little bit to make my point. I’m not sure if 50 IOPs is too little or too much for an OS drive. I don’t know if your enclosure has 10 100GB drives with 100 IOPS in RAID5 or 25 300GB drives with 10,000 IOPS in RAID10. And the read/write challenges is nothing new hard drive manufactures nor SAN vendors – great strides have been taken to reduce the performance of a read and write request coming in simultaneously; namely cache.

But here’s the facts: if we plan ahead of time for these scenarios and work with the SAN admins, we can increase the SAN’s overall efficiency. By knowing the loads of other servers on the RAID array, we can intelligently place our loads and prevent any cross server thrashing of the disks. Sometimes the answer may be one big
RAID 10 array, or three smaller RAID 5 array. Proper benchmarking is the key and should be the first step in any new server installation to insure we can identify any issues as soon as possible.

HTH

First Blog Post

2011-08-23T15:30:33Z

Well, this is my first blog post. This blog is designed to consolidate my ramblings and thoughts into a concise and available format, trying to give back to the community. For several years I've been working with SharePoint and have relied heavily on the community to get me through some tough situations. As I've progressed through the challenges, I've realized that there are several lessons to be learned Brady Bunch style. I've reached a point where I'm ready for feedback to grow and progress in my SharePoint repoitoire.

So stay tuned - this is going to be fun.