Lab 4: Building Application & SQL Workloads (Jan-June 2015 Azure Hybrid Cloud IT Camps)

This post contains Lab 3 of the 5 labs created for our current set of US DX IT Camps.

The complete set of labs are listed here:

• Lab 1 – Building the Foundation (Virtual Network, Storage, and Cloud Service)
• Lab 2 – Building Workloads (Creating your DC and SQL Servers)
• Lab 3 – Working with Identity (Sync your domain to Azure AD, setup and test Multi-Factor Authentication)
• Lab 4 – Building Application and SQL Workloads (Install the Web server.  Configure your SQL Server database and connectivity from your Web Server)
• Lab 5 – Building Application Workloads (Install, configure, and a sample web application)
• Lab Appendix  (Installing AD by running a script on DC01, and installing Azure PowerShell Cmdlets)
• Lab 6 (Optional) – A SUSE Linux VM in Azure (Build, connect to, and add Ubuntu Desktop to a SUSE Linux VM)

Lab 4: Building Application & SQL Workloads

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

In this section you will create a new virtual machine to host 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 machine.

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.

1. On the Virtual machine configuration page, in CLOUD SERVICE, select itcservice<ID> .
2. In STORAGE ACCOUNT, select itcstore<ID>.
3. In REGION/AFFINITY GROUP/VIRTUAL NETWORK, verifity ITC-VNet is selected
4. In VIRTUAL NETWORK SUBNETS verify that AD-Production (192.168.10.0)/24 is selected.
5. Click the Next arrow.
6. On the Virtual machine configuration page, under Security Extensions, check Microsoft Antimalware.
7. Click the Complete icon.

Configure SQL Server System Defaults

While the web server is being created, let’s go setup some defaults for SQL Server. You would never want to store SQL Data on the system drive, so the first thing we will do is add an additional disk that will be used for holding the SQL Server Data. We will create a single simple drive but you could create multiple drives and use storage spaces as an alternative. See the Lab Appendixfor 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

Click the Check Mark button to createand attach the new virtual hard disk to virtual machine.

Now let’s connect a remote desktop session to SQL01

1. On the SQL01 virtual machine Dashboard tab, 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
2. Click No on the Networks Prompt to connect to other devices.

Now from the Remote Desktop console of SQL01we’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:

• F:\MSSQL
• F:\MSSQL\DATA
• F:\MSSQL\LOGS 
• F:\MSSQL\BACKUP

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

1. Inside Computer Management select Disk Management. An “Initialize Disk” window will pop up, make sure the new disk is selected and click OK.
2. 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:
3. 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.
4. 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.
5. 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.

• Click Start – Type SQL Server 2014 Management
• Right Click SQL Server 2014 Management Studio and select “Pin to Start”
• 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.

Now, you will update the database’s default locations for DATA, LOGS and BACKUP folders.

1. To do this, right click on your SQL Server instance Name SQL01 (upper left corner) and select Properties.

2. Select Database Settingsfrom the left side pane.

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

4. 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 from within an RDP connection to SQL01.

1. First you will need to disable IE Enhanced Security mode.

1. Open Server Manager, and then select Local Server.
2. Click the hyperlink labeled On next to IE Enhanced Security Configuration.
3. In the dialog box, select Off for Administrators.
4. Click OK.

• 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.
• Right Click each of the AdventureWorks files. Select Download. , Save As to store them in F:\MSSQL\Data.
• You should have already created the F:\MSSQL\Data folder.

In SQL01, open SQL Management Studio.

1. Click Connect.
2. In SQL Management Studio, click New Query (on the toolbar) .
3. 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\
4. Expand Databases, and then verify you have a new database named Test. (If you do not see it, Right Click on Databases, select Refresh)
5. In SQL Management Studio, right-click SQL01, and then click Properties.
6. In Server Properties, click Security.
7. Under Server authentication, click SQL Server and Windows Authentication mode, and then click OK.
8. In the dialog box, click OK.
9. Right-click SQL01, and then click Restart.
10. In the dialog box, click Yes.

1. Wait for the restart to complete.

• Under SQL01, expand Security, and then click Logins.
• Right-click Logins, and then click New Login.
• In Login name, type DataManagementApp.
• Click SQL Server authentication.
• In Password and Confirm password, type Passw0rd!
• Uncheck User must change password at next login.
• Click User Mapping.
• Check the database Test, and then in the Database role membership area, check DB_datareader and DB_Owner.
• 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.
• 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.

In your RDP session to SQL01, open Server Manager:

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

Confirm Connectivity to SQL01 From WEBFE01

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

Perform the following tasks from within an RDP connection on 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.

1. When prompted, log on as sysadmin using Passw0rd! as the password.
2. Click yes.

• In Server Manager, Click Local Server – turn off IE Enhanced Security Configuration
• In Server Manager, on the Tools menu, click Windows PowerShell ISE.
• On the View menu, click Show Scripting pane.
• 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.
• Click on the Folder on the task bar to open Computer. Double-Click Data (C:) Click Home | New Folder type AzureManagement press Enter
• Open a web browser Start – Internet Explorer. “Don’t use recommended settings” (for now) click OK
• 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
• NOTE: you can just click OK to any security warnings you get
• 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
  NOTE: The above URL is Case Sensitive!
• 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\
• 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.

1. NOTE : 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

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