Share via

Test Lab Guide: SQL Server 2014 in Azure

  • Article

This Microsoft Test Lab Guide (TLG) provides you with step-by-step instructions to create a server running SQL Server 2014 in the base configuration test lab in Azure or another Azure-based test lab. With the resulting test lab, you can add applications or services that require SQL Server.

Introduction

Test Lab Guides (TLGs) allow you to get hands-on experience with new products and technologies using a pre-defined and tested methodology that results in a working configuration. When you use a TLG to create a test lab, instructions tell you what servers to create, how to configure the operating systems and platform services, and how to install and configure any additional products or technologies. A TLG experience enables you to perform all of the configuration steps on both the front-end and back-end that go into a single- or multi-product or technology solution.

The purpose of this TLG is to enable you to create a server running SLQ Server 2014 as a virtual machine in the TestLab Azure Virtual Network as created with the Test Lab Guide: Base Configuration in Azure.

Important: The following instructions are for configuring a test lab. Individual computers are needed to separate the services provided on the network and to clearly show the desired functionality. This configuration is neither designed to reflect best practices nor does it reflect a desired or recommended configuration for a production network. The configuration, including IP addresses and all other configuration parameters, is designed only to work on this Azure-based test lab network.

Test lab overview

The SQL Server 2014 in Azure test lab consists of the following:

  • One Azure virtual machine named DC1 that is configured as an intranet domain controller and Domain Name System (DNS) server.
  • One Azure virtual machine named APP1 that is configured as a web and file sharing server.
  • One Azure virtual machine named CLIENT1 that will act as an intranet client.
  • One Azure virtual machine named SQL1 that is configured as a SQL server.

The SQL Server 2014 in Azure test lab is shown in Figure 1.


Figure 1  The SQL Server 2014 test lab in Azure

This configuration allows DC1, APP1, SQL1, and CLIENT1 to be:  

  • Connected to the Internet to install updates, access Internet resources in real time, and participate in public cloud technologies such as Microsoft Office 365 and other Azure services.
  • Remotely managed using Remote Desktop Connections by your computer, which is connected to the Internet or your organization network.

There are two steps to setting up the SQL Server 2014 test lab in Azure.

  1. Create the Base Configuration in Azure.
  2. Configure SQL1.

Note You must be logged on as a member of the Domain Admins group or a member of the Administrators group on each computer to complete the tasks described in this guide. If you cannot complete a task while you are logged on with an account that is a member of the Administrators group, try performing the task while you are logged on with an account that is a member of the Domain Admins group.

Important: The instructions for configuring the computers of the Corpnet subnet of the SQL Server 2014 test lab are designed to be simple and require as few computers as possible. In some cases, servers provide multiple roles that would normally be placed on different servers. This configuration is neither designed to reflect best practices nor does it reflect a desired or recommended configuration for a production network.

If you do not already have an Azure subscription, you can sign up for a free trial at Try Azure. If you have an MSDN Subscription, see Microsoft Azure Special Pricing: MSDN, MPN, and Bizspark Benefits.

Ongoing costs of test lab virtual machines in Azure

Virtual machines in Azure incur an ongoing monetary cost when they are running. This cost is billed against your free trial, MSDN subscription, or paid Azure subscription. For more information about the costs of running Azure virtual machines, see Virtual Machines Pricing Details and Azure Pricing Calculator.

To minimize the cost of running the test lab virtual machines, you can do one of the following:

  • Create the test lab and perform your needed testing and demonstration as quickly as possible. When complete, delete the test lab virtual machines from the Virtual Machines page of the Azure Management Portal.
  • Shut down your test lab virtual machines into a deallocated state from the Virtual Machines page of the Azure Management Portal. However, you should restart your virtual machines in a specific order. For more information, see Starting the test lab virtual machines after being shut down.

Step 1: Create the Base Configuration in Azure

Set up the Base Configuration test lab for the Corpnet subnet using the instructions in Test Lab Guide: Base Configuration in Azure.

Step 2: Configure SQL1

SQL1 will act as a database server for the Corpnet subnet. Its configuration consists of the following:

  • Create an Azure Virtual Machine for SQL1.
  • Join SQL1 to the CORP domain.
  • Configure the Windows Firewall for an inbound rule.

