- Advertisement -
HomeVirtualizationVDIVDI-LAB-2018 – Part 6 – Installing and configuring SQL Server 2017

VDI-LAB-2018 – Part 6 – Installing and configuring SQL Server 2017

Summary:

In this part, we are going to install and configure SQL server 2017, then create the databases for vCenter server, View Composer and View Events. Also, important configurations will be made in order to add this user later on to a View Connection server.

This is part 6 out of 12 of the VDI LAB series. Check out the introduction first.

The ultimate VDI deployment guide (from scratch) with VMware vSphere 6.5 and Horizon View 7.3 – 2018. 😉

1. Prerequisites

  • Windows Server 2016 template, previously created in part 4 of this series
  • Download SQL Server 2017 (This will be performed in section 3)

2. Prepare the VM

  1. Deploy the VM from the template previously created and modify the hardware specs for CPU, RAM, and HDD.
  2. Configure a static IP address.
  3. Change computer name and add the server to the domain.
Part6 Image01
  1. Use the domain controller server credentials.
Part6 Image02
  1. Reboot the VM

@Domain Controller Server

  1. The SQL Server will be added to the computers folder of the domain controller server. Later this can be moved to the new OU VM / Computer (Created in part 5 - section 5 - of this series).
Part6 Image03

3. Install SQL Server 2017

- Advertisement -

@ SQL Server VM

  1. Download SQL Server 2017, here.
  2. Let’s use the Developer edition.
Download SQL Server 2017
  1. Double-click in the application SQL file to start the installation.
Part6 Image05
  1. From installation type select Custom
SQL Server 2017
  1. Select media download target (Just leave as default).
Part6 Image07
  1. The download will begin. This will take some time, after this, the installation wizard will start.
Part6 Image08
  1. From SQL Server Installation Center, select Installation.
Part6 Image09
  1. Select: “New SQL Server stand-alone installation or add features to an existing installation”
Part6 Image10
  1. Check Specify a free edition and choose “Developer”
Part6 Image11
  1. Accept license terms.
Part6 Image12
  1. Decide whether or not to use Microsoft Update
Part6 Image13
  1. Review Install Rules and make corrections if necessary.
Part6 Image14
  1. Select Database Engine Services. Only this feature is necessary for our vSphere environment.
Part6 Image15
  1. Specify a name and instance ID. I am using “VM” for both options.
Part6 Image16
  1. Leave as default for service account configurations.
Part6 Image17
  1. In Authentication Mode select Mixed Mode, enter a password and then click to Add Current User.
Part6 Image18
  1. Click Install.
Part6 Image19
  1. The installation shouldn't take long.
Part6 Image20
  1. Review the installation and features status and click Next.
Part6 Image21

4. Install SQL Server Management Tools

  1. Still from SQL Server Installation Center, select: “Install SQL Server Management Studio”
SQL Server 2017
  1. This action will take you to the Microsoft link to start the download. Select: “Download SQL Server Management Studio 17.4” or the latest one.
Part6 Image23
  1. Go to Downloads, double-click SSMS application and click Run.
Part6 Image24
  1. From Microsoft SQL Server Management Studio wizard, click Install.
Part6 Image25
  1. Wait for the installation to be completed.
Part6 Image26
  1. Click Close to finish the installation wizard.
Part6 Image27
  1. Click Start and find Microsoft SQL Server Management Studio.
Part6 Image28
  1. From Connect to Server, click Browse for more... in Server name. Then go to Local Servers / Database Engine and select the instance previously created during the SQL server installation.
Part6 Image29
  1. Type Login and Password credentials and check Remember Password. Click Connect.
Part6 Image30
  1. We now have access to the database server.
Part6 Image31

5. Create Databases and Users

@ Management Studio

Let’s create vCenter database and user. This will be the most important database and user if vCenter Server Windows based is used.

NOTE: I’ll use vCenter based on windows for the sake of practicing, but using VCSA is recommended and lots of steps can be cut down from this installation.

NOTE: If you plan to use VCSA 6.7 as the one to control the desktops, is better to have an embedded database, and the creation of this vCenter user won't be required. But still, View Composer and View Events database are necessary.

5.1 Create vCenter Database

  1. Right-click Databases and click New Database...
