The best new method for moving SharePoint 2007 databases to a new database server!!!

So my team was up working for 18 hours on moving a new enterprise client SharePoint databases to a brand new database server. We followed this article, which was supposedly the best practice for moving the databases to a new database server. http://technet.microsoft.com/en-us/library/cc512725.aspx

After following this procedure we had several farm errors and had to roll back. We then took a moment to think about how SQL interacts with SharePoint and vice versa.

1. SharePoint servers all reference the SQL database server individually when serving content to end users and performing operations such as an index, excel web services render, InfoPath form render (app server roles). The servers know to look at the database server through this registry key. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0\Secure\ConfigDB.

2. The SharePoint config databases also store the SharePoint server names in them as parentid = 'InstanceID_GUID'.

This is why many people are forced to either use the -renameserver command in stsadm or build a new farm and migrate all content databases over to that new farm when choosing to change the database backend.

Alas the excellent solution, to this highly annoying issue that even Microsoft will be publishing in a few months based on our call tonight.

So what is this solution you ask? Think of how nice it would be if there were a single place to go to change the database server name and it worked every time. There is, it’s called SQL Alias’s.

If you have and old database server and you want to change the server that the farm points to for all databases including the config database. All you have to do is install the SQL client tools on each server. Setup alias's with clicfg.exe and create an aliases that says the old server name = the new server name.

If you are using named instances this works as well. Just create an additional alias with the /instance name included. You have to create two aliases when you have instance names involve because some SharePoint functions operate directly with the SQL server (Central Admin Functions), and some need the instance name specified.

This farm supports 20,000 users and all testing so far indicated zero reduction in performance.

Here is the tutorial as promised.

Perform following steps before creating alias and moving the SharePoint databases to new SQL server instance

• Quiesce the SharePoint farm


• Backup all the database on old SQL server (OldServerA\i4)
  Note: The SQL is on Microsoft Cluster Service (MSCS) cluster group, they are not virtual. But they do have instances and i4 stands for instance


• Restore all the databases on new SQL server (NewServerB\i1)


• Stop all the web sites in IIS Manager (Start à intemgr à Stop each site that related to SharePoint)


• Stop the services on all the Web Front Ends (WFEs) and Application Servers (Start>Run > services.msc)


• Microsoft Single Sign-On service


• Office Document Conversions Launcher service


• Office Document Conversions Load Balancer service


• Office SharePoint Server Search service


• Windows SharePoint Services Administration service


• Windows SharePoint Services Search service


• Windows SharePoint Services Timer service


• Windows SharePoint Services Tracing service


• Windows SharePoint Services VSS Writer service

Next create the alias on all the SharePoint WFEs and Application servers

Start > Run > cliconfig
Note: you need to have admin rights to run SQL configuration manager command line tool

Click on Alias Tab>Click Add

Uncheck the Dynamically determine port
Click OK

Click Add


Uncheck the Dynamically determine port
Click OK

Final results will look similar to this.

Click Apply and OK

Restart the services (Start>Run>services.msc)
Start the web sites (Start>intemgr>Start each site that related to SharePoint)
Make sure all Application Pools are up and running.
Open Central Admin
Open Home Site

SharePoint 2007 Backend Database Rename

Recently I was part of an effort to move a large farm to a new set of database servers. The farm consisted of the following machines.

4 Web Front End's
1 Application Server
2 SQL Cluster Nodes

The idea was to be able to move the entire farm from the old database server to the new database server. The challenge with doing this is that the configuration databases cannot be moved in the traditional manner of a backup and restore. The reason is that the farms databases hold the name of the SQL server or Cluster as a reference point. While you could take the content databases and easily move them, you would have to leave your Configuration Databases where they are, which is usually not desirable.

So how do I move my farm to a new Database Server or Cluster?

1. Perform a full backup of all data from Central Admin

2. Perform a full backup of all SQL databases and then restore them to the new DB server

3.  Before you stop any services ensure that the farm is quiesced.  This will prevent a known issue with the SSP becoming corrupt

4. Just stop IIS on the WFE's not the index service

5. Ensure that all user accounts have been assigned identical permissions on the new DB server.  All DB rights should come with the DB restores, but just double-check

6. Use STSADM and perform following step
stsadm -o renameserver -newservername <newsqlserver> -oldservername <oldsqlserver>

