Install SQL Server 2014 on Server Core

    In the past years it has been more and more obvious that Microsoft is pushing Server Core as the default server installation option. As such they have improved a lot of things in it over time like management and compatibility. A crucial role for an enterprise server is the database role and it would be normal to be supported on server core also; actually, in my opinion, it makes a lot of sense because the database and the database engine can be managed remotely with the same tools as you would manage them locally. So Server Core lets you run SQL in a more compact OS, with less updates to be done, less restarts to make and a smaller attack surface.

    Although the title says SQL Server 2014, it is 100% possible to install also 2012 or 2016 on Server Core. Before we dig in the good stuff lets first see how the lab setup looks like and what you will need.

    Prerequisites

    First of all I have installed 2 virtual machines with the following configuration:

    Name: DC1

    Role: Domain Controller

    OS version: Windows Server 2012 R2 with GUI

This is the domain controller for my test domain which will have installed the SQL Server Management tools

    Name: DB1

    Role: Database Server

    OS version: Windows Server 2012 R2 server core

This machine will have the database engine on it.

    For both servers I assume you know how to configure the basic settings like server name, domain join, IP address configuration.

    I have created a user account which will have admin rights on the database and placed it in Domain Admins so it can log on and do management tasks on the DC as well:

    User: DBAdmin  member of Domain Admins group

    Some tools will also be needed. They can be downloaded from Microsoft. First of course is SQL Server 2014. Make sure you download the version that includes the management tools also. I will be using SQL 2014 Express which can be downloaded from: Here.

    3 additional tools are required. They are the Powershell Tools, the Shared Management Objects and the Command Line Utilities. The link for them is: Download SQL Server 2014 tools.

    The Command Line Utilities can bo obtained by expanding the Install Instructions:

Download SQL Server CMD Utilities

Download SQL Server CMD Utilities

    The other 2 programs are downloaded by pressing the Download button and selecting them:

Get the PS Tools and the SQL SMO

Get the PS Tools and the SQL SMO

    The last step before beginning is to extract the install files from the SQL Server installation .EXE. Put the files on the server after you are done.

    Install SQL Server

    First of all: we will be using Powershell to install and configure SQL so log on the DB1 server with DBAdmin and start Powershell:


start Powershell

Start Powershell on Server Core

Start Powershell on Server Core

    The next thing I recommend is to enable file sharing in case you have the tools on the other server and want to copy them. This is only a firewall rule which needs to be enabled:


Enable-NetFirewallRule FPS-SMB-In-TCP

Get-NetFirewallRule FPS-SMB-In-TCP

Enable File Sharing firewall rule on Server Core

Enable File Sharing firewall rule on Server Core

    I put everything that I downloaded in ‘C:\SQL Server’. The actual installation kit is in the SQL Install folder and next to it you can see the 3 MSI files and an INI file that contains installation instructions. I will show you how to set up this INI file a little later. You can make the same folder structure if you want.

Install files folder structure

Install files folder structure

    A requirement for SQL Server is the 3.5 version of the .NET framework. To install it just insert the Windows installation disk in the VM’s optical drive and run the following Powershell command (in my case the DVD is assigned the D letter; check to see what letter you get and modify the command accordingly):


Install-WindowsFeature NET-Framework-Core -Source D:\sources\sxs

Install .NET 3.5 on server core

Install .NET 3.5 on server core

    Now we can get to bussines. Since the installation will be done from the command line, all the needed parameters have to be specified either directly when running the command or using a configuration file that the setup will read. I like to use the configuration file approach as it is more cleaner and good for using in the future at other installations. Here is how mine looks like:

SQL server 2014 install file

SQL server 2014 install file

