Tuesday, 13 March 2007

Attaching Content DB's in MOSS/WSS3

After completing your capacity planning exercise prior to deploying MOSS/WSS3 you will have the number of content DB's you are going to start with (a very different proposition to working out the number of content DB's you'll end up with!). For the sake of the article let's say the number came out at 22 per WebApp.


So, you can either create 21 DB's manually (remember, you already have one) and then assign them manually, which will be about 90 seconds to create each DB in SQL, plus around 60 seconds to attach each DB to the WebApp, so it will take (1+1.5)*21 for each WebApp, totalling 52.5 minutes of repetitive boredom (unless you have a placement student or intern to hand!)...


So, partly because I'm lazy, partly because a hate repetitive tasks, but mainly because I like scripting, I thought I'd write some scripts to do all this for me...

The SQL script

This script will create you the additional 21 content DB's, it assumes your first content DB already exists and is called SharePoint_ContentDB_01, simply copy-paste into SQL Query Analyzer... (Thanks to Steve Maxwell for this)


USE [master]
@DBName nVARCHAR(255)
, @Number nVARCHAR(4)
, @DataFileName nVARCHAR(255)
, @LogFileName nVARCHAR(255)
, @DataFile nVARCHAR(255)
, @LogFile nVARCHAR(255)
, @ServiceIdentity nVARCHAR(255)
, @AppPoolIdentity nVARCHAR(255)
@Number = '02'
/* set these to the correct values for you environment */
, @ServiceIdentity = '<SERVICE Account>'
, @AppPoolIdentity = '<AppPool Account>'
WHILE @Number <= 22
SET @DBName = 'SharePoint_ContentDB_'+@Number
@DataFileName = @DBName
, @LogFileName = @DBName + '_log'
/* set the following paths to the correct locations in your environment */
, @DataFile = 'M:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' + @DBName + '.mdf'
, @LogFile = 'L:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' + @DBName + '_log.LDF'
(NAME = ''' + @DBName + '''
, FILENAME = ''' + @DataFile + '''
/*set the following pre-size and growth values to whatever suits your needs - this helps ensure data file are contiguous*/
, SIZE = 10240000KB
, FILEGROWTH = 5120000KB)
(NAME = ''' + @LogFileName + '''
, FILENAME = ''' + @LogFile + '''
/*set the following pre-size and growth values to whatever suits your needs - this helps ensure log file are contiguous*/
, SIZE = 2048000KB
, FILEGROWTH = 1024000KB)
COLLATE Latin1_General_CI_AS_KS_WS'
EXEC dbo.sp_dbcmptlevel @dbname=@DBName, @new_cmptlevel=90
EXEC [SharePoint_ContentDB_01].[dbo].[sp_fulltext_database] @action = 'enable'
SET @SQL = 'USE '+@DBName+';EXEC sp_changedbowner '''+@ServiceIdentity+''''
SET @SQL = 'USE '+@DBName+';CREATE USER ['+@AppPoolIdentity+'] FOR LOGIN ['+@AppPoolIdentity+']'
SET @SQL = 'USE '+@DBName+';EXEC sp_addrolemember ''db_owner'', '''+@AppPoolIdentity+''''
SET @Number = @Number + 1
SET @Number = RIGHT('00' + @Number,2)

The VB

Place this script in C:\Add_ContentDB_Script\

It will create a batch file called AddDBAContentDB.bat which must be run in order to attach the content DB’s to the web application. The batch file must NOT exist already as no checks to find an existing file are performed.


The variables

The script must be changed to suit the environment and the following variables edited:


SQLServ = SQL Server/Instance where the DB’s have been created (eg; MOSSSQLCLU01)

DBnameconv = The naming convention used, without number. (eg; SharePoint_contentDB_)

DBnumberEnd = The number of DB’s plus one (if 22 DB’s have been created use 23)

DBnumberStart = The first DB not already attached to the MOSS/WSS site collection (eg; 02)

SiteURL = The NLB URL of the MOSS/WSS WebApp to attach the DB’s (eg; http://web.archive.org/web/20070319224126/http://intranet.domain.com/)

SiteLimit = The maximum number of sites allowed in the DB

SiteWarn = The number at which a warning is generated


The Script

dim SQLServ
Dim DBnameconv
Dim DBnumberEnd
Dim DBnumberStart
Dim SiteURL
Dim SiteLimit, SiteWarn
'set the parameters used here
DBnameconv = "SharePoint_contentDB_"
DBnumberEnd = 23
DBnumberStart = 2
SiteURL = "http://intranet.domain.com"
SiteLimit = 50
SiteWarn = 40
Dim ObjFile, objExp
Dim strPath, strFile, strFilePath, strMyFile
' ste the path to use
strPath = "C:\Add_ContentDB_Script\"
strFile = "AddDBAContentDB.bat"
strFilePath = strPath & strFile
' FSO creates the object called - ObjFile
' no validation here = file cannot already exist!!!
Set objFile = CreateObject("Scripting.FileSystemObject")
Set strMyFile = objFile.CreateTextFile(strFilePath, True)
' write the header
strMyFile.WriteLine("cd ""Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN\""")
DBnumberCurrent = DBnumberStart
'start the loop
do while DBnumberCurrent < DBnumberEnd
' .WriteLine to add data to each line in the file
if DBnumberCurrent < 10 then
strmyFile.WriteLine("STSADM -o addcontentdb -url " & SiteURL & " -databasename " & DBnameconv & "0" & DBnumberCurrent & " -databaseserver " & SQLServ & " -sitewarning " & SiteWarn & " -sitemax " & SiteLimit)
strMyFile.WriteLine("STSADM -o addcontentdb -url " & SiteURL & " -databasename " & DBnameconv & DBnumberCurrent & " -databaseserver " & SQLServ & " -sitewarning " & SiteWarn & " -sitemax " & SiteLimit)
End If
DBnumberCurrent = (DBnumberCurrent + 1)

The batch file

The file created can then be run (you'll need to be a farm admin to do this bit)...


You'll then have your 22 content DB's attached to your WebApp. Happy Days!

No comments:

Post a Comment

Please feel free to comment on this post, I want to hear your feedback!