Azure VM Part 2 – Create a development network

…or create a small customer network, for that matter…

The last month is been dedicated to learning more about Windows Azure, and how to facilitate features like SQL databases, web sites, virtual network, DNS servers and virtual machines to be able to create my own optimized development environment.  I have been playing around with my work MSDN subscription and measured the cost for a small environment – with its own domain controller, web and SQL server.

Basic CPUs RAM Price/month
A0 (Extra Small) 1 768mb $15
A1 (Small) 1 1.75gb $55
A2 (Medium) 2 3.5gb $110

When creating such an environment in Windows Azure, you should do the following:

  1. Create Affinity Group
    1. Select the datacenter nearest your location. Mine is North Europe.
  2. Create Storage Account
    1. Select affinity group from (1) as Region/Affinity Group.
    2. Also choose Geo-Redundat as replication
  3. Register DNS server
    1. Set internal IP address to
  4. Define Virtual Network.
    1. Remember to select affinity group from (1) and DNS server from (3)
    2. Cost is about $50/month
  5. Create VM for AD
    1. Select “basic” tier
    2. It’s enough with a size on “A0 (shared core, 768MB memory)” for a development environment ($15/month), opposite to $55/month for A1. This can be changed in 5 minutes (including reboot).
    3. Set local administrator username and password
    4. Write the global name for the AD machine
    5. Select the virtual network defined in (4)
    6. Select the storage account defined in (2)
    7. Validate IP for AD (should be 10.0.04)
    8. install the AD domain services role and promote VM to a domain controller
    9. Create administrator and service accounts
  6. Create VM for SQL Server
    1. Create a new VM from gallery (SQL Server 2014 Web Edition)
    2. Select “Basic” tier. You don’t need load balances and auto-scaled development environment
    3. Select Basic-A1 size (easily changed to Basic-A2 og Standard-A5 for that matter)
    4. Select administrator username and password
    5. Select the virtual network defined in (4)
    6. Select the storage account defined in (2)
    7. Create a 1433 (standard MS SQL port) endpoint
    8. Join the server in created domain from (5.8)
    9. Add SQL administrator with sysadmin
    10. Create an inbound rule in the firewall for SQL Server
    11. Configure SQL Server “Max memory” (70-80% of total memory)
    12. If production server, you should add two disks for data and log formatted with 64k block size.
  7. Cerate VM for Web server
    1. Create a new VM from gallery (Windows Server 2012 R2 Datacenter)
    2. Select Basic-A0 or Basic-A1 size
    3. Same as 6 d-f
    4. Create http and https endpoints
    5. Join the server in created domain from (5.8)
    6. Install IIS web role and install software for web deploy

Powershell commands for (5.8) – install AD and promote as domain controller:

Set-DnsClient –InterfaceAlias "Ethernet*" –ConnectionSpecificSuffix 
Install-WindowsFeature AD-Domain-Services –IncludeManagementTools 
Install-ADDSForest –DomainName

Powershell command for joining a computer to domain (as in 6.8 and 7.5):

Set-DnsClient –InterfaceAlias "Ethernet*" ` –ConnectionSpecificSuffix 
Add-Computer –Domain 

The cost for this setup is about $130-$250, depending on server configuration you need:

  • Small development
    • AD + VN = Basic-A0 + $50
    • Web = Basic-A0
    • SQL = Basic-A1
    • Cost = $50 + 2*A0 + 1*A1 = $50 + $30 + $55 = $135/month
  • Small production
    • AD + VN = Basic-A0 + $50 = $65
    • Web = Basic-A1 = $55
    • SQL = Basic-A2 = $110
    • Cost = $65 +$55 + $110 =  $230/month

Now you should be ready for coding…




Azure VM Part 1 – Create SQL Server VM

When you buy a MSDN subcription with Visual Studio you receive free credits (renewed every month) to use in Windows Azure. This makes it very easy to create your own development and test environment very fast. In my case, as a developer and DBA, I want to create my own virtual machines that I have full control over.

You are able to access most of Azure through PowerShell, and I was particular excited about the possibility to create virtual machines on the fly in PowerShell, and here is the first version of my create_azure_sqlvm.ps1.

Example usage:

create_azure_sqlvm.ps1 "sql06" "sql06admin" "itsaSecret2014"

This script has 3 required parameters: vm-name, admin loginname and password. These parameters must be supplied to script as shown above. In addition, there are 2 default parameters, location and instanceSize. The first default parameters $location is which data center the new virtual machine will be stored. The default value is set to “North Europe” since this is the nearest to my home city Fredrikstad 🙂

All available locations can be retrieved by the following command:

Get-AzureLocation | Select Name

The second default parameter $instanceSize tell Azure how big the virtual machine should be when it is created. The default value for the script is set to “Small”. This value might not be ideal for a production server, but in my case for a development environment, this is okey. Se link for more information on sizes.

The script will install the latest SQL Server 2014 Web edition available. See the ImageFamily parameter to select another image for the SQL Server installation.

When you run the script in administrator mode in PowerShell, you will get some similar out as show below:


According to my calculations this virual machine will cost about $50-70 per month, depending on how much it is used.

PowerShell – Format SQL Disk Partition

When installing SQL Server you have to ensure that the DATA, LOG and TEMPDB disk are formatted with 64K block size (allocation unit size). This must be done to reduce number of physical page reads from disk when SQL Server engine needs fresh data that is not updated in cache. Since database files most certain are larger than 64K, this is the block size you need for your database files.

As a result, I have implemented an unattended installation script in PowerShell where I check the Block size of the DATA, LOG and TEMP disks. If they don’t have 64K block size, I will format them immediatly.

Here is my PowerShell function for this use.

function FormatSQLDisk([string]$driveletter, [string]$drivelabel)
    Format-Volume 	`
	-DriveLetter $driveletter `
	-NewFileSystemLabel $drivelabel `
        -FileSystem NTFS `
	-AllocationUnitSize 65536 –Force -Confirm:$false

Happy formatting…

Unattended install for SQL Server 2014 CTP2

The process of creating an unattended installation procedure for the newest “beta” release of SQL Server is the same as described in my previous post for SQL Server 2008 R2. But one of the major difference is that you should install SQL Server 2014 on Windows Server 2012, and we can use the newest version of Powershell and take advantage of new, powershell functionality like disk management and other advanced OS configurations.


I created the configuration file as shown above, and added the following statements:


I also had to remove the UIMODE and QUIET statements, since they can’t be used with the statement to optimize the unattended install options:


This is my first install of SQL Server 2014 CTP2, and I will optimize the powershell installation script with a set of the most important pre- and post installation tasks.

Happy installing…


SQL Server 2014 CTP 2 available

The road to new version of SQL Server seems to come closer every month. This week the CTP2 version was released. The main feature for the SQL Server 2014 is the in-memory processing, and extended AlwaysOn capabilities and better integration between on-premis and cloud to enable hybrid database solutions.

When I have completed 70-462 exam, I will definitivly set up hybrid alwayson solution on my VMs at work to test these new capabilities, in additions to the in-memory stuff.

SQL Server 2014 – Memory Optimized Table (CTP1)

The new SQL Server 2014 CTP1 comes with a new cool feature called “memory optimized data”. This feature allow us to create a table in pretty much same manner as before with some different switches when defining the table file group and some options when the table is created. The feature is available from the Management Studio GUI, but wil open a new query windows as when we define a FileTable. But I guess this will be changed in future releases.


The below example shows how to create a database with a memory optimized filegroup.


The below example show how to create a table in the newly created memory optimized filegroup. This is done by the option “MEMORY_OPTIMIZED = ON”. A memory-optimized table can be defined with one of two DURABILITY values: SCHEMA_AND_DATA or SCHEMA_ONLY with the former being the default. A memory-optimized table defined with DURABILITY=SCHEMA_ONLY, which means that changes to the table’s data are not logged and the data in the table is not persisted on disk. This will increase performance dramatically.


Rember that NONCLUSTERED indexes are not available in CTP1.