| Author | Reitse Eskens (@2meterDBA), Kirill Kravtsov (@nvarscar) |
| Availability | Windows, Linux, macOS |
Want to see the source code for this command? Check out Install-DbaInstance on GitHub.
Want to see the Bill Of Health for this command? Check out Install-DbaInstance.
This function will help you to quickly install a SQL Server instance.
This function will help you to quickly install a SQL Server instance on one or many computers.
Some of the things this function will do for you:
Fully customizable installation parameters allow you to:
Note that the downloaded installation media must be extracted and available to the server where the installation runs.
NOTE: If no ProductID (PID) is found in the configuration files/parameters, Evaluation version is going to be installed.
When using CredSSP authentication, this function will try to configure CredSSP authentication for PowerShell Remoting sessions.
If this is not desired (e.g.: CredSSP authentication is managed externally, or is already configured appropriately,)
it can be disabled by setting the dbatools configuration option 'commands.initialize-credssp.bypass' value to $true.
To be able to configure CredSSP, the command needs to be run in an elevated PowerShell session.
Install-DbaInstance
[[-SqlInstance] <DbaInstanceParameter[]>]
[-Version] <String>
[[-InstanceName] <String>]
[[-SaCredential] <PSCredential>]
[[-Credential] <PSCredential>]
[[-Authentication] <String>]
[[-ConfigurationFile] <Object>]
[[-Configuration] <Hashtable>]
[[-Path] <String[]>]
[[-Feature] <String[]>]
[[-AuthenticationMode] <String>]
[[-InstancePath] <String>]
[[-DataPath] <String>]
[[-LogPath] <String>]
[[-TempPath] <String>]
[[-BackupPath] <String>]
[[-UpdateSourcePath] <String>]
[[-AdminAccount] <String[]>]
[[-Port] <Int32>]
[[-Throttle] <Int32>]
[[-ProductID] <String>]
[[-AsCollation] <String>]
[[-SqlCollation] <String>]
[[-EngineCredential] <PSCredential>]
[[-AgentCredential] <PSCredential>]
[[-ASCredential] <PSCredential>]
[[-ISCredential] <PSCredential>]
[[-RSCredential] <PSCredential>]
[[-FTCredential] <PSCredential>]
[[-PBEngineCredential] <PSCredential>]
[[-SaveConfiguration] <String>]
[-PerformVolumeMaintenanceTasks]
[-Restart]
[-NoPendingRenameCheck]
[-EnableException]
[-WhatIf]
[-Confirm]
[<CommonParameters>]
PS C:\> Install-DbaInstance -Version 2017 -Feature All
Install a default SQL Server instance and run the installation enabling all features with default settings. Automatically generates configuration.ini
PS C:\> Install-DbaInstance -SqlInstance sql2017\sqlexpress, server01 -Version 2017 -Feature Default
Install a named SQL Server instance named sqlexpress on sql2017, and a default instance on server01. Automatically generates configuration.ini.
Default features will be installed.
PS C:\> Install-DbaInstance -Version 2008R2 -SqlInstance sql2017 -ConfigurationFile C:\temp\configuration.ini
Install a default named SQL Server instance on the remote machine, sql2017 and use the local configuration.ini
PS C:\> Install-DbaInstance -Version 2017 -InstancePath G:\SQLServer -UpdateSourcePath '\\my\updates'
Run the installation locally with default settings apart from the application volume, this will be redirected to G:\SQLServer.
The installation procedure would search for SQL Server updates in \my\updates and slipstream them into the installation.
PS C:\> $svcAcc = Get-Credential MyDomain\SvcSqlServer
PS C:\> Install-DbaInstance -Version 2016 -InstancePath D:\Root -DataPath E: -LogPath L: -PerformVolumeMaintenanceTasks -EngineCredential $svcAcc
Install SQL Server 2016 instance into D:\Root drive, set default data folder as E: and default logs folder as L:.
Perform volume maintenance tasks permission is granted. MyDomain\SvcSqlServer is used as a service account for SqlServer.
PS C:\> $svcAcc = [PSCredential]::new("MyDomain\SvcSqlServer$", [SecureString]::new())
PS C:\> Install-DbaInstance -Version 2016 -InstancePath D:\Root -DataPath E: -LogPath L: -PerformVolumeMaintenanceTasks -EngineCredential $svcAcc
The same as the last example except MyDomain\SvcSqlServer is now a Managed Service Account (MSA).
PS C:\> $config = @{
>> AGTSVCSTARTUPTYPE = "Manual"
>> BROWSERSVCSTARTUPTYPE = "Manual"
>> FILESTREAMLEVEL = 1
>> }
PS C:\> Install-DbaInstance -SqlInstance localhost\v2017:1337 -Version 2017 -SqlCollation Latin1_General_CI_AS -Configuration $config
Run the installation locally with default settings overriding the value of specific configuration items.
Instance name will be defined as 'v2017'; TCP port will be changed to 1337 after installation.
SQL Server version you wish to install.
This is the year version (e.g. "2008R2", "2017", "2019", "2022")
| Alias | |
| Required | True |
| Pipeline | false |
| Default Value | |
| Accepted Values | 2008,2008R2,2012,2014,2016,2017,2019,2022 |
The target computer and, optionally, a new instance name and a port number.
Use one of the following generic formats:
Server1
Server2\Instance1
Server1\Alpha:1533, Server2\Omega:1566
"ServerName\NewInstanceName,1534"
You can also define instance name and port using -InstanceName and -Port parameters.
| Alias | ComputerName |
| Required | False |
| Pipeline | false |
| Default Value | $env:COMPUTERNAME |
Name of the SQL Server instance to install. Overrides the instance name specified in -SqlInstance.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Securely provide the password for the sa account when using mixed mode authentication.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Windows Credential with permission to log on to the remote server.
Must be specified for any remote connection if SQL Server installation media is located on a network folder.
Authentication will default to CredSSP if -Credential is used.
For CredSSP see also additional information in DESCRIPTION.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Chooses an authentication protocol for remote connections.
Allowed values: 'Default', 'Basic', 'Negotiate', 'NegotiateWithImplicitCredential', 'Credssp', 'Digest', 'Kerberos'.
If the protocol fails to establish a connection and explicit -Credentials were used, a failback authentication method would be attempted that configures PSSessionConfiguration
on the remote machine. This method, however, is considered insecure and would, therefore, prompt an additional confirmation when used.
Defaults:
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | @('Credssp', 'Default')[$null -eq $Credential] |
| Accepted Values | Default,Basic,Negotiate,NegotiateWithImplicitCredential,Credssp,Digest,Kerberos |
The path to the custom Configuration.ini file.
| Alias | FilePath |
| Required | False |
| Pipeline | true (ByValue) |
| Default Value |
A hashtable with custom configuration items that you want to use during the installation.
Overrides all other parameters.
For example, to define a custom server collation you can use the following parameter:
PS> Install-DbaInstance -Version 2017 -Configuration @
As long as you don't specify the item ACTION, some items are already set by the command, like SQLSYSADMINACCOUNTS or *SVCSTARTUPTYPE.
If you specify the item ACTION, only INSTANCENAME and FEATURES are set based on the corresponding parameters and QUIET is set to True.
You will have to set all other needed items for your specific ACTION.
But this way it is possible to use the command so install a Failover Cluster Instance or even to remove a SQL Server instance.
More information about how to install a Failover Cluster Instance can be found here: https://github.com/dataplat/dbatools/discussions/7447
Full list of parameters can be found here: https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt#Install
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Path to the folder(s) with SQL Server installation media downloaded. It will be scanned recursively for a corresponding setup.exe.
Path should be available from the remote server.
If a setup.exe file is missing in the repository, the installation will fail.
Consider setting the following configuration in your session if you want to omit this parameter: Set-DbatoolsConfig -Name Path.SQLServerSetup -Value '\\path\to\installations'
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | (Get-DbatoolsConfigValue -Name 'Path.SQLServerSetup') |
Features to install. Templates like "Default" and "All" can be used to setup a predefined set of components. Full list of features:
Default: Engine, Replication, FullText, Tools
All
Engine
Tools: SSMS, BackwardsCompatibility, Connectivity
Replication
FullText
DataQuality
PolyBase
MachineLearning
AnalysisServices
ReportingServices
ReportingForSharepoint
SharepointAddin
IntegrationServices
MasterDataServices
PythonPackages
RPackages
BackwardsCompatibility
Connectivity
ReplayController
ReplayClient
SDK
BIDS
SSMS: SSMS, ADV_SSMS
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | Default |
| Accepted Values | Default,All,Engine,Tools,Replication,FullText,DataQuality,PolyBase,MachineLearning,AnalysisServices,ReportingServices,ReportingForSharepoint,SharepointAddin,IntegrationServices,MasterDataServices,PythonPackages,RPackages,BackwardsCompatibility,Connectivity,ReplayController,ReplayClient,SDK,BIDS,SSMS |
Chooses authentication mode for SQL Server. Allowed values: Mixed, Windows.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | Windows |
| Accepted Values | Windows,Mixed |
Root folder for instance components. Includes SQL Server logs, system databases, etc.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Path to the Data folder.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Path to the Log folder.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Path to the TempDB folder.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Path to the Backup folder.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Path to the updates that you want to slipstream into the installation.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
One or more members of the sysadmin group. Uses UserName from the -Credential parameter if specified, or current Windows user by default.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
After successful installation, changes SQL Server TCP port to this value. Overrides the port specified in -SqlInstance.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
Maximum number of computers updated in parallel. Once reached, the update operations will queue up.
Default: 50
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 50 |
Product ID, or simply, serial number of your SQL Server installation, which will determine which version to install.
If the PID is already built into the installation media, can be ignored.
| Alias | PID |
| Required | False |
| Pipeline | false |
| Default Value |
Collation for the Analysis Service.
Default value: Latin1_General_CI_AS
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Collation for the Database Engine.
The default depends on the Windows locale:
https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support#Server-level-collations
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Service account of the SQL Server Database Engine
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Service account of the SQL Server Agent
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Service account of the Analysis Services
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Service account of the Integration Services
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Service account of the Reporting Services
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Service account of the Full-Text catalog service
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Service account of the PolyBase service
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Save installation configuration file in a custom location. Will not be preserved otherwise.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Allow SQL Server service account to perform Volume Maintenance tasks.
| Alias | InstantFileInitialization,IFI |
| Required | False |
| Pipeline | false |
| Default Value | False |
Restart computer automatically if a restart is required before or after the installation.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Disables pending rename validation when checking for a pending reboot.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | (Get-DbatoolsConfigValue -Name 'OS.PendingRename' -Fallback $false) |
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Shows what would happen if the command were to run. No actions are actually performed.
| Alias | wi |
| Required | False |
| Pipeline | false |
| Default Value |
Prompts you for confirmation before executing any changing operations within the command.
| Alias | cf |
| Required | False |
| Pipeline | false |
| Default Value |