Standardising new database creation using Powershell and SMO

Personally, I think that standardisation of a SQL Server is an important part of the DBA role; it should start by ensuring that instances are standardised, with the same naming convention and set of configurations added, and go all the way through to coding practices and object naming convention. It’s important not to miss off database creation from this list.

When you create a new database, if you do not specify any options SQL Server will use the Model database as a template, which is a form of standardisation in itself. Many SQL Server instances only have the default model db as provided at install, which means that your standards include: a PRIMARY file set to grow in 1MB chunks with no upper limit and a log file set to 1MB with 10 percent growth. I can think of very few real world databases that would use these settings, so one of the first things you should do after install is tweak the model database to something that fits with your requirements.

However, even if you have configured the model database to meet your needs, there are still a few caveats:-

  • You can’t add user filegroups to the model database
  • The database owner will always be set to the login that the creation script is run under

The filegroup issue is a particular concern for me, generally speaking I like to keep the database meta data stored in it’s own files so that during a partial restore the database can be brought online quickly, and then seperate user data out into secondary files and filegroups as appropriate.

Likewise, the database owner being set by default to the login creating the database can cause problems down the line; people leave organisations leaving databases owned by a user who has left, and cross database permissions when using chaining/trustworthy can be troublesome.

In order to address these problems, I decided to create a powershell function to utilise smo in creating a new database. This means that the entire CREATE DATABASE script can be whittled down to a single powershell cmdlet and some variables that can be passed in. The benefits of this also include a standardised naming convention for databases objects such as files, filegroups and logs, and also mean that we can standardise other options such as ensuring that percentage autogrowth isn’t used and ensure that we always create a filegroup for user data and set that to be the default.

The function accepts the following parameters:-

  • $TargetServer – the target SQL instance on which to create the db.
  • $DatabaseName – the name of the new database.
  • $NumberOfFileInUserFilegroup – the number of files that will be created in the user filegroup; these will all be created with the same options.
  • $UseDefaultFileLocation – defaults to true; when true will pull the default file locations from the server, when false will look at the following two parameters for file locations.
  • $NonDefaultFileLocation – optional, specifies the location to create database files.
  • $NonDefaultLogLocation – optional, specifies the location to create the log file.
  • $Collation – optional, if not set the server collation setting will be used
  • $RecoveryModel – optional, if not set the Model db recovery model will be used.
  • $DatabaseOwner – defaults to “sa”. Note that the smo does not seem to accept logins that are not specifically a login principal on the server; e.g. a login that accesses through group membership cannot be set.
  • $UserDataFileSize – The size, in MB, of each file in the user filegroup.
  • $UserDataFileMaxSize – The maximum size, in MB, that each file in the user filegroup can grow to.
  • $UserDataFileGrowth – The amount, in MB, that each file will grow by; set to 0 if no growth is required.
  • $LogSize – The size, in MB, of the transaction log.
  • $LogGrowth – The amount, in MB, that the log will grow by. Recommend setting this the same as the Log size in order to maintain VLF size parity.
  • $PrimaryFileSize – Defaults to 10MB; the size, in MB, of the PRIMARY data file for database meta data.
  • $PrimaryFileGrowth – Defaults to 10MB; the amount, in MB, that the PRIMARY data file will grow by.
  • $PrimaryFileMaxSize – Defaults to 100MB; the maximum size, in MB, that the PRIMARY data file will grow to.

The script performs a few sanity checks, making sure that the database doesn’t already exist, and ensuring that if we are not using the default file locations that the $NonDefaultFileLocation and $NonDefaultLogLocation parameters are populated (whilst I’m writing this, I’m thinking I should also add a check that those directories exist…. *adds to the “to do” list*).

The way in which the database class of the smo works, is to create the object that we want to create and then create it on the database server. So, we create our object as a variable with the following:-

$NewDB = New-Object Microsoft.SqlServer.Management.Smo.Database($SQLServer, $DatabaseName)

Once this object is created, we can add the relevant components that we want to create by creating objects for them as new variables, and assigning that object to the new database object. This is shown in the following section to first create an object for the PRIMARY file group and then add it to the database object:-

$PrimaryFG = new-object Microsoft.SqlServer.Management.Smo.Filegroup($NewDB, "PRIMARY")
    $NewDB.Filegroups.Add($PrimaryFG)

For the user filegroup, as we may want to create more than a single file, we run the code to create the file object and add it to the filegroup in a loop; we can reuse the same file object because once it has been added to the filegroup that file will be part of our new database object:-

