Creating a Client VM on Azure for testing

I had recently blogged about setting up a SQL Server environment on Microsoft Azure for testing purposes. With the availability of Windows 7 and Windows 8.1 images, developers and testers could take advantage of deploying the applications to these Azure VMs for testing without having to create or setup new machines in their environments. Deploying Windows 7 and Windows 8.1 Enterprise clients to Microsoft Azure is now available for MSDN subscribers.

When you attempt to create a new Virtual Machine, the Wizard will offer you a choice of picking a virtual machine from a Gallery . imageThe gallery has a pre-created set of images which you can use to create your virtual machine and can save you time from additional post-setup configurations. I am looking to create a Windows 8.1 machine which comes in two flavors:

a. Windows 8.1 Enterprise (x64)

b. Windows 8.1 Enterprise N (x64)

I chose Windows 8.1 Enterprise x64.

In the virtual machine configuration page, I provided the details for the VM like the machine name, user account  as seen in the Screenshot 1. I picked an A1 size configuration (Basic) for the client. This can obviously be extended at will at a later time.

On the next configuration page, I used an existing cloud service that I had and picked the pre-created storage account for hosting the VHDs. Creating a storage account is not mandatory as the wizard for creating a VM will create a randomly named storage account for you. However, I do not like have entities named with weird alphanumeric sequences which is why I chose to pre-create the storage account.

In the next configuration screen, I picked the VM Agent (enabled by default) and the Microsoft Antimalware which is in Preview (see Screenshot 2). image

Now you have a virtual machine created in a few minutes which is ready for testing.

If you need to install additional software for testing, then install it on the virtual machine by establishing a remote desktop connection to the Azure VM. Once your post-deployment steps are complete, capture an image of the virtual machine. This can be done using the Capture option available in the Azure Management portal by highlighting the VM instance that you want to capture the image of! Once the image is captured, it will be available under the Images tab under Virtual Machines in the Azure Management portal. This however has a caveat! The image is created in the same container where your VM VHD is stored. Subsequent captures of the VM are stored in the same Storage Account. If you do not want this behavior, then you will need to use the Create Image wizard available in the Images tab. This is something that I will show in another blog post.



Deploying Windows 7 and Windows 8.1 Enterprise Clients to Microsoft Azure Available for MSDN Subscribers

Azure VM Image


Volume Shadow barfs with 0x80040e14 code

I was replying on a MSDN forum thread recently and found that the a VSS backup was failing due to large number of database volumes residing on a particular volume.

I re-created the issue on my box by limiting the number of Max Worker Threads to 128 on my SQL Server 2008 instance. After making the change to the number of worker threads, I created 99 databases on the D: drive of my server using the following script:

set nocount on
declare @cntr int = 1,@sql varchar(8000)
while @cntr < 100
set @sql = 'CREATE DATABASE [db'+cast(@cntr as varchar(5))+'] ON  PRIMARY

( NAME = N' 'db'+cast(@cntr as varchar(5))+''', FILENAME = N' 'D:\DBFiles\db'+cast(@cntr as varchar(5))+'.mdf' ')
( NAME = N' 'db'+cast(@cntr as varchar(5))+'_log' ', FILENAME = N' 'D:\DBFiles\db'+cast(@cntr as varchar(5))+'.ldf'')'

set @cntr = @cntr + 1

I then attempted to backup the DBFiles folder on the D: drive using Windows Server Backup and it failed.


After that I looked into the SQL Server Errorlog and found the following errors:

2010-12-06 03:39:15.280 Backup       Error: 3041, Severity: 16, State: 1.2010-12-06 03:39:15.280 Backup       BACKUP failed to complete the command BACKUP DATABASE db68. Check the backup application log for detailed messages.

2010-12-06 03:39:15.370 spid53       Error: 18210, Severity: 16, State: 1.2010-12-06 03:39:15.370 spid53       BackupVirtualDeviceFile::PrepareToFreeze:  failure on backup device ‘{DB840D1B-DD17-4D73-83F1-7390F676DEE4}1’. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).


I had already documented earlier that the infamous 995 error message could be a problem with the sqlvdi.dll but in this case, it is not!

The next thing I did was to pick the errors from the Application Event logs and I found the following errors repeated multiple times:

Error    12/6/2010 3:39:15 AM    SQLVDI    1    None    SQLVDI: Loc=SignalAbort. Desc=Client initiates abort. ErrorCode=(0). Process=3920. Thread=11604. Client. Instance=SQL2008. VD=Global\{DB840D1B-DD17-4D73-83F1-7390F676DEE4}1_SQLVDIMemoryName_0.Error    12/6/2010 3:39:15 AM    SQLWRITER    24583    None    Sqllib error: OLEDB Error encountered calling ICommandText::Execute. hr = 0x80040e14. SQLSTATE: 42000, Native Error: 3013

Error state: 1, Severity: 16

Source: Microsoft SQL Server Native Client 10.0

Error message: BACKUP DATABASE is terminating abnormally.

SQLSTATE: 42000, Native Error: 3224

Error state: 1, Severity: 16

Source: Microsoft SQL Server Native Client 10.0

Error message: Cannot create worker thread.

If you look closely at the above error, then you will find that the following error message stands out like a sore thumb: “Cannot create worker thread”. After this issue occurs, you will find that the SQLWriter shows the following when you execute the command: vssadmin list writers from a command prompt window:

Writer name: ‘SqlServerWriter’   Writer Id: {a65faa63-5ea8-4ebc-9dbd-a0c4db26912a}

   Writer Instance Id: {9075f235-fdee-4445-851b-a69c54bd8b33}

State: [8] Failed

Last error: Non-retryable error

Ensure that you do not have any memory related errors in the SQL Server Errorlogs or any DBCC MEMORYSTATUS outputs printed in the Errorlog during the time of the backup failure. If there aren’t, then the above issue occurs due to the SQL instance hitting a worker thread limit.

The reason for this is explained by Rob Dorr (Principal SQL Server Escalation Engineer at Microsoft) in his blog post:

The controller and database worker come from the worker pool.  So if your max worker thread is configured for 255 the answer would be a max of 255 / 2 = 127 databases.    You probably won’t get to 127 with this configuration as you have system workers (lazy writer, checkpoint, log writer, and others) that already are using workers.

On my SQL instance, I have 120 database and I need about 240 worker threads to complete this operation. However, I have only 128 worker threads configured for my SQL instance. If you have Dedicated Administrator Connection enabled for the SQL instance, then you can this troubleshooting further and execute a select against the DMV sys.dm_os_schedulers (for SQL Server 2005 or above) and get an output of the following type:

status                 counts
———————- ——–

HIDDEN ONLINE          168



Query used:

select status, count(*) as counts
from sys.dm_os_schedulers
group by status

As you will see that there are about 168 hidden online schedulers created for performing the Snapshot backups. But SQL instance in question doesn’t have sufficient work threads to facilitate this operation.

What can you do in such situations:

1. Increase the number of worker threads to account for the additional worker threads required for completing the VSS backups. This can be counter-productive as it can lead to non-Buffer Pool memory contention on 32-bit instances of SQL Server. The higher the number of max worker threads on your SQL Server instance, more the amount of non-BPool memory is consumed for the thread stack. This results in depletion of the available Buffer Pool memory.

2. Change the physical location of the SQL Server database files so that all the database files do not reside on the same volume.

3. Perform a VSS Backup with SQL Server VSS Writer service turned OFF. However, you cannot recover any SQL Server database files from such a Volume Shadow Backup.

4. Use SQL Native backups or other VDI backup tools (non-Snapshot) to perform SQL Server database backups.

Note: This also happens when you select any file on a drive which has a high number of SQL Server database files. This is because the SQL Server VSS Writer tries to enumerate the database files for the SQL Server instance that reside on the volume that has been selected for the Snapshot backup.

Location of SQL Binaries can flip out Bare Metal backups

Bare Metal recovery allows system administrators to recover a system from failure within a short period of time and also manage system state backups in a more efficient manner. However, there is a slight catch here when you have SQL Sever binaries installed in a non-default location.

I recently worked on an issue where Windows Backup Admin was listing a non-system drive as a critical component when trying to perform a bare-metal backup using the following command:

wbadmin start backup -allcritical -backupTarget:<any existing drive name>:

When I looked into the contents of the drive, I found that the drive only had SQL Server database files and binaries on it. This is the output that I got:

wbadmin start backup -allcritical -backupTarget:S:
wbadmin 1.0 – Backup command-line tool
(C) Copyright 2004 Microsoft Corp.

Retrieving volume information…
This will back up volume OS(C:),New Disk(D:),New Disk(E:) to S:.
Do you want to start the backup operation?
[Y] Yes [N] No N

The operation ended before completion.

I then decided to use DiskShadow to show me all the critical components marked by the VSS Writers. I got the following output:

WRITER “System Writer”:

– Volumes affected by this component:

