Converting the Microsoft Integrated SQL 2012 Demo from Hyper-V to VMWare Workstation

April 14, 2012

Today I decided to get to a long overdue task – looking at Microsoft latest all up BI demo VM containing SharePoint 2010 and SQL 2012 RC0 which includes Power View. Now we know SQL 2012 went GA last week, but this demo was released a couple of months ago so has RC0 which was close to the RTM version anyway. On mssalesdemos.com you will find the download, its called “New SQL Server 2012-RC0 fully integrated Demo”.

Before you download from there beware that the download is split into many rar files and if you prefer a single download I suggest you go here instead. Be warned, the file is about 28.4Gb, so you will need a stable internet connection and are advised to use software that can resume downloads just in case the connection breaks. This will extract to about 65Gb, so have enough disk space on hand. You will need even more to get to the final result we are looking for.

Now, one problem you may face is that the demo is a Hyper-V image, which means unless you have a Windows Server 2008 environment available, you cant run it. I use VMWare Workstation for all my VM work for the demos and presentations I do. So I needed a way to convert this. Warning: converting it will cause Windows and Office on the image to be non-activated. I used my own TechNet keys to re-activate after conversion.

There are many resources online telling you how to perform the conversion. However I found I had to hunt around and collate information from a few sources to get it right. There are also a few traps that are not so fun. So, my aim was to save others trouble and have it all in one place. Warning: Back up your extracted VM first before following any steps below!

There are 2 ways I found to convert the image over to VMWare, kindly blogged by Jim Holmes (Twitter: @aJimHolmes)

  1. Create a Windows Virtual PC and then convert using VMWare VCenter Converter
  2. Convert the .vhd disk to a VMware compatible .vmdk and then create a native VMWare image attaching that disk instead

Option 2 needs the Professional version of WinImage, so I opted to go with Option 1 instead, which I admit is longer, but a good learning experience and will save you having to buy WinImage or similar. If anyone knows of a free tool to do this a comment would be great!

Step 1 – Try to create a Virtual PC

So, following option 1, I tried to create the VPC using the downloaded VHD but I got an error.

Some research led me to discover that this is because the VHD is too big – the limit for Virtual PC appears to be 127Gb, but the disk size in the demo is 250Gb. I found this out by simply mounting in in Windows 7 use the Disk Management are of the Computer Management console

:

And here it is:

I’d like to clear up potential confusion here – the “SQL2012-RC0-ImageX.vhd” file is 75Gb, but it’s a Dynamic Disk so this represents its physical size whereas the 250Gb is its logical size. Dynamic Disks are useful as they don’t pre-allocate space beforehand. On the other hand performance is affected a little.

So I needed a way to shrink this disk.

 

Step 2 – Shrink the VHD so that it is not too big for Windows Virtual PC

This can be done easily using VHD Resizer. However the problem is you cant shrink this disk because the partitions within it are occupying the whole disk. So first we need to shrink the partition represented by drive letter J in the above diagram. Any partition management tool will do the job, but you are need to defragment it first so you can get to the smallest possible size. This is because you cant make partition smaller than the furthest bit of data stored on it. I used O&O Defrag to do both at once, and chose to shrink the partition to 90Gb to leave some free space (15Gb). Heres the result:

 

Once this was done I was able to use VHD Resizer to shrink the disk. I detached it form the Computer Management console first. Note in the image below I picked a separate drive as the destination copy on purpose. This will be significantly faster than creating the new vhd on the same physical drive on your PC. On my system I achieved about 90Mb/s which still meant waiting 15 minutes.

 

Step 3 – Create VPC, then convert to a VMWare workstation image

Now that the vhd is resized we can create the Virtual PC and it wont give us the error we got in Step 1. Windows virtual PC will create 2 files for the new VM. Firstly, a .vmcx file which sits by default in C:\Users\<your_windows_user_name>\Virtual Machines. However, when you specify the location of the VM during creation it actually also creates a .vmc file in that location and this is the file that we will work with.

We are going to use VMWare Vcenter Converter. Once installed follow this post to convert the Virtual PC image to a VMWare image. There is one problem though, you will get this error:

 

The comment at the end of the blog post guided me as to what was wrong, and it took me a while to find that post in the first place. There are a lot of irrelevant links on the Web for that Vcenter Converter error that can lead you on a wild goose chase. The fix for this is to open the .vmc file in a text editor (I use NotePad++) and change the <relative> node details to be a full physical path to the vhd.

Before:

 

After:


Now VCenter converter will have no problems reading the file and will happily convert to your chosen VMWare format, including converting the disk to a .vmdk.

I converted it to a VMWare workstation 7 image and it worked fine! That’s it!

Advertisements

Customizing Filter Persistence in PPS2007

November 14, 2010

A couple of days ago i had to get into the PPS 2007 backend stored procs in order to customize the filter behaviour. As we know, PPS remembers the value of the filter selection across user sessions. This can be a good thing or a bad thing depending on your setup and requirements.

In my case I had a number a number dashboards built with a filter called “Site”. This filter was populated from an SSAS 2005 cube and simply populated a single-select list with a list of facilities that my client had in their business. So a user would pick a facility from the list and the dashboard would then filter the information based on the selection.

We had leveraged security in SSAS to lock down access to facilities via roles (as is best practice.) This worked great, since users would only be able to see facilities in the dropdown if they had been granted access via an SSAS role.

Most users in the business had access to only one facility, so the filter list would contain just a single item. This way regardless of which dashboard they visited, the “Site” filter would have the same selection.

So far so good.. but here things start causing problems. Some users needed access to more than one facility, so again the roles in SSAS reflected this. The issue was that these select users were now getting “lost” because they would select “Facility A” on one dashboard, then drill down into a detailed dashboard expecting it to remember the selection from another dashboard. Some things to note here:

  • The “drilldown” was a hack to get to a completely different dashboard. Sadly, PPS does not support URL links (while the older BSM did!). So I had used this commonly used trick originally highlighted by Nick Barclay
  • PPS does not natively support passing ANY values between dashboard pages

So I started researching how to turn off the filter persistence. Annoyingly this is not a simple on/off setting somewhere.. not even in a text config file! What is required is to get into your PPSMonitoring database and comment out some code in the stored proc [dbo].[ParameterValuesCreate]. So this only gets me halfway there.. now any filter will always default to the first item in the list.

So i knew i needed to customise this stored proc and turned out i needed to learn some XPath in the process. My requirements summarised:

  • PPS must remember filter values per user ONLY for filters called Site.
  • The selection must persist across any dashboard. So a user looking at “Facility B” will always see this selected no matter which dashboard they change to.

The final solution was:

  • Look at [dbo].[FCObjects], parse the XML column [SerializedXML] to extract the GUID for filters called “Site”. (This means the facility filters must be named “Site” all the time for this to work everyehere in the solution).
  • Within [SerializedXML] i get a number of ParameterUIElement nodes for each dashboard. Each of these contains a single filter defitinion, as a descendant node of a dashboard element.
  • I then had to loop through these to extract just a single node pertaining to the site filter. This is done for each dashboard, so effectively i end up with a list of all GUIDs for any filter called site across the whole solution.
  • Finally i check if the filter being passed to the stored proc is actually a Site filter by seeing if it exists in the list created above. If it is, I update the rows in the table [dbo].[ParameterValues] which correspond to the GUIDs i got earlier (i.e. “Site” filter values)

So here is the code below.. noting that this was a first hack and there are improvements I can think of already such as getting rid the of cursor and possible replacing with a CTE.. also creating a table of the GUIDs found so that we dont have to parse [dbo].[FCObjects] every single time a dashboard is viewed or filter is changed. This version may get slow if there are many items in the PPSMonitoring DB.


USE [PPSMonitoring]
GO
/****** Object: StoredProcedure [dbo].[ParameterValuesCreate] Script Date: 11/12/2010 11:03:15 ******/

SET ansi_nulls ON
GO
SET quoted_identifier ON
GO

ALTER PROCEDURE [dbo].[Parametervaluescreate] @Login [NVARCHAR](1800), @ParameterUniqueName [NVARCHAR](100),@SerializedXml [XML]
AS
BEGIN
SET nocount ON
DECLARE @tErrMsg NVARCHAR(50)
SET @tErrMsg = ‘ParameterValuesCreate Failed For Object – ‘
DECLARE @TransactionIsOurs INT
DECLARE @ReturnCode INT

— init
SET @TransactionIsOurs = 0
IF @@TRANCOUNT = 0
BEGIN — only if @@TRANCOUNT is 0, we do BEGIN TRAN
BEGIN TRANSACTION
SET @TransactionIsOurs = 1
END

— BEGIN MOD BY BHAVIK – customise filter persistence behaviour
/* —————————— */
— check if this was a “Site” parameter, if so update all other site parameters for this user
— this way site will be consistent across any dashboard they visit
— ** Note – filter MUST be called Site in any dashboards for this code to work across all **

— vars
DECLARE @tempxml XML
DECLARE @NodeList XML
DECLARE @search VARCHAR(5)

SET @search = ‘Site’
DECLARE @FilterGUIDS TABLE (id UNIQUEIDENTIFIER)

— retrieve the guids for all filters in dashboard elements with name “Site”
— this brings back ALL ParameterUIElement items for the dashboard
DECLARE nodeloop CURSOR FAST_FORWARD FOR
SELECT serializedxml.QUERY(‘/Dashboard/ParameterElements/ParameterUIElement’)
FROM dbo.fcobjects
WHERE serializedxml.EXIST(‘/Dashboard/ParameterElements/ParameterUIElement/Properties/BpmProperty[@Text=sql:variable(“@search”)]’) = 1

— iterate over the list of ParameterUIElements and filter keep only the “Site” ones
OPEN nodeloop
FETCH NEXT FROM nodeloop INTO @NodeList
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @tempXML = c.QUERY(‘.’)
FROM @NodeList.NODES(‘/ParameterUIElement’) t(c)
WHERE CONVERT(VARCHAR(MAX), c.QUERY(‘.’)) LIKE ‘%Site%’

INSERT INTO @FilterGUIDS([id])
SELECT @tempXML.VALUE(‘(/ParameterUIElement/@Guid)[1]’, ‘uniqueidentifier’)

FETCH NEXT FROM nodeloop INTO @NodeList
END

CLOSE nodeloop
DEALLOCATE nodeloop


IF EXISTS (SELECT id FROM @FilterGUIDS WHERE id = @ParameterUniqueName)

BEGIN
/* —————————— */
— BEGIN ORIGINAL MICROSOFT CODE
–If this parameter value already exists (for this login), update. Otherwise, insert.
IF( EXISTS(SELECT *
FROM [ParameterValues]
WHERE LOGIN = @Login
AND [ParameterUniqueName] = @ParameterUniqueName) )
BEGIN
UPDATE [ParameterValues]
SET [LastUpdated] = Getdate(),
[SerializedXml] = @SerializedXml
WHERE [Login] = @Login
AND [ParameterUniqueName] = @ParameterUniqueName

IF @@ERROR <> 0
BEGIN
RAISERROR (5580001, 16, 1, @tErrMsg, 7, N’ParameterValues’) WITH LOG
SET @ReturnCode = 1
GOTO exit_label
END
END
ELSE
BEGIN
–Insert record
INSERT INTO [ParameterValues] ([Login],[ParameterUniqueName],[LastUpdated],[SerializedXml])

VALUES (@Login, @ParameterUniqueName, Getdate(), @SerializedXml)

IF @@ERROR <> 0
BEGIN
RAISERROR (5580002,16,1,@tErrMsg,8,N’ParameterValues’) WITH LOG
SET @ReturnCode = 1
GOTO exit_label
END
END

— END ORIGINAL MICROSOFT CODE
/* —————————— */
— update all Site params for this user
UPDATE [ParameterValues]
SET serializedxml = @SerializedXml,
lastupdated = Getdate()
WHERE parameteruniquename IN (SELECT id FROM @FilterGUIDS)
AND [Login] = @Login
END

— END MOD BY BHAVIK
/* —————————— */

IF @TransactionIsOurs = 1
BEGIN
COMMIT TRANSACTION
END
RETURN 0

EXIT_LABEL:
IF @TransactionIsOurs = 1
AND @@TRANCOUNT > 0
ROLLBACK TRANSACTION
RETURN @ReturnCode
END


SQL Server 11 “Denali” CTP1 – Part 1 – SSMS Changes

November 10, 2010

The next version of SQL Server, Codename “Denali” had its CTP1 release today. Ive downloaded it and installed on a test VM and thought i would update the blog my experience so far.

Rather than jump under the covers straight away, i referred to the “Whats New” document online as a starting point. This looks to be a little incomplete in some places at the moment. Im going to summarise the bits i found most interesting and relevant to my work i.e. this is not an exhaustive list of all new features. As i looked at each of the features in the documentation i had a play around with some in SSMS or elsewhere.

SSMS

  • Right off the bat SSMS has had a littble bit of an overhaul.. you will notice new blue background shading and windows are more dockable like in Visual Studio 2010:

  • Something i discovered by mistake is that the dockable items can snap to the side or top of your screen just like any other Windows 7/Server 2008 window.. interesting. This means multi-monitor support is there. As confirmed by Brent Ozar here:

The default font for queries has changed changed to the fixed-width “Consolas” font. I also found it cool that you could CTRL-mouseup or down to zoom, just like Word and other apps. Screenshot shows me zooming to an exaggerated level.. note the Zoom level box in the bottom left:

I will update this post as i discover more new things in SSMS.


Locating SQL Server Samples

October 6, 2010

When trying to get SQL Server samples from codeplex you may wonder where they all go. This is something i keep coming across as it is not something one does often. So here is quick guide to where to find them once installed.

Assuming you installed all the SQL server samples available, you will notice that after installing samples you will have a Start menu shortcut leading to:

C:\Program Files\Microsoft SQL Server\100\Samples

Here you will find the Adventurworks online/offline reports and other SSIS/SSAS samples.

However, if you install the Adventureworks OLTP, DW, and OLAP databases (usually if you are working with B.I.) you need to find the location of some projects in order to deploy. Now you need to look in the Toosl folder i.e.

C:\Program Files\Microsoft SQL Server\100\Tools\Samples

i have assumed a default SQL installation directory on drive C.