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.

4. Use the domain controller server credentials.

5. Reboot the VM

@Domain Controller Server

6. 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).

3. Install SQL Server 2017

@ SQL Server VM

1. Download SQL Server 2017, here.
2. Let’s use the Developer edition.

3. Double-click in the application SQL file to start the installation.

4. From installation type select Custom

5. Select media download target (Just leave as default).

6. The download will begin. This will take some time, after this, the installation wizard will start.

7. From SQL Server Installation Center, select Installation.

8. Select: “New SQL Server stand-alone installation or add features to an existing installation”

9. Check Specify a free edition and choose “Developer”

10. Accept license terms.

11. Decide whether or not to use Microsoft Update

12. Review Install Rules and make corrections if necessary.

13. Select Database Engine Services. Only this feature is necessary for our vSphere environment.

14. Specify a name and instance ID. I am using “VM” for both options.

15. Leave as default for service account configurations.

16. In Authentication Mode select Mixed Mode, enter a password and then click to Add Current User.

17. Click Install.

18. The installation shouldn't take long.

19. Review the installation and features status and click Next.

4. Install SQL Server Management Tools

1. Still from SQL Server Installation Center, select: “Install SQL Server Management Studio”

2. 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.

3. Go to Downloads, double-click SSMS application and click Run.

4. From Microsoft SQL Server Management Studio wizard, click Install.

5. Wait for the installation to be completed.

6. Click Close to finish the installation wizard.

7. Click Start and find Microsoft SQL Server Management Studio.

8. 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.

9. Type Login and Password credentials and check Remember Password. Click Connect.

11. We now have access to the database server.

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...

2. 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.

5.2 Create vCenter user

1. Got to the Security folder and Right-click Logins, click New Login...

2. 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.

3. The domain controller will ask for credentials.

4. From General, select Windows authentication, click OK.

5. 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!

6. Right-click on the database instance and select Properties.

7. Go to Permissions, select the vCenter user and check following permissions: View any definition and View server state. Click OK.

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

2. Apply same permissions and user mappings to these users as was done for the vCenter user, following their respective user/database.

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.

6. Important Configurations

1. Start SQL Server Browser service, by default is Disabled.

2. 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
3. Go to Start / SQL Server Configuration Manager.

4. 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.

5. 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.

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