Tag: SharePoint 2007

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

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

spSecurityTrimmedControl – An Indispensible Tool for your public facing SharePoint web site (and others)

Published May 29, 2010

At one point or another, if you design or modify SharePoint sites, particularly public facing web sites, you’ll have a need to show some design elements to some people, and not to others. SharePoint itself does a very good job of security trimming most elements based on your security level, but there are some cases where it just isn’t designed to do what you want it to do.

Take a public facing SharePoint site for example. Designers need to be able to work with pages, and have access to all of the tools, the ribbon, etc. You of course don’t want public users to see any of these things.

If you log in as an anonymous user, SharePoint knows that you’re not an editor, so it trims out all of the editing controls.

The trouble is, not all of the controls that I want to hide from the anonymous user are trimmed. In this case,the navigation breadcrumb on the left,and the login control on the right. In fact, in may cases, the entire blue bar at the top will need to be hidden from the anonymous user.

As an aside, the sign in control is interesting. This is the same control that you see in the first image that gives the logged in user access to their profile, my site, etc. It turns into a login control for anonymous users, which is great when you have both public and secure areas of your site. The trouble is, that control shows up whether or not it’s even possible to log in. As part of locking down a public facing SharePoint site, I always extend the application into an internet zone, turn on anonymous access, and disable both basic and Integrated authentication.

In this case, clicking on the login control simply results in an error. It would be nice if SharePoint could detect that authentication wasn’t even possible, and hide the control completely. Of course I digress, but this brings us back to the main point – how do we hide the offending elements from those with low or no privileges? It turns out that it’s actually pretty simple – we use the spSecurityTrimmedControl.

This control is simply a container that will either show or hide it’s contents based on a users security level. Simply edit the master page that the site uses (or better yet, create a new one based on your current one and then tell the site to use it). Below is an example of using the control to hide a link to the current site page when the user is not an editor.

The important attribute of the control is the permissions attribute. It basically acts as a switch, so if you have at least the permission listed, you will see the control. A complete list of the allowable values can be found here on MSDN.

Exercise caution however when hiding the ribbon. Don’t hide the ribbon’s container, because it needs to be seen in order to calculate page positioning, instead, hide only the contents of the container. You can hide ContentPlaceHolders quite successfully though, because the server can still see them.

Use of this control is by no means limited to public facing web sites, but it is particularly handy for them. In fact, when requested, I use this control to hide the “View All Site Content” and “Recycle Bin” links in team sites.

3 Comments

Deploying Reporting Services Reports to SharePoint using Business Intelligence Development (Visual) Studio

Published May 19, 2010

If you are using BIDS to develop reports for Reporting Services in SharePoint Integrated mode, you may find some of the deployment options somewhat confusing. Paths in Native mode must be relative, white in integrated mode, they must be absolute. To get to the deployment options, you right click on the project from the Solution Explorer window, and select Properties.

The highlighted areas are the ones that we need to be concerned with. The TargetServerURL property is the most important of the bunch, as you are essentially telling Visual Studio where to find the Reporting Services Web Service. The value that you select here should be the root of the site collection where the report is contained. Basically, because the SharePoint front end is now the report server this makes sense, and the property makes sense in native mode, but for integrated mode this property should be called TargetSiteCollectionURL.

The xxxFolder parameters all behave the same way, and they should contain the complete path to the container for each one (which easily could be the same value). The path should include everything including http, the site collection,the path to the site,the library, and if used, the SharePoint folder. In integrated mode, if you replace folder with Library, or even better, path, this will make more sense.

5 Comments

Installation Considerations for Reporting Services in SharePoint Integrated Mode

Published May 6, 2010

Ever Since SQL Server 2005 R2, it has been possible (with varying degrees of difficulty) to tightly integrate Reporting Services with SharePoint. What this means is effectively discarding the management and user interfaces that come with a Reporting Services native mode installation, and managing and use all reporting through the SharePoint interface.

This has really nice benefits for those managing the Reporting Services environment, mainly because you can simply leverage your SharePoint storage and security infrastructure instead of having to create and maintain another one simply for reports. Users benefit from a consistent user experience, and are easily able to create filtered reports through the Report Viewer web part.

Integrated mode is however not without its complications, and with the release this week of SQL Server 2008 R2 (which includes some very nice Reporting enhancements), I thought that I’d go over them. Here are some of the more important moving parts:

Reporting Services Add-In must be installed on EACH front end web server in the farm
Reporting Services database must be created in SharePoint integrated mode
Anonymous access must be disabled for the target application
The machine hosting Reporting services must be a member of the SharePoint farm

If everything that you’re running is on a single server, you really don’t need to read any further. In that scenario, everything is straightforward. But if you’re not on a demo system, or you have more than 3 users, chances are that your farm is distributed, and least broken out into one Web Front End (WFE) and one SQL Server.

As I’ve mentioned previously,The Reporting Services add in now installs as a prerequisite with SharePoint 2010,which makes the first point a no brainer. However, if you have SharePoint 2007 and multiple web front ends (your query servers count), you need to install the add in. Also if you add a new Front End server down the road, don’t forget the add in.

If you’re currently have a Reporting Services server running in native mode, and you’re looking to upgrade to SharePoint Integrated mode, don’t expect an enjoyable experience. You can’t upgrade your existing native mode database to integrated mode, you’ll need to export everything, create a new database in integrated mode, and move everything into it. You can however switch back and forth on the server side if you need to, but as you do, it’s all or nothing.

One thing that I wanted to do was to take an election results analysis demo that I’ve recently done with SQL Server 2008 R2 mapping and expose it to the world. Unfortunately, that’s not going to be an option because you can’t run the application in anonymous mode with the plug in. If you have this requirement, then Integrated Mode is not for you.

Finally, the big requirement is that the machine running Reporting Services must be a member of the SharePoint farm. Typically you only install the SharePoint bits on the WFEs and the Index servers, and the SQL Server itself is left alone. Basic guidance from Microsoft indicates that now you need to install the SharePoint bits on the SQL server and then join the farm. This may be daunting to those that have yet to “enjoy” the challenges of running a multiple WFE farm. It also might be a particular challenge if you don’t “own” the SQL server itself.

My preference (typically, not always) is to take the other approach, and install Reporting Sevices itself (none of the other services, database engine, etc) on one of the SharePoint front end servers. In our case, we have a 3 server farm (one front end, one indexer/query/central admin, and one SQL, and Reporting Services is on the Indexer/query/central admin box (I need a better name for it).

In this scenario, the Reporting Services databases themselves still live on the main SQL server, but the services and rendering all happen from the WFE machine. This makes for a very low touch solution in terms of your SQL server, and is much easier to maintain. This model also also allows you to get going with the new features available in SSRS R2 without having to first upgrade your central SQL server.

One caveat – with 2007, I always found that it was a requirement to also run the Central Administration application on the machine with Reporting Services. I don’t know if that was a true limitation, or something that I was missing. I haven’t yet tried it using R2 and 2010, but when I do, I’ll come back here and update this. Also – if you know something I don’t, please feel free to enlighten me!

5 Comments