Monthly Archives: July 2005

SQL Server Report Pack for SPS

Pretty good reports out of the box. I have a Virtual PC environment, so it was very easy to download this app and install.

FYI – you dont need an SPS installation for the sample reports.

What you need is
1. SQL Server
2. SQL Server Reporting

Its best if you have a standalone SQL server (wihout any SPS installed on top of it as the SPS virtual server will compete with the Reports Server IIS, there are ways around, but for simplicity sake).

Whats great is there is a sample database included in the install. Just install the demo database, upload the reports onto your SQL server, configure some options and the sample reports are available for you. For more details about the download check this Link .


Set a quota limit for an individual site

The quota feature is disabled by default in Windows SharePoint Services —there are no default quota values or templates. You can implement a virtual server level quota, with multiple quota templates and you can also edit an individual site quota.

How do I get about editing individual site quotas. Heres how.

SharePoint Portal Server Central Administration –> SharePoint Portal Server

Under Portal Site and Virtual Server Configuration
Configure site quotas and locks from the Virtual Server List page –> Manage site collection quotas and locks

In the “URL of top-level Web site: *” enter the site url, for eg http://servername/sites/sitename
This point is ridiculous, but don’t include the trailing slash “/”, leave it just sitename, else there is an error. Furthermore if your portal dns is SERVERNAME.LOCAL ; try just using SERVERNAME in the url above, may save you some trouble for error screens.

Under the “Site Quota Information”

Let the Current quota template be “Individual”

Check the boxes for Limit site storage & Send warning email and update the values as you like. Let users react to the warning email and to give them some time I set the warning email to 90% of site storage.

This page will also display in the bottom “current storage used”, so you can estimate how much they want more. Dont just add 100%, make a reasonable guess. If they are using 200MB, dont just increase it to 400MB. Ask questions, have a process, etc.

Hit OK.

And you just made another user a very happy person.

Here is some more information on Configuring Site Collection Quotas and Locks from the Microsoft Site.

SPS – HP Tool for Capacity Planning

Are caught by your “Sales Guys” for SPS architecture & configuration to be explained in a better way.

In addition to an excellent article which provides guidelines for capacity planning there is this really nice SharePoint Portal Server 2003 Sizing and Configuration Tool V2.0 released by HP which goes a step furthur and says what HP systems you would need (this tool configures BladeSystem or Proliant DL Series) and the exact configuration.

Download the tool and see how you can play with some options. In short the tool is a .Net application which takes into account the guidelines suggested by Microsoft and translates them into a report (which you can print) that you can pass off to the hardware staff.

MSDE or WMSDE – The Difference

There has been some debate between MSDE and WMSDE and lets hope this post will clarify these two products and state some differences.

When you install Microsoft Windows SharePoint Services on a single server using the defaults, you have an installation that uses Microsoft SQL Server 2000 Desktop Engine (Windows) (WMSDE) for your databases.

SharePoint Portal Server 2003 includes a version of Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) with Service Pack 3.

1. Is in the context of SharePoint Portal Server
2. Has a managed concurrency workload governor that limits up to five concurrent batch workloads for optimal performance. If there are more than five concurrent workloads, it is highly recommended that you migrate to SQL Server 2000
3. MSDE 2000 supports (read as maximum size) up to 2 gigabytes (GB) per database. This limitation is per database, not per server. A single computer can support 16 MSDE 2000 instances, each with databases of up to 2 GB in capacity.

1. Is in the context of Windows SharePoint Services
2. If you are running Microsoft SQL Server 2000 Desktop Engine (Windows) (WMSDE) for your database, no search features are available. If you want to allow full-text searching on your Web sites, you must upgrade to SQL Server 2000.
3. WMSDE does not impose the 5 concurrent users and 2 GB per database limitation.

MSDE and WMSDE is fine in a small-scale environment, when you are hosting just a few Web sites, but if your server suddenly gets popular and you need to start hosting hundreds of sites, you may run into performance and storage problems. If you find yourself in this situation and need to move to a more scaled out solution, you can switch to using Microsoft SQL Server 2000 Service Pack 3 (SP3) as your database back end

Q. How do I distinguish between an MSDE and WSMDE installation?
A. Easy, see this BUG # : 469531.

How to Migrate Sites from (WSS-WMSDE) to (SPS-SQL)

Imagine a nice client doing good testing of a default WSS installed over WMSDE in a single server pilot installation.

They will ask during the pilot “We saw what WSS can do for us, Can you install SPS and show us some more?”

“Sure” you reply like a good architect would because SPS will really help them in collaboration with all the nice features.

And then they say “We want SPS with SQL Server on the same pilot box”.


You do realise that the default WSS installation that you did on their pilot box was with WMSDE. And you realise there already is some data in that WSS/WMSDE box with some additional sites and important documents that they dont want to loose.

So here is the problem statement “We would like to perform an in place upgrade / migrate of a pilot or a staging box which was installed with WSS and WMSDE to SPS and SQL Server”.

There are a couple of options which one could go for. Read through these scenarios and in the end I will explain which one I choose and why. Assumption is made that SQL Server is now installed on the pilot box as we need the SQL Enterprise Manager to connect to the local WMSDE instance (computer\sharepoint) and perform backups of WMSDE config and content databases. (to be saved onto media in case you land in trouble)

First scenario:

“Upgrade WSS/WMSDE to WSS/SQL and then install SPS over existing WSS”

[A] Migrating from WMSDE to SQL Server

[B] How To Integrate an Existing Windows SharePoint Services Installation with SharePoint Portal Server 2003

So you have upgraded in-place WSS/WMSDE to WSS/SQL in Step A and then from WSS/SQL to SPS/SQL in Step B.

Second scenario:

“Using the stsadm.exe tool with WSS/WMSDE create full site backups of all induvidual sites”

stsadm.exe -o enumsites -url http://localhost/
I have stsadm in my path. You will probably get a couple top sites including the top level. e.g.
http://localhost” Owner=”Domain\Administrator”
http://localhost/sites/government” Owner=”Domain\Administrator”
http://localhost/sites/mergers” Owner=”Domain\Administrator”

Now you would want to create 3 backup files for each of the above 3 sites, see below
stsadm.exe -o backup -url http://localhost/ -filename default.dat
stsadm.exe -o backup -url http://localhost/sites/government -filename government.dat
stsadm.exe -o backup -url http://localhost/sites/mergers -filename mergers.dat

It is now a good idea to backup the WMSDE config and content databases using the SQL Enterprise Manager. (Right Click on Database name, All Tasks, Backup)

And now the important part. We must check if Windows SharePoint Services Service Pack 1 was applied or not. If it was applied then in a later SPS installation we must apply it again before stsadm -o restore, else there will be an error. An easy way to check is using Control Panel – Add Remove Programs – Microsoft Windows SharePoint Services – Click here for support information. If the Version says 11.0.5608.0 then the SP was not applied, on the other hand if the Version says 11.0.6361.0 then SP1 was applied. Make a note of the Version Number.

Click for a large view (new window)

The image above shows a WSS & SPS installations with Version Numbers. Click on image for full view.

Now that we have all data, we can uninstall WSS
1. Uninstall WSS
2. Install SPS with SQL Server, Create a portal.
3. Ensure that the default out of the box portal is working (news, topics, site directory, etc)

At this point we have a clean SPS with SQL installation and a functioning Portal. As said earlier compare the WSS Version Number from control panel with the Version Number noted earlier from the WSS/WMSDE stage. If they dont match these next steps wont work.

We have 3 files that need to be restored to the portal http://server/sites/wss http://server/sites/government and http://server/sites/mergers

So lets fire away
stsadm.exe -o restore -url http://localhost/sites/wss -filename default.dat
stsadm.exe -o restore -url http://localhost/sites/government -filename government.dat
stsadm.exe -o restore -url http://localhost/sites/mergers -filename mergers.dat

All done, so far so good. If you were successfull you should add links to these under Site Directory

Third Scenario:

“Backup the WMSDE content database and add this backed up content database as “Additional Content Database” in a new SPS SQL installation.”

In the WSS/WMSDE situation
Enterprise Manager -> Connect to you WMSDE instance (computername\sharepoint)
Right Click on the content database (STS_123456, etc) All Tasks, Backup the database to a file on your filesystem. Call it c:\temp\WMSDE_STS_12356.dat

1. Uninstall WSS
2. Install SPS with SQL Server, Create a portal.
3. Ensure that the default out of the box portal is working (news, topics, site directory, etc)

At this point we have a clean SPS with SQL installation and a functioning Portal. As said earlier compare the WSS Version Number from control panel with the Version Number noted earlier from the WSS/WMSDE stage. If they dont match these next steps wont work.

Under SQL Enterprise Manager import the “c:\temp\WMSDE_STS_12356.dat” file as a new Database called PORTAL_SITE_01.

How to import an WMSDE DB into SQL Server?

SQL Enterprise Manager -> Expand SQL instance -> Right Click on your Databases (top one with open folder icon) -> All Tasks -> Restore Database -> General Option Tab -> Restore as Database “PORTAL_SITE_01” -> Choose “From Device” -> Select Device -> Add “c:\temp\WMSDE_STS_12356.dat” (which is the filename of the backup created from WMSDE using SQL Enterprise Manager) -> OK -> Options Tab (within Restore Database window) -> Ensure “Move to Physical File Name” have correct file path entries, they both should point to your SQL Server instance DATA directory.

Begin the Restore. It takes about 3~4 minutes for each Gig of data you have. (depending on your server power)

There, the WMSDE content database is now within the SQL Server as a new Content Database.

Goto SPS Central administration – Windows SharePoint Services – Configure Virtual Server Settings – Default Website – Manage Content Database – Add a Content Database.

Use the PORTAL_SITE_01 as your Database name, 9000 and 15000 as warning and maximum sites. Click OK. Your content databases page now will have 2 databases listed, PORTAL_SITE and PORTAL_SITE_01.

Here in this scenario I noticed that the top level site in WSS http://servername/ was not pulled over for some reasons, so I just used stsadm for the top level site and brought it as http://servername/sites/wss

Add links to the individual sites http://servername/sites/wss , http://servername/sites/government and http://servername/sites/mergers under the SPS Site Directory. If you wish you could go into each one of the restored sites and “Configure Connection to Portal” settings so they have “Up to Portal Name” in the navigation bar.


The first scenario is out of option, too complex and had to deal with host header names, (yikes, I already have enough issues with virtual server, eh). Not feasible in a large enterprise environments where you have to do a dozen paperwork for a hostname dns entry.

Second is good if you only have less than a dozen sites to move/upgrade and they are relatively small (not in Terrabytes). You could potentially write a script to stsadm -o restore looping through the enumsites, but I like to do them one by one just because I have been bitten in the past. (in prior assignments, users stored mdb files onto WSS [apparently then the admin removed this extension & I have no idea why] and this extension is blocked by default in WSS). Just a note that each prior existing installations are very unique and will vary a lot in mileage. So expect anything.

I really like third scenario, where there is not much work to be done if you had over a dozen sites or a Terrabyte of data. I actually did the entire Scenario Three in less than an hour for a database size of about 1.5 GB, (and that includes uninstall WSS, Install SPS, Move the Database, etc). Please note however that the top level WSS site was not available under SPS using Scenario Three and I had to manually stsdam to bring it over. Your mileage may vary. stsadm will migrate all documents, sites, sub sites and the permissions as well. Its a great quick fix tool.

Good luck to all performing these steps.

stsadm backup restore issues

As reported here there could be some issues when trying to stsadm backup and restore on the SAME box or should I specifically say ON THE SAME MSDE or SQL instance. What happens is when we take an stsadm backup, the individual list items, documents, etc are backed up including the GUID for that particular item. When we try to restore it back onto the SAME server it is going to complain.

stsadm -o backup http://server/sites/atestsite -filename atestsite.dat
stsadm -o restore http://server/sites/anewsite -filename atestsite.dat

“No content databases are available for restoring this site collection. Create a new content database and then try the restore operation again.”

Typically you will encounter this while testing backups and restores on the same box (single server configuration), however trying it on a different box or a WSS installation that is using a different SQL/MSDE instance will work OK. So for our pilot sake, we can just add a content datbase under the main WSS administration pages. SPS is smart enough to understand which content database to use when there are duplicate GUID.

My goal was to migrate all those WSS sites, http://server/sites/site1 etc into the “SPS Site Directory” area, stsadm worked very well, without issues. Next I will try to move the content databases directly from WMSDE into SQL Server and post results of such operation.