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]GODECLARE@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 <= 22BEGINSET @DBName = 'SharePoint_ContentDB_'+@NumberSELECT@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=90EXEC [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 + 1SET @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 SQLServDim DBnameconvDim DBnumberEndDim DBnumberStartDim SiteURLDim SiteLimit, SiteWarn'set the parameters used hereSQLServ = "MOSSSQLCLU01"DBnameconv = "SharePoint_contentDB_"DBnumberEnd = 23DBnumberStart = 2SiteURL = "http://intranet.domain.com"SiteLimit = 50SiteWarn = 40Dim ObjFile, objExpDim strPath, strFile, strFilePath, strMyFile' ste the path to usestrPath = "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 headerstrMyFile.WriteLine("cd ""Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN\""")DBnumberCurrent = DBnumberStart'start the loopdo while DBnumberCurrent < DBnumberEnd' .WriteLine to add data to each line in the fileif DBnumberCurrent < 10 thenstrmyFile.WriteLine("STSADM -o addcontentdb -url " & SiteURL & " -databasename " & DBnameconv & "0" & DBnumberCurrent & " -databaseserver " & SQLServ & " -sitewarning " & SiteWarn & " -sitemax " & SiteLimit)ElsestrMyFile.WriteLine("STSADM -o addcontentdb -url " & SiteURL & " -databasename " & DBnameconv & DBnumberCurrent & " -databaseserver " & SQLServ & " -sitewarning " & SiteWarn & " -sitemax " & SiteLimit)End IfDBnumberCurrent = (DBnumberCurrent + 1)Loop'done!strMyFile.CloseWScript.Quit
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!