Installing Microsoft SQL Server on Windows Server Core for any of your enviornments should not be a daunting task, even for a developer that doesn’t have an IT Team. Let’s go through the simple steps you need to follow to get the installer on the server installing it. Once installed we can make sure we have our Azure Firewall configured correctly if it is a development server.
Limitations
Windows Server Core doesn’t support all the same features that Windows Server does for MS SQL Server. You will need to decide if the limitations are acceptable for your needs or not.
Not Supported Features
- Reporting Services
- SQL Server Data Tools (SSDT)
- Client Tools Backwards Compatibility
- Client Tools SDK
- SQL Server Books Online
- Distributed Relay Controller
- Master Data Services
- Data Quality Services
To see the most up to date list check out the Microsoft Docs
Pre-Reqs
Let’s assume you created a new Windows Server Core 2016 instance from Windows Azure.
- Windows Server Core
- SQL Server 2016 Disk Image
- FTP Server, File Share or Azure Blob Storage
- Patience
Server Memory Allocation
When we went through the steps in this walkthrough we tried it on the lowest resource Azure Server which allocated 1GB of Memroy. The installation process ran out of memory so we used 2 GB to install and swapped it back to 1 GB of memory after the install.
We are using such low resources because this is a development enviornment that doesn’t need anything more. Our philosophy with Windows Server Core is only use what you need and if you can get away with low resources do it.
Transfer the Disk Image
To get started we will need to get the ISO or Disk Image onto the Server so we can run the installer. There are various ways to go about this, we decided to quickly create an Azure Storage Blob and transfer the files there that we can then download from the server via powershell commands.
- Start off by compressing the .iso file into a .zip file. This step is required, we ran into iso corruption issues when using powershell to download the file
- Copy the file to some file share, ftp server or Azure Blob Storage account that you can access from the server
Let’s assume we have access to your compressed installer at http://your.file.store/sql_server_2016.zip. RDP into your Windows Server and open powershell, then enter the following commands:
1
2
$webclient = New-Object System.Net.WebClient
$webclient.DownloadFile("http://your.file.store/sql_server_2016.zip", "C:\sql_server_2016.zip")
Once the file is downloaded we can begin extracting the ISO
1
Expand-Archive C:\sql_server_2016.zip .
This will copy the iso we compressed into the current directory. Once this command completes we can begin the installation process
Mount the Disk Image
The Disk Image or ISO should be available on your server and we are ready to mount the image and begin the installer. The commands to accomplish this are slightly different from how you would do this in the desktop enviornment because you are running powershell commands. Earlier we extracted our iso to C:\sql_server_2016.iso, the command expects the full path so let’s be sure to specify it.
1
Mount-DiskImage -ImagePath C:\sql_server_2016.iso
If the command runs successful there will be no errors and we can navigate to our newly mounted drive. You will need to figure out what drive letter was mounted you can easily guess the next drive letter, in our case it is the E:\Drive
Navigate to the E:\Drive by entering in the following command
E:
The easiest way to determine if you are in the correct mounted drive is typing ls as your command and inspecting the drive contents
Install SQL Server
There are many ways to use the command line to install SQL Server and there is an overwhelming amount of information available at the Microsoft Docs. This tutorial is focused at developers and one time installations so we are going to utilize the UI on Windows Server Core.
Wait One Second, you said we are using Windows Server Core without a Desktop Enviornment
Even though we are using Windows Server Core certain installers and applications will still work with their desktop enviornment if you specify it. For example Install Shield installers will work as you would expect. In our case the installer process doesn’t work exactly how we expect so we need to specify some flags, but once we do that it works just like you would expect.
1
./setup.exe /UIMODE=EnableUIOnServerCore /ACTION=INSTALL
The installer UI will open up and you can follow the steps like you usually do
An easy way to verify if your SQL Server installed correctly is check the list of running Windows Services. You can do that by running the Get-Service command
Troubleshooting
No guide is foolproof and sometimes it won’t work on the first try. My best piece of advice if you have a bad installation is just uninstall SQL Server, reboot and try again. To uninstall execute the following command
1
./setup.exe /UIMODE=EnableUIOnServerCore /ACTION=UNINSTALL
Firewall & Remote Access
Our SQL Server is installed and running but we can’t access it, you need to open up the correct firewall ports on both the Server and Azure (or your network or other cloud provider). Still on the server in powershell enter the following command:
1
netsh advfirewall firewall add rule name="MS SQL Server DB Remote Management" dir=in action=allow protocol=TCP localport=1433
This command will open up the port just at the server firewall level. If you are connecting from within the network you are all set, but if you need to connect from outside of the network then you need to continue to open up port 1433