Robert's SQL Blog

Remember to vote for your AlwaysOn Availability Groups

2013-04-24T08:37:34Z

While visiting a customer and helping them set up AlwaysOn Availability Groups we came across a surprising "feature". On my laptop I had build a demo lab consisting of one domain controller, three Windows 2008R2 Core member servers with SQL 2012 SP1 installed and a full Windows 2008 R2 installation with SQL Server management studio installed. I had setup the cluster, enabled AlwaysOn and created an empty database to create an availability group. I tested the availability group by manually failing over using management studio and this worked fine.

But that's in the demo world. Usually we have all kind of conditions that cause our demo's not to work with our customers. In this case the customer had set up an availability group as well and the failover was working when initiated from management studio. But stopping the SQL Server service caused the Availability Group to stay in a resolving state and never failover. I tried this on my machine and it failed over perfectly. The only difference between my setup and the customer was that I was using a three node cluster and they were using a two node + file share cluster. To match the customers setup I removed the third replica from the availability group and evicted this node from the cluster. This left me with a two node cluster. What is important to remember is that this is not a recommended setup. In this setup you will never have majority and the cluster will go offline when one of the nodes goes offline. This basically defeats the purpose of a cluster. But to satisfy my curiosity I stopped the SQL Server service. To my surprise the availability group failed over to the other node.

A quick recap:

Three node cluster with an availability group: Stopping the service fails over the availability group
Two node cluster (not recommended) with an availability group: Stopping the service fails over the availability group
Two node with file share witness: Stopping the service leaves the secondary in a resolving state and the listener remains offline

This is a known issue and if you read the signs along the road you would have noticed this. The reason I missed it at first is that in the earlier builds the link in the warning did not work. Below is a screenshot of the warning you will get when you perform a failover in the third scenario.

There is a KB article that describes this behavior (http://support.microsoft.com/kb/2761129) and this references the same KB article (http://support.microsoft.com/kb/2494036) that is mentioned in the link in the warning message. In short it turns out that the nodes do not have a vote. You can see this by querying sys.dm_hadr_cluster_members or clicking the View Cluster Quorum Information link in the AlwaysOn dashboard. This will show NULL or Not Available. This means the nodes do not have a vote and we need the vote to determine if there is quorum.

After applying the hotfix we get the desired behavior and the dmv and dashboard show 1 indicating each node has a vote. Stopping the service results in a failover leaving the new primary in a Not Synchronizing state. This is the expected state but leaves the availability group at risk. Another replica must come online as soon as possible to start synchronizing. In this state the transaction log will not be able to truncate and reuse the space within the log file.

From 0 to SQL Demo lab in a few easy steps – Part 4

2013-03-08T17:30:16Z

I have not been able to follow up my post for a long time. Work has to be done as well. The good thing is that it gives me a lot of new ideas for blog posts and as a (late) resolution for the new year I will write more blog posts

But back to part 4 of building a SQL Demo lab. There are a few things you have to consider building an entire lab. Some are easy and some require creativity. First let me list the steps you would take to create an AlwaysOn demo manually.

Build a Domain Controller
Provision some users to the domain
Install 2 or more member servers
Install SQL Server 2012
Configure SQL Server
Build a cluster on top of the member servers
Enable AlwaysOn

And that should leave you with a ready-to-go demo machine.

In the previous part we have done all the individual steps but the complexity lies in the sequence. We cannot add a member server if there is no domain controller and we cannot enable AlwaysOn if there is no Windows Cluster build on top of the member servers. And what would be the use of automating things if we have to execute three or four scripts manually.

Key Value Pairs to the rescue

Hyper-V virtual machines have this concept of exchanging data through a dedicated registry portion. There is some good information out there but the best I found so far was this blog post by Taylor Brown; http://blogs.msdn.com/b/taylorb/archive/2012/12/05/customizing-the-key-value-pair-kvp-integration-component.aspx. By leveraging this concept I can write some PowerShell code that will read this data in a loop and exit when a certain value has been saved to the registry key. This will enable me to create one script that will call different scripts to install the various servers but in the proper sequence. In my VM I will set a status. Using a function on the host I read the value and depending on the status I will proceed to the next step.

There had to be challenges

Of course there had to be. What I wanted was to alter the unattend.xml files. I could have taken the easy road and use placeholders like @computernamehere@ and just do a textual replace. But I choose to use XML to navigate through the unattend.xml and change the proper value. To give you an example of some funny PowerShell behavior. Imagine this piece of XML:

<root>
<data type="database">
<name>master</name>
</data>
<data type="job">
<id>1</id>
</data>
</root>

If you navigate through this piece of XML in PowerShell, for example $xml.root.data.name it will return master. Great! A new world of possibilities arises. Things get ugly if you want to change the value. You get an error telling you the value cannot be set. It took some time but it turns out to be related to the fact the data element is there twice but the name element only once. When you navigate through the XML PowerShell uses late binding to figure this out but when you write to it, it wants to know exact. I solved this in my script by using Xpath queries to get to the correct element.

On my SkyDrive you can find the scripts I used: http://sdrv.ms/Y0oUfv

The main script I used is Create-Lab which will create a Domain Controller and configure it. Installs 1 management member server and 3 SQL Servers. When the installation of the third server is finished a cluster is build and Availability Groups are enabled. The instances are restarted using WMI. This proved to be the most reliable method. If you want a shorter example of what the script does check out Create-Lab-Demo. This creates a lab with SQL 2008 R2 and SQL 2012 installed on it.

Important! In the Create… scripts there are names and paths I used on my machine. Evaluate each one and set this to your environment. Also in my script I refer to two *.wim files for the base installation. Due to legal and other reasons I cannot provide you these files but you can use a *.wim file from the Windows setup media or create your one using the method I described in one of my earlier posts. Also the iso files I used for SQL Server are of course not included and you need to provide your one. Finally, the keys included are setup keys the installer uses when you would choose Skip Installation during the Windows Setup process. Check one of my earlier posts to see where to retrieve these keys.

Feel free to contact me for any further information or improvements. And remember, do not use these scripts in production environments. They were built to create quick demo labs.

From 0 to SQL Demo lab in a few easy steps – Part 3

2012-09-07T13:55:00Z

In part 1 showed how you could install Windows on a virtual disk and use that disk to create a new virtual machine. We improved on part 2 to show how to set the administrator password or the computer name during installation to minimize user input.

In part 3 we will take another step. It's nice to have a clean Windows installation with the desired network configuration or computer name but what if I need a domain controller setup with some default settings or have a SQL server joined to an existing domain.

The magic is in the unattend file as well. Besides setting the computer name or network we can also autologon for a specified number of times. We can also specify commands we want to run when the logon takes place.

First the autologon. To get this going add the following XML right before the UserAccounts segment but after the OOBE.

<AutoLogon>
 <Password>
 <Value>p@ssw0rd</Value>
 <PlainText>true</PlainText>
 </Password>
 <Username>Administrator</Username>
 <LogonCount>1</LogonCount>
 <Enabled>true</Enabled>
 <Domain>CONTOSO</Domain>
 </AutoLogon>

Again, not the best method for a production environment but it fits my purpose. If you use WAIK to generate the XML file the password will be encrypted.

We can now specify some commands we want to execute during the logon process. It is important to know how to install what you want using commands only. For active directory this is easy. You run DCPROMO and specify an unattend file. In this file you specify the domain name, level and dns settings. Some examples of the unattend file are on technet.

To copy it over I add another Copy-Item to my PowerShell script to copy my ad installation file to the destination system. Finally you must add the command to execute:

<FirstLogonCommands>
 <SynchronousCommand wcm:action="add">
 <CommandLine>dcpromo.exe /unattend:c:\ad.txt</CommandLine>
 <Description>Installation of AD</Description>
 <Order>1</Order>
 </SynchronousCommand>
 </FirstLogonCommands>

If all goes well you will have a shiny new domain controller. Now for step two. Adding a member server to the domain. There are several ways to add a machine to the domain. I have tried a few but my knowledge of active directory is not at the same level as SQL so I ran in some issues using the autojoin. Eventually what worked for me was just specifying the user to join the domain. Again, using the autojoin is much safer than putting passwords in text files. This is what I added after the network settings in the unattend file.

<component name="Microsoft-Windows-UnattendedJoin" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="http://schemas.microsoft.com/WMIConfig/2002/State" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <Identification>
 <UnsecureJoin>false</UnsecureJoin>
 <Credentials>
 <Domain>CONTOSO</Domain>
 <Username>bob</Username>
 <Password>p@ssw0rd</Password>
 </Credentials>
 <JoinDomain>contoso.com</JoinDomain>
 <MachineObjectOU>OU=Servers,DC=contoso,DC=com</MachineObjectOU>
 <DebugJoin>true</DebugJoin>
 </Identification>
 </component>

The next thing was installing SQL server. There are several ways to do this. One is to use the sysprep feature and include SQL in your base image and then finalize the installation. This proved to be limited for me. This only works for some components of SQL and also not for all versions of SQL. I wanted a solution that would work regardless of the version. My solution was to add the DVD during the VM creation and run a cmdline setup of SQL Server. This gives me much more control and flexibility. To install SQL server I created a cmd file with a very basic setup:

d:\Setup.exe /q /ACTION=Install /FEATURES=SQLEngine,ADV_SSMS /INSTANCEID=SQL2012 /SQLSVCACCOUNT="NT Service\MSSQL$SQL2012" /ADDCURRENTUSERASSQLADMIN="True" /AGTSVCACCOUNT="NT Service\SQLAgent$SQL2012" /AGTSVCSTARTUPTYPE=Automatic /BROWSERSVCSTARTUPTYPE=Automatic /TCPENABLED=1 /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS /UPDATEENABLED=0

And using the logon command I showed earlier I started this installation after the first logon. I also added a LOGOFF command to the cmd file just to return to the logon screen.

This works perfectly. I did notice the installation of SQL took a long time because of some .NET requirements. I have included these in my base image and things got a lot faster afterwards. Check your installation logs to see where most time is spend and see if you can speed things up by including these in the base image.

In the final part I will provide you with the definite PowerShell scripts and command files to recreate what I showed here.

From 0 to SQL Demo lab in a few easy steps – Part 2

2012-08-22T15:09:00Z

This post is part of a series of posts about buidling a lab environment using some of the new PowerShell cmdlets and features in Windows 8.

In the previous post I explained how to create a virtual disk, apply a standard Windows Server 2008 image to it and attach the disk to a new VM. If you followed my steps you should have noticed that it was not a fully automated experience. During the installation I was asked to provide the regional settings, set or skip a license key, accept the EULA and provide an administrator password. And when I finally was logged in I still had to do a lot of configuration like setting an ip-address and renaming the machine. In this post we will get these steps out of the way as well.

The first step is to do some primary settings. In my case I enable PowerShell and Remote Desktop. This is also a good time to download updates and .NET frameworks you might need later. Try to keep things as basic as possible. Only set the ip-address if that's needed. We will change it later on.

Once the machine is to your liking it's time to sysprep it. Sysprep is located in the sysprep folder inside the system32 folder. Sysprep removes all personal information from the machines and makes it possible to create an image for redeployment. Start sysprep and choose OOBE Experience, Shutdown and make sure the box at Generalize is checked or better run it from the commandline: sysprep /generalize /oobe /shutdown. Make sure you specify the generalize option. This will create a new machine identifier for every machine. If you miss that one you could get into weird problems.

After sysprep is done it shuts down the machine. Now it's time to create an image that we can use as a base for deployement. You only have to do this once for every base image you want to create. I'm using a Windows 2008 and Windows 2008 Core template. Using DISM you can create an image (WIM) file. First you need to mount the image using Mount-VHD <pathtovhd>. Now capture the image with: DISM.exe /Capture-Image /ImageFile:D:\vm\base.wim /CaptureDir:G:\ /name:Base-Image. Replace the paths and name with your own. After some time a file is created. Make sure to dismount the vhd using Dismount-VHD. This concludes step 1 of this task. Next up is to automate the installation. There is an easy way and a hard way. The easy way is to use the Windows Automation Installation Kit. This tool has an editor to create unattended files. Another option is to create one with notepad. Of course you can also copy from others. Here is an example of the unattend.xml I'm using to create a domain controller. Read through it, I will explain the values in a moment.

<?xml version="1.0" encoding="utf-8"?>
 <unattend xmlns="urn:schemas-microsoft-com:unattend">
 <settings pass="specialize">
 <component name="Microsoft-Windows-Shell-Setup" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="http://schemas.microsoft.com/WMIConfig/2002/State" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">\
 <ComputerName>DC-01</ComputerName>
 <TimeZone>UTC</TimeZone>
 <RegisteredOrganization>Contoso Corp.</RegisteredOrganization>
 <RegisteredOwner>Contoso Corp.</RegisteredOwner>
 <ProductKey>7P8GH-FV2FF-8FDCR-YK49D-D7P97</ProductKey>
 </component>
 <component name="Microsoft-Windows-DNS-Client" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="NonSxS" xmlns:wcm="http://schemas.microsoft.com/WMIConfig/2002/State" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <Interfaces>
 <Interface wcm:action="add">
 <DNSServerSearchOrder>
 <IpAddress wcm:action="add" wcm:keyValue="1">127.0.0.1</IpAddress>
 </DNSServerSearchOrder>
 <Identifier>Local Area Connection</Identifier>
 </Interface>
 </Interfaces>
 </component>
 <component name="Microsoft-Windows-TCPIP" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="http://schemas.microsoft.com/WMIConfig/2002/State" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <Interfaces>
 <Interface wcm:action="add">
 <Ipv4Settings>
 <DhcpEnabled>false</DhcpEnabled>
 <Metric>10</Metric>
 <RouterDiscoveryEnabled>false</RouterDiscoveryEnabled>
 </Ipv4Settings>
 <Identifier>Local Area Connection</Identifier>
 <UnicastIpAddresses>
 <IpAddress wcm:action="add" wcm:keyValue="1">192.168.200.1/24</IpAddress>
 </UnicastIpAddresses>
 <Routes>
 <Route wcm:action="add">
 <Identifier>1</Identifier>
 <Metric>10</Metric>
 <NextHopAddress>192.168.200.1</NextHopAddress>
 <Prefix>0.0.0.0/0</Prefix>
 </Route>
 </Routes>
 </Interface>
 </Interfaces>
 </component>
 </settings>
 <settings pass="oobeSystem">
 <component name="Microsoft-Windows-International-Core" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="http://schemas.microsoft.com/WMIConfig/2002/State" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <InputLocale>132105</InputLocale>
 <SystemLocale>en-US</SystemLocale>
 <UILanguage>en-US</UILanguage>
 <UserLocale>en-US</UserLocale>
 </component>
 <component name="Microsoft-Windows-Shell-Setup" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="http://schemas.microsoft.com/WMIConfig/2002/State" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <OOBE>
 <HideEULAPage>true</HideEULAPage>
 </OOBE>
 <UserAccounts>
 <AdministratorPassword>
 <Value>p@ssw0rd</Value>
 <PlainText>true</PlainText>
 </AdministratorPassword>
 </UserAccounts>
 </component>
 </settings>
 <settings pass="windowsPE">
 <component name="Microsoft-Windows-Setup" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="http://schemas.microsoft.com/WMIConfig/2002/State" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <UserData>
 <AcceptEula>true</AcceptEula>
 </UserData>
 </component>
 </settings>
 </unattend>

There are several sections in the xml. One section is the pass phase. This tells the setup routing at what point to use the settings. Some settings are only valid in one phase while other can be in multiple. The Windows Automation Kit knows and will only choose the right ones.

In the first part of the file the computer name is set along with the time zone and other details. The product key is not a real key but a temporary one. It's actually the same key that's entered when you click skip during the setup process. You can find this key inside a file in the Windows installation dvd. So, it's not a real key, just one that will get the setup process automated.

In the second part the network is set. In this example the ip 192.168.200.1 is used with netmask 255.255.255.0. The DNS is set to 127.0.0.1 because this machine will take the role of domain controller with DNS installed. The gateway is set to the 192.168.200.1 as well.

The last part is where the locale settings are set. The values can be retrieved from this table. Also the EULA is accepted and the administrator password is set. If you use the Windows Automation Kit the password will be encrypted. As a best practice you should not include the password in plain text.

So far for preparation, now it's show time. The trick is to get the unattend.xml we created located at the rootdrive of the machine. Using PowerShell and the commands from the previous post this task is easy. As you might recall I mounted the disk to apply the image and set the bootrecords. Run through the steps from the first post up until the point where the drive is dismounted. With the disk still mounted you can copy whatever file you want to the drive, including an unattend. If you add this PowerShell one-liner before you dismount the disk the file will be copied: Copy-Item source destination –force. This will copy the file from A to B. Now dismount the disk and you are done.

Once the autounattend is copied you can start the VM. After a few minutes and with no user interaction you should be able to log into the machine.

This brings us one step closer to a complete unattended installation of a lab environment. In the next post I will explain how to perform post install actions like installing and configuring AD and setting up SQL Server.

From 0 to SQL Demo lab in a few easy steps – Part 1

2012-08-15T11:16:00Z

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?

Create the VM
Insert the DVD
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.

Using table variables (or temp tables) to speed up… or slow down

2012-07-21T14:04:40Z

In SQL Server you can use temporary tables to store intermediate results. This is a common used technique to speed up query processing. Recently I came across a problem where the temporary tables were causing the performance degradation. I'm not going into the different temporary tables types, there are good posts on these. I'm going to walk you through a real-life example and show that the most given answer in the SQL Server world holds true; "It Depends".

In this case the desired result was a single stored procedure that would return multiple results based on some or no search criteria. If a user navigated to the page the complete set was returned and only one filter was applied. In the result set there was a row count, a paged result and some distinct fields from the total results to provide additional filtering. Think of these as slicers and dicers you use in Excel. I will demonstrate the general idea using the AdventureWorks database. I'm using the 2008 version here.

DECLARE @t table (id int IDENTITY, salesorderid int)

INSERT INTO @t (salesorderid)

SELECT soh.SalesOrderID FROM Sales.SalesOrderHeader soh

INNER JOIN Purchasing.ShipMethod sm ON soh.ShipMethodID = sm.ShipMethodID

WHERE sm.Name = 'CARGO TRANSPORT 5'

ORDER BY soh.OrderDate DESC;

-- 1 Get Rowcount

SELECT COUNT(*) AS TotalRecords FROM @t;

-- 2 Get Current page

SELECT soh.OrderDate, soh.SubTotal, st.Name, p.LastName, c.AccountNumber FROM Sales.SalesOrderHeader soh

INNER JOIN @t t ON soh.SalesOrderID = t.salesorderid

INNER JOIN Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID

INNER JOIN Sales.SalesPerson sp ON soh.SalesPersonID = sp.BusinessEntityID

INNER JOIN Sales.Customer c ON soh.CustomerID = c.CustomerID

INNER JOIN Person.Person p ON sp.BusinessEntityID = p.BusinessEntityID

WHERE t.id BETWEEN 1 AND 20

ORDER BY soh.OrderDate DESC;

-- 3 Get SalesTerritory values

SELECT DISTINCT st.TerritoryID, st.Name FROM Sales.SalesTerritory st

INNER JOIN Sales.SalesOrderHeader soh ON st.TerritoryID = soh.TerritoryID

INNER JOIN @t t ON soh.SalesOrderID = t.salesorderid

ORdER BY st.Name;

-- 4 Get OrderDate Year values

SELECT DISTINCT YEAR(soh.OrderDate) as OrderDateYear FROM Sales.SalesOrderHeader soh

INNER JOIN @t t ON soh.SalesOrderID = t.salesorderid

ORDER BY OrderDateYear;

In preparation a table variable is filled with the filtered set of ID's and is sorted in the desired order. In the first query we retrieve the row count for the set. The second set is the actual result set the user will see in the application. In this case it's the first page with 20 results. In step 3 and 4 additional results are returned that are used in the application to provide quick filters for the user. When I run this query on my database I get 4 sets with a total of 35 rows. For this I used 25628 reads. That seemed a lot to me. The execution plan for this one showed some missing indexes and some clustered index scans here and there. I added a few indexes. If you are running along, here they are:

CREATE NONCLUSTERED INDEX temp_SALESORDERHEADER_ShipMethodId ON Sales.SalesOrderHeader(ShipMethodId) INCLUDE(SalesOrderID, OrderDate);

CREATE NONCLUSTERED INDEX temp_SALESORDERHEADER_Select ON Sales.SalesOrderHeader(SalesOrderId, OrderDate) INCLUDE(CustomerID, SalesPersonID, TerritoryID, SubTotal);

CREATE NONCLUSTERED INDEX temp_SALESORDERHEADER_Territory ON Sales.SalesOrderHeader(SalesOrderID, TerritoryID)

CREATE NONCLUSTERED INDEX temp_TERRITORY_Name ON Sales.SalesTerritory(TerritoryID, Name);

CREATE NONCLUSTERED INDEX temp_PERSON_Lastname ON Person.Person(LastName)

This got my reads down to 900. That's to show you will need a proper indexing strategy. But still, more work could be done. One of the things I tried first was use a temporary table instead of a table variable. But this increased my reads. Although I could add indexes the index creation itself would cause even more reads. And in this example the set is not that big but when I was working with the real set things got ugly pretty quickly.

The last thing I tried was to remove the temporary tables altogether and use a view. My thought was to create a view with the search criteria in the view and maybe index that view. This view could easily be reused by the other sets. The definition of the view and the adjusted queries are below:

CREATE VIEW vSearch AS

SELECT soh.SalesOrderID, soh.TerritoryID, soh.OrderDate, soh.SalesPersonID, soh.CustomerID, soh.SubTotal, sm.Name

FROM Sales.SalesOrderHeader soh

INNER JOIN Purchasing.ShipMethod sm ON soh.ShipMethodID = sm.ShipMethodID

-- 1 Get Rowcount

SELECT COUNT(*) AS TotalRecords FROM vSearch WHERE Name = 'CARGO TRANSPORT 5'

-- 2 Get Current page

SELECT v.OrderDate, v.SubTotal, st.Name, p.LastName, c.AccountNumber FROM

(SELECT ROW_NUMBER() OVER (ORDER BY OrderDate DESC) AS row_id, SalesOrderID,

TerritoryID, SalesPersonID, CustomerID, OrderDate, SubTotal

FROM vSearch WHERE Name = 'CARGO TRANSPORT 5') v

INNER JOIN Sales.SalesTerritory st ON v.TerritoryID = st.TerritoryID

INNER JOIN Sales.Customer c ON v.CustomerID = c.CustomerID

INNER JOIN Person.Person p ON v.SalesPersonID = p.BusinessEntityID

WHERE v.row_id BETWEEN 1 AND 20

ORDER BY v.OrderDate DESC;

-- 3 Get SalesTerritory values

SELECT DISTINCT st.TerritoryID, st.Name FROM Sales.SalesTerritory st

INNER JOIN vSearch v ON v.TerritoryID = st.TerritoryID

WHERE v.Name = 'CARGO TRANSPORT 5'

ORdER BY st.Name;

-- 4 Get OrderDate Year values

SELECT DISTINCT YEAR(v.OrderDate) as OrderDateYear FROM vSearch v

WHERE v.Name = 'CARGO TRANSPORT 5'

ORDER BY OrderDateYear;

An additional index was needed:

CREATE NONCLUSTERED INDEX temp_IX_SALESORDERHEADER_ShipMethodID ON Sales.SalesOrderHeader (ShipMethodID) INCLUDE (OrderDate,CustomerID,SalesPersonID,TerritoryID,SubTotal);

This resulted in 466 reads. More optimizations can be done bit a big win was gained by using a view and just retrieving the data directly instead of using temporary sets.

A big lesson lies in the creation of the temporary objects. If you create a simple table with two columns and just 10 records and you select the table you will only get a few reads. If you create a table variable, insert the real table into the variable and select it again you get a couple of hundred reads. On small sets you won't notice a big difference but on larger sets the difference on cpu, reads, writes and duration is really noticeable.

I got my MCM Lab result.... And I failed!

2012-07-06T16:51:48Z

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.

Kerberos and AvailabilityGroups - What you need to know

2012-03-04T17:47:00Z

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

SQL Server 2012 RC0, A New AlwaysOn Experience

2011-11-20T09:01:27Z

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.

 if sys.fn_hadr_backup_is_preferred_replica('AlwaysOnDb1') = 0
begin
    -- This is not the preferred replica
    raiserror( 'Backup is not preferred on this replica',10,1) 
end
else
begin
    -- Backup this database
    backup database AlwaysOnDb1 to disk = '\\dc-01\temp\AlwaysOnDb1_FULL.bak' WITH COPY_ONLY
end
 

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.

Getting HadronCore on SQL Server 2012

2011-10-31T21:29:00Z

One of the new features of SQL Server 2012 is AlwaysOn Availability Groups. You can think of Availability Groups as Database Mirroring on steroids. Database mirroring is only limited to one database, one secondary and if you wanted to read off it you had to create a snapshot. And in the case of a failover the application has to reconnect to another machine.

AlwaysOn Availability Groups provide much more flexibility for high availability and disaster recovery. With AlwaysOn you can:

Use multiple databases within an availability group. The group becomes the unit of failover and not a single database.
Use up to four secondary's.
Secondary’s can be readable without using a snapshot.
Backups can be taken off secondary’s.
A Virtual Network Name can be used to redirect clients.

Another great feature is the support for Windows server core editions and the growing adaption of PowerShell. In this post I will guide you through a gui-less installation of AlwaysOn availability groups. Let’s get started.

If you want to use availability groups you will need a Windows Failover Cluster. No need to run off. This is not the cluster you are used too. Besides, cluster installations have become much easier with Windows Server 2008 and SQL Server 2008. You can still cluster a SQL instance with SQL Server 2012, in fact it has much improved as well. What would you say about SMB support, local storage support for TempDB, multi-subnet clustering and better diagnostics and failover policy. But let’s not get sidetracked, we are doing availability groups.

To prove that SQL isn’t clustered I will install SQL first on all machines and configure Windows Failover Clustering later. All machines are Windows Server 2008 R2 Sp1 Core editions with all updates installed including .NET 4.0. Every machine has one disk, the OS disk. No shared cluster disks or iSCSI drives here. The machines have one NIC and are all bound to a local Contoso domain. I’m using one domain account for all installations and I also added the account to the SYSADMIN server role afterwards. Installation is easy, pop in the disk or whatever you are using and fire away:
setup.exe /q /ACTION=Install /FEATURES=SQLENGINE /INSTANCENAME=MSSQLSERVER
/SQLSVCACCOUNT=CONTOSO\sqlsvc /SQLSVCPASSWORD=p@ssw0rd
/AGTSVCACCOUNT=CONTOSO\sqlsvc /AGTSVCPASSWORD=p@ssw0rd
/IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS
/AGTSVCSTARTUPTYPE=Automatic /SQLSYSADMINACCOUNTS=CONTOSO\SqlAdmins
/TCPENABLED=1

This will install the SQL Server engine on the box, and just SQL Server. That’s all I need, but you are not limited to only installing the engine on core. Second thing is to open up the firewall; netsh advfirewall firewall add rule name="SQL Server Default TCP Port" dir=in action=allow protocol=tcp localport=1433 profile=domain. To prepare thing for the Availability Groups I will open port 5022 as well. This is the port I will use for the mirroring endpoint. I’m using this command; netsh advfirewall firewall add rule name="SQL Server HADR Endpoint" dir=in action=allow protocol=tcp localport=5022 profile=domain.

That’s all. I repeated this on all my four machines and checked the connection between all of them. Next step is getting Windows Failover Clustering working.

For all machines we need to enable Failover Clustering. For this we use the trusty DISM command line tool; DISM /online /enable-feature /featurename:FailoverCluster-Core.

Now we got everything setup to get the cluster going. Installing a cluster on core couldn’t be easier. On the shell type: cluster /cluster:cls-sql-01 /create /nodes:"SQL-02 SQL-03 SQL-04 SQL-05" /ipaddr:192.168.200.8/24 and you’re done. Wait for it to finish and you’ve got yourself a nice cluster, a very basic cluster, but it’s enough to get AlwaysOn going. And you can use the cluster.exe to make it as complicated as you like.

The next step is to enable AlwaysOn. Remember that if you destroy a cluster and rebuild it you also have to disable and enable AlwaysOn. Enabling AlwaysOn has to be done with PowerShell or the SQL Configuration Manager. In my case it will be PowerShell because I don’t have a GUI. There are two ways to do this, start SQLPS, the SQL Server PowerShell host or import the SQLPS module in PowerShell. I prefer the second method. So I first start PowerShell, then import SQLPS using Import-Module SqlPS. This will give a warning which you can ignore. To enable AlwaysOn we use one of the new SQL Server cmdlets, Enable-SqlHadrService. This function accepts a parameter to tell which instance you want to enable and optionally you can force it to restart the instance. I’m using Enable-SqlHadrService –serverinstance SQL-02 –force to enable AlwaysOn and force a restart. If you don’t supply –force you will be prompted to restart the instance. This is repeated for all instances.

Now we have to make a sidestep. In CTP3 we have to enable a traceflag to get all the functionality I wanted in my little demonstration. For this I once again restorted to PowerShell and used New-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQLServer\Parameters" -Name "SQLArg3" -Value "-T9532" -PropertyType "String". Now this it limited to my setup and machine. The general idea is to add an extra value to the startup arguments. If this will work in your environment depends on your configuration. So, it is the registry you are dealing with, if you are not comfortable or don’t know what’s going on in this statement don’t do it.

Now the big moment has arrived. The following long line of PowerShell will setup the availability groups for me, and again for me. Tweak and alter it to satisfy your needs. This script is based on the one provided in Books Online (http://msdn.microsoft.com/en-us/library/gg492181(v=SQL.110).aspx).

 $Primary = "SQL-02" 
 $SecondaryList = "SQL-03","SQL-04","SQL-05" 
 $AgName = "AG_PS_DEMO" 
 $Database = "SAMPLE" 
 $BackupDirectory = "\\DC-01\temp\" 
 $ENDPOINTPORT = 5022 
 pushd
 
 # Backup databases and logs 
 foreach($db in $Database) 
 { 
 Write-Host -ForegroundColor Green "Backup $db (and log) on $Primary" 
 $fullBackupFile = Join-Path $BackupDirectory "$db.bak" 
 $logBackupFile = Join-Path $BackupDirectory "$db.trn" 
 
 Backup-SqlDatabase $db $fullbackupFile -ServerInstance $Primary 
 Backup-SqlDatabase $db $logBackupFile -ServerInstance $Primary -BackupAction "Log" 
 }
 
 New-SqlHadrEndpoint -Path SQLSERVER:\SQL\$Primary\Default -Name HadrEndpoint -Port $ENDPOINTPORT 
 Set-SqlHadrEndpoint -Path SQLSERVER:\SQL\$Primary\Default\Endpoints\HadrEndpoint -State Started 
 New-SqlHadrEndpoint -Path SQLSERVER:\SQL\SQL-03\Default -Name HadrEndpoint -Port $ENDPOINTPORT 
 Set-SqlHadrEndpoint -Path SQLSERVER:\SQL\SQL-03\Default\Endpoints\HadrEndpoint -State Started 
 New-SqlHadrEndpoint -Path SQLSERVER:\SQL\SQL-04\Default -Name HadrEndpoint -Port $ENDPOINTPORT 
 Set-SqlHadrEndpoint -Path SQLSERVER:\SQL\SQL-04\Default\Endpoints\HadrEndpoint -State Started 
 New-SqlHadrEndpoint -Path SQLSERVER:\SQL\SQL-05\Default -Name HadrEndpoint -Port $ENDPOINTPORT 
 Set-SqlHadrEndpoint -Path SQLSERVER:\SQL\SQL-05\Default\Endpoints\HadrEndpoint -State Started
 
 # Create availability replicas as templates 
 $replica1 = New-SqlAvailabilityReplica -Name $Primary -EndpointURL "TCP://SQL-02:$ENDPOINTPORT" -AsTemplate -FailOverMode 'Automatic' -AvailabilityMode 'SynchronousCommit' -Version 11 
 $replica2 = New-SqlAvailabilityReplica -Name $SecondaryList[0] -EndpointURL "TCP://SQL-03:$ENDPOINTPORT" -AsTemplate -FailOverMode 'Automatic' -AvailabilityMode 'SynchronousCommit' -Version 11 
 $replica3 = New-SqlAvailabilityReplica -Name $SecondaryList[1] -EndpointURL "TCP://SQL-04:$ENDPOINTPORT" -AsTemplate -FailOverMode 'Manual' -AvailabilityMode 'AsynchronousCommit' -Version 11 
 $replica4 = New-SqlAvailabilityReplica -Name $SecondaryList[2] -EndpointURL "TCP://SQL-05:$ENDPOINTPORT" -AsTemplate -FailOverMode 'Manual' -AvailabilityMode 'AsynchronousCommit' -ConnectionModeInSecondaryRole 'AllowAllConnections' -Version 11
 
 # Create AG on primary 
 cd SQLSERVER:\SQL\$Primary\default 
 Write-Host -ForegroundColor Green "Create availability group $AgName on $Primary" 
 New-SqlAvailabilityGroup $AgName -AvailabilityReplica ($replica1,$replica2,$replica3,$replica4) -Database $Database
 
 foreach($Secondary in $SecondaryList) 
 { 
 # Join AG on secondary 
 Write-Host -ForegroundColor Green "Join $Secondary to availability group $AgName" 
 cd SQLSERVER:\SQL\$Secondary\default 
 Join-SqlAvailabilityGroup -Name $AgName
 
 # Restore databases and logs 
 foreach($db in $Database) 
 { 
 Write-Host -ForegroundColor Green "Restore $db (and log) on $Secondary" 
 $fullBackupFile = Join-Path $BackupDirectory "$db.bak" 
 $logBackupFile = Join-Path $BackupDirectory "$db.trn"
 
 Restore-SqlDatabase $db $fullbackupFile -ServerInstance $Secondary -NoRecovery 
 Restore-SqlDatabase $db $logBackupFile -ServerInstance $Secondary -RestoreAction "Log" -NoRecovery
 
 trap { Write-Warning "Error while restoring, but continuing." ; continue } 
 }
 
 # Join databases on secondary 
 Write-Host -ForegroundColor Green "Join databases to availability group $AgName" 
 $availabilityGroup = get-item SQLSERVER:\SQL\$Secondary\DEFAULT\AvailabilityGroups\$AgName
 
 foreach($db in $Database) 
 { 
 Add-SqlAvailabilityDatabase -InputObject $availabilityGroup -Database $db 
 } 
 } 
 popd

To finish it off a fancy screenshot of the hard work.

To get things going on Windows Server 2008 Core is not hard. Up to point where you setup the availability groups it’s much faster, easier and repeatable. Setting up the availability groups can be done from a remote machine using the wizard if you like. The script I demonstrated here worked on my setup, you have to change it to get things going on your side.