Pre-create SharePoint 2010 databases in SQL Server 2008 using PowerShell

There are some scenarios where it is a good idea to create databases in SQL Server prior to installing SharePoint. Typically, this is where a DBA may want control over the database naming convention and configuration in advance of letting you loose on the install DVD!

There are other benefits with doing this, too. For example, you can set the database and log file location on the file system, configure recovery mode, pre-grow, autogrowth and maximum database sizes in advance, and other Microsoft recommendations listed in the Storage and SQL Server capacity planning and configuration article on TechNet.

The example here lists all the databases and their configuration settings in a CSV file. We then use a PowerShell script to import this data and use the Invoke-Sqlcmd cmdlet to run a .sql file that creates the databases, configures the properties set in the CSV file, and assigns the SharePoint install account database owner permissions.

Before doing this, I recommend that you look at a couple of articles on TechNet in addition to the one listed above. First, the Deploy by using DBA-created databases article, which covers the process of installing SharePoint 2010 on pre-created databases (note that you cannot pre-create the Search service application databases using the method shown in this article), and also the “Adding the SQL Server Snap-ins to Windows PowerShell” section of the Running SQL Server PowerShell article, which goes through how to add the SQL Server Provider to the regular PowerShell or PowerShell ISE consoles – you will need to do this before running the PowerShell script shown in this article. Don’t forget that you will also need to login as an account with the correct permissions in SQL to be able to create the databases and set the permissions required.

CSV File

I am showing an example CSV file below that includes all columns needed for the script used in this article, but of course, you can add or remove any columns required for your deployment:

DBNAME,SETUPUSER,DATAFILEPATH,DATASIZE,DATAMAXSIZE,DATAFILEGROWTH,LOGFILEPATH,LOGSIZE,LOGFILEGROWTH,RECOVERYMODE
SP_Test1,DOMAIN\SP_Admin,C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLINSTANCE\MSSQL\DATA,1024000KB,1024000KB,102400KB,C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLINSTANCE\MSSQL\LOGS,1024KB,10%,SIMPLE
SP_Test2,DOMAIN\SP_Admin,C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLINSTANCE\MSSQL\DATA,512000KB,UNLIMITED,20%,C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLINSTANCE\MSSQL\LOGS,1024KB,10%,FULL

Admittedly, it doesn’t look the easiest to read here, but if you copy the contents to a CSV file using Notepad it will look a lot clearer. Note that I am not specifying SharePoint databases in the CSV file – I’ll leave that one to you considering your naming conventions and database configuration will vary anyway.

SQL File

Next, we need a .sql file that will be used by the PowerShell script to create the databases. This file was created using the “Script Action” option in the SQL Management Studio console after creating a single database and assigning dbo permissions to the SharePoint install account.

I then saved the contents of the script as a “Create SharePoint Database.sql” file and replaced the values configured in the console with variables – e.g., replacing the database name with $(DBNAME). The PowerShell script shown later will replace these variables with values from the CSV file:

/* Create SharePoint database */
CREATE DATABASE [$(DBNAME)] ON  PRIMARY
( NAME = N'$(DBNAME)', FILENAME = N'$(DATAFILEPATH)\$(DBNAME).mdf' , SIZE = $(DATASIZE) , MAXSIZE = $(DATAMAXSIZE) , FILEGROWTH = $(DATAFILEGROWTH) )
LOG ON
( NAME = N'$(DBNAME)_log', FILENAME = N'$(LOGFILEPATH)\$(DBNAME)_log.ldf' , SIZE = $(LOGSIZE) , FILEGROWTH = $(LOGFILEGROWTH))
COLLATE Latin1_General_CI_AS_KS_WS
GO
ALTER DATABASE [$(DBNAME)] SET COMPATIBILITY_LEVEL = 100
GO
ALTER DATABASE [$(DBNAME)] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [$(DBNAME)] SET ANSI_NULLS OFF
GO
ALTER DATABASE [$(DBNAME)] SET ANSI_PADDING OFF
GO
ALTER DATABASE [$(DBNAME)] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [$(DBNAME)] SET ARITHABORT OFF
GO
ALTER DATABASE [$(DBNAME)] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [$(DBNAME)] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [$(DBNAME)] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [$(DBNAME)] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [$(DBNAME)] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [$(DBNAME)] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [$(DBNAME)] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [$(DBNAME)] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [$(DBNAME)] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [$(DBNAME)] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [$(DBNAME)] SET  DISABLE_BROKER
GO
ALTER DATABASE [$(DBNAME)] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [$(DBNAME)] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [$(DBNAME)] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [$(DBNAME)] SET  READ_WRITE
GO
ALTER DATABASE [$(DBNAME)] SET RECOVERY $(RECOVERYMODE)
GO
ALTER DATABASE [$(DBNAME)] SET  MULTI_USER
GO
ALTER DATABASE [$(DBNAME)] SET PAGE_VERIFY CHECKSUM 
GO
USE [$(DBNAME)]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [$(DBNAME)] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

/* Add SharePoint account as the database owner */
USE [$(DBNAME)]
GO
CREATE USER [$(SETUPUSER)] FOR LOGIN [$(SETUPUSER)]
GO
USE [$(DBNAME)]
GO
EXEC sp_addrolemember N'db_owner', N'$(SETUPUSER)'
GO

PowerShell Script

Finally, we need the PowerShell script. This walks through each line in the CSV file, reads the columns, and uses the Invoke-Sqlcmd cmdlet to run the .sql file - replacing the variables with the CSV column data:

$csvData = @(Import-Csv -Path C:\Scripts\SQL\Databases.csv)
foreach ($line in $csvData)
{
    $parameters = @()
    $line | Get-Member -MemberType NoteProperty | ForEach-Object {
       
        $parameters = $parameters + ($_.Name + "=" + $line.($_.Name))
    }   
    Invoke-Sqlcmd -InputFile "C:\Scripts\Create SharePoint Database.sql" -ServerInstance "SQLINSTANCE" -Variable $parameters
}

If you are using this method to pre-create SharePoint databases, don’t forget to apply the other recommendations listed in the Deploy by using DBA-created databases article.

You can also use the method of replacing variables shown in the PowerShell script above to manage any repeatable action in SQL – not just database creation. For example, you could keep a .sql file handy that adds an AD account as a SQL login and then use the Invoke-Sqlcmd command to add the SharePoint install account to SQL before creating any databases:

SQL File:

USE [master]
GO
CREATE LOGIN [$(USER)] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[British]
GO
EXEC master..sp_addsrvrolemember @loginame = N'$(USER)', @rolename = N'dbcreator'
GO
EXEC master..sp_addsrvrolemember @loginame = N'$(USER)', @rolename = N'securityadmin'
GO

PowerShell script:

Invoke-Sqlcmd -InputFile "C:\Scripts\Add Account to Logins.sql" -ServerInstance "SQLINSTANCE" -Variable "USER=DOMAIN\SP_Admin"