Matt Groves Blog: March 2007

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]
GO
DECLARE
@DBName nVARCHAR(255)
, @Number nVARCHAR(4)
, @DataFileName nVARCHAR(255)
, @LogFileName nVARCHAR(255)
, @DataFile nVARCHAR(255)
, @LogFile nVARCHAR(255)
, @SQL nVARCHAR(MAX)
, @ServiceIdentity nVARCHAR(255)
, @AppPoolIdentity nVARCHAR(255)
SELECT
@Number = '02'
/* set these to the correct values for you environment */
, @ServiceIdentity = '<SERVICE Account>'
, @AppPoolIdentity = '<AppPool Account>'
WHILE @Number <= 22
BEGIN
SET @DBName = 'SharePoint_ContentDB_'+@Number
SELECT
@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'
SET @SQL = 'CREATE DATABASE ' + @DBName + '
ON PRIMARY 
(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
, MAXSIZE = UNLIMITED
, FILEGROWTH = 5120000KB)
LOG ON 
(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
, MAXSIZE = UNLIMITED
, FILEGROWTH = 1024000KB)
COLLATE Latin1_General_CI_AS_KS_WS'
EXEC (@SQL)
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+''''
EXEC (@SQL)
SET @SQL = 'USE '+@DBName+';CREATE USER ['+@AppPoolIdentity+'] FOR LOGIN ['+@AppPoolIdentity+']'
EXEC (@SQL)
SET @SQL = 'USE '+@DBName+';EXEC sp_addrolemember ''db_owner'', '''+@AppPoolIdentity+''''
EXEC (@SQL)
SET @Number = @Number + 1
SET @Number = RIGHT('00' + @Number,2)
END

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
SQLServ = "MOSSSQLCLU01"
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)
strMyFile.WriteLine("cd\")
' 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)
Else
strMyFile.WriteLine("STSADM -o addcontentdb -url " & SiteURL & " -databasename " & DBnameconv & DBnumberCurrent & " -databaseserver " & SQLServ & " -sitewarning " & SiteWarn & " -sitemax " & SiteLimit)
End If
DBnumberCurrent = (DBnumberCurrent + 1)
Loop
'done!
strMyFile.Close
WScript.Quit