Master Data Services, Step 1 What is it?

So I’m taking a look at Master Data Services, which is a new addition to SQL 2008 R2.  SQL 2008 R2 added a lot of new features to SQL Server, a lot of them will push DBA’s outside of the normal box we sit in.  That box has become a lot smaller over the last couple years as certifications have pushed us to be Database Administrators, Database Developers, or Business Intelligence Developers.  But all three professions had area’s that overlapped one another, and more often than not the way a work place will define the title depends on what they want you to do.

Strictly speaking, and going by the area of study covered by the certifications in each area, Master Data Management would seem to be a concept covered by Data Architects and maybe Database Developers.  However, Master Data Services ships with SQL 2008 R2 and if you company bought it you may be asked at some point to install it, or even what it does, And in that case here is a bit of an overview.

1^st if you are using SQL 2008 R2 or looking at it I would encourage you to go to, http://blogs.msdn.com/b/microsoft_press/archive/2010/04/14/free-ebook-introducing-microsoft-sql-server-2008-r2.aspx?wa=wsignin1.0 , and download the free e-book Introducing Microsoft SQL Server 2008 R2 by Ross Mistry and  Stacia Misner.  Ross is an expert with Microsoft and is currently working implementing SQL Server solutions out of the Microsoft Technology Center in Silicon Valley and Stacia is a BI expert that currently works for the amazing folks at SQLSkills.com.   The E-book is free, which is always a great bargain, and is chocked full of tasty knowledge nuggets……. mmmmmm nuggets.

So what is Master Data Services?  The 50,000 high view is a location where you can import multiple sources of data, organize the data, present a master view of the data, that can be exported to source databases, but will not be in and of itself the Gold Master Database.  Master Data Services uses a web API, and is not exposed via SSMS, SQL Server Management Studio.  It can integrate with SQL Server Sharepoint and have work flows and business rules written against it.  It exposes the Windows API and can have custom C# components coded for it.

You can set it up as a multi server deployment, or on one server.  However it will require x64 hardware and a Web Server.  For an overview of how to set up/install MDS take a look at the Master Data Management Team Blog, http://sqlblog.com/blogs/mds_team/archive/2009/12/10/installing-and-configuring-master-data-services-2008-r2-november-ctp.aspx . 

So reading over that description you may say, “this is a DBA/database technology?!?”.  Microsoft has decided that it will bundle it’s data related components with SQL Server.  This is good and bad in that normally you would do a point and click install for SQL Server, there are much more complex installs, but normally you have a Database Server, a set of drives, and your application lives on this server (be it virtual or physical).

You have applications that use your databases and data cubes, you may have dedicated SSRS and SSIS servers.  But at the end of the day we are a portion of an application, not typically the whole of an enterprise level application.  Master Data Services is, IMHO, an Enterprise level application.  It is a much deeper dive than just point click, optimize, and maintain.

I’ll be taking a deeper look as I go, but I just wanted to offer an up front, that this will not be a normal DBA road trip.

Thanks,

Brad

Denali

Denali CTP1 is available for download, http://www.microsoft.com/sqlserver/en/us/product-info/future-editions.aspx, This will be the latest major Version release since SQL 2008, and will build upon the minor version release from SQL 2008 R2.  There look like some really interesting features that will need to be flushed out more.  Brent Ozar has blogged about Hadron here, http://www.brentozar.com/archive/2010/11/sql-server-denali-database-mirroring-rocks/ .

Hadron will be where mirroring is going for SQL 2011, some new features will be that it is built on top of Windows Clustering, you can have up to 4 mirrors (only 1 in CTP 1), and you can execute read only queries against the mirrors, no need for snapshots. For more info see here, http://msdn.microsoft.com/en-us/library/ff877884(v=SQL.110).aspx .

Columnar level indexes, this was teased during the keynote for PASS opening night.  This would be big for Data Analytics for SQL Server, this will allow SQL to scream and compete with Oracle, DB2, TeraData, and Informix.  The TCP benchmarks on this will be unlike anything SQL has seen before.

This will be a really interesting ride.  More to come.

Thanks,

Brad

Powershell 2.0 Required for UCP on Windows XP

So I’m trying to install SQL Server 2008 R2 Utility Control Point, so I can take a look at all that the product has to offer and see how we can utilize it at my current place of business.

For the sake of full disclosure I’m doing this on a laptop running Windows XP SP 3, x86, Dual Core Processors and about 3 GB of memory.

I installed it about a week ago, and set up another named instance and had a SQL Agent Job running stored procedures from Adventure works every minute, to help simulate a work load on one of the databases.

I’ve read how it can take up to 45 minutes to get data posted to the dash board and so I set the jobs and got busy doing other things.   One week later I take a look at my Utility Explorer to see all the beautiful looking screens.

Needless to say this isn’t what I expected to see.  It almost looks as if there is no data.  So I check my enrolled instances to see what status they are running under.

Now I’m really bothered, apparently something has gone wrong.  So I take a look at my SQL Agent jobs to see if the jobs were created and if the history has been running properly.

I see that my sysutility_mi_collect_and_upload job has been failing.  So I isolate just that job, seeing as how collecting and uploading the data is directly related to seeing nice dashboards (or so I would guess).

And YOWZA! This has not been running at all, a quick glance back over the history and I can see this job has never run correctly. Let’s take a closer look at why this is failing.

Step 2 of the job is failing and the error is big and ugly  The important part is the following:

  (Get-Wmiobject  <<<< Win32_MountPoint) |   InvocationName   : Get-Wmiobject  PipelineLength   : 1  PipelinePosition : 1          WARNING : 11/8/2010 12:25:10 PM : 00000000000000000000000000000000000000000000000000000000000000000000000000000000WARNING : 11/8/2010 12:25:10 PM :     ErrorRecord    : Command execution stopped because the shell variable "ErrorActi                   onPreference" is set to Stop: Invalid class   StackTrace

When I set up the UCP I remember seeing one Yellow Warning/Yield Sign.  Honestly I breezed right by it, because if it’s not going to work you get a big red Error sign, right.  Right?  Wrong.

The Warning box was pointing to WMI not being set up correctly.  There are several Powershell class files that are required by UCP.  So the Question is what are they and how do you check that they are there.

They are:

Win32_MountPoint

Win32_Volume

Win32_LogicalDisk

Win32_PerfRawData_PerfProc_Process

Win32_PerfRawData_PerfOS_Processor

Win32_Processor

So how do you check for them?  Open up PowerShell and type get-wmiobject [objectname]

As you can see from above this is the error you will get if you are missing one of the classes.

A little more research shows me that Powershell 1.0 and Windows XP does not contain Win32_MountPoint and Win32_Volume classes.  This was reported on Microsoft Connect at here: https://connect.microsoft.com/SQLServer/feedback/details/540412/sysutility-mi-collect-and-upload-job-failure?wa=wsignin1.0 .

Microsoft Employee Jennifer Beckmann reported that the user was able to get everything working after installing Powershell 2.0

The link above lists a “work around”, by a user named Dryknot.  I would not recommend this solution as it recommended opening up the SQL Agent job and editing the PowerShell Code.  No offense to Dryknot I’m sure that she/he is a perfectly great person, and that they took the time to post a work around to help others shows that they are indeed a great person just trying to help others.  But there are 2 reasons I would not make this change.

1. You are changing the requirements that are being gathered from one class, Win32_MountPoint to another, Win32_MappedLogicalDisk.  So it’s like saying I want to gather information on an Orange and buying an Apple.  One thing is not like the other, even though they are both drive classes.
2. I’m missing 2 classes.  Even if I were to fix the Win32_MountPoint issue, I would still have one with Win32_Volume.  And if you use Mount Point’s you know how critical it is to gather space usage on them.  DON’T CHEAP OUT HERE.  Do not accept substitutes go for the original.

So If you are planning on implementing a UCP on a Windows XP machine make sure that you have Powershell 2.0 running or you could end up with the same results I did.

Thanks,

Brad

******DISCLAIMER********

All advice is AS IS.  There are no warranties or services provided as a result of anything you see here.