Last week RC0 for SQL Server 2012 was released. If you missed it you can find it here: http://www.microsoft.com/download/en/details.aspx?id=28145.

One area where you will find improvements or changes is AlwaysOn Availability Groups. In this post I will step through the wizard to show you the improvements that were made. The basis is a 4 node Windows Server 2008 R2 cluster with a stand alone SQL Server 2012 RC0 installation on each of them. All instances use the same domain account. On my first instance, SQL-02, I have two databases; AlwaysOnDb1 and AlwaysOnDb2. I will create an availability group for these databases and sync these to three replica’s.

• In Management Studio connect to the first instance, SQL-02 and navigate to the new AlwaysOn High Availability tree item. Right click it and choose New Availability Group Wizard.
• Skip the Welcome Screen by clicking Next.
• First specify the name for the availability group. I’m using AG-AlwaysOn-1. Click Next to proceed.
• In the next screen you can specify the databases you can use in your availability group. Choose all that apply and click Next to proceed. If for some reason the database cannot be chosen check the status column.
  
• The next screen is where the real magic happens. This screen has changed a lot since CTP3. Let’s go through each tab. The first tab is Replicas. This is where you will specify the replicas. Add an replica by clicking Add Replica and choose your favorite option. I’m using all four instances.
  
• The second tab is Endpoints. This is where you will specify the endpoints used by the mirroring sessions. By default these are encrypted.
• The third tab is Backup Preferences. This is where you specify where the backups are taken. But remember, this is not forced. You have to create the logic yourself. Read more about backups on replicas here: http://msdn.microsoft.com/en-us/library/hh245119(v=sql.110).aspx. To demonstrate this later on I will set the top priority on SQL-04, then SQL-03 and last SQL-02. I will configure SQL-05 as a machine where no backups are allowed.
  
• The final tab on this screen is the Listener tab. The listener is the connection between the availability group and the application. The name specified in this tab, is the name you use for your connection. Provide a name and a port and set the network mode to Static or DHCP according to your network setup.
  
• Click Next to continue and specify how you want the data to be initialized. There are three options here:
  1. Full – If you want the wizard to take care of the synchronization.
  2. Join Only – If you have already placed the databases on the replica’s.
  3. Skip – If you plan to place the databases on another time.
• Clicking Next will validate your configuration and the greener the better.
  
• Next followed by Finish will create the availability group.
  
• The warning is related to the Windows Server cluster configuration. In my case this is not the most desired configuration. The warning provides a link to the KB article that describes the quorum models and what the preferred one is.

Now that the availability group is created we can open up the dashboard to view the health. You can open the dashboard by right clicking the AlwaysOn High Availability item in management studio and choose Show Dashboard. This is what it looks like.

To quickly check if I can perform backups on a replica I connect to SQL-05 using management studio, open a new query window and create a COPY_ONLY backup. This will work, although I specified during the wizard that this replica was not available for backups. So remember, you can always create a COPY_ONLY full or a log backup on the replicas. For log backups remember that these will also be part of the log chain. You WILL need these to restore your database to a point in time. You can create backup logic with the new sys.fn_hadr_backup_is_preferred_replica function. Here is an example.

SQL Server 2012 RC0 provides an even better experience for the availability groups but you still have to be in control. Know what you are doing. Where you want backups to happen. If log backups are made on replicas. With greater powers come greater responsibilities.

A couple of weeks ago I got the result from my SQL 2008 MCM Lab exam and I failed.

I have been working with SQL Server for almost 12 years. I have been a developer, dba and architect and last year I joined Microsoft to become a Premier Field Engineer on SQL Server. This means I have a broad experience with several aspects of SQL Server and since joining Microsoft I learned something new every week, if not every day.

So how did I prepare for the MCM Lab exam? Well I did not. And I choose to. I wanted to know where I stood. Talking to other people who were thinking of doing the MCM and reading through the materials I was not sure what to expect. And for me that makes it hard to prepare myself. Although one could argue this is exactly what you would do in real life, it just doesn't feel the same. Failing the MCM Lab exam made me more experienced. Every day I waited for the result I knew more and more what I did wrong and how it should have been done. So even though I failed I have learned a lot. I think you can prepare yourself perfectly by going through the material listed on the MCM site. I just didn't have to time to go through it although I did went through some video's. So in a sense I did prepare myself, but for me, everyday is preparation for the MCM. I get challenged every week by different customers, with different SQL Server setups and different challenges. Some are hard, some are easy, some bring up a lot of discussion and some are just a lot of fun but every engagement has made me more knowledgeable on SQL.

Of course I am retaking the exam. And the fact that I did not pass but did well, strengthens me in my believe that I can achieve this. But this time I come prepared. I know my weak points.This means I can develop a proper battleplan. I will watch a few of the MCM videos, especially those in my weak areas (http://technet.microsoft.com/en-us/sqlserver/ff977043.aspx). I will setup a lab myself an try out some scenarios I think are common or hard to setup. Being able to apply knowledge from experience will help you getting the job done.

I love the exam. It is so much more testing your skills then the "regular" exams. But then again, this is the master exam. If you are pursuing the exam yourself challenge yourself with peers, get a group of people together trying to achieve the same goal. And if you think you are ready, go for it. Worst thing that could happen is that you fail. But that wouldn't be the end of the world. You can always retry.

Denali is packed with new features. A lot of features evolve around the engine, BI, Data Quality but there are also new features added to the installation process. In a previous post I already mentioned managed service accounts and in this post I will show how to install SQL Server Denali on Windows 2008 R2 Core Edition. As an added bonus I will be using managed service accounts again but instead of having the domain admin activate the managed service account I will delegate these permissions to Bob, our SQL administrator. I’m using the same setup as in my previous post.

I installed a new Windows Server 2008 R2 Core Edition and set a fixed ip (10.10.10.5/255.255.255.0). The computer was named SQL-02 and added to the domain CONTOSO. Again, Bob was added to the local administrators group using net localgroup Administrators CONTOSO\Bob /ADD.

Preparing the managed service account

First we will setup a new group for SQL Admins, add a new managed service account, sql2, and delegate permissions to the SQL Admins group. For Bob to be able to control the managed service account we must add him to the SQL Admins group.

• On the domain controller as the domain admin create a new group in AD:
  dsadd group cn=SqlAdmins,cn=users,dc=contoso,dc=com
• Now add Bob to that group:
  dsmod group cn=SqlAdmins,cn=users,dc=contoso,dc=com –addmbr cn=Bob,cn=Users,dc=contoso,dc=com
• Create a managed service account. First start PowerShell and import the AD cmdlets using Import-Module ActiveDirectory. Then create the account:
  New-ADServiceAccount sql2
• To verify the creation you can use Get-ADServiceAccount sql2.
• To delegate permissions to the SQL Admins group we must use dcals. Again, the command and procedure is documented in the Step by Step guide. Exit PowerShell and then run:
  dsacls "CN=sql2,CN=Managed Service Accounts,DC=contoso,DC=com" /G "CONTOSO\SqlAdmins:SDRCLCRPLOCA" "CONTOSO\SqlAdmins:WP;Logon Information" "CONTOSO\SqlAdmins:WP;Description" "CONTOSO\SqlAdmins:WP;DisplayName" "CONTOSO\SqlAdmins:WP;Account Restrictions" "CONTOSO\SqlAdmins:WS;Validated write to DNS host name" "CONTOSO\SqlAdmins:WS;Validated write to service principal name"
• Now Bob can log into SQL-02.
• First enable PowerShell using sconfig.cmd. Choose 4. Configure Remote Management followed by 2. Enable Windows PowerShell.
• Next we need to install a few pre-requirements before we can install SQL Server Denali. First install/enable .NET 3.5.1 using:
  dism.exe /online /enable-feature /featurename:NetFx3-ServerCore
• Next up we need the WOW64 .NET 2.0 bits. Run these two commands to get these:
  dism.exe /online /Enable-Feature /FeatureName:ServerCore-WOW64
  dism.exe /online /Enable-Feature /FeatureName:NetFx2-ServerCore-WOW64
• Now Bob needs to associate the managed service account with this machine. For that we need the Active Directory cmdlets. Run the following command to install the AD PowerShell cmdlets:
  dism /online /enable-feature /featurename:ActiveDirectory-PowerShell
• Next, start up PowerShell and import the AD cmdlets using Import-Module ActiveDirectory.
• Now we need to activate the managed service account at this machine. For this we run Get-ADServiceAccount sql2 to verify that the account is enabled, not associated with another computer and what the SamAccountName is.
  
• Now we install the managed service account on the computer. We need the SamAccountName here, sql2$ in this case.
  Install-ADServiceAccount -identity sql2$
• If everything is correctly configured you will succeed and when you run Get-ADServiceAccount sql2 you will see that HostComputers is filled now. And while the name HostComputers suggest otherwise, a managed service account can only be associated with one computer.
• Finally the time has come to install SQL Server “Denali” CTP3. There is no GUI on core so we need to resort to the command prompt interface. This is documented in Books Online; http://msdn.microsoft.com/en-us/library/ms144259(v=sql.110).aspx.
• Put in the installation media and run the following command (on multiple lines for readability):
  setup.exe /q /ACTION=Install /FEATURES=SQLENGINE /INSTANCENAME=DENALICTP3A /SQLSVCACCOUNT=CONTOSO\sql2$ /AGTSVCACCOUNT=CONTOSO\sql2$ /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS /AGTSVCSTARTUPTYPE=Automatic /SQLSYSADMINACCOUNTS=CONTOSO\SqlAdmins /TCPENABLED=1
• Afther the installation is finished you can check the summary.txt file in the Bootstrap folder, C:\Program Files\Microsoft SQL Server\110\Bootstrap\Log\Summary.txt.
• As with the machine in my previous post I wanted this instance to run on port 6000 as well. Start PowerShell and run this command:
  (gwmi –namespace root\Microsoft\SqlServer\ComputerManagement11 –class ServerNetworkProtocolProperty –filter “InstanceName=’DENALICTP3A’ AND ProtocolName=’Tcp’ AND IPAddressName=’IPALL’ AND PropertyName=’TcpPort’”).SetStringValue(6000)
• This will use WMI to set the tcp port for IPALL. For SQL pro’s the filter should look familiar. After that we need to restart the instance. We use Stop-Service MSSQL`$DENALICTP3A –force to stop the service. We need to escape the dollar sign and we need the force parameter because there are dependent services (SQL Agent). Now start the service with Start-Service MSSQL`$DENALICTP3A.
• To verify SQL is listening on we can search the errorlog for the text “Server is listening on [ ‘any’ <ipv4> 6000].” or you can use netstat –aon | findstr :6000. This will give you the process id that is listening on that port. Using tasklist /FI “PID eq <pid>” where replacing <pid> with the earlier found process id should give sqlserver.exe in return.
• Now SQL is listening on port 6000. We need to open up the appropriate ports in the firewall. use these two commands to open port 6000 and allow UDP traffic for the Browser service.
  netsh advfirewall firewall add rule name="SQL Server DENALICTP3A Instance" dir=in action=allow protocol=tcp localport=6000 profile=domain
  netsh advfirewall firewall add rule name="SQL Server Browser Service" dir=in action=allow protocol=udp localport=1434 profile=domain
• Now returning to the Windows 7 client and SSMS let’s see how we are connected to these instances.
  

With Windows Server 2008 R2 Core you get a smaller install base; 33 processes running on core vs 41 on full installation, 351MB vs 452MB memory in use after start and 4GB vs 9GB disk space. Of course your mileage may vary but you get the general idea. Not only did I show how easy it is to install SQL Server Denali CTP3 on a Windows Server Core edition but also how you permissions to control managed service accounts can be delegated to other administrators.

Give SQL Server Denali CTP3 a run and remember to provide your feedback. If you’re fast you might win something as well, http://blogs.technet.com/b/dataplatforminsider/archive/2011/08/19/announcing-the-sql-server-code-name-denali-feedback-challenge.aspx.

With the freshly released SQL Server 2012 we can put availability groups to live. You have seen all the demo's and now you are ready to get things going in your own datacenter. At this point you might get to a few surprises. One of these might be to get Kerberos going for your availability groups. If we recap the theory behind availability groups we get a more clearer picture.

In SQL Server 2012 we use availability groups as a unit of failover. We have up to five single SQL instances. The underlying nodes participate in the same Windows Failover Cluster. In the cluster configuration you will notice a few resources shared amongst these independent SQL instances:

• Availability Group Resource
• IP-Address
• Virtual Network Name

Now if you want to use Kerberos you must make sure all prerequirements are met. The most important thing being a service principal name, or SPN. We set a SPN using the SETSPN command that's included in the Windows. A good start on more information is Books Online: http://msdn.microsoft.com/en-us/library/ms191153(v=sql.110).aspx

Getting Kerberos to work on just SQL should not be hard. So now we introduce availability groups. This is where it gets harder. To get Kerberos working on availability groups you must also set a SPN on the virtual network name.

So if you have an availability group named AG-SQL-1 in the CONTOSO.COM domain you must set a SPN on ag-sql-1.contoso.com with the correct port and the domain account running the SQL Server process. So here is a catch. What if the availability group fails over to another node. It needs to be registered again unless that node is running with the same domain credentials.

Bottom line: if you want to use Kerberos with availability groups you need to run all nodes with the same domain account. This will save you a lot of headaches.

You can read up on it in Books Online: http://msdn.microsoft.com/en-us/library/ff878487(v=sql.110).aspx#PrerequisitesSI

As a Premier Field Engineer I get into a different challenge every week. These challenges can be anything; performance issues, architecture review or migration scenarios. If I'm at a customer site it is not always possible (or recommended) to test my solution on the production systems. And if it's a possible bug I want to reproduce it so it can be investigated. For this you will need a lab environment. But building a lab environment can be a time consuming task. Most of the times I need different SQL versions, clustering, AlwaysOn or another Microsoft product relying on SQL server. And when I'm ready to present and prove my solution I don't want any data from other customers to be visible. For these reasons I always build new and fresh environments. If it's a performance issue I can get away with just a single VM but if it is a more complex scenario I might have to install a domain controller and a series of other machines as well.

I used to run Windows Server 2008 R2 on my laptop as my primary OS. Basically because I needed Hyper-V. But with Windows 8 I can run Hyper-V on a client OS and as a bonus I get a lot more PowerShell cmdlets to automate recurring tasks. Do you see where I'm going? There is some great software available to automate the deployment of VM's. You can use Windows Deployment Services or System Center Virtual Machine Manager. But I wanted an out-of-the-box solution that didn't require any additional tooling other than Windows 8 with Hyper-V enabled. And there is.

This post will be part of a series of posts because getting a complete AlwaysOn cluster running is a lot of preparation and understanding all the steps will make it easier to customize this to your purpose. Basically I will break it down in a few steps:

• Part 1: Installing Windows on a virtual disk and boot it in Hyper-V
• Part 2: Automating and customizing the installation using sysprep and unattend
• Part 3: Running post deployment tasks like AD configuration or SQL Server installation
• Part 4: Putting it together

Installing Windows on a virtual disk and boot it in Hyper-V

Before you can start to automate anything with PowerShell you will need the steps to do it. Imagine you would create a new VM? What steps do you need to take?

1. Create the VM
2. Insert the DVD
3. Install Windows

If we are going to do this in PowerShell you will need to reorder some of the items. In PowerShell we will create the disk first, install Windows on it and create the VM in the final step.

Create a vhd(x) with PowerShell

There are several ways to create a virtual disk. You can use DISKPART but that can be a bit of a drag and is harder to automate, or you can use PowerShell. In Windows 8 you can use some new and cool cmdlets to create a virtual disk. The next script will create a vhd and format it. I will explain them after the script.

$vhdpath = "D:\vm\dc-01.vhdx"
$vhd = New-VHD -Path $vhdpath -SizeBytes 30GB

Mount-DiskImage -ImagePath $vhdpath
$disk = Get-DiskImage -ImagePath $vhdpath | Get-Disk

Initialize-Disk -Number $disk.Number -PartitionStyle MBR
$volume = New-Partition -DiskNumber $disk.Number -UseMaximumSize -AssignDriveLetter -IsActive | Format-Volume -Confirm:$false
$drive = [string]$volume.DriveLetter + ":\"

We start with setting a variable to make things easier later on. The variable $vhdpath hold the path to our virtual disk. The first step is to create the disk using New-VHD with the specified size, in my case 30GB is enough. The disk will be dynamically by default but you can create a fixed or differencing disk as well. Refer to the documentation for more options: http://technet.microsoft.com/en-us/library/hh848503.

The following step is to mount the image using Mount-DiskImage specifying the path. When the disk is mounted you can format it using PowerShell instead of DISKPART. Formatting is a three-step action; first initialize the disk, partition it and finally format it. This is not different from DISKPART. Once it's formatted we can get a handle to the drive which we will need later on.

With a freshly formatted drive we are ready to install Windows. For those of you that are not familiar with deployment or images; we can deploy Windows directly to a disk. No need to boot into a DVD. When you install Windows an image file (*.wim) is applied to the destination. The proper boot records are written and you are off to go. Prior to Windows 8 you would need a tool called ImageX to capture and apply images to a destination. Being part of the Windows Automation Deployment Toolkit this was another download and dependency and does not fit in my 'only use what's default' policy. But with Windows 8 this is not a problem. The command line tool DISM is extended to capture and apply images as well. Being part of the operating system it can be used for my purpose. Check out the next piece of PowerShell.

Install the DVD

$image = "F:\sources\install.wim"
$dismArgs = @("/Apply-Image", "/ImageFile:$image", "/index:4", "/ApplyDir:$drive")
Start-Process -FilePath "DISM.exe" -ArgumentList $dismArgs -NoNewWindow -Wait -PassThru

There is no DISM cmdlet so we will resort to invoking the process. The source is set to my DVD drive with the Windows Server 2008 R2 DVD loaded. The index is set to 4 because I'm installing the Enterprise Core edition. You need to supply an index if the image contains multiple installations. I will not elaborate much on this because in the next post I will show another method. If you want to find out the index number of the installation use DISM /Get-WimInfo /wimfile:<pathtowimfile>. Finally we apply this to the drive we obtained after the formatting.

When that's finished the proper boot records need to be written to the disk. The tool is bcdboot and we pass the drive and boot type as parameter.

$bcdargs = @((Join-Path $drive "Windows"), "/s $drive", "/f BIOS")
Start-Process -FilePath "bcdboot.exe" -ArgumentList $bcdargs -NoNewWindow -Wait -PassThru

Create the VM

And basically that's it. All that is left is to create the VM, start it and open it. And as you might have guessed, Windows 8 comes loaded with all you need cmdlets.

$vmname = "DEMO-01"
Dismount-DiskImage -ImagePath $vhdpath

$vm = New-VM -name $vmname -MemoryStartupBytes 512MB -SwitchName "Hyper-V Internal Network" -VHDPath $vhdpath
Set-VMMemory $vm -DynamicMemoryEnabled $true -MinimumBytes 512MB -MaximumBytes 1GB
Start-VM $vm
vmconnect localhost -G $vm.VMId

First another variable is loaded with the name of the VM. Then the disk is dismounted and a new VM is created. It's connected to my Hyper-V switch named "Hyper-V Internal Network". You will need to adjust this to your environment. In the second step dynamic memory is configured. I use this a lot. Using dynamic memory enables me to have 6 VM's running with 8GB internal memory and still not run into any issues. The final two steps are starting the vm and connecting to it using vmconnect. The latter can be discarded of course. Also notice I'm using the identifier to connect to the VM. During my testing I found out it is possible to create multiple VM's with the same name. Connecting to the identifier makes sure you connect to the right one.

After a few minutes you should have a shiny Windows installation awaiting you to create a new administrator password. In the next post I will go into customizing the installation, sysprepping it and automating the installation process.