SQL Server Days 2014–Belgium


The Belgian SQL Server User Group (SQLUG) has been organizing the SQL Server Days for a while now and they have quickly established themselves as a very good event with both quality speakers and content. SQL Server Days 2014 is the 7th edition of this event

SQL Server Days 2014 Main Banner

Learn form the best, from and with your peers. There will be deep drive training with international speakers & hard core technical sessions on database administration, development and business intelligence.

If you’re in traveling range of Belgium and a SQL Server professional this is an event that you should seriously consider attending! I have done it in the past and I try to get my own DBA’s to go as well. Why. It’s great quality but more than that it’s all about lifelong learning, getting out of the house to prevent tunnel vision and learning what challenges your peers are facing and how they deal with those.

In a time of cut backs, austerity, tax increases, economic doom and gloom there is still a lot to learn, opportunities to seize and a better, prosperous future to build. The community is there to help those that want to help themselves improve professionally and personally without breaking the bank.  So get registered and prepare to learn and network.

More info:

SQL Server Days 2014
September 30 – October 1
Day 1 = precon / Day 2 = conference
Where: San Marco, Schelle

Fixing “Windows cannot find the Microsoft Software License Terms. Make sure the installation sources are valid and restart the installation” Or "Windows installation encountered an unexpected error. Verify the installation sources are accessible, and restart the installation. Error code: 0xE0000100"


When trying to install a Windows 2012 (R2) or Windows 8(8.1) VM you can encounter the following error:

"Windows cannot find the Microsoft Software License Terms.  Make sure the installation sources are valid and restart the installation."

Right after selecting the operating system.image

or perhaps even this error

"Windows installation encountered an unexpected error. Verify the installation sources are accessible, and restart the installation.

Error code: 0xE0000100"

image

The main reason for this on Hyper-V is that you have been to conservative on memory allocation and it could pass some checks. You can hit these errors when you did not assign enough memory to the virtual machine or accepted the default. The default is 512MB and I’ve noticed that on Windows Server  2012 (R2) Hyper-V this can be to little.

image

So the fix is a easy as upping the assigned amount of memory. I went for 1024MBimage

Now start the VM again, hit any key to boot form the virtual DVD to start the setup. After selecting the OS version to install you’re now greeted by the screen to accept the license terms instead of a warning.

image

So click next and install your VM.

What Is AutoRecovery.avhdx all about?


Introduction

As you might have noticed or read about here Dealing With Event ID 10103 “The virtual machine ‘VM001′ cannot be hot backed up since it has no SCSI controllers attached. Please add one or more SCSI controllers to the virtual machine before performing a backup. (Virtual machine ID DCFE14D3-7E08-845F-9CEE-21E0605817DC)” In Windows Server 2012 R2, backups of Windows Server 2012 R2 Hyper-V hosts require the virtual machines to have a vSCSI controller. So any VM where this has been removed (there is one by default) run into backup issues. I have written a bit more about this already in Some Insights Into How Windows 2012 R2 Hyper-V Backups Work. But what is that all about?

The Volume Shadow Copy Service Auto-Recovery phase

Well, this is nothing new actually. If you read up on how work you’ll notice that there is an Auto-recovery phase.

10. If the shadow copy is successfully created, the Volume Shadow Copy Service returns the location information for the shadow copy to the requester. In some cases, the shadow copy can be temporarily made available as a read-write volume so that VSS and one or more applications can alter the contents of the shadow copy before the shadow copy is finished. After VSS and the applications make their alterations, the shadow copy is made read-only. This phase is called Auto-recovery, and it is used to undo any file-system or application transactions on the shadow copy volume that were not completed before the shadow copy was created.

The purpose of this phase is to allow for a read only mount of the volume and file system. Many VSS writes and even NFTS transactions leverage this mechanism. Not all backup products however leverage this phase. This means that those products have to mount the volume/file system in read/write mode and need the file system and application to roll back transaction from their logs to have a application consistent backup. Hyper-V being the good citizen it is, does leverage this auto-recovery phase and hence you now see where that avhdx or avhd comes from.

For example the SQL Server VSS writer leverages this to update components in a shadow copy before the shadow copy is permanently changed to read-only. A prime example is a database that needs to rollback any incomplete transactions for all shadow copies.

Before Windows Server 2012 R2 Hyper-V the two ways to backup a virtual machine were:

  • Saved State method:  The VM goes into saved state mode during the processing of the PrepareForSnapshot event, snapshots are taken of the appropriate volumes, and the VM is returned to the previous state during the processing of the PostSnapshot event. This is used when there are no integration components for the guest operating systems, they are missing, out of date or there are other issues. It’s an attempt to at least get a backup as requested if a better method is not available.
  • Guest Snapshot method: which uses VSS inside the guest VM during the PrepareForSnapshot event.

Please read Backing Up and Restoring Virtual Machines and  Overview of Processing a Backup Under VSS for more details on this. Note that this also already leverages the Auto-Recovery phase!

Now in Windows Server 2012 R2 host level backups of virtual machines no use Hyper-V checkpoints (formerly know as snapshots) instead of the guest snapshot method. This also leverage the Auto-Recovery phase and that’s where autorecovery.avhdx comes in. In Hyper-V this the Auto-Recovery phase process is accomplished In Hyper-V  by hot-adding the special snapshot virtual hard disk (autorecovery.avhdx) to the virtual machine. Again, this is why since Windows Server 2012 R2 Hyper-V the virtual machine requires a vSCSI controller to be present for backups to succeed. The checkpoints are automatically merged as the volume shadow process completes and autorecovery.avhx does not even exist for the entire lifetime of the backup. When browsing backup data or VSS integrated application consistent snapshots you will see them.

image_thumb[3]

Restoring Backups

I hope that you now have a better understanding of what is happening and why. Basically you see Hyper-V doing all it can do to make application consistent backups whenever it can in the best possible and most efficient way.

Auto-Recovery is an optional phase and as not all backup products are created equal some do not leverage this phase. A good backup product that support Windows Server 2012 R2 Hyper-V must be able to handle the fact that Hyper-V backups leverage this during both backup & recovery.

When you perform a complete restore of virtual machine all merging that needs to be done should be taken care when you first boot the restored virtual machine. However if you restore just vhdx files or files in vhdx files you might not have application consistency. This is something you should be aware off.

Thanks for reading!

Manually Merging Hyper-V Checkpoints


A Last ditch Effort

Fist of all you need to realize this might not work. It’s a last ditch effort. There is a reason why you have backups (with tested restores) and why you should monitor your environment for things that are not as they should be. Early intervention can pay off.

Also see blog post on a couple of more preferred actions.

If you have lost checkpoints, you have basically lost data and corruption/data inconsistencies are very much a possibility and reality. If the files have been copied and information about what file is the parent the dates/timestamps are what you have to go by. You might not know for sure if you have them all.

Setting up the demo

For demo purposes we take a test VM and ad files to indicate what checkpoint we’re at.

We start with ORGINAL.TXT on the desktop and we create a checkpoint, which we rename accordingly.

image

We add a file called CHECK01.TXT and we create a checkpoint, which we rename accordingly.

image

We add a file called CHECK02.TXT and we create a checkpoint, which we rename accordingly.

image

We add a file called NOW.TXT no more checkpoints are taken.

image

The file names represent the content you’d see disappear if you applied the checkpoint and we have reflected this in the name for the checkpoints.

image

As we want to merge all the snapshots and and up with a usable VHDX we’ll work back from the most recent differencing disk until all is merged. As you can see this is a straight forward situation and I hope you’ll never be running having to deal with a vast collection of sub trees Smile.

Finding out what are the parents of avhdx files

In this demo it’s pretty obvious what snapshot exist and what avhdx files they represent. We’ve even shown you the single tree visualized in Hyper-V Manager. In reality bad things  have happened and you don’t see this information anymore. So you might have to find out yourself. This is done via inspect disk in Hyper-V manager. I you’re confused about what the parent is of (a)vhdx files this tool will help you find out or show you what the most recent one was.

image

Sometimes the original files have been renamed or moved and that it will show you’re the last known valid parent.

image

Manually Merging the checkpoints

Remember to make a copy of all files as a backup! Also make sure you have enough free diskspace … you need working space! You might need another shot at this. As we want to merge all the snapshots and and up with a usable VHDX we’ll work back from the most recent differencing disk until all is merged in the oldest one which is the vhdx. You can look at the last modified time stamps to find out the correct order in which to work. The most recent avdx is the one used in the virtual machine configuration file and locate the information for the virtual hard disk.

image

The configuration file’s avhdx is the one containing the “NOW” running state of the VM.

Note: You might find some information that you need to rename the extension avhdx to vhdx (or avhd to vhd). The reason for this was that in Windows 2008 Hyper-V Manager did not show avhd files in the Edit virtual disk wizard. You can still do this and it will still works, but you do not need to. Ever since Windows Server 2008 R2 avhd (and with since Windows Server 2012 avhdx) files do show up in Hyper-V Managers Disk edit.

For some insights as to why the order is important read this blog by Ben Armstrong What happens when a snapshot is being merged? [Hyper-V]

WARNING: If you did not start with the most recent one and work your way down, which is the easiest and least confusing way all is not lost. But you will have to reconnect the first more recent (a)vhdx to one to it’s new parent. This is needed as by merging a snapshot out of order more recent one will have lost it’s will have lost it’s original parent.

Here’s how to do this: Select reconnect. This is the page you’ll get if you’d start edit disk wizard as all other option are unavailable due to the missing parent.

image

The wizard will tell you what used to be the parent and allow you to select a new one. Make sure to tick the check box for Ignore ID mismatch or the reconnect will fail as you’re previous out of order merge has created a new a(vhdx). If your in this pickle by renaming (a)vhdx files or after a copy this isn’t needed by the way.

image
Follow the wizard after that and when your done you can launch the edit disk wizard again and perform a merge. It’s paramount that you do not mix up orders when doing so that you reconnect to the parent this or you’ll end up in a right mess. There are many permutations, keep it simple!. Do it in order Smile. If you start having multiple checkpoint trees/subtrees things can get confusing very fast.

You might also have to reconnect if the checkpoints have lost their connection the what they know to be their last parent for other reasons. In that case you do this and when that’s done, you merge. Rinse and repeat. The below walk through assumes you have no reconnects to be done. If so it will tell you like in the example above.

Walk trough:

Open the Edit Disk Wizardimage

Select the most recent avhdx & click “Next”

image

image

We choose to merge the avhdx

image

In our case into its parent disk

image
Verify the options are correct and click “Finish”

image

Let the wizard complete

image

That’s it. You’ve merged the most recent snapshot into it’s parent. That means that you have not lost the most recent state of the virtual machine as when it was running before you shut it down. This can be verified by mounting the now most recent avhdx and looking at the desktop for my user profile. You can see the NOW.txt text file is there!

OK, dismount the avhdx and now it’s rinse and repeat.

image

image

You do this over an over again until your merge the last avhdx into the vhdx.

image

Than you have the vhdx you will use to create a new virtual machine.

image

Make sure you get the generation right.

image

Assign memory

image

Connect to the appropriate virtual switch or not if you’re not ready to do this yet

image

Use your vhdx disk that’s the remaining result of your merging efforts

image

image

When you boot that virtual machine you’ll see that all the text files are there. It’s as if you’ve deleted the checkpoints in the GUI and retained “NOW” in the vhdx.

image

image

Last but not least, you can use PowerShell or even DiskPart for this but I found that most people in this pickle value a GUI. Use what you feel most comfortable with.

Thanks for reading and hope this helps someone. Do remember “big boy” rules apply. This is not safe, easy or obvious in each and every situation so you are responsible for everything you do in your environment. If your in to deep, way over your head, etc. call in some expert help.

3 Ways To Deal With Lingering Hyper-V Checkpoints Formerly Known as Snapshots


Lingering or phantom Hyper-V checkpoints or snapshots

Once in a while the merging of checkpoints, previously known as snapshots, in Hyper-V goes south. An example of this is when checkpoints are not cleaned up and the most recent avhdx or multiple of these remains in use as active virtual disk/still even as you don’t see them anymore as existing in the Hyper-V Manager UI for example. When that happens you can try looking at the situation via PowerShell to see if that show the same situation. Whatever the cause, once in while I come across virtual machines that have one or more avhdx (or avdh) active that aren’t supposed to be there anymore. In that case you have to do some manual housekeeping.

Now please, do not that in Windows Server 2012(R2) Hyper-V replica is using checkpoints and since Windows Server 2012 R2 backups also rely on this. Just because you see a snapshot you didn’t create intentionally, don’t automatically think they’re all phantoms. They might exits temporarily for good reason Winking smile. We’re talking about dealing with real lingering checkpoints.

Housekeeping

Housekeeping comes in a couple of variants form simply dusting of to industrial cleaning. Beware of the fact that the latter should never be a considered a routine operation. It’s not a normal situation. It’s a last ditch resort and perhaps you want to call support to make sure that you didn’t miss anything else.

Basically you have tree options. In order of the easiest & safest to do first these are:

  1. Create a new checkpoint and delete it. Often that process will take care of merging the other (older) lingering avhd/avhdx files as well. This is the easiest way to deal with it and it’s as safe as it gets. Hyper-V cleans up for you, you just had to give it a kick start so to speak.
  2. Shut down the VM and create a new checkpoint. Export that newly created checkpoint. Yes you can do that. This will create a nicely exported virtual machine that only has the relevant vhd/vhdx files and no more checkpoints (avhd/avhdx). Do note that this vhd/vhdx is dynamically expanding one. If that is not to your liking you’ll need to convert it to fixed. But other than that you can dump the old VM (don’t delete everything yet) and replace it by importing the one you just exported. For added security you could first copy the files for save guarding before you attempt this. image
  3. Do manual mergers. This is a more risky process & prone to mistakes. So please do this only on a copy of the files. That way you’ll give Microsoft Support Services a fighting change if things don’t work out or you make a mistake. Also note that in this case you get one or more final VHDX files which you’ll use to create a new virtual machine with to boot from. It’s very hands on.

So that’s the preferred order of things to try/do in regards to safety. The 3rd option, is the last resort. Don’t do it before you’ve tried options 1 and 2. And as said above, if you do need to go for option 3, do it on copies.If you’re unsure on how to proceed with any of this, get an expert involved.

There’s actually another option which is very save but not native to Hyper-V. In the running virtual machine which current state you want to preserve do a V2V using Disk2vhd v2.01. Easy and sort of idiot proof if such a thing exists.

In a next blog post I’ll walk you through the procedure for the 3rd option. So if this is your last resort you can have practiced it before you have to use it in anger. Bit please, if needed, and do make sure it’s really needed as discussed above, try 1 first. If that doesn’t do it. Then try option 2. If that also fails try option 3. Do not that for option 2 and 3 you will have to create a new virtual machine with the resulting VHDX, having the required settings documented will help in this case.

Failed at dumping XP in a timely fashion? Reassert yourself by doing better with Windows Server 2003!


I could write a blog post that repeats the things I said bout XP here for Windows 2003 with even some more drama attached so I won’t. There’s plenty about that on the internet and you can always read these blogs again:

I also refer you to a old tweet of mine that got picked up by some one and he kind of agreed:

image

Replace “XP” with “Server 2003” and voila. Instant insight into the situation. You are blocking yourself from moving ahead and it getting worse by the day. All IT systems & solutions rot over time. They become an ever bigger problem to manage and maintain, costing you time, effort, money and lost opportunities due to blocking to progress. There comes a day that creative solutions won’t pop up anymore like the one in this blog post  Windows XP Clients Cannot Execute Logon Scripts against a Windows Server 2012 R2 Domain Controller – Workaround and more recently this on where people just waited to long to move AD over from Windows Server 2003 to something more recent It turns out that weird things can happen when you mix Windows Server 2003 and Windows Server 2012 R2 domain controllers. All situations where not moving ahead out of fear to break stuff actually broke the stuff.

In the environments I manage I look at the technology stack and plan the technologies that will be upgraded in the coming 12 months in the context of what needs to happen to support & sustain initiatives. This has the advantage that the delta between versions & technologies can never become to big. It avoids risk because it doesn’t let delta grow for 10 years an blocks introducing “solutions” that only supports old technology stacks. It make sure you never fall behind too much, pay off existing technology debt in a timely fashion and opens up opportunities & possibilities. That’s why our AD is running Windows Server 2012 R2 and our ADFS was moved to 3.0 already. It’s not because a lot of things have become commodities you should hand ‘m over to the janitor in break/fix mode. Oh the simplicity by which some wander this earth …

OODA

Observe, Orient, Decide, Act. Right now in 2014 we’ve given management and  every product/application owner their marching orders. Move away from any Windows 2008 / R2 server that is still in production. Why? They demand a modern capable infrastructure that can deliver what’s needed to grasp opportunities that exits with current technology. In return they cannot allow apps to block this. It’s as easy and simple as that. And we’ll stick to the 80/20 rule to call it successful and up the effort next year for the remainder. Whether it’s an informal group of dedicated IT staff or a full blown ITIL process that delivers that  doesn’t matter. It’s about the result and if I still see Windows 7 or Windows 2008 R2 being rolled out as a standard I look deeper and often find a slew of Windows 2003 or even Windows 2000 servers, hopefully virtualized by now. But what does this mean? That you’re in a very reactive modus & in a bad place. Courage & plans are what’s needed. Combine this with skills to deal with the fact that no plan ever woks out perfectly. Or as Mike Tyson said “Everybody has a plan until they get punched in the mouth. … Then, like a rat, they stop in fear and freeze.”

Organizations that still run XP and Windows Server 2003 are paralyzed by fear & have frozen even before they got hit. Hiding behind whatever process or methodology they can (or the abuse of it) to avoid failure by doing the absolute minimum for the least possible cost. Somehow they define that as success and it became a mission statement. If you messed up with XP, there’s very little time left to redeem yourself and avoid the same shameful situation with Windows Server 2003. What are you waiting for? Observe, Orient, Decide, Act.

Configuring timestamps in logs on DELL Force10 switches


When you get your Force10 switches up and running and are about to configure them you might notice that, when looking at the logs, the default timestamp is the time passed since the switch booted. During configuration looking at the logs can very handy in seeing what’s going on as a result of your changes. When you’re purposely testing it’s not too hard to see what events you need to look at. When you’re working on stuff or trouble shooting after the fact things get tedious to match up. So one thing I like to do is set the time stamp to reflect the date and time.

This is done by setting timestamps for the logs to datetime in configuration mode. By default it uses uptime. This logs the events in time passed since the switch started in weeks, days and hours.

service timestamps [log | debug] [datetime [localtime] [msec] [show-timezone] | uptime]

I use: service timestamps log datetime localtime msec show-timezone

F10>en
Password:
F10#conf
F10(conf)#service timestamps log datetime localtime msec show-timezone
F10(conf)#exit

Don’t worry if you see $ sign appear left or right of your line like this:

F10(conf)##$ timestamps log datetime localtime msec show-timezone

it’s just that the line is to long and your prompt is scrolling Winking smile.

This gives me the detailed information I want to see. Opting to display the time zone and helps me correlate the events to other events and times on different equipment that might not have the time zone set (you don’t always control this and perhaps it can’t be configured on some devices).

image

As you can see the logging is now very detailed (purple). The logs on this switch were last cleared before I added these timestamps instead op the uptime to the logs. This is evident form the entry for last logging  buffer cleared: 3w6d12h (green).

Voila, that’s how we get to see the times in your logs which is a bit handier if you need to correlate them to other events.