Hello again,

Well now that the dust has settled it’s time to seriously blog again. In any event, as some of you may know I have changed roles and I’m now a Technical Evangelist for Windows Azure and SQL Server / SQL Database.  What I’ve learned in this new role, is that this is a lot to get across. So I figured I’d blog as much about how to learn this stuff as I could. Two kill two birds with one stone I plan to do any and all practices, samples, development and labs in Windows Azure. I will use the IaaS Virtual Machines where necessary, and standard Windows Azure features everywhere else.

To attain SQL mastery, one should follow the certification paths for MCSA SQL Server, MCSE Data Platform, and MCSM Data Platform.  Starting with the MCSA SQL Server we have three exams: 70-461 – Querying Microsoft SQL Server, 70-462 Administering Microsoft SQL Server 2012 Databases, and  70-463 Implementing a Data Warehouse with Microsoft SQL Server 2012.   The interesting part is that for the first exam 70-461 you need databases set up, which you learn how to do for the second exam 70-462. So I’m going to talk about the second exam first.

Setting up Labs in Windows Azure for Exam 70-462 Administering Microsoft SQL Server 2012 Databases

To learn the material for this exam, I am using the MS Press study guide: Training Kit (Exam 70-462): Administering Microsoft SQL Server 2012 Databases (hereafter referred to as ‘the book’). In the introduction of the book, the authors have provided setup instructions for configuring the lab servers. However, the configuration is for local Hyper-V installations based on Windows Server 2008 R2. What I want to do so that I can study and do the labs from anywhere and on any device, is set up this environment entirely in Windows Azure.  There are a couple key things you need to consider when doing this.

  1. Ideally your Windows Azure account needs to be able to support up to 9 cores to run all of the VMs in the book. If you follow the Install a new Active Directory forest in Windows Azure tutorial it recommends a Large instance to support the two attached drives which means 4 cores for the DC, and 1 for each of the 5 VMs you will create. You can get by with 7 cores but your DC performance may suffer a bit. Keeping in mind that this is a training course, and the DC actually won’t break a sweat, it’s probably not a big deal to stack everything onto a small instance with one core and one attached disk.  You can’t use Extra Small instances because they only have 756MB of RAM. The minimum supported by SQL is 1GB.
  2. You will need to set up a Windows Azure Virtual Network. This tutorial (Create a New Virtual Network in Windows Azure) can walk you through that.  
    CAUTION: Read the book first to find out what the default IP addresses are before you set up your network or you will constantly be translating what you set up versus what the book has written down. In the book, the IP Addresses are all 10.10.10.xx The Windows Azure Virtual Networks adhere to RFC 1918 for private address ranges.  “Address space must be a private address range, specified in CIDR notation 10.0.0.0/8, 172.16.0.0/12, or 192.168.0.0/16 (as specified by RFC 1918).” Since all of the lab VMs will be in the 10.10.10.x subnet, we can set our address range to 10.10.10.0/8

    Another important point is to understand how Windows Azure DNS works for your requirements. In most cases, the built in DNS will work fine. However, if you determine that you want your own DNS server, such as the DC DNS as described in the book, you MUST set up this DNS server first, then configure the Windows Azure Virtual Network, and point the DNS entry to your server. If you do not do this, you cannot change it later! (at least not in this preview release). That being said, you can still manually configure the servers in your virtual network to use your server for DNS, you just have to configure the properties of the IP configuration as shown in the image below:     

    image
    In my case, I set things up on 10.0.0.x subnet rather than the 10.10.10.xx one in the book more out of habit than anything else. Note that I also changed the Advanced settings to add the contoso.com DNS suffix to the connection. You can do this manually, but it will be added once the machine is joined to the domain anyway. I did it here to ensure my network config was more complete.
  3. You will start building your virtual machines from the Gallery in Windows Azure. The first one you build will be your Domain Controller. The book wants you to name this SQL-DC but Windows Azure won’t like this name due to the hyphen. You can try SQLDC, but when you get to the internet accessible name in step 3 of the VM setup, chances are SQLDC.cloudapp.net will already be taken (especially if you aren’t the first person to read this post Smile ) I tend to prefix my machine names with my initials or something to make them all work. For example mine is called RHSQLDC instead. I use this as the Windows Azure VM Name and for the rhsqldc.cloudapp.net URL name. Also, I strongly suggest you change the default password from the one in the book Pa$$w0rd to something more unique, otherwise someone else can log in to your VMs if they read this post, or the book. You don’t want that.
    TIP: To send Ctrl+Alt+Del across Remote Desktop to change a password, press Ctrl+Alt+End
    4.      
  4. You will then need to set up your DC. This tutorial: Install a new Active Directory forest in Windows Azure (hereafter referred to as ‘the tutorial’) can help you do that.
    CAUTION: Again, read through the DC configuration in the book. Replace the information in the tutorial, such as domain name, and IP address scheme with the information from the book.
    GOTCHA: The book says to run the following command:
      netsh interface ipv4 set address “Local Area Connection” static 10.10.10.10
    (page xix, step 2) DO NOT RUN THIS COMMAND! This won’t work on a Windows Azure VM because the accessible NIC is not called that, and most importantly, if you change the IP Address, you will no longer be able to connect to the machine through RDP.
    SOLUTION: Leave the IP Addresses alone. This is fine because the IP address assigned to the VM will remain the same for the life of the VM. You will need to change your instructions in the book from the 10.10.10.10 address for the DC to whatever was assigned to your machine in Windows Azure. Just write down the IP addresses assigned to your machines. They will likely be sequential in the order you set them up starting with the DC at 10.10.10.4 and moving to 10.10.10.5, .6, .7, etc. In my case with my 10.0.0.x address range, my DC was set up with 10.0.0.4 and survives reboots just fine.
  5. Continue to follow the tutorial with the Contoso.com FQDN from the book.
  6. When you get to step where you configure data files, do NOT accept the defaults like the book says. You will install them on the disk you attached in the tutorial Step 1.3 and 1.4. Here is a screen shot of what I set up for my locations. This will ensure persistence and the data redundancy provided by Azure are in full effect for your system.
    image

 

At this point, you can follow the tutorial through backing up your new DC. Then proceed with the book from page xx Prepare AD DS.

Where the book starts having you add member servers to the domain, (page xx Prepare a Member Server and Join It to the Domain) you can create Windows Server 2008 R2 SP1 editions from the Virtual Machine Gallery. Don’t be tempted by the Microsoft SQL Server 2012 Evaluation images! If you do that, you won’t get to do the SQL Server install exercises in the book. Not to mention, they may not come out configured correctly.

image

 

For each of the members servers, make sure you attach it to the storage account and the virtual network you set up in step 3 for the region and Windows Azure account you want to set the lab environment built in.

image

If you don’t do this, it may set it up in the wrong region, or if you have multiple Windows Azure accounts associated with your logged in Microsoft ID, it may get set up in the wrong account.

Each of your member servers will need to have it’s DNS values set, and it will need to be joined to the domain. If you did not set up your own DNS server when you initially configured the Windows Azure Virtual Network, you will need to set up the DNS server entry in the network configuration first. Once you do that, you can then join the server to the domain.  Just follow the commands in the book for this. Since you already named your servers during the Windows Azure setup, you can skip the part about renaming the server. From an elevated command prompt just run: (you can safely ignore the warming about the service not being started)

netsh interface ipv4 set dnsservers “Local Area Connection 2” static 10.10.10.xx primary

netdom join SQL-B /domain:contoso.com

If you have changed things to suite the Windows Azure environment, you will need to adjust these commands for:

  • The actual IP Address to whatever yours was set to by the Windows Azure DHCP
  • You will need to add the /UserD:Administrator  /PasswordD:YourPasswordHere to the netdom command

Feel free to add the /REBoot option to the netdom command to get it to automatically restart the machine because you have to do that next anyway.

TIP: If you try to copy and past the commands above into the command prompt window you may get an error saying the command has the wrong syntax. That is because of the little hidden extra bits that are copied from the HTML to the command window. It’s better, and likely necessary, to just type the commands into the command window.

Getting the Software on the Machines

So the next issue to conquer, since you were patient and didn’t use the SQL Server 2012 image from the gallery, is to actually get SQL Server installed onto these machines as you go through Chapter 1 Planning and Installing SQL Server 2012 EXERCISE 1 System Configuration Checker .  While data ingress into Windows Azure is free, it’s still not instant. So downloading SQL Server Eval or SQL Server Enterprise from MSDN still takes time. What I did is set up a staging server to download installation files to, and then use that as a file server for the other machines. In my case I used SQL-A as my file server.  Setting up a files server on the Windows Server 2008 R2 VM is exactly like setting it up on a physical or Hyper-V based server. If you haven’t done this before, it’s worth learning and you can start here: ( http://technet.microsoft.com/en-us/library/hh831487.aspx ) for information on File and Storage Services for Windows Server 2008 R2.

I downloaded the files to the temporary storage drive. Created a folder there, and then set that folder up as a share. I gave other Domain Members access to the share so that my other machines in the domain on my Virtual Network can pull the install files from there, rather than over the Internet. It’s much faster that way.

The files I downloaded are:

I used PowerISO to mount the .ISO image of the SQL Server installation media. Once you have that set up, it acts just like a DVD drive.

Out of the box, when I ran the SQL Server Configuration Checker, everything passed. So you shouldn’t have to download any .NET frameworks, or other files. You will have to install .NET Framework 3.51 from the Server Manager – Features wizard though.

Once you start going through Exercise 2 Installing the SQL Server Database Engine you will get to step 19. It mentions the ConfigurationFile.ini will save the configuration you used. I suggested copying this file to you Software Share so that you can copy it to other SQL Server installs when you want to quickly duplicate this SQL Server installation.

The installation will seem to hang on an Install_.NETKBblahblah_Action but don’t worry, it’s still working. It has to download and configure some of the .NET stuff and it' takes a bit. Pretty soon the install will chug right along.

At this point, you are pretty much over the hurdles with getting SQL Server up and running on a Windows Azure Virtual machine. The book will walk you though the rest of the exercises, and they should work just fine on the Windows Azure infrastructure.