This weekend, I was asked to sit on a panel for an Ask the Experts/Stump the Chumps session. We got on the topic of running SQL Server on VMWare. One of the questions asked was if you could tell when your sysadmin team moved your VM between hosts. As far as I know, there is no way of telling from the VM what host you’re on. However, as DBAs, we often have access to the VCenter database. That means we have access to the data.
As a word of caution, your sysadmin team may not want you to know this information. If spelunking through the VCenter is ethically questionable in your organization, I would avoid doing this.
This query will tell you what host your VM lives on. If you run it on different days and see different results for the host, that means your machine has been moved.
SELECT vm.DNS_NAME, host.DNS_NAME FROM dbo.VPX_VM vm JOIN dbo.VPX_HOST host ON vm.host_id = host.id WHERE vm.dns_name = 'mydbserver'
But we can do a little more digging. I went spelunking through my database and came up with this little nugget. It will tell you when your machine is moved as well as other notable events.
SELECT evt.vm_name, evt.host_name, evt.event_type, evt.create_time, evt.username FROM dbo.VPX_EVENT evt WHERE evt.vm_name = 'mydbserver' AND evt.event_type IN ('vim.event.VmBeingHotMigratedEvent', 'vim.event.VmBeingRelocatedEvent', 'vim.event.VmGuestShutdownEvent', 'vim.event.VmMigratedEvent', 'vim.event.VmRelocatedEvent') ORDER BY evt.create_time
I love getting questions like this. I knew it could be done, but it challenged me to figure it out in more details.