Home » Automate SQL Server installation using PowerShell

Automate SQL Server installation using PowerShell

by Vlad Drumea
0 comment

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

ParameterDescription
-InstanceNameShould be the name of the instance in case of a named instance. Leave empty if -IsDefault is used.
-saPwdMandatory. The password that will be set for the sa account during installation.
-IsDefaultOptional. Switch. If -IsDefault is used then a default instance will be installed.
-InstanceRootDirOptional. The parent directory where the instance’s main directory will be created. Defaults to D:\MSSQL if not provided
-BackupRootDirOptional. The parent directory where the instance’s database backup files will be stored. Defaults to the value of InstanceRootDir if not provided.
-UserDataRootDirOptional. The parent directory where the instance’s user database data files will be stored. Defaults to the value of InstanceRootDir if not provided.
-UserTLogRootDirOptional. The parent directory where the instance’s user database tlog files will be stored. Defaults to the value of UserDataRootDir if not provided.
-TempdbDataRootDirOptional. The parent directory where the instance’s Tempdb data files will be stored. Defaults to the value of InstanceRootDir if not provided.
-TempdbTLogRootDirOptional. The parent directory where the instance’s Tempdb tlog files will be stored. Defaults to the value of TempdbDataRootDir if not provided.
-InstanceCollationOptional. The collation that the instance should use. Defaults to SQL_Latin1_General_CP1_CI_AS if not provided.
-StaticPortOptional. The static TCP port that should be configured for the instance.
-AddFirewallRulesOptional. Switch. Adds inbound firewall rules for the TCP ports used by SQL Server and the SQL Server Browser service.
-InstallSSMSOptional. Switch. If used, the script will check if SSMS 19 is installed and will install it if it’s not.
-AutoMaxMemoryOptional. 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.
-MaxMemoryMBOptional. 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.
-DontPatchOptional. Switch. When used, the script skips applying the CU patch even if the installation kit is in the CUInstallKit directory.
-CustomScriptOptional. Used to provide the path to a custom .sql script that does some extra post-install configuration.
-AutoRebootOptional. 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
File Explorer showing the usual contents of the SQL Server installation kit directory, with w additional folders highlighted: CUInstallKit and SSMSInstallKIt As well as the 2019_DefaultInstTemplate.ini and 2019_NamedInstTemplate.ini config files and the InstallSQLServer.ps1 script. automate sql server installation powershell

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.

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.


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

Windows powerShell window showing the progress of the above installation command automate sql server installation powershell

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:

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

You may also like

Leave a Comment

* By using this form you agree with the storage and handling of your data by this website.

This site uses Akismet to reduce spam. Learn how your comment data is processed.