Microsoft Training Blog

SharePoint Designer Governance

I hear these and many similar comments and questions all the time in my SharePoint Site Administrator/Power User classes:

• “Oh, I can’t do that because my company won’t let us use SharePoint Designer.”
• “I’d like to create a workflow, but no one is allowed to use SharePoint Designer.”
• “How do I get my company to let me use SharePoint Designer?”

These questions and comments all relate to SharePoint governance.  SharePoint governance is a very important consideration for any SharePoint deployment, one that from my experience a lot of organizations cut corners (or do even realize what they should be doing), to the detriment of their SharePoint deployment’s success.  SharePoint governance is a big subject; more than one blog post can cover.  Of all the subjects and classes we teach here at QuickStart (from Citrix, Cisco, VMWare, to Microsoft SQL Server, Windows Server, Exchange, etc.), there is only one product we have a dedicated class for: SharePoint.  I present this as evidence for the need for SharePoint governance, without which has led some people to call SharePoint a “virus”.  (Go Google or Bing “SharePoint” and “Virus” if you don’t believe me).

In this blog post, I’m going to discuss a specific SharePoint governance issue, that of SharePoint Designer.  What is SharePoint Designer, why do I care, who should have and use and for what, and what can and should be in a governance plan to make best use of SharePoint Designer will be the questions answered in this post.

What is SharePoint Designer?

SharePoint Designer is a tool that developed out of Microsoft FrontPage (back when it was still and active product) that Microsoft eventually made free to anyone using SharePoint.  Due to the way that SharePoint stores files in a SQL Server database, most web development tools cannot work with SharePoint content so a SharePoint aware design tool is necessary.  Over time, Microsoft has added more and more functions into SharePoint Designer, so it is not just a web customization and design tool.  SharePoint Designer 2010 allows Site administrators to, among other things:

• Customize SharePoint pages, including applying cascading style sheets, add web part zones, remove or change formatting
• Customize and apply custom master pages to change page layouts
• Backup and restore sites (limited, should not be used as a true backup solution)
• Add DataView web parts to SharePoint pages (used to display external data content)
• Design SharePoint Workflows
• Create sites, lists, libraries and change site structure
• Configure security settings
• Create External Content Types using Business Connectivity Services

Many of the new features added to SharePoint Designer increase the potential audience for the tool.  It is a very useful tool that can make administration of sites (security, creating and managing lists and libraries) faster and easier.

Why do I care about SharePoint Designer governance?

SharePoint Designer is, however, a tool that can be dangerous if users do not know what they are doing.  The experience of SharePoint 2007 and SharePoint Designer 2007 showed that users can make sites difficult to use, waste time changing colors and look and feel, create inconsistent navigation, or even completely breaking pages and sites so that they cannot even load.  This resulted in a lot trouble for other users, IT, help desks, and contributed to resistance and negative opinions of SharePoint some users gained.  Many organizations have since completely banned or restricted SharePoint Designer use to IT only.  The risk to this approach is losing the benefits that SharePoint Designer can provide to their organization.

Who should use SharePoint Designer and what should they be using it for?

Users who administer sites (create list and libraries, customize web parts on pages, set site security) or business users who already work with data or set business process rules could be beneficial users of SharePoint Designer.  The biggest risks with SharePoint Designer are those things related to customizing pages, not site administration, incorporating external data, creating and using lists and libraries.  Not allowing users to do these things with SharePoint Designer risks having SharePoint be treated as a web-based file server and project tracker that can take a lot of work in the Web UI to make useful.  By letter users build simple team or department workflows, display and incorporate data from external business systems, administer security and customize web parts quickly, they can build integrated solutions that users want to use, rather than SharePoint sites that are too “SharePointy”, as I’ve heard them described.  SharePoint sites that are too restricted to just storing list items and documents.

How can I gain the benefits and yet governing SharePoint Designer usage to minimize risk?

The discussion invariably leads to how to govern SharePoint Designer but still reap the benefits of using it.  Some organizations create teams of SharePoint Designer experts who handle requests from users for changes and features.  Some deploy SharePoint Designers to their site designers and site administrators in the company.  Banning SharePoint Designer is easy and safe and some administrators have been burned for allowing it. 

Fortunately, Microsoft saw the problems (and likely experienced some of them first hand in their own deployments), and provided some governance tools for us:  At the site collection or web application levels, administrators can restrict what is allowed with SharePoint Designer.  You can prevent users from customizing pages from the site definition (but still allow web part customization), prevent master pages customization, and prevent users from changing site structure (but still allow creation, modification, and deletion of lists and libraries).  This allows an organization to deploy SharePoint Designer to approved users, but limit those users to the most useful and less dangerous capabilities (Administration of security and managing list and libraries, creating workflows, BCS External Content Types, advanced formatting of lists, etc).

No governance can work with just computer based tools.  Governance starts and ends with good, logical, and well-designed governance policies in place and communicated clearly to those people they govern.  A good governance policy for SharePoint Designer is one that identifies who can benefit from SharePoint Designer, establishes rules and guidelines for using it, and incorporates a training aspect to the policies.  You wouldn’t let an employee drive a corporate vehicle without some proof they can operate it successfully, so don’t give users SharePoint Designer without some procedure to validate they know your policies and the software.  I’ve seen organization build a computer based training program with videos and examples, then use SharePoint surveys to quiz the users, with a final test of sample site with tasks for the users to perform.  An approver then reviews the site and approves (possibly through a SharePoint workflow) the user to have SharePoint Designer installed for them.  Third party training certifications have also been used in the past to meet this requirement.  Don’t forget that governance policies will vary from site to site.  A site like an employee HR site used by everyone in the company, you may not let anyone outside IT use SharePoint Designer with it, but a project team site it may be highly appropriate for a project manager to use SharePoint Designer.

If you have need of more information about SharePoint Governance or SharePoint Designer, or want to train your users to use SharePoint Designer, QuickStart had extensive 2 day classes for both of the subjects.

 Thank you!

Steven Randell Allen, Systems Engineer, QuickStart Intelligence

Microsoft: The New MCSAs and MCSEs, Your Questions Answered...

From Microsoft: Born to Learn

Hi Everyone!

As hopefully you all have seen, about two and a half weeks ago we announced our new cloud-enabled certifications – Microsoft Certified Solutions Associate (MCSA) and Microsoft Certified Solutions Expert (MCSE)! We have had lots of great questions come through about all the changes we have made and we wanted to make sure you all have the answers to these questions in one place.

I have the MCITP: EA and/or MCITP: SA. What do the changes mean to me?

It seems there is confusion and concern regarding the existing MCITP: Enterprise Administrator and MCITP: Server Administrator certifications and what they mean moving forward in this new program. The MCITP: EA and MCITP: SA are still the certifications to strive for on Windows Server 2008 technology. The MCSA: Windows Server 2008 was created solely as a pre-requisite for the new MCSE: Private Cloud certification.

Those of you that already earned the MCITP: EA were granted the MCSA: Windows Server 2008 certification as a way for us to acknowledge the expertise you already have. They are not considered equivalent certifications.

I have the MCITP: VA. What do the changes mean to me?

For those of you that have achieved the MCITP: Virtualization Administrator certification, this still remains a very valuable certification. For those of you on the path to achieving the MCITP: VA, keep on moving. Many organizations are looking for individuals with virtualization skills. As for moving into our new program, if you passed exam 659, you have passed one of the 5 exams required for the MCSE: Private Cloud certification. If you haven't already passed your Windows Server exams, we recommend you pursue the MCSA: Windows Server 2008. We have a promotion running now through June to help, if you are interested - buy your MCSA.

You announced MCSA: WS 2008, but why isn’t there an MCSE for Windows Server?

I think there is some confusion about that we announced the MCSA: Windows Server 2008 certification, but yet there are no MCSE certifications announced for Windows Server. The MCSA and MCSE certifications based on Windows Server 2012 technology will be announced in the near future. When we announce the MCSA and MCSE certifications based on Windows Server 2012, we will be providing upgrade paths for all of you that have earned the MCITP: SA and/or MCITP: EA certifications, making it easier to transition in to the new program. Eventually (within about a year or so), you will see that the pre-requisite for the MCSE: Private Cloud certification will then require the MCSA on Windows Server 2012 technology. Additionally, for those of you that have passed exam 659: TS: Windows Server 2008 R2, Server Virtualization and now have the MCSA: Windows Server 2008 certification, you only have one exam left to earn the new MCSE: Private Cloud certification (exam 246).

