Microsoft Virtualization Support Policy

We do work on a lot of environments which have VMWare Virtualization. The first thing that needs to be done is to find the version of the ESX in use in the environment.

VMware ESX Update

Build Number

ESX 3.5.0 Update 1

64607

ESX 3.5.0 Update 2

110268

ESX 3.5.0 Update 3

123630

ESX 3i (3.5.0) Update 3

123629

If you trying to seek Microsoft CSS support for a non-Microsoft virtualized environment, then you need to be aware of the Virtualization Support Policy:

http://support.microsoft.com/kb/897615

Also if you want to find out if your virtualized environment is supported, then you can use the Support Policy Wizard.

Here is link for a step-by-step guide to find out the ESX versions and builds:

http://www.techhead.co.uk/how-to-determine-the-vmware-esx-or-esxi-build-version

Reference articles:

Determining detailed build number information for VMware ESX 4.0.x hosts

Determining detailed build number information for VMware ESX 3.0.x and 3.5.x hosts

VDI application throwing back HEX – 0x080770007

While taking a VDI backup of a SQL Server database, you get a VDI error with the following HEX code 0x080770007.

If you used simple.exe to perform the backup, then you would get the following error:

C:\TEMP>simple.exe B <SERVERNAME>\<INSTANCENAME> testdb
Connected to Server: <SERVERNAME>\<INSTANCENAME>
Backing up database: testdb
Performing a BACKUP using a virtual device.
VDS::Create fails: x80770007

If you lookup the 0x080770007 (VD_E_INSTANCE_NAME VD_ERROR) translates to failed to recognize the SQL Server instance name.

Then check if the following condition holds true:
There is no DEFAULT instance of SQL Server on the machine where you are trying to take a VDI backup and the SQL instance that you are connecting to perform a backup is a named instance. If above condition is true, then the issue is with CreateEx function of the interface IClientVirtualDeviceSet2. The CreateEx function is used to create the virtual device set and has the following syntax:

HRESULT IClientVirtualDeviceSet2::CreateEx (
LPCWSTR lpInstanceName,
LPCWSTR lpName,
VDConfig* pCfg);

The "lpInstanceName" parameter identifies the SQL Server instance to which the SQL command needs to be sent to. If the CreateEx method has NULL as the first parameter, then it would always connect to the Default instance. If the server doesn’t have a default SQL instance, then the first parameter needs to be provided with the instance name.
Eg. If you have a named instance on the server as "SERVER1\SQLINST1, then the first parameter for CreateEx should be "SQLINST1".

Another issue that can cause this error is when you use simple.exe to take a backup from an instance that is not installed on the box from which you are running simple.exe from. This can be true for other backup applications as well.

The reason for this is that simple.exe or any backup application using SQLVDI.DLL calls, you get the interface to the device set using the following piece of code. Eg: For simple.exe:

hr = CoCreateInstance (
        CLSID_MSSQL_ClientVirtualDeviceSet, 
        NULL,
        CLSCTX_INPROC_SERVER,
        IID_IClientVirtualDeviceSet2,
        (void**)&vds);

CoCreateInstance is used when you want to create only one object on the local system. If you planning to take a backup from a remote instance and using code similar to the one used in simple.exe, then you need to use CoCreateInstanceEx since the interface set would reside on the remote computer.

Another issue is that when we call CreateEx to create the virtual device set, we can only pass in the instance name and not the computer name. So, the backup application’s code should be designed in a correct manner for this to work which means .

Most known vendor applications Legato, NetBackup, Tivoli, LiteSpeed, SQLSafe etc. take care of these considerations!

Links: Modified simple.exe application code

Quick facts about applying updates on a SQL Cluster

When a SQL Server 2005 Service Pack is applied on a clustered SQL instance (SQL 2005 and above), the Service Pack/Cumulative Update/Hotfix will upgrade all the member nodes, if a cluster-aware component is selected on the component selection screen. There is no need to run the SP installation after failing over the SQL resources to the passive nodes and re-applying the SPs. The passive nodes are upgraded using a Task Scheduler job created by the SQL setup.

As for the non-cluster aware components like SSIS, Tools etc., if you select them along with the clustered components, then the patches will be applied for the non-cluster aware components as well on the member nodes. In case, you do NOT select the non-cluster aware components while running the update for the cluster aware components, then you will manually have to patch these components.

