Category Archives: SQL

Optimizing virtual SQL Server performance

Some months ago I implemented these steps and saw a striking improvement in the performance of our applications (between 2x and 4x depending on the query):

  • Firstly, if you’re using iSCSI, make sure the network switches are ones which have been validated as ok by your storage vendor. I’ve run into poor performance using ones which ought to have worked and offered all the required features (Flow Control, Jumbo Frames), but which in reality were causing problems.
  • If you’re using iSCSI with a software initiator (be it at hypervisor or guest OS level), consider using Jumbo Frames to reduce I/O related CPU activity.
  • Move your VMDKs to a new SAN VMFS volume that is Thick-Provisioned. Although in my environment the EqualLogic array extends LUNs by 16MB at a time, over time this can fragment things appreciably. With a 1TB LUN this can get pretty bad.
  • Use Storage vMotion to make the VMDK files Thick Provisioned too. This eliminates fragmentation of the VMDK since it’s no longer growing in small increments. I think this made quite a big difference, despite a whitepaper from VMware denying a performance impact. The reasoning is that since the storage array has a big cache, having the data fragmented all over the disks shouldn’t really matter that much. I don’t really believe it, and my own results seemed to prove otherwise (what about a backup operation which will need to read your data sequentially in one long pass?). My SQL server vMotion operations were very slow compared to other servers, suggesting they were heavily fragmented in their old location.
  • Move all of your databases (including these system ones: msdb, model, master), their logs, and fulltext catalogs to a SAN LUN directly attached inside the Guest VM using Microsoft iSCSI initiator and your SAN vendor’s Integration Tools. If you use Vmxnet3 adapters then the TCP calculation overhead will be handled by the hypervisor which in turn can be passed to Broadcom bnx2 TOE NICs if you’re using vSphere 4.1. Having the databases on a separate LUN allows off-host backup of the databases using Backup Exec with your SAN vendors’ SQL-aware VSS Hardware Provider. Database backups can then occur at any time without any impact to the SQL server’s performance. I have written a dedicated post on this subject.
  • Create that SAN partition with its NTFS blocks aligned with the SAN’s own disk blocks to ensure no needless multiplication of I/O (64KB offset for EqualLogic – full explanation here).
  • Keep TempDB on the C: drive in its default location. That way I/O to that database is segregated and can be cached differently since it is using VMware’s iSCSI initiator and not the Microsoft initiator. Typically TempDB has high I/O, but it’s not a database that you need to back up so you don’t need to be able to snapshot it on the SAN.
  • Create an SQL management task to rebuild and defragment the database indexes and update their statistics every week (say, Sunday at 3:00am).
  • Change database file autogrow amounts from 1MB to 64MB to mitigate NTFS-level fragmentation of the database MDF files as they grow.

Upgrading to vCenter 4.1 with bundled SQL Express Edition – database migration fails

My infrastructure uses a bundled SQL Express Edition database because it isn’t hugely complex, and I didn’t want too much dependency on other servers (themselves VMs). I encountered problems upgrading the vCenter database while moving from Windows Server 2003 R2 SP2 x86 to Windows 2008 R2. I was migrating from vSphere 4.0U1 to 4.1. Perhaps this precise combination of versions was the problem, or perhaps it was that my database started life as VirtualCenter 3.5.

The process seems simple enough – unzip and run the Data Migration Tool from the installation media on the source vCenter server, move this folder (now with \data added) to the destination server and launch the install.bat script. However, there seem to be two major snags. The first is that the backup process will fail:

DB logs: HResult 0x2, Level 16, State 1 Named Pipes Provider: Could not open a connection to SQL Server

VMware has a knowledgbase article about this. They claim it’s caused by a misconfiguration of the SQL 2005 Express Edition instance which is pretty rich considering it was set up by their own installer. Make the named pipe change they suggest and it will work. Now unplug this machine from the LAN or disconnect its network adapter in vSphere if it’s a VM – remember you can connect the VI Client directly to the hypervisor which is hosting it.

The destination server needs to be configured with the same hostname as the source. I had then assumed that the restore tool would need to be run after a new install of vCenter 4.1 was placed on the destination server, so I installed vCenter. Then I discovered that the install.bat script in the datamigration folder refuses to run if it detects the product is already present. So naturally I uninstalled it and tried again. Perhaps this is what messed things up, or perhaps it’s because I’m using Windows 2008 R2.

Anyway, the datamigration\install.bat script kicks off the main product installer, supposedly importing all your backed up settings.

According to the vCenter 4.1 Upgrade Guide page 40 item 10 you are supposed to:

Select Install SQL Server 2005 Express instance (for small-scale deployments) and click Next.

Item 16 on the same page states that:

When the vCenter Server installation finishes, click Finish. The data migration tool restores the backed up configuration data.

If you do this you may, like me, discover that it actually doesn’t work and you end up with a completely blank database instance. Consulting datamigration\logs\restore.log I found no reference at all to any database restore.

My workaround

Go to your original vCenter server. Open the Data Sources MMC snap-in. In System DSN you should see an entry like so:

Note down the details, then create the same entry on your destination server (this will create a 64bit DSN). Notice how on page 38 of the Upgrade Guide it specifically states that:

If you use the data migration tool to migrate a SQL Server Express database located on the vCenter Server system to a new system, you do not need to create the 64-bit DSN. The data migration tool creates the DSN as part of the installation process

Apparently sometimes you do need to create the DSN.

On the destination server, copy the file \datamigration\data\vc\vc_upgraded_db and paste it in C:\temp. Rename it to vc_upgraded_db.bak.

Still on the destination server download, install and run the 64bit SQL Management Studio Express. Even if you’ve uninstalled vCenter, the SQL Express Edition instance will be left behind. If there’s not already one from a failed install, create a local database called VIM_VCDB. Restore the backup in C:\temp\vc_upgraded_db.bak over the top, paying attention to select Options -> Overwrite Existing Database and browsing to the target file locations of both the mdf and ldf files – the old database was found in C:\Program Files\Microsoft SQL Server\MSSQL.1\Data but on the 64bit system it’s C:\Program Files (x86)\…

Right-click your database, and select Properties. In Options, make sure your database recovery model is set to Simple. If you don’t do this your transaction log will fill up in a couple of days and the vCenter services will stop. In my case it seemed to have defaulted to Bulk-Logged.

Once that’s done you’ll need to update your newly created DSN to set the default database to VIM_VCDB. Now run datamigration\install.bat once again but this time opt to use an existing database as shown below:

Strangely, you will find that the you cannot use the SYSTEM account for the vCenter services however this can easily be changed in the Services MMC snap-in later.

And that’s it. You should end up with a working install with your data intact. One final tweak is to delay launch of the vCenter services so they don’t fail to start up at boot time.

UPDATE – after wasting a number of hours today with this, I’ve done some more searching and found this VMware KB article which basically just admits that the Data Migration Tool sometimes doesn’t work, and won’t even report errors in the log! When I download something as important as this, I sort of take it for granted that I won’t have to Google “vcenter data management tool does not migrate database” the moment I try using it (can’t believe I didn’t try that).

Moving your SQL 2005 databases ready for VSS off-host backups

Many storage vendors now offer hardware Volume ShadowCopy Service providers for their storage arrays which allow the SAN itself to carry out the snapshot, rather than the underlying OS. These providers are Exchange- and SQL-aware so they will quiesce the transaction logs just before the snapshot.

The big win here is off-host backup – the target server asks the SAN to snap the data volume then carries on as normal. The backup media server meanwhile will mount this SAN snapshot and back it up directly from the SAN. In this way you can backup SQL and Exchange environments in the middle of the day without any performance degradation (assuming of course that you have the IOPS headroom on the SAN). Symantec Backup Exec 12.5 and later supports this technology but it must be purchased as an option – Advanced Disk-based Backup Option or ADBO.

However, to off-host backup Exchange or SQL you will need to have both the databases/mail stores and the transaction logs on the same SAN LUN. This flies in the face of the old wisdom of segregating logs onto RAID1 spindles, but it’s important to realise that a modern SAN makes this perfectly viable. The EqualLogic PS4000XV in my environment for instance has a write latency of <1.0ms in RAID50. Microsoft used to recommend keeping SQL logs on disk with a write latency sub 10ms (now they say 1-5ms).

Moving all of the transaction logs on a crowded SQL server is tricky for several reasons:

  • the Transact-SQL database alter command requires you to know the database’s logical filenames. On an SQL 2005 server, these are largely predictable but it gets complicated when some of the DBs have migrated from SQL 2000 and if some of them were restored from backups of databases with different names (dev or test versions which then went into production for example).
  • the system databases are likely to be on C: and if you want to grab all DBs in one backup job these will need moving too (TempDB is usually ignored by most backup software and can stay where it is).
  • though some guides on the Web suggest detaching and re-attaching the DBs – this is a surefire way to end up with a total disaster since the re-attached DBs will have new GUIDs which will wreck SharePoint amongst others.

Backing up then restoring your databases specifying new file paths is one method but the danger is that you would need to isolate them so that no changes occurred during that time window (which could be a long time).

Moving user databases

The best solution for these is 99% careful preparation work – to build a long list of T-SQL database alter commands to change the SQL file references, and a batch script to move the actual files to the database drive. You can also use this as an opportunity to clean up any badly named files, and move ones that are in the wrong place.

It is highly recommended that if you haven’t already done so, you should set the following registry values on the SQL server which will guard against future inconsistencies. If they already exist, check they’re still valid:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultData
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultLog
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\BackupDirectory
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\FullTextDefaultPath

For each database you need to run the following Transact-SQL:

Use [DBname]
Select * from sys.database_files

This will return all the files in the filegroup including full text catalogs (if they exist) together with their logical names (name column):
Database logical filenames

In this example the transaction log is already in the desired location, but it if was in say C:\TRANSACTIONS LOGS we would need to write:

alter database [SUSDB] modify file (name = SUSDB_log, filename = 'G:\DATABASES\SUSDB_log.ldf')

You would then add this to your file move batch script:


My method was to run a full SQL backup to commit the transaction logs (less data to move), run the alter database commands all at once (which don’t take effect until the SQL Server service next starts), stop the SQL Server service, run the file move batch script, check for any errors, then start the SQL Server service again. Once it’s up, you can try to expand each database in SQL Management Studio. Any databases with damaged file paths will not expand. Refer back to your command prompt window to try and figure out what went wrong (usually a typo).

In this way you should be able to move all of the logs with a bare minimum of downtime – several minutes in my case.

Moving system databases

Moving system databases is fairly straightforward, but it will require a little more downtime. Again, I’d probably leave TempDB where it is to separate its I/O from the rest as it can be high and we don’t need to back it up. If you do want to move it, the procedure is the same as any non-system database. The rest though are special cases.

Run the following and note the current file locations which will probably be in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA

use model
select * from sys.database_files
use msdb
select * from sys.database_files
use master
select * from sys.database_files

Now close SQL Management Studio and run the following from a Command Prompt (the parameters are case sensitive!):

net stop mssqlserver
net start mssqlserver /c /m /T3608

Open SQL Management Studio again but read this carefully. With these startup parameters, SQL Server will only allow a single connection. The default behaviour of the GUI is to open the Object Explorer window once you connect, which counts as a connection. You need to click on the Disconnect button, and close the Object Explorer child window. You should then be able to open a New Query.
If you closed the Object Explorer without disconnecting you’ll get the error “Server is in single user mode. Only one administrator can connect at this time.” and you’ll need to stop and start the service again, as above, and repeat. Next:

sp_detach_db 'model'
sp_detach_db 'msdb'

Move the files to the new location (logs and databases remember), then run the following taking care to substitute in your new file paths:

sp_attach_db 'model','G:\DATABASES\model.mdf','G:\DATABASES\modellog.ldf'
sp_attach_db 'msdb','G:\DATABASES\msdbdata.mdf','G:\DATABASES\msdblog.ldf'

Stop the SQL Server service. Start it again normally (no parameters) and check you can expand model and msdb in Management Studio.

We just have the master database left to move. Stop the SQL Server service again. Move master’s log and database files to the new location. On the SQL server machine’s console, open Start Menu > Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager.
In the category SQL 2005 Services, select SQL Server (MSSQLSERVER) and look at the Properties. Select the Advanced tab. Select Startup Parameters and pull down the dropdown next to it.
Change the value from the defaults of:

-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

to your new file paths (don’t change the error log path by accident):

-dG:\DATABASES\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lG:\DATABASES\mastlog.ldf

SQL Server Configuration Manager
Finally, start the SQL Server Service. Done!

The trouble with full-text catalogs

If you rely on the EqualLogic Auto-Snaphot Manager to tell you whether your databases now support SAN snapshots you could be in for a surprise when you backup using ADBO in Backup Exec:

V-79-57344-34086 – ADBO: Offhost backup initialization failure on: “”.
Snapshot provider error (0xE0008526): Backup Exec could not locate a Microsoft Volume Shadow Copy Services (VSS) software or hardware snapshot provider for this job. Select a valid VSS snapshot provider for the target computer.
Check the Windows Event Viewer for details.

This is an awful error message because it doesn’t really describe the problem (and you won’t find anything meaningful in the Event Viewer). It almost looks like a registration failure of the Hardware VSS Provider, which is misleading, and caused me about 2 hours of out-of-hours work reinstalling it, taking the server offline, etc. to satisfy Symantec Support. However, run a job with the same selection list but using normal AOFO (Advanced Open File Backup) and you get:

AOFO: Initialization failure on: “”. Advanced Open File Option used: Microsoft Volume Shadow Copy Service (VSS).
V-79-10000-11219 – VSS Snapshot error. The volume or snapped volume may not be available or may not exist. Check the configuration of the snapshot provider, and then run the job again.
The following volumes are dependent on resource: “C:” “D:” “G:”.

Much clearer – there’s a dependency on the D: drive being detected, the drive I migrated from. By chance I changed the backup selection list realised that some databases backed up while others didn’t. The cause turned out to be a full text catalog.

The EqualLogic ASM only checks the database and log files, not full-text catalogs. Moving these seems to be pretty difficult. Microsoft have an MSDN document describing database moves (see section on catalogs further down the page). I have tried following this process to the letter, and when that didn’t work I tried various permutations of stopping the SQL Server service, the SQL FullText Search service (which seems to autorestart), the SQL Server Agent service, copying the files, not copying the files (expecting SQL to move them) etc. No combination seemed to work for me. What I found was that, while it is easy enough to move the catalog path like so:

alter database [ExampleDB] modify file (name = [sysft_ExampleDB], filename = 'G:\DATABASES\FTData\ExampleDB')

there is some meta data that does not get updated and the ADBO backup will still fail when the VSS provider checks all the file dependencies.
sys.database_files shows the correct paths. Eventually I discovered that

Select * from sys.fulltext_catalogs

still showed the old location for the catalogs. The only way I could find to get this to update was to rebuild the full-text catalog in SQL Management Studio – expand the database > Storage > Full Text Catalogs > right-click > Rebuild.

For me this was acceptable and quick, but I imagine some infrastructures might not be so tolerant of a rebuild.