Prerequisites:
Account(s):
as a best practice you should create two (domain) accounts for running the sql service and the sql agent:
Login |
Description |
_svc_sqldb |
SQL Server Service Account – is NOT local Admin on SQL Servers |
_svc_sqlagent |
SQL Server Agent Account – is NOT local Admin on SQL Servers |
…in addition i create a global domain and local domain group in AD for SQL Admins – put Members to the global domain group, put global domain group into local domain group (local group gets the permission for sql server –> see sql.ini file…)
Name |
Description |
_gg_SQLAdmins |
Global Group – All SQL Administrators |
_lg_SQLAdmins |
Local Group – All Glocal SQL Admin Groups |
Disk:
best practice for SQL servers is to put Data, Log and Temp Files in different harddrives (in physical word in different raid configs) – i prefer this also in virtual environments even if i have a config that puts all vDisks on the same physical drives – per SQL VM create 4 vDisks:
Filename |
Description |
SystemOS.vhdx |
Boot Disk with OS |
SQL-Data.vhdx |
Shared SQL Components and Instance Dirs |
SQL-Temp.vhdx |
Drive for SQL Temp DB's |
SQL-Log.vhdx |
Drive for SQL Log's |
(see sql.ini for configuring the different drive letters and paths…)
if you want to manage your harddrives remotely by mmc plugin – you have to enable the appropriate firewall-rules on server core AND your workstation machine (if you not enable on your workstation machine you will get the error “RPC server unavailable):
check if the rules for remote disk management are enabled:
|
Get-NetFirewallRule | Where { $_.DisplayGroup -Eq "Remote Volume Management" } | Format-Table |

…not enabled by default – type:
|
Enable-NetFirewallRule -DisplayGroup "Remote Volume Management" |
…and check again: 
…if you prefer the GUI – Server Manager from Admin Workstation is great – you can use it to manage disks remotely (change disk label,…)
Hotfixes, CUs, SPs and Patches:
..while setup is running, it can implement existing updates – copy all updates in a directory named i.e C:\SQLSetup\Updates\… and refer this path in your SQL.ini
|
mkdir C:\SQLSetup\Updates copy <sqlupdates> C:\SQLSetup\Updates |
i.e. for SQL Server 2016 RTM – download CU2: https://www.microsoft.com/en-us/download/details.aspx?id=53338
SQL.INI:
…if you do not created a ini file before, you can copy this sample sql.ini and edit for your own:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128
|
;SQL Server 2012/2014/2016 Configuration File [OPTIONS] ; Required to acknowledge acceptance of the license terms. IACCEPTSQLSERVERLICENSETERMS="True" ; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter. ACTION="Install" ; Detailed help for command line argument ENU has not been defined yet. ENU="True" ; Parameter that controls the user interface behavior. Valid values are Normal for the full UI,AutoAdvance for a simplied UI, and EnableUIOnServerCore for bypassing Server Core setup GUI block. ; UIMODE="EnableUIOnServerCore" --> cannot be used with Quietsimple Parameter... ; Setup will not display any user interface. QUIET="False" ; Setup will display progress only, without any user interaction. QUIETSIMPLE="True" ; Specify whether SQL Server Setup should discover and include product updates. The valid values are True and False or 1 and 0. By default SQL Server Setup will include updates that are found. UpdateEnabled="True" ; Specify whether SQL Server setup should discover and include product updates. The valid values are True and False or 1 and 0. By default, SQL Server setup will include updates that are found. UpdateSource="C:\SQLSetup\Updates" ; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, MDS, and Tools. The SQL feature will install the Database Engine, Replication, Full-Text, and Data Quality Services (DQS) server. The Tools feature will install Management Tools, Books online components, SQL Server Data Tools, and other shared components. FEATURES=SQLENGINE ; Displays the command line parameters usage HELP="False" ; Specifies that the detailed Setup log should be piped to the console. INDICATEPROGRESS="False" ; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system. X86="False" ; Specify the root installation directory for shared components. This directory remains unchanged after shared components are already installed. INSTALLSHAREDDIR="D:\Program Files\Microsoft SQL Server" ; Specify the root installation directory for the WOW64 shared components. This directory remains unchanged after WOW64 shared components are already installed. INSTALLSHAREDWOWDIR="D:\Program Files (x86)\Microsoft SQL Server" ; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions. This parameter is required when installing the SQL Server Database Engine (SQL), Analysis Services (AS), or Reporting Services (RS). INSTANCENAME="SCVMMDB" ; Specify the Instance ID for the SQL Server features you have specified. SQL Server directory structure, registry structure, and service names will incorporate the instance ID of the SQL Server instance. INSTANCEID="SCVMMDB" ; Specify that SQL Server feature usage data can be collected and sent to Microsoft. Specify 1 or True to enable and 0 or False to disable this feature. SQMREPORTING="False" ; Specify if errors can be reported to Microsoft to improve future SQL Server releases. Specify 1 or True to enable and 0 or False to disable this feature. ERRORREPORTING="False" ; Specify the installation directory. INSTANCEDIR="D:\Program Files\Microsoft SQL Server" ; Specifies the directory for the data files for tempdb. SQLTEMPDBDIR="F:\MSSQLTempDB\SCVMMDB" ; Specifies the directory for the log files for tempdb. SQLTEMPDBLOGDIR="E:\MSSQLTempDBLog\SCVMMDB" ; Specifies the directory for the data files for user databases. SQLUSERDBDIR="D:\MSSQLData\SCVMMDB" ; Specifies the directory for the log files for user databases. SQLUSERDBLOGDIR="E:\MSSQLLogs\SCVMMDB" ; Agent account name AGTSVCACCOUNT="DOMAIN\_svc_sqlagent" AGTSVCPASSWORD=”Password” AGTSVCSTARTUPTYPE="Automatic" ; Level to enable FILESTREAM feature at (0, 1, 2 or 3). FILESTREAMLEVEL="0" ; Account for SQL Server service: Domain\User or system account. SQLSVCACCOUNT="DOMAIN\_svc_sqldb" SQLSVCPASSWORD=”Password” SQLSVCSTARTUPTYPE="Automatic" ; Specifies a Windows collation or an SQL collation to use for the Database Engine. SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS" ; Startup type for Browser Service. BROWSERSVCSTARTUPTYPE=Automatic ; Use this parameter to provision logins to be members of the sysadmin role. SQLSYSADMINACCOUNTS="CORP\_lg_SQLAdmins" ; Specifies the directory for backup files. SQLBACKUPDIR="E:\Backup" |
Install:
..in CMD Shell on server core type:
|
cd C:\SQLSetup .\setup.exe /ConfigurationFile=C:\SQLSetup\Install_Instance_SCVMMDB.ini |


HINT: see troubleshooting section for a bug in the setup routine – you have to add permissions on your backup dir, if you install additional instances…
after setup is finish – you have to manually create the firewall rule for accessing your instance:
SERVER2016/SQL2016:
per server:
|
New-NetFirewallRule –Name 'SQL-Server-Browser' –DisplayName 'SQL Server Browser (UDP-In 1434)' –Direction Inbound –Action Allow –Protocol UDP –LocalPort 1434 –Profile Any |
per instance:
|
New-NetFirewallRule –Name 'SQL-Server-SCVMMDB-Instance' -DisplayName 'SQL Server (TCP-In) SCVMMDB Instance' -Program 'D:\Program Files\Microsoft SQL Server\MSSQL13.SCVMMDB\MSSQL\Binn\sqlservr.exe' -Direction Inbound -Action Allow -Protocol TCP -Profile Any |
per listener (if you use alwayson):
|
New-NetFirewallRule -DisplayName "SQL Listener SCVMMDB Instance (TCP-In <Listener Port ie. 1436>)" -Direction Inbound -Action Allow -LocalAddress <ListenerIP> -Protocol TCP -LocalPort <Listener Port ie. 1436> -Profile Domain |
older versions SERVER2012R2/SQL2012 (other Profile Parameter and SQL Path):
per server:
|
New-NetFirewallRule -DisplayName 'SQL Server Browser (UDP-In 1434)' -Direction Inbound -Action Allow -Protocol UDP -LocalPort 1434 -Profile All |
per instance:
|
New-NetFirewallRule -DisplayName 'SQL Server (TCP-In) SCVMMDB Instance' -Program 'D:\Program Files\Microsoft SQL Server\MSSQL11.SCVMMDB\MSSQL\Binn\sqlservr.exe' -Direction Inbound -Action Allow -Protocol TCP -Profile All |
per listener (if you use alwayson):
|
New-NetFirewallRule -DisplayName "SQL Listener SCVMMDB Instance (TCP-In <Listener Port ie. 1436>)" -Direction Inbound -Action Allow -LocalAddress <ListenerIP> -Protocol TCP -LocalPort <Listener Port ie. 1436> -Profile Domain |
you can check if everything is ok with management studio – connect to the sql server\instance name and version number should be 13.0.2164 (SQL2016 with CU2) – see version numbers: https://sqlserverbuilds.blogspot.co.at/
AlwaysOn config:
…if you want to create a AlwaysOn SQL Infra – do exact the same on a second server (don’t forget to create cluster first…)
Enable AlwaysOn:
Open PowerShell with Admin Rights (if you have a fresh install and not reopened your powershell window – no SQL cmdlet will be found (!) – so don´t forget to logoff and logon before start PS)
|
Enable-SqlAlwaysOn –Path SQLSERVER:\SQL\<Name of Server>\<Name of Instance> |

…do this on ALL sql nodes…
HINT – AlwaysOn: if you create a AvailabilityGroup and want to use it for i.e. SystemCenter VMM – see: http://mscloudgurublog.azurewebsites.net/2016/10/30/installing-highly-available-systemcenter-vmm-2016-howto/ don´t forget that AlwaysOn does NOT sync user logins on SQL automatically – so if you install VMM every works perfect until the first SQL Failover – after that VMM services crashes, because it can not connect to your database.
Good way to keep the user´s in sync, is a great tool named dbatools – it´s free of charge and you can find it via: https://dbatools.io/getting-started/
Installation is very simple via PSGallery on your SQL server – open powershell and type:
|
Install-Module -Name dbatools |
..aswer the following questions about NuGet and so on with Yes (you need a internet connection from your server..)
Test the connection to the server you logged in and the other sql server nodes that part of your Availability Group with:
|
#Syntax: Test-SqlConnection -SqlServer <fqdn of server\Name of Instance> #Example: Test-SqlConnection -SqlServer SQL1\SCVMMDB |
Keep users in sync type:
|
# Syntax: Sync-SqlLoginPermissions -Source <fqdn\instancename> -Destination <fqdn\instancename> -Verbose # Example: Sync-SqlLoginPermissions -Source SQL1\SCVMMDB -Destination SQL2\SCVMMDB -Verbose |
i will do this in a scheduled task so have a perfect solution to keep all sql user logins on all sql servers in sync.
Troubleshooting:
you can find any error or information in the SQL Setup log file located in C:\Program Files\MicrosoftSQL Server\130\Setup Bootstrap\Log\ – see the reference article: https://msdn.microsoft.com/en-us/library/ms143702.aspx
HINT: …i found a bug in the sql setup – if you install the instances with a unattended .ini file and point every instance to backup their databases to ie. E:\backup directory – the setup process create only for the FIRST instance the appropriate permissions for this directory (ie: NT Service\MSSQL$COMMON has Full permission to E:\Backup NOT other instances ie. NT Service\MSSQL$SCVMMDB…..) – to solve this open a Admin CMD Shell on every SQL node and enter:
|
icacls E:\Backup /GRANT "NT SERVICE\MSSQL$SCVMMDB:(CI)(OI)F" |
you can check the correct permissions with:
if you have 2 instances (COMMON and SCVMMDB) it should look like this:
