Environments, You All Look the Same to Me

Several years ago, I was working on moving data from the reporting server to the development environment so I could test structural changes. Thinking I was in the development environment, and not realizing my level of access was such that it would work in production, I ran TRUNCATE TABLE Users and didn’t bother to verify what environment I was actually running commands in. It was shortly after my panicked call to the DBA requesting help, that I found out the default access to the reporting server was Administrator. (Clearly there needed to be a discussion about permissions given to production servers, but that’s a different story). Fortunately, he easily fixed it, and I learned that the only way to be sure this wasn’t going to happen again was to never to have two different environments open in the same SQL Serve Management Studio (SSMS).

Registered Server Menu
One problem I’ve always had with SSMS, is the fact that you can have multiple servers open at the same time with no differentiation. It’s nice to be able to open them simultaneously, but it is also dangerous from a data integrity standpoint. Did you run that update on the production database server or the development one? It’s easy to say, “Well keep track of what you are doing!” That’s true, you should be cognizant, but people are people and sometimes you get distracted. It’s too easy to make a mistake, and there should be something that says, “HEY YOU’RE ON A PRODUCTION SERVER BE CAREFUL WHAT YOU ARE DOING!” After several years of bemoaning this issue I found a solution to my problem. You can change the properties on a registered server to show a different color on the open connections to it. Simply open the Registered Server Properties.

Registered Server Properties

Select the Connection Properties tab.

Connection Properties

Check the Use Custom Color, press the Select button, and choose the color.

Selected Color

Now when you open a new query window from that server, the bottom of the window is the selected color.

Query Window

The Shake and Bake Dashboard

A few weeks ago, someone asked me if it is possible to create a server dashboard (real time processor usage, available memory etc.) using web technologies. Quickly thinking about it, I thought “sure” how hard could it be, but there are several pieces needed to make it functional and meaningful.

  1. A way to send the data to clients. (You could have each page poll a web server over and over, but that creates a lot of overhead for little benefit.)
  2. A way to retrieve the server data on at regular intervals.
  3. A way to meaningfully present the data.

Transmitting Data to the Client

A short time ago, Microsoft released SignalR 2.0 and made it incredibly easy to install and use in projects. It is a method of using WebSockets to create a single open TCP connection to frequently transmit data between the browser and the server. Browsers can send data without opening a new HTTP request, and servers can push new data to the client when needed. It’s fast, efficient and removes the requirement for the client to continuously poll for updates.

The documentation and tutorials are easy to follow and walk you through the process of setting up a test project to teach the basics. The only issue I found with installing it in my own project was making sure the JQuery bundle referenced in the _Layout.cshtml was moved to execute before the RenderBody method.

Polling Data

ASP.NET code is typically an on demand only process. A client must get or post data to the server in order for the server side code to execute. Once the request ends, the objects in that process are disposed/readied to be garbage collected and makes having an in memory object which makes updates at regular intervals problematic. There are ways around this, such as having a process ping the web server at regular intervals, but this made it more complicated than I wanted as I would have to create another application to communicate to the web server to trigger the update actions.

While searching for a solution, I discovered IRegisteredObject which allows you to use the HostingEnvironment.RegisterObject to keep an object in web application’s memory. After that I created a Timer object to gather information about the current process and send it through SignalR on regular intervals.

Displaying Data

Displaying the chart was the easiest part of the process. Google has an excellent charting module in JavaScript. It’s powerful, easy to use, and fast which I needed to quickly refresh the graph when new data came from the server.

Sparkline

Getting the module from Google is extremely easy. Add the following tag to the page:

To create a basic sparkline that refreshes from a SignalR update only took the following code to allow the graphing to start as soon as the paged finished loading:

All the code for the project can be found here.