Bill Ramos on SQL Server

MDW Reports–New Source Code ZIP File Available

Bill Ramos — Sat, 19 Feb 2011 19:19:00 GMT

In my MDW Reports series, I attached V1 of the RDL files in my post - May the source be with you! MDW Report Series Part 6–The Final Edition. Since that post, Rachna Agarwal from MSIT in India updated the RDL files that are ready to go in a single ZIP.

The reports assume that they will ne uploaded to the Report Manager’s root folder and use a shared data source named MDW. The reports also integrate with the new Query Hash Statistics reports.

You can download them from my SQLBlog.com download site.

SQL Saturday 47 Phoenix February 2011

Bill Ramos — Sat, 19 Feb 2011 18:34:00 GMT

Today I presented data collection strategies for SQL Server 2008 at Phoenix SQL Saturday 47. I’ve attached my deck to this post so that you can get the links and references that I presented.

To learn more about the Data Collector, check out these links.

SQL Server 2008 Data Collector Proof of Concept – How to get started with Data Collector in your organization
SQL Server Query Hash Statistics – Replacement for the shipping Query Statistics collection set
Writing Reports Against the Management Data Warehouse – This is part 1 of the series on MDW reports. You can see all the articles through this link.

Be sure to click on the attachment link to download the slides.

SQL Server 2008 Data Collector Proof of Concept

Bill Ramos — Mon, 31 Jan 2011 03:37:39 GMT

In this blog post, I will go through the steps needed to prepare for a proof of concept (POC) for using the SQL Server 2008 R2 Data Collector (DC) and Management Data Warehouse (MDW) reports (covered in part 2) to get you up and running. I’ll cover the installation of the new Query Hash Stats collection set from Bart Duncan and how to get the MDW reports installed on your SQL Server Reporting Services (SSRS) server. My assumption for the POC is that you’ll have around 30 systems that you’ll want to monitor.

Download the Query Hash Statistics Collection Set

The Query Hash Statistics collection set was created by Bart Duncan of Microsoft. At the tail end of the SQL Server 2008 release cycle, the Manageability team ran out of time to incorporate the late changes that the Engine team made to support the “Query Fingerprint” and “SQL Fingerprint” features. See Bart Duncan’s blog post – “Query Fingerprints and Plan Fingerprints (The Best SQL 2008 Feature That You've Never Heard Of)” for a deep understanding of how this works as well.

What it this means for the Data Collector is that the collection of Query Plans and Text is highly optimized with the new Query Hash Statistics collection set. In addition, Bart has created a set of reports which offers more insight into the most expensive queries running on your system.

Download the Query Hash Statistics project and unzip the files to a shared location accusable by the computer hosting the MDW and the computers hosting the SQL Server database engines that you’ll collect data from.

Getting Started with Data Collection

I’m going to assume that you’ve already installed SQL Server 2008 or SQL Server 2008 R2 Database Engine and Management Tools. Optionally you can install Reporting Services for running the RDL files that I’ll provide.

The MSDN topic “Getting Started with the Data Collector”, goes over the basic steps for creating the Management Data Warehouse database and the Data Collector, but there a few things that you should know before you begin.

Plan on Centralized Collection. The MDW has the most value when the database is on server that is not used for your production workloads. The Data Collector is designed with this configuration in mind. This eliminates most of the “Heisenberg Observer Effect”. If you already have a SQL Server instance running System Center Operations Manager, I recommend putting the MDW database there as a single management host. The instance doesn’t need too much power, just plenty of disk space available.
Planning MDW Storage. For pessimistic planning, your MDW will grow 600 MB per day per instance for 14 days (the default retention period for the system collection sets). Once the retention period kicks in, data and collection logs will get purged at 2am local time for the centralized MDW. You’re looking at 8.4 GB times 30 instances or 252 GB.
Creating the MDW via the Wizard. Now that you know the pessimistic size for the MDW, when you run the wizard use this value for the initial size of the MDW versus the default of 100MB. This way you don’t get a performance hit all the time in the MDW as it grows 10MB at a time. You should set the growth size to 10GB as well when new instances are added. You can leave the transaction file size as the default since this rarely grows in size.
Keep Security Simple. For the POC, you’ll want to use a single domain user account that has admin rights to the SQL Server instance and also admin rights to the machines that will be reporting data to the MDW. This is not the recommended final configuration, but makes the POC easier to configure. You can always go back to a lower privileged proxy account once you understand all of the moving parts.
Set up Data Collection. For each of the instances you want to collect data from, you’ll run the Configure management Data Warehouse Wizard. Once the wizard completes, go to the Query Statistics system collection set under the Data Collector node as shown below and issue the Stop Data Collection Set command.

Follow the Quick Start Guide for Query Hash Statistics. This page contains steps to configure the prerequisites, which you can skip since I’ve already covered them above.

After updating the MDW to support the new collection set, installing the collection set on the target instance, and copying the reports as instructed to the MDW server, you are ready to go.

Query Hash Statistics Reports

The reports provided for the SQL Server Query Hash Statistics collection set replace the Query Statistics reports. To open the reports, connect SSMS to the SQL Server instance that hosts your MDW database, right-click on the MDW database in Object Explorer, and select "Custom Report..." from the Reports submenu. Browse to the location where you extracted the Query Hash Statistics files, and open MdwOverviewCustom.rdl. You will not need to manually browse to the .RDL file after this; you'll find the report name in the Reports context menu. The first time you open a custom report, SSMS will ask you to confirm that you trust the report.

You’ll want to wait a few hours after collecting data to run the reports so that you can start making sense of the data.

It’s that easy. In the next blog post, I’ll cover how to install the MDW reports on an SSRS server.

Technorati Tags: Data Collector,MDW,Query Hash Statistics,Proof of Concepts,SQL Server 2008 R2,SQL Server 2008

Be sure to follow me on Twitter at @billramo

Bill Ramos–Moving On–But Not Too Far

Bill Ramos — Fri, 19 Nov 2010 17:37:16 GMT

Bill Ramos, formally Principal Program Manager, SQL Server Manageability
Now WorkStream Manager for http://www.advaiya.com

Hi All,

As of November 22, after 15 years at Microsoft as a Program Manager, I'm joining a new company called http://www.advaiya.com. They specialize in developing strategy for the Microsoft product line, delivering content to the community and the Microsoft sales force, training and education, and working with the Microsoft sales force doing pre-sales activities. I'll do what I can to stay away from too much "Marketitecture", but no promises. :-) In this new role, it's going to be my job along with Regional Director Scott Golightly to contribute to the SQL Server community. I look forward to blogging with SQLBlog.com and hope to see you on the road.

Cheers,
Bill Ramos
Follow me on http://twitter.com/billramo
Now blogging at: http://sqlblog.com/blogs/bill_ramos/default.aspx

May the source be with you! MDW Report Series Part 6–The Final Edition

Bill Ramos — Thu, 18 Nov 2010 14:49:00 GMT

Bill Ramos, Principal Program Manager, SQL Server Manageability

Up until this point, I’ve walked through step by step how the MDW reports could be recreated to use SQL Server Reporting Services 2008 R2 and edit them in Report Builder 3.0.

At last week’s SQL PASS, I announced in my Wednesday session that I would release the source to the RDL files that I demonstrated.

At the bottom of this post, you’ll find a zip file that contains the RDL files that we converted up to this point. I say we because this was a collaboration with Bart Duncan – author of Query Hash Statistics, Rachna Agarwal from our Microsoft IT department in Hyderabad India and myself.

Download Query Hash Stats

The first think you will want to do is download and install the SQL Server Query Hash Statistics project on MSDN Code Gallery. This includes a new version of the Query Statistics collection set – the old one – you’ll want to turn off – and a set of reports that can run inside of SSMS as a custom report. I’ve included a slightly modified version of Bart Duncan’s reports that work in SSRS.

May the Source Be With You

You will want to refer back to my post “What are all these reports anyways? – MDW Report Series Part 5” that lists of the MDW reports and there relationship to each other. These reports assume that you have created shared data source called MDW in your Reports manager filder. Just unzip the reports and upload them to your Reporting Services server. Start with the new MdwOverviewCustom.rdl file and you should be good to go.

Here is a list of the reports you will find at the bottom of this posting in MDW RDL Files.zip.

MdwOverviewCustom.rdl – Bart’s new an improved Overview report

mdw_overview.rdl – The original one as part of the blog series

QueryHashStatsReportsDetail.rdl – From Query Hash Stats that includes a query to get the execution plan.

QueryHashStatsReportsTopN.rdl – The new and inproved Query Statistics report from Query Hash Stats. You’ll never want to look back after seeing this report.

server_activity.rdl – From the blog series.

sql_active_requests.rdl – See “What are all these reports anyways? – MDW Report Series Part 5”

sql_activity.rdl – See “What are all these reports anyways? – MDW Report Series Part 5”

sql_disk_snapshot.rdl – See “What are all these reports anyways? – MDW Report Series Part 5”

sql_memory_snapshot.rdl – See “What are all these reports anyways? – MDW Report Series Part 5”

sql_waits_blocking_chain_detail.rdl – See “What are all these reports anyways? – MDW Report Series Part 5” This report needs some debugging.

sql_waits_blocking_snapshot.rdl – See “What are all these reports anyways? – MDW Report Series Part 5” This report needs some debugging.

sql_waits_generic_snapshot.rdl – See “What are all these reports anyways? – MDW Report Series Part 5”

sql_wait_stats_snapshot.rdl – See “What are all these reports anyways? – MDW Report Series Part 5”

Send Your Comments Via Twitter

That’s it! Have fun with the source. Let me know what you think about them on http://twitter.com and include my @billramo address and the hash tag #MDWReports.

One Little Gotcha

The source reports point to a shared MDW datasource on my old machine at Microsoft. You'll most likely end up with the following error:

The report server cannot process the report or shared dataset. The shared data source 'MDW' for the report server or SharePoint site is not valid. Browse to the server or site and select a shared data source. (rsInvalidDataSourceReference)

The workaround is pretty simple, create your own MDW shared data source and then after uploading the report, open it up in Report Builder 3.0 or BIDS. Simply go the MDW data source, right click and select properties. Click on the Browse button to find your version of the MDW data source and click OK. Save the report,, and you should be good to go.

SQL Server Code-Named “Denali”–Getting Started with Availability Groups and Keyboard Customization

Bill Ramos — Sun, 14 Nov 2010 18:41:09 GMT

Bill Ramos
Principal Program Manager, SQL Server Manageability

If you missed SQL PASS or you didn’t get your copy of SQL Server code named “Denali”, you’re in luck. You can now download it from: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9

For the full experience, you will want to run the 64 bit version on Windows Server 2008 R2. You can find the TechNet 180 day evaluation copy of Windows Server 2008 R2 at: http://technet.microsoft.com/en-us/evalcenter/dd459137.aspx

Availability Groups Setup

To test our the new Availability Group feature set, you will need at least on good sized Hyper-V box that can run two VMs each with Windows Server 2008 R2 and then install the feature “Failover Clustering” on each node. Then, on each node, install an instance of Denali on each of the nodes. Use a Windows account for the database engine instance because the two instances need to talk to each other along with a shared location for the initial backup/restore seeding process.

You are not installing SQL Server Failover Cluster instances, so you don’t need a shared storage device. Think of Availability Groups as Database Mirroring on steroids.

I typically test with 1GB Hyper-V machines and then set the SQL Server memory limit to 500MB (thanks to BrentO for this tip at SQL PASS).

On each instance, you need to “Enable SQL HADR service” using SQL Server Configuration Manager.

Restore some test databases from one of your existing systems onto the database engine instance that you designate as the Primary. make sure the recovery model is set to Full. You may need to take a full backup and a transaction backup to get things to work in the wizard.

While connected to the Primary instance in the Object Explorer (OE), make a new connection to the Secondary instance in OE. This is to make sure both instances can talk to each other. To be extra paranoid, go to the Secondary computer; connect to the instance on that computer; and then connect to the Primary instance. This is to make sure you don’t run into any Kerberos issues.

From there, you’ll restart the instance on both nodes; launch SSMS; expand the Manageability node; right click on the Availability Group folder; and choose the New Availability Group… command.

Rather than go through the specifics of the wizard, it should be intuitively obvious what you need to do. When you add the Secondary replica instance in the wizard, and for the Primary replica as well, change the Read Mode in Secondary setting for both to “Allow all Connections”. This enables all applications like SSMS and Excel to access the databases that you selected for the availability group as read-only databases.

Check out the HADR books-on-line for the topic at http://msdn.microsoft.com/en-us/library/ff878484(SQL.110).aspx. BrentO also has a great write up titled “SQL Server Denali: HADRON ROCKS.“

Customizing the Keyboard

Before you file the connect item that Ctrl+E no longer executes a query – we know about this already – and the team is apologizes for the temporary inconvenience. F5 and the often forgotten Alt+X keys still work.

The Denali SQL Server Management Studio now uses the Visual Studio 2010 shell. As part of the conversion to the new shell, the team left in the ability for you to customize the keyboard just like the full Visual Studio product! At this point you should be saying w00t

To add Ctrl+E to Execute a query, do the following:

Go to the Tools menu and select Customize
Click on the Commands tab in the Customize dialog and then click on the Keyboard… button at the bottom of the dialog.
In the Press shortcut keys field, press Ctrl+E

You’ll see where we goofed up by assigning Ctrl+E as a global shortcut key to Query Execute (Ctrl+E(DMX Script Editor))

To fix this snafu, to the following:

In the “Show Commands Containing” control, type query.execute and press the tab key.
In the drop down the list of available values in the “Use new shortcut in:” control and select SQL Query Editor.
Press Ctrl+E in the “Press shortcut keys:” control and click on the Assign button.
Then click OK to dismiss the keyboard Options and Close to dismiss the Customize dialog

Ctrl+E now works. In the next public release of Denali, this will be fixed.

For more information regarding keyboard customization in Denali, see the topic “Manageability Enhancements (Database Engine)”

Don’t forget to follow http://twitter.com/ms_sql_server for breaking news and use the #denali hash tag. If you are having problems, check out the MDSN forums and as always, you can use the “Community” menu and “Report a Bug” command to file a Connect bug or suggestion. Don’t forget to use search to see if someone as filed a Connect bug on the same problem already like “SSMS Short Cut key "CTRL + E" is not working to execute any query”

Finding Bill Ramos at SQL PASS 2010

Bill Ramos — Mon, 08 Nov 2010 00:18:46 GMT

With the SQL PASS conference beginning tomorrow and in the spirit of Jen & Sean of the MidnightDBA fame I thought I would give you a run down of what I’ll be up to and things you might consider attending – just because.

Monday

SQL PASS Welcome Reception 6-8pm

This event is taking place at Washington State Convention Center in 6ABC. It’s a great chance to meet and greet old and new friends. Don’t forget the Quiz bowl. Team 2 with Buck Woody and Jen McCown should be a hoot.

SQL PASS Speakers and Volunteers Party 8-10pm

It turns out that none of the Microsoft speakers got the official invite, but we are able to attend. If you are a Microsoft speaker reading this, please email me for the details. I hear that bowling is involved.

Tuesday

Ted Kummert’s Keynote 8:45 to 10am

This is a keynote that you don’t want to miss. Tons of announcements and demos in 6ABC

Pickup Kilt for SQL Kilt Wednesday 10am

I’ll be picking up my kilt for my Tuesday 4:30 session on SCOM and BPA for the DBA where I will be modeling for UtiliKilt!

Utilikilt in Seattle opens its doors weekdays from 10am to 6pm. If you buy a kilt from them, be sure to mention my name. I! happen to be a official Utilikilt evangelist. did you know that they are the largest kilt manufacture in the United States? You can find their store at:

Utilikilts Flagship Retail Store
Authorized Dealer
620 1st Ave
Seattle WA 98104 – Map
(206) 282-4226

Be sure to learn how to best wear your kilt at http://blog.albanach.org/2006/07/how-to-wear-kilt-literally.html. Check out the link “Sit like a lady” as well.

Dev Pod Duty on 6th Floor – 11am 1:30pm

Learn what we are doing in the new release of SQL Server to making developers more productive. You can also learn more about that we did in SQL Server 2008 with Data-Tier Applications.

DBA448M - Si Se Puede! Achieving Separation of Duties with SQL Server Room 608 with @SQLGal from 3-4:15pm

Speaker - DBA268M - What’s new with SQL Management Pack and SQL Best Practices Analyzer? 4:30 to 5:45pm in Room 613-614

Can someone please bring a camera to my session? I’d like to prove that I was the first of the Ramos Clan to wear a kilt. I’m giving away System Center related books. I’ll also have Hershey chocolate bars, peanut butter, and Reese’s peanut butter cups – you’ll have to attend this session to understand the meaning.

Inappropriate PASS Sessions Party 2010 6:30pm

This party filled up fast, but I was one of the lucky ones.

SQL Sentry VIP Party 8:30

I’ve been invited to a private party hosted by the folks from SQL Sentry. I’m looking forward to seeing @AaronBertrand in his new role at SQL Sentry. If you haven’t checked out their free Query Execution Plan viewer, please do so. I showed it off to our SQL Performance Lab folks and they love it.

Wednesday

Keynote Speaker Day 2 - Tom Casey 9-10am 6ABC

This is another keynote that you don’t want to miss.

Microsoft® SQL Server® Mission Critical Booth Duty 10:45 to 1pm

I’d like to tell you want I’m going to be talking about at the booth, but I have to wait until after Tom Casey’s keynote and announcements.

DBA239 - Inside the SQL Server 2008 Data Collector, Ben Nevarez, 3-4:15pm, Room 4C4

Learn the basics of the Data Collector and Management Data Warehouse. My session on writing reports against the MDW follows.

Speaker - DBA450M - Managing system performance with the Data Collector and Management Data Warehouse with Reporting Services, 4:30-5:45pm, Room 4C4

I will unlock the mysteries around the Management Data Warehouse and how how you can build your own reports using SQL Server 2008 R2 Report Builder 3.0. I’ll also release a zip file that includes the key MDW reports as RDL files that you can customize for your own shop. Right after the session, Ben and I will head down to Room 305 to do an extended Q&A session.

Be sure to check out Bart Duncan’s Query Hash Statistics codeplex project that extends the DC and MDW to give better results with less storage that the existing Query Statistics collection set and reports. He’s been trying to ship this code since early 2007 and it’s finally out. It is so cool, I had to work it into my demos.

SQL Karaoke, Bush Garden from 7pm

Kilt’s and karaoke – can it get any better?

Thursday

AD475S - Query Tuning Tips, 10:15 to 11:45, Room 2AB, Itzik Ben-Gan

This is the first chance that I’ll get to hear Ben talk. I’ve always been scheduled opposite of his sessions.

DBA449M - Expert Query Performance Troubleshooting, 1 to 2:15pm, Room 3AB, Boris Baryshnikov, Conor Cunningham

You can never learn too much when it comes to tuning queries.

SQL PASS Focus Group - SQL Server and System Center Better Together, 3-4:30pm, Room 305

This is a by invitation only session that’s now full. It’s an extension of my Tuesday afternoon session help understand why DBAs don’t use SCOM as much as they could.

Plug for Focus Groups

Don’t miss this great opportunity to participate in one of the many focus groups that Microsoft will be conducting at PASS Summit 2010. It’s easy to sign-up! Just choose the focus group you want to participate in from the listing below and email the person who is assigned to that topic area. Your contact will be able to answer any further questions you might have about the focus group as well as schedule your participation.

All focus groups will be held at the Washington State Convention and Trade Center during the hours of the conference. Please choose from the topics below:

Focus Group Title and Contact
Focus Group Description

SQL Virtualization Experience, Present and Future
(Serdar Sutay)

Offering a highly available SQL Server environment depends on adherence to best practices and awareness of the latest patches and updates. More information on managing your SQL Server environment can help increase uptime by decreasing the time to resolve unplanned downtime. Join this focus group to help shape the next generation of Microsoft tools to help analyze the configurations of your SQL Server deployments.

DC and MDW
(Bill Ramos)

The DC & MDW focus group is to gather birds of a feather interested in learning more about the SQL Server 2008 Data Collector and Management Data Warehouse from Bill Ramos and Benjamin Nevarez. This focus group is open to anyone attending Bill’s and Benjamin’s sessions and will start at 5:50 pm.

Compliance in the Cloud
(Al Comeau)
Customers who have or are investigating deployment on SQL Azure, or are in general considering deploying in the cloud, to influence future planning in the areas of Security, Privacy, and Compliance.

SQL Server Configuration Analysis Futures
(Paul Mestemaker)

Technology Trends & the DBA
(Mark Stempski)

This moderated session will explore the current state of deploying and managing SQL Server within the Enterprise and discuss how trends like commodity hardware, virtualization, cloud computing (public & private), and compliance & auditing requirements are changing the way IT uses technology to bring business value and how these change the role of the DBA.

Upgrade and Patching
(Mark Stempski)

The purpose of this focus group is to understand upgrade and patching requirements and scenarios for both “public clouds” and “private clouds.” We will lead the participants through a discussion and will ask then to fill out a small set of survey questions. We are looking for a better understanding of customer’s expectations and requirements for high availability SLAs for upgrade and patching, understand pain points, etc.

Alerting in RS
(Mark Stempski)

Participants will see latest instantiation of Alerting for Reporting Services, participate in Q and A and help us identify and prioritize enhancements for further work.

Enhanced Contextual Help for Reporting Services/other MS BI Product Offerings
(Mark Stempski)

Participants will be shown concepts depicting expanding contextual help for Report Builder 3.0 and beyond and be asked to provide feedback to help us choose between competing designs. Previous customer comments and 2 benchmark tests of RB 3.0 have provided those areas that we think could benefit from this approach which we think might be an alternative to Office Ribbon contextual help. Participants will help us determine future of delivery of on-line and local help with inputs to MSDN and TECH Net for the Business Intelligence products.

Windows Azure Platform Development Experience (Mark Stempski)

Are you working on the Windows Azure Platform? If so, we’d like to talk with you. We want to learn about your experience, and the challenges you faced as you developed your application. We also like “get your reaction to some ideas about Windows Azure’s future. To thank you for your help, we’ll be providing you with a selection of Microsoft Hardware or Software as a gift.

SSIS/DTS Package, Database Table, View and Report Dependency and Impact Relationships
(Dawei Huang)

Join us to discuss your current practice about dealing with SSIS/DTS package, database table, view and report dependency and impact relationships. You will also be involved in a short design exercise for some of the scenarios we are thinking about. If you are a SSIS/DTS/ETL/DBA, you are welcome to join the discussion.

Survey Instrument (DATSTAT) for Classifying Users, Tasks and Environment that can be Administered in Several Venues at PASS Summit
(Mark Stempski)

PASS participants will also be encouraged to visit an on-line survey. The survey is designed to allow us to easily gather user needs and requirements and contact information. It also affords another communication vehicle between the product groups and users as often users will be invited to opt into various research activities. These activities run the gamut from early conceptual pieces through product definition work (prototypes) to early actual bits such as the CTPs or other release vehicles. Early looks and an opportunity to influence what we build at the early stages product development stages in exchange for telling us about your needs, something about yourself and work environment.

Focus on Design with the Microsoft Learning User Experience Team
(Barbara Birney)

Feel strongly about your experience finding classes to take in our Training Catalog and locating training centers you can use online? Our MSL User Experience Team would like to talk with non-certified IT Professionals and Developers about how effective our website experience meets your needs.

Influencing SQL Certification Exams - Be a Part of the Community of Item Contributors
(Don Tanedo)

This session will cover the role of subject matter experts in the development of certification exams, the methods of assessing skills in Microsoft certification exams, and the value of communities in the direction and focus of SQL Server certifications.

Present and Future of HP-Microsoft BI and DW Joint Technology
(Yaya Guo)

Join HP and Microsoft in this session, as we share joint development plans on the latest BI and DW technology, during which you will gain unparalleled insight into the direction on HP appliances and MS SQL solutions.

Hope to see you at SQL PASS!

What are all these reports anyways? – MDW Report Series Part 5

Bill Ramos — Mon, 25 Oct 2010 01:09:16 GMT

By Bill Ramos, Principal Program Manager, SQL Server Manageability Team

It just dawned on me that in preparing for SQL PASS and helping folks understand more about the existing MDW reports, that there is zero documentation on what reports are in SSMS for the system collection sets. Kalen Delaney covered the top level reports in her SQL Server 2008 white paper SQL Server 2008 Management Data Warehouse. In the SQL Server 2008 documentation, there is one topic How to: View a Collection Set Report that covers the three main reports. That’s it!

In this blog post, I’ll provide an overview of each of the reports so that you know what we’ll end up with once I’ve published all of the RDL files – rewritten for Reporting Services 2008 R2.

Report Time Line Control

The report time line control allows the user to select a point in time for reports showing data over time. The grayish blue areas represent time periods where there is data in the warehouse. The broad blue line represents the boundaries of the currently-selected time window. The report reflects the data in the blue window.

The user can navigate (change the size or position of the selected time window) using the buttons below the timeline, or by clicking on an arbitrary point on the timeline. Here is an example of a timeline control for an MDW instance that has no data.

Report Drill-Through Relationships

This is the “money” diagram that shows the navigation relationship between all of the MDW reports.

Reports in the MDW

This section shows examples of each of the MDW reports. The good news is that I don’t have to describe them for you since the description of each of the reports is in the header for the report. Hopefully the data that is presented in the report is intuitively obvious to the casual observer. Yes, the images are a bit on the old side, but they are mostly what we ended up shipping for SQL Server 2008. There were no changes made to the reports for SQL Server 2008 R2. Here we go…

Server Activity History

System CPU Usage

Launch this report by clicking on the green System line in the %CPU Utilization chart in the Server Activity History report.

System Disk Usage

Launch this report by clicking on the green System line in the Disk I/O Usage chart in the Server Activity History report.

SQL Server Memory Usage

Launch this report by clicking on the light blue SQL Server line in the Memory Usage chart in the Server Activity History report.

System Memory Usage

Launch this report by clicking on the green System line in the Memory Usage chart in the Server Activity History report.

SQL Server Waits

Launch this report by clicking anywhere within the SQL Server Waits chart in the Server Activity History report.

SQL Server Blocking

Launch this report by clicking on the Lock hyperlink within the SQL Server Waits report.

SQL Server Blocking Chain Detail

Launch this report by clicking on either the blocking chain part in the chart or on the Chain # link number in the list in the SQL Server Blocking report.

SQL Server Active Requests

Launch this report by clicking on the Sample Time within the SQL Server Blocking Chain Detail report.

When you expand the SPID row, you get details for that SPID as shown below.

SQL Server Observed Waits

Launch this report when clicking on any of the other wait hyperlinks in the Wait Category list in the SQL Server Wait report.

Note, in this report, it shows the queries that just happen to be sampled during the time of the observed waits. If you need to perform detailed wait analysis, check out Jonathan Keyayais’s white paper Using SQL Server 2008 Extended Events.This is 500 level material, but the MDW reports tend to get you close enough.

Query Statistics History

Launch this report directly from the Data Collection reports menu or my clicking on the blue SQL Server line in the % CPU Utilization chart in the Server Activity History report.

My motto here – click on the big stuff. We only list 10 queries here because these are the most expensive ones to worry about. Listing more would be like rearranging deck chairs on the Titanic while it’s sinking.

Query Details

Launch this report by clicking on any hyperlink text in any of the reports that has them. For example, SQL Server Observed Waits, SQL Server Active Requests, and the Query Statistics reports all have links to Transact SQL queries.

Note, clicking on the Edit Query Text link in the report, launches the query editor with the selected text. This is due to a limitation of the report viewer control not having the ability to select text and copy it to the clipboard. Once we get the reports ported to run with SQL Server Reporting Services, you’ll be able to copy text from the reports.

You can also click in the Duration, Physical Reads and Logical Writes links to sort the plans by these dimensions.

Query Plan Detail

Launch this report by clicking on the Query Plan line in the chart or the Plan # in the list in the Query Details report. The top part of the report is the same as the Query Details report, but the killer demo portion is shown below.

If there is a missing index for the plan, you can see the query text, but the copy/paste problem exists. So we added the View Graphical Execution Plan link to this report to display the execution plan that was captured by the Query Statistics collection set.

The Other Reports

We have Disk Usage reports, but they aren’t all that interesting and have some nasty bugs that require rewriting with the time line control. I’ll leave them for a future blog post.

Other Performance Troubleshooting References

For some great references on troubleshooting SQL Server performance problems, check out http://sqlcrunch.com/PerformanceTuning/tabid/100/Default.aspx. Rod Colledge has done a great job of cataloging all of the greatest white papers on all topics around SQL Server.

See you at SQL PASS

I’ll be at SQL PASS presenting how to write reports against the MDW for hosting them on Reporting Services on Wednesday at 4:30. my session follows Ben Nevarez’s Data Collector and Management Data Warehouse session. Right after my session, Ben and I will handle Q&A in room 305.

Be sure to wear your Kilt on Wednesday - http://www.sqlpass.org/summit/na2010/Connect/AfterHours.aspx. I’ll be wearing my Kilt on both Tuesday and Wednesday to drum up support for the event. If you need a Kilt, check out the folks at UtiliKilts in Seattle or order on line.

Enabling SQL Server Management Pack Alerts with System Center Operations Manager 2007 - Enabling the Blocking Sessions Rule

Bill Ramos — Sun, 10 Oct 2010 21:47:00 GMT

Bill Ramos, Principal Program Manager, SQL Manageability

In my previous blog post Installing the SQL Server 2008 R2 Management Pack for System Center Operations Manager 2007 R2 I showed how you can install SCOM 2007 R2 on a “bare-metal” VM using SQL Server 2008 R2 as the back end. One of the big features of the SQL Server 2008 R2 Management Pack is that we reduced the noise (or false alerts) reported by SCOM. This was done by disabling many of the alerts that we initially thought were critical/important and realized, it was better for you – the DBA – to determine what you think is important.

In this post, I’ll demonstrate how to enable and test one particular rule – Blocking Sessions – that is part of the SQL 2008 DB Engine set of rules.

This blog post is part of the series of posts that I’m using to prepare for my SQL PASS 2010 session on the SQL MP.

Using SQL Server Management Studio to find blocking sessions

First, a little background on how to find out your blocking problems within SQL Server Management Studio. This is importance since once you find the blocking SQL Process ID (SPID) , you’ll want to probably kill the SPID and then fix the application or query that is causing the problem for the rest of your system.

Standard Reports – Server – Activity – All Blocking Transactions

The Activity – All Blocking Transactions report is available by right clicking on the Server of interest; select the Reports command; then the Standard Reports command; and finally selecting the Activity – All Blocking Transactions report. As you can see from the example above, you can get a real time view of the blocking problem with this report. The trick is, you need to run the report when the problem is happening to diagnose identify the problem SPID.

Using Activity Monitor

With Activity Monitor, you can quickly filter to the “Head Blocker” SPID by clicking on the [v] control for the Head Blocker column in the Processes section of the tool and select the value 1 – if present – to filter to the offending SPID. In the case shown above, the connection string information for the Application identifies the offending SPID as “BadApplication ver 1.2”. If only all problems were so easy to identify.

By right clicking on the highlighted row in Activity Monitor, you can issue the “Kill Process” command. If the problem reoccurs, you can consider using the APP_NAME() function within the Classifier Function for the Resource Governor feature introduced in SQL Server 2008 Enterprise Edition to block the application from connecting to the server.

Configuring SCOM 2007 and the SQL Server MP to detect blocking sessions

Out of the box, after you have discovered the SQL Server computer using SCOM 2007 – see Installing the SQL Server 2008 R2 Management Pack for System Center Operations Manager 2007 R2. Once the system has cycled and SCOM agent is reporting health results to the Operations Console, you’ll see that even though a blocking situation is in progress, the monitor for the Microsoft SQL Server – Computers – indicates a Healthy state for the computer in question as shown below.

I happen to know that the highlighted server – BILLRAMO-KAT1 – is experiencing a blocking transaction problem. In order to detect the blocking scenario, you need to create an Override for the SQL Server MP.

Creating the Override for Blocking Sessions

To create and override for the blocking sessions scenario, you’ll need to do the following steps:

Click on the Authoring workspace bar and select the Monitors item under the Management Pack Objects folder in the Authoring tree as shown below.
In the Look for: control in the Monitors workspace, type in the phrase Blocking Sessions and then click Find Now. SCOM searches across all of the Management Packs that you have installed looking for the Monitor that contains the phrase. Select the Blocking Sessions for Type SQL 2008 Blocked SID Provider as shown below.
There should be 2 Blocking Sessions monitors as a result of the search. The second one is located under the SQL 2005 DB Engine target. You’ll need to perform the same steps to set up the override for your SQL Server 2005 computers. Right click on the Blocking Sessions line and select the Overrides > Override the Monitor > For all objects of class: SQL 2008 DB Engine command. This displays the Override Properties.
Before you go and enable the override, you are going to create a new destination management back. If you were to create the override in the “Default Management Pack”, you will lose the ability to easily export the overrides you setup for SQL Server 2008 computers to share with others. Creating the New destination management back, click on the New… button.
Complete the first page of the Create a Management Pack dialog as shown below with the name: SQL Server 2008 Customizations.
Click the Next button to go to the Knowledge Article page. You can leave this blank for now. This page allows you to provide information to the SCOM operator/DBA as to the purpose of the MP. Click the Create button to complete the wizard. You should now see “SQL Server 2008 Customizations” as the destination management pack in the Overrides Properties dialog.
Depending on your service level agreement for your application, you will want to set override properties to values that are appropriate for your systems. For example, for systems that are in production, you hope that you tested any new applications to avoid the blocking problems in the first place, so the default Interval (sec) collection value of 300 makes sense. If you have a shop of cowboy coders slinging code into production, you might want to set the value to 30. For demonstration purposes, I’ve used the settings shown below.
Click the OK button and then switch over to your Monitoring workspace. After 30 seconds, navigate to the Computers node under Microsoft SQL Server and if you are impatient, press F5 to refresh. If all goes well, you should see the following Critical alert.
I know and you know that the Critical state was caused by the blocking session scenario, but the way you can tell for sure is to double click on the Critical state cell to display the Health Explorer for the computer as shown below.

You can select the critical leaf node “Blocking Sessions” to see the knowledge article for the problem. If you click on the State Change Events tab, you can see when the last time a state transition occurred.

Back to SSMS Activity Monitor

At this point, you need to go back to SSMS to kill the offending SPID using Activity Monitor. SCOM provides an easy way to get to SSMS by going to the Database Engines node under the Server Roles for Microsoft SQL Server. You can then click on SQL Management Studio in the Actions pane as shown below.

To launch SSMS with the proper connection context, select the instance path with the Database Engines list and then click on the SQL Management Studio action indicated by the big red arrows above. Then, launch the Activity Monitor, select the Head Blocker, and kill the session. Problem solved!

Summary

Now you know how to:

Create a custom management pack (attached to this blog post as “SQL Server 2008 Customizations.xml”). You can save yourself the trouble of using the dialogs by downloading this file to your SCOM machine, going to the Administration workspace, clicking on the “Import Management Packs…” command in the Actions pane, and selecting the XML file.
Override a monitor that is disabled by default. You can also edit any of the enabled monitors the same way. Again, be sure to use the custom management pack vs. the Default Management pack
Troubleshoot a blocking session problem using SCOM 2007 and SSMS – as I like to say – better together.

To learn more about the Blocking Sessions monitor and other monitors in the SQL Server Monitoring Management Pack, be sure to Download and read the SQLServerMPGuide.doc file.

Technorati Tags: System Center Operations Manager,SQL Server Management Center,SCOM 2007 R2,SSMS,SQL Server,SQL Server Monitoring Management Pack,SQL Server MP,SQL Server 2008

Installing the SQL Server 2008 R2 Management Pack for System Center Operations Manager 2007 R2

Bill Ramos — Mon, 04 Oct 2010 04:16:00 GMT

By Bill Ramos
Principal Program Manager, SQL Server Manageability

Abstract

In this blog post, I will go over the steps to install System Center Operations Manager (SCOM) 2007 R2 and the recent release of the SQL Server 2008 R2 Management Pack (MP) on a bare bones Windows Server 2008 R2 Hyper-V virtual machine. I’ll include where you can download the various software components (free & evaluation copies). My goal is to make operational DBAs familiar with SCOM 2007 and the various MPs that are available to help you better manage large number of server instances.

This blog post is part of the series of posts that I’m using to prepare for my SQL PASS 2010 session on the SQL MP.

Installing SCOM 2007 R2 and the SQL Server 2008 R2 MP

I’m starting with a Windows Server 2008 R2 Hyper-V image with 4GB of RAM and 1 virtual CPU. This configuration is the minimum recommended configuration for all of the SCOM 2007 R2 features.

Step 0 – Download and install updates

There is always something out there that needs to get updated after installing Windows Server 2008 R2. Be sure to check for updates as part of your Initial Configuration Tasks as shown below.

Generally, you will need to reboot after this step to make sure Windows Server 2008 R2 didn’t have more updates that require a reboot. Just reboot to be on the safe side.

Step 1 – Add the IIS Role

After the reboot, you will need to add the IIS role to your server. Just click on the “Add roles” command shown above in the “Customize This Server” section of the Initial Configuration Tasks dialog. This will launch the Add Roles Wizard. At this point, I’m assuming you are logged into the server with a Windows login that is a system administrator for the machine.

Just click Next past the introduction page. You will then select the Web Server (IIS) role as shown below.

Then click Next. The wizard will then take you to the “Introduction to Web Server (IIS)” page. You are welcome to read the page and click on the hyperlinks to read more about IIS, but we are focusing our attention on getting SCOM up and running. So, click Next to move along.

The Role Services dialog is the tricky one since you need just about all of the services selected to pass the prerequisite checks for SCOM. So, select all of the check boxes to be on the safe side. At some point, I’ll follow up with a blog post on what you can disable. See below for what the Role Services page should look like before selecting the Next command.

Once you have selected every service – click Next to go to the Confirmation page and then click Install. With any luck, you should get to the Results page indicating “Installation succeeded”.

Go ahead and click the close button for the Add Roles wizard. Time for a quick test to see if IIS installed. go ahead and launch IE 8. If you are running with Enhanced Security Configuration, we’ll deal with that in a moment since you’ll need to download more software from the web.

In the IE8 address bar, type http://localhost. If all went well, you should see the following:

Go ahead and close the browser for now. You can also close the Initial Configuration Tasks dialog and you might want to check the box that says, “Do not show this window at login”.

As soon as the dialog closes, Windows Server 2008 R2 launches the Server Manager. At this point, let’s reconfigure “Configure IE ESC” so that you can browse the web without the annoying prompts. NOTE: It is Microsoft’s recommendation to have this feature on for production servers,, but we are just trying to get things spun up the first time with ease. So, be sure to turn ESC back on once everything is running well for you.

Go ahead and click on the Configure IE ESC link as shown below.

Then select Off for both Administrators and Users as shown below and click OK.

Step 1.5 – Installing AJAX

There is one more add-on that you must install called ASP.NET AJAX 1.0 that you can download from here. It’s also listed in the download links below. Just click on the Download button on the web site and run through the installation taking all of the defaults.

Step 2 – Installing SQL Server 2008 R2

I’m taking a gamble here since support for using SQL Server 2008 R2 was just announced on September 25, 2010 as part of a “FAST PUBLUSH” knowledge base article – see Support for System Center Operations Manager 2007 R2 that runs on a SQL Server 2008 R2 database. You don’t really need to check out KB Article 2425714 since I’ll guide you through the basics later on.

I’ll assume that you have access to the installation files for SQL Server 2008 R2. Check out the download links at the end of this posting for getting an evaluation copy. I recommend going with an Enterprise SKU since it has some cool features like row level compression for your database tables that can save you up to 20% or more disk space with a little more overhead on CPU. There are other great scalability features you can check out at the MSDN article titled Features Supported by the Editions of SQL Server 2008 R2.

Go to your installation folder and the click on Setup.exe. This displays the SQL Server Installation Center as shown below. You’ll want to select the Installation page.

Then select the hyperlink for New installation or add features to an existing installation as shown below.

I’m not going to run through all of the installation screens, but here are the basics to get you up and running. To keep things simple, I’ll assume you will use a Windows domain account with system administration permissions for all of the SQL Server services. I know this is not recommended, and you can change to lower privileged accounts once everything is working.

On the Setup Role page, select the SQL Server Feature Installation option.
On the Features Selection page, click Select All – maybe unclick SQL Server Books Online – since the topics will point to the web based topics by default.
On the Instance Configuration page, select the Default instance.
On the Service Accounts page, click on the “Use the same account for all SQL Server services” button and then enter in the Windows account that has system administration privileges. Yes – I did it again – you should really use a low privileged account, but this is a test environment. Make sure to set the Startup Type for SQL Server Agent to Automatic as well. DO NOT CHANGE THE Collation for the server. SCOM requires the use of SQL_Latin1_General_CP1_CI_AS.
On the Database Engine Configuration page, be sure to click “Add Current User” for the admin account. Like a good DBA, be sure to change your data directories off the C: drive.
On the Analysis Services Configuration page, click “Add Current user” and change the Data Directories as needed.
On the Reporting Services Configuration page, select the top option for “Install the native mode default configuration”.
You should be all set for installing SQL Server 2008 R2

With any luck, setup would have gone without a hitch and you can close down the SQL Server Installation Center dialog.

Step 3 – Install Microsoft Office 2010

If you want to author your own Management Packs, you need to install as a minimum Microsoft Word. SCOM 2007 R2 will work with older versions of MS Word, but this is a new box, so you might as well install all of Office. At least, you no longer need Outlook to send emails with SQL Server. NOTE: You will need to install the 32-bit version of Office for this all to work correctly. SCOM uses the Visual Studio Tools for Office runtime which is a 32 bit tool and only works with 32 bit versions of Office at the time of this blog posting.

Go ahead and just install with the default “Big Button” install option – Install Now.

Step 4 – Install SCOM 2007 R2

Note – Before you do anything, make sure you didn’t skip Step 1.5 above. If you forgot, the installation process will politely remind you that you needed to install the ASP.NET AJAX component.

Go to your installation location for System Center Operations Manager R2 release and run OPSMGR2007R2SEL.exe from your local disk. This file is a self extracting zip file with all the components needed to install SCOM 2007 R2.

When you run the .exe file, you should see the following message box kicking off the installation process.

Followed by the WinZip Self-Extractor UI.

Click the Unzip bottom. At the end of the unzipping process, you should get a message box indicating that 243 file(s) unzipped successfully. Once you click OK, the System Center operations Manager 2007 R2 Setup will be displayed. You may find it hidden behind another window. It looks like this:

Click on the Browse this CD because we need to “manually” run the DBCreateWizard command that is located in the SupportTools\AMD64 folder as shown below.

Here are the relevant instructions from the the article “Support for System Center Operations Manager 2007 R2 that runs on a SQL Server 2008 R2 database”.

Step 4.1 - How to use the DBCreateWizard tool to install the OperationsManager database

Note You must run the DBCreateWizard tool on the server that is running SQL Server 2008 R2.

Open the <InstallationMedia>\SupportTools\AMD64 folder
Note <InstallationMedia> represents installation media of System Center Operations Manager 2007 R2.
Double-click the DBCreateWizard.exe file to start the Database Configuration Wizard.
Note You may receive an error message when you start the wizard.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
938997 (http://support.microsoft.com/kb/938997/ ) Operations Manager 2007 Support Tools requirements
On the Welcome page, click Next.
On the Database Information page, perform the following operations:
- Under Database Type, select Operations Manager Database.
- Under Full Database Instance Name, select the instance of SQL Server where you want the database to be installed.
  Note An instance of SQL Server has the name of <ServerName>\<InstanceName>. The default instance has the name of <ServerName>.
- Leave the Create New Database option selected.
- Leave the SQL Port box blank.
  Note If you do not provide a port, the Database Configuration Wizard uses the port 1433 to connect to SQL Server. However, if the instance of SQL Server that you specify uses a port other than 1433 for connection, you must specify the port in the SQL Port box.
- By default, the database name is OperationsManager under Database Name.
- By default, the database size is 500 megabyte (MB) under Database Size.
Click Next.
On the Management Group page, perform the following operations:
- Under Management Group name, specify a name for the management group.
- By default, the BUILTIN\Administrators user group is specified under Configuration Administrator. If you want to specify a different user group, follow these steps: (NOTE: I just went with BUILTIN\Administrators)
  1. Click Browse.
  2. In the Select Group dialog box, click Locations.
  3. In the Locations dialog box, select the domain that contains the user group that you want to specify, and then click OK.
  4. Under Enter the object name to select, type the user group, and then click Check Names.
  5. If the name that you typed becomes underlined, click OK.
  For more information, see the "Before you Start" section of the following Microsoft TechNet website:
  http://technet.microsoft.com/en-us/library/dd789046.aspx (http://technet.microsoft.com/en-us/library/dd789046.aspx)
On the Error Reporting page, optionally indicate whether you want to send error reports to Microsoft, and then click Next.
On the Summary page, review the configuration summary shown below, and then click Finish

If everything went according to plan, you will get a message box indicating that the “Database created successfully” and click OK.

Step 4.2 - How to use the DBCreateWizard tool to install the OperationsManagerDW database

Note You must run the DBCreateWizard tool on the server that is running SQL Server 2008 R2.

Open the <InstallationMedia>\SupportTools\AMD64 folder
Note <InstallationMedia> represents installation media of System Center Operations Manager 2007 R2.
Double-click the DBCreateWizard.exe file to start the Database Configuration Wizard.
Note You may receive an error message when you start the wizard.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
938997 (http://support.microsoft.com/kb/938997/ ) Operations Manager 2007 Support Tools requirements
On the Welcome page, click Next.
On the Database Information page, perform the following operations:
- Under Database Type, select Operations Manager Data Warehouse Database.
- Under Full Database Instance Name, select the instance of SQL Server where you want the database to be installed.
  Note An instance of SQL Server has the name of <ServerName>\<InstanceName>. The default instance has the name of <ServerName>.
- Leave the Create New Database option selected.
- Leave the SQL Port box blank.
  Note If you do not provide a port, the Database Configuration Wizard uses the port 1433 to connect to SQL Server. However, if the instance of SQL Server that you specify uses a port other than 1433 for connection, you must specify the port in the SQL Port box.
- By default, the database name is OperationsManagerDW under Database Name.
- By default, the database size is 500 megabyte (MB) under Database Size.
Click Next.
On the Summary page, review the configuration summary, and then click Finish.

Step 4.3 – Now it’s time to install SCOM 2007 R2

Click on the Hyperlink to “Install Operations manager 2007 R2”. Then, click Next on the welcome page.
Accept the license terms and click Next.
Type in a User Name and Organization and click Next.

Now comes the tricky part because we don’t want to install the Database components at this time as per the article “Support for System Center Operations Manager 2007 R2 that runs on a SQL Server 2008 R2 database”. This article neglects to tell you how not to install the Database components. Starting from the figure below:

Click on the icon drop down control next to the word Database as highlighted above.

Choose the command “This component will not be available.”. Once you’ve selected the command, the dialog should look like this:

Enter in the name of your SQL Server into the dialog below and click Next.
Enter in your Domain account name in the dialog below and press Next
Enter in your domain name again for the SDK and Config Service Account and click Next.
For the Web Console Authentication Configuration, choose the first option “Use Windows Authentication (Recommended)” and click Next.
For the Customer Experience Improvement Program, chose the first option “Join the Customer Experience Improvement Program (Recommended). By doing this, we can see how you are using the product to determine if we are firing too many false alerts, or that you are not using certain reports, etc. This will help the product teams make rational decisions on feature enhancements based on your input. Click Next to continue. Then, click Install.

If life is treating you fairly, the installation should complete and display the following dialog.

You don’t want to Start the Console quite yet, but you do want to backup the Encryption Key when you Finish the wizard. just follow the steps for backing up the key to a box off-site from the original machine. The instructions in the wizard are straightforward.

Step 4.3 - How to install System Center Operations Manager 2007 R2 Reporting

Note You must follow these steps on the SQL Server Reporting Services server.

Open Computer Management.
In the Computer Management MMC snap-in, expand System Tools, expand Local Users and Groups, and then click Groups.
Locate the following group:
SQLServerReportServerUser$<hostname>$MSRS10_50.<SQLInstanceName>
Rename the group to the following by removing "_50" from the group name:
SQLServerReportServerUser$<hostname>$MSRS10.<SQLInstanceName>
Install System Center Operations Manager 2007 R2 Reporting.
For more information, see the following Microsoft TechNet Website: http://technet.microsoft.com/en-us/library/bb432143.aspx

Important When you install System Center Operations Manager 2007 R2 Reporting, you should not install the Data Warehouse component. See below image for not installing the DW component.

The installation process is very similar to installing the main SCOM 2007 tools, so I’ll skip the dialogs. Again, when you get to the “Operational Data Reports” wizard page, please select Yes, send operational data reports to Microsoft.
After you install System Center Operations Manager 2007 R2 Reporting, rename SQLServerReportServerUser$<hostname>$MSSRS10.<SQLInstanceName> back to original name SQLServerReportServerUser$<hostname>$MSSRS10_50.<SQLInstanceName>

Supported configurations for System Center Operations Manager 2007 R2
For more information about supported configurations for Operations Manager 2007 R2 that runs on a SQL Server 2008 R2 database, visit the following Microsoft Website: http://technet.microsoft.com/en-us/library/bb309428.aspx

You can now Exit the SCOM 2007 R2 installation program

Step 5 – Install the SQL Server Monitoring Management Pack

Go to the download center for the free SQL Server MP at http://www.microsoft.com/downloads/en/details.aspx?FamilyID=8c0f970e-c653-4c15-9e51-6a6cadfca363&displaylang=en

Go ahead and download the SQLServerMPGuide.doc file for detailed information regarding the MP.
Click Download for the SQLServerMP.msi file and then run it in place – or save it to your Downloads folder and then run the MSI.
For the installation folder, leave it to the default location under the Program Files (x86) folder structure. Also, select to Install the MP for Everyone.
Click Install to download the files.

The files are simply downloaded to the install location, you now need to import the MP files into the SCOM Operations Console.

Go to the Start menu, locate the Operations Console, and click on the command. If all the stars aligned, you should see a screen that looks like this for Monitoring when you select Windows Computers in the Monitoring tree.
Now, select the Administration workspace item and then highlight the Management Packs node in the tree as shown below:
Now choose the Import Management Packs… command as shown above. In the Select Management Packs dialog, click on the + Add button and choose Add from disk … as shown below.
Go ahead and click Yes to search the web for dependencies, but they are all in download location C:\Program Files (x86)\System Center Management Packs\SQLServerMP. press Ctrl+A to select all files in the dialog and click Open. Then, click the Install button.
You will see two Discovery and Monitoring packs for SQL 2005 and SQL 2008. SQL Server 2008 covers both 2008 and 2008 R2 release.
Once the import is complete, press the Close command.
Now click back to the Monitoring workspace and then expand the Microsoft SQL Server node and select Computers as shown below. You might have to click inside the detail area and press F5 to see your SCOM SQL instance.
To Add and new instance to monitor, select the Administration tab and click Discover Wizard… hyperlink as shown below.
Select the Windows computers option and click Next.
Choose the Advanced discovery option and det the Computer and Device Classes to Servers only and click Next..
Choose the Browse for option for the Discovery Method and type in the computer name you want to add in the dialog area. You can also browse for computers.
Use your admin Windows account for the Administration Account and click Discover.
Check the FQDN for the SQL machine that you want to discover and click Next.
Chose the Other action for the Agent Action Account and type in your Windows credentials, then click Finish. This will display the Agent Management Task Status. Wait for this to finish. You should see a dialog like this completing the addition of the new box. SCOM will attempt to enumerate all of the SQL Server instances on the box. Then click Close.
If you go have to Monitoring and click on Windows Computers, you should now wee two box names. When you go to SQL Computers, you should see two instances as well.

Software Evaluation Download Sites

Are the “go to” links to download evaluation copies of the software described in this blog post.

Windows Server 2008 R2 (64-bit only) – 180 Day Trial - http://technet.microsoft.com/en-us/evalcenter/dd459137.aspx
SQL Server 2008 R2 – 180 Day Trial - http://technet.microsoft.com/en-us/evalcenter/ee315247.aspx
System Center Operations Manager 2007 R2 – 180 Day Trial - http://www.microsoft.com/systemcenter/en/us/operations-manager/om-try-it.aspx#downloads
SQL Server Monitoring Management Pack – Free - http://www.microsoft.com/downloads/en/details.aspx?FamilyID=8c0f970e-c653-4c15-9e51-6a6cadfca363&displaylang=en
Microsoft Office Professional 2010 – 60 Day Trial - http://technet.microsoft.com/en-us/evalcenter/ee390818.aspx. You’ll need this if you want to author your own management pack.
ASP.NET Ajax Extensions 1.0 – Free - http://www.microsoft.com/downloads/en/details.aspx?FamilyID=ca9d90fa-e8c9-42e3-aa19-08e2c027f5d6&displaylang=en

Looking Forward to SQL PASS

As I continue to prepare for my SQL PASS session on the new SCOM MP for SQL Server, I’ll continue blogging – especially examples with using the new SQL Server 2008 R2 Monitoring MP. I will also cover how the data collection with SCOM 2007 R2 can be used in conjunction with the SQL Server 2008 Data Collector and Management Data Warehouse and the SQL Server 2008 R2 manageability tools.

Please feel free to add a comment to the blog post and don’t forget to rate it!

Technorati Tags: SCOM 2007 R2,SQL Seerver 2008 R2,SQL MP

Server Activity History Report–MDW Report Series Part 4

Bill Ramos — Mon, 28 Jun 2010 15:50:00 GMT

By Bill Ramos, Program Manager, SQL Server Manageability Team

In part 3 of the report series, I showed how to get CPU data from the MDW and display it as a thumbnail chart in the Server Activity History report. In part 4, I’ll show how to extract data from the MDW for the Disk I/O Usage, Network Usage, and Memory Usage thumbnail charts rounding out the top of the report as sown below.

For the DBA new to Reporting Services, I’ll also show how to make sure that the color for System and SQL Server series is the same across the thumbnail charts – something that the shipping reports don’t do. I’ll also point out some other tricks like changing the thickness of the lines in the charts – seemed like it should be simple – but took me an hour to figure that one out.

Disk I/O Usage Thumbnail

The Disk I/O Usage chart gets its data from the Process performance counter object and has four conversion records in the core.performance_counter_report_group_items table that I described in part 3. The System series reports the _Total for the sum of the IO Read Bytes/sec and IO Write Bytes/sec performance counters. The SQL Server series reports the sum of the same two counters for the specific instances. You can use the following query to see what counters are used.

USE sysutility_mdw  -- Use your MDW name here

GO


SELECT  counter_subgroup_id, series_name, object_name, object_name_wildcards, 

        counter_name, instance_name, multiply_by

FROM    core.performance_counter_report_group_items

WHERE   (counter_subgroup_id = N'IOUsage');

When you run the query, you’ll notice that the multiply value is 0.0000009537. This value is used to convert bytes/sec to MB/sec ( = bytes / 1024 / 1024).

From a reporting standpoint, we can reuse the Perfmon Dataset used for the CPU chart by just changing the filter expression for the chart.

Tweaking the CPU Chart Before Copy/Paste

The easiest way to get going with the Disk I/O Usage chart is to copy the CPU chart and paste it back into the report. Before doing the copy/paste action, there are two little changes you’ll want to make.

Getting the series colors to be consistent for the charts. Reporting Services uses color palettes to determine which color to display for the discrete series values in the chart. For the this report, I’m using the BrightPastel palette. You can also create custom palettes by following the MSDN topic: How to: Define Colors on a Chart Using a Palette. The trick is knowing that the Reporting Services uses the first color in the palette for the first series, the second color for the second series, and so on, based on the order of the series values in the group. If we jump ahead to the Network Usage chart, you’ll see that there is only one series group value = “System”. The other charts have both “System” and “SQL Server”. By default, the chart displays the series groups in ascending order. This means, you would normally see “SQL Server” before “System”. What ends up happening is, Network chart, “System” shows up as Blue, but since it’s the second value in the other charts, it shows up as Gold. To correct this problem, do the following:
- Click the CPU chart to display the Chart Data on the lines in the CPU chart that represents the series.
- Choose the Series Properties command as shown below

- Go to the Sorting page and click Add.
- Use the Sort by value of [series_name] and the order of Z to A.
- Click OK. The thumbnail charts will now show System before SQL Server giving a consistent color for the series groups.
Making the series lines thicker. In a future posting, we are going to add an Action to the series lines to drill into detail reports. to make it easier to click on the line, you’ll want to make the line thicker. To save you the agony of guessing what property value to set, just follow these instructions.
- Right-click on one of the lines in the series graph – inside of the chart area – and choose the Series Properties command.
- Go to the Border page.
- Change the Lined width from 1pt to 2pt.
- Click OK. The lines inside of the chart should now be thicker.

Creating the Disk IO Usage Chart

Just select the CPU chart so that the entire outline of the chart is selected and issue the paste command. Then move to chart along side of the CPU chart. Follow these steps to modify the CPU chart to use the IOUsage data.

Chart Properties

Right click on the new chart and choose the Chart Properties command.
On the General page, change the name of the chart to DiskIOChart.
On the Filters page, change the filter Value from cpuUsage to IOUsage and click OK.

Chart Data Values

Click in the chart data region to display the Chart Data control
Change the aggregate function for [avg_formatted_value] from Avg to Sum as shown below. This is so that the you report the sum of IO Read Bytes/sec and IO Write Bytes/sec performance counters for the interval period.

Vertical Axis Properties

The chart needs to show MB/sec so the maximum range for values is no longer 100. To change this axis values, do the following:

Right-click on the vertical axis numbers and choose the Vertical Axis Properties command.
On the Axis Options page, change the Maximum value from 100 to be blank. This allows the chart to automatically scale based on the maximum value for the time window.
Click OK.

Label Text Changes

Change “%CPU” in the title to “Disk I/O Usage”
Change “%CPU” on the vertical axis to “MB/sec”

At this point, you can now Run the report to check out your work.

Network Usage Thumbnail

The Network Usage chart is almost like Disk IO. It uses just one counter ( Bytes Total/sec) from the Network Interface counter object. to create the chart, do the following:

Copy the Disk I/O Usage chart and paste it into the report. Move it just to the right of the existing Disk I/O chart.
Right click on the chart and choose the Chart Properties command.
On the General page, change the Name of the chart to NetworkUsageChart.
On the Filters page, change the filter value from IOUsage to networkUsage.
Click on the chart area to display the Chart Data control.
In the Sum Values section, click on the little down arrow for the [sum[avg_formatted_values]] expression (the second one down) and select the Expression command. For the Y Value expression text, enter the following:

=Avg(Fields!avg_formatted_value.Value) / 1024 / 1024

The reason for using the expression is that the record for for networkUsage in the core.performance_counter_report_group_items table has a multiply factor of 1.0 (a compensated bug) . We originally tested on slow network cards and didn’t realize we needed to report on a scale of MB/sec. Rather than fix the table, the “unnamed” developer put the conversion of bytes to MB in the chart value expression. We have to live with this for now.

Change the chart title from “Disk I/O Usage” to Network Usage”.

At this point, the Network Usage chart is ready to go.

As you can see, reporting against performance counters is really simply with the help of the Perfmon dataset that uses the snapshots.rpt_generic_perfmon stored procedure in the MDW. The magic having the corresponding records in the core.performance_counter_report_group_items table.

Memory Usage Thumbnail

Data for the memory usage for the System and SQL Server is stored in the snapshots.sql_process_and_system_memory table. This data is collected every minute by the Server Activity collection set from the sys.dm_os_process_memory, sys.dm_os_sys_memory and sys,dm_sys_info DMVs using the following query:

SET NOCOUNT ON

SELECT 

    pm.physical_memory_in_use_kb            AS sql_physical_memory_in_use_kb, 

    pm.large_page_allocations_kb            AS sql_large_page_allocations_kb, 

    pm.locked_page_allocations_kb           AS sql_locked_page_allocations_kb, 

    pm.total_virtual_address_space_kb       AS sql_total_virtual_address_space_kb, 

    pm.virtual_address_space_reserved_kb    AS sql_virtual_address_space_reserved_kb, 

    pm.virtual_address_space_committed_kb   AS sql_virtual_address_space_committed_kb, 

    pm.virtual_address_space_available_kb   AS sql_virtual_address_space_available_kb, 

    pm.page_fault_count                     AS sql_page_fault_count, 

    pm.memory_utilization_percentage        AS sql_memory_utilization_percentage, 

    pm.available_commit_limit_kb            AS sql_available_commit_limit_kb, 

    pm.process_physical_memory_low          AS sql_process_physical_memory_low, 

    pm.process_virtual_memory_low           AS sql_process_virtual_memory_low, 

    

    sm.total_physical_memory_kb             AS system_total_physical_memory_kb, 

    sm.available_physical_memory_kb         AS system_available_physical_memory_kb, 

    sm.total_page_file_kb                   AS system_total_page_file_kb, 

    sm.available_page_file_kb               AS system_available_page_file_kb, 

    sm.system_cache_kb                      AS system_cache_kb, 

    sm.kernel_paged_pool_kb                 AS system_kernel_paged_pool_kb, 

    sm.kernel_nonpaged_pool_kb              AS system_kernel_nonpaged_pool_kb, 

    sm.system_high_memory_signal_state      AS system_high_memory_signal_state, 

    sm.system_low_memory_signal_state       AS system_low_memory_signal_state, 

    

    si.bpool_commit_target                  AS bpool_commit_target, 

    si.bpool_committed                      AS bpool_committed, 

    si.bpool_visible                        AS bpool_visible

FROM sys.dm_os_process_memory AS pm

CROSS JOIN sys.dm_os_sys_memory AS sm   -- single-row DMV

CROSS JOIN sys.dm_os_sys_info AS si;    -- single-row DMV

To prepare this data for the reports, you can use the snapshots.rpt_sql_process_and_system_memory stored procedure to get the data just like you would for the performance counters.

This stored procedure only takes three parameters:

ServerName
EndTime
WindowSize

Create the SqlProcessAndSystemMemory Dataset

To create the dataset that calls the snapshots.rpt_sql_process_and_system_memory stored procedure, do the following:

Right click on the Datasets node in the Report Data control and select the Add Dataset command.
Change the Name field to SqlProcessAndSystemMemory.
Select the “Uue a dataset embedded in my report” option.
Select the MDW Data source.
Choose the Stored Procedure option for Query type.
Choose the snapshots.rpt_sql_process_and_system_memory for the stored procedure name.
Click OK for the Dataset Properties.
Click OK again for the Define Query Parameters dialog.

You now have a dataset ready for the Memory Usage thumbnail chart. You’ll notice that there are 45 fields returned in the dataset – we will only need four fields.

Creating the Memory Usage Chart

This chart is a bit different from the other charts – there is no series group. Instead, it uses two values. Here is what you can do to create the chart:

Copy the Network Usage chart to the clip board and paste it back into the report. Move it just to the right of the Network Usage chart.
Right click on the chart and select the Chart Properties command.
In the General page, change the name of the chart to MemoryUsageChart. Change the Dataset name to SqlProcessAndSystemMemory.
In the Filters page, click inside of the filter expression and then choose the Delete command. This chart doesn’t use a filter.
Click OK for the Chart Properties.
In the Chart Data control for the new chart, right click on the [series_name] group within the Series Group section and choose the Delete Series Group command.
In the Chart Data control, right click on the avg_formatted_value field in the Sum Values group and choose the Delete Series command.

At this point, you should only have the [interval_end_time] field in the Category Group section for Chart Data.

Adding the Series Values

To add the two series value expressions, you need to click on the Plus icon on the caption line as shown below and select the Expression command.

Enter in the following expression for System Memory. This expression computes the memory used by the system as the total physical memory minus the available physical memory averaged over the interval period and divides the result by 1024 to convert the value to MB.

=Avg(Fields!system_total_physical_memory_kb.Value-Fields!avg_system_available_physical_memory_kb.Value) / 1024

To complete the series definition, do the following:

Right click on the new series and select the Series Properties command.
On the Legend page, change the Custom legend text to System.
On the Border page, change the Line width value to 2pt.
Click OK to for the Series Properties dialog.

Next, we will add the SQL Server Memory used value. This comes directly from the result field named avg_sql_physical_memory_in_use_kb.Value.

Click on the Plus command for the Sum Values group like you did for the previous group and choose the Expression command.
Enter in the following expression for the series value.

=Avg(Fields!avg_sql_physical_memory_in_use_kb.Value) / 1024

Click OK for the Expression dialog.
Right click on the second series and choose the Series Properties command.
On the Legend page, change the Custom legend text to SQL Server.
In the Border page, change the Line width value to 2pt.
Click OK to for the Series Properties dialog.

Let’s clean up the chart title by replacing Network Usage to Memory Usage. Then change the vertical axis label from MB/sec to just MB.

That’s it for this post. I’ve attached the latest version of Server_Activity_Part4.rdl to as a reference.

In the next post, I’ll go over the SQL Server Waits stats chart, but with improvements to stabilize the colors and use an area chart instead of the stacked bars.

Technorati Tags: Data Collector,SQL Server 2008,SQL Server 2008 R2,SSMS Reports,MDW

Server Activity History Report–MDW Report Series Part 3

Bill Ramos — Mon, 21 Jun 2010 16:57:00 GMT

By Bill Ramos, Program Manager, SQL Server Manageability Team

In part 2, I added the hyperlink to the MDW Overview report to launch the Server Activity History report. In this post, I’ll show how to get the data and display the thumbnails in the report for %CPU. I’ll follow up post 4 to include Disk I/O Usage, and Network Usage. I’ll go through how performance counters are stored in the MDW and one of the cool stored procedures in the MDW that gets the performance data and makes it chart ready. Finally, I’ll walkthrough how to create the CPU thumbnail chart.

Getting PerfMon Data from the MDW

The Server Activity collection set is responsible for gathering the Performance Counters needed. This collection set gathers various DMVs and Performance Counters every 60 seconds and uploads the data to the MDW every 15 minutes by default. As shown below, you can view what counters the Server Activity collection set gathers by navigating to the Server Activity collection set under the Data Collection node in the Management folder in the Object Explorer and issue the Properties command.

The values for the performance counters are stored in the snapshots.performance_counter_values table. To save disk space, the counter name and object references are stored in the snapshots.performance_counter_instances table as a lookup. To get the values for an instance over the desired time period, you need to map the snapshot_id in the performance_counter_values table through the core.snapshots_internal and snapshot.source_info_internal tables. See the schema diagram below.

There is a complexity when reporting the percentage of CPU time for specific processes like SQL Server. Mainly, the value returned needs to be divided by the logical CPU count on the target instance. Needless to say, this is logic that you don’t what to include in the report definition.

Snapshots.rpt_generic_perfmon to the Rescue

I’d like to give Bart Duncan credit for coming up with idea of this stored procedure that gathers the performance counter values for the desired time period and returns a result set that makes it easy to to create charts against the data. This stored procedure is included as part of the shipping MDW and relies on the core.performance_report_group_items table for supplying the grouping information for charts used by the MDW. In a future post, I’ll show you how to report against performance counters that aren’t part of the Server Activity system collection, but that’s for another day.

Inside of the core.performance_report_group_items table, there are three groups defined for the thumbnail charts. Here is a query you can use to see the grouping for the thumbnails.

USE mdw

GO


SELECT 

       [counter_group_id]

      ,[counter_subgroup_id]

      ,[series_name]

      ,[object_name]

      ,[counter_name]

      ,[instance_name]

      ,[multiply_by]

      ,[divide_by_cpu_count]

FROM [mdw].[core].[performance_counter_report_group_items]

WHERE counter_group_id = 'ServerActivity' AND

      counter_subgroup_id IN ('cpuUsage', 'IOUsage', 'networkUsage')

ORDER BY counter_subgroup_id, series_name

When you look at the results set, you’ll see a column called multiply_by. The value of 0.0000009537 is used to convert bytes to MB (1.0 / (1024 * 1024 ) ).

The rpt_generic_perfmon stored procedure takes the following parameters.

Parameter Definition	Notes
@ServerName sysname	Instance name that maps to value from the mdw_overview report.
@EndTime datetime	UTC time for the last upload time. The default will be the UTC time for the report execution if it’s not provided.
@WindowSize int	Represents the time in minutes shown in the report. The start time for the report is @EndTime – @WindowSize. The default value is 240 (4 hrs).
@DataGroupID nvarchar(128)	Value for counter_group_id in core.performance_report_group_items. For all of the performance counters in this report, the value will be ‘ServerActivity’.
@CollectionSetUid nvarchar(64)	The GUID for the Server Activity collection set for this report – ‘49268954-4FD4-4EB6-AA04-CD59D9BB5714’
@interval_count int = 40	The number of data points to use in the chart given the @WindowSize. The default value is 40.

Building Out the Server Activity Report

Now it’s time to get down to business. Open up the Server_Activity report using the Report Builder that we created in part 2 which us essentially a blank report with two parameters: ServerName and EndTime.

Report Parameters

ServerName. For testing purposes, I use the name of one of the server instances that reports into the MDW. The attached report will have blank value.

EndTime. To accommodate running the Server Activity report directly, we’ll add a default value to the EndTime parameter by going to the parameter dialog for EndTime, go to the Default Values page, use the Specify values option, and press the Add button. Paste the following expression in to the value that retrieves the current date in UTC format:

=DateTime.UtcNow

WindowSize. Next, we’ll create the WindowSize parameter using the Add Parameter command. Cal the name WindowSize and use Integer for the data type. Go to the Available Values page and select the Specify values option. You’ll add 5 values as follows:

Label	Value
15 minutes	15
1 hour	60
4 hours	240
12 hours	720
24 hours	1440

For the default value, specify a the value of 240.

The remaining three report parameters will be setup has hidden parameters. We need to use parameters instead of the new Report Variable feature starting with Reporting Services 2008 because query parameters can only reference report parameters.

Parameter Name	Data type	Visibility	Default Value
DataGroupID	Text	Hidden	ServerActivity
CollectionSetUid	Text	Hidden	49268954-4FD4-4EB6-AA04-CD59D9BB5714
interval_count	Int	Hidden	40

Data Source

Like the MDW_Overview report, you will create a data source called MDW and point it to the shared MDW data source that you created in part 1.

Datasets

The Server Activity report will have one dataset – for now – called Perfmon that will be associated with the snapshots.rpt_generic_perfmon stored procedure. As shown below.

If you had no typos with the parameter names, Report Builder 3.0 matches each of the stored procedure parameters with the report parameter names, so it’s ready to use for your report objects.

CPU Chart

Now for some charting. We’ll start with the Chart Wizard, select the PerfMon dataset, and press Next.

Choose the Line and click Next.

Drag the avg_formatted_value field into the Values box. This will be the value in the Y axis value represented as a percentage value from 0 to 100 for the CPU.

Drag the interval_end_time into the Categories box. This represents the number of interval_count values along the X axis.

Drag the series_name field into the Series box. There are two series values: SQL Server and System.

Click Next to go to the style page. Pick the style that suits your taste and click Finish to complete the wizard. I’ll use Generic for the style. If all went well, you should see a chart that looks like this:

Before attempting to run the report – it’s tempting – there are a bunch of tweaks we need to make to the chart.

Change filters. We need to add a filter to the chart that shows only the counter_subgroup_id = cpuUsage. Right click on the Chart and choose Chart properties. Go to the Filters page and click Add. For Expression, select [counter_subgroup_id], Operator is =, and Value is cpuUsage.

Horizontal Axis Properties. We need to set the minimum and maximum values based on the WindowSize and EndTime. Right click on horizontal labels along the X-axis for the chart and choose the Horizontal Axis Properties command. Complete the dialog as follows:

Choose the Scalar for Axis type. This allows the entire range of numbers to get charted out that match the WindowSize. If you forget to do this, in cases of missing values for the WindowSize (collector was turned off or instance was down), the chart will look look like you have data because it’s charting the discrete values over the range.

Setting the Axis range and interval. For the Minimum expression, we’ll use the following expression:

=DateAdd ("n", -1 * Parameters!WindowSize.Value, Parameters!EndTime.Value )

This sets the start range to be the EndTime – WindowSize. For the Maximum value, set the it to: [@EndTime].

Labels. On the Labels page, you want to check on Hide axis labels. There is no room in the thumbnail chart to show labels for the 40 data points.

Major Tick Marks. On the Major Tick Marks page, check on the option to Hide Major tick marks. This just adds noise to the chart.

After you click OK, you then need to select the Axis Title label under the horizontal axis and delete it. This is because we want to use this space for the Series labels.

Vertical Axis Properties. Settings for the Y-axis are a little simpler since we are dealing with a percentage value. Set the minimum value = 0 and the maximum value = 100. We’ll leave major tick marks on and show labels. For the Axis Title, change the text to ‘% CPU’.

Series Legend. To move the series legend to the bottom of the chart, right click on the legend and select Legend Properties. Then, choose the Legend position as shown below.

Finally, you will want to change the Chart Title to % CPU. Your design surface should look like this:

You can now Run the report and if you did everything correctly and your data collector was collecting data and uploading, you should see something like this.

Let’s add one more label on the report to display the report range of data in local time – just like the shipping report.

Switch back to Design mode.

Use the Insert menu and select a Text Box and drag out a text box just under the report title for the length of the report.

Click the Center paragraph command.

Paste in in the following expression:

= "Selected time range: " & 
    code.UTCToLocal( DateAdd("n", -1 * Parameters!WindowSize.Value, Parameters!EndTime.Value)) & 
   " to " &
    code.UTCToLocal( Parameters!EndTime.Value)

When you click outside of the text box, the expression text gets replaced with the Expr tag.

This post is getting pretty long, so it’s time to stop here. You should now know how performance counters are stored in the MDW; how to use the snapshots.rpt_generic_perfmon stored procedure to retrieve the data and the role of the core.performance_co8unter_report_group_items table; and, how to create a thumbnail chart to display the cpuUsage.

In the attached ZIP file, I’ve included the mdw_overview.rdl and the new server_activity.rdl files. If you are enjoying this series and find it useful, feel free to add your comments.

Technorati Tags: Data Collector,SQL Server 2008,SQL Server 2008 R2,SSMS Reports,MDW

MDW Overview Report for Data Collector (Server Activity) – MDW Report Series Part 2

Bill Ramos — Sun, 20 Jun 2010 03:23:00 GMT

By Bill Ramos, Program Manager, SQL Server Manageability Team

In this post, we’ll complete the MDW Overview report from part 1 of this series and then start into the makings of the Server Activity report as shown below.

The Server Activity report has 4 major sections that I’ll go through that includes the timeline; server thumbnails; SQL Server waits, and SQL Server activity. It’s going to take several blog postings to get though this report.

Adding Hyperlink to Server Activity Upload Time

Back to the completing the MDW Overview. The next step is to add hyperlinks to the collection set upload times that will launch the Server Activity report. The example below shows where I left off in part 1.

Creating the Blank Server Activity Report

The Server Activity report needs two key parameters:

ServerName (type Text) – Name of the server name \ instance reporting into the MDW.
EndTime (type Date/Time) – Interval End Time (UTC) for the report to display.

There are several other hidden parameters that I’ll get into as we build up the Server Activity report. Go ahead and create a new blank report in the same folder as the mdw_overview.rdl file and add two parameters. While you are at it, go ahead and add the UTCtoLocal function to the Report Code property from part 1 (see below) and then save the report as Server_Activity.rdl.

Public Shared Function UTCtoLocal(ByVal UTCValue) As String

' Get the local time zone and a base Coordinated Universal 

' Time (UTC).

Dim localZone As TimeZone = TimeZone.CurrentTimeZone

If IsNothing(UTCValue) Or (Not IsDate(UTCValue)) Then

   Return Nothing

Else

   ' Calculate the local time and UTC offset.

   Dim localTime As DateTime = localZone.ToLocalTime(UTCValue)

   Dim localOffset As TimeSpan = localZone.GetUtcOffset(localTime)

   Return localTime.ToString()

End If

End Function

Adding the Hyperlink Action

Now, go back to the mdw_overview report and right click on the Server Activity <<expr>> text box that displays the last download time and select the Text Box Properties… command. You’ll navigate to the Action page and choose the Go to report option, specify the Server_Activity report, and add the two parameters as shown below.

You can now test out the link by running the mdw_overview report within the designer and then click on the Server Activity upload time. You should see blank Server_Activity report with the parameter values as shown below.

At this point, you can save your mdw_overview report and shut it down. In future posts, we’ll hook up Query Statistics and Disk Usage reports.

It’s time to take a break and get this post uploaded. I spend the last week picking apart the Server Activity data sources and report code and it looks like several posts will be needed to go through the report. Tomorrow, I’ll go over the report parameters and get into the details of creating the CPU thumbnail. No attachment in this post.

Technorati Tags: Data Collector,SQL Server 2008,SQL Server 2008 R2,SSMS Reports,MDW

MDW Overview Report for Data Collector – MDW Report Series Part 1

Bill Ramos — Mon, 07 Jun 2010 02:53:00 GMT

By Bill Ramos, Program Manager, SQL Server Manageability Team

The Management Data Warehouse (MDW) overview report that I mentioned in my previous blog post on Viewing MDW Reports with a Low Privileged User Account provides a list of database instances and the last collection set upload times for the MDW database. Below is an example of the report when run inside of SSMS.

By clicking on the last upload hyperlink, SSMS launches the report displaying the Server Activity, Query Statistics, or Disk Usage for the instance.

The question that many people have is – how can I run these reports from a Reporting Services server rather than from SSMS? The short answer is that you can’t. However, by understanding the schema of the MDW database and the ways that the reports extract data from the MDW through various stored procedures, you can create your own reports to get data from the MDW.

Unfortunately, there is little documentation on the reports and especially the stored procedures used to access data. In this series of blog posts, I’m going to run through the various reports that SSMS provides for analyzing performance that is collected in the MDW database and how you can create similar reports.

I wish I could simply provide the report source code as part of a codeplex project, but the source code references SSMS based assemblies and uses resourced strings that may it very difficult to modify and use. Instead, I’ll provide RDL files compatible with Reporting Server’s Report Builder 3.0 that ships with SQL Server 2008 R2. I’ll assume that you have a Reporting Server already setup as part of the series. I’ll also assume that you have an MDW database already setup and your collecting performance data through the Data Collector feature that we shipped with SQL Server 2008. See Getting Started with the Data Collector. I’m also going to assume that you are building the reports directly against the Reporting Server using Report Builder 3.0 using Windows security.

MDW Overview Report

The MDW Overview report is the natural starting point since it is the main launch point for all the MDW reports. Here is an example of what the end result looks like running from Reporting Server.

Connecting to the MDW

The first step is setting up shared data that the reports will use when connecting to the MDW database. Here is an example of one I created in the root folder for the my Report Server.

The MDW I’m referencing is sysutility_mdw. This is the same MDW file that is created when you create a Utility Control Point with SQL Server 2008 R2, but it can be any MDW created using the Configure Management Data Warehouse wizard.

Starting with a Blank Report, you need to right click in the Data Sources node in the Report Data pane and create a new data source pointing to the shared connection as shown below.

Dataset for Instances

To get the instances that have uploads stored in the MDW along with the last collection upload time, you can use the snapshots.rpt_list_all_servers stored procedure. Below, I show creating a dataset named MdwServerList that calls the stored procedure.

If you right click on the new MdwServerList, issue the Query command, and then run the query, you’ll see one row per instance that is uploading to your shared MDW database as shown below.

The snapshots.rpt_list_all_servers gets its data from the core.snapshots view in the MDW. Feel free to script out the stored procedure to see how it retrieves the data. There is a comment in the procedure pointing out where you should add new collection sets to the result set.

I should point out that the resulting last upload times are represented as a “Coordinated Universal Time” (UTC) that will need to be converted to local time for the report. To convert UTC to local time, I’m going to use a function called UTCtoLocal that will go into the Custom Code section for the Report Properties. Right click on the report background and issue the Report Properties command and then go to the Code page in the dialog and paste the following code snippet into the Custom Code area.

Public Shared Function UTCtoLocal(ByVal UTCValue) As String
' Get the local time zone and a base Coordinated Universal
' Time (UTC).
Dim localZone As TimeZone = TimeZone.CurrentTimeZone
If IsNothing(UTCValue) Or (Not IsDate(UTCValue)) Then
   Return Nothing
Else
   ' Calculate the local time and UTC offset.
   Dim localTime As DateTime = localZone.ToLocalTime(UTCValue)
   Dim localOffset As TimeSpan = localZone.GetUtcOffset(localTime)
   Return localTime.ToString()
End If
End Function

I’ll use this function later on once we’ve created the table showing the results.

Table for the Results

To create the table for displaying the results, I’ll walk you through the steps using the Table Wizard. First choose the MdwServerList dataset.

Next, specify the Row groups and Values.

Next, choose the layout.

Finally, choose a style and finish the wizard.

The end result is a table that displays the instance name and the three last upload times for server activity, query statistics, and disk usage collection sets.

As I stated earlier, if you were to run the report, the date and times reported will be UTC. Let’s fix up the server_activity_last_upload field by using the UTCtoLocal function. Right click in the [server_activity_last_upload] cell in the table and choose the Expression command.

In the Set expression for: Value text box, paste in the following code and click Ok:

=IIf (IsNothing(Fields!server_activity_last_upload.Value),
"No Data Available",
code.UTCtoLocal( Fields!server_activity_last_upload.Value )
)

You’ll want to add similar expressions for Fields!query_statistics_last_upload.Value and Fields!disk_usage_last_upload.Value.

Next, you’ll want to clean up the report, add titles and explanation text similar to the shipping version of the MDW Overview report as shown below.

The <<Expr>> below the title displays the run time for the report using the following expression:

="at: " & FormatDateTime(Globals!ExecutionTime)

Add Column Sorting

The shipping version of the report includes column sort controls as shown below.

To add sorting for Instance Name, right click in the Instance Name column header cell and choose the Text Box Properties command. Then complete the Interactive Sorting page as shown below.

For Server Activity, you’ll do the same thing, but choose [server_activity_last_upload] for the Sort by field. The same goes for Query Statistics and Disk usage column headings.

Adding the MDW Name to the Report Title

The last step is adding the database name to the report title. In the SSMS version of the report, the MDW database name is passed in as a parameter to the report and its simply appended to the title string. To get the database name for this report, we need to create a dataset that returns the name. Just create a dataset that looks like the one below.

Then change the expression for report title text box to the following expression:

= "Management Data Warehouse Overview: " & First(Fields!database_name.Value, "database_name")

Almost Done, But Not Quite

If you followed along, you now have an RDL file that runs on your Reporting Server that displays the MDW Overview of instances being collected using the Data Collector. I’ve attached a copy if my version of mdw_overview.rdl as a reference, but I encourage you to follow along.

The next step in the process is to add hyperlinks to collection upload time cells that drill into the detail reports for Server Activity, Query Statistics, and Disk Usage. But, that’s for another blog post.

Acknowledgements

I’d like to acknowledge Jin Chen from MSFT, who posted the source for UTCtoLocal on the MSDN forum.

I’d also like to acknowledge Bart Duncan who is a developer on our team for pointing me to the source files for the MDW reports and the little secrets on how they work.

Technorati Tags: Data Collector,SQL Server 2008 Express,SQL Server 2008 R2,MDW,Management Data Warehouse,SSMS,SSMS Reports

Viewing MDW Reports with a Low Privileged User Account

Bill Ramos — Thu, 13 May 2010 03:18:52 GMT

Just last week, one of our technical sales people asked me the question, when will we release the source RDL files for the Management Data Warehouse (MDW) feature that we shipped with SQL Server 2008. The reason that he asked was that his customer wanted to host the reports on a Reporting Services server so that a low privileged user could view the reports against an MDW shared database hosted on a shared server without actually having to connect to the instance being collected.

I told him, wait – we have a feature that allows you to run the MDW reports on the shared server by opening up the shared server instance in SSMS and then right clicking on the MDW database and selecting the Reports > Management Data Warehouse Overview report. It should be as simple as simply adding the low privileged login account to the mdw_reader role on the MDW database. If you go to the MDSN help topic on Data Collector Security, you’ll see that:

“Members of the mdw_reader role have Read access to the management data warehouse. Because the purpose of this role is to support troubleshooting by providing access to historical data, members of this role cannot view other elements of the management data warehouse schema.”

Great, it’s natural to assume that that’s just what I need to do to run reports. Reports are technically read-only and use the MDW database as it’s source. Well, not exactly.

When I setup my test ‘mdw_user’ SQL login account as a user to the MDW database that I was using – in this case – the new SQL Server 2008 R2 ‘sysutility_mdw’, the first thing I noticed is that the Management Data Warehouse Overview report command was missing! It turns out that the Object Explorer (OE) queries the database for an extended property called ‘Microsoft_DataCollector_MDW_Version’. If OE finds it, it displays the overview report command. It turns out that in order for a user to see extended properties on a database, the need to have VIEW DEFINITION rights. Needless to say, we forgot to add that right to the MDW installation script. The simple workaround is to grant those rights to the mdw_reader role as follows:

-- Allow the mdw_reader and mdw_admin roles to see the MDW overview report in OE
use [sysutility_mdw]
GO
GRANT VIEW DEFINITION TO [mdw_reader]
GO
GRANT VIEW DEFINITION TO [mdw_admin]
GO

Now, you can see the overview report command and run it as shown below.

The Overview report looks like this for my setup with two instances uploading data to the shared sysutility_mdw database.

With the overview report, you get a summary of the last upload times for the three system collection sets for each instance that has uploaded to the MDW. From this point, you can click on the hyperlink for one of the collection sets to view the report. This way you don’t actually have to connect to the target server just to see the same reports that you could run from the Data Collection node.

Well, if you do attempt to run any of the reports with the low privileged user, you are going to end up with one of a couple of insufficient privileges' error message.

This is due to the fact that all of the MDW reports in the footer indicate the state of the data collector so that you can tell if things are running ok. Unfortunately, the queries to see if the data collector are running reply on having access to three views in the system msdb database.

There are two basic workarounds for this. The first workaround is to make the low privileged use a member of either three of the existing dc_admin, dc_operator, or dc_proxy database roles in msdb. The problem is that these roles have more privileges than needed to run the reports. The second workaround is to created a new role in msdb that I’ll call ‘dc_report_reader’ and then add the user to that role. Here is the script that I used.

-- Add a new msdb role for the dc_report_reader. In the future, the MDW wizard would add the login to this new role
-- for any login associated with the mdw_reader, and mdw_admin roles so that they can run reports.
USE [msdb]
GO

CREATE ROLE [dc_report_reader] AUTHORIZATION [dbo]
GO

GRANT SELECT ON [dbo].[syscollector_collection_sets] TO [dc_report_reader]
GO

GRANT SELECT ON [dbo].[syscollector_execution_log] TO [dc_report_reader]
GO

GRANT SELECT ON [dbo].[syscollector_config_store] TO [dc_report_reader]
GO

-- For each mdw_reader like user, you need to add them to the dc_report_reader role in MSDB
USE [msdb]
GO

CREATE USER [mdw_user] FOR LOGIN [mdw_user]
GO

EXEC sp_addrolemember N'dc_report_reader', N'mdw_user'
GO

Once you run the script for your low privileged user, you can now use all of the canned MDW reports. I should point out that you also need to do the same with the mdw_admin role. It turns out this role has the same limitations as the mdw_reader role.

The workaround is fairly straight forward and I’ve added the same script to the end of the Data Collector Security help topic as Community Content. If you would like to see this fixed in a future service pack of SQL Server, you can also vote for this item on the SQL Server Connect site at http://connect.microsoft.com/SQLServer/feedback/details/558417/logins-with-the-mdw-reader-role-are-unable-to-view-the-mdw-reports-from-the-management-data-warehouse-report.

As far as the original question about making the MDW reports available via CodePlex, we aren’t ready to do that yet. However, I’m planning to put together a series of blog posts that explore each of the reports and the way they get their data from the MDW over the next couple of months.

In my next post, I’ll start simple and talk about the MDW overview report.

Technorati Tags: SSMS,Data Collector,Management Data Warehouse

New Manageability White Papers for SQL Server 2008

Bill Ramos — Fri, 12 Jun 2009 22:48:17 GMT

TechNet Books Online just published three whitepapers - check them out!

SQL Server 2008 Management Data Warehouse: http://msdn.microsoft.com/en-us/library/dd939169.aspx
SQL Server 2008 Policy-Based Management: http://msdn.microsoft.com/en-us/library/dd938891.aspx
Understanding and Using PowerShell Support in SQL Server 2008: http://msdn.microsoft.com/en-us/library/dd938892.aspx

DAT315 – Session Review for SSMS Tips & Tricks Session

Bill Ramos — Fri, 15 May 2009 19:37:41 GMT

One of the attendees of my TechEd session “DAT315 - Manageability Series: Uncover Hidden Secrets of T-SQL Scripts with Microsoft SQL Server Management Studio” suggested that I post the list of demos that I did for the session so that they could remember the ah-ha moments. Here we go.

Object Explorer Details (OED)

The property pane below the list of objects can be expanded to show more properties.
Right click on the column header in OED to add columns. Columns can be sorted. Settings stored for each object type.
When you copy selected rows, the headers are included. the example was to sort tables by row count, copy the top 10 items, paste in Excel. In Excel, press Alt+F1 to chart the result.
Navigate as you type works in OED based on the sorted column. The same works in Object Explorer – just need to include the schema name. Use the Filter command to focus on a set of objects you are working with.
Use the Search command in the toolbar area of OED to look for object names. If you are in a database, search looks for all objects with the matching name. Use LIKE expressions. If you start the search at the server level, search works across all databases. The Stop command halts the search.

Shell Tips

Customize the toolbar through Tools | Customize or right click on toolbar and choose Customize command. Move toolbar buttons like Debug, Stop, and Parse to the end of your toolbar so that you don’t click them by accident.
While in customize, take commands like Edit.SelectAll, and Edit.Delete and drag them on to the toolbar to duplicate the old Query Analyzer Erase button.
Showed off the Go to Find Combo box for Find, Help, and Commands.
Use Dynamic Help command to show help for commands based on keywords the cursor is on with the T-SQL editor window.

Solutions and Projects

Create Projects for your scripts. You can use multiple projects per solution to organize your work.
Set the Connect for your project to use as the default connection for scripts in your project.
Bookmarks allow you to save positions within your scripts for marking TODO areas or places of interest. You can create folders to organize your bookmarks within your solution. Use the Edit | Bookmark command set to see the keystrokes. Bookmarks are saved with the solution.
Use Templates to store your standard scripts of interest. Each user has their own folder that you could push changes to using SMS. Use the Ctrl+Shirt+M command to replace template parameters that look like:
<object_name, datatype, default_value>
Showed how you can change the default template for the New Stored Procedure command by going into the Program Files directory C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\Stored Procedure.
Modify the SQLFile.sql file in C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql to add text to New Query windows.

Editor Tips

Change the color of the Editor status bar to help prevent you from executing the wrong query against a production server. Use the connection properties for Registered Servers, or the Connection properties in the Connection dialog for OE or Editor windows to change the color.
Use Tools | Options for Fonts & Colors to change the size of your tooltip. You can also change the size of the results in grids, etc.
To Block Select text, use Alt+Mouse moving. With the keyboard, use Shift+Alt+ArrowKeys
Delete leading Whitespace for a line of text or selected lines of text with Ctrl+K, Ctrl+\ combo.
Use the spitter control at the upper right corner of the text editor window to split your file just like MS Word. You can also use the Window | Split command to do the same. Window | Remove Split to do just as the command states.
Easily add a new line below the current line – regardless of cursor position in the line using Ctrl+Shift+Enter. Add a line above the current one using Ctrl+Enter.
Move a line down using Alt+Shift+T. Sorry – no command to move the line up.
Using Ctrl+I to incrementally search for text in the editor. It finds as you type. When you found what you are looking for, use F3 to Find Next and Sift+F3 to find previous. Search term goes into the Go to Find combo.
If you collapse regions in you text and can’t find it using the Find command, make sure you set the search option Find in Hidden text.
Use Ctrl+J with intellisense to replace one column with another. Use object aliases to make finding items easier.
You can set options like – turn off Intellisense when working against a stressed production server – by using the Tools | options setting for the T-SQL Editor.

Debugging

Demonstrated the 3 part series that I blogged about. Start with “Basic Debugging”

SSMS Start-Up Options and Activity Monitor

Demonstrated how to change your Startup options to launch Activity Monitor each time you start SSMS.
Demonstrated how to use Activity Monitor to track down queries with missing indexes. See blog entry “Not more excuses of missing indexes with Activity Monitor in SQL Server 2008”
Demonstrated how to use Activity Monitor to track down blocking queries.

Multi-Server Query Execution

From registered servers, run queries against any folder. Status bar is in a different color and shows how many servers will execute the query.
Configure output options to include Server name and Login name in the results using Tools | Options for Multi-Server queries.
Demonstrated using the template file by dragging the ChangeSAPassword template into a blank multi-server query editor. Here is the script example.

-- Change SA password across servers
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD='<Password_String, sysname, SAPassWord>';
GO
SELECT name, sid, modify_date
FROM sys.sql_logins
WHERE name = 'sa';
GO

Use the Ctrl+Shift+M key to replace the SA password.

Thank you everyone who attended the session.

Technorati Tags: SQL Server,TechEd,SSMS

PowerShell Script Examples from DAT314

Bill Ramos — Thu, 14 May 2009 05:36:42 GMT

I’d like to thank everyone who attended my TechEd session today titled “Manageability Series: Microsoft SQL Server Automation on Steroids, Including PowerShell Support”. Sean McCown – SQL MVP – and all around DBA – creator of the Midnight DBA videos available at: http://midnightdba.itbookworm.com/ – was kind enough to stop by after the session to point out how the SQLPS shell doesn’t support plug-ins. Chad Miller’s SQLPSX library of useful scripts for working with SQL Server will work with the SQLPS shell – you just might not get all the functionality. Your best bet is to configure the standard PS shell as Michiel Wories describes in his blog post at: “SQL Server PowerShell is Here!”.

Here is a list of the PowerShell script examples that I demonstrated in my session that Buck Woody provided to me.

# TechEd PowerShell Overview examples for DAT314
# Originally from Buck Woody http://blogs.msdn.com/buckwoody/default.aspx

# Show Databases not backed up in a day
DIR "SQLSERVER:\SQL\SQL1\SQL2K8\Databases" |
where-Object {((get-date)-($_.LastBackupDate)).days -gt 1} |
sort-Object -Property LastBackupDate |
select-Object Name, RecoveryModel, LastBackupDate

# Server Discovery
$machine_name = "SQL1"
get-wmiobject -class win32_service -computer $machine_name |
where { $_.name -like 'MSSQL*' -and $_.Description -like '*transaction*'} |
select name |
ConvertTo-HTML -title "Services" -head "<link rel='stylesheet' href='styles.css' type='text/css' />" |
Out-File C:\ServiceNames.html

# Show all services and their states using WMI
$strComputer = "."
$tab = [char]9
$colItems = get-wmiobject -class "Win32_Service" -namespace "root\cimv2" -computername $strComputer | sort "State"
foreach ($objItem in $colItems) {write-host $objItem.State $tab $objItem.Name}

#Write to the log:
$log = New-Object System.Diagnostics.EventLog
$log_type = "Application"
$source = "ApplicationName"
$message = "Message To Enter Go TechEd"
$eventid = 12347
$type = "Information"
$log.set_log($log_type)
$log.set_source($source)
$log.WriteEntry($message,$type,$eventid)
#Read the log:
get-eventlog application | Where-Object {$_.EventID -eq 12347}
get-eventlog application | Where-Object {$_.source -eq "ApplicationName"}
#Clear the event log
Get-EventLog -list | % {$_.Clear()}

# Accout Group names using WMI
$groups = Get-WmiObject –computerName SQL1 Win32_Group
$groups | %{$_.Name}

# Simple Yes and No
$s = 'Welcome to Powershell - You Like?'
[System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
$d = [Windows.Forms.MessageBox]::Show($s, "Windows Form", [Windows.Forms.MessageBoxButtons]::YesNo, [Windows.Forms.MessageBoxIcon]::Question)
$d -eq [Windows.Forms.DialogResult]::Yes

#--------------------------------------------
# SQLPSX - SQL Server PowerShell Extensions
# By Chad Miller
# http://sqlpsx.codeplex.com/
# Set of useful CMDLETs that use SMO objects
#--------------------------------------------
# View some of the commands
Get-Command *et-Sql* | Select Name

# With SQLPSX
$server = Get-SqlServer 'SQL1\DEMO1'

# With SMO
#Connect and run a command using SMO
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") "SQL1\DEMO1"

# Get server processes
$server.EnumProcesses() | Format-Table

$db = Get-SqlDatabase 'SQL1\SQL2K8' AdventureWorks
Get-SqlUser $db | Format-Table

Get-SqlLogin $server | select members

Technorati Tags: SQL Server,TechEd,PowerShell

Welcome to TechEd 2009

Bill Ramos — Mon, 11 May 2009 06:37:18 GMT

This is my first TechEd in a long time. The last time I spoke at a TechEd was back in 1997 in Japan! I have three sessions this Tuesday, Wednesday, and Thursday that talk about how to get the most out of our manageability suite of tools in SSMS.

If you happen to be at TechEd this year, stop by the Technical Learning Center at the DAT area and say hello. I’ll be on Twitter as well @billramo talking about #teched.

Technorati Tags: TechEd

SQLPSX from Chad Miller

Bill Ramos — Thu, 07 May 2009 06:19:10 GMT

I’ve been playing around Chad Miller’s SQL Server PowerShell Extensions – SQLPSX 1.5 on CodePlex getting ready for my TechEd session Manageability Series: Microsoft SQL Server Automation on Steroids, Including Powershell on Wed 5/13.

Chad has developed huge library of Powershell cmdlets, functions, and scripts that hit the key scenarios for SQL Server DBAs. All of the functions that Chad developed can be accomplished using the SMO object model calls, but Chad’s functions exposes them like you would expect to see them in PowerShell.

For example, once you install his library along with using either the SQL PowerShell shell or a PowerShell 1.0 shell referencing the SQL snapins – see Running SQL Server PowerShell – you can mix and match functionality.

For example, take his example of running DBCC CheckDB for a database.

#Get a database object and set to a variable
$db = get-sqldatabase 'SQL1\DEMO1' pubs

#Run a checkdatabse
invoke-sqldatabasecheck $db

That’s all it takes – no need to use the SMO object model. You can use a command like this to run DBCC CheckDB against all databases:

cd SQLSERVER:\SQL\SQL1\DEMO1\Databases
dir | invoke-sqldatabasecheck

This is all very cool and something that I’ll be presenting in my TechEd session. Be sure to check out SQLPSX and Chad’s excellent blog.

Technorati Tags: SQL Server,PowerShell,TechEd

Transact-SQL Debugger for SQL Server 2008 – Part 3

Bill Ramos — Sun, 12 Apr 2009 22:39:43 GMT

In part 3 of working with the debugger, I’ll talk about how to set breakpoints and the trick to setting them in stored procedures and triggers.

This 3 part series covers:

Basic debugging
Configure remote debugging & best practices
Debugging triggers and stored procedures with breakpoints

The trick with setting breakpoints in called stored procedures and triggers is that they need to be set within the temporary file that the debugger creates for the object.

In this scenario, I’m going to call the HumanResources.uspUpdateEmployeeHireInfo stored procedure in the 2005 version of AdventureWorks. This stored procedure updates two tables: Employee and EmployPayHistory both in the HumanResources schema. The goal of the example is to set a breakpoint in the trigger for the Employee table and then show how the debugger catches it the next time through.

The obvious thing that you would try is to Modify the trigger and then toggling the breakpoint [F9] on the line you want to stop at. The problem is that the debugger has no context. so the approach shown below won’t work.

What you need to do is step into the stored procedure or trigger you want to break on and set the breakpoint in the temp file the debugger creates.

Let’s step through the example starting with the script to call the stored procedure.

I’ve toggled the breakpoint to demonstrate how breakpoints can be set in a script of batch statements. When U press [ALT]+[F5] twice to start and continue debugging, you’ll see the debug stops at the break point in the script.

You will now want to Step Into the stored procedure with [F11]. The debugger loads up the stored procedure into a new editor window and stops at the first executable line.

Notice in the Call Stack window that procedure name is followed by (SQL1\SQL2K8.AdventureWorks) indicating the server instance and database name context for the debugging session. If you hover over the statement, you will see a tool top showing the parameters with values called and the current line.

Step into [F11] again moves to the BEGIN TRY statement. [F11] again to the BEGIN TRANSACTION statement. One more [F11] positions you on the UPDATE statement for the Employee table.

Step into [F11] the UPDATE statement causes the debugger to load the update trigger for the Employee table.

At this point, we can now set a breakpoint [F9] on the UPDATE statement for the trigger.

In the breakpoint window for this example, you’ll notice something interesting.

You’ll see that the debugger is actually showing the object_id for the trigger name. This way the the debugger can keep track of the breakpoint for future sessions.

For now, lets press [ALT]+[F5] to let the debugger continue. Lets test out the breakpoint for the trigger.

First toggle off the breakpoint [F9] in line 11 of the calling script. Then press [ALT]+[F5] to start the debugger. You’ll see that the breakpoint is still present in the Breakpoint Window.

One more continue [ALT]+[F5] stops right where you want!

Let’s finish the debug session with the Continue command [ALT]+[F5].

At this point, I should point out that breakpoints are persisted with the database solution/project. If you didn’t have solution, any breakpoints you defined will go away when you close SSMS. To learn more about solutions, you can refer to the help topic – Using Solution Explorer.

Technorati Tags: SQL Server Mangement Studio,SQL Server 2000,Transact-SQL Debugger

Transact-SQL Debugger for SQL Server 2008 – Part 2

Bill Ramos — Sun, 12 Apr 2009 07:02:13 GMT

This blog will go over what you need to know for setting up your environment for remote debugging along with best practices and other things to consider when using the debugger in SSMS.

This 3 part series covers:

Basic debugging
Configure remote debugging & best practices
Debugging triggers and stored procedures with breakpoints

The debugger consists of 2 components. The server component is installed with the sqlservr.exe. The client component is installed as part of SSMS. When SSMS and the instance you want to debug are on the same system, there is nothing configure. You do however need to login to the instance as a member of the sysadmin role.

If you need to debug a remote instance, you need to enable the program and port exceptions using the Windows Firewall Control Panel application on both computers.

On the computer that is running the instance of the Database Engine, in Windows Firewall, specify the following information:

Add TCP port 135 to the exceptions list.

Add the program sqlservr.exe to the exceptions list. By default, sqlservr.exe is installed in C:\Program Files\Microsoft SQL Server\MSSQL10.InstanceName\MSSQL\Binn, where InstanceName is MSSQLSERVER for the default instance, and the instance name for any named instance.

If the domain policy requires network communications to be done through IPsec, you must also add UDP port 4500 and UDP port 500 to the exception list.

With the server instance taken care of, you now need to take care of SSMS on the client.

On the computer that is running SQL Server Management Studio, in Windows Firewall, specify the following information:

Add TCP port 135 to the exceptions list.
Add program ssms.exe (SQL Server Management Studio) to the exceptions list. By default, ssms.exe is installed in C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE.

You are now ready to launch SSMS for remote debugging.

It is highly recommended that Transact-SQL code be debugged on a test server, not a production server, for the following reasons:

Debugging is a highly privileged operation. Therefore, only members of the sysadmin fixed server role are allowed to debug in SQL Server.
Debugging sessions often run for long periods of time while you investigate the operations of several Transact-SQL statements. Locks, such as update locks, that are acquired by the session might be held for extended periods, until the session is ended or the transaction committed or rolled back.

That’s it for part 2.

Technorati Tags: SQL Server management Studio,SQL Server 2008,Transact-SQL Debugger

Transact-SQL Debugger for SQL Server 2008 – Part 1

Bill Ramos — Sun, 12 Apr 2009 02:22:16 GMT

In my next series of blog posts, I’ll explore the Transact-SQL Debugger for SQL Server 2008. This feature only works against database instances of SQL Server 2008. If you need to use a debugger for SQL Server 2005, you’ll have to use the Visual Studio Professional SKU or better. Please refer to the MSDN topic on How to: Enable SQL Server 2005 Debugging.

This series will cover:

Basic debugging
Configure remote debugging & best practices
Debugging triggers and stored procedures with breakpoints

The Transact-SQL debugger in SQL Server Management Studio enables you to find errors in Transact-SQL scripts, stored procedures, triggers, and functions by observing their run-time behavior. You can start the debugger when you are using the Database Engine Query Editor window. By using the Transact-SQL debugger, you can do the following:

Step through the Transact-SQL statements in the editor line by line, or set breakpoints to stop at specific lines.
Step into or over Transact-SQL stored procedures, functions, or triggers that are run by the code in the editor window.
Watch the values that are assigned to variables, and observe system objects such as the call stack and threads.

The examples that I’m going to show are based on the SQL Server 2005 version of AdventureWorks that can be downloaded here. My demo for this post assumes that SSMS in debugging and instance on the same machine. I’ll cover remote debugging in Part 2. If you need to deal with remote debugging and can’t wait – check out the help topic on Configuring and Starting the Transact-SQL Debugger.

To kick things off, open the script file that you want to debug and then click on the green debug toolbar button or press [ALT]-[F5].

SSMS launches into a Debugging session by clearing aside may of your tool windows, opens the debugger specific tool windows and debugger toolbar, and displays a Yellow arrow indicating the next statement to execute.

To step through statements like two set statements in this example, press the F11 key twice. You’ll notice that the Locals is now populated with the two variables.

The next F11 action then steps into the stored procedure. If you press the Step-Over [F10], you would go right over the stored procedure and in this case – end the debugging session.

The debugger opens a special editor window for the stored procedure that you just stepped into along with hints that you don’t really want to make edits to this file.

The other thing you’ll notice is the Locals window now shows the value for the SP parameters and the Call Stack window is updated to show that you are now in the SP.

The Locals window allows you to edit values so that you can change scenarios inside of the debug session. You can use the mouse or the [CTRL]+[ALT]+[V], [L] command to navigate to the Locals window. This is a little different pattern of calling up debugger windows like [CTRL]+[ALT]+[C] for the Call Stack window because [CTRL]+[ALT]+[L] was already taken for displaying the Solution Explorer. Back to the task – now double click on the value 819 for @StartProductId and then type in 820 and press [ENTER]. You’ll see that the value changes color to Red meaning it’s been modified.

We could continue to step thru the procedure, but since this procedure only has one more command, we can press the Step Out [SHIFT]+[F11] command to complete execution in the stored procedure.

If you had additional statements in the original script DebugSPExample.sql script like this:

The debugger would set focus to the editor window and indicate in the status bar “Debugging query” to remind you that you are still debugging.

If you press [ALT]+[F5], you would complete the debugging session for this demo.

Here are the keystrokes for the debugger with the Standard keyboard setting.

Action	Standard
Start or continue debugging	ALT+F5
Stop debugging	SHIFT+F5
Step into	F11
Step over	F10
Step out	SHIFT+F11
Implement the Run To Cursor command	CTRL+F10
Display the QuickWatch dialog box	CTRL+ALT+Q
Toggle breakpoint	F9
Delete all breakpoints	CTRL+SHIFT+F9
Display the Breakpoints window	CTRL+ALT+B
Break all	CTRL+ALT+BREAK
Display the Watch 1 window	CTRL+ALT+W, 1
Display the Watch 2 window	CTRL+ALT+W, 2
Display the Watch 3 window	CTRL+ALT+W, 3
Display the Watch 4 window	CTRL+ALT+W, 4
Display the Autos window	CTRL+ALT+V, A
Display the Locals window	CTRL+ALT+V, L
Display the Immediate window	CTRL+ALT+I
Display the Call Stack window	CTRL+ALT+C
Display the Threads window	CTRL+ALT+H

That’s it for debugging basics for part 1.

Technorati Tags: SQL Server Management Studio,Transact-SQL Debugger,SQL Server 2008

SQL Server Management Studio Tips – Part 4

Bill Ramos — Sat, 11 Apr 2009 05:58:15 GMT

This is the fourth in a series of posts highlighting Scott Cate’s video series of Visual Studio tricks over the last few months from Sara Ford’s blog.

As I mentioned in Part 1, many of the commands are documented in the SQL Server 2008 help topic – SQL Server Management Studio Keyboard Shortcuts.

Blog Post	Comments
#57 [Ctrl]+[J] invokes statement completion	This works best when using aliases.
#59 Resize the Statement Completion Window	A handy way to see more items where vertical size is persisted.
#61 Presenters Must Read .. Increase the Editor Tooltip Font Size	This is something that I already have in my demo environment. It’s useful for people with old eyes as well.
#62 Display parameter info for a function	Using [CTRL]+[SHIFT]+[SPACE] is very handy if you missed the parameter info the first time.
#64 Keyboard Shortcut to Display Quick Info Tooltip	In SSMS, the command is a little different. You need to use [CTRL]+[K], [CTRL]+[I]
#65 Edit.WordComplete via Intellisense	This is handy typing in column names without having to display the drop down list. *[CTRL]+[Space] or [ALT]+[RIGHT]*
#66 Using Quick find in the Current Document	Calls out how you can also dock the Find tool window.
#67 Search in hidden text in the editor	Something to remember if you get in the habit of collapsing statements.

That’s all of Scott’s great tips that also apply to SSMS.

Technorati Tags: SQL Server Management Studio,SQL Server 2008,SQL Server 2005

SQL Server Management Studio Tips – Part 3

Bill Ramos — Fri, 10 Apr 2009 06:23:00 GMT

This is the third in a series of posts highlighting Scott Cate’s video series of Visual Studio tricks over the last few months from Sara Ford’s blog.

As I mentioned in Part 1, many of the commands are documented in the SQL Server 2008 help topic – SQL Server Management Studio Keyboard Shortcuts.

Blog Post	Comments
#33 Drag and Drop techniques inside the Visual Studio Editor	This post shows how you can use the Ctrl key to make a copy of text you are dragging in the editor and how to drag text into a tabbed editor window.
#34 Cut and Paste Collapsed code in Visual Studio	With SQL Server 2008 Intellisense, it’s important to know how hidden text is impacted by cut/copy/paste.
#39 Middle button support in the Visual Studio Editor	I keep forgetting you can click on the wheel of the mouse to scroll.
#41 Forward and Backward navigation in Visual Studio Editor	Only [CTRL] + [-] works for moving back, but it’s still a handy time saver. If you want to add the tool bar button to your query editor toolbar, you’ll find Navigate Backwards in the View section.
#43 Select from Cursor to last “Go Back” marker in Visual Studio	This is real handy when you are moving the cursor and realize you want to select the text. *[CTRL]+[=]* = Select from current cursor position to last Go Back marker.
#44 Delete whole word (or whitespace) at a time forward and backwards	This is real handy for deleting columns in the project list. [CTRL]+[BACKSPACE] to delete the word to the left and [CTRL]+[DELETE] to delete words to the right.
#45 Move cursor to top or bottom of screen (without scrolling)	The use of [CTRL]+{UP ARROW] or [DOWN ARROW] keys to scroll without changing the cursor location is very handy.
#47 Swapping Anchor points (THIS IS COOL!) in Visual Studio	When not getting the initial selection right – this is very handy. In SSMS, the keystroke is actually [CTRL]+[K], [A]
#56 Show Shortcut Keys in Visual Studio Toolbar Tooltips	Great aid to learn keyboard shortcuts for toolbar buttons.

On to Part 4 for tomorrow.

Technorati Tags: SQL Server Management Studio,SQL Server 2008,SQL Server 2005