Recover Microsoft Cluster VMs Not Power On After Migration

A lesson to remember if you do not have the time to read this entire post: do not migrate the cluster VMs without fully understanding the impact.

Here is our story.

We had a Microsoft SQL 2008 Cluster VMs in the CIB (see my previous post about various Microsoft Cluster VMs configuration). The shared disks of the cluster VMs were on an EMC SAN. When the free space of EMC SAN was running low, an engineer migrated the cluster VMs (the VMs were powered off during the migration) to the VSAN v.6.1 hosts and storage. The migration completed successfully, but the VMs would not power on with the error message “Cannot use non-thick disks with clustering enabled (sharedBus='physical'). The disk for scsi1:0 is of the type thin.”

Because VSAN does not support Microsoft Cluster with the shared disk (non shared disk cluster, e.g. SQL AlwaysOn Availability Group is supported), this is no option but migrating the VMs back to the original hosts and SAN storage.

PS: In this case, the new target storage is VSAN. I think if the new target storage were the traditional SAN,  the cluster would break too. Because the cluster VMs were not shared anymore after the migration (see below). But you probably could recover the cluster by reconfiguring the VMs to share the shared disks without migrating the VMs back to the original storage.

When we reviewed the disks of the migrated VMs on the VSAN storage, each VM had its own copy of the shared disks. So the cluster VMs were not shared the shared disks any more. We could not simply migrate the VMs back to the original hosts and SAN storage.

When we reviewed the original EMC SAN storage, the VMDK files of the shared disks were still left there, only the non shared disk (e.g. the OS’s C drive) was completely migrated to the VSAN storage.

Recovery Procedure:

1. Document the SCSI controller ID (e.g. SCSI (1:0)) of each shared disk from the migrated VMs. This may not be very important. But we are going to use the same SCSI controller for each corresponding disk when re-adding the shared disks
2. Since the VMDK files of the shared disks were still left on the original SAN storage, we can speed up the recovery by migrating the non shared disks of each VMs only. In this case, we are only migrating the hard disk 1 of each VM (the OS drive) back to the original SAN.
3. How to migrate only the OS drive back to the original host and storage? We used VMware vCenter Converter, and only select the hard disk 1. This worked beautifully.
• 
4. PS. In this case the VMs were migrated to the VSAN storage. We could not use scp to copy the VMDK file manually between the hosts. If we want to use scp, we need to migrate the VMDK files to a non-VSAN storage first. This is why I think vCenter Converter is the best tool in this case.
5. Now the non-shared disk of each VM are back to the original host and SAN storage. Make sure both VMs are registered on the same ESXi host.
6. If the VMs were not on the same ESXi host, use Migrate, Change host, check the checkbox “Allow host selection with this cluster” (this option is not selected by default) to put both VMs on the same ESXi host.
   • 
7. Re-add the SCSI controller(s) to the first VM and set the SCSI Bus Sharing to Virtual
8. Re-add the shared disks using the existing VMDK files to the first VM; match the SCSI ID documented in the first step. We also make sure the order of the hard drives matching the original VM’s configuration
•  
9. Power on the first VM
10. Log in Windows and verify the shared drives’ drive assignments are correct
11. Launch Failover Cluster Manager to verify the cluster services and applications are online
12. Re-add the SCSI controller(s) to the second VM and set the SCSI Bus Sharing to Virtual
13. Re-add the shared disks using the existing VMDK files to the second VM; match the SCSI ID documented in the first step
14. Power on the second VM
15. Log in Windows and verify no shared drive is shown in Windows Explorer, and they should be shown “reserved” in the Disk Management
16. Launch Failover Cluster Manager to verify the second node is online

vMotion Microsoft Cluster VMs

vSphere supports three different configurations of Microsoft Cluster Service (MSCS):

• Clustering MSCS VMs on a single host (aka a cluster in a box - CIB)
• Clustering MSCS VMs across physical hosts (aka a cluster across boxes - CAB)
• Clustering physical machines with VM

see Setup for Failover Clustering and Microsoft Cluster Service, ESXi 6.0 for more information.

