Share via

IT-Camp Azure Labs – Lab 4 – Building Applications with SQL Workloads – Step by Step

  • Article

Lab 4 Building Application & SQL Workloads

Create a new web server virtual machine from the Microsoft Azure management portal

You will create a new virtual machine to run the web application. You can create this VM using quick create; however, that will not enable you to specify the service or storage, and will create separate storage and services for this VM. You will use the gallery option to ensure you can specify the storage and services for the VM.

Perform the following tasks in the Azure management portal:

  1. Click VIRTUAL MACHINES located on the left menu of the Azure management portal.

  2. Click +New to CREATE A VIRTUAL MACHINE.

  3. Click COMPUTE, click VIRTUAL MACHINE, and then click FROM GALLERY.

  4. In Choose an Image, click Windows Server 2012 R2 Datacenter, and then click the Next arrow.

  5. Create a new virtual machine using the values in the following table, and then click the Next arrow.
    -

    <table>
<tbody>
<tr class="odd">
<td><strong>Property</strong></td>
<td><strong>Value</strong></td>
</tr>
<tr class="even">
<td>VIRTUAL MACHINE NAME</td>
<td>WEBFE01</td>
</tr>
<tr class="odd">
<td>TIER</td>
<td>Standard</td>
</tr>
<tr class="even">
<td>SIZE</td>
<td>A2</td>
</tr>
<tr class="odd">
<td>NEW USER NAME</td>
<td>SysAdmin</td>
</tr>
<tr class="even">
<td>NEW PASSWORD and CONFIRM</td>
<td>Passw0rd!</td>
</tr>
</tbody>
</table>

  6. On the Virtual machine configuration page, in CLOUD SERVICE, select itcservice<ID> .

  7. In STORAGE ACCOUNT, select itcstore<ID>.

  8. In REGION/AFFINITY GROUP/VIRTUAL NETWORK, verifity ITC-VNet is selected

  9. In VIRTUAL NETWORK SUBNETS select AD-Production-Static (192.168.11.0)/24,

  10. Click the Next arrow.

  11. On the Virtual machine configuration page, under Security Extensions, check Microsoft Antimalware.

  12. Click the Complete icon.

    • The virtual machine will take a few minutes to create. Depending on the load this may take between 5 and 25 minutes.
    • Wait for the new virtual machine to finish before proceeding.

Configure SQL Server System Defaults

While the web server is being created, let’s go setup some defaults for SQL Server. You would never store SQL Data on a boot drive so the first thing we will do is add an additional drive that will be used for the SQL Data. We will create a single simple drive but you could create multiple drives and use storage spaces as an alternative. See Appendix Labs for details.

Perform the following tasks in the Azure management portal.

  1. In the Azure management portal, click VIRTUAL MACHINES.

  2. Click SQL01. Click Dashboard. On the virtual machine Dashboard page for SQL01, click the Attach button (chain icon) located on the bottom navigation toolbar and select Attach Empty Disk.  Complete the following fields on the Attach an empty disk to the virtual machine form:

    File Name: sql01-sql01data

    Size : 50 GB

    Host Cache Preference: None

  3. Click the Check Mark button to create and attach the new virtual hard disk to virtual machine.

  4. On the virtual machine Dashboard page for SQL01, click the Connect button located on the bottom toolbar (far left icon) and click the Open button to launch a Remote Desktop Connection to the console of this virtual machine. Click Use another account to login at the console of your virtual machine with the local credentials defined above. Follow the prompts to continue connecting
    -

    <table>
<tbody>
<tr class="odd">
<td><strong>Property</strong></td>
<td><strong>Value</strong></td>
</tr>
<tr class="even">
<td><strong>USER NAME</strong></td>
<td>SysAdmin</td>
</tr>
<tr class="odd">
<td><strong>NEW PASSWORD</strong></td>
<td>Passw0rd!</td>
</tr>
</tbody>
</table>

  5. Perform the following tasks in an RDP connection to SQL01.

  6. Now from the Remote Desktop console of SQL01 we’ll create a new partition on the additional data disk attached above and format this partition as a new F: NTFSvolume. After formatting this new volume, you’ll create following folders:

    1. F:\MSSQL
    2. F:\MSSQL\DATA
    3. F:\MSSQL\LOGS
    4. F:\MSSQL\BACKUP

  7. Click Yes on the Networks Prompt to connect to other devices.

  8. Once inside Server Manager, go to Tools (upper right corner menu) then select Computer Management.

  9. Inside Computer Management select Disk Management. An “Initialize Disk” window will pop up, make sure the new disk is selected and click OK.

  10. Right click unallocated space on Disk 2 and select “New Simple Volume… ” Click Next: then Next for the Specify Volume Size. The drive letter should be preconfigured to “F”, click Next:

  11. Change the Volume Label to DATA and click Next: Click Finish.

    NOTE: If you get a Microsoft Windows popup asking you if you want to format, you can just close it (we are already formatting the disk). Once you see the new F: drive in the upper volume window you can close the computer management window and continue.

  12. Click on the Folder on the task bar to open Computer. Double-Click Data (F:) Click Home | New Folder type MSSQL press Enter. Press Enter again to drill down to the MSSQL folder then repeat the process to create the remaining folders (DATA; LOGS; BACKUP) You can then close the computer window and the Server Manager window to continue.

  13. Open SQL Server Management Studio from the Start Screen and update default folder locations to the F: volume.

    Tip! On the Windows Start Screen, you can quickly find the application tile for SQL Server Management Studio beginning to type the name of this application to automatically search for matching tiles.

    1. Click Start – Type SQL Server 2014 Management

    2. Right Click SQL Server 2014 Management Studio and select “Pin to Start”

    3. Click SQL Server 2014 Management Studio on the start screen to launch the application. Connect to the SQL Server default instance using your Windows Account by clicking Connect on the Connect to Server window.

    4. Now, you will update the database’s default locations for DATA, LOGS and BACKUP folders. To do this, right click on your SQL Server instance Name SQL01 (upper left corner) and select Properties.

    5. Select Database Settings from the left side pane.

    6. Locate the Database default locations section and update the default values for each path to point to the new folder paths created above. Click OK

    7. Right-Click SQL01 and select Restart; In the popup that asks Are you Sure, select Yes. if you go back into properties; you should see the change took place.

      Close SQL Server Management Studio.

Import and configure the testing database on SQL01

In this task, you will import the testing database provided by your development team. You will then create a user account that will be used by the web front end to access the data in the database.

Perform the following tasks in an RDP connection to SQL01.

  1. First you will need to disable IE Enhanced Security mode.
  2. Open Server Manager, and then select Local Server.
  3. Click the hyperlink labeled On next to IE Enhanced Security Configuration.
  4. In the dialog box, select Off for Administrators.
  5. Click OK.
  6. In SQL01, use Internet Explorer In the Security popup, select Don’t use recommended settings for now then click OK; Navigate to https://1drv.ms/1qFeJ2g .
  7. Right Click each of the AdventureWorks select Download. Download, Save As to store them in F:\MSSQL\Data.
    • You should have already created the F:\MSSQL\Data folder.
  8. In SQL01, open SQL Management Studio.
  9. Click Connect.
  10. In SQL Management Studio, click New Query (on the toolbar) .
  11. In the Query Editor, type the following query, and then press CTRL+E.(or click Execute button)
    • Create Database Test on (filename = ‘F:\MSSQL\Data\adventureworks_data.mdf’), (filename = ‘F:\MSSQL\Data\adventureworks_log.ldf’) for attach
  12. Expand Databases, and then verify you have a new database named Test. (If you do not see it, Right Click on Databases, select Refresh)
  13. In SQL Management Studio, right-click SQL01, and then click Properties.
  14. In Server Properties, click Security.
  15. Under Server authentication, click SQL Server and Windows Authentication mode, and then click OK.
  16. In the dialog box, click OK.
  17. Right-click SQL01, and then click Restart.
  18. In the dialog box, click Yes.

Wait for the restart to complete.

  1. Under SQL01, expand Security, and then click Logins.
  2. Right-click Logins, and then click New Login.
  3. In Login name, type DataManagementApp.
  4. Click SQL Server authentication.
  5. In Password and Confirm password, type Passw0rd!
  6. Uncheck User must change password at next login.
  7. Click User Mapping.
  8. Check the database Test, and then in the Database role membership area, check DB_datareader and DB_Owner.
  9. Click Script at the top of the dialog to see what the Powershell would be to create the user and set permissions. It will be displayed in a tab behind the dialog.
  10. Click OK.

Configure firewall ports for SQL01

Next, you must enable WEBFE01and SQL01 to communicate internally within the service. While general IP connectivity is provided by DHCP, both servers are workgroup members and have the public firewall profile enabled. You will enable SQL Server traffic and PING traffic inbound on SQL01.

