SQL Maintenance

February 11, 2011

This is a rather long story about SQL Maintenance. The short version: you really need maintenance plans on your SQL Servers. The long version:

A couple of weeks ago I was asked to help troubleshoot problems with a Microsoft Project Server 2007 environment. After looking into the details, it seemed clear the problem only occurred on large projects. It also felt like a timeout. The message was:

Project Center cannot access the project(s) you are trying to view. It is most likely that you either don’t have permissions to view the project, another user has deleted this project(s) before you were able to view it or that another user is in the process of publishing the project.

I will not dive into Project Server troubleshooting here, because main reason of this error message was the lack of SQL maintenance. The databases were in bad shape, causing all database related tasks much longer to complete (in this case too long).

This is not Project Server specific though. Every SQL Server should be setup to run some maintenance on a regular basis. In SQL Server, this is done by Maintenance Plans. Although I’m not a certified DBA, I think it is safe to share a basic maintenance plan. Use at your own insights.


  • SQL Server Management Studio
  • SQL Server Agent running and set to start automatic

So here goes:

  1. Open SQL Server Management Studio
  2. Navigate to Management > Maintenance Plans
  3. Right click on the folder Maintenance Plans and choose Maintenance Plan Wizard
  4. The Wizard will speak for itself mostly…
  5. Provide a name
    In this case, I will create a weekly plan for the User databases. The User databases are all databases that are not System databases (master, model, msdb and tempdb). The System databases do not require the same extensive maintenance)
  6. Create a schedule for the plan to run
  7. Select tasks to be run

    In this case, I chose the following: 

    1. Check Database Integrity
      Make sure the database is consistent, if not we have a serious problem
    2. Rebuild Index
      Ideally you would check for fragmentation first, and then choose between Reorganize of Rebuild
    3. Update Statistics
      Not always necessary,  for instance SharePoint has timer jobs that perform this tasks
    4. Backup (Full and Transaction Log)
      Speaks for itself
    5. Maintenance Cleanup Tasks
      Clean up some old logfiles or backups
  8. Set the right order in which the tasks will be run
  9. Set options for the Integrity Check
    In this case I selected All User Databases (newly created databases are added automatically)
  10. Set options for Index Rebuild
  11. Set options for Update Statistics
  12. Set options for Backup Databases
    Note that you can check to create subfolders per database. Also, I selected to verify the backup (which will cause the tasks to run longer)
  13. Set options for Backup Database Transaction Log
    Same options as previous task. Note that system databases do not have transaction logs
  14. Set cleanup options
    We can choose to have the tasks remove old backups or logfiles.
  15. Write a logfile of the maintenance plan and/or send a report by email (if database mail is configured)
  16. Review and test


  • A consistency check can be done on a daily basis, while Index or Statistics operations would only need to run once a week or so
  • Test the plan. See how much time it takes to complete
  • Test the plan. See how it impacts performance. Index operations can be very heavy
  • Monitor. Review logfiles. It’s nice to have these tasks automated now, but how will you know if a consistency check failed?
  • Maintenance plans are run by the account that created or modified them. Consider the account you use to create these plans.

I hope this helps in having more healthy SQL Servers. It’s like fitness and yoga for your databases 🙂


Open PDF in SP2010

December 9, 2010

Today I ran into a SharePoint 2010 environment where PDF documents would render a Save As dialog box. The desired behavior was opening the PDF document in the browser (or opening a PDF viewer directly).
Of course, we can set the Browser File Handling to Permissive on the Web Application level (through Central Administration). This should work, but not in our case. It turns out some Document Libraries or Webs don’t feel like taking over this setting. Each list also has a Browser File Handling property.
A blog post by Kim Nguyen helped me in the right direction, all I had to do was modify her script to my wishes 🙂
The property to set: BrowserFileHandling
In PowerShell:

# Set BrowserFileHandling on all document libraries in all webs