#add the required number of files to the filegroup in a loop
    #set the filecounter
    $FileCounter = 1

    #open a loop while the filecounter is less than the required number of files
    While ($FileCounter -le $NumberOfFilesInUserFilegroup)
    {
        #Set the file name
        $UserFileName = $UserFileGroupName + "_" + [string]$FileCounter
        $Message = "Creating file name $UserFileName in filegroup $UserFileGroupName"
        Write-Verbose $Message
        #create the smo object for the file
        $UserFile = new-object Microsoft.SQLServer.Management.Smo.Datafile($UserFG, $UserFileName)
        $UserFile.FileName = $LocalDataDrive + "\" + $USerFileName + ".ndf"
        $UserFile.Size = ($UserDataFileSize * 1024)
        $UserFile.GrowthType = "KB"
        $UserFile.Growth = ($UserDataFileGrowth * 1024)
        $UserFile.MaxSize = ($USerDataFileMaxSize * 1024)
        #add the file to the filegroup
        $UserFG.Files.Add($UserFile)
        #increment the file counter
        $FileCounter = $FileCounter + 1
    }

We then set the collation and recovery model if specified; however we can’t change the database owner or set the user file group to the default until after we have created our database (at this point it’s still just a powershell object). So, to actually create the database on our server, we run the following:-

$NewDb.Create()

Our database is now created; but if we looked at it at this point, the small PRIMARY filegroup we created would still be the default, and the database owner would still be the login that we’re using to run our powershell under. So, the final couple of commands can be run to set the database owner and set the user filegroup as the default:-

#Set the owner
    $Message = "Setting database owner to $DatabaseOwner"
    Write-Verbose $Message
    $NewDB.SetOwner($DatabaseOwner)

    #set the user filegroup to be the default
    $Message = "Setting default filegroup to $UserFileGroupName"
    Write-Verbose $Message
    $NewDB.SetDefaultFileGroup($UserFileGroupName)

In the function, I’ve also added the cmdletbinding() call to allow the standard parameters such as -verbose to be used; when running the cmdlet, you can get comfort messages using the -verbose switch which will result in all the write-verbose output being displayed. Otherwise, the only output we will get will either be any errors (such as if the database already exists) or a success message.

With this function, the entire database creation script can be reduced to a single line of powershell code. If I wanted to create a new database called “MyNewDatabase”, with 4 user files each sized 256MB (so total 1GB) and a max size of 4GB, I would run the following:-

New-SQLDatabase -TargetServer "MySQLServer" -DatabaseName "MyNewDatabase" -NumberOfFilesInUserFilegroup 4 -UserDataFileSize 256 -UserDataFileMaxSize 1024 -UserDataFileGrowth 256 -LogSize 256 -LogGrowth 256

The full function code is below with the export-module cmd added so you can save as a psm1 file and load via your powershell profile using the import-module cmdlet.

