commands

^

Install-DbaInstance

Author Reitse Eskens (@2meterDBA), Kirill Kravtsov (@nvarscar)
Availability Windows only

 

Synopsis

This function will help you to quickly install a SQL Server instance.

Description

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:

  • Add your login as an admin to the new instance
  • Search for SQL Server installations in the specified file repository
  • Generate SA password if needed
  • Install specific features using 'Default' and 'All' templates or cherry-pick the ones you need
  • Set number of tempdb files based on number of cores (SQL2016+)
  • Activate .Net 3.5 feature for SQL2012/2014
  • Restart the machine if needed after the installation is done

Fully customizable installation parameters allow you to:

  • Use existing Configuration.ini files for the installation
  • Define service account credentials using native Powershell syntax
  • Override any configurations by using -Configuration switch
  • Change the TCP port after the installation is done
  • Enable 'Perform volume maintenance tasks' for the SQL Server account

Note that the dowloaded 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 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.

Syntax

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>]
    [[-EngineCredential] <PSCredential>]
    [[-AgentCredential] <PSCredential>]
    [[-ASCredential] <PSCredential>]
    [[-ISCredential] <PSCredential>]
    [[-RSCredential] <PSCredential>]
    [[-FTCredential] <PSCredential>]
    [[-PBEngineCredential] <PSCredential>]
    [[-SaveConfiguration] <String>]
    [-PerformVolumeMaintenanceTasks]
    [-Restart]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

 

Examples

 

Example: 1
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

Example: 2
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.

Example: 3
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

Example: 4
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.

Example: 5
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.

Example: 6
PS C:\> $config = @{
>> AGTSVCSTARTUPTYPE = "Manual"
>> SQLCOLLATION = "Latin1_General_CI_AS"
>> BROWSERSVCSTARTUPTYPE = "Manual"
>> FILESTREAMLEVEL = 1
>> }
PS C:\> Install-DbaInstance -SqlInstance localhost\v2017:1337 -Version 2017 -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.

Required Parameters

-Version

SQL Server version you wish to install. This is the year version (e.g. "2008R2", "2017", "2019")

Alias
Required True
Pipeline false
Default Value
Accepted Values 2008,2008R2,2012,2014,2016,2017,2019

Optional Parameters

-SqlInstance

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
-InstanceName

Name of the SQL Server instance to install. Overrides the instance name specified in -SqlInstance.

Alias
Required False
Pipeline false
Default Value
-SaCredential

Securely provide the password for the sa account when using mixed mode authentication.

Alias
Required False
Pipeline false
Default Value
-Credential

Used when executing installs against remote servers

Alias
Required False
Pipeline false
Default Value
-Authentication

Chooses an authentication protocol for remote connections. Allowed values: 'Default', 'Basic', 'Negotiate', 'NegotiateWithImplicitCredential', 'Credssp', 'Digest', 'Kerberos' If the protocol fails to establish a connection Defaults:

  • CredSSP when -Credential is specified - due to the fact that repository Path is usually a network share and credentials need to be passed to the remote host to avoid the double-hop issue.
  • Default when -Credential is not specified. Will likely fail if a network path is specified.
Alias
Required False
Pipeline false
Default Value Credssp
Accepted Values Default,Basic,Negotiate,NegotiateWithImplicitCredential,Credssp,Digest,Kerberos
-ConfigurationFile

The path to the custom Configuration.ini file.

Alias FilePath
Required False
Pipeline true (ByValue)
Default Value
-Configuration

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 @ 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

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')
-Feature

Features to install. Templates like "Default" and "All" can be used to setup a predefined set of components. Full list of features: Default All Engine Tools Replication FullText DataQuality PolyBase MachineLearning AnalysisServices ReportingServices ReportingForSharepoint SharepointAddin IntegrationServices MasterDataServices PythonPackages RPackages ReplayController ReplayClient SDK BIDS 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,ReplayController,ReplayClient,SDK,BIDS,SSMS
-AuthenticationMode

Chooses authentication mode for SQL Server. Allowed values: Mixed, Windows.

Alias
Required False
Pipeline false
Default Value Windows
Accepted Values Windows,Mixed
-InstancePath

Root folder for instance components. Includes SQL Server logs, system databases, etc.

Alias
Required False
Pipeline false
Default Value
-DataPath

Path to the Data folder.

Alias
Required False
Pipeline false
Default Value
-LogPath

Path to the Log folder.

Alias
Required False
Pipeline false
Default Value
-TempPath

Path to the TempDB folder.

Alias
Required False
Pipeline false
Default Value
-BackupPath

Path to the Backup folder.

Alias
Required False
Pipeline false
Default Value
-UpdateSourcePath

Path to the updates that you want to slipstream into the installation.

Alias
Required False
Pipeline false
Default Value
-AdminAccount

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
-Port

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
-Throttle

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
-ProductID

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
-EngineCredential

Service account of the SQL Server Database Engine

Alias
Required False
Pipeline false
Default Value
-AgentCredential

Service account of the SQL Server Agent

Alias
Required False
Pipeline false
Default Value
-ASCredential

Service account of the Analysis Services

Alias
Required False
Pipeline false
Default Value
-ISCredential

Service account of the Integration Services

Alias
Required False
Pipeline false
Default Value
-RSCredential

Service account of the Reporting Services

Alias
Required False
Pipeline false
Default Value
-FTCredential

Service account of the Full-Text catalog service

Alias
Required False
Pipeline false
Default Value
-PBEngineCredential

Service account of the PolyBase service

Alias
Required False
Pipeline false
Default Value
-SaveConfiguration

Save installation configuration file in a custom location. Will not be preserved otherwise.

Alias
Required False
Pipeline false
Default Value
-PerformVolumeMaintenanceTasks

Allow SQL Server service account to perform Volume Maintenance tasks.

Alias
Required False
Pipeline false
Default Value False
-Restart

Restart computer automatically if a restart is required before or after the installation.

Alias
Required False
Pipeline false
Default Value False
-EnableException

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
-WhatIf

Shows what would happen if the command were to run. No actions are actually performed.

Alias wi
Required False
Pipeline false
Default Value
-Confirm

Prompts you for confirmation before executing any changing operations within the command.

Alias cf
Required False
Pipeline false
Default Value

 

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.