2023-04-03

SQL Server Setup using Configuration File

This month blog post talks about another overlooked feature of SQL Server. SQL Server Setup can generate a configuration file based upon your inputs during you run the installation GUI. After that you can reuse the generated configuration file to deploy SQL Servers throughout the enterprise with the same configuration. It helps you to standardize installations throughout the enterprise. Here comes a demo showing how to use configuration file to install a stand-alone instance.

Execute sql server setup.exe > Installation > New SQL Server stand-alone installation.

Click Skip and Next buttons until reaches Installation Type > Perform a new installation.

In this demo, I'm using SQL Server 2019 Developer edition, but no matter which edition you are going to install (except express), you can still generate and use configuration file by following the same procedures stated here.

Enter the product key if you have, accept the license terms. Then in Feature Selection page, choose the features you needed. In this demo, I only chose Database Engine Services and SQL Server Replication.
Change the directories to install as you wish, for example, in D:\ drive.

Choose Default Instance or enter the Named Instance.

For performance boost, I enabled Grant Perform Volume Maintenance Task privilege which essentially enabled Database Instant File Initialization (IFI). Then I customized the server collation to SQL_Latin1_General_CP1_CI_AS.

In Database Engine Configuration page, I added current user as sysadmin, tempdb 2 data files, MAXDOP 2, and accepting recommended max server memory.

In the Ready to Install page, copy the Configuration File Path, then Click the Cancel button. 

You can open the generated .ini config file in notepad, to see the configuration values you just entered.
For example:
FEATURES=SQLENGINE,REPLICATION
INSTANCENAME="INSTANCE1"
INSTANCEDIR="D:\Program Files\Microsoft SQL Server"
SQLMAXDOP="2"
SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
SQLSVCINSTANTFILEINIT="True"
SQLSYSADMINACCOUNTS="DESKTOP-LJND0A5\Monkey"
SQLTEMPDBFILECOUNT="2"
USESQLRECOMMENDEDMEMORYLIMITS="True"

Now close the installer program. Open a DOS command prompt, go to the sql server installer media directory, execute the sql server SETUP.EXE with config file specified, e.g.
SETUP.EXE /ConfigurationFile="C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log\20230404_020323\ConfigurationFile.ini"


You still need to traverse through the installer GUI, accept the license terms, but this time you can see the setup already made the selections that you defined in the last time generating the config file.

Setup configuration file is not as powerful as powershell scripts such as dbatools.io, but it's a quick and easy way for MSSQL DBA to standardize installation, especially DBA likes me that not so familiar with scripting.

No comments:

Post a Comment