Awesomesauce: Perf Dashboard on Native SSRS

I had been asked the question about using the Perf Dashboard reports from a Reporting Services instance multiple times. Though this was not the intention of launching the SQL Server Performance Dashboard Reports but there is a compelling need at times to have these accessible from a web URL. In today’s age, you will find a lot of DBAs monitor their SQL Server instances remotely. Sometimes, there is another layer of complexity added to this when they want to just look at the health of the SQL Server instance by accessing a URL exposed through a corporate server in their environment. In such cases, you wouldn’t want to jump through a few hoops of setting up your VPN connection and blah blah.

So with all that in mind, let’s talk about how you can get your SQL Server 2012 Performance Dashboard to your existing SQL Server Reporting Services instance.

  • First you need to create a new Reporting Services Project using SQL Server Data Tools (SSDT).
  • Use the solution explorer to add all the existing performance dashboard reports from the C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard folder.
  • Modify the project properties to ensure that the data source gets written to the same folder as the reports.
  • Create a shared data source pointing to your SQL Server instance.
  • Open each report that was imported into the project and modify the data source properties to use the shared data source that you created in the above step.
  • Modify the project deployment properties as shown below.

image

Now you are ready to deploy your Performance Dashboard SSRS project to your reporting services instance.

What can you do next?

You can now set up Report Server subscriptions for the set of reports that you wish to receive via email. You can get a status report of your SQL Server instance without even having to lift a finger and that to right in your mailbox! Smile

Note that Performance Dashboard is a tool which is provided “AS-IS” by Microsoft. The steps mentioned above will help you deploy the existing Performance Dashboard reports to a SQL Server Reporting Services instance. However, the failure to deploy these reports as mentioned above is not liable for support by Microsoft SQL Server Support team.

SQL Saturday #116: Are you gonna be there?

I am really looking forward to SQL Saturday 116 which is the first SQL Saturday being organized in Bangalore at the Microsoft Office on January 7th, 2012. There are some interesting stuff lined up for the day with two different tracks: Main and Practical. You can look up the schedule here.

Not only do you have an amazingly line-up of speakers but there’s also going to be a SQL Server Clinic. Those of you who have attended the SQL Pass Summit might be familiar with this concept where you have MSFT CSS Engineers having deep technical expertise in SQL Server available to you to discuss your real-world SQL problems that you face in your environments that you manage or work on. We love the challenge of new problems that we have not seen before and derive immense amount of satisfaction in helping someone resolve a tough or difficult issue involving SQL Server! If you have a specific problem to solve, it helps to bring the details. ERRORLOG files, error messages, specific query syntax, or details of your environment. The more background context you provide us with, the more we can narrow down with our suggestions. No presentations, just some serious troubleshooting and on-the-spot assistance from 2:30PM IST – 5PM IST under the Practical track.

I am glad that I will have the opportunity of presenting at SQL Saturday #116 along with the likes of Pinal Dave [Blog | Twitter], Vinod Kumar [Blog | Twitter] and Balmukund Lakhani [Blog | Twitter]. My presentation topic reads “Demsytifying SQL Server Memory Dumps” at 11:30AM IST. As the topic reads, I am going to clarify why digging into the root causes which result in SQL Server memory dumps might not be a daunting task!

I am totally looking forward to Jan 7th!! That would be a cracker of way to start off the first weekend on the New Year! Are you gonna be there?

Virtual Tech Days Session

The 12th edition of Virtual Tech Days is scheduled from 28th September to 30th September. I will be delivering a session under the Manage Your Data – DBA Track. The session is about Managing SQL Server Made Easy. You can register for this edition of Virtual Tech Days here.

In this session, I shall demonstrate how to use Management Studio and the existing features in SQL Server to manage your SQL Server instances. This will feature use of Extended Events, Custom Reports, Management Data Warehouse and much more.

Event Date: 29th September
Event Time: 4:15PM – 5:15PM IST

The DBA track will also feature sessions from well known SQL Server community members: Pinal Dave [Blog | Twitter] and Vinod Kumar [Blog | Twitter].

Pinal’s session: Ancient Trade of Performance Tuning – Index, Beyond Index and No Index | Timing: 11:45am-12:45pm

Vinod’s session: Monitoring Performance in Cloud for SQL Azure Applications | Timing: 3:00pm-4:00pm

My colleague, Balmukund Lakhani [Blog | Twitter], will also be delivering two sessions during Virtual Tech Days:

imageTips for Successful SQL Server Deployment in Enterprise Environment | Timing: 10:30am-11:30am IST

High Availability – A Story from Past to Future | Timing: 1:45pm-2:45pm IST

Day 2 Agenda: http://www.virtualtechdays.com/agendaDay2.aspx

And there are some cool prizes to be one!! Will you be there?

T-SQL Tuesday #19: Disasters and Recovery

This month’s revolving blog party a.k.a. T-SQL Tuesday is being hosted by Allen Kin (blog | twitter). The topic for this month is Disasters and Recovery. The creation of fail-safe mechanisms is probably one of the most important facet of any IT administrator role in today’s world where online transactions have become synonymous to daily lives. When creating a DR strategy, you need to keep three things in mind:

1. RPO Recovery Point Objective
2. RTORecovery Time Objective
3. SLA – Service Level Agreements

Mike Walsh has already documented about the above three points in an earlier post on SQL University DBA Week. So what am I going to talk about in this post….. Well, since a major part of my daily job involves working on critical situations which sometimes involve disaster recovery, I will talk about some key but simple points that you should be aware of while restoring your database[s] in case your server encounters a disaster.

1. Always have backups – This point no matter how many times reiterated is still less! You should always have backups of your databases. You should store your backups on a separate media which is not the same as the disk drives which store the database files. This ensures that you don’t have a single point of failure. I have seen a lot of scenarios where the backups and the database files are stored on the same drive. Once the drive goes BOOM!!… You are left with zilch!! A bad scenario to be in!

2. Test your backups – Just taking regular backups doesn’t ensure that you will be safe when a disaster strikes. You need to restore your backups and ensure that the backups can be restored successfully. If you have an automated DR strategy in place, then it is always good to perform dry-runs to ensure that your team is well versed with the recovery process when the need arises. You don’t want to be grappling with your restore scripts during a crisis situation. The next nugget of information is to ensure that a DBCC CHECKDB on the restored database completes without any errors. Just because the restore was successful, doesn’t mean that the database is consistent!

3. Know your environment – An application doesn’t just depend on your database[s]. There might be customized connection settings, connection aliases, specific logins, database users, linked servers etc. which need to be kept handy in case you need to bring a new environment online which was a clone of your previous disaster ridden system. I have seen multiple times where the databases have been restored successfully but the logins and linked specific to the application are missing. So now you have an environment which has the application databases but other specifics pertaining to the application’s functioning are missing.

4. System databases need to be backed up also – System databases do need to be backed up as well. Eg. Without the master database backup in a disaster scenario, you will be missing the necessary logins that your application needs to login to the user database.

5. Benchmarking is very important – As I mentioned earlier, a dry-run is very important. This is primarily due to the fact that if you do not know how much time a restore is going to take, you cannot define your RTO and adhere to your agreed SLAs. A classic situation is that the application needs to be up within 4 hours but since no once tested the entire restore cycle, no one knows how long it will take to restore the set of full/differential/log backups that are available.

6. Have multiple points of failure – This is mostly considered as a good to have but in critical environments, I consider this as a must-have! A simple implementation of this would be redundancy. Keep two copies of your database backups. If one set of database backups are inconsistent, you have a redundant set of backups to fall back on. A decision taken to disk space by reducing the number of redundant copies can look very daft when you are not able to bring a production system online due to the unavailability of consistent backups.

7. Never rely on REPAIR ALLOW DATA LOSS as your savior – The REPAIR ALLOW DATA LOSS option provided with CHECKDB should always and always be your last resort! This means that when all else fails, then you resort to repair options. This repair option should never be your first option for recovering from a disaster because as the name states it always results in data loss!!

8. Know how long a CHECKDB takes to complete on the database – If you do not run CHECKDB regularly on the database for which you are creating a DR strategy, then you are inviting trouble. Always run periodic CHECKDB on your databases and note the time taken so that you have a fair estimate on how long a CHECKDB should take to complete successfully on the given database.

9. Redundant database copies – A lot of environments use Database Mirroring, Log Shipping and Replication to maintain duplicate copies of the database. If you are using any of these features to maintain copies of the existing databases, then you need to note two things: first being the latency between the primary and secondary copies. This will define your RPO as the average latency will be the amount of data loss that you should be prepared to deal with and this will also define RPO to some measure as the time taken to recover the missing data would be defined by latency. Another point to keep in mind is that if you decide to use one of the alternate database copy of the database as the new production database, then you need to ensure that you avoid certain gotchas. Example: Orphaned users for SQL Authenticated logins when you use log shipping or database mirroring.

10. Keep in mind the additional SQL Server features being used – If you are using replication or mirroring or log shipping on the primary database being recovered, then you need to account for additional steps before restoring the databases as a simple restore of a database backup for such a database will not do. Eg. Special considerations need to be followed for restoring replicated databases.

For the non-technical aspects, a disaster recovery plan should include the following:

Disaster recovery plan types include the following (from Books Online):

  1. A list of people to be contacted if a disaster occurs
  2. Information about who owns the administration of the plan
  3. A checklist of required tasks for each recovery scenario. To help you review how disaster recovery progressed, initial each task as it is completed, and indicate the time when it finished on the checklist.

The above points might seem like basics but it would be surprising that they don’t get religiously followed on some production environments!

Tools Tips and Tricks: Round-up

Last month I ran a blog series on the different Tools that CSS uses for certain troubleshooting scenarios SQL Server related issues. There were 12 posts that covered the use of different tools like Debug Diagnostic, XPerf, tools from Sysinternals, SQLDIAG, SQL Nexus and some debugging tips.

Now it is time to do a SELECT [PostURL], [Summary] FROM [TroubleshootingSQL].[tblBlogSeries] WHERE [Series] = ‘Tools Tips and Tricks’…..

Post#1: Tools Tips and Tricks #1: Process MonitorSome best practices to be followed when capturing Process Monitor traces to ensure that the data collection doesn’t causes additional performance issues.

Post#2: Tools Tips and Tricks #2: SQL Express RANU instances Explains how to connect to SQL Express Run As User Instances (RANU) using existing tools.

Post#3: Tools Tips and Tricks #3: Custom Rowsets using SQL NexusAnother quick tip on using SQL Nexus to import outputs of .sql scripts used to capture diagnostic data using Rowset Importer.

Post#4: Tools Tips and Tricks #4: RML Utilities – Some helpful tips on the use of RML Utilities which is used by SQL Nexus under the hood for importing SQL Profiler traces into a SQL Server database.

Post#5: Tools Tips and Tricks #5: SQLDIAG and RANU – Explains how to capture diagnostic data for SQL Express Run As User Instances (RANU) using SQLDIAG.

Post#6: Tools Tips and Tricks #6: Custom Reports in SQL NexusA quick tip on how to create custom reports for SQL Nexus a tool widely used within CSS for analyzing performance diagnostic data collected from a SQL Server instance.

Post#7: Tools Tips and Tricks #7: PsExec as parent and ProcMon as childExplains how to use PsExec (a tool from Sysinternals) to launch a process remotely and capture data. In this example, I have used Process Monitor as the remote process.

Post#8: Tools Tips and Tricks #8 – Debug Diagnostic and Crash Rules – A walkthrough on using Debug Diagnostic tool for capturing crash dumps and analyzing them using the Crash Analysis rule.

Post#9: Tools Tips and Tricks #9: PSSDIAG Configuration Manager – Explains how to configure PSSDIAG collection using Configuration Manager GUI with a few tips and tricks on tweaking the XML configuration file.

Post#10: Tools Tips and Tricks #10: Caching PDB files locally – Explains how to cache symbol files locally using CDB.exe.

Post#11: Tools Tips and Tricks #11: Debug Diag and Memory leaks – A walkthrough on configuring Debug Diag for tracking memory leaks for a program which can be extended to tracking non-BPool allocations for SQL Server.

Post#12: Tools Tips and Tricks #12: XPerf, Memory usage and much more – A walkthrough on how to use XPerf Heap allocation tracking for identifying memory consumers for a program. Can be extended to SQL Server Out-of-Memory (OOM) issues for non-BPool memory crunch.