Select Page

Building a SQL Server Virtual Lab in Windows: Test the Virtual Lab

Author: JP Chen | | July 22, 2020

In this final blog post of the series, we will test the virtual lab by registering all the SQL Server instances and then running a multi-server query to query all SQL Server instances. Then, we will wrap-up the testing by creating a shared folder in DV-SQL01 and then access it in both DV-SQL02 and DV-SQL03.

Registering all the SQL Server instances in DV-SQL01

Like creating an account with a website which stores your user account details so that you won’t have to key them in again the next time, registering a server in SSMS stores the server connection details for future connections so that you don’t need to type them in again.

In this section, we will create a server group and then register all the SQL Servers in the virtual lab.

To register all the SQL Servers in DV-SQL01:

  1. Logoff the current logged on local administrator user.
  2. Logon to DV-SQL01 using the DV-SQLNET\JBauer domain user account.
  3. Open SSMS.
  4. Type DV-SQL01 in the Server name text box.
  5. Click Connect.
    This will connect you to the DV-SQL01 default instance.
  6. Click the View menu on SSMS.
  7. Click Registered Servers on the drop-down menu.
  8. Expand Database Engine.
  9. Right-click Local Server Groups.
  10. Click New Server Group… on the drop-down menu.
  11. Type Virtual Lab SQL Servers in the Group name text box.
  12. Click OK.
  13. Right-click the Virtual Lab SQL Servers folder.
  14. Click New Server Registration… on the drop-down menu.
  15. Type DV-SQL01 in the Server name text box.
  16. Click SAVE.
  17. Repeat steps 13 to 16 to register DV-SQL02 to the Virtual Lab SQL Servers server group.
  18. Repeat steps 13 to 16 to register DV-SQL03 to the Virtual Lab SQL Servers server group.
    As shown in Figure 2-41 Creating a New Server Group and Registering SQL Servers, you will find the three instances of SQL Servers registered under the Virtual Lab SQL Servers server group.

    Figure 2-41 Creating a New Server Group and Registering SQL Servers
     
    This completes creating the Virtual Lab SQL Servers server group and registering DV-SQL01, DV-SQL02, and DV-SQL03. Next, we will run a multi-server query to query all the SQL Server instance in the Virtual Lab SQL Servers server group.

Running a Multi-Server Query to Query all SQL Server Instances

If you have a script and you need to run it against multiple SQL Servers, would you rather run it once again all servers or choose to run it one at a time for each of the SQL Servers? Most likely, you would like the former option. Multi-server query allows you to do just that and the results returned by the query can be combined into a single results pane. The results set will include one additional column identifying the server name for each server.

To run a multi-server query to query all the instances in the Virtual Lab SQL Servers server group:

  1. Right-click on the Virtual Lab SQL Servers server group.
  2. Click New Query on the pop-up menu.
    Notice the SSMS status bar turns into pink color indicating that it is a multi-server query.
  3. Run the following Querying the Server Properties.sql script to query the server properties for all the registered SQL Servers in the Virtual Lab SQL Servers server group:

    01  -- Querying the Server Properties
    02  SELECT
    03  SERVERPROPERTY('ServerName') AS [Instance Name],
    04  SERVERPROPERTY('ProductVersion') AS [Version Build],
    05  SERVERPROPERTY ('Edition') AS [Edition],
    06  SERVERPROPERTY('ProductLevel') AS [Service Pack],
    07  CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
    08      WHEN 0 THEN 'SQL Server and Windows Authentication mode'
    09      WHEN 1 THEN 'Windows Authentication mode'
    10  END AS [Server Authentication],
    11  CASE SERVERPROPERTY('IsClustered')
    12      WHEN 0 THEN 'False'
    13      WHEN 1 THEN 'True'
    14  END AS [Is Clustered?],
    15  [cpu_count] AS [CPUs],
    16  [physical_memory_kb]/1024 AS [RAM (MB)]
    17     FROM [sys].[dm_os_sys_info]

    Script 2-4 Querying the Server Properties

  4. Notice the output of Figure 2-42 Output of the Multi-Server Query shows all the server properties for the three SQL Server instances registered in the Virtual Lab SQL Servers server group:


    Figure 2-42 Output of the Multi-Server Query

Creating a Share Folder and Accessing It from All Servers

Creating a share folder allows you to share and transfer files between servers. In this section, we will create a folder in DV-SQL01 and then test accessing it in DV-SQL02 and DV-SQL03.

Note: In a Production environment, as part of the change control process, you will need to make a service request for the Windows Administrators to create the share folder and grant the permissions to the specific user accounts.

To create a folder and share it in DV-SQL01 then test accessing in DV-SQL02 and DV-SQL03:

  1. Logon to DV-SQL01 as JBauer.
  2. Press Win+E on your keyboard.
  3. Click This PC on the left pane.
  4. Click Local Disk C:\.
  5. Create a folder and name it 24×7.
  6. Right-click on the 24×7 folder.
  7. Click Properties on the pop-up menu.
  8. Click the Sharing tab as shown in Figure 2-43 Sharing a Folder.
  9. Click Share…
    You can add the domain users or groups you wish to grant access to this folder and the permission level.
  10. Click Share.
     

    Figure 2-43 Sharing a Folder
  11. Type Administrator in the User name text box.
  12. Press Tab on your keyboard.
  13. Type Password$ in the Password text box.
  14. Press Enter on your keyboard.
    You will see the Network access message box notifying you that the folder \\DV-SQL01\24×7 is shared.
  15. Click Done.
  16. Click Close.
  17. Logon to DV-SQL02 with the JBauer domain user.
  18. Press Win+R on your keyboard.
  19. Type \\dv-sql01\24×7 in the Open text box.
  20. Press Enter on your keyboard.
  21. Create a text file and name it DV-SQL02.
    You can create this file because you are logged on as JBauer and you have Owner permission.
  22. Logon to DV-SQL03 with the JBauer domain user.
  23. Press Win+R on your keyboard.
  24. Type \\dv-sql01\24×7 in the Open text box.
  25. Create a text file and name it DV-SQL03.
  26. Go back to DV-SQL01.
  27. Open the 24×7 As shown in Figure 2-44 Checking the 24×7 Folder, you will notice the text files created in DV-SQL02 and DV-SQL03.
     

    Figure 2-44 Checking the 24×7 Folder

 

This completes the testing of the virtual lab.

You have now completed setting up the virtual lab for SQL Server 2019 HADR testing. You can repeat these steps in later days when you need to re-create the virtual lab for further testing. Thanks for tuning in for my blog series. You can find all of the blog posts below if you missed anything.

If you’re looking for SQL Server support, please reach out.

Virtualization Concepts

Virtual Network for the Virtual Lab

Software & Hardware Requirements

Installing Oracle VM VirtualBox and Creating a Virtual Network

Creating the Domain Controller (DC) VM

Create VMs for the SQL Servers

Configuring VMs for the SQL Servers

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.

Vijay Muthu | February 4, 2021

MongoDB Best Practices: Design, Deployment & More

This post provides a rundown of best practices to use when running MongoDB.

Esayas Aloto | February 28, 2017
sharepoint ideas

Using SharePoint: 9 Awesome Things You Can Do With SharePoint

Become familiar with these 9 uses for SharePoint that you can execute in just a few clicks without using any code. Learn more about using SharePoint here!

Amol Gharat | May 23, 2017

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS