SharePoint – Page 26 – The White Pages

SQL Express, Microsoft Internal Database, and SharePoint – Be Careful Upgrading

Published August 16, 2010

Anyone that has worked with SharePoint for some time will tell you that setting it up using the Basic or default install is a big no no. Doing so uses only local accounts, prevents you from adding any additional servers to the farm, and because the SharePoint system account is local, it effectively prevents SharePoint from seeing any data on any other systems. Reversing this requires a complete uninstall of SharePoint. It is much better to do a proper farm install, even if the databases are to be stored on the same machine.

There is however one business case where the basic install has historically been the right approach, and it’s very specific. To explain, a little explanation of some different flavours of SQL is required. Most people are familiar with SQL Server Standard Edition and Enterprise Edition. Also available for download is a free edition of SQL Server entitled SQL Server Express, which has a few limitations, most notably a single database size limit of 4 GB (and has none of the BI components). There is (or was) also a lesser known version of SQL Server known as the Windows Internal Database. It can’t be downloaded, but occasionally will show up on a system when a Microsoft product that requires SQL Server is installed into an environment without an available SQL Server.

What’s the difference between the Windows Internal Database (WID) and SQL Express? SQL Express is a little easier to manage (and possible to uninstall, unlike WID). WID is relatively invisible, but the major difference between the two is the size restriction. Microsoft will often change the maximum DB size limit for the WID in the interest of the product that it is backing. This is very much true of SharePoint 2007. Given SharePoint’s focus on storing large content,Microsoft didn’t want people to bump into that 4 GB size restriction too quickly,so for WSS 3.0 (the free version of SharePoint 2007), they removed the limit completely.

This means that if you use WSS 3.0, and you install using the basic configuration option, you have no licensing limits on the amount of content that you can store within SharePoint. It is very important to note that this is ONLY true of WSS 3.0, if you install SharePoint Server 2007 in basic mode, once you hit 4 GB everything stops working. Also, if you decide that maybe you should do a best practice farm install, and you install SQL Express separately from SharePoint, you will run into that 4 GB wall with either WSS or SharePoint server.

This then is the one business case where it is appropriate to use the basic installation. If the feature set of WSS 3.0 is adequate, and you will want to store more than 4 GB in a single site collection, basic installation is for you.

Thus far I’ve been addressing WSS 3.0 and SharePoint Server 2007. With SharePoint Foundation 2010, and SharePoint Server 2010, Microsoft made some significant changes to the model. Now, they exclusively rely on SQL Express for the basic installation. While this is great for anyone that’s ever wanted to uninstall an instance of the Windows Internal Database, Express has that 4 GB limit for both editions, which essentially means that they’ve closed the door on the free storage ride.

This also means that if you are in this category of organizations using more than 4 GB of SharePoint storage, you’re going to need to spring for a SQL Server license if you want to upgrade to SharePoint Foundation 2010. You will also need to do an uninstall, and a reinstall to get yourself into a farm configuration. It’s OK, it’s not as scary as it sounds.

The SharePoint 2010 changes mean that there is now no good reason to use basic installation in a production environment. Development or testing, OK, but why not use farm in that scenario as well? It more closely approximates the real world.

UPDATE – 24/08/201

I was listening to Todd Klindt’s Admin Netcast #53 today, and he was addressing some of the above issues. He brought up two very good related points. Firstly, in SQL Express 2008 R2, the storage limit has been increased to 10 GB, so that might help you out. Also, you can use the SQL FileStream Remote Blob storage to help get the size of your content database down.

3 Comments

License State Error Joining a SharePoint 2010 Server to an Existing Farm

Published August 7, 2010

I ran into an interesting error this week at a client site. They had been having server trouble, and that involved setting up a new instance of the operating system, and obviously all of the SharePoint bits needed to be reinstalled. Once that was done, I ran the Products and Technologies configuration wizard, set it to join an existing farm, entered the farm credentials and everything was good until Step 3 – where it connects to the configuration database. At that point it choked with:

“The license state for the current server doesn’t match the farm’s license state.”

My first impulse was that I had used the wrong key when installing the bits. Trying to join an Enterprise farm with a Standard server, or vice versa could cause this, but I was able to confirm that I had used the right key.

After a fair bit of head scratching, I realized that I had committed the mistake that I always commit with 2010, and I had forgotten to install the Office Web Applications. I had indeed installed them on the farm, and the rebuilt server did not yet have it installed. After installing them, I was happy to see that the server joined the farm successfully.

What Domain Accounts to Use For SharePoint 2010

Published August 6, 2010

When setting up a SharePoint farm, whether 2007 or 2010, you have the option of providing various service identities throughout the process. Indeed, every application can run with its own identity. Far too often, administrators pick a single account, and use it for everything. While this is certainly the easiest approach, it is far from the most secure, and it can be very limiting down the road if you need to get granular with your permissions. The trouble is that there are a lot of intricacies as to what account does what, and getting it right requires a pretty comprehensive understanding of the product.

