SQL Server

Step-by-step guide to install a dedicated Microsoft SQL server.

More from the Lab!

 

For the needs of the Citrix Guru lab, we will setup a dedicated Microsoft SQL Server. It will be used by Citrix applications like XenDesktop or XenApp, etc.

Requirements

  • Create a service account member of the domain
  • Add this service account as admin of the Windows server where you plan to install SQL
  • Install .NET 3.5 SP1 (Windows Feature in 2012 R2)
  • Disable Windows Firewall
  • WMI service must be running
  • Internet is required during the installation to download updates

Configuration

I created a new virtual machine with the following configuration:

  • Name: SQL01
  • Domain: CITRIXGURU
  • 2048 MB of RAM
  • 1 vCPU
  • Network adapter: 1 – LAN (vLAN ID 2)
  • 80 GB HDD
  • Windows Server 2012 R2 Datacenter edition

Download SQL Server 2014 SP1

Microsoft SQL Server 2014 with SP1 is the latest RTM version available.

Available on MSDN.

MSDN
MSDN

Installation

Once you’ve downloaded the .ISO file from MSDN, mount it to the virtual machine.

Setup.exe will start automatically.

Select New SQL Server stand-alone installation or add feature to an existing installation.

Select New SQL Server stand-alone installation or add feature to an existing installation
Select New SQL Server stand-alone installation or add feature to an existing installation

Enter your product key. (if the file is from MSDN) the license should be automatically populated.

Enter product key

Accept the license.

Accept the license
Accept the license

Select Use Microsoft Update.

Use Microsoft Update
Use Microsoft Update

Run Pre-checks.

Run Pre-checks
Run Pre-checks

Select SQL Server feature installation.

Select SQL Server feature installation
Select SQL Server feature installation

Select the features below.

Select the features
Select the features

Select the location of the installation, here it is the default.

Select the location
Select the location

Another Pre-checks.

Another Pre-checks
Another Pre-checks

Enter the name of the instance. Here it is MSSQLSERVER.

Instance name
Instance name

Now change the account for the following services:

  • SQL Server Agent
  • SQL Server Database Engine
  • SQL Server Analysis Services
  • SQL Server Reporting Services

Here we are using our new SVC_SQL domain account.

Configure service account
Configure service account

Select Mixed-Mode and define the SA password. Select add current user to add your current user as admin of the SQL server.

Define SA password
Define SA password

Next screen, select Add Current user.

Add current user
Add current user

Select Install and configure.

Install and configure
Install and configure

Select install to start.

Start the installation
Start the installation

Install in progress.

Install in progress
Install in progress

Installation is done.

Install is done
Install is done

Connect to the SQL Server

Management Studio

Now open Microsoft Management Studio.

Server: SQL01
SQL Authentication:
Login: SA
Password: your password

Connect with Management Studio
Connect with Management Studio

You can open the Security tab to see that the service account and the current user have been added as administrators of the SQL server.

SQL Permissions
SQL Permissions

For future needs, I’ve created a SQL account (SQL_CITRIX) that will be used by Citrix applications to connect to the SQL server.

SQLCMD

You can also use the SQLCMD command line tool to connect to the server.

The following command line lists all the databases on the SQL server.

Registry

With the following command line, you can easily see the instance(s) installed on the server.

More from the Lab!

 



5 COMMENTS

  1. When you created your SVC_SQL domain account what group member ships did you give this account? Its seems like you would have to set the password to never expire also.

    Why do you not instead use a AD Services account?

    Steps on Domain controller:
    1. Start “Windows PowerShell” with RUN AS Administrator.(Just put Windows PowerShell in the search windows. You get 4 different ones but you want the one with that exact name. )
    2. You need to load the AD Module in PowerShell type: Import-module ActiveDirectory
    o Loads AD Module in PowerShell
    3. Command to create a Manage Service account called “whatdoIcallthis”.
    Note max user id is 14 characters.
    o New-ADServiceAccount -Name whatdoIcallthis -enabled $true
    -Creates Account
    o Add-AdComputerServiceAccount -Identity ServerFred -ServiceAccount whatdoIcallthis
    -Ties services account to computer that is going to use it.

    Second from the server you need the account for you link it to the needed service.
    SQL SErver
    1. On server using account. Install “Active Directory module for Power Shell” feature
    2. Start “Windows PowerShell” with RUN AS Administrator
    3. Module in PowerShell type: Import-module ActiveDirectory
    4. Install-ADServiceAccount –Identity whatdoIcallthis

    Using account:
    Services accounts must enter with a $ sign when entered.
    Correct way to enter a service account: DomainName\whatdoIcallthis$
    Incorrect way not DomainName\whatdoIcallthis

    If the window used to enter the service account does not accept “$”, then you use a workaround. Put the service account in a local or domain group. Whatever makes the most sense. And use that group instead.

Comments are closed.