What is MCSA: SQL Server 2008 about, and how does it lead to the new MCSE: DP or MCSE: BI?

Similar to the MCSA: Windows Server 2008 certification, we have awarded the new MCSA: SQL Server 2008 certification. Those individuals who have passed exam 432: Microsoft SQL Server 2008, Implementation and Maintenance and exam 448: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance have now been granted the MCSA: SQL Server 2008 certification. You are now one step closer to achieve the MCSE: Data Platform and/or the MCSE: Business Intelligence certification.

I’m excited about the new certifications and titles! But what was going on with the new logos?

For those of you that already saw your new certification and tried to access your new logos, you may have seen the logos were incorrect. Thank you to all of you who let us know this issue was happening. The issue is now fixed and we apologize for any inconvenience.

We hope this information is helpful and if you have questions let us know.

Have a great day!

Erika Cravens, Microsoft

QuickStart: Column Store Indexes

As our BI tools get better,… (Performance Point Services, Reporting Services, PowerView, PowerPivot, etc,… ) we start collecting more and more data in our data warehouses.  It’s not uncommon to find dimension tables with millions (or hundreds of millions) of records.  That’s good news for the analysts who want as much data available as possible.  However it can be pretty tough on the data warehouse server to process queries against these huge tables.

SQL 12 has brought us a great option to address this problem.  Column Store Indexing.  It’s not perfect, and it probably won’t cure all of your processing woes.  But it’s pretty good, and should give most data warehouses a significant performance boost.  Microsoft is claiming performance gains for a “typical” data warehouse at between 10 and a 100 times faster.

One thing to keep in mind, is that once you apply Column Store Indexing, your tables become read only.  We can still use partitioning to switch data in and out, or use the drop/rebuild index to bring new data in.  That is fine as our data warehouse generally has a higher level of latency.

There are two things working together to make this happen.  One is that data is stored in a compressed columnar data format (stored by column) instead of row store format(stored by row).  The other is the new “batch mode” execution.  Data can now be processed in batches (1,000 row block) versus row-by-row. 

The other is the new “batch mode” execution.  Data can now be processed in batches (1,000 row block) versus row-by-row.  Batch mode also enables additional algorithms that can significantly reduce CPU overhead.

Keep in mind that “Row mode” might be used if there is SQL Server memory pressure or if parallelism is unavailable.  You can confirm batch vs. row mode by looking at the graphical execution plan.

Keep in mind that column store indexes are always non-clustered and non-unique.  They cannot be created on views, indexed views, or sparse columns.  And they cannot act as primary or foreign key constraints.  Unlike other index types,… there are no “key columns”.  Instead you choose the columns that you anticipate you will be using in your queries.  You can have up to 1,024 columns, and the ordering in your CREATE INDEX statement doesn’t matter. 

Another thing to be aware of is that as I’ve mentioned above, once you build the Column Store Index, the table becomes “Read Only” and INSERT/UPDATE/DELETE/MERGE is no longer allowed.  Nor is ALTER INDEX REBUILD/REORGANIZE.  You have a few options for keeping your data ware house latency down.  You can use Partition Switches (IN and OUT).  You can drop the columnstore index, load data, then add the column store index back in.  Or you can use a UNION ALL statement.

You can also prevent a query from using the columnstore index by using the columnstore index query hint IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX.

As you can see, there are a couple of limitations to using Column Store Indexes, but I think that they are overcome when looking at the 10 to 100 times performance gains.  They are easy to create, so consider building them and testing them to see if they are a good fit for your environment.

Thank you!

Larry Heppelmann, Systems Engineer, QuickStart Intelligence

QuickStart: New End User Reporting Tool: Power View

Some of you may have read previous blogs, or attended one of my BI classes, and heard me lament that true “end-user BI” just isn’t here yet.  I stick to my guns on that despite certain Microsoft evangelists assuring me that it is.  I think we have different definitions of “end-user”.

I am, however, ready to admit that it is around the corner.  With SQL RC0, the long awaited Project Crescent has evolved into Power View.  This is the end-user reporting tool we have been waiting for.

It’s lightweight, your clients need only a browser.  Power View is a Silverlight control that exists in a web browser, and is packed with a lot of features.  Generally this means that it is going to be complex to use, but in this case, it is not.

When you open it up, you are presented with a blank canvas.  Click on the entity (table) you want, and it will add the entire table as a report item.  You could select individual attributes as well.  This could be the whole report, as a table or matrix, or you can change it to a card interface that looks and behaves a lot like web parts in SharePoint, without the complexity of the SharePoint design interface (not that SharePoint is that complex, but we are aiming at end-users here).

You can add charts to the canvas, with a very intuitive interface.  Choose different chart types and populate the charts with data.  The main barrier here is not the report product, but the user’s knowledge of the data.  The interface is friendly, bordering on simple, assuming that the user understands the data.  This is a hurdle that technology can’t really help with.

When you click on the simple filtering and charting options, it passes the filter on to everything else on the canvas.  You don’t need to bring in filters, or link them like in SharePoint, it is done for you.

After you have populated the canvas with tables, matrices, cards, charts, etc,… you may find that you haven’t told the whole story yet.  Power View allows you to add multiple views of the report.  You may want to create a copy of the existing view, and tweak it, or you may want to start with a completely new view and add new functionality to it.

After creating the report, obviously you can publish to SharePoint, but you can also save it into a PowerPoint presentation.  Each view of the report becomes a slide in your new deck, and there is a button on the slide to allow the report view to be dynamic, allowing you to sort and filter right in the PowerPoint slide during your presentation.

I’ve always believed that for true high-end reporting, you will have to fall back to your BI Dev team to get the functionality you want.  Tools like this will be good for taking some of the lightweight, ad-hoc reporting tasks off of your desk.  That might still hold true to some extent, but with the advent of Power View, we are giving end users a tool that goes beyond lightweight, ad-hoc reporting.  The limiting factor is going to be their knowledge of the data.

While thinking about the viability of this product, I had planned on doing a demo filled with screen shots.  Instead, I found a great video demo put out by Microsoft that I think will give you a great picture of this product and its ease of use.

Thank you!

Larry Heppelmann, Systems Engineer, QuickStart Intelligence

How and What Can Microsoft Manage?

The year was 1992.  Using Windows for Workgroups 3.11, I’m sure that you remember how to control access to the Run menu, and other workstation control measures by editing the [Restrictions] section of the PROGMAN.INI control file.  Then came Windows 95 with PolEdit.exe, the System Policy Editor.  Group Policy followed that, and soon we believed in Intelimirror technologies, promoting the “follow me” concept that a business user’s software should follow them wherever they go. 

Intellimirror brought us to the beginning of this century, and, twelve years after that, we are still trying to address the problem of “User-Device-Affinity” or UDA for short.  We IT people want to direct appropriate business software to the user, wherever that user is and on whatever device the user may be using.  Make no mistake about this: Microsoft is serious about supporting business users who bring their iPhones and other consumer devices into work.  Look up the phrase “Consumerization of IT” for more information.

OK, the subject of this blog is Microsoft Management technologies, spanning those humble beginnings in PROGMAN.INI, including the burgeoning Group Policy settings and Client Side Extensions, and now, in 2012, the introduction of an entirely new datacenter management family of products.  “System Center 2012” is to the Datacenter what Microsoft Office is to the business user:  A suite of powerful, interrelated tools to accomplish a wide range of tasks and functions, namely the delivery of secure, well-managed IT services to business units.

Confusion abounds with the term “System Center”.  Do you mean the new SMS, System Center Configuration Manager?  Or do you mean System Center Operations Manager, for monitoring server applications and services?  There’s much more, to be revealed at RTM (Release To Manufacturing) time next month.  But you can find all of the System Center family ready to use in your non-production environments, chomping at the bits in Release Candidate (RC) status right now.

System Center 2012 Configuration Manager with Endpoint Protection

You’ve probably heard of the oldest member of the Microsoft Management family, which predates the System Center umbrella brand by about fifteen years:  Systems Management Server, now System Center Configuration Manager.  That solid tool has expanded its reach, both in depth and breadth for its 2012 release.  It is, I believe, Microsoft’s largest and most comprehensive product ever, and we can see that CM 2012 has been greatly redesigned and has inherited new functionality from previous versions and from other products.  ConfigMgr 2012 takes care of compliance management, change and configuration management, and includes flexible methods of deploying OS and applications software to business users, wherever they are in the world and whatever device they are using at the moment.  CM 2012 now includes anti-malware support, called Endpoint Protection (System Center EPP, formerly Forefront Endpoint Protection and before that, Forefront Client Security)

And maybe you’ve heard of MOM?  Microsoft Operations Manager was the second major version of Operations Manager, which we now call System Center Operations Manager.  OpsMgr has set a pioneering standard for model-based management, and this continues with OpsMgr 2012.  Expect to manage not only all Windows machines and their applications, but also many Linux distributions and any network devices like routers and switches.

System Center 2012 Operations Manager

How about management of virtualization platforms like VMware and Citrix hypervisors VMs?  System Center Virtual Machine Manager 2012 takes care of Microsoft’s Hyper-V, of course, but in the same window, you can be managing your VMware and Citrix hosts and virtual machines.  Yes, one Microsoft family to manage them all.  All your virtualization spare parts, such as templates, ISO files, VHDs, and OS profiles are contained in the VMM Library, the new mechanic’s bench for the datacenter.

And if we say the word “cloud” one more time, will you scream?  Well, a cloud is a managed object in VMM 2012.  You create your own private cloud right in the VMM console.  You manage one or more clouds, including Windows Azure subscriptions, in a brand new System family member which is a sibling to VMM.  Its new name is System Center App Controller, formerly referred to by its codename “Concero”.

System Center 2012 Virtual Machine Manager, and App Controller

And for continuous backup and point-in-time restores, we have the fourth or fifth major version of System Center DPM, Data Protection Manager.  DPM is a VSS-capable enterprise backup solution from Microsoft for your datacenter.

System Center 2012 Data Protection Manager

Confused with the sprawl of System Center products?  Well, we’ve only started.  Now let’s connect them together in some meaningful framework.  System Center Service Manager 2012 is the second version of Microsoft’s Customer Service Desk management software, including a comprehensive set of process-based tools to accommodate all facets of incident management, problem management, change and configuration management, service requests, and release management.  Service Manager is one of the wrappers of the System Center family, having several IT portals that provide a measure of self service and approval management to business users.

System Center 2012 Service Manager

And if Service Manager is like a wrapper, what about the glue and structural IT process automation to hold all the other products together, to keep them playing from your business “sheet of music”?  You already guessed it:  System Center Orchestrator is the new name for Opalis.  With SCO, IT can now, without any coding, tie together any kind of service request with an implementation.  We can intercept alerts and create tickets and automated resolutions.  Your creativity is the limit when it comes to orchestrating IT service delivery and anything else in your datacenter.

System Center 2012 Orchestrator

Your large company probably already has a licensing model that permits you to use all of the System Center products.  It’s time to compare the price you pay for Microsoft Management with the price you might be paying for other vendors’ products.

As an IT Pro, if you become skilled at one or two of these products, chances are pretty good that you will be a sought-after IT Pro over the next few years.  If you learn and apply all of the family, well, I’d say that you would be in a very enviable position to manage the world.  Name your price to those employers who will be competing for your attention in their datacenters.  But if you’re not there yet, please do join us at QuickStart Intelligence for our growing portfolio of System Center training offerings!  We’ll help you manage the world.  Show your developing skills to the businesses who need them now.

Mark Wheatley, Senior Principal Instructor, QuickStart Intelligence.

IT Training Rumination: The hours you spend...

There are moments, while being an IT somebody that you shake your head in wonder…possibly at the deliberation of how did that happen, or what next is going to have difficulties. Other times you are amusingly surprised at the outcome of an occurrence, something unexpected solves an issue, or you finally find the one setting that was overlooked. These are just a few examples of particulars that several of you have experienced over the years/months/weeks/days/hours…and it will continue…

Have you encountered the scenario where you are so focused on troubleshooting that you start to miss hours of time? You are cognizant that the world is still there, that light from the sun moves across the windows, that lunch was a couple of hours ago, but sometimes you get that sense: if you walk away you will miss that all important action, that needed to be accomplished for the end result of resolving. Items like - The click Install or New button that shows up towards the middle of an install. The second window that barely shows up behind the main window and unless someone told you, you miss perceiving it until that sense you get states “this is taking too long…” and you start moving screens around and find it.

I am in the middle of testing out a lab for one of our Quickstart courses and encountered a lot of different configuration settings during the passage of a day. With Hyper-V installed on a laptop there are ten different virtual images being used to build out an environment connected to another laptop that has its own ten virtual images, simulating two different connected domains. As I was saving the state of the images that night I looked at the time running and saw 14:22. I know I had started all of the images that same morning. So what felt like a couple of hours had turned into fourteen.

While I was working on the lab, did I shake my head in wonder? – Yes. Where there times of: when the steps in the documentation where not the steps I used to resolve something? – Yes. Did I get the satisfaction in the afternoon as sections were aligning well that this going to work? – Yes.

Sometimes there are hours when it is not about how much time it takes. This was one of those occurrences.

Has this happened to you? What do you think about the hours spend doing anything IT related? Beneficial to learn something on your own or wish someone could just tell you?

Doug Niles, Systems Engineer, QuickStart Intelligence

Auditing Tools in SQL Server 2008 and SQL Server 2008 R2

SQL Server contains many tools that can be used for various types and levels of auditing:

I hope you found this article looking at the available auditing tools in SQL Server 2008 interesting and useful.  In the next articles in this series, we will look at the two most powerful, secure, and flexible auditing methods in SQL Server 2008, SQL Server Audit and Event Notifications.  Following those we will look any changes to auditing that SQL 2012 provides us.

Has this helped you and what do you think?

Steven Allen, Systems Engineer, QuickStart Intelligence

Base-Lining Server Performance Pt. 3

Part 3 of 3

In this 3-part series I am going to take a look at documenting and troubleshooting performance problems on Windows servers.  In Part 1, I will discuss the need for base-lines.  Part 2 will look at how to create one, and in Part 3, I will describe how to analyze current performance in light of past performance (the base-line) with a goal of discovering why performance seems to be bad.

In the first two parts of this series, I discussed the importance of base-lines and how to create them.  To wrap it up, I am going to discuss how to use the base-line to analyze current performance, typically to find out what is causing poor performance in a server.  So when you first suspect that there is a problem you will need to make sure you have a few items.  First you will need to use a computer with two monitors.  You can get it done on one monitor, but it will add a great deal of frustration and a good deal of time switching back and forth between windows.  It is much easier to have each window opened on a separate monitor.  Second, you will need connectivity to the server in question, because you are going to run Performance Monitor (perfmon) and measure live performance while comparing it to your base-line.  Third, you will need a notepad and pencil.  In case you’re not sure, I mean the kind of notepad made out of multiple sheets of paper, not the electronic kind.

Here is your process.  You should open 2 copies of perfmon.  In one, you will open your base-line log, in the other you will look at live data on the effected server, one copy on each monitor.  From the base-line copy of perfmon, add the counters for just one object.  In the live copy, add the same counters for the same object.  If you add multiple objects at once, there is simply too much data to look at effectively.  Now that you have your base-line and your live data in front of you, there will be a huge temptation to start looking at the pretty graphs.  AVOID the temptation.  For now, you just want to look at one piece of information, namely the average number for a counter.  Go slowly and look at one counter at a time.  Click on the same counter in each copy of perfmon.  Is the average number basically the same or is it radically different.  This will be a judgment call, there aren’t absolutes here.  If the numbers are significantly different, write down the counter and the two averages.  Now move on to the next counter and repeat the process.  Once you have examined all the counters in one object, get rid of it and open the next object.  I find it best to do all the objects that are of the same type, one after the other.  So, you would do the first logical disk, then the second, then the third and so on, until all the disks are finished.  Then you move on to the NICS, etc.  This is going to be time consuming if you intend to do a good job.  Once you have looked at all counters of all objects, your notepad will have more than a few items listed.  Go back, clear all counters from the two copies of perfmon and add in just those things that are listed on your notepad.  Only now do you want to look at the graphs.

Finally, ask yourself some questions.  What are the patterns here?  Are the patterns such that all items spike at nearly the same time or are there successions?  How do the patterns fit in with certain times of the day?  One of the most common examples is as follows:

Spike in Memory usage, followed by spikes in processor and disk activity almost immediately afterward.

In order to understand the problem, you need to understand how Windows works.  When it runs low on available memory, it swaps some things out to hard disk, in your swap file.  This of course increases disk activity and the processor since it needs to make decisions about what to swap out.  This is just an example, but it shows you that the more you know about Windows processes, the easier it becomes.

Well, I hope this has helped.  If you have questions there are lots of resources for performance monitoring, or you could always come take a class.  Wink, wink, nod, nod.

So, now that you've read all parts. What are your thoughts?

Jeff Rathjen, Systems Engineer, QuickStart Intelligence

Base-Lining Server Performance Pt. 2

Part 2 of 3

In this 3-part series I am going to take a look at documenting and troubleshooting performance problems on Windows servers.  In Part 1, I will discuss the need for base-lines.  Part 2 will look at how to create one, and in Part 3, I will describe how to analyze current performance in light of past performance (the base-line) with a goal of discovering why performance seems to be bad.

In Part 1, I tried to convince you of the need for a base-line.  Now, let’s take a look at how to do just that.

There are many tools you can buy that will help you analyze server performance.  My favorite is free, and comes with Windows and has been part of the server software since the early NT days.  It is called Performance Monitor (perfmon).  At this point, some of you are a bit disappointed; you were expecting something better.  In my experience, most people don’t like perfmon, not because it is hard to use, but because they don’t know what to do with the info it provides.  (Is a big number good or bad? How do I make the numbers better? What do the numbers mean, anyway?)  Relax!  At least that’s what Frankie says to do.  The good news is that you don’t need to have all those answers in order to do base-lines.  I’m not going to tell you how to use perfmon, you can probably figure that out on your own.  If you aren’t sure, grab a book, use Google, or (shameless promotion here) come take a class at QuickStart.  The real question is “What do I measure for a base-line?”  Glad you asked ;).  So, here goes.

First, open perfmon and create a new User Defined Data Collector Set.  Name it whatever you want, I usually include the server name and the date the data was collected.  Don’t use the templates, they aren’t flexible enough, so create a Custom Data Collector Set.  You want to add Performance Counters.

Second, there are four objects I always measure, no matter what else I measure.  The four are:

1. Logical Disk
2. Memory
3. Network Interface
4. Processor

Additionally, add any objects that reflect the function of the server.  Is it a file server?  Add File Services counters.  Is it a SQL Server, add the appropriate counters for that.

For each of these objects you will want to add ALL of the counters.  In the end, you will probably not use all of them, but it is better to have them and not need them than the other way around..

Also. You should measure all of the counters for each instance of the object.  In other words, if you have multiple NICs, measure all the counters for each NIC, independent of the others.

Third, you need to measure long periods of time.  I generally recommend letting the base-line run for one week.  This means that you can’t measure very often, so set you measurements to be taken once every 15 or even 30 minutes.  Remember, we are not troubleshooting, we are looking for averages.  We are not expecting problems, just the opposite.  If you measure every few seconds, you collect so much data, it is hard to make sense of it later.

Fourth, you will need to store it.  When you create the Data Collector Set, it will ask for a storage location.  However, once the base-line is finished, you may want to store the log files in a central location. 

Fifth and finally, at the end of the week, or however long you decide to base-line for, stop and review how things were during that time.  Was performance on that server just what you were expecting?  Were there any unusual things that happened such as a server crash, or hardware configuration change?  If anything unusual happened with that server during the base-line, throw it away and redo it next week.  It is important that the base-line reflect normal behavior. 

When I do a base-line, I usually give the data a quick glance to make sure everything got measured and saved, but I don’t bother analyzing the data.  Trust me, it won’t really tell you anything interesting.  The only use for this data is when you compare it to live data, which we will talk about in the next installment.  Until then, get your base-lines running and get a good measure of normal behavior.

Anything to add?

Thank you!

Jeff Rathjen, Systems Engineer, QuickStart Intelligence

New in SQL 2012 Business Intelligence (BI) Pt.1:Data Quality Services

I’ve just finished teaching the beta classes for SQL 2012 BI and someone asked me: “So, what’s new in BI?”  Why would I want to upgrade to SQL 2012? Aside from the database engine and T-SQL changes, what’s new in BI?

The answer would have to be a number of things: Data Quality Services, improvements to PowerPivot for Excel, and Tabular Data Models. There are some changes in SQL Server Integration Services.

So, what is Data Quality Services (DQS)? It is a feature you can install on top of the SQL Server database engine. This creates some databases, stored procedures, and allows you to connect using the desktop installation of the DQS. It also allows use of the new SSIS DQS cleansing client.

Why do you want to use DQS?  You want to use it, because analyzing data from disparate sources is the heart of BI, and analyzing bad data leads to bad business decisions.  So before we analyze data we want to have accurate and consistent data.  One way to get that is to create a data warehouse.  (Data warehouses offer other advantages such as a single source of the data, but that’s not the issue here.)  It would be nice if the business users could examine their data, see the bad data, and correct it.  Now they can. And you can do this without having a data warehouse, before you analyze your data.

Most of us have encountered bad data: a field that should be filled in that is blank, a filled in field with a misspelled value (Wadhington instead of Washington), a variety of values instead of the standard approved value (Washington, Washington State, WASH, and WA). DQS allows the business user with the DQS client on their desktop to examine their data for patterns.

You start on the DQS home page:

You can see that you can create a new knowledge base, or use an existing one.  Once you have created or used a knowledge base, it appears below the Recent knowledge base heading.  Knowledge bases allow you to specify domains of values (City, State, Country, StoreType, etc.) Once you have a knowledge base, you can then use it against data from Excel or a SQL Server database to cleanse and standardize the data.

In a knowledge base, which may already have some, but not all, categories or domains you want, you can create a new domain.

Here I’ve created a new domain called city, of type string. I did this because when I looked at my Excel spreadsheet source, it had a column called city, that couldn’t be mapped to any domain in my knowledge base. Once I’ve mapped columns to existing domains, or created new domains, I can proceed to examine what values I have. I may have values of both WA and Washington, as below:

Note that I’ve marked that AR is an acceptable alternative for Arkansas, but that ‘Ark’ is not.  I can look for records that are bad, and mark them as errors. For those that are not errors, but alternatives, I can mark them as synonyms,

and then choose which is the leading synonym.

Here you can see that someone had entered ‘Whalesale’, I want it to show as ‘wholesale’.  Note that it is the business user (who knows the data) who is using the DQS client to indicate which is the leading value.  They can then connect to other data as part of a Data Cleansing project to correct the incorrect values.  DQS allows them to examine the corrections, as well as any newly identified errors, or new values. You can see below the suggestion made by DQS when it found a record for ‘New Yrk’; it suggested New York.

At the right you can see the ‘Approve’ and ‘Reject’ buttons. As the user approves the change, it gets moved to the Corrected tab. You can see that 3 records were already corrected. The data owner would, of course, iteratively examine data, since data input can result in new bad variants, especially as the business adds customers, products, addresses, etc.  This data is then published to the Data Quality Services database on SQL Server 2012.

Once the knowledge base is published, it can be used to examine and cleanse other data. Once the user is done cleansing the data, it can be exported to Excel, to a csv file, or to a database. You can see in the screenshot below that the exported results show the source data and the output (cleansed data).  In this way, the business data owner can document what has been changed and share those results with other business users.

Notice that I said this was being done by the business user or data owner.  This is a key aspect of DQS: the business data owner, not the IT admin, DBA, or developer is doing the work since they know the business.  Once there is a data model in DQS, then the BI developer could become involved by creating an SSIS package that examines the data using the new DQS cleansing client component.

I hope you can see how you could take advantage of this new tool to engage the users in cleansing and management of data.  This is part of the Microsoft approach of ‘Self-Service Business Intelligence’.

If you’re interested in finding out more, you can go to the DQS home page. You can also attend the new 10777A- Implementing Data Warehouse with Microsoft SQL Server 2012"Denali". The course is in beta at this point, using RC0 bits.  In class you can get your hands on the DQS client and try it out. In this class you will also learn about the other new features of SSIS 2012, as well as about Master Data Services which was new in SQL 2008 R2, and is much improved in SQL 2012. In my next blog in this series I will talk about Master Data Services, Microsoft’s tool for Master Data Management, and how that fits with Data Quality Services.

Thank you!

Saskia Schott, Systems Engineer, QuickStart Intelligence