Hands-on Lab: Build a SharePoint Dev-Test Farm in the Cloud (Part 5 – Configure SQL Server 2012 in a Windows Azure VM)


Windows Azure Infrastructure Services provides cloud-based storage, virtual networks and virtual machines that can be provisioned on-demand to support lab, pilot or production application workloads. In this multi-part Step-By-Step hands on lab format we will walk you through the entire process.  The introduction and index post can be found at: Hands-on Lab: Build a SharePoint Dev-Test Farm in the Cloud using Windows Azure Infrastructure Services (Part 0 – Introduction and Series Index)

Prior Step: Configure Windows Server Active Directory in a Windows Azure VM

Exercise 5: Configure SQL Server 2012 in a Windows Azure VM

Provision a new Windows Azure VM to run SQL Server 2012 by performing the following steps:

1)      Sign in at the Windows Azure Management Portal with the logon credentials used when you signed up for your Free Windows Azure Trial.
 

2)      Select Virtual Machines located on the side navigation panel on the Windows Azure Management Portal page.
 

3)      Click the +NEW button located on the bottom navigation bar and select
Compute | Virtual Machines | From Gallery.
 

4)      In the Virtual Machine Operating System Selection list, select SQL Server 2012 SP1 Enterprise on WS 2012 and click the  button.
 

5)      On the Virtual Machine Configuration page, complete the fields as follows:

Version Release Date: Select the latest version release date to build a new VM with the latest OS updates applied.
 
Virtual Machine Name: XXXlabdb01

Size: Large (4 cores, 7GB Memory)
 
New User Name: Choose a secure local Administrator user account to provision.

New Password and Confirm Password fields: Choose and confirm a new local Administrator password.

Click the  button to continue.
 

6)      On the Virtual Machine Configuration page, complete the fields as follows:
 
Cloud Service: Create a new cloud service

Cloud Service DNS Name: XXXlabfarm.cloudapp.net

Region/Affinity Group/Virtual Network: Select XXXlabnet01 – the Virtual Network defined in Exercise 3 above.

Virtual Network Subnets: Select Subnet-1 (10.0.0.0/23)
 
Storage Account: Select the Storage Account defined in Exercise 1 above.

Availability Set: Create an availability set
 
Availability Set Name: XXXlabdb
 
Click the
 button to continue.
 

7)      On the Virtual Machine Configuration – Endpoints page, click the  button to accept the default firewall endpoint values and begin provisioning the new virtual machine.
 
As the new virtual machine is being provisioned, you will see the Status column on the Virtual Machines page of the Windows Azure Management Portal cycle through several values including Stopped, Stopped (Provisioning), Starting, and Running (Provisioning).  This will take several minutes. When provisioning for this new Virtual Machine is completed, the Status column will display a value of Running and you may continue with the next step in this guide.
 

8)      After the new virtual machine has finished provisioning, click on the name (XXXlabdb01) of the new Virtual Machine displayed on the Virtual Machines page of the Windows Azure Management Portal.
 

9)      On the virtual machine Dashboard page for XXXlabdb01, make note of the Internal IP Address displayed on this page.  This IP address should be listed as 10.0.0.5
 
If a different internal IP address is displayed, the virtual network and/or virtual machine configuration was not completed correctly.  In this case, click the DELETE button located on the bottom toolbar of the virtual machine details page for XXXlabdb01, and go back to Exercise 2 and Exercise 3 to confirm that all steps were completed correctly.
 

1)      On the virtual machine Dashboard page for XXXlabdb01, click the Attach button 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: XXXlabdb01-data01
Size: 50 GB
Host Cache Preference: None

Click the  button to create and attach the new virtual hard disk to virtual machine XXXlabdb01.
 

10)   On the virtual machine Dashboard page for XXXlabdb01, click the Connect button located on the bottom navigation toolbar and click the Open button to launch a Remote Desktop Connection to the console of this virtual machine.  Logon at the console of your virtual machine with the local Administrator credentials defined in Step 5 above.
 

11)   From the Remote Desktop console of XXXlabdb01, create a new partition on the additional data disk attached above in Step 10 and format this partition as a new F: NTFS volume. After formatting this new volume, create the following folders:
 

a)      Create F:MSSQL folder
 

b)      Create F:MSSQLDATA folder
 

c)      Create F:MSSQLLOGS folder
 

d)      Create F:MSSQLBACKUP folder

Step-By-Step: Once inside Server Manager, go to Tools (upper right corner menu) then select Computer Management. Inside Computer Management select Disk Management.  An “Initialize Disk” window will pop up, make sure the new disk is selected and click OK. 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: Change the Volume Label to DATA and click Next: Click Finish.  
Once you see the new F: drive in the upper volume window you can close the computer management window and continue.

 

Step-By-Step: 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)

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

a)      Connect to the SQL Server 2012 default instance using your Windows Account.
 

b)      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 and select Properties.
 

c)      Select Database Settings from the left side pane.
 

d)      Locate the Database default locations section and update the default values for each path to point to the new folder paths defined above in Step 11.  Click OK

e)      Right-Click XXXlabdb01 and select Restart; if you go back into properties; you should see the change took place.

f)       Close SQL Server Management Studio.

13)   In order to allow SharePoint to connect to the SQL Server, you will need to add an Inbound Rule for the SQL Server requests in the Windows Firewall. To do this, open Windows Firewall with Advanced Security from Server Manager (Tools menu)

a)      Select Inbound Rules node, right-click it and select New Rule to open the New Inbound Rule Wizard.

b)      In the Rule Type page, select Port and click Next.

c)      In Protocols and Ports page, leave TCP selected, select Specific local ports, and set its value to 1433. Click Next to continue.

d)      In the Action page, make sure that Allow the connection is selected and click Next.

e)      In the Profile page, leave the default values and click Next.

f)       In the Name page, set the Inbound Rule’s Name to SQLServerRule and click Finish

g)      Close Windows Firewall with Advanced Security window.

14)   Using the Server Manager tool, join this server to the contoso.com domain and restart the server to complete the domain join operation. Step-By-Step: Server Manager | Local Server | WORKGROUP | Change… | Domain | contoso.com | OK. Enter your domain credentials (CONTOSOYourUserName) click OK then on Welcome to consoto.com click OK; on restart popup click OK; Click Close on System Properties then click Restart Now on the popup.

15)   After the server restarts, connect again via Remote Desktop to the server’s console and login with the local Administrator credentials defined above in Step 5.

16)   Open SQL Server Management Studio from the Start Screen type SQL click SQL Server Management Studio and then Connect (default credentials).  Then we need to add the CONTOSOYourUserName (defined when setting up XXXLabad01) user to SQL Server with the Sysadmin server role selected.

a)      Expand Security folder within the SQL Server instance. Right-click Logins folder and select New Login.

b)      In the General section, set the Login name to CONTOSOYourUserName, and select the Windows Authentication option. Click Search Click Locations… Enter your domain credentials (credentials you defined ealier) expand entire directory, click contoso.com click OK  enter the username in the “object name” field click Check Names if the name resolves correctly, click OK.

c)      Click Server Roles on the left pane.  Select the checkbox for the Sysadmin server role.

d)      Click the OK button and then close SQL Server Management Studio.

The configuration for this virtual machine is now complete, and you may continue with the next exercise in this hands-on lab guide.

Next Step: Configure SharePoint Server 2013 in a Windows Azure VM