-
Notifications
You must be signed in to change notification settings - Fork 10
SQL DB Parameters
EdVassie edited this page Jul 20, 2018
·
8 revisions
| Previous FineBuild Parameter Syntax | SQL AS Parameters Next |
|---|
The parameters that are useful for a SQL DB Install are given below. The parameters cover both the basic install and the provisioning that can be performed by SQL FineBuild.
If you are happy that the default value for a given parameter is right for your organisation then you do not need to provide that parameter.
When you run SQL FineBuild you should always provide a /AdminPassword: parameter giving your own password in order to log on automatically after a reboot.
The parameters are divided into the following sections:
- Basic Install
- Account Provisioning
- Always On Provisioning
- Buffer Pool Extension Provisioning
- Cluster Provisioning
- Database Mail Provisioning
- Database Maintenance Provisioning
- Data Quality and Master Data Services Provisioning
- Distributed Replay Provisioning
- Distributed Transaction Coordinator Provisioning
- External Services Provisioning
- Management Data Warehouse Provisioning
- Full Text Search Provisioning
- PolyBase Provisioning
- Replication Provisioning
- SQL Agent Provisioning
- tempdb Provisioning
- Version Upgrade Provisioning
- Special Situations
The table below shows the parameters that are most commonly used with a SQL DB Install
| Parameter | Default | Comment |
|---|---|---|
| /SetupSQLDB: | No | Set to YES to install SQL Database Engine |
| /Edition: | EVAL | Edition of SQL Server to be installed |
| /Instance: | MSSQLSERVER | Name of SQL Instance to install |
| /saPwd: | (none) | Provide your password for the sa account |
| /SecurityMode: | (none) | Security Mode of SQL Server |
| /SqlSvcAccount: | (none) | Name of SQL DB Service Account |
| /SqlSvcPassword: | (none) | Password for SQL DB Service Account |
| /GroupDBA: | (none) | Name of DBA sysadmin group |
| /GroupDBANonSA: | (none) | Name of DBA Non-sysadmin group |
You should also consider the Vol Parameters
The table below shows the parameters that can normally be left at their default values for a SQL DB Install
| Parameter | Default | Comment |
|---|---|---|
| /SetupGovernor: | (none) | Setup Resource Governor |
| /AuditLevel: | 2 | Level of autiding within SQL Server |
| /BrowserSvcAccount: | (none) | Name of SQL Browser Service Account |
| /BrowserSvcPassword: | (none) | Password for SQL Browser Service Account |
| /BrowserSvcStartupType: | Manual | (SQL2008 and above) SQL Browser service startup type |
| /CollationSQL: | Latin1_General_CI_AS | Collation setting for SQL DB Engine |
| /DirSQL: | MSSQL | Folder name to hold SQL Server database files, etc |
| /DisableNetworkProtocols: | 0 | (SQL2005 Only) Code to disable various network protocols |
| /ErrorReporting: | 0 | Automatically report errors back to Microsoft |
| /FileStreamLevel: | 2 | Level of Filestream provisioning |
| /FileStreamShareName: | FS(instance) | Share Name for Filestream |
| /LabBackup: | Backup | Volume Label to be applied to Backup Volume |
| /LabData: | SQL Data | Volume Label to be applied to SQL Data Volume |
| /LabDataFS: | FS Data | Volume Label to be applied to SQL FileStream Volume |
| /LabDataFT: | FT Data | Volume Label to be applied to SQL Full Text Volume |
| /LabLog: | SQL Log | Volume Label to be applied to SQL Log Volume |
| /LabLogTemp: | Temp Log | Volume Label to be applied to SQL tempdb Log Volume |
| /LabPrefix: | (none) | Prefix to be added to all Volume labels |
| /LabSysDB: | SQL SysDB | Volume Label to be applied to SQL System Databases Volume |
| /LabTemp: | SQL Temp | Volume Label to be applied to SQL tempdb data Volume |
| /MaxDop: | (calculated) | Maximum Degree of Parallelism (based on number of Processors) |
| /NPEnabled: | 1 | (SQL2008 and above) Named Pipes Protocol enabled |
| /NumErrorLogs: | 30 | Number of SQL Server Errorlogs to be retained |
| /NumTF: | 20 | Number of Trace Flag (/TFnn:) parameters to search for |
| /PathSQLSP: | ..\Service Packs | Path to Service Packs folder |
| /PID: | (none) | PID for SQL Server installation |
| /RoleDBANonSA: | DBA_NonAdmin | DBA Non-Sysadmin System Role name |
| /SetupParam: | Yes | Configure SQL Server Trace Flag parameters |
| /SetupPBM: | Yes | Setup Policy Based Management |
| /SetupServices: | Yes | Configure service recovery options |
| /SetupSnapshot: | Yes | Configure Snapshot Isolation |
| /SetupSPN: | Yes | Configure Service Principal Names |
| /SetupSQLBC: | Yes | Install SQL Server Backward Compatibility components |
| /SetupSQLDBFS: | Yes | Install SQL Server Filestream components |
| /SetupSQLInst: | Yes | Configure SQL Server Properties options |
| /SetupSQLServer: | Yes | Configure SQL Server Surface Area options |
| /spConfigureCLREnabled: | 1 | Enable CLR |
| /spConfigureCostThreshold: | 20 | Cost Threshold fopr Parallelism |
| /spConfigureMaxServerMemory: | (calculated) | Maximum memory for SQL Server |
| /spConfigureOptimizeForAdHocWorkloads: | 1 | Enable Optimize for Ad-Hoc Workloads |
| /spConfigureRemoteAdminConnections: | 1 | Enable Remote Management of SQL Server |
| /spConfigureRemoteProcTrans: | 0 | Enable MSDTC support for Remote Procedure execution |
| /SQLAutoStart: | 1 | (SQL2005 only) SQL DB service startup type |
| /SQLSvcStartupType: | Automatic | (SQL2008 and above) SQL DB service startup type |
| /SQLBrowserAutoStart: | 0 | (SQL2005 only) SQL Browser service startup type |
| /SQLSysadminAccounts: | (none) | Additional SQL sysadmin Accounts |
| /SQLOperator: | SQL Alerts | Default Operator name for Alert notification |
| /SQLWriterAutoStart: | 0 | (SQL2005 only) SQL VDI Writer service startup type |
| /SPLevel: | (see text) | Desired Service Pack Level. Varies dependent on SQL Version |
| /SPCULevel: | (see text) | Desired Cumulative Update Level. Varies dependent on SQL Version, but not relevant for SQL 2012 and above |
| /SQLProgDir: | Microsoft SQL Server | High-level folder name for SQL Server programs |
| /SQMReporting: | 0 | ??? |
| /TCPEnabled: | 1 | Enable TCP Protocol |
| /TCPPort: | 1433 | Port for SQL Server |
| /TCPPortDAC: | 1434 | Port for SQL Server DAC |
| /TelvcAccount: | (none) | Name of SQL Telemetry Service Account |
| /TelSvcPassword: | (none) | Password for SQL Telemetry Service Account |
| /TelSvcStartupType: | Manual | SQL Telemetry service startup type |
| /TFnn: | (none) | Additional SQL Server Startup Trace Flags (nn is 01 to 20) |
| /WriterSvcStartupType: | Manual | (SQL2008 and above) SQL VDI Writer service startup type |
| Parameter | Default | Comment |
|---|---|---|
| /SetupCmdShell: | No | Setup xp_cmdshell Proxy Account |
| /CmdShellAccount: | (none) | Name of xp_Cmdshell Proxy Account |
| /CmdShellPassword: | (none) | Password for xp_Cmdshell Proxy Account |
| /GroupDBA: | (none) | Name of DBA sysadmin group |
| /GroupDBANonSA: | (none) | Name of DBA non sysadmin group |
| /NTAuthAccount: | Network Service | (Windows 2008 R2 and below) Default Service Account name |
| /NumLogins: | 20 | Number of accounts to search for |
| /saName: | sa | New name of sa account |
| /SetupOldAccounts: | Yes | Remove redundant accounts created during SQL install |
| /SetupSAAccounts: | Yes | Configure sysadmin accounts |
| /SetupSAPassword: | Yes | Configure sa account password |
| /SetupStdAccounts: | Yes | Configure SQL Server standard accounts |
| /SetupStretch: | No | Configure Azure Strtch Database support |
| /SetupSysDB: | Yes | Configure System Database options |
| /SetupSysIndex: | Yes | Configure additional indexes for System Databases |
| /SetupSysManagement: | Yes | Configure system management routines |
| /SetupSystemViews: | Yes | Install System Views chart |
| /SQLSysadminAccounts: | (none) | Additional SQL sysadmin Accounts |
| /UserLoginnn: | (none) | Additional SQL logins (nn is 01 to 20) |
| /UserPasswordnn: | (none) | Passwords for additional SQL logins (nn is 01 to 20) |
| /WinLoginnn: | (none) | Additional Windows logins (nn is 01 to 20) |
This section shows the parameters that can be added to the basic install in order to provision for Always On. These parameters are only valid if you are installing on to a Windows Cluster.
| Parameter | Default | Comment |
|---|---|---|
| /SetupAlwaysOn: | No | Setup Always On |
| /SetupAODB: | No | Add databases to Availability Group |
| /ClusterNameAO: | (calculated) | Name of Always On Cluster |
| /ClusAOIPSuffix: | (calculated) | TCP final octet for AO Cluster address |
| /EncryptAO: | AES | Encryption algorithm for Always On communications |
| /ClusAOSuffix: | AO | Suffix added to Windows Cluster Name to form the Always On Cluster Name |
| /TCPPortAO: | 5022 | Port for Always On communication |
| Parameter | Default | Comment |
|---|---|---|
| /SetupBPE: | No | Setup Buffer Pool Extension |
| /BPEFile: | 100 GB | Size of BPE File |
This section shows the parameters that can be added to the basic install in order to provision a SQL Server Cluster. These parameters are only valid if you are installing on to a Windows Cluster.
Cluster names can either be generated automatically or supplied by you. See Cluster Naming Convention for more details of how this is done.
The table below shows the parameters that are most commonly used with a SQL Server Cluster Install. You should also see Distributed Transaction Coordinator Provisioning
| Parameter | Default | Comment |
|---|---|---|
| /SetupSQLDBCluster: | No | Set to YES to install a SQL Server DB Engine Cluster |
| /SetupAPCluster: | No | Enforce Active/Passive cluster configuration |
The following parameters are mandatory for SQL2005 but not required for more recent versions:
| Parameter | Default | Comment |
|---|---|---|
| /AgtClusterGroup: | (none) | Local Group holding SQL Agent Service Accounts |
| /SQLClusterGroup: | (none) | Local Group holding SQL DB Service Accounts |
The table below shows the parameters that can normally be left at their default values for a SQL Server Cluster Install
| Parameter | Default | Comment |
|---|---|---|
| /AgtDomainGroup: | (none) | (SQL2008 and above) Local Group holding SQL Agent Service Accounts (Optional) |
| /ClusterNameSQL: | (calculated) | Name of SQL DB Cluster |
| /ClusterNameAO: | (calculated) | Name of Always On Cluster |
| /ClusterNameFS: | (calculated) | Name of File Server Cluster |
| /ClusAOIPSuffix: | (calculated) | TCP final octet for AO Cluster address |
| /ClusDBIPSuffix: | (calculated) | TCP final octet for SQL DB Cluster address |
| /ClusterTCP: | IPv4 | Default type of IP address to use for SQL DB Cluster |
| /ClusAOSuffix: | AO | Suffix added to Windows Cluster Name to form the Always On Cluster Name |
| /ClusDBSuffix: | DB | Suffix added to Windows Cluster Name to form the DB Engine Cluster Name |
| /ConfirmIPDependencyChange: | 0 | Prompt for IP Dependency Change for Distributed Clusters |
| /LabDataFT: | FT Data | Volume Label to be applied to SQL Full Text Volume |
| /NetNameSource: | CLUSTER | Source of names to be assigned to Network Adaptors |
| /PreferredOwner: | (none) | Preferred Server to own Cluster Roles |
| /SetupClusterShares: | No | Setup Shares for Cluster Volumes |
| /SQLDomainGroup: | (none) | (SQL2008 and above) Local Group holding SQL DB Service Accounts (Optional) |
This section shows the parameters that can be added to the basic install in order to provision for Database Mail.
| Parameter | Default | Comment |
|---|---|---|
| /SetupDBMail: | Yes | Setup Database Mail |
| /DBMailProfile: | Public DB Mail | Name of default public DB Mail profile |
| /MailServer: | (none) | Mail Server Name or Alias |
| /SQLEmail: | (DBAEmail) | DBMail public Email Address |
| Parameter | Default | Comment |
|---|---|---|
| /SetupDBAManagement: | Yes | Setup basic DBA management routines |
| /SetupDBOpts: | Yes | Setup FineBuild Standard Database Options |
| /SetupGenMaint: | Yes | Setup advanced generic database maintenance |
| /BackupStart: | 21:00:00 | Time Database Full Backups will start |
| /BackupRetain: | 23 | Number of hours a Full or Differential Backup will be retained |
| /BackupLogFreq: | 60 | Number of minutes between Database Log Backups |
| /BackupLogRetain: | 24 | Number of hours a Log Backup will be retained |
| /DBA_DB: | DBA_Data | Database name to hold DBA routines |
| /DBAEmail: | (none) | Email Address for DBA Alert Messages |
| /DBOwner: | DBOwner | Name of default Database Owner account |
| /DirDBA: | DBA Files | Folder name to hold DBA files |
| /JobCategory: | Database Maintenance | Name of Job Category for Database Maintenance jobs |
| /LabDBA: | DBA Misc | Volume Label to be applied to DBA Files Volume |
| Parameter | Default | Comment |
|---|---|---|
| /SetupDQ: | (none) | Setup Data Quality Services |
| /SetupDQC: | (Yes | Setup Data Quality Client |
| /SetupMDS: | (none) | Setup Master Data Services (MDS) |
| /SetupMDSC: | (none) | Install Master Data Services Client |
| /DQPassword: | (saPwd) | Password for Data Quality Services |
| /MDSDB: | MDSData | Name of Master Data Services database |
| /MDSPort: | 5091 | Port for Master Data Services Web Site |
| /MSDSite: | MDS | Name of MDS Web Site |
| /MDSAccount: | (none) | Name of MDS Account |
| /MDSPassword: | (none) | Password for MDS Account |
| Parameter | Default | Comment |
|---|---|---|
| /SetupDRUCLT: | Yes | Setup Distributed Replay Client |
| /SetupDRUCtlr: | (none) | Setup Distributed Replay Controller |
| /CltSvcAccount: | (none) | Name of Distributed Replay Client Service Account |
| /CltSvcPassword: | (none) | Password for Distributed Replay Client Service Account |
| /CltStartupType: | Manual | Distributed Replay Controller Service startup type |
| /CtlrSvcAccount: | (none) | Name of Distributed Replay Controller Service Account |
| /CtlrSvcPassword: | (none) | Password for Distributed Replay Controller Service Account |
| /CtlrStartupType: | Manual | Distributed Replay Controller Service startup type |
Distributed Transaction Coordinator items are normally only relevant for a Cluster Install
| Parameter | Default | Comment |
|---|---|---|
| /SetupDTCCID: | Yes | Setup new CID for MSDTC |
| /SetupDTCCluster: | (none) | Setup MSDTC Cluster |
| /SetupDTCNetAccess: | No | Setup MSDTC Network Access |
| /ClusDTCIPSuffix: | (calculated) | TCP final octet for DTC Cluster address |
| /DTCMultiInstance: | Yes | Create a dedicated MSDTC Cluster for each SQL DB Instance Cluster |
| /TCPPortDTC | 13300 | Port for DTC communication |
| Parameter | Default | Comment |
|---|---|---|
| /SetupAnalytics: | (none) | Setup External Advanced Analytics Server |
| /SetupPython: | (none) | Install Python Services |
| /SetupRServer: | (none) | Install R Services |
| /SQLSharedMR: | Yes | Configure R Services to be shared with SQL Server |
| /ExtSvcAccount: | (none) | Name of External Services Service Account |
| /ExtSvcPassword: | (none) | Password for External Services Service Account |
| Parameter | Default | Comment |
|---|---|---|
| /SetupSQLDBFT: | Yes | Install SQL Server Full Text Search components |
| /SetupFT: | Yes | Configure SQL Server Full Text options |
| /SetupSemantics: | Yes | Install Semantic Search database |
| /VolDataFT: | (VolData) | Volume to host Full Text files |
| Parameter | Default | Comment |
|---|---|---|
| /SetupManagementDW: | (none) | Setup Management Data Warehouse |
| /ManagementDW: | ManagementDW | Database name for Management Data Warehouse |
| /MDWAccount: | (none) | Name of MDW Data Collector Proxy Account |
| /MDWPassword: | (none) | Password for MDW Data Collector Proxy Account |
| Parameter | Default | Comment |
|---|---|---|
| /SetupPolyBase: | (none) | Install PolyBase |
| /PBPortRange: | 16450-16460 | PolyBase Port Range |
| /PBScaleout: | True | Enable Scaleout for PolyBase |
| /PBDMSSvcAccount: | (none) | Name of PolyBase Data Mover Service Account |
| /PBDMSSvcPassword: | (none) | Password for PolyBase Data Mover Service Account |
| /PBDMSSvcStartupType: | Automatic | PolyBase Data Mover Service startup type |
| /PBEngSvcAccount: | (none) | Name of PolyBase Engine Service Account |
| /PBEngSvcPassword: | (none) | Password for PolyBase Engine Service Account |
| /PBEngSvcStartupType: | Automatic | PolyBase Engine Service startup type |
| Parameter | Default | Comment |
|---|---|---|
| /SetupSQLDBRepl: | Yes | Install SQL Server Replication components |
| /SetupDistributor: | No | Setup SL Server Replication Distributor Database |
| /DistributorDatabase: | Distribution | Name of Replication Distribution database |
| /DistributorPassword: | (saPwd) | Password for Replication Distribution |
This section shows the parameters that can be added to the Basic Install in order to provision SQL Agent.
| Parameter | Default | Comment |
|---|---|---|
| /SetupSQLDBAG: | Yes | Install SQL Agent components |
| /SetupSQLAgent: | Yes | Configure SQL Agent Properties options |
| /AgentJobHistory: | 500 | Number of history records to be kept for a given SQL Agent Job |
| /AgentMaxHistory: | 20000 | Maximum number of Job History records to be kept |
| /AGTSvcStartupType: | Automatic | (SQL2008 and above) SQL Agent service startup type |
| /AGTAutoStart: | 1 | (SQL2005 only) SQL Agent service startup type |
| /AgtSvcAccount: | (SqlSvcAccount) | Name of SQL Agent Service Account |
| /AgtSvcPassword: | (SqlSvcPassword) | Password for SQL Agent Service Account |
This section shows the parameters that can be added to the Basic Install in order to provision tempdb.
| Parameter | Default | Comment |
|---|---|---|
| /SetupTempDb: | Yes | Configure tempdb options |
| /SqlTempdbFileCount: | (calculated) | Number of files for tempdb |
| /SqlTempdbFileSize: | 200 MB | Size of tempdb data files |
| /SqlTempdbLogFileSize: | 50 MB | Size of tempdb log file |
| /VolTemp: | (VolData) | Volume to host tempdb data files |
| /VolLogTemp: | (VolLog) | Volume to host tempdb log file |
This section shows the parameters that can be added to the Basic Install in order to provision an upgrade to a new version of SQL Server.
| Parameter | Default | Comment |
|---|---|---|
| /FailoverClusterRollOwnership: | (none) | Automatically Fail Over cluster during SQL Version upgrade |
| /FTUpgradeOption: | (none) | Upgrade Option for Full Text Search |
| /UseSysDB: | (none) | Use existing System Databases in upgrade |
- Non-English Versions Of SQL Server - please refer to the link for details of the parameters you need
Copyright FineBuild Team © 2018. License and Acknowledgements
| Previous FineBuild Parameter Syntax | Top | SQL AS Parameters Next |
|---|
Key SQL FineBuild Links:
SQL FineBuild supports:
- All SQL Server versions from SQL 2019 through to SQL 2005
- Clustered, Non-Clustered and Core implementations of server operating systems
- Availability and Distributed Availability Groups
- 64-bit and (where relevant) 32-bit versions of Windows
The following Windows versions are supported:
- Windows 2022
- Windows 11
- Windows 2019
- Windows 2016
- Windows 10
- Windows 2012 R2
- Windows 8.1
- Windows 2012
- Windows 8
- Windows 2008 R2
- Windows 7
- Windows 2008
- Windows Vista
- Windows 2003
- Windows XP