After completing your capacity planning excercise 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 repetative boredom (unless you have a placement student or intern to hand!)...
So, partly because I'm lazy, partly because a hate repetative 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
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!
Posted
03-15-2007 10:14 PM
by
Matt Groves