7. Bring IIS back online on all WFE's

8. Start the farm from Central Admin

9. Double-check that the DB Server name under topology view matches the new db server name

10. Ensure that search is working properly and that resulted are being returned.

If you are planning to move your Farm to a new Database Server consider the following.

What is my company's Recovery Time Objective (RTO), and will I be able to bring the farm backup in time if I cannot get the migration to work correctly?

Announcing Faceted Search v2.5 from the Microsoft SharePoint Search Blog

Starting Faceted Search 2.5, the solution relies on Microsoft Enterprise Library to address common software requirements in caching, logging, exception handling, policy injection etc., etc. More importantly, the 2.5 is a ground breaking release that is setting new targets for the Faceted Search. So, what's new?

New Features

1. Caching – dramatically improves performance and decreases the load on the search engine

The solution uses 2 mechanisms for manageable cache: quick and long. I built the caching logic on assumption that user knows what he/she is looking for. The Search Facets web part will cache original result set and use it for the search refinement, paging and other postbacks. If the initial result set doesn't provide full coverage of the search, the smart 2^nd thread will run against real-time data providing adjustment to the cached match.

2. Synchronization with Core Search Results web part

The MOSS search is adjusted by several parameters that designer can set for the Core Search Results web part itself. These include remove duplicates, enable trimming, permit noise words. When you drop the Search Facets web part to the search results page, it will find the Core Search Results, read its parameters and sync the search query parameters to exactly match ones used by the Core.

3. Support for advanced search

It was the most wanted feature since Faceted Search 1.0. With 2.5, the Facets are rendered for advanced search although do not extend yet to ranges. The functionality is accomplished by extending SearchQuery structure to accommodate POST requests and sync back to GET query.

4. Match of search counters

This release introduced an updated search syntax that is design to provide matching counters to the core search. In fact, the new search query is using both KeywordQuery and FullTextQuery through the use of generics.

public class GenericQuery<T> : IDisposable where T : Query

{

private EventHandler _customLogic;

 

public ResultTableCollection Execute(EventArgs args)

{

_customLogic(_query, args);

return _query.Execute();

}

 

...

}