When applying patches for non-cluster aware components, you do not need to failover the resources.

Setup takes the SQL resource OFFLINE/ONLINE on the active node. The resource is taken offline to patch the binaries and then it is brought online by SQL Setup to apply the configuration scripts. There are no failovers involved during SQL 2005 patching.

Cluster Aware components: Database Services, Analysis Services

Non-cluster Aware components: Integration Services, Reporting Services, Tools

IO Frozen messages while taking NT Backup for SQL databases

I recently replied on a MSDN forum post where the question was regarding why the following message was showing up for SQL database files even though no SQL Server database file was being backed up by NTBackup.

Let’s assume that you have a folder called D:\Foo on your machine which you are backing up using NTBackup. On the same driver you have SQL Server database files residing in another folder which are not being backed up. When you start the NTBackup job to perform the backup, you will find the following messages in the Application Event Logs:

Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 3197
Date:  1/2/2010
Time:  1:35:31 AM
User:  NT AUTHORITY\SYSTEM
Computer: <server name>
Description:
I/O is frozen on database msdb. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.

The above message will be reported for each and every database on the instance which has files on the D:drive. This will be immediately followed by an equal number of messages for I/O resuming for the same set of databases.

Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 3198
Date:  1/2/2010
Time:  1:35:31 AM
User:  NT AUTHORITY\SYSTEM
Computer: <server name>
Description:
I/O was resumed on database msdb. No user action is required.

The first question that would cross your mind is whether this goofy behavior is actually a bug. This is a by-design behavior of VSS (Volume Shadow) framework. NTBackup uses VSS to perform backups. When VSS is asked to perform a snapshot backup, it will ask the Writers registered with system to kick in which have files on that particular drive. Since, the drive contains SQL database files, the snapshot is created during which SQL Writer freezes IO for the SQL databases. After the snapshot is created, NTBackup finds that the database files are in the exclusion list (i.e. not being backed up) due to which the files do not reside in the physical snapshot backup file. From the SQL Server Errorlogs, you will be able to confirm this as the following message would be printed for each database whose file reside on the drive in question:

Backup Database backed up. Database: msdb, creation date(time): 2005/10/14(01:54:05), pages dumped: 1, first LSN: 319847:440:173, last LSN: 319848:24:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {‘{C0903B61-9239-4747-86C4-D4ADBED76428}3’}). This is an informational message only. No user action is required.

Notice that the number of pages dumped is 1 which means that the database was not actually backed up.

This behavior is documented under:

951288    SQL Server records a backup operation in the backupset history table when you use VSS to back up files on a volume
http://support.microsoft.com/default.aspx?scid=kb;EN-US;951288

This should NOT be a problem unless you are running into the issue mentioned in article below in which case you need to disable SQL Writer service:
937683 Error message when you try to restore a database by using SQL Server Management Studio in SQL Server 2005 after you use the Backup tool: “Restore failed for Server ‘<ServerName>’ (Microsoft.SqlServer.Smo)”
http://support.microsoft.com/default.aspx?scid=kb;EN-US;937683

This behavior will be exhibited for any application that uses VSS to backup files from a drive that has SQL Server database files. Since, SQL Database files are not like any other files on the filesystem, VSS invokes SQL Writer to perform the backups of SQL database files. If your SQL Writer service is experiencing issues, then your SQL database backups performed using VSS wouldn’t be trustworthy!

How to find out which object belongs to which filegroup

Recently I found the need to write a query which would tell me which table belonged to which File Group or Partition Scheme in a SQL Server 2005 database. I found that a system SP exists called “sp_objectfilegroup” to return the filegroup information for one table. This SP takes a parameter which is the object id of the table that you are interested in. However, using the sys.data_spaces catalog view and tying it back to sys.indexes output, I can find out which table belongs to which filegroup on the database. 

select object_name(a.object_id) as ObjectName, 

IndexName = case when a.name is null then 'Heap' else a.name end,(select name from sys.data_spaces where data_space_id = a.data_space_id) as FileGroupName 

from sys.indexes a where index_id < 2

 

I’m sure many people can find alternate ways to do so 🙂 but I thought of posting one in case I needed the same script in the future again.