We now have enough work under our belt with SharePoint 2010 that I feel comfortable sharing some of our best practices around account creation for SharePoint 2010. The product itself has gotten more complex, and so therefore have the configuration options. There is no “one size fits all” approach for all scenarios, but the list that I am providing below should work as a good starting point. There is often a trade-off between the ease of manageability and providing good security, and the approach below,I feel,find a good balance.

The chart below describes the account, its purpose, what rights it needs to the local machines in the farm (including the SQL server machine(s), the rights it needs for SQL Server directly, and the rights it needs to the Active Directory domain.

Base Set of SharePoint 2010 Service Accounts

Account	Purpose	Local Rights	SQL Rights	Domain Rights
spSetup	Used to login to the farm servers Used to install bits on the farm servers	Administrator Remote Desktop Login	DB Creator Security Admin	Member
spFarm	Identity for all Windows Services Identity for all SQL Services (optional) Identity for Profile Synchronization Service Identity for all code running with elevated permissions (web parts)	None ⁽¹⁾⁽³⁾	DB Creator Security Admin	Member
spApps	Identity for all SP Application App Pools ⁽⁴⁾	None	None	Member
spServices	Identity for all SP Service Applications⁽⁴⁾	None	None	Member
spUPS	Identity for the User Profile Service	None	None	Member Replicating Directory Changes⁽²⁾
spCrawl	Used by the Indexer to crawl content	None	None	Member⁽⁵⁾
spBI	Trusted account for Reporting Services and PerformancePoint when not using Kerberos	None	DB Access as appropriate	Member
spSuperUser	Used for Object Caching	None	None	Member
spSuperReader	Used for Object Caching	None	None	Member

(1) Needs to be a part of the Local Administrators group while the User profile service is being created. See my previous post for more details. Once created, this account can be removed.

(2) AD Permission required by the User Profile service

(3) Required for a specific AD container when using the incoming email service. See this post for details on how.

(4) There may be a large number of these, one per entity

(5) Appropriate rights will need to be granted to this account for any EXTERNAL content being crawled (file system, shared folder, Lotus Notes, etc)

Hopefully this will help a few of you get started with a little less head scratching.

Who Are The SharePoint Sample People?

Published July 8, 2010

Since 2006, I’ve been looking at their pictures. I see them on an almost daily basis in all sorts of settings. If you’ve ever installed SharePoint 2007, you’ve seen them too. They’ve become familiar companions, almost dear friends. I fully expected that with the release of SharePoint 2010, they would gradually fade away, and we would lose touch, only to meet years later on Facebook, but no, they came right along and they’re back with 2010! I am of course referring to the smiling faces you see when you spin up a new Collaboration Portal site collection, those that I like to call the SharePoint People!

The trouble is, I don’t know their names! Who are these people that I see so frequently? Where do they live? Do you know who they are? I’ve decided that I’m keen to know! I’d love to put a name to a face.

Yes, I need to get out more.

1 Comment

Using a Fixed Width Page in SharePoint 2010

Published July 5, 2010

For the most part, I prefer dynamic width pages on an intranet. It allows users to take advantage of the screens that they have, and to work the way that they want. They do have one big drawback though in that they don’t allow consistent control of presentation. You never know for sure where a particular screen artefact will be. This can be a real problem, particularly when it is necessary to maintain a consistent brand. The solution to this is to use a fixed width page, which basically keeps the width of the content constant, no matter what the width of the browser.

If you previously worked with SharePoint 2007, you’ll know that this was the default behaviour of the standard Publishing master pages (like BlueBand.master). Team sites, and most other sites used a variable width template. SharePoint 2010 now uses variable width for both types of templates, and I’m not sure that’s a good thing in a web content management scenario.

I recently had to set up my first fixed length centered page in 2010, and it was harder than I thought it would be. I isolated the class controlling the width of the content (#s4-workspace), explicitly set the width, and refreshed the page. it loaded correctly,but upon load,it immediately snapped back over to the left and filled the page. Obviously, there’s some javascript trickery going on here.

Luckily, thanks to Tom Wilson, the correct solution was quickly obtained. Basically, javascript is looking for that s4-workspace id, and doing what it thinks is best with it. Since we beg to differ, the solution is to first remove that id tag altogether. Then, you need to add in the appropriate styling to compensate for the lack of javascript positioning.

.SFC{
overflow:auto;
background: transparent url(''/SiteCollectionImages/Page_BG.jpg'') no-repeat fixed left top;

}
.SFC form
{
width:958px;
margin-left:auto;
margin-right:auto
}

The background image isn’t necessary, but it can be used to fill the non content region of the browser.

6 Comments