.csharpcode, .csharpcode pre {font-size:small;color:black;font-family:consolas, "Courier New", courier, monospace;background-color:#ffffff;} .csharpcode pre {margin:0em;} .csharpcode .rem {color:#008000;} .csharpcode .kwrd {color:#0000ff;} .csharpcode .str {color:#006080;} .csharpcode .op {color:#0000c0;} .csharpcode .preproc {color:#cc6633;} .csharpcode .asp {background-color:#ffff00;} .csharpcode .html {color:#800000;} .csharpcode .attr {color:#ff0000;} .csharpcode .alt {background-color:#f4f4f4;width:100%;margin:0em;} .csharpcode .lnum {color:#606060;}

Additionally, the WHERE clause of the search query was modified to provide closer match to the Core counter.

5. Introducing Parent-Child relationships

By design, the facets can support only 2 levels. This release extended the Facets schema to allow management of the nested layers. That eases the pain of displaying complex hierarchies such as geography, or org chart etc. Parent-Child relationship can be set by facet name and facet value, or just by facet name.

<Column Name="BDCCity" DisplayName="City" ParentName="BDCState" />

<Column Name="BDCState" DisplayName="State" >

<Mappings>

<Mapping Match="Alberta" ParentName="BDCCountry" ParentValue="Canada"/>

<Mapping Match="Manitoba" ParentName="BDCCountry" ParentValue="Canada" />

<Mapping Match="Ontario" ParentName="BDCCountry" ParentValue="Canada"/>

<Mapping Match="Quebec" ParentName="BDCCountry" ParentValue="Canada"/>

</Mappings>

</Column>

.csharpcode, .csharpcode pre {font-size:small;color:black;font-family:consolas, "Courier New", courier, monospace;background-color:#ffffff;} .csharpcode pre {margin:0em;} .csharpcode .rem {color:#008000;} .csharpcode .kwrd {color:#0000ff;} .csharpcode .str {color:#006080;} .csharpcode .op {color:#0000c0;} .csharpcode .preproc {color:#cc6633;} .csharpcode .asp {background-color:#ffff00;} .csharpcode .html {color:#800000;} .csharpcode .attr {color:#ff0000;} .csharpcode .alt {background-color:#f4f4f4;width:100%;margin:0em;} .csharpcode .lnum {color:#606060;}

In the configuration above, the City facets will display only after the user chose the State. The State itself will match the country of origin.

6. Extending search to logical "OR" queries

Original facets always represent "AND" queries. That implies ability to narrow the search results by adding extra criteria. In this release I prototyped the way to expand the search by adding additional matches to the criteris. This in fact resulted in rewamped the Bread Crumbs UI. Proviuded now out-of-the-box support for languages is a good example of how "OR" queries empower the search.

7. Simplified web part properties

The 2.5 release is friendly to modifications of the web part properties. I have all properties classified and broken down to groups for each of the web parts.

8. Other

There are lots and lots of numerous fixes and enhancements, including improved security validation, code refactoring, extending facet sorting, support of quoted search and duplicates etc., etc.

What's next

It's my privilege to say that we have a team now that helps to shape new releases and brainstorm the furutre of the Faceted Search. In present we are looking at AJAX and SilverLight and hopefully you'll start seeing more and more power of Facets in the near future.

 

New Tool released to CodePlex called The SharePoint Cross-site Configurator!

The SharePoint Cross-site Configurator is really a tool which helps optimize the management of governance across a SharePoint farm. It automates changing governance variables such as expiration policies, auditing policies, content type management settings and many other governance related items.

As governance becomes more and more important to Microsoft we receive more and more documentation on its implications, uses and best practices. Here are some documents that I found extremely useful on a recent IA project.

SPConfigurator

• http://www.codeplex.com/SPConfigurator

Governance Documents

• Sample governance plan

• Governance checklist guideßAwesome!

• Keeping control of your SharePoint sites with IT governance

• Compliance and records management

• Information architecture in SharePoint Server 2007

• SharePoint Products and Technologies customization policy

• Windows SharePoint Services manageability controls

• White paper: Compliance features in the 2007 Microsoft Office system

• White paper: Steps for building governance into SharePoint Server 2007

• Robert Bogue's SharePoint Governance, Part 1

• Robert Bogue's SharePoint Governance, Part

SharePoint Content Deployment Wizard

Today I came across the SharePoint Deployment Wizard while looking for a way to deploy entire site collections from one farm to another. I just finished building a three stage SharePoint environment consisting of DEV, QA and Production and I needed a way to push the site collections through the staged environment without using the stsadm tool. I loaded the content deployment wizard, specified a few URL's and the entire collection moved from DEV to QA with no issues at all.
Since we had only a week left to deploy the solutions this tool saved a huge amount of work for our team.

http://www.codeplex.com/SPDeploymentWizard

Recursive Site Backup Script

I came across this backup script today while looking for a way to enumerate sites and then create individual site backups based on the resulting .XML output.

Just copy the following code into a .vbs file and modify the top variables for your environment.

'Moded by Stephen Costigan stephen.costigan@us.sogeti.com
Option Explicit

'Set path to STSADM
Const STSADM_PATH = "C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN\stsadm"

'Change based on where you want the log files to go to
Const STSADM_LOGPATH = "C:\LogFiles"

'Change based on where you want the backup files to go to
Const STSADM_BAKPATH = "C:\BakFiles"

'Set file names for log and XML
Const STSADM_LOGFILE = "BackupLog.txt"
Const STSADM_XMLFILE = "BackupXML.txt"

'Change based on the portal URL
Const STSADM_PORTAL_URL = "https://portal.com/"

'Change to determin how many instances of backup you want running at once
Const NUM_BACKUP_PROCESSES = 4

'Declare objects
Dim objFso, objFolder, objFiles, objFile, objShell, objExec, objXml, objSc, objUrl, objLog, objLog2, objRunningBackupProcess

'Declare strings and integers
Dim strResult, strUrl, strFileName, strCmd, n

Dim g_intTotalProcessesLaunched ' As Integer
Dim g_intOpenProcessSlot ' As Integer
Dim g_bInitialLaunch ' As Boolean
Dim g_bOpenProcessSlot ' As Boolean
Dim g_bKeepWaiting ' As Boolean
ReDim g_CurrentProcesses(NUM_BACKUP_PROCESSES) ' As Array

'Set values
g_intTotalProcessesLaunched = 0
g_bInitialLaunch = True
g_bKeepWaiting = False

'Open folder where backups and log files will be stored
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder(STSADM_LOGPATH)

'Delete all files currently present in the backup folder.
Set objFiles = objFolder.Files
For Each objFile in objFiles
objFile.Delete(True)
Next

'Create log file and write first entry
Set objLog = objFso.CreateTextFile(STSADM_LOGPATH + STSADM_LOGFILE, True)
objLog.writeline(Now & ": Begin backup")

'Retrieves all site collections in XML format.
Set objShell = CreateObject("WScript.Shell")
Set objExec = objShell.Exec(STSADM_PATH & " -o enumsites -url " & STSADM_PORTAL_URL)
strResult = objExec.StdOut.ReadAll
objLog.writeline(Now & ": Enum Sites")

'Create XML log file and push content into it
Set objLog2 = objFso.CreateTextFile(STSADM_LOGPATH + STSADM_XMLFILE, True)
objLog2.writeline(strResult)
objLog2.close
objLog.writeline(Now & ": Wrote XML")

'Load XML in DOM document so it can be processed.
Set objXml = CreateObject("MSXML2.DOMDocument")
objXml.LoadXML(strResult)
objLog.writeline(Now & ": Read XML into DOM")

'Loop through each site collection and call STSADM to make a backup.
For Each objSc in objXml.DocumentElement.ChildNodes
If (g_bInitialLaunch) Then
Set objExec = LaunchNewProcess()
g_intOpenProcessSlot = (g_intTotalProcessesLaunched - 1) Mod NUM_BACKUP_PROCESSES
Set g_CurrentProcesses(g_intOpenProcessSlot) = objExec
If (NUM_BACKUP_PROCESSES = g_intTotalProcessesLaunched) Then
g_bInitialLaunch = False
End If
Else
g_bKeepWaiting = True
' Delay here until one of the processes in the array exits, then start another
Do While g_bKeepWaiting
WScript.Sleep 1000
objLog.writeline(Now & ": Waiting for command to execute and complete")

'Walk the array and see if everything is still running
'N.B., This algorithm finds only the lowest open slot and fills it with a new running process
'If there are multiple open slots, the higher ones will be filled on subsequent passes through
'the loop. This may mean that if processes exit in less than the sleep delay above, there may
'be fewer running instances than the NUM_BACKUP_PROCESSES limit.

For n = (NUM_BACKUP_PROCESSES - 1) To 0 Step -1
Set objRunningBackupProcess = g_CurrentProcesses(n)
g_bOpenProcessSlot = Not (objRunningBackupProcess.Status <> 1) ' 1 is WshScriptExec.WshFinished
If g_bOpenProcessSlot Then
g_bKeepWaiting = False
g_intOpenProcessSlot = n
objLog.writeline(Now & ": Found Open Slot: g_intOpenProcessSlot is " & g_intOpenProcessSlot)
End If
Next
If (False = g_bKeepWaiting) Then
Set objExec = LaunchNewProcess()
Set g_CurrentProcesses(g_intOpenProcessSlot) = objExec
objLog.writeline(Now & ": g_intTotalProcessesLaunched is " & g_intTotalProcessesLaunched)
End If
Loop
End If
Next

'Write completion message to lof file
objLog.WriteLine(Now & ": Backup of portal site collections complete")
objLog.WriteLine("Total Backup Processes: " & g_intTotalProcessesLaunched)

'Close log file
objLog.close


'Function that actually kicks off the backup process for each site collection
Function LaunchNewProcess()
strUrl = objSc.Attributes.GetNamedItem("Url").Text
strFileName = STSADM_BAKPATH & Replace(Replace(strUrl, "https://", ""), "/", "_") & ".bak"
strCmd = STSADM_PATH & " -o backup -url """ + strUrl + """ -filename """ + strFileName + """"
objLog.WriteLine(Now & ": Executing Command '" & strCmd & "'")
Set LaunchNewProcess = objShell.Exec(strCmd)
g_intTotalProcessesLaunched = g_intTotalProcessesLaunched + 1 ' Keep track of how many instances are launched
End Function

Welcome

Welcome to Stephen Costigan's SharePoint Blog.

I am currently a Principle Architect for Sogeti USA working on various large SharePoint 2007 implementations.

This blog will act to serve content depicting new ideas, features, experiences and discoveries as I architect new solutions.

Stephen Costigan