[OPTIONS]
ACTION="Install"
FEATURES=SQLENGINE
INSTANCENAME="SQLExpress"
INSTANCEID="SQLExpress"
SQLSYSADMINACCOUNTS="LAB\DBAdmin"
IAcceptSQLServerLicenseTerms="True"
TCPENABLED=1
BROWSERSVCSTARTUPTYPE="Automatic"
UPDATEENABLED=False
    Just create a text file, copy everything in it and save it with the INI extension. Mine is named install_sql.ini.
    The ACTION parameter specifies that we want to install the software and not update it for example. FEATURES specifies what exactly we are installing. In this case we only need the actual SQL ENGINE. The INSTANCENAME and INSTANCEID properties have to be specified even though these are the default values. You can specify whatever value you want for the instance. We will set DBAdmin as the SQL administrator. TCPENABLED means that the TCP protocol will be used for connections to the database. The BROWSERSVCSTARTUPTYPE sets the stat type of the Browser service to automatic.
    With all this done it’s finally time to install our database. Navigate to the SQL Server folder and run the following command:
& '.\SQL Install\SETUP.EXE' /Q /ConfigurationFile=.\install_sql.ini
Install SQL Server 2014 on Nano Server

Install SQL Server 2014 on Nano Server

    The installation will take about 10 minutes. After it is finished just run the following Powershell command to check that the services are installed and running like in the picture:


Get-Service *sql*

Get a list of SQL services

Get a list of SQL services

    Before making the final configuration we have to install the 3 MSI files that we downloaded. Please install them in the same order as me.


msiexec /i .\SharedManagementObjects.msi /q

msiexec /i .\PowershellTools.msi /q

msiexec /i .\SqlCmdLnUtils.msi /q

Install the 3 MSI packages

Install the 3 MSI packages

    Next we will make some settings but first restart the server.

     Configure the SQL server installation

    Some settings have to be made before connecting to the server remotely. First we will enable remote management. This can be done from SQLCMD.EXE. Be carefull with the -S switch as it is case sensitive. Replace DB1 and SQLExpress with your server name and instance name.

SQLCMD.EXE -S DB1\SQLExpress

EXEC sys.sp_configure N'remote access', N'1'

RECONFIGURE WITH OVERRIDE

Activate SQL server remote management

Activate SQL server remote management

    By default SQL server listens on dynamic TCP ports. Let’s set it to listen for this instance on 1433 instead. This task can be done from Powershell (replace DB1 and SQLExpress with your own server and instance name):


Import-Module sqlps

$MachineObject = new-object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') "DB1"

$instance = $MachineObject.getSmoObject("ManagedComputer[@Name='DB1']/ServerInstance[@Name='SQLExpress']")

$instance.ServerProtocols['Tcp'].IPAddresses['IPAll'].IPAddressProperties['TcpPort'].Value = "1433"

$instance.ServerProtocols['Tcp'].IPAddresses['IPAll'].IPAddressProperties['TcpDynamicPorts'].Value = ""

$instance.ServerProtocols['Tcp'].Alter()

Set 1433 as the listening port

Set 1433 as the listening port

    In order for communication to work between the SQL server and remote machines 2 ports have to be open in the firewall:

  • TCP 1433
  • UDP 1434

    Here is how to make the 2 rules:


New-NetFirewallRule -Name "SQL Server" -DisplayName "SQL Server" -Profile Any -Direction Inbound -Action Allow -Protocol TCP -LocalPort 1433

New-NetFirewallRule -Name "SQL Browser" -DisplayName "SQL Browser" -Profile Any -Direction Inbound -Action Allow -Protocol UDP -LocalPort 1434

Open 1433 in the firewall

Open 1433 in the firewall

Open 1434 in the firewall

Open 1434 in the firewall

    Almost done. Just restart the SQL instance and the SQL Browser services.


Restart-Service MSSQL`$SQLEXPRESS

Restart-Service SQLBrowser

Restart the 2 needed SQL services

Restart the 2 needed SQL services

    Now go over to the other server (in my case DC1) and install the SQL server management tools. After that open the SQL Server Management Studio as the DBAdmin account.

Run SQL Management Studio as DBAdmin

Run SQL Management Studio as DBAdmin

    Now connect to the instance just installed:

Connect to the SQL instance running on server core

Connect to the SQL instance running on server core

    That’s it. You have SQL Server on Windows Server 2012 R2 installed as server core.

 

Leave a Comment

Your email address will not be published. Required fields are marked *