Seven obstacles of SQL Server Core Installation

Seven obstacles of SQL Server Core Installation

Following topics:
- How to Install and prepare Windows Core for SQL Server installation

- SQL Server Windows Core installation does not support Reporting Service

- Five Steps of Remote SQL Server installation using PowerShell


I strongly believe when a system has less moving parts it behaves more stable and responds faster.
The Windows Core is perfect example when there are much less moving parts than in Windows with GUI.
Why is that?
Generally Windows Server installed with EVERYTHING, with all possible features you might not need, especially when you plan to have only SQL Server in that box.

There are three reasons why Windows Server with Core is better than Server with a GUI:
1. Security: Core has less features. That means less surface for intruders to attack.
2. Management: No unnecessary updates and planned reboots.
3. Resource consumption: Less memory usage and less CPU overhead


I've seen a situation when 8 DBAs connected to a SQL Server via Remote Desktop. Run SQL Server Management Studio with several tabs and query results opened and disconnected from the server without closing their sessions.
Discussion about the lack of memory on that server was very informative and interesting.

You do not have that problem with Core because ALL SQL Server administration can be done remotely from DBA's PC or dedicated Gateway Server. Less people can go to your server more secure and stable it is.

In this post I will describe my experience with installing SQL Server in Windows Core environment.

Here are all Seven obstacles I've hit during that installation:

1. Installation must be performed ONLY in command line mode. There is just NO GUI. You Have to start SQL Server installation with following command:

setup.exe /ACTION=Install

2. Because there is no GUI, installation MUST be absolutely "QUIET", which requires following parameter:

/Q

3.During the installation you are usually asked about License Agreement. Right? To suppress that and "quietly" answer "I Accept SQL Server License Terms", you have to add following parameter:

/IAcceptSQLServerLicenseTerms

4. SQL Server on Core does not support ALL SQL Server features. For Instance it does not support Reporting Services and Management Studio. To solve the issue you have to specify features you want to install like this:

/FEATURES=SQL,AS,IS

More details about features are in MDSN: https://msdn.microsoft.com/en-us/library/ms144259.aspx#Feature

5. You have to decide how you will access your SQL Server. If your Server is in a domain, than you can provide domain admin account or domain group. If you do not have a domain you better use SQL authentication. You also have to specify Local user accounts with passwords, which will be used for running services:

/SECURITYMODE=SQL /SAPWD=<Your strong SA Password> /SQLSVCACCOUNT="MyDomain\MyAccount" /SQLSVCPASSWORD=<Password> /SQLSYSADMINACCOUNTS="MyDomain\MyAccount" /AGTSVCACCOUNT="MyDomain\MyAccount" /AGTSVCPASSWORD=<Password> /ASSYSADMINACCOUNTS="MyDomain\MyAccount"

6. You have to specify name of your SQL Server instance:

/INSTANCENAME=MSSQLSERVER

7. You have to specify the SQL Server product key. If you do not do that, SQL Server will be installed in Evaluation mode. You do it like this:

/PID=<Valid SQL Server product key>

That is it. Seems very easy.

Lets Start do SQL Server Core installation in three easy steps:

Step 1. Connect to a Windows Core Desktop and login as an administrator:

After you login, screen should look like this:

Step 2. Copy-paste following script and hit enter:

d:\setup.exe /ACTION=Install /Q /IAcceptSQLServerLicenseTerms /INSTANCENAME=MSSQLSERVER /SAPWD=MyStrongSAPassword2016 /SECURITYMODE=SQL /FEATURES=SQLENGINE /SQLSVCACCOUNT="WinCore\CoreAdmin" /SQLSVCPASSWORD="LocalAdmin2016" /SQLSYSADMINACCOUNTS="WinCore\CoreAdmin" /AGTSVCACCOUNT="WinCore\CoreAdmin" /AGTSVCPASSWORD="LocalAdmin2016" /ASSYSADMINACCOUNTS="WinCore\CoreAdmin"

There are few assumptions:
A. I've used Server name "WinCore"
B. I've used Server Admin Account "CoreAdmin" with password "LocalAdmin2016"
C. My SQL Server installation DVD is on "D:\" Drive.
D. I've tried "Evaluation" installation mode.

It should look like this:

After several minutes of the installation I got following:

In case your SQL Server installation produced errors, the error file and details will be stored in following folder: "C:\Program Files\Microsoft SQL Server\<Version Number>\Setup Bootstrap\Log\" on the Server.

Step 3. Open Incoming Access Port for Your newly installed SQL Server and Restart the box.

At first, run following command in PowerShell to open the port

New-NetFirewallRule -DisplayName "Open Inbound Port 1433" -Direction Inbound –LocalPort 1433 -Protocol TCP -Action Allow

Then, restart the box.

After the restart, I opened SSMS on my local machine and connected to the newly built SQL Server:

In this post I did not cover the Windows Core installation itself, but I might do it in one of my following posts.