However, vMotion is supported only for CAB with pass-through RDMs. Do not vMotion MSCS VMs in the other two configuration.

In addition, do not vMotion MSCS VMs to a VSAN storage, because VSAN does not support thick provision and SCSI bus sharing on the VM SCSI adapter. The VM will not be able to power on with the error message “Cannot use non-thick disks with clustering enabled (sharedBus='physical'). The disk for scsi1:0 is of the type thin.”

Fix “could not locate entry in sysdatabases” Error

Problem: I ran into this error when executing a script on a SQL database.  I could attach or deattach the database, and SQL Management Studio showed the database was fine.

Fix: it turns out the database has ‘-‘ in the name, e.g. database_2008-10-01.  Renaming the database by removing ‘-‘ fixed the problem.

Do not use ‘-‘ in SQL database name.

SQL Server Authentication and Authorization

Just read a blog post illustrating the SQL account problem when moving or restoring SQL database between servers.  As the blog says:

“SQL Server security is a little complex, but for our purposes it’s enough to consider two core artifacts: logins and users. Logins are instance-level objects (stored in master) and users are database-level objects (stored in the user database). Each of these are responsible for authorization in their respective domains, i.e., used to grant permissions at the instance and at the database respectively. But only logins are used for authentication. So in order to even log on to the server you have to have a login.

Users and logins are associated with each other through a matching identifier called a SID, and in order for a person to connect to and use a database, he must have a user in the target database and matching login on the instance. And here’s where our problem comes from: while users are stored in and move with the database, logins are not and do not. They’re left behind:

The result of this is that someone who could connect to D when it lived on S may no longer be able to do so when it moves to T because their login is missing. This is reparable, of course: you just need to manually add the required logins to the new instance. It’s also not easily automatable, since the target instance may already have a different login with the same name, which would cause a collision.”

It sounds the Contained Database in the next version of SQL Server “Denali” solved the authentication problem.  For now, we can resync the user login by using

sp_change_users_login 'update_one', 'username', 'username'

SQL Server Database Version List

This web page lists each SQL Server version number (from SQL v.6.5 to 2008 R2) and its patch level.  It is useful to compare your installed version with the latest version available.

This is another web site with the similar information.

How to Manage SQL Server Database Files

• Log files
• Query log space usage for every DB on the server: DBCC SQLPERF(‘logspace’)
• Add a log file to the DB: ALTER DATABASE dbname ADD LOG FILE(NAME = logicalname, FILENAME = ‘filepath’, SIZE = size, MAXSIZE = maxsize, FILEGROWTH = filegrowth)
• Modify auto growth: ALTER DATABASE dbname FILE (NAME = logicalname, AUTOGROW = size)
• Remove MAXSIZE limit: ALTER DATABASE dbname (NAME = logicalname, MAXSIZE = UNLIMITED)
• Prevent autogrow: ALTER DATABASE dbname (NAME = logicalname, AUTOGROW = 0)
• Data files
• the command is essentially the same with some new options (e.g. “TO FILEGROUP”).

How to Plan SQL Server Database Files

• Log files:
  • Place log files on a different physical partition than data files.
  • It’s not true – using several log files will get a performance gain – log files are written sequentially which means that each log file is filled up before the next one is written to.
• Data files:
  • Place data files on a different physical partition from log files.
  • It’s true – using several data files can get a performance gain – data files use an equal fill algorithm (all of the files are filled equally as much as possible).
• File growth:
  • Do not accept the defaults for file growth.
  • The best is to set both data and log files as large as possible – it can be very expensive (performance wise) to grow files
  • The next best is to set files to autogrow by fairly large predicable increments (grow data file by at least 1 GB).  It’s best to grow files by a specific value than by a percentage.
  • Set all data files to grow at the same rate, and all log files to grow at the same rate.  Data files don’t have to grow at the same rate as log files.

More details see http://www.petri.co.il/sql-server-database-files.htm

How to Schedule SQL Server 2005 Express Backup in Windows 7

http://www.mydigitallife.info/2010/07/06/how-to-perform-scheduled-backups-for-sql-server-2005-express-in-windows-7/