Part6 Image32
  1. Name the vCenter database and leave the rest as default, if no special requirement is needed. I found that the database Initial Size of 10MB is OK for my environment. Click OK.
Part6 Image33

5.2 Create vCenter user

  1. Got to the Security folder and Right-click Logins, click New Login...
Part6 Image34
  1. From Login Name, click Search… then input vCenter user (previously created in the domain controller server in part 5 of this series), click Check Names and then OK.
Part6 Image35
  1. The domain controller will ask for credentials.
Part6 Image36
  1. From General, select Windows authentication, click OK.
Part6 Image37
  1. From User Mappings, select the vCenter database and check this user as db_owner. Or just follow the example and mark as the image below, this is very important!
Part6 Image38
  1. Right-click on the database instance and select Properties.
Part6 Image39
  1. Go to Permissions, select the vCenter user and check following permissions: View any definition and View server state. Click OK.
Part6 Image40

5.3 Create View Composer and View Events Databases and Users

  1. Follow the same steps for the vCenter database and user creation to create:
  • View Composer database
  • View Composer user
  • View Events database
  • View Events user
  1. Apply same permissions and user mappings to these users as was done for the vCenter user, following their respective user/database.
- Advertisement -

IMPORTANT NOTE: When creating the user for the View Events, SQL Server Authentication MUST be selected, otherwise, it won’t be possible to add to View Connection server.

Part6 Image41

6. Important Configurations

  1. Start SQL Server Browser service, by default is Disabled.
Part6 Image42
  1. Also, in order to add View Events user later to View Connection server. Make sure TCP/IP status is Enabled and open port 1433 in SQL server. Follow this link for more details. https://kb.vmware.com/s/article/1029537
  2. Go to Start / SQL Server Configuration Manager.
Part6 Image43
  1. Go to SQL Server Configuration Manager (local) / SQL Server Network Configuration / Protocols for VM (instance). Very that TCP/IP is Enabled, If not, enabled it.
Part6 Image44
  1. Right-click on TCP/IP and go to IP Addresses tab and go way down to find IPAII. Clear the information on TCP Dynamic Ports and add 1433 port to TCP port. Just as the image below.
Part6 Image45

After this, databases and users will be ready for the infrastructure.

Juan Mulford
Juan Mulford
I have been active in IT for over fourteen years now. I am a solutions architect, working with storage, virtualization, and VDI solutions. For the past ten years, I have been living and working in Taiwan.

1 COMMENT

Leave a Reply

- Advertisement -

Popular Articles

mulcas.com-Raspberry-Pi

Raspberry Pi OS in a Virtual Machine with VMware

4
Although the Raspberry Pi OS is designed and optimized for the Raspberry Pi module, it is possible to test and use it without its hardware, with VMware. This solution can be useful if you are a developer (or just a curious guy) and don't have a Raspberry Pi module with you
Unable to delete inaccessible datastore

Unable to delete an "inaccessible" datastore

4
I was switching my storage array, so I migrated the VMs from that old datastore/storage to a new datastore/storage. The old datastore was shared by 3 ESXi hosts, no cluster. After migrating the VMs and unmount/delete the datastore, it was still presented in two of the ESXi hosts and was marked as inaccessible.
mulcas.com-VMware-OVF-Tool

How to export a Virtual Machine using the VMware OVF Tool

8
The VMware OVF Tool is implemented by VMware for easily importing and exporting virtual machines in Open Virtualization Format (OVF) standard format. Here, I want to show you how to download and install it, and then how to use it from a Windows machine.
This is not a valid source path / URL

This is not a valid source path / URL - SourceTree and Gitlab

0
I have been working on a project with a friend who set up a repository in Gitlab but even though I was able to view all projects on it, I couldn’t really join the repository. I was using SourceTree and Gitlab.
WinSCP VCSA

Unable to Access the VCSA 6.7 via WinSCP

9
One of the many and easiest ways to get the logs from the ESXi hosts and vCenter Server Appliance (VCSA), is accessing directly to the files directory using tools such as WinSCP, which helps to transfer the files between a local and a remote computer ( Ex. VCSA). I never have had issues with the ESXi hosts and WinSCP, however, trying to access the VCSA is a different story.
- Advertisement -

Recent Comments