Monitoring native Windows deduplication job progress with Power Shell

I am a big fan of Windows data deduplication since I first saw it in Windows Server 2012 RC. You can never have enough of the disk space, right? 馃檪 It has its downsides (mostly maintenance-related – I’ll describe it later) but the benefit is amazing – official Microsoft blog claims they were able to squeeze 6TB of data in 1.6 TB. Another cool thing about data deduplication is that you can even have it running on Windows 8 (see here for details) by importing a package and then enabling a new Windows feature.

The deduplication itself is controlled through a number of Power Shell cmdlets, but the thing is that you can’t really see the progress of it. To help with it, I came up with an idea of running Get-DedupJob in a loop and showing the output on a progress bar. Here’s the script:

for ($j = 0 ; $j -le 10000; $j++) 
  Get-DedupJob | %{
      -Id $_.ProcessId 
      -Activity ($_.Volume + " - " + $_.Type) 
      -Status ($_.State.ToString() +  " " + $_.Progress.ToString() + "% percent complete") 
      -PercentComplete $_.Progress}; 

    Start-Sleep 2 | out-null;

You might be also tempted to check out what the disk savings are – you can use similar loop concept to do it:

for ($j = 0 ; $j -le 10000; $j++) 
  Start-Sleep 2 | out-null;

I am using deduplication to control the size of my VM library at home lab and it works like charm – allows me to keep 400+GB of VHDs in less than 150 GB. However you need to remember about certain things (read the docs for details):

  • There are three kind of jobs – optimization, scrubbing and garbage collection. You have to ensure that all of them are running – for example a simple file delete command does not instantly reclaim unused space. This is especially important in a lab that’s not running 24×7. You have to consider adjust deduplication schedule to suit your needs
  • Enabling background optimization might have an impact on longevity of SSD drives due to additional I/O operations.
  • You need to have Windows 8/Server 2012 or newer to suport deduplication-enabled volumes. Windows 7 or older OSes cannot access it.
  • Last but not least, you can’t enable deduplication on a system drive.

Building a clustered test lab with Windows Server 2012 RC and SQL Server 2012 RTM Evaluation

I have already described what my test lab at home looks like, however I find it interesting enough to provide details how to set it up. This post will be first in series and will cover planning and preparing the environment. Next posts will be about:

  • installing Windows Server 2012 in a VHD and聽enabling Hyper-V role
  • preparing virtual machine template and setting up virtual machines
  • preparing environment for setting up failover cluster聽
  • installing SQL Server on 2-node cluster

I am using concepts shown in a blog post by Jose Barreto describing this setup with Windows Server 2008 R2 and SQL Server 2008 R2.聽We will use Windows Server 2012 and SQL Server 2012 but main idea is the same.聽To build this lab you require:

  • a PC which:
    • has a CPU that supports virtualization in hardware (for Intel you want a CPU supporting VT-x technology, for AMD – AMD-V) – most聽non-low-end聽CPUs do
    • has minimum of 4 GB RAM (8 GB or more recommended)
    • has 50 GB free HDD space (up to you)
  • some software – all available for free:
    • Windows Server 2012 RC聽or Hyper-V Server 2012 RC聽installation media (I will use “full” Windows Server as I didn’t do it using command-line/PowerShell in Hyper-V Server) – I don’t recommend using VHD provided by Microsoft since it expands from 8 to 40 GB during boot time and you’d need lots more disk space ;
    • SQL Server 2012 Evaluation聽– valid from 180 days, but it’s more than enough for start (you might try Developer Edition if you have one – you don’t have to worry about expiration then)
  • whole procedure takes about 8 hours (depends on your system speed, mainly I/O, so if you’re blessed with RAID 0 on SSD drives you might actually do it much faster) – be warned!

What we’re going to do is we will install fresh Windows Server 2012 operating system in a VHD (so as not to mess with existing setup – you may install on a physical disk if you don’t mind, performance-wise it’s negligible) – I assume that a main OS on the PC we’re using is Windows 7/Server 2008 R2聽or newer, because XP does not support native-boot from VHD (not sure about Vista/Server 2008, but does anyone use it anymore? :P). Within this newly installed Server 2012 we will enable Hyper-V role and set up 4 virtual machines using one base template and four differencing disks. Then we will configure those machines like this:

  1. First machine is a domain controller and聽a DNS server – clustering requires聽a domain and Active Directory requires DNS.
  2. Second machine is storage controller – in the post by Jose (mentioned above) separate download of iSCSI Target is required for Windows Server 2008 R2, however it’s incorporated in Server 2012 – we don’t have to install anything, only enable additional OS features and configure them.
  3. Third machine is an active node of SQL Server failover cluster.
  4. Fourth machine is a passive node of SQL Server failover cluster.
  5. You may add more nodes to the cluster or consider setting up AlwaysOn.

This makes our lab completely standalone, so it can operate even if there is no connection to the outside. If it’s not required to have it this way, for example – you have a domain controller – you may skip creation of first VM. If you have a NAS/SAN, you may use it and skip the second machine. You may also consider combining first two machines into one (if you’re low on resources and don’t want to spawn a horde of machines). AD services in case of a domain so huge as this one will not be very demanding, definitely we’re not going to have a workload on storage impact a domian controller significantly. On the other hand you may also try setting up additional machines to see how it works – I made some with System Center 2012 RC, but you can also build a load-balanced SharePoint farm with a clustered database engine.

When it comes to network – no additional configuration will be done as clustering in Windows Server 2008 removed the need for dedicated heartbeat, so as long as our VMs see each other it will be fine (you may try to include DHCP server in this setup but remember to keep it聽contained so that it does not try to provide addresses in every network it may find). Just remember – live system may not have exactly the same architecture and whatever works for our homemade cluster may not be enough when it’s deployed into production (but hey, that’s a lab we’re building!).

When it comes to resource assignment for VMs I use the following pattern (I have Phenom II X4 CPU and 16 GB in my rig):

  1. For domain controller – 2 CPUs and 768 – 1024 MB RAM.
  2. For storage server – 2 CPUs and 768 MB RAM.
  3. For SQL Servers – 4 CPUs and 2048 – 3072 MB RAM.
  4. Rest – whatever I feel like/need.

When I’m setting this on a laptop where I have memory limitations (4 GB RAM) I cut down first two VMs to 512 MB RAM and SQL Servers to 1024 – 1280 MB. This allows me to remain operable, however sacrificing a lot of performance. Therefore 8 GB RAM is recommended.

And this concludes our preparation. Next post will get us through host setup – we will install Windows Server 2012 in a VHD and make it bootable so stay sharp!

My home lab and virtualization challenges

This time I’m going to write about something less SQL Server related. It’s about my home lab and I’m planning to do with it in the next couple of weeks.
First few words about the hardware. Since I’m always tight on budget, I’m on AMD team since 10 years and as they have been doing some marvellous job in Athlon XP and Athlon 64 era, last five years are completely dominated by Intel. The only field where AMD remained competitive is price – past tense is not by accident, because I consider last year’s release of AMD FX CPUs (also known as Bulldozer) as marketing move without necessary (or promised) technical advantage. I use 3-year-old Phenom II X2 550 with 4 cores unlocked and I’m extremely satisfied with performance of this little 100$ worth CPU. However, if I were to buy a new computer at the moment, I would definitely go for Intel’s i5-2500 or i7-3770 – their performance is simply mind-blowing, not to mention their overclockability (they easily reach 4,5 GHz). Looking at the performance charts (for example at you will see that today’s desktops have higher performance ratings than three year old dual socket servers.
The Phenom II uses AMD AM3 socket which backwards compatible with AM2+ motherboards, therefore the CPU has two integrated memory controllers – one for DDR2, second for DDR3. I tested both and found that two sticks of DDR2 and DDR3 are no problem for it, however it has some issues with 4 DDR3 sticks – requires dropping down memory frequency and loosening tmimings to work correctly. I use 16 GB (4×4) of DDR3 RAM and I’m quite happy about it – it really allows to have fun with virtualization.
Speaking of virtualization, I use virtual machines a lot thanks to wonderful guide by Jose Barreto – before reading it I had three separate machines creeping in size and hardly manageable. After reading and utilizing techniques inside I became resource-hungry since my VMs started to consume all RAM I had, so I expanded in one year from 4 to 16 GB. It also helped me get some basic acquaintance with Active Directory, clustering, DFS and System Center. I recommend setting your own environment like this one to every Windows administrator – fantastic educational purposes. It really does not cost you a lot – you might consider using your laptop for it and if you don’t like to mess with another OS on it – install Windows Server 2008 R2 on USB drive or even flash stick (I used PWBoot together with Windows image – confirmed to work with Windows 7, Windows Server 2008 R2 and Hyper-V Server 8 Beta).
In short how this environment looks like – you need Windows Server 2008 R2 SP1 Enterprise or Datacenter Edition – you may even use Evaluation which is available for free from Microsoft. SP1 is highly recommended – it extends significantly Hyper-V role features giving it for example Dynamic Memory option. You install in on a physical machine, configure to get it working, apply necessary patches and enable Hyper-V role. Inside Hyper-V, you require one base virtual disk for each operating system you are going to use and a number of differencing disks relating to base drives. Base virtual is obtained by using following procedure:

  1. Create a new machine with an empty dynamically expanding VHD
  2. Adjust VM parameters as needed (CPU, RAM), mount OS image and install it
  3. Apply latest Service Pack and security updates
  4. Perform sysprep with generalize option and shutdown the machine

After those steps the base VHD is ready and you can start spawning VMs by creating differencing VHDs linking to this one. Then you create virtual machines, assign an empty differencing VHD to each VM, configure parameters (CPU, RAM, network) and start it. This way you can have 5 new VMs operable in about an hour – it saves a lot of time if you install the OS only once. By the way, sysprep is supported for each system since Windows NT 4.0, so if you want to do a lot of experiments you can build your base virtual disks library by expense of less than 100 GB and then just create ready-to-use VMs with a few mouse clicks.
My home lab contains following VMs at the moment:

  1. Domain controller
  2. Storage server – using iSCSI Target
  3. 2 clustered file servers
  4. 3 clustered SQL Servers (using 2008 R2 and 2012 RTM – it’s a multi-instance cluster)
  5. System Center Virtual Machine Manager 2012 RC
  6. System Center Operations Manager 2012 RC

I rarely have all of them running – no need for that, but this setup fits just fine in 16 GB – if I wanted to add more VMs to this, I would definitely require more RAM or build another Hyper-V host and play with Quick/Live Migration.
Since there are two factors limiting me at the moment – RAM and disk space – I’m thinking of doing something spectacular with this but don’t have too many options. At first, I considered building a massive NT 4.0 environment, but since NT 4 allows only 2 servers to be grouped in a cluster, it’s not that interesting (on the other hand, spawning 50 NT 4s in an hour seems feasible with help from SCVMM, but it’s of little use, apart from pure fun). I might also consider using Windows 2000 but Advanced Server requires 128 MB RAM, the same as Windows Server 2003 Enterprise Edition. So it needs to be decided – maybe 8-node failover cluster of SQL Server 2005 on Windows Server 2003 is not that far away.
Definitely I need a facelift for this lab – upgrade from Windows Server 2008 R2 to 2012 once RC comes out end of June. This will also allow me to rebuild whole environment since it’s got a bit messy during last year. I might scrap SCOM in favor of Sharepoint 2010 and have some fun with new BI features of SQL Server 2012. I’m also considering playing with big data by distributing the load between a number a virtual computers.
Well, that’s it for now, so if you have some comments on a home lab design or want to ask something, please comment, I’m awaiting responses.

Trial installation of Windows Server 8 and SQL Server 2012 – failed

As I stated yesterday evening, trial installation of Windows Server 8 followed by SQL Server 2012 was about to happen.
And it did. Without much success, though.
Windows Server 8 installation – no problem, 30 minutes and you’re done (I am using standard Toshiba Satellite L650-1NT laptop – Core i5 560, 4GB RAM, standard 250 GB HDD).
Sysprep and VM start after sysprepping – no problem as well, you’re set after 10 minutes.
SQL Server 2012 RTM – installation looks no different than SQL Server 2008/R2 or Denali. But… error occured during actual installation – no .NET Framework 3.5. How come? The previous installer used to detect missing .NET 3.5 and enable it. The setup continued till the end, but neither DB engine nor management tools have been installed.

So it’s a point to be validated – either new installer doesn’t check it and you have to enable .NET 3.5 prior to installation(which you should, BTW) or it’s a case of Windows Server 8 that does not get it’s features recognized (that would be a shame). More tests to come.

Windows Server 8 Beta available for download

Microsoft released public beta of Windows Server 8 just yesterday. It is available for download here in two flavours (DVD ISO image and ready-to-go VHD). I downloaded both, but for obvious reasons I chose VHD for start. It allows you to have new server running in less than 10 minutes thanks to native VHD boot features in Windows 7. Keep in mind that a disk you download is an expandable one with maximum of 40 GB and it is expanded on boot to it’s maximum – if you don’t have enough free space, you will encounter the revamped BSOD.
The features look pretty amazing. Storage engine has completely been redesigned with SMB 2.2, transparent failover, deduplication, new file system and lots of other interesting features (check for example this article). Not to mention Hyper-V 3.0 with extended resource support (256 CPUs and 2 TB of RAM per machine – not that easy to spare at the moment, but don’t forget Moore’s Law!), enhanced clustering capabilities (up to 4000 machines per cluster, no shared storage required) and redefined Live Migration. And BTW, the interface has been modified – since it’s Windows 8, it’s Metro, with the Server Manager being the core of server operations, now allowing for multi-server administration (looks pretty cool, but couldn’t get it to work yet).
All I am waiting for now are few hours of spare time to play with it a little bit more, perhaps install SQL Server 2012 RC0 (already done by Aaron Bertrand – read the full story here) and try some clustering to take advantage of simplified storage provisioning.

Migrating virtual machines from Virtual Server 2005 R2 to Hyper-V

I was tasked with migration of two virtual machines from Virtual Server 2005 R2 to Hyper-V in order to prepare them for upgrade from SQL Server 2005 SP3 to SQL Server 2008 R2 SP1. Unfortunately, not everything went as smoothly as initally planned, mostly due to incosistencies in the environment.

First of all, the upgrade is quite straightforward:

  1. Shutdown the VM in Virtual Server.
  2. Copy the VHDs to Hyper-V server.
  3. Create new VM in Hyper-V and assign resources – CPUs, RAM and network. Do not create VHDs in the wizard.
  4. Attach previously copied VHDs.
  5. Start the virtual machine.
  6. Install Hyper-V integration services (requires several restarts).

Now there are few things you need to remember:

  • You need to have Virtual Server 2005 R2 SP1 Virtual Machine Additions (VMA) installed in the VM to allow upgrade to Hyper-V Integration Services. The lowest version possible is 13.813 (previous version summary available here).
  • If you have earlier VS 2005 R2 VMA prior to SP1, either uninstall it or upgrade it to SP1. Now, upgrade of VMA聽= uninstall聽old version, install new one. Remember to have correct ISO at hand – you can’t uninstall VMA version 13.552 (2005 R2) using version 13.813 (2005 R2 SP1).
  • Upgrade of VMA does not take place automatically – you have to do it yourself, otherwise you may have a VM with 2005 R2 additions in VS 2005 R2 SP1 which impact VM performance.

There are many benefits in upgrading VMs from VS 2005 to Hyper-V:

  • ability to assign multiple CPUs to a聽machine
  • support for large (up to 127 GB) VHDs
  • better integration with host operating system (especially when running the same system in host and guest)
  • in Windows Server 2008 R2 SP1, dynamic memory allocation based on guest demand

In my case I struggled to uninstall VMA because I had no source prepared, but finally managed to do it by ripping it off VS 2005 R2 installation. The refreshed machines are happy working with additional resources avialable.

Logging off disconnected users

And now for something completely different鈥 As it is essential for a DBA to know his/her way around in server administration, let鈥檚 think of something else for a minute. If your IT department consists of more than 2 people, you most likely have experienced such situation – you try to log on via RDP but there is message alike:

What happens is that standard Windows Server license allows only for 2 concurrent RDP connections to the server. If more are required, you have to install additional server role – namely Terminal Server (in 2003/2003 R2) or Remote Desktop Services (in 2008/2008 R2), but this has its consequences (primarily related to licensing).

Sometimes it’s just about sheer laziness of administrators. Instead of logging off, they just disconnect their RDP sessions still occupying a precious slot. If this is a problem, you can use command line tools available in Windows Server 2003 Admin Pack and RSAT. I’m referring here to qwinsta and rwinsta (known from Vista also as ‘query session’ and ‘reset session’):

D:\>qwinsta /?
Display information about Remote Desktop Sessions.

QUERY SESSION [sessionname | username | sessionid] 
              [/SERVER:servername] [/MODE] [/FLOW] [/CONNECT] [/COUNTER] [/VM]

sessionname         Identifies the session named sessionname. 
  username            Identifies the session with user username. 
  sessionid           Identifies the session with ID sessionid. p 
  /SERVER:servername  The server to be queried (default is current). 
  /MODE               Display current line settings. 
  /FLOW               Display current flow control settings. 
  /CONNECT            Display current connect settings. 
  /COUNTER            Display current Remote Desktop Services counters information. 
  /VM                 Display information about sessions within virtual machines
D:\>rwinsta /? 
Reset the session subsytem hardware and software to known initial values.

RESET SESSION {sessionname | sessionid} [/SERVER:servername] [/V]

sessionname         Identifies the session with name sessionname. 
  sessionid           Identifies the session with ID sessionid. 
  /SERVER:servername  The server containing the session (default is current). 
  /V                  Display additional information.

You can see my point here – you can find all sessions using qwinsta /server:servername and then for all disconnected users use rwinsta to force their log off. You can go a step further – prepare a list of servers (in a text file or somewhere in Active Directory) and run it against all of them. You can even schedule it if disconnected users are nuisance. Remember – you require administrative access to a server you’re querying.

@for /f "tokens=2" %i in ('qwinsta /server:server_name^| find /i "disc"') do @echo y | rwinsta %i /server:server_name

What happens here 鈥 you get the list of sessions from server and search for string 鈥渄isc鈥. All lines satisfying this condition are tokenized and second token (being a session ID) is extracted. Then for each of those sessions forced log off is performed using session ID (by default, rwinsta asks for confirmation, therefore an echo).

Now if you have a list of servers prepared, just loop through it replacing server_name with a variable, or run your favourite LDAP query to get a list of servers from Active Directory. This is the final of this story, so if you have comments about it, feel free to share it.