My Writings. My Thoughts.
Microsoft Performance Dashboard for SQL Server 2008
// April 9th, 2012 // Comments Off // SQL
Microsoft’s SQL Server Performance Dashboard for SSRS 2005 is still an excellent bit of kit for checking the status of your SQL Server (Microsoft Download page).
However this is a slight coding issues when trying to install the setup.sql in SQL Server 2008 r2:-
This is due to a line in the code:-
Which needs to be changed to the following:-
Microsoft Performance Dashboard for SQL Server 2008 – Issue 2
// April 9th, 2012 // Comments Off // SQL
Microsoft’s SQL Server Performance Dashboard for SSRS 2005 is still an excellent bit of kit for checking the status of your SQL Server (Microsoft Download page).
An Error has occured during report processing. (rsProcessingAborted). Cannot read the next data row for the dataset SESSION_CPU_WAIT_INFO (rsErrorReadingNextDataRow)
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
Again this is a simple fix. Either amend the following code in setup.sql or look for the SP [MS_PerfDashboard].[usp_Main_GetSessionInfo] in the msdb database.
The line to change is:-
sum(convert(bigint, datediff(ms, login_time, getdate()))) – sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,
to
sum(convert(bigint, CAST( DATEDIFF(minute, login_time, getdate()) as bigint)*60000
+DATEDIFF(millisecond, DATEADD(minute, DATEDIFF(minute, login_time, getdate()), login_time), getdate() )))
-SUM(CONVERT(bigint, s.total_elapsed_time)) as idle_connection_time,
Microsoft Performance Dashboard for SQL Server 2008 – Issue 3
// April 9th, 2012 // Comments Off // SQL
Microsoft’s SQL Server Performance Dashboard for SSRS 2005 is still an excellent bit of kit for checking the status of your SQL Server (Microsoft Download page).
An Error has occured during report processing (rsProcessingAborted). Cannot read the next data row for the dataset CPU_UTILIZATION_HISTORY. (rsErrorReadingNextDataRow).
Arithmetic overflow error converting expression to data type int.
Again this is another simple fix. Either amend the following code in setup.sql or look for the SP [MS_PerfDashboard].[usp_Main_GetCPUHistory] in the msdb database.
The line to change is:-
dateadd(ms, -1 * (@ts_now – [timestamp]), GetDate()) as EventTime,
to
dateadd(ms, -1 * (@ts_now – [timestamp])/1000, GetDate()) as EventTime,
Launch PuTTY from your browser – Adding Procotols to Windows
// March 7th, 2012 // No Comments » // Windows
For awhile now, I have wanted the ability to launch putty from a simple hyperlink.
PuTTY enables Windows users to access *nix machines through SSH. For many in the tech industry this tool is absolutely crucial. While it is fairly full featured, it is quite cumbersome for generic SSH use. One may say “Jon, all you need to do is run PuTTY, enter the host name you wish to connect to and provide a user name and password.” This process is certainly fine for a single SSH operation. This becomes quite a pain with multiple hosts at once.
What if there was a way to create a hyperlink which launches PuTTY? One would be able to launch PuTTY from email clients, browsers, spreadsheets, etc… An example link would be: ssh://google.com
What do we need to do?
- Register a new protocol (in our case, let’s call it “ssh”) to Windows’s registry.
- Create a batch file that can take in strings such as “ssh://google.com” and massage them and pass them on to putty.exe
- Point the registry modification to that batch file.
One method of editing the registry is to create a .reg file, save it, right click it and select merge. The .reg file will then be merged with the registry. We will use this method. The key created can always be deleted by later from regedit.
Here is an example .reg file which points the protocol “ssh” to a batch file located at “C:/putty_util.bat” :
REGEDIT4 [HKEY_CLASSES_ROOT\ssh] @="URL:ssh Protocol" "URL Protocol"="" [HKEY_CLASSES_ROOT\ssh\shell] [HKEY_CLASSES_ROOT\ssh\shell\open] [HKEY_CLASSES_ROOT\ssh\shell\open\command] @="\"C:\\putty_util.bat\" %1"
Now save ssh.reg locally, right click it, and select merge.
Note: You can create any new protocol by changing each “ssh” in this .reg file to whatever abbreviation you wish to use for your protocol. You will likewise want to change the string @=”\”C:\\putty_util.bat\” %1″ to point at whatever application you wish to use. In this case, we are using the application C:\putty_util.bat and passing in the first argument we receive to it. In the example of ssh://google.com, we will be passing in the string “ssh://google.com”
Unfortunately, PuTTY does not understand arguments which start off with “ssh://” . As a result, we must use a batch file (C:\putty_util.bat) to remove the “ssh://” and any other oddities. Here is the batch file for my box:
@echo off set var=%1 set extract=%var:~6,-1% "C:\Program Files\PuTTY\putty.exe" %extract%
set extract=%var:~6,-1% is telling us to remove the first 6 characters and the last character from var and place that value in extract. The removal of the first 6 characters is the removal of “ssh://”. We remove the last character as there seems to be a trailing slash present in the input to our batch file.
With extract holding just the hostname, we now execute putty.exe and pass in extract as an argument. In this case, putty.exe is located at C:\Program Files\PuTTY\putty.exe. Please change this to the location of your putty.exe. Save this batch file to C:\putty_util.bat
Once this is done, anytime you have a link such as ssh://google.com you should be able to simply click it and launch PuTTY.
I will have a followup post with instructions on how to accomplish the same with ssh in other operating systems.
Sources:
http://kb.mozillazine.org/Register_protocol
External Links:
Best WordPress Plugins
// March 6th, 2012 // No Comments » // Wordpress plugins
W3 Total Cache
The ultimate swiss-army knife of caching, from the basic page caching (keeping a static copy of the page), to CSS/JS/HTML minification (making your files smaller), as well as handling uploads to a Content Delivery Network.
WP-SuperCache
If W3TC is too much for you, SuperCache is the answer. Very simply, it makes static HTML files to serve users, and much fewer settings to break.
WP-DB Backup
Does what it says on the tin – backs up your database. However, certainly not an easy solution and only works with core tables – so plugin data is lost.
WP-Optimize
For those of you that don’t have direct access to the SQL Server, WP-Optimize optimizes the database by running the optimize SQL command, removing post revisions, spam and unapproved comments. It’s essential if you don’t manually manage your database or run your own optimization scripts. Also includes functionality to rename users, useful for changing the default admin user.
Secure WP
From the WebSite Defender guys, this gives you a thorough security check and make a number of small adjustments to harden your Wp install, such as hiding the version number. Also available as an online service. Download | Plugin Site
Redirection
Monitor your site 404 errors and create simple 301 redirects easy to keep the Google link juice flowing and your readers free from 404 pages. Find what’s 404ing, redirect it, easy. Very useful durring site migrations.
WordPress SEO by Yoast
Widely regarded as the SEO plugin for WordPress. It has an extensive feature set and rave reviews. Give it a whirl, there are alternatives though. Includes XML sitemap features.
Google XML Sitemaps
Creates a site map for you to submit to Google and automatically updates it when you post new content. Essential for getting indexed quickly and accurately.
A simple and easy way to copy a file system between two Linux servers
// February 29th, 2012 // No Comments » // Uncategorized
I’ve been an Engineer for about 20 years now and I couldn’t tell you the amount of times I have had to copy data between systesm
I’ve used all sorts of tools to duplicate data.
Database replication, Array-based replication, rsync + tar piped to tar over SSH.
On the Windows side: good old teracopy and robocopy.
When rsync and tar where the right tool, I often asked myself why there wasn’t a generic file system replication tool when I completed my work. Well, it appears there is. The cpdup utility provides an easy to use interface to copy a file system from one system to another. In it’s most basic form you can call cpdup with a source and destination file system:
$ cpdup -C -vv -d -I /data 192.168.0.1:/data
root@192.168.0.1's password: Handshaked with ftp /data Scanning /data ... Scanning /data/conf ... /data/conf/main.cf.conf copy-ok /data/conf/smb.conf copy-ok /data/conf/named.conf copy-ok Scanning /data/www ... Scanning /data/www/content ... Scanning /data/www/cgi-bin ... Scanning /data/dns ... /data/lost+found Scanning /data/lost+found ... cpdup completed successfully 1955847 bytes source, 1955847 src bytes read, 0 tgt bytes read 1955847 bytes written (1.0X speedup) 3 source items, 8 items copied, 0 items linked, 0 things deleted 3.8 seconds 1007 Kbytes/sec synced 503 Kbytes/sec scanned
This will cause the entire contents of /data to be migrated through SSH to /data on the remote server. It appears cpdup picks up everything including devices and special files, so this would be a great utility to clone systems. There are also options to replace files that are on the remote end, remove files that are no longer on the source, and various others that can be used to customize the copy operation. Nifty utility, and definitely one I’ll be adding to my utility belt.
Enabling MySQL query logging
// February 29th, 2012 // No Comments » // MySQL
I recently ran into the need to see what was being queried against a MySQL server.
I enabled query logging by adding the following two statements to the [mysqld] block in the /etc/my.cnf configuration file:
general_log=1 general_log_file=/var/log/query.log
The first line enables logging, and the second line tells MySQL were to write the logs. Once enabled you can see the queries executed against your server by paging out the contents of /var/log/query.log. This will have one or more entries similar to the following:
233 Query Select FOUND_ROWS()
120212 13:15:07 233 Quit
120212 13:15:12 234 Connect rsyslog@localhost on
234 Init DB syslog
234 Query SHOW TABLES LIKE '%SystemEvents%'
234 Query SELECT SQL_CALC_FOUND_ROWS id, devicereportedtime, facility, priority, fromhost, syslogtag, processid, infounitid, message FROM SystemEvents ORDER BY id DESC LIMIT 100
Pretty cool, and definitely super useful for debugging problems and figuring out how restrictive you can be with your GRANT statements.
Ajax Rating Counter :: AJAX based star rating system
// February 7th, 2012 // No Comments » // PHP
This package can be used to implement a star rating system that uses AJAX to update the average rate without reloading the rate page after a user has voted. It can display the current average rating between 1 to 10 using star icon images.
A new rating submitted by an user is sent to the server using an AJAX request. The server returns the updated rating average and the star icon images change to the new rating value.
Download:
