Installing SQL Server 2012 on Windows Server 2012 Core

This post accompanies a video tutorial which is available here http://youtu.be/Tytfq6WXvas


SQL-Server-2012-logo-405x260-390x242

I would like to document and share how to install SQL Server 2012 on a Core installation of Windows 2012. Windows Server Core as I am you may be aware of is a version of Windows Server without the graphical tools and weight of a full Windows Server installation. Some of the reasons for using Server Core are listed here:- 

  • Smaller attack surface as there is less installed and running by default .
  • Less patching and reboots as many services and features such as Internet Explorer, Windows Explorer, start screen are not installed.
  • Smaller disk footprint as the OS takes approximately 4GB as opposed to around 8GB for the full install.

New in Windows Server 2012 is the ability to install the full GUI if required and then remove it again.  This would usually only be required to install certain drivers or applications that must have a GUI.

The installation is the demo video is done using a Windows Server 2012 Core VM which is then configured through sconfig and joined to a domain.  The server already has some installation files and scripts on the C Drive as part of the SYSPREP image it was copied from.

The scripts used in the video are available in this article. SQL Server and Analysis Services are configured to run using Domain Accounts.


The Process

Prepare any code and scripts up front so they are ready for the installation.  Ensure the steps below are followed for a smooth installation.

1. Enable Remote Management, Remote Desktop, check and configure network IP setting

2.  Join the server to the domain and create the required service accounts in Active Directory.

3.  Ensure the Sources\sxs folder from the Windows 2012 installation media is available or copied to the server. Mine is copied to C:\SourcesSxS

4.  Allow SQL Server through firewall script :-

netsh advfirewall firewall add rule name="Open SQL Server Port 1433" dir=in action=allow protocol=TCP localport=1433

5.  Allow Analysis Services through the firewall :-

netsh advfirewall firewall add rule name="SQL Server Analysis Services inbound on TCP 2383" dir=in action=allow protocol=TCP localport=2383 profile=domain

Here are the SQL scripts used in the demo, and the code for each is found below.

image

6. Create or download and customise the ConfigurationFile.ini for SQL Server.

This can be done running through the SQL Server setup full GUI on another server and stopping before the installation part and grabbing the ini file from the default location of :- “C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log”

See this link for full details from Technet:-

http://technet.microsoft.com/en-us/library/dd239405(v=sql.110).aspx

You can then customise this file as you wish and select the components you require, installation type,  service accounts, directory locations.  Note Reporting Services (SSRS) is not supported on Server Core.  However SSIS is.

Here is the ConfigurationFile.ini from my setup for you to compare yours against or just copy this one !

Add SSMS, ADV_SSMS to the Features line to add the management tools.

Code Snippet
  1. ;SQL Server 2012 Configuration File – Mitesh Chauhan 2012
  2. [OPTIONS]
  3.  
  4. ; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.
  5.  
  6. ACTION="Install"
  7.  
  8. ; Detailed help for command line argument ENU has not been defined yet.
  9.  
  10. ENU="True"
  11.  
  12. ; Parameter that controls the user interface behavior. Valid values are Normal for the full UI,AutoAdvance for a simplied UI, and EnableUIOnServerCore for bypassing Server Core setup GUI block.
  13.  
  14. ;UIMODE="Normal"
  15.  
  16. ; Setup will not display any user interface.
  17.  
  18. QUIET="False"
  19.  
  20. ; Setup will display progress only, without any user interaction.
  21.  
  22. QUIETSIMPLE="True"
  23.  
  24. ; Specify whether SQL Server Setup should discover and include product updates. The valid values are True and False or 1 and 0. By default SQL Server Setup will include updates that are found.
  25.  
  26. UpdateEnabled="False"
  27.  
  28. ; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, MDS, and Tools. The SQL feature will install the Database Engine, Replication, Full-Text, and Data Quality Services (DQS) server. The Tools feature will install Management Tools, Books online components, SQL Server Data Tools, and other shared components.
  29.  
  30. FEATURES=SQLENGINE
  31.  
  32. ; Specify the location where SQL Server Setup will obtain product updates. The valid values are "MU" to search Microsoft Update, a valid folder path, a relative path such as .\MyUpdates or a UNC share. By default SQL Server Setup will search Microsoft Update or a Windows Update service through the Window Server Update Services.
  33.  
  34. UpdateSource="MU"
  35.  
  36. ; Displays the command line parameters usage
  37.  
  38. HELP="False"
  39.  
  40. ; Specifies that the detailed Setup log should be piped to the console.
  41.  
  42. INDICATEPROGRESS="False"
  43.  
  44. ; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system.
  45.  
  46. X86="False"
  47.  
  48. ; Specify the root installation directory for shared components.  This directory remains unchanged after shared components are already installed.
  49.  
  50. INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"
  51.  
  52. ; Specify the root installation directory for the WOW64 shared components.  This directory remains unchanged after WOW64 shared components are already installed.
  53.  
  54. INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"
  55.  
  56. ; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions. This parameter is required when installing the SQL Server Database Engine (SQL), Analysis Services (AS), or Reporting Services (RS).
  57.  
  58. INSTANCENAME="MSSQLSERVER"
  59.  
  60. ; Specify the Instance ID for the SQL Server features you have specified. SQL Server directory structure, registry structure, and service names will incorporate the instance ID of the SQL Server instance.
  61.  
  62. INSTANCEID="MSSQLSERVER"
  63.  
  64. ; Specify that SQL Server feature usage data can be collected and sent to Microsoft. Specify 1 or True to enable and 0 or False to disable this feature.
  65.  
  66. SQMREPORTING="False"
  67.  
  68. ; RSInputSettings_RSInstallMode_Description
  69.  
  70. RSINSTALLMODE="FilesOnlyMode"
  71.  
  72. ; Specify if errors can be reported to Microsoft to improve future SQL Server releases. Specify 1 or True to enable and 0 or False to disable this feature.
  73.  
  74. ERRORREPORTING="False"
  75.  
  76. ; Specify the installation directory.
  77.  
  78. INSTANCEDIR="C:\Program Files\Microsoft SQL Server"
  79.  
  80. ; Agent account name
  81.  
  82. AGTSVCACCOUNT="Demo\SQLSVC"
  83.  
  84. ; Auto-start service after installation.  
  85.  
  86. AGTSVCSTARTUPTYPE="Manual"
  87.  
  88. ; Startup type for Integration Services.
  89.  
  90. ISSVCSTARTUPTYPE="Automatic"
  91.  
  92. ; Account for Integration Services: Domain\User or system account.
  93.  
  94. ISSVCACCOUNT="NT Service\MsDtsServer110"
  95.  
  96. ; The name of the account that the Analysis Services service runs under.
  97.  
  98. ASSVCACCOUNT="Demo\SSASSVC"
  99.  
  100. ; Controls the service startup type setting after the service has been created.
  101.  
  102. ASSVCSTARTUPTYPE="Automatic"
  103.  
  104. ; The collation to be used by Analysis Services.
  105.  
  106. ASCOLLATION="Latin1_General_CI_AS"
  107.  
  108. ; The location for the Analysis Services data files.
  109.  
  110. ASDATADIR="C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Data"
  111.  
  112. ; The location for the Analysis Services log files.
  113.  
  114. ASLOGDIR="C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Log"
  115.  
  116. ; The location for the Analysis Services backup files.
  117.  
  118. ASBACKUPDIR="C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Backup"
  119.  
  120. ; The location for the Analysis Services temporary files.
  121.  
  122. ASTEMPDIR="C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Temp"
  123.  
  124. ; The location for the Analysis Services configuration files.
  125.  
  126. ASCONFIGDIR="C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Config"
  127.  
  128. ; Specifies whether or not the MSOLAP provider is allowed to run in process.
  129.  
  130. ASPROVIDERMSOLAP="1"
  131.  
  132. ; Specifies the list of administrator accounts that need to be provisioned.
  133.  
  134. ASSYSADMINACCOUNTS="DEMO\administrator" "DEMO\ssassvc"
  135.  
  136. ; Specifies the server mode of the Analysis Services instance. Valid values are MULTIDIMENSIONAL and TABULAR. The default value is MULTIDIMENSIONAL.
  137.  
  138. ASSERVERMODE="MULTIDIMENSIONAL"
  139.  
  140. ; CM brick TCP communication port
  141.  
  142. COMMFABRICPORT="0"
  143.  
  144. ; How matrix will use private networks
  145.  
  146. COMMFABRICNETWORKLEVEL="0"
  147.  
  148. ; How inter brick communication will be protected
  149.  
  150. COMMFABRICENCRYPTION="0"
  151.  
  152. ; TCP port used by the CM brick
  153.  
  154. MATRIXCMBRICKCOMMPORT="0"
  155.  
  156. ; Startup type for the SQL Server service.
  157.  
  158. SQLSVCSTARTUPTYPE="Automatic"
  159.  
  160. ; Level to enable FILESTREAM feature at (0, 1, 2 or 3).
  161.  
  162. FILESTREAMLEVEL="0"
  163.  
  164. ; Set to "1" to enable RANU for SQL Server Express.
  165.  
  166. ENABLERANU="False"
  167.  
  168. ; Specifies a Windows collation or an SQL collation to use for the Database Engine.
  169.  
  170. SQLCOLLATION="Latin1_General_CI_AS"
  171.  
  172. ; Account for SQL Server service: Domain\User or system account.
  173.  
  174. SQLSVCACCOUNT="Demo\SQLSVC"
  175.  
  176. ; Windows account(s) to provision as SQL Server system administrators.
  177.  
  178. SQLSYSADMINACCOUNTS="DEMO\administrator" "DEMO\sqlsvc"
  179.  
  180. ; Provision current user as a Database Engine system administrator for SQL Server 2012 Express.
  181.  
  182. ADDCURRENTUSERASSQLADMIN="False"
  183.  
  184. ; Specify 0 to disable or 1 to enable the TCP/IP protocol.
  185.  
  186. TCPENABLED="1"
  187.  
  188. ; Specify 0 to disable or 1 to enable the Named Pipes protocol.
  189.  
  190. NPENABLED="0"
  191.  
  192. ; Startup type for Browser Service.
  193.  
  194. BROWSERSVCSTARTUPTYPE="Manual"
  195.  
  196. ; Specifies which account the report server NT service should execute under.  When omitted or when the value is empty string, the default built-in account for the current operating system.
  197. ; The username part of RSSVCACCOUNT is a maximum of 20 characters long and
  198. ; The domain part of RSSVCACCOUNT is a maximum of 254 characters long.
  199.  
  200. RSSVCACCOUNT="NT Service\ReportServer"
  201.  
  202. ; Specifies how the startup mode of the report server NT service.  When
  203. ; Manual – Service startup is manual mode (default).
  204. ; Automatic – Service startup is automatic mode.
  205. ; Disabled – Service is disabled
  206.  
  207. RSSVCSTARTUPTYPE="Automatic"

7.  Run the .NET Framework 3.5 Feature Installation from PowerShell.

The following script enables non signed code to run, then installs the .NET Framework Feature and tells Windows to find the source files in the C:\Sources\sxs folder.

Set-ExecutionPolicy RemoteSigned -Force

Install-WindowsFeature –Name NET-Framework-Features –Source C:\Sources\sxs

8.  Insert Media (if not already inserted or copied locally). Run the SQL installation command or batch file.

The text used in mine is here (carriage returns added for readability):

d:\setup.exe /SQLSVCPASSWORD=P@ssword123 /AGTSVCPASSWORD=P@ssword123 

/ASSVCPASSWORD=P@ssword123  /configurationfile=c:\Scripts\configurationFile.ini /IAcceptSQLServerLicenseTerms

9.  Wait for the install to complete and check the log files. 

The installation log files will be found in in “C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log”. Reboot (recommended) and test connectivity when setup has completed.

10.  Run the Mofcomp command or script to allow remote configuration of SQL Server Manager. The script is simply mofcomp sqlmgmproviderxpsp2up.mof from the “C:\Program Files (x86)\Microsoft SQL Server\110\Shared” directory


Troubleshooting

Cannot access the SQL Server from a remote server.

First check networking with ping etc. Check IP Addressing, DNS, subnets and LAN/VLAN config.

Once the above is checked the problem is most likely to do with firewall settings on the Windows Core Server. You can run the Coreconfig FirewallSettings.PS1 from the Powershell command line. Download from codeplex (link below) and copy to the virtual disk. You can do this by copying across the network or shutting down the VM and double clicking the VHDX file which will open up the drive to allow you to copy files to and from the VHDX.  Remember to Eject it when finished.

Download Core Configurator from here

http://coreconfig.codeplex.com/

The Firewall settings should look something like this.

clip_image001

This should enable remote connections correctly.

Alternatively turn off the firewall which can be done with the same tool, or Group Policy for example.

Cannot connect to Analysis Services

Again this is most likely to be a firewall issue. Run the following command to allow access to the Analysis Services default port.

netsh advfirewall firewall add rule name="SQL Server Analysis Services inbound on TCP 2383" dir=in action=allow protocol=TCP localport=2383 profile=domain

WMI Errors

When connecting remotely via Computer management you may get WMI errors.

See the following link

http://msdn.microsoft.com/en-us/library/ms188690(v=sql.110).aspx

Cannot connect to SQL Server Configuration Manager Remotely

Error :- Cannot connect to WMI provider.You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers with SQL Server Configuration Manager.
Access is denied.

The fix for this to run the following command from the correct directory shown below.

Mofcomp sqlmgmproviderxpsp2up.mof

clip_image003

 

Cannot connect to SQL Server as dynamic ports are used for a named instance.

Add exceptions for the SQLSERVR.EXE file in the C:\Program Files\Microsoft SQL Server\MSSQL11.<instance_name>\MSSQL\Binn directory. Also ensure the SQL Browser service ports are unblocked. to do this enter:-

netsh advfirewall firewall add rule name="SQL Server Browser Services inbound on TCP 2382" dir=in action=allow protocol=TCP localport=2382 profile=domain

Fort further SQL Server firewall related configuration steps please see the following links:-

Configure the Windows Firewall to Allow SQL Server Access

http://technet.microsoft.com/en-us/library/cc646023.aspx#BKMK_ssas

Configure the Windows Firewall to Allow Analysis Services Access

http://technet.microsoft.com/en-us/library/ms174937.aspx

Thanks for reading !

 

Please let me know if you found this article useful and if you use or plan to use Windows Core with SQL Server and if there are any interesting findings you would like to share.

Advertisements

About Mitesh Chauhan
Mitesh Chauhan, Azure Cloud Solutions Architect. This is my blog where I share articles and thoughts on IT Infrastructure and architecture. The topics I am most passionate about are Implementation and architecture of rock solid Cloud Infrastructure based around SQL Server and Windows Server mainly using Microsoft Azure. MCTS - Azure Architecture MCTS - Azure Implementation MCSE Server Infrastructure (Windows Server 2012) , MCITP SQL Server 2008, Togaf Certified, Prince 2 Practitioner.

4 Responses to Installing SQL Server 2012 on Windows Server 2012 Core

  1. brad says:

    Very good article! I had no problem installing sql 2012 on Win 2012 server core, following this.
    Notes:
    I modified the ConfigurationFile.ini by hand, which was fairly simple.
    I had to perform step 7, after step 10 (after sql was installed and the directory path existed).
    Many thanks.

    • Hi Thanks Brad ! I have also updated the order of the blog to reflect this at it makes more sense to do it this way round. thanks again and glad you found it useful. Hope you also checked out the video too.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: