Hello all,
I've been meaning to get around to posting this script for the
SharePoint community. I have a MOSS 2007 farm configured with two SQL
2005 database servers on the back end set up for mirroring. I decided
to play around with automating (as much as possible) the failing over
of all the MOSS 2007 databases. I went through many iterations of the
script below, and feel this is pretty close to being exactly what I
intended. I'm assuming you already have a SQL mirrored back end
configured for your MOSS 2007 farm. There are a couple things to note
here.
- The script assumes stsadm in is in your environment variables path. Here's how you do it.
http://stevepietrekweblog.wordpress.com/2007/06/08/quick-access-to-stsadm-command/ - The script is intended to failover all of the databases, and
reconfigure SharePoint to use the new SQL server. It will not take into
account one or two databases failing over. - It's designed to assist in the event of a catastrophic
failure of the principal SQL server. It'll go from principal to mirror
or mirror to principal. - You will need to modify the variables in the script to
reflect your environment. (I left all of my information in there as a
reference). - After the failover, you'll need to go to your MOSS 2007
admin site, and for each content databases select a SharePoint services
search server.
Out of the box, there is no way to tell the failed over sites which server is the search server. fortunately Gary Lapointe has addressed this issue for us. Go to his site http://stsadm.blogspot.com/ and download his stsadm extension "managecontentdbsettings" this will allow you to point the site content databases to a search server. I have the extension installed on my server and it works great. I did not include the switch for setting the search server in the script as you may or may not want to install the extension.
Another great script that is actually referenced in the SQL Failover Commands script is the warmupserver script. You'll have to install the scripts or remove the section in my script that references them. The line you would have to remove is after the IISRESET, it's simply called startup.
iisreset
PING 1.1.1.1 -n 1 -w 2000 >NUL
ECHO.
startup <= remove this line if you don't want to install the warmupserver scripts
You can get it a Joel Olsen's site here http://blogs.msdn.com/joelo/archive/2006/08/13/697044.aspx All you need to do is extract then to the root of your C: drive of your MOSS server, modify startup.bat and warmupserver.cmd to reflect your environment, and your ready. Here's what mine look like.
startup.bat
@echo off
echo This script will:
echo * run stsadm to warmup the admin interfaces
echo * hit each page in the portal to force their JIT
echo.
echo.
call WarmUpServer home.magerr.com
warmupserver.cmd
@echo off
if "%1"=="" echo. & echo ERROR: Missing ServerName parameter. & goto Usage
call :HitPage %1 /Pages/default.aspx
call :HitPage sp2007 /default.aspx
call :HitPage ssphome /ssp/admin/default.aspx
goto :EOF
:HitPage
echo Hitting http://%1%2 ...
cscript HttpRequest.vbs GET http://%1%2 /q //nologo
goto :EOF
:Usage
echo.
echo Usage: WarmUpServer ServerName
echo.
echo Example: WarmUpServer server
echo.
goto :EOF
The script that does all of the heavy lifting is called HttpRequest.vbs and is in the warmupserver.zip
I have two scheduled tasks running on my MOSS production server that fire the warmupserver script. One runs every two hours, and the other runs at system startup. I've been using them in production for months now. These scripts make the farm fly!
I've actually had a couple of my MOSS databases failover to the mirrored SQL server. This pretty much makes the farm inaccessible for the users. I usually don't know about it unless I happen to check, or someone calls. That's just not acceptable. One of my next projects is to try to figure out a way to address one or two databases failing over. I'd like to do it where no user intervention is necessary. Mike Watson has a great post on his site SharePoint Hosting and Development, that includes a nice SQL script to help get started. I think I'm going to see what I can do using his suggestions.
That's about it for this post
Thanks.
Here's the failover script. Copy it, save it as failover.cmd Enjoy 
@ECHO OFF
Rem ===================================================================
Rem == This section needs to be modified to reflect your environment ==
Rem ===================================================================
Rem sqlpri = Primary SQL Server
Rem sqlmir = Mirror SQL Server
Rem sspdb = SSP Database
Rem sspsite = SSP Admin Site
Rem ssptitle = SSP Title
Rem mossdb = Moss application Database
Rem mosssite = Moss URL
Rem mysitedb = MySite Database
Rem mysite = MySite URL
Rem idxsrv = Index Server
Rem idxloc = Index file location
Rem sspadm = SSP login acct
Rem ssppass = Password for ssp login
Rem domain = domain for sspadmin login
SET sqlpri=spsql1
SET sqlmir=spsql2
SET sspdb=WSS_Content_SSPHome
SET sspsite=http://ssphome.magerr.com/ssp/admin
SET ssptitle=SSP_HOME
SET mossdb=WSS_Content_home
SET mosssite=http://home.magerr.com
SET mysitedb=WSS_Content_MySite
SET mysite=http://mysite.magerr.com
SET idxsrv=sp2007
SET idxloc="C:Program FilesMicrosoft Office Servers12.0DataOffice ServerApplications"
SET sspadm=spadmin
SET ssppass=p@ssw0rd
SET domain=magerr
Rem ===================================================================
Rem ==== You should not have to modify anything below this section ====
Rem ===================================================================
ECHO ******************************************************
ECHO.
ECHO ** SharePoint SQL Mirror Failover Script
ECHO.
ECHO ** Created July 1 2007
ECHO ** Gene Magerr
ECHO ** genemagerr@hotmail.com
ECHO ** Version 1.0 – July 8, 2007
ECHO ** Version 1.1 – July 15,2007 – Added -preparetomove.
ECHO ** Version 1.3 – January 20, 2007 – Added variables
ECHO **
ECHO ** You have a royalty-free right to use, modify, reproduce, and
ECHO ** distribute this script file in any way you find useful, provided that
ECHO ** you agree that the creator, owner above has no warranty, obligations,
ECHO ** or liability for such use.
ECHO.
ECHO ** This batch file will prepare and disconnect
ECHO ** all of the sharepoint sites content databases,
ECHO ** stop and restart all related services, point
ECHO ** SharePoint to the failed over database server,
ECHO ** and re attach the failed over content databases.
ECHO.
ECHO ******************************************************
ECHO.
ECHO Please choose a menu option.
ECHO.
ECHO 1. Use the Principal SQL Server – %sqlpri%
ECHO 2. Use the Mirrored Server – %sqlmir%
ECHO 3. Quit.
ECHO.
CHOICE /C:123 /N /T:180 /D:3
ECHO.
IF ERRORLEVEL == 3 GOTO QUIT_MENU
IF ERRORLEVEL == 2 GOTO USE_MIRROR
IF ERRORLEVEL == 1 GOTO USE_PRIMARY
:USE_PRIMARY
ECHO.
ECHO Configuring %sqlpri% as Principal SQL Server. %sqlmir% will be the mirror.
stsadm -o renameserver -oldservername %sqlmir% -newservername %sqlpri%
PING 1.1.1.1 -n 1 -w 2000 >NUL
ECHO Stopping and restarting key services
ECHO.
net stop oSearch
net stop SPAdmin
net stop SPTimerv3
net stop SPTrace
net start oSearch
net start SPAdmin
net start SPTimerv3
net start SPTrace
PING 1.1.1.1 -n 1 -w 2000 >NUL
ECHO.
ECHO Preparing %mosssite% Content Database
stsadm -o preparetomove -contentdb %sqlpri%:%mossdb% -site %mosssite%
ECHO Removing content database for %mosssite%
stsadm -o deletecontentdb -url %mosssite% -databasename %mossdb% -databaseserver %sqlpri%
PING 1.1.1.1 -n 1 -w 2000 >NUL
ECHO Preparing %mysite% Content Database
stsadm -o preparetomove -contentdb %sqlpri%:%mysitedb% -site %mysite%
ECHO Removing content database for %mysite%
stsadm -o deletecontentdb -url %mysite% -databasename %mysitedb% -databaseserver %sqlpri%
PING 1.1.1.1 -n 1 -w 2000 >NUL
ECHO Preparing %sspsite% Content Database
stsadm -o preparetomove -contentdb %sqlpri%:%sspdb% -site %sspsite%
ECHO Removing content database for %sspsite%
stsadm -o deletecontentdb -url %sspsite% -databasename %sspdb% -databaseserver %sqlpri%
ECHO Stopping and restarting key services
ECHO.
net stop oSearch
net stop SPAdmin
net stop SPTimerv3
net stop SPTrace
net start oSearch
net start SPAdmin
net start SPTimerv3
net start SPTrace
PING 1.1.1.1 -n 1 -w 2000 >NUL
ECHO.
ECHO Adding content database for %mosssite%
stsadm -o addcontentdb -url %mosssite% -databasename %mossdb% -databaseserver %sqlpri%
PING 1.1.1.1 -n 1 -w 2000 >NUL
ECHO Adding content database for %mysite%
stsadm -o addcontentdb -url %mysite% -databasename %mysitedb% -databaseserver %sqlpri%
PING 1.1.1.1 -n 1 -w 2000 >NUL
ECHO Adding content database for %sspsite%
stsadm -o addcontentdb -url %sspsite% -databasename %sspdb% -databaseserver %sqlpri%
PING 1.1.1.1 -n 1 -w 2000 >NUL
ECHO Reconfiguring Search
stsadm -o osearch -action stop -f
PING 1.1.1.1 -n 1 -w 2000 >NUL
stsadm -o osearch -action start -role IndexQuery
PING 1.1.1.1 -n 1 -w 2000 >NUL
ECHO Reconfiguring SSP and Index
ECHO.
stsadm -o editssp -title %ssptitle% -sspadminsite %sspsite% -ssplogin %domain%\%sspadm% -ssppassword %ssppass% -indexserver %idxsrv% -indexlocation %idxloc%
PING 1.1.1.1 -n 1 -w 2000 >NUL
ECHO Stopping and Restarting IIS
ECHO.
cd
iisreset
PING 1.1.1.1 -n 1 -w 2000 >NUL
ECHO.
startup
GOTO QUIT_MENU
:USE_MIRROR
ECHO.
ECHO Configuring %sqlmir% as Principal SQL Server. %sqlpri% will be the mirror.
stsadm -o renameserver -oldservername %sqlpri% -newservername %sqlmir%
PING 1.1.1.1 -n 1 -w 2000 >NUL
ECHO Stopping and restarting key services
ECHO.
net stop oSearch
net stop SPAdmin
net stop SPTimerv3
net stop SPTrace
net start oSearch
net start SPAdmin
net start SPTimerv3
net start SPTrace
PING 1.1.1.1 -n 1 -w 2000 >NUL
ECHO.
ECHO Preparing %mosssite% Content Database
stsadm -o preparetomove -contentdb %sqlmir%:%mossdb% -site %mosssite%
ECHO Removing content database for %mosssite%
stsadm -o deletecontentdb -url %mosssite% -databasename %mossdb% -databaseserver %sqlmir%
PING 1.1.1.1 -n 1 -w 2000 >NUL
ECHO Preparing %mysite% Content Database
stsadm -o preparetomove -contentdb %sqlmir%:%mysitedb% -site %mysite%
ECHO Removing content database for %mysite%
stsadm -o deletecontentdb -url %mysite% -databasename %mysitedb% -databaseserver %sqlmir%
PING 1.1.1.1 -n 1 -w 2000 >NUL
ECHO Preparing %sspsite% Content Database
stsadm -o preparetomove -contentdb %sqlmir%:%sspdb% -site %sspsite%
ECHO Removing content database for %sspsite%
stsadm -o deletecontentdb -url %sspsite% -databasename %sspdb% -databaseserver %sqlmir%
ECHO Stopping and restarting key services
ECHO.
net stop oSearch
net stop SPAdmin
net stop SPTimerv3
net stop SPTrace
net start oSearch
net start SPAdmin
net start SPTimerv3
net start SPTrace
PING 1.1.1.1 -n 1 -w 2000 >NUL
ECHO.
ECHO Adding content database for %mosssite%
stsadm -o addcontentdb -url %mosssite% -databasename %mossdb% -databaseserver %sqlmir%
PING 1.1.1.1 -n 1 -w 2000 >NUL
ECHO Adding content database for %mysite%
stsadm -o addcontentdb -url %mysite% -databasename %mysitedb% -databaseserver %sqlmir%
PING 1.1.1.1 -n 1 -w 2000 >NUL
ECHO Adding content database for %sspsite%
stsadm -o addcontentdb -url %sspsite% -databasename %sspdb% -databaseserver %sqlmir%
PING 1.1.1.1 -n 1 -w 2000 >NUL
ECHO Reconfiguring Search
stsadm -o osearch -action stop -f
PING 1.1.1.1 -n 1 -w 2000 >NUL
stsadm -o osearch -action start -role IndexQuery
PING 1.1.1.1 -n 1 -w 2000 >NUL
ECHO Reconfiguring SSP and Index
ECHO.
stsadm -o editssp -title %ssptitle% -sspadminsite %sspsite% -ssplogin %domain%\%sspadm% -ssppassword %ssppass% -indexserver sp2007 -indexlocation %idxloc%
PING 1.1.1.1 -n 1 -w 2000 >NUL
ECHO Stopping and Restarting IIS
ECHO.
cd
iisreset
PING 1.1.1.1 -n 1 -w 2000 >NUL
ECHO.
startup
:QUIT_MENU