Perform the following tasks in an RDP connection to SQL01.

  1. In your RDP session to SQL01, open Server Manager.
  2. Click Local Server.
  3. Next to Windows Firewall, click Public: On.
  4. In Windows Firewall, click Advanced settings.
  5. In Windows Firewall with Advanced Security, click Inbound Rules, and then click New Rule.
  6. In Rule Type, click Port, and then click Next.
  7. In Specific local ports, type 1433, and then click Next.
  8. On the Action page, click Next.
  9. On the Profile page, click Next.
  10. In Name, type Allow SQL 1433, and then click Finish.
  11. In Windows Firewall with Advanced Security, click Inbound Rules, and then click New Rule.
  12. In Rule Type, click Custom, and then click Next.
  13. On the Program page, click Next. (All programs should be selected)
  14. On the Protocol and Ports page, in Protocol type, select ICMPv4, and then click Next.
  15. On the Scope page, click Next.
  16. On the Action page, click Next.
  17. On the Profile page, click Next.
  18. In Name, type PING, and then click Finish.
  19. Disconnect from the SQL01 RDP session.

Confirm Connectivity to SQL01 From WEBFE01

Next, let’s make sure we can successfully connect to SQL01 from our Web Server.

Perform the following tasks in an RDP connection to WEBFE01

  1. In the Azure management portal, click VIRTUAL MACHINES, click WEBFE01, and then click Dashboard.

  2. On the bottom bar, click CONNECT, and then click Open.

  3. Click Connect.

  4. When prompted, log on as sysadmin using Passw0rd! as the password.

  5. Click yes.

  6. On WEBFE01, in Server Manager, Click Local Server – turn off IE Enhanced Security Configuration

  7. On WEBFE01, in Server Manager, on the Tools menu, click Windows PowerShell ISE.

  8. On the View menu, click Show Scripting pane.

  9. In the Command pane, type PING SQL01, and then press ENTER.

    • The ping results indicate you can connect to SQL01 using the internal network provided by your service which contains the VMs.

  10. Click on the Folder on the task bar to open Computer. Double-Click Data (C:) Click Home | New Folder type AzureManagement press Enter You can then close the computer window and the Server Manager window to continue.

  11. Open a web browser Start – Internet Explorer. “Don’t use recommended settings” (for now) click OK

  12. You need to Add the site https://itcmaster.blob.core.windows.net to your trusted sites. In Internet Explorer – Click Tools (Gear in upper right corner) – Internet Options – Security Tab – Trusted Sites – Sites – Type: https://itcmaster.blob.core.windows.net then click Add – Close – OK

  13. NOTE: you can just click OK to any security warnings you get

  14. Download https://itcmaster.blob.core.windows.net/fy15q3/AzureManagement.zip by typing the URL into the address bar on your WEBFE01 server. Click Save as then save to C:\AzureMangement Folder

  15. Using File Explorer Open the c:\AzureManagement folder, right-click on the AzreManagement.zip file; select Extract All Change the path to C:\ then click Extract

    Close “Local Disk (C:) window. You should have a window up still that is showing you C:\AzureManagement\

  16. Open with notepad and copy the contents of the C :\AzureManagement\Test Database Connectivity.txt (Test Database Connectivity) file to your clipboard, and then on WEBFE01, in Windows PowerShell ISE paste in the Script pane.

  17. NOTICE : If you changed the computer name, username or password you will have to change the script to change the defaults at the top of the script

  18. Click the play button, or press F5 to run the script.

    • The output of the script is a small set of system data which indicates you can communicate with the SQL Server instance on SQL01.

POWERSHELL Deep Dive:

All tasks performed on the SQL Server in this lab can be done using PowerShell. If you want to see what it would look like check out: https://itproguru.com/downloads/SQLProvisionScript.txt

To Add the Disk to the Azure VM you could run the command:

Set-AzureSubscription –SubscriptionName “<YourSubscriptionName>” -CurrentStorageAccount itcstoredan01

Get-AzureVM “ITCService<YourID>” -Name “SQL01” | Add-AzureDataDisk -CreateNew -DiskSizeInGB 50 -DiskLabel ‘Data’ -LUN 0 | Update-AzureVM

You do have to have the Azure PowerShell Module installed and PowerShell connected to your Azure Account (In Lab 5 we show you how!)

Continue on to Lab 5!

Lab Home Page Includes DOCX and PPTX

Description and Live links to Series