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.
First of all I have installed 2 virtual machines with the following configuration:
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
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:
The other 2 programs are downloaded by pressing the Download button and selecting them:
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:
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
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.
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
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:
[OPTIONS] ACTION="Install" FEATURES=SQLENGINE INSTANCENAME="SQLExpress" INSTANCEID="SQLExpress" SQLSYSADMINACCOUNTS="LAB\DBAdmin" IAcceptSQLServerLicenseTerms="True" TCPENABLED=1 BROWSERSVCSTARTUPTYPE="Automatic" UPDATEENABLED=False
& '.\SQL Install\SETUP.EXE' /Q /ConfigurationFile=.\install_sql.ini
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:
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
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
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()
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
Almost done. Just restart the SQL instance and the SQL Browser services.
Restart-Service MSSQL`$SQLEXPRESS Restart-Service SQLBrowser
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.
Now connect to the instance just installed:
That’s it. You have SQL Server on Windows Server 2012 R2 installed as server core.