Setting Up a Virtual Server Lab With Hyper-V

For years I had used VMware as my hypervisor of choice for virtual labs but I recently switched to Hyper-V. The last few weeks I have been getting familiar with how things work in Microsoft's virtualization environment and decided to write a quick step by step on how to get a virtual lab up and running. Before I get started with that I would like to share some of the reasons why I decided to switch.

• Hyper-V is now included with Windows 8 Pro and Enterprise editions (No need to spend extra money for cutting edge virtualization technology on my laptop or workstation)
• Hyper-V has matured now to a point where it can now make a great case for enterprises to adopt this technology over any other virtualization platform so I would expect to see more enterprises running their private clouds on Hyper-V. See Hyper-V competitive advantage datasheet
• I am a Microsoft Certified Professional / Trainer and I work for a Microsoft Gold Partner. There is a great deal of support offered to IT professionals focused on Microsoft technologies and many of the demos / labs are available in the form of Hyper-V virtual machines. It would not be worth it to take the extra time to recreate such resources from scratch using a different virtualization technology.
• I see I great deal of opportunities derived from the Azure IaaS offering and it's ability to run Hyper-V machines. I can create a Virtual Server and run it on my laptop, the Data Center or upload it to the cloud. 
• I can browse a VHD as if it were just another disk. 
• I can boot my windows computer from a VHD

Now, back to the original subject. Let's get started.

Enabling Hyper-V

First things first, right? if you have not already enabled Hyper-V in your windows 8 Pro or Enterprise OS you can do so by executing the following PowerShell command:

enable-WindowsOptionalFeature -Online -FeatureName Microsoft-Hyper-V -All

Recap of SQL Saturday #151 Orlando - September 29, 2012

This last Saturday September 29, I had the opportunity to speak at SQL Saturday in Orlando. It was such a great experience from many different levels. I guess I will start with the speaker’s dinner. I arrived on Friday afternoon and after checking in at the hotel, I worked on the final details of my presentation. I then headed down to Liam Fitzpatrick's Irish Restaurant on Market Promenade Avenue where the speaker’s dinner was taking place.

SSIS 2012 Project Versions

Did you know that when you deploy projects to the SSIS catalog in SSIS 2012, the previous version of the project is kept in the server?
All you have to do to access the version history is right click in the name of the project and click on “Versions”. Assuming of course that you are using the new project deployment mode. This feature is not available if you use the package deployment mode.

How does the SQL Server engine guarantees that Change Data Capture has extracted the necessary transactions from the transaction log file before log truncation occurs.

As you know Change Data Capture harvests transactions from the log file. I was wondering what happens when the log file gets truncated during transaction log backups or when an automatic checkpoint takes place on a database in simple recovery mode causing log truncation. What if the CDC job has not harvested those records in the portion of the log to be truncated. I had the feeling that the SQL Engine ensured that this would not happen but I wanted to understand the internals of this process. turns out, with all the information available about CDC it was hard to find a straight answer on this. But I did. 

My SQL saturday #62 schedule

My SQL Saturday #62 schedule:

This are the sessions I think I will be attending:

8:30      Efficient Data Wharehouse Design
9:45      SSIS Cafeteria
11:00    Cool tricks to pull from your SSIS hat
12:15    Accelerating BI Development with BI xPress
1:30      MDX 201
2:45      SQL Smackdown: SSIS Vs PowerShell
4:00      Creating a Metadata Mart w/ SSIS - Data Governance

It will be very SSIS oriented as you can see.

Hope to get a chance to catch up with many of you during this SQL Event

Luis

Package execution waiting due to SPID -2 blocking insert

I was troubleshooting a foreign key violation error in a  package that populates a fact table and ran into an interesting situation. When running the package from Visual Studio it ran up to the point of the final insert in the fact table at which point it hung.

Off course I proceeded to troubleshoot this and found that the final insert was been blocked by  SPID -2. After running all sort of queries using different management views I had no info as to what was blocking the insert. I turned to google and finally I read an entry in a forum that described this SPID -2 as an orphan transaction and pointed out that the fix was restarting MSDTC. Before doing that I wanted to at least confirm this so I started querying again using the transaction related management views but could not find this orphan transaction. At that point decided to restart MSDTC and ran the package again.. surprise! The package ran all the way. I had tried several times before doing this with the same results (process blocked by SPID -2)

After further research I found that this SPID is visible by querying  sys.syslockinfo (I guess I missed this one on my initial query attempts) from there you can obtain the req_transaction_uow then you can kill this unit of work by using the kill command followed by the req_transaction_uow. No need to restart MSDTC

example:

 select req_spid, req_transactionuow from sys.syslockinfo

-2 D5499C66-E398-45CA-BF7E-DC9C194B48CF

57 00000000-0000-0000-0000-000000000000

51 00000000-0000-0000-0000-000000000000

58 00000000-0000-0000-0000-000000000000

59 00000000-0000-0000-0000-000000000000

kill 'D5499C66-E398-45CA-BF7E-DC9C194B48CF'

Super fast Data Warehouse dimensions loading with SSIS and Change Tracking

The Challenge: Updating Type 1 dimensions as fast as possible. Source tables can be quite large and retrieving only changes and new data will reduce the time it takes to update your dimensions dramatically.