function New-SQLDatabase
{
    [cmdletbinding()]
    Param
    (
    # set variables for the database
            [string]$TargetServer                      #server name to create the db
          , [string]$DatabaseName                      #database name
          , [double]$NumberOfFilesInUserFilegroup      #number of files in the user filegroup

    # optional db variables
          , [boolean]$UseDefaultFileLocations = $true
          , [string]$NonDefaultFileLocation
          , [string]$NonDefaultLogLocation
          , [string]$Collation
          , [string]$RecoveryModel
          , [string]$DatabaseOwner = "sa"

    #set the user data size, maxsize and growth
          , [double]$UserDataFileSize
          , [double]$USerDataFileMaxSize
          , [double]$UserDataFileGrowth #use 0 for no growth

    #set the log size and growth
          , [double]$LogSize
          , [double]$LogGrowth

    #set the primary file size in MB (will be converted to kb later)
          , [double]$PrimaryFileSize = 10
          , [double]$PrimaryFileGrowth = 10
          , [double]$PrimaryFileMaxSize = 100
    )

    #set the error action to stop on any errors
    $ErrorActionPreference = "Stop"

    #Verbose message to show the server
    $Message = "Instantiating smo object for server $TargetServer"
    Write-Verbose $Message

    #instantiate the sql server object
    $SQLServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $TargetServer

    # if we are using the default file locations, get them from the server
    if ($UseDefaultFileLocations -eq $true)
    {
            #get the default file locations; if the master is in the default location we use that path as the default file will not be set
        $LocalDataDrive = if ($SQLServer.DefaultFile -eq [DBNULL]::value)
                                    {$SQLServer.MasterDBPath}
                                else
                                    {$SQLServer.DefaultFile}
        $LocalLogDrive = $SQLServer.DefaultLog
    }
    elseif ($UseDefaultFileLocations -eq $false -and ($NonDefaultFileLocation -eq $Null -or $NonDefaultLogLocation -eq $Null))
    {
        Write-Error "Non Default file locations selected, but are not supplied"
    }

    #output message in verbose mode
    $Message = "Setting local Data drive to $LocalDataDrive and local log drive to $LocalLogDrive"
    write-verbose $Message

    #check to see if the database already exists.
    if ($SQLServer.Databases[$DatabaseName].Name -ne $Null)
    {
        Write-Error "Database $DatabaseName already exists on $TargetServer"
    }

    #create the new db object
    $Message = "Creating smo object for new database $DatabaseName"
    Write-Verbose $Message
    $NewDB = New-Object Microsoft.SqlServer.Management.Smo.Database($SQLServer, $DatabaseName)

    #add the primary filegroup and a primary file
    $Message = "Creating PRIMARY filegroup"
    Write-Verbose $Message

    $PrimaryFG = new-object Microsoft.SqlServer.Management.Smo.Filegroup($NewDB, "PRIMARY")
    $NewDB.Filegroups.Add($PrimaryFG)

    #add the primary file
    $PrimaryFileName = $DatabaseName + "_PRIMARY"
    $Message = "Creating file name $PrimaryFileName in filegroup PRIMARY"
    Write-Verbose $Message
    #create the filegroup object
    $PrimaryFile = new-object Microsoft.SqlServer.Management.Smo.DataFile($PrimaryFG, $PrimaryFileName)
    $PrimaryFile.FileName = $LocalDataDrive + "\" + $PrimaryFileName + ".mdf"
    $PrimaryFile.Size = ($PrimaryFileSize * 1024)
    $PrimaryFile.GrowthType = "KB"
    $PrimaryFile.Growth = ($PrimaryFileGrowth * 1024)
    $PrimaryFile.MaxSize = ($PrimaryFileMaxSize * 1024)
    $PrimaryFile.IsPrimaryFile = "true"
    #add the file to the filegroup
    $PrimaryFG.Files.Add($PrimaryFile)

    #add the user data file group
    $UserFilegroupName = $DatabaseName + "_MainData"
    $Message = "Creating user filegroup $UserFileGroupName"
    Write-Verbose $Message

    $UserFG = new-object Microsoft.SqlServer.Management.Smo.Filegroup($NewDB, $UserFilegroupName)
    $NewDB.Filegroups.Add($UserFG)

    #add the required number of files to the filegroup in a loop
    #set the filecounter
    $FileCounter = 1

    #open a loop while the filecounter is less than the required number of files
    While ($FileCounter -le $NumberOfFilesInUserFilegroup)
    {
        #Set the file name
        $UserFileName = $UserFileGroupName + "_" + [string]$FileCounter
        $Message = "Creating file name $UserFileName in filegroup $UserFileGroupName"
        Write-Verbose $Message
        #create the smo object for the file
        $UserFile = new-object Microsoft.SQLServer.Management.Smo.Datafile($UserFG, $UserFileName)
        $UserFile.FileName = $LocalDataDrive + "\" + $USerFileName + ".ndf"
        $UserFile.Size = ($UserDataFileSize * 1024)
        $UserFile.GrowthType = "KB"
        $UserFile.Growth = ($UserDataFileGrowth * 1024)
        $UserFile.MaxSize = ($USerDataFileMaxSize * 1024)
        #add the file to the filegroup
        $UserFG.Files.Add($UserFile)
        #increment the file counter
        $FileCounter = $FileCounter + 1
    }

    #now create the log file
    $LogName = $DatabaseName + "_Log"
    $Message = "Creating log $LogName"
    Write-Verbose $Message
    #add the log to the db
    $TLog = new-object Microsoft.SqlServer.Management.Smo.LogFile($NewDB, $LogName)
    $TLog.FileName = $LocalLogDrive + "\" + $LogName + ".ldf"
    $TLog.Size = ($LogSize * 1024)
    $TLog.GrowthType = "KB"
    $TLog.Growth = ($LogGrowth * 1024)
    #add the log to the db
    $NewDB.LogFiles.Add($TLog)

    #set database settings; collation, owner, recovery model

    #set the collation
    if ($Collation.Length -eq 0)
    {
        $Message = "USing default server collation"
        Write-Verbose $Message
    }
    else
    {
        $Message = "Setting collation to $Collation"
        Write-Verbose $Message
        $NewDB.Collation = $Collation
    }

    #set the recovery model
    if ($RecoveryModel.Length -eq 0)
    {
        $Message = "Using default recovery model from the Model database"
        Write-Verbose $Message
    }
    else
    {
        $Message = "Setting recovery model to $RecoveryMode"
        Write-Verbose $Message
        $NewDB.RecoveryModel = $RecoveryModel
    }

    #we should now be able to create the db, and run any other config settings afterwards
    $Message = "Creating Datbase $DatabaseName"
    Write-Verbose $Message
    $NewDb.Create()

    #now do post db creation work, set the dbowner and set the default filegroup
    #Set the owner
    $Message = "Setting database owner to $DatabaseOwner"
    Write-Verbose $Message
    $NewDB.SetOwner($DatabaseOwner)

    #set the user filegroup to be the default
    $Message = "Setting default filegroup to $UserFileGroupName"
    Write-Verbose $Message
    $NewDB.SetDefaultFileGroup($UserFileGroupName)

    #Write completed message
    $Message = "Completed creating database $DatabaseName"
    Write-Output $Message

    #reset the error action
    $ErrorActionPreference = "Continue"
}

Export-ModuleMember -Function New-SQLDatabase
Advertisements

3 thoughts on “Standardising new database creation using Powershell and SMO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s