In this post I cover a script I’ve been using to automate SQL Server installation with PowerShell in my home lab.
As opposed to my previous blog post that demos the creation of a SQL Server Developer edition container, this installs a full-fledged SQL Server instance.
Why SQL Server Developer edition?
Because it’s provided for free by Microsoft for testing and development purposes and, it has 1:1 feature parity with SQL Server Enterprise edition, and it’s what I’ve been using in my home lab ever since it was announced back in 2016.
If you want to learn more about this, Bob Ward has a blog post that covers this in-depth and answers some questions that people tend to have about Developer Edition.
Note that even if I wrote this script with Developer edition in mind, there’s no reason for it not to be useful in production environments, especially since it’s fairly flexible with the option of running additional .sql files post-install.
Options that aren’t covered within the script, such as using AD service accounts, can be handled from the config files.
Backstory
Since I’ve always had various reasons for spinning up VMs with SQL Server Developer edition instances at home for learning, testing and demoing stuff, I’ve wanted to make the installation process as smooth as possible while also maintaining a specific standard in terms of configuration.
SQL Server 2017 made things a lot simpler by including the tempdb configuration step in the install process via both GUI and config.ini file, so that’s the moment where I’ve figured I’d look into using PowerShell to automate my instance installations.
You might be wondering “why not use dbatools’ Install-DbaInstance?” – the reason is I didn’t know about it back then.
Plus it ended up being one of my first steps into working on automating with PowerShell.
The script
You can find the script and the required config.ini templates in the SQLServerInstall folder of my SQL Server Scripts GitHub repository.
What it does
The script does the following:
- Installs SQL Server 2017 or 2019 or 2022.
- Writes the configuration file used to C:\Temp
- Sets the sa password to the one provided.
- Adds the user executing this script as a member of the sysadmin fixed server role.
- Configures MAXDOP based on the number of CPU cores on the machine (up to 8).
- Configures the number of tempdb data files based on the number of CPU cores on the machine (up to 8).
- Sets Max Memory for the instance depending on the input or by calculating it based on the installed physical memory.
- Tweaks model database file sizes and growth increments.
- Sets the model database to use the simple recovery model.
- Sets CTP to 50.
- Runs any custom .sql script file provided via the -CustomScript parameter.
- Installs CU pack, if there’s any present in the CUInstallKit directory.
- Configures a static TCP port if one is provided.
- Adds inbound firewall rules for SQL Server, if requested.
- Installs SSMS if requested and if SSMS 19 or above is not already installed.
- Reboots the machine.
Parameters and switches
| Parameter | Description |
|---|---|
-InstanceName | Should be the name of the instance in case of a named instance. Leave empty if -IsDefault is used. |
-saPwd | Mandatory. The password that will be set for the sa account during installation. |
-IsDefault | Optional. Switch. If -IsDefault is used then a default instance will be installed. |
-InstanceRootDir | Optional. The parent directory where the instance’s main directory will be created. Defaults to D:\MSSQL if not provided |
-BackupRootDir | Optional. The parent directory where the instance’s database backup files will be stored. Defaults to the value of InstanceRootDir if not provided. |
-UserDataRootDir | Optional. The parent directory where the instance’s user database data files will be stored. Defaults to the value of InstanceRootDir if not provided. |
-UserTLogRootDir | Optional. The parent directory where the instance’s user database tlog files will be stored. Defaults to the value of UserDataRootDir if not provided. |
-TempdbDataRootDir | Optional. The parent directory where the instance’s Tempdb data files will be stored. Defaults to the value of InstanceRootDir if not provided. |
-TempdbTLogRootDir | Optional. The parent directory where the instance’s Tempdb tlog files will be stored. Defaults to the value of TempdbDataRootDir if not provided. |
-InstanceCollation | Optional. The collation that the instance should use. Defaults to SQL_Latin1_General_CP1_CI_AS if not provided. |
-StaticPort | Optional. The static TCP port that should be configured for the instance. |
-AddFirewallRules | Optional. Switch. Adds inbound firewall rules for the TCP ports used by SQL Server and the SQL Server Browser service. |
-InstallSSMS | Optional. Switch. If used, the script will check if SSMS 19 is installed and will install it if it’s not. |
-AutoMaxMemory | Optional. Switch. If used, the script will calculate Max Memory based on the installed physical memory. Leaving the greater of 4GB or 10% to the OS. |
-MaxMemoryMB | Optional. The value in MB that the instance’s Max Memory parameter should be set to. If neither -AutoMaxMemory nor -MaxMemoryMB are used, then the script will default to 4GB. |
-DontPatch | Optional. Switch. When used, the script skips applying the CU patch even if the installation kit is in the CUInstallKit directory. |
-CustomScript | Optional. Used to provide the path to a custom .sql script that does some extra post-install configuration. |
-AutoReboot | Optional. Switch. When used, the script skips prompting to confirm the reboot and just reboots the machine. |
Requirements
Script location
The script and the appropriate configuration template files for the version you’re trying to install need to be in the same directory as the. installation kit’s setup.exe.
Config.ini templates
The script comes with 6 configuration template files, 2 for each version, one for default instance and one for named instance installations.
These need to be copied in the installation kit directory while respecting the versions. For example, in the SQL Server 2019 kit you should have the two 2019 configuration files.
Directory structure
Since I keep my installation kits on my NAS and copy them from there to my VMs, I maintain a directory structure that the InstallSQLServer.ps1 script relies on.
The following two directories also need to be in the installation kit directory:
- CUInstallKit – the destination for Cumulative Update installation kits. You can have as many as you want here, the script will always install the latest one.
The CU kits should follow the standard SQLServer[YYYY]-KB[KBNumber]-x64.exe naming convention. - SSMSInstallKit – the destination for the SQL Server Management Studio installation kit.
The SSMS kit should respect the SSMS-Setup*.exe naming convention
automate sql server installation powershell
SQL Server installation using PowerShell
The execution is fairly simple, just open PowerShell as administrator and run the script.
Examples
Default instance with basic config
The following command installs a default (not named) instance of SQL Server, 2017 in this case, sets Max Memory to 2GB, applies the CU available, and installs SSMS.
| 1 | .\InstallSQLServer.ps1 -IsDefault -saPwd S3cureP@ass -MaxMemoryMB 2048 -InstallSSMS |
During the installation of SQL Server and subsequent CU pack, you’ll see the progress bar.
automate sql server installation powershell
Named instance with static port and firewall rules
In this case the instance will be a named one (SQL2022_RO) using the Romanian_CI_AS collation.
1455 will be set as the static TCP port and firewall rules will be added for both SQL Server and SQl Server Browser.
SSMS installation check will be skipped.
| 1 2 3 | .\InstallSQLServer.ps1 SQL2022_RO -saPwd S3cureP@ass -AutoMaxMemory ` -StaticPort 1455 -AddFirewallRules ` -InstanceCollation Romanian_CI_AS |
Unpatched named instance with additional script executed and the instance root directory on the C drive
This will install a named instance (SQL2019_CS) using a case-sensitive collation, set to listen on TCP port 1457.
Allow inbound firewall rules will be created, CU won’t be installed, the instance root directory will be on the C drive (don’t do this outside of test/home lab environments) an additional .sql script will be executed before the VM is auto restarted
| 1 2 3 4 5 | .\InstallSQLServer.ps1 SQL2019_CS -saPwd S3cureP@ass -StaticPort 1456 ` -AddFirewallRules -AutoMaxMemory -DontPatch ` -InstanceCollation SQL_Latin1_General_CP1_CS_AS ` -InstanceRootDir "C:\AlsoMSSQL" -InstallSSMS ` -CustomScript C:\Temp\ExtraConfig.sql -AutoReboot |
The ExtraConfig.sql file just consists of some basic configuration changes because I didn’t want to flood the terminal with output from database restores and data loads that I generally run as part of the test environment builds.
I like to use the SQL Server version as part of the instance name, but it has no bearing on the script deciding which version is being installed.
That’s actually controlled via the configuration file names.
Have files on different drives
The following command installs a named instance, SQL2022_01, applies the CU, auto configures memory, uses SQL_Latin1_General_CP1_CS_AS collation, has the system databases and user database data files on drive D, tempdb files and user database tlog files on drive E, and backups on drive F:
| 1 2 3 | .\InstallSQLServer.ps1 SQL2022_01 -saPwd SuperStr0ngPassword ` -AutoMaxMemory -InstanceRootDir D:\ -UserDataRootDir D:\ ` -UserTLogRootDir E:\ -TempdbDataRootDir E:\ -BackupRootDir F:\ |
In this example, the database files will be located as follows:
- system databases will be located in D:\SQL2022_01\MSSQL16.SQL2022_01\MSSQL\DATA
- user database data files will be located in D:\SQL2022_01\Data
- tempdb data files will be in E:\SQL2022_01\TempDB
- user database and tempdb tlog files will be in E:\SQL2022_01\TLog
- default backup location will be F:\SQL2022_01\Backup
Conclusion
This is my preferred way of spinning up SQl Server Developer edition instances in my home lab.
At this point, building a new Windows VM and sniping up one or more instances on it complete with test databases takes a few minutes and 95% of the work is done by PowerShell.
I’ll also use containers for lighter testing from now on, but anything that needs a more “production-like” environment will still be VM-based.
automate sql server installation powershell
2 comments
How easy is it to incorporate SSRS into the script?
SSRS is a completely separate installer with its own installation and configuration process.
So, pretty difficult and unrelated. At least from my point of view as a person who rarely interacts with SSRS.