Create an Azure Virtual Machine for SQL1

  1. In the task bar of the Azure Management Portal, click NEW > COMPUTE > VIRTUAL MACHINE > FROM GALLERY.
  2. On the Choose an Image page, click SQL Server 2014 RTM Enterprise-Windows Server 2012 R2, and then click the Next arrow.
  3. On the Virtual machine configuration page:
    • In VIRTUAL MACHINE NAME, type SQL1.
    • In SIZE, select A3.
    • In NEW USER NAME, type the name of a local administrator account. You will use this account when initially connecting and as an alternate set of credentials when SQL1 joins the CORP domain. Choose a name that is not easy to guess.
    • In NEW PASSWORD and CONFIRM, type a strong password for the local administrator account.
    • Record the local administrator account name and password.
  4. Click the Next arrow.
  5. On the second Virtual machine configuration page:
    • In CLOUD SERVICE, select the cloud service that you created for your test lab.
    • In STORAGE ACCOUNT, select the storage account for the TLG virtual machines (VMs) and extra disks.
  6. Click the Next arrow.
  7. On the third Virtual machine configuration page, click the complete icon. Wait until Azure creates the virtual machine.
  8. On the virtual machines page of the Azure Management Portal, click Running in the STATUS column for the SQL1 virtual machine.
  9. In the task bar, click CONNECT.
  10. When prompted to open SQL1.rdp, click Open.
  11. When prompted with a Remote Desktop Connection message box, click Connect.
  12. When prompted for credentials, use the following:
    • Name: SQL1\[Local administrator account name] (from step 3)
    • Password: [Local administrator account password] (from step 3)
  13. When prompted with a Remote Desktop Connection message box referring to certificates, click Yes.

To check name resolution and network communication between SQL1 and DC1, run the ping dc1.corp.contoso.com command at a Windows PowerShell command prompt and verify that there are four replies.

Join SQL1 to the CORP domain

Run the following commands at the Windows PowerShell command prompt:

 Windows PowerShell commands

Add-Computer -DomainName corp.contoso.com

Restart-Computer


Note that you must supply your CORP\User1 account domain credentials after entering the Add-Computer command.

To reconnect to the SQL1 virtual machine, do the following:

  1. After SQL1 restarts, on the virtual machines page of the Azure Management Portal, click Running in the STATUS column for the SQL1 virtual machine.
  2. In the task bar, click CONNECT.
  3. When prompted to open SQL1.rdp, click Open.
  4. When prompted with a Remote Desktop Connection message box, click Connect.
  5. When prompted for credentials, use the following:
    • Name: SQL1\[Local administrator account name]
    • Password: [Local administrator account password]
  6. When prompted by a Remote Desktop Connection message box referring to certificates, click Yes.
  7. From the Start screen, type SQL Server Management, and then click SQL Server Management Studio.
  8. In Connect to Server, click Connect.
  9. In the Object Explorer tree pane, open Security.
  10. Right-click Logins and then click New Login.
  11. On the General page, in Login name, type CORP\User1.
  12. On the Server Roles page, click sysadmin, and then click OK.
  13. Close the Remote Desktop connection window for SLQ1.
  14. On the virtual machines page of the Azure Management Portal, click Running in the STATUS column for the SQL1 virtual machine.
  15. In the task bar, click CONNECT.
  16. When prompted to open SQL1.rdp, click Open.
  17. When prompted with a Remote Desktop Connection message box, click Connect.
  18. When prompted for credentials, use the following:
    • Name: CORP\User1
    • Password: [User1 account password]
  19. When prompted by a Remote Desktop Connection message box referring to certificates, click Yes.

Configure Windows Firewall for an inbound rule

From the desktop, right-click the Windows PowerShell icon, click Run as Administrator, and then click Yes when prompted. Run the following command at the Windows PowerShell command prompt:

 Windows PowerShell commands

New-NetFirewallRule -DisplayName “SQL Server” -Direction Inbound –Protocol TCP –LocalPort 1433-1434,5022 -Action allow

Your SQL Server 2014 test lab in Azure is now ready for your experimentation or for the installation of additional applications or services that use Microsoft SQL Server 2014.

For additional Azure TLGs to use with this configuration, see the Azure Test Lab portal page.

Additional Resources

For more information about Azure infrastructure services, see Infrastructure Services | Azure.

To provide the authors of this guide with feedback or suggestions for improvement, send email to tlgfb@microsoft.com.

We strongly encourage you to develop and publish your own TLG content, either in the TechNet Wiki (example: Test Lab Guide: Demonstrate Remote Access VPNs) or in your own publishing forum (example: Test Lab Guide (Part 1) - Demonstrate TMG PPTP, L2TP/IPsec and SSTP Remote Access VPN Server). See Creating and Publishing Test Lab Guide Content for information about the types of content you can create and links to templates, guidance, and examples.

For a list of additional Microsoft TLGs, see Test Lab Guides in the TechNet Wiki.

Appendix

This appendix describes how to start the test lab virtual machines after being shut down.

Starting the test lab virtual machines after being shut down

To ensure that your virtual machines work properly when starting all of them from the Stopped (Deallocated) state, start them in the following order:

  1. DC1
  2. APP1
  3. SQL1
  4. CLIENT1

To start a virtual machine from the Virtual Machines page of the Azure Management Portal, click Stopped next to the virtual machine name, and then click Start in the task bar.