param([string]$SiteName = “http://intranet”, $Path = “C:\PowerShell-Log.rtf”)

Start-Transcript -Path $Path -Append

$Site = Get-SPSite($SiteName)

# Root Web
$Web = $Site.OpenWeb()
Write-Host -BackgroundColor Magenta “Opening Web ” $Web.Url
foreach ($List in $web.Lists) {
    Write-Host ” Opening List ” $List.Title
    if($List.BaseType -match “DocumentLibrary”) {
        if($List.BrowserFileHandling -eq “Strict”) {
            $List.BrowserFileHandling = “Permissive”
            Write-Host -ForegroundColor Green ”  BrowserFileHandling set to Permissive”
        } else {
            Write-Host -ForegroundColor Yellow ”  BrowserFileHandling already set to Permissive”
        }    } else {
        Write-Host -ForegroundColor Cyan ”  List is not a Document Library (” $List.BaseType “)”

# Sub Webs
foreach ($Web in $Site.AllWebs) {
    Write-Host -BackgroundColor Magenta “Opening Web ” $Web.Url
    foreach ($List in $web.Lists) {
        Write-Host ” Opening List ” $List.Title
        if($List.BaseType -match “DocumentLibrary”) {
            if($List.BrowserFileHandling -eq “Strict”) {
                $List.BrowserFileHandling = “Permissive”
                Write-Host -ForegroundColor Green ”  BrowserFileHandling set to Permissive”
            } else {
                Write-Host -ForegroundColor Yellow ”  BrowserFileHandling already set to Permissive”
        } else {
            Write-Host -ForegroundColor Cyan ”  List is not a Document Library (” $List.BaseType “)”

It’s already two weeks ago, but I decided to go for the 70-668 exam too. It’s wasn’t as hard as I’d imagined, though the questions do require more understanding of the platform at a higher level. Now that the certification is done, I’d love to move on to the next certification project. From a SharePoint IT perspective, there is nothing between the IT-Pro and the Master certification. I’d love to go for that one too, but that would require “a bit” more dedication and effort. Who knows, someday…

SharePoint 2010 Certified!

August 12, 2010

Last Tuesday I took the step and went for my first SharePoint 2010 certification, 70-667 SharePoint 2010, Configuring. I wasn’t absolutely sure I would pass it, since there was so little information about the contents of the exam. Given the fact that e-office let me work with SharePoint 2010 non-stop since the first day I started there (February 1st), I figured I should be so bold to just take the exam without practice.

So, I booked the exam on Monday evening and  scheduled it for the next morgning. No time for second thoughts 🙂 Despite the fact that I do not know all PowerShell commandlets and Central Andmin options by heart, I did manage to pass the exam with 816 points. I would have liked a better score, but then I should probably have done some preparing…

On to the 70-668 exam 🙂

Wan’t to learn about wave 14 of Communications Server and related products? My colleague Joachim will be hosting a free Live Meeting on September 20 where you can learn and ask everything about CS14. 3 interesting sessions / speakers should proof to make an educational meeting 🙂

On August 17, e-office (Jan Gremmen and myself) will be hosting a “summerschool” session covering SharePoint 2010 best practices for the IT Pro. Our partner AvePoint will be joining us to give some insights into what they have to offer when it comes to backup, migrations and storage optimizations. Jan and myself will be talking about backup and migration or a more global scale and also dive into the SharePoint 2010 Service Application model, and what this means for your infrastructure and topology.

More details can be found on the e-office website. It’s a free event and will be hosted in Houten (Netherlands) on August 17, from 09:00 to 13:00.

I hope to see you there!

In a previous article I wrote about our migration from SharePoint 2007 to SharePoint 2010. In order to get Search working in a cross-domain environment, Claims Based Authentication is the way to go. While creating a CBA Web Application is not much of a challenge, it does have some implications on your migration path.

Following the Technet documentation we were able to migrate our sites, using SQL backups en Mount-SPContentDatabase. This results in a mounted Content Database that can already be accessed. But, it seems there is another step to take which as of yet is not mentioned in every Technet article. When moving from Classic Authentication to Claims Based Authentication, we also have to migrate our user info. Makes sense, but this was only mentioned in context with Forms Based Authentication, which is why I never read it.

So, when migrating from Classic Authentication to Claims Based Authentication, here are the steps:

1) Restore your SQL databases to your SQL server
2) Make sure the farm account has DBO permissions on these databases
3) Create your CBA Web Application in Central Admin or PowerShell
4) Run Test-SPContentDatabase and fix any possible errors
5) Run Mount-SPContentDatabase
*) If you are in a cross-domain environment, run the stsadm -o setProperty -pn PeoplePicker-SearchADforest command (see details)
6) Migrate users through PowerShell:
$WebApp = Get-SPWebApplication https://mywebapp

That’s all! Remember that, before step 1, you need to run your checks on the SharePoint 2007 environment. SP2 for MOSS added the stsadm -o PreUpgradeCheck command, which checks for any possible issues you might encounter when migrating to SharePoint 2010. This is a crucial step to your migration!

I hope this helps in better migrations 🙂