Category Archives: SQL

Installing SQL Server 2016 Core on Windows Server 2016 Core

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:

image

…not enabled by default – type:

…and check again: image

…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

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:

Install:

..in CMD Shell on server core type:

image

image

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:

per instance:

per listener (if you use alwayson):

older versions SERVER2012R2/SQL2012 (other Profile Parameter and SQL Path):

per server:

per instance:

per listener (if you use alwayson):

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)

image

…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://blog.mscloud.guru/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:

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

Keep users in sync type:

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:

you can check the correct permissions with:

if you have 2 instances (COMMON and SCVMMDB) it should look like this: