Review a working option to setup a SQL Server 2012 BI solution using Azure’s IaaS VMs

  • Article

By: Nicholas Dritsas

Reviewers: James Podgorski, Olivier Matrat, Sanjay Mishra

Overview

Azure’s Iaas VMs plus Virtual Networks can be used to configure easily a set of servers/VMs to host the main components of a BI solution. This process assumes you already have an Azure subscription plus access to add new VMs and Virtual Networks.

Setting up a virtual network

  1. Go to the Azure portal here: https://windows.azure.com/.  Ensure you connect to the preview portal.

  2. Select +New bottom left of the welcome screen and choose Network, Custom Create. (for setup details, look here.)

    1. Enter a network name and choose either an existing Affinity Group or choose to create a new one.
    2. For Address space, you can give a range such as 10.4.0.0/16.  If you like, you can break it down to a few subnets for better segmentation. 
    3. Then select to Create a DNS, give it a name and use as IP address the 5th IP of your range, eg 10.4.0.4.  The first 4 IP addresses of a specified IP range are reserved.
    4. This IP is expected to be used by the first VM we will create below as our Domain controller.  Mind you, in Azure a VM keeps its provided IP
      the same during its lifetime.  So, even if your settings remain DHCP, your IP remains the same even if the VM restartsor fails over.  For more details, see here.

  3. Creating a DC (this step must be done first before adding any other VM in the network you just created).

    1. Create a new VM from the Gallery using Windows 2008 R2 image.  Small or medium is fine since this will be used only as DC and DNS. Then, ensure you choose VM as standalone, choose your storage account in
      the same region to host it and select the Virtual Network name you created above.
    2. After the new VM creation is complete, attach to it a new empty disk of 10GB or so by choosing the VM and selecting Attach at the bottom panel, then choose Attach empty disk. 
    3. Then, connect to the VM by selecting the VM name and selecting Connect at the bottom. 

                                                              i. After you connect, ensure its IP is indeed 10.4.0.4 by running ipconfig in command prompt. 

                                                            ii. Using the disk management found in server manager, format the new disk as F: (or any other drive letter you like) drive. Create 3 subdirectories there to host AD db, logs and sysvol files later on.

                                                           iii.Then, we need to promote to it to DC, look here for details.  In simple terms, run the program dcpromo.exe from the command prompt and follow instructions to create a new forest.

            1. Choose DHCP and ignore the warning
            2. Store AD db, logs and sysvol on the 3 subdirectories in the F: drive
            3. After all setup is done, restart the VM.

                     
iv.     After it restarts, go to Windows Explorer, select Network and Enable file sharing and discovery.

                                                            
v.     Create a new domain admin account user too for future use

4. Now, you have a domain in azure.  Create one new VM from the Gallery using SQL 2012.  Then, join the domain you just created.  Go to Windows Explorer, select Network and Enable file sharing and discovery. 
Now, you should be able to ping the DC by IP and name.

                                                              
i.    You have to open inbound ports in the vm that hosts ssas and sql server (or you disable the firewall for domain networks if you like).  See here:  https://msdn.microsoft.com/en-us/library/bb934283.aspx

                                                             
ii. You can find the ssas ports or any process ports from this link:  https://support.microsoft.com/kb/2466860.  I added a new inbound rule to allow ports 1433 and 2383. 1433 is for sql server when 2383 is the port for the default
ssas instance.

 

5. Create another new VM from the Gallery using Windows 2008 R2.  Then, join the domain you just created.  Go to Windows Explorer, select Network and Enable file sharing and discovery.  Now, you should be able to ping the DC.

        1. Install Excel 2010 and try to connect to SSAS server. It should work fine.