With the release of Windows Server 2012 I thought I would take the opportunity to look at running SQL 2012 Core on Windows Server 2012.

This blog is part 2 of a series which will look at the configuration for installing SQL 2012 Core on Windows Server Core, and then finish with adding AlwaysOn functionality to the environment.

In Part 1 of this series we covered building the Management Server (SQL2012-MGMT) and the SQL Server (SQL2012-01) 

 

IMPORTANT: It is important to highlight that there are particular requirements for installing SQL Server on Windows 2012 and those are available at http://support.microsoft.com/kb/2681562/EN-US. For SQL 2012 the RTM release is supported on Windows 2012 however you should always refer to the official MSDN documentation to verify your hardware and software requirements (for SQL 2012 see http://msdn.microsoft.com/en-us/library/ms143506.aspx). It is also important to note that this work is out of pure interest within an isolated LAB environment and does not represent any official supportability of this configuration, it is based on my experience when configuring the environment and you should verify the suitability and supportability of such configuration within your own environment. 

 

Step 1 - SQL Install (SQL2012-01)

In this step we need to complete the SQL install on the Windows Server 2012 Core installation. As there is no GUI the install needs to be performed using an unattended install file and from the command line.

  1. Connect to the SQL Server using either your Hyper-V Console or RDP.
  2. First we need to create the directories
    1. From the Command Prompt navigate to the drive and use the mkdir command to create the required directories where you will store the database files.
      For my environment I have created the following directories as this provides segregation within environments with multiple SQL Instances on the one server.
      - E:\MSSQL11.MSSQLSERVER\MSSQL\Data
      - F:\MSSQL11.MSSQLSERVER\MSSQL\Data
      - G:\MSSQL11.MSSQLSERVER\MSSQL\Data
      - H:\MSSQL11.MSSQLSERVER\MSSQL\BackupDB
      - H:\MSSQL11.MSSQLSERVER\MSSQL\BackupTrnLog
  3. Ensure that the Windows Server 2012 installation media is inserted/mounted to the server's CD/DVD drive.
  4. Using powershell add the .Net 3.5 feature by the following command where #: is the drive to the Windows Server 2012 installation media (this is required as the installation source for .Net 3.5 was not included with the Windows install local source media):

    Install-WindowsFeature -Name NET-Framework-Core -Source #:\sources\sxs
  5. Ensure that the SQL media is available on the server by either either:
    1. CD/DVD drive presented to the VM and mapped to the correct ISO (recommended)
    2. or by copying the install media onto a drive of the vm
  6. Normally the GUI will be disabled for installation packages on Server Core, however you can run the SQL install package with the following parameter to allow the use of the GUI to allow you to generate the unattended installation file.

    D:\setup.exe /UIMODE=EnableUIOnServerCore /Action=install /INDICATEPROGRESS=True

    My personal preference is to always use an unattended install file, for more granular control and also easier management of multiple builds, so I use these parameters to run the UI and generate the unattended file or when troubleshooting installation issues. IndicateProgress ensures that the log information is displayed in the command line window so you can determine any hung operations or errors.

    For a full list of available parameters to configure within the unattended configuration file see http://msdn.microsoft.com/en-us/library/ms144259.aspx

    TIP: Ensure that you specify your local account as a SysAdmin account (or your DBA Team's Domain Group) so that you can connect and administer the server remotely. I'll cover restricting the membership of the sysadmins group and why you no longer need that privilege in another post, but at least for the initial setup this simplifies things, just remember to change it later.
  7. To perform the install using the Unattended Configuration File I created a directory on the server C:\SQLInstall and copied my unattended configuration file from the location provided by the GUI Install Wizard just prior to commencing the install, into that directory. This will also simplify the creation of my 2nd server later on when configuring AlwaysOn.
    So from the command prompt, run the following command to perform the install:

    D:\setup.exe /Action=Install /Q /ConfigurationFile=[PathToUnattendedFile]

    e.g. D:\setup.exe /Action=Install /Q /ConfigurationFile=C:\SQLInstall\ConfigurationFile.ini
  8. When the process completes, verify both from the command prompt output as well as from the log file C:\Program Files\Microsoft Sql Server\110\Setup Bootstrap\Log\Summary.txt that the process was performed successfully. To open the log file run the following from the command prompt:
    notepad.exe C:\Program Files\Microsoft Sql Server\110\Setup Bootstrap\Log\Summary.txt

 

 

Step 2 - Post SQL Install Configuration (SQL2012-01)

In this step we need to configure the additional settings like memory, TCP ports, etc, but more importantly this shows how to remotely manage your SQL Install.

  1. Connect to the Remote Management Server (SQL2012-MGMT) using either your Hyper-V Console or RDP.
  2. You may be tempted to immediately open the SQL Server Configuration Manager however the tool only connects to the local server by default. To connect to a remote server you must first open the Server Manager tool, locate the SQL Server (SQL2012-01), right click this server and then select Computer Management from the menu, this process is explained in more detail in Part 1 of this series.


    TIP: If the Server Manager console is closed simply open the new Start Menu and start typing "Server M", the new Start Screen in Windows Server 2012 will automatically start searching for applications that match the text you type and the Server Manager application should be displayed in the results.

  3. From the Computer Management console, expand Services and Applications, and SQL Server Configuration Manager
  4. The use of the configuration manager is the same as if you were on a local machine.
    TIP: If you receive a connectivity error here (e.g. The RPC Server is unavailable. [0x800706ba]) you need to check your Firewall settings on both the SQL Server and the Remote Management Server. See Part 1 for these settings.

    The things I configure here are :
    1. TCP/IP ports (because I do not like using the default port of 1433 as it is typically the target of attackers)
    2. Client Aliases (because I don't like to remember or tell people what port I am using, remember to set these up on the RMS server as well)
  5. Open the SQL Server Configuration Manager locally on the RMS server from the new Start Screen, and configure any Client Aliases as required.
    TIP: ensure you configure both 32-bit and 64-bit aliases to ensure that all applications can locate the server instances
  6. If you have changed the TCP/IP port that SQL uses make sure you create the appropriate firewall rule by running a command such as the following back on the console of the SQL Server (e.g. SQL2012-01).
    netsh advfirewall firewall add rule name="SQL TCP In" dir=in protocol=tcp localport=#### action=allow
  7. Next, open SQL Management Studio from the new Start Screen and connect to the SQL Server (e.g. SQL2012-01)
  8. From a New Query window run the following command to set the max server memory as appropriate for your server. When setting this value make sure you allow memory for the OS and any other applications you are running on the server (including any other SQL applications such as SSIS)
    EXEC sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE
    GO
    EXEC sp_configure 'max server memory (MB), ###;
    GO
    RECONFIGURE
    GO

    In my case I have run the following script as it preconfigures a number of the settings I like to set:
    EXEC sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE
    GO
    --Set the allowed memory
    EXEC sp_configure 'max server memory (MB), 900;
    GO
    --Enable backup compression
    EXEC sp_configure 'backup compression default', 1;
    GO
    --Set the Index Fill Factor %
    EXEC sp_configure 'fill factor (%)', 90;
    GO
    --Set the default backup media retention
    EXEC sp_configure 'media retention', 2;
    GO
    RECONFIGURE
    GO

 

Step 3 - Testing and Provisioning

Now that your SQL Installation is complete and you have performed your initial configuration steps, the next steps are.

  1. Run tests against the environment to check connectivity
  2. Capture a performance baseline
  3. Configure and test your backup and maintenance tasks
  4. Provision databases and users

Of cause this is best practise, in my Lab environment I am going to assume I know what I am doing (famous last words!)

 

What's Next

Congratulations your SQL Server 2012 Core install on Windows Server 2012 Core is now complete.

In the follow up blog to this series we will start to configure the AlwaysOn high availability feature.