– \\?\Volume{2407f815-f9c4-11df-aef7-806e6f6e6963}\ [C:\]
– \\?\Volume{22c3ff31-f965-11df-a20b-00155df1c42a}\ [D:\]
– \\?\Volume{0342683d-f96a-11df-8a1f-00155df1c42a}\ [E:\]

This is because the SQL Server program binaries were installed on the D: and E: drives. And these are the paths that show up as critical as per the System Writer:

– d:\program files\microsoft sql server\100\dts\binn
– d:\program files\microsoft sql server\msas10.mssqlserver\olap\bin
– d:\program files\microsoft sql server\msrs10.mssqlserver\reporting services\reportserver\bin
– d:\program files\microsoft sql server\mssql10.mssqlserver\mssql\binn
– e:\program files\microsoft sql server\msas10.inst01\olap\bin
– e:\program files\microsoft sql server\msrs10.inst01\reporting services\reportserver\bin
– e:\program files\microsoft sql server\mssql10.inst01\mssql\binn

DiskShadow command used: list writers detailed


A volume is a critical volume if it contains system state information. The boot and system volumes are included automatically. The requester must include all volumes that contain system-critical components reported by writers, such as the volumes that contain the Active Directory. System-critical components are marked as “not selectable for backup.” In VSS, “not selectable” means “not optional.” Thus, the requester is required to back them up as part of system state.

Based on the above description, it is clear that I cannot perform a Bare Metal backup without including D: and E: drives.

Note: The above is just an example. There might be other system-critical components which are marked as not selectable for backup by other VSS Writers. VSS works on Volume Shadow concept. So, if a particular drive in a volume is marked as critical, then the entire volume would need to be backed up.

As I mentioned earlier, for system critical components, the “Is selectable: FALSE” is set (VSS_CF_NOT_SYSTEM_STATE flag is set for non-system components). In the above example, the drive on which the SQL binaries exist is marked as critical by the System Writer.

Setting up Perfmon Logs

One of best ways to monitor your system performance for disk contention, high CPU, memory crunch etc. is to use Windows Performance Monitor Logs and Alerts. These logs can be used to capture historical information about different performance objects and would help us understand what was happening on the system at that point of time. In Windows Server 2008 & Windows Vista, Perfmon has a cool new utilty (a management MMC snap-in) called Reliability Monitor which helps you a bird’s eye view of your system stability.

Steps to setup Perfmon data collection for Windows Server 2003/XP
This can be done by opening up Perfmon:

  1. Click on “Performance Logs and Alerts
  2. Click on “Counter Logs
  3. Right click on the same and click on “New Log Settings
  4. Give the log a name
  5. Click on “Add Objects” and add all the objects that are needed for your data collection
  6. Click on the “Log Files” tab
  7. You can change the log file location by clicking on the “Configure” button
  8. Click on the “Schedule” tab and then for “Start Log” and “Stop Log”, select the “Manually (Using the Shortcut menu)” option
  9. Click on the “OK” button to create the log
  10. Then right-click on the Log and click on “Start” to begin the logging
  11. Right-click on the log file and click on “Stop” to end the logging

How to setup a Perfmon data collection for Windows Server 2008/ Vista/ Windows Server 2008 R2

  1. Open up the Performance Monitor snap-in (Start -> Run -> perfmon)
  2. Expand the “Monitoring Tools” option and Right Click on “Perfomance Monitor” -> “New” -> “Data Collector Set
  3. Give the data collector set a name and click on Next
  4. Give the location where you want to save the Perfmon Logs and click on Next
  5. Here you can provide the Run As user of leave that as the Default user
  6. Select the option “Save and Close” and click on Finish
  7. Then go back to the Perfmon snap-in main window and you should see a Data Collector set with the same name that you created under Data Collector Sets -> User Defined
  8. Click on the Data Collector Set and on the right hand pane, you should see a System Monitor Log Performance Counter. Right click on it and click on properties.
    • Under the Performance Counters tab, add the relevant performance counters required for your data collection, set the log format (binary, SQL, CSV, comma separated) and the sample collection interval time.
    • Under the File tab specify the file format name and logging properties for the file.
  9. Now you can start the Perfmon data collection by right clicking on the Data Collector Set and click on Start or you could do some more work for maintaining disk space by setting up some rules using the Data Manager to create .CAB files or delete older files in case we are setting up perfmon logs for long term monitoring.

There is hardly any performance impact in collecting perfmon logs on any server. Any perfmon data collection unlike other forms of data collection don’t generate voluminous data in terms of size but contain hordes of infromation which can provide valuable insight to an issue which is impacting critical business applications running on a server.

Windows Reliability and Performance Monitor