In the recent past, I had to work on a SQL Server 2000 instance which became unresponsive after a short period of time the service was restarted. Since this was SQL Server 2000, I didn’t have the opportunity to use a Dedicated Administrator Connection (DAC) to log into the SQL Server instance to see if a DAC connection succeeded. And if it did, could I figure out what was happening on the SQL Server engine that it was not accepting a new connection.
Post the SQL Server service restart, the ERRORLOG very happily indicated no issues and if you weren’t already ready to tear your hair out due to the lack of error messages, the connection failure reported the most generic of errors messages:
Server: Msg 11, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check your network documentation.
I did the basic due diligence to check if the network protocols were enabled and if the port on which the SQL Server instance was supposed to listen on was actually open. I did happen to check the netstat output to check the activity on the port and found a large number of connections on the SQL Server port. I did a quick check of the count of the number of connections showing up to determine if this was a TCP port exhaustion issue. But that was not the case either! The Errorlog didn’t even report a Deadlocked Schedulers condition for me to know that there was an issue.
Read the rest of this entry »
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.
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!
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.
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?