Managing SQL Server on Server Core How to

Managing SQL Server on Server Core How to survive without a GUI 1

Sponsors

Managing SQL Server on Server Core About Me Frank Henninger ● ● ● 1 10+ years as a DBA ○ Oracle on Linux ○ IBM DB 2 LUW on AIX ○ SQL Server on Windows Contributor for dbatools and dbachecks Contact info ○ Email : frank@osirismedia. biz ○ SQLCommunity Slack: @frank 687 ○ Twitter : @Osiris 687

Managing SQL Server on Server Core The Many Versions of Windows has many flavors 2 Nano Server Core Server Desktop Extremely stripped down version of Windows that is only supported for usage in containers (e. g. Docker) Windows without Windows. No desktop or RDP access by default Stock Windows Server. Full Desktop Experience.

Why Use Server Core? Managing SQL Server on Server Core ● Light weight ○ Smaller footprint ○ Less resource usage ○ More servers can be virtualized on the same hardware $$$ ● Security ○ With fewer items installed, the range of possible attacks is shrunk ○ End user security ● Scripting ● Containerization 3

Managing SQL Server on Server Core Things to consider How do we adapt? Supported Features Not all features are supported Patching Manual or automatic Configuration Oh! The tough part! 4 Installation No GUI, No Problem Remote Management Tools Who needs to login anyway? Server Core Basics Be nice to your sysadmin. Admin Shares, ISO Mounting, Policies

Supported Features 2012 2014 2016 Managing SQL Server on Server Core 2017 Database Engine �� �� Replication �� �� Full Text Search �� �� Analysis Services �� �� �� R Services (In-Database) Integration Services �� �� Client Tools Connectivity �� �� SSMS Distributed Replay Client 5 Not Supported: SSRS, Client Tools Backwards Compatibility, Client Tools SDK, Distributed Replay Controller, Master Data Services, Data Quality Services

Server Core Basics ● How to access the system ○ Console ○ RDP ( if enabled ) ○ Powershell ● Configuration ○ SConfig ○ Powershell ● Copying Files ○ Shares ● Managing Services ○ Computer Management ○ Powershell 6 Managing SQL Server on Server Core ● Policy Management ○ Export/Import ● What still works ○ Notepad ○ msinfo 32 ○ regedit ○ timedate. cpl ○ taskmgr ○ Most Sys. Internals tools ( Process Manager, etc )

Managing SQL Server on Server Core Installation No GUI, No Problem ● Installation must be via a configuration file ● Configuration files can be created manually or by running the installer on another machine and saving the generated file. ● If generated via installer, you must update the generated config to enable the /QUIET switch 7

Patching No GUI, No Problem, Part Duex ● Installing patch files require command line switches ○ ● ■ /q : Quiet ■ /IAccept. SQLServer. License. Terms : Avoid license dialog ■ /Action=patch : What activity to take ■ /All. Instances : Update all instances No output will appear on the console. Activity will be recorded in the setup logs on the server ○ 8 <patchfile>. exe /q /IAccept. SQLServer. License. Terms /Action=Patch /All. Instances For 2016, C: Program FilesMicrosoft SQL Server130Setup Bootstrap Managing SQL Server on Server Core

Managing SQL Server on Server Core Configuration Oh! The Tough Part! Most configuration is done remotely anyway. Common Configuration Tools ● SQL Server Management Studio ○ Works as before ● SQL Ops Studio ○ Works as before ● Computer Management ○ Use “Connect to Another Computer” ● SQL Server Configuration Manager ○ Has issues… ■ Requires a desktop ■ Does not provide “Connect to Another Computer” ○ However, it does provide a snap-in for Computer Management ○ Must be installed on a management machine with desktop ○ Requires only Client Tools Connectivity feature be installed 9

Managing SQL Server on Server Core Configuration Powershell is your friend # Firewall Rules New-Net. Firewall. Rule -Display. Name "SQL Server" ` -Direction Inbound ` -Program "D: Program FilesMicrosoft SQL ServerMSSQL 13. MSSQLSERVERMSSQLBinnsqlservr. exe" ` -Remote. Address Any ` -Action Allow New-Net. Firewall. Rule -Display. Name "SQL Server Browser & DAC" ` -Direction Inbound ` -Local. Port "1434" ` -Protocol "UDP" ` -Remote. Address Any ` -Action Allow Enable-Net. Firewall. Rule -Display. Group "Distributed Transaction Coordinator" # Distributed Transaction Coordinator $Confirm. Preference="None" Set-Dtc. Network. Setting -Dtc. Name "Local" ` -Authentication. Level Mutual ` -Inbound. Transactions. Enabled $True ` -Outbound. Transactions. Enabled $True ` -Remote. Client. Access. Enabled $True $Confirm. Preference="High" 10

Managing SQL Server on Server Core Configuration Sql Server Configuration Manager The standalone version of SSCM can only connect to the local machine. However, SSCM is actually an MMC snap-in and it will appear in Computer Management 11

Managing SQL Server on Server Core Configuration Sql Server Configuration Manager SSCM is version specific. If you manage multiple versions of SQL Server, multiple copies of SSCM need to be installed on the management machine. They are hard to tell apart. If you select the wrong one, a WMI provider error occurs 12

Managing SQL Server on Server Core Remote Management Options other than SSMS Powershell SQL Server SMO Microsoft provides SQL Server Management Objects (SMO) for use with. Net languages and a Sql. Server module for powershell. dbatools Community developed powershell module with over 600 commands to simplify management and migration of sql server 13 1

Best Practices Managing SQL Server on Server Core IMOHO ● ● 14 Get to know your system admins ○ Local Security Policies ○ Administrative Shares Copying a patch to the server locally is easier to install instead of via a UNC path ● Embrace Powershell ○ It makes life easier! ● DBATools is your new best friend.

DBATools 15 Managing SQL Server on Server Core

Title of My Presentation Demo Time 1

Title of My Presentation CREDITS Shapes & Icons Images & Backgrounds Vectorial Shapes in this Template were created by Slides. PPT. com. The photos in this template have been downloaded from www. gratisography. com. Icons on this template have been downloaded from https: //www. flaticon. com/ Authors: Freepik The Backgrounds from Starline - Freepik. com Corner Graphics Designed by Pio_pio - Freepik. com COLORS #142906 #548035 #230739 Fonts The fonts used in this template are taken from Google fonts. ( Calibri) Download the fonts from the following url: https: //www. google. com/fonts/ #8 c 59 b 4 #ffcc 00 #f 0 a 94 d 17 template by Slides. PPT. com

Patching Managing SQL Server on Server Core No GUI, No Problem, Part Duex Patching via powershell can be automated. Including taking VMWare Snapshots 5