Creating New Database — Standard Settings

Recently I have been able to work with our development teams to not create new databases. There are several reasons for me making this request of the teams.  The number one problem is that they were deployed with default settings and owners which I am not a fan.  These things kill my OCD, but also is not best for performance.

I know you are saying why not just do this in TSQL using SSMS.  Well there is a good reason for me.  I try to do all things using the PowerShell commands that I can to allow for learning and experimentation.  So, I compiled this PowerShell script using splatting and dbatools to make this process quick ,easy and repeatable when a ticket hits my queue to create a new database.

My first response is “do you know what your workload will be like or do you know the volume of data to expect”?  This is not something that comes back with a valid response 90% of the time.  My starting size values are small, but can be adjusted based on information gathered from the requester.  There are more advanced settings that can be added if the needs arise. (I.e. multiple file groups, etc.)

Settings:

• Name = Database Name requester wants
• DataFIlePath = Value gathered from the SQL Instance
• LogFilePath = Value gathered from the SQL Instance
• PrimaryFileSize and Growth = 1 GB each
• LogSize and Growth = 512 MB each
• RecoverModel = SIMPLE is used for all Development and Test environments then reviewed for Production setting
• Owner = sa for all databases unless a good reason is given

$instance = "localhostsql2017"
$settings = Connect-DbaInstance -sqlInstance $instance
$params = @{
    SqlInstance       = "$($instance)"
    Name              = "NewDatabase"
    DataFilePath      = "$($settings.DefaultFile)"
    LogFilePath       = "$($settings.DefaultLog)"
    PrimaryFilesize   = 1024
    PrimaryFileGrowth = 1024
    LogSize           = 512
    LogGrowth         = 512
    Recoverymodel     = "SIMPLE"
    Owner             = "sa"
}
New-DbaDatabase @params

After running the PowerShell script the NewDatabase has been created on the localhostSQL2017 instance.  Now if you look at the database settings you will have a database with the settings you want applied instead of default non-preferred settings.

I understand there can be other settings configured and could be more complex, but this is an easy starting point for a simple use database.