Skip to content

Automated MCM Readiness Videos download

Welcome to the “All Things C#” blog, at least for today’s post.

Earlier today I found out that additional videos had been added to the SQL Server MCM Readiness Videos site since the last time I browsed it serveral months ago. Last time, I downloaded the videos by opening up each page and manually clicking the appropriate links. Since there are now 70 videos available (!) the “manual method” would be a pain in the neck. So, I figured I’d spin a few cycles automating the process.

Yeah, I’m a SQL guy. But I know that something this simple can be automated. I saw “The Social Network” too. So with Visual Studio on one monitor and Google on the other, I hammered out a C# class that took care of business. And, since I’m such a nice SQL guy, I’m sharing it with everyone else too! The source code is below.

In order to run this, open Visual Studio and create a new C# Console Application project. Replace the source for “Program.cs” with the code below, edit the static variables appropriately, and fire it off!

No, it’s not pretty. Yes, it could be commented, annotated, parameterized, and a ton of other things before I published it. Right now I don’t really care! Like I said, I’m a SQL guy and not a C# guy, and I just want to get on with watching some of the 41 hours of video that I just downloaded. So, maybe someday I’ll clean this up .. but not today.

If you don’t have Visual Studio but want a compiled EXE, let me know and we can work something out.

Enjoy!

Oh … and I should probably mention that to download all 70 videos, at least in WMV format, totals 7.26GB of files, so you’ll probably want to fire this off and let it run overnight. Or at least let it download in the background while you watch the first video or two that comes in.

using System;
using System.Collections.Generic;
using System.Text;
using System.Collections.Specialized;
using System.Text.RegularExpressions;
using System.Xml;
using System.IO;
using System.Net;

/*
 * AUTHOR: Randy Rabin - aka mailto://rtpsqlguy@hotmail.com - see blog post at https://rtpsqlguy.wordpress.com
 * DATE: March 25, 2011
 * BRIEF DESCRIPTION: Automated download of all videos from the SQL Server MCM Readiness Videos site
 * 
 * NOTE: This program downloads all videos found under the "More..." link off of the main page
 *      By default it downloads WMV files to folder C:\MCM Readiness Videos. Edit the class-level statics below to change these settings
*/

namespace GetMCMVideosFromTechnet
{
    class Program
    {
        static readonly string mainMcmReadinessVideosPage = @"http://technet.microsoft.com/en-us/sqlserver/ff977043.aspx";
        static string downloadPath = @"C:\MCM Readiness Videos\";
        static readonly string downloadMediaType = "wmv";

        // class-level collection populated and used by multiple methods below
        static StringCollection mainUrlList = new StringCollection();

        static void Main(string[] args)
        {
            // Create the download folder if it doesn't already exist
            if(!Directory.Exists(downloadPath))
                Directory.CreateDirectory(downloadPath);

            if (!downloadPath.EndsWith(@"\"))
                downloadPath += @"\";

            // Grab the list of page links from the XML stream linked to by the "More..." button on the MCM page
            ReadXmlList(mainUrlList);

            // For each page link, download the video from that page to local disk
            DownloadWebPageList(mainUrlList);
        }

        static void ReadXmlList(StringCollection urlList)
        {
            Console.WriteLine("Loading main web page...");

            string mainMcmPageSource = GetWebPage(mainMcmReadinessVideosPage);
            Regex regex = new Regex("<a href=\".*?.xml\">More...</a>");

            string xmllink = regex.Match(mainMcmPageSource).Value;
            xmllink = xmllink.Substring(xmllink.LastIndexOf("<a href")).Replace("<a href=\"", "").Replace("\">More...</a>", "");

            Console.WriteLine("Loading \"More...\" link information...");

            XmlDocument doc = new XmlDocument();
            doc.Load(xmllink);

            XmlNodeList nodelist = doc.SelectNodes("/rss/channel/item/link");
            foreach(XmlNode node in nodelist)
                urlList.Add(node.InnerText);
        }

        static string GetWebPage(string url)
        {
            WebClient wc = new WebClient();
            return wc.DownloadString(url);
        }

        static void DownloadWebPageList(StringCollection urlList)
        {
            int cntr = 0;

            foreach (string url in urlList)
                DownloadMediaFromWebPage(url, ++cntr, urlList.Count);
        }

        static void DownloadMediaFromWebPage(string url, int cnt, int listsize)
        {
            string webPageSource = GetWebPage(url);
            string pageTitle = FindPageTitleInPageSource(webPageSource);
            string mediaUrl = FindMediaLinkInPageSource(webPageSource);

            Console.WriteLine("Downloading {0} of {1}: {2}", cnt, listsize, pageTitle);

            DownloadMedia(pageTitle, mediaUrl, downloadPath);
        }

        static string FindPageTitleInPageSource(string webPageSource)
        {
            Regex regex = new Regex("span class=\"EyebrowElement\">.*?</span>");
            string pageTitle = regex.Match(webPageSource).Value;

            pageTitle = pageTitle.Replace("span class=\"EyebrowElement\">", "").Replace("</span>", "");

            return pageTitle;
        }

        static string FindMediaLinkInPageSource(string webPageSource)
        {
            Regex regex = new Regex("<a href=\"http://download.microsoft.com.*?" + downloadMediaType + "\"");
            string medialink = regex.Match(webPageSource).Value;

            medialink = medialink.Replace("<a href=\"", "").Replace("." + downloadMediaType + "\"", "." + downloadMediaType);

            return medialink;
        }

        static void DownloadMedia(string pageTitle, string mediaUrl, string downloadPath)
        {
            string downloadFile = downloadPath + pageTitle + " -- " + mediaUrl.Substring(mediaUrl.LastIndexOf('/') + 1);

            if (!File.Exists(downloadFile))
            {
                WebClient wc = new WebClient();
                wc.DownloadFile(mediaUrl, downloadFile);
            }
        }
    }
}

Advertisements

TriPASS Lightning Talk slide deck

For anyone who is interested .. here is the link to the slide deck from my “lightning talk” from the March 2011 TriPASS user group meeting last night.

sys.dm_os_performance_counters: Realtime or Cumulative?

After publishing my initial posts on this subject, I became suspicious of several “realtime” counters that appeared to always increase as time went on. They never decreased! The first one that I noticed this behavior was “Databases:Log Growths” which, for one particular server I was monitoring, was always a non-zero value. I quickly realized that the value, although not zero, was not changing either.

I figured where there was smoke there was probably fire, and so I built a script to analyze all of the realtime counters across the 50-odd instances of SQL that I monitor. Below are my findings. Unfortunately I was not able to confirm or deny all counters (140 of them!), as some of them remained stubbornly at zero for the entire polling interval. Others changed, but so infrequently that I could not determine with 100% certainty which type they are. So, there is still room for improvement.

 

Not Realtime

In any case, first is a list of the realtime counters that I confirmed to really be cumulative:

Counter Object Counter Name
SQLServer:Broker Activation Task Limit Reached
SQLServer:Broker Statistics Broker Transaction Rollbacks
SQLServer:Broker Statistics Dropped Messages Total
SQLServer:Broker Statistics Enqueued Local Messages Total
SQLServer:Broker Statistics Enqueued Messages Total
SQLServer:Broker Statistics Enqueued Transport Msg Frag Tot
SQLServer:Broker Statistics Enqueued Transport Msgs Total
SQLServer:Broker Statistics SQL RECEIVE Total
SQLServer:Broker Statistics SQL SEND Total
SQLServer:CLR CLR Execution
SQLServer:Cursor Manager Total Cursor flushes
SQLServer:Databases Log Growths
SQLServer:Databases Log Shrinks
SQLServer:Databases Log Truncations
SQLServer:Transactions Version Store unit creation
SQLServer:Transactions Version Store unit truncation

           

“Probably” Not Realtime

I tagged these six counters as “probably” not realtime based on the word “total” in their name, but I did not find any supporting evidence as these counters were zero on all of my servers.

SQLServer:Broker Statistics Activation Errors Total
SQLServer:Broker Statistics Corrupted Messages Total
SQLServer:Broker Statistics Forwarded Messages Total
SQLServer:Broker Statistics Forwarded Msg Byte Total
SQLServer:Broker Statistics Forwarded Msg Discarded Total
SQLServer:Cursor Manager Total Async population count

These next four counters were cumulative on most of my servers, but exhibiting realtime behavior on a few others, so I have to include them in the “probably” bucket as well.

SQLServer:Broker/DBM Transport Current Bytes for Send I/O
SQLServer:Broker/DBM Transport Current Msg Frags for Send I/O
SQLServer:Broker/DBM Transport Pending Bytes for Send I/O
SQLServer:Broker/DBM Transport Pending Msg Frags for Send I/O

 

Pure Guesswork

This final list of counters is guesswork, as all values were zero across all servers for the polling interval. In lieu of any data to the contrary, I would consider these to be realtime counters.

SQLServer:Broker Statistics Forwarded Pending Msg Bytes
SQLServer:Broker Statistics Forwarded Pending Msg Count
SQLServer:Broker/DBM Transport Pending Msg Frags for Recv I/O
SQLServer:Broker/DBM Transport Recv I/O Buffer Copies Count
SQLServer:Database Mirroring Log Remaining for Undo KB
SQLServer:Database Mirroring Log Scanned for Undo KB
SQLServer:Databases Commit table entries
SQLServer:Exec Statistics DTC calls
SQLServer:General Statistics Event Notifications Delayed Drop
SQLServer:General Statistics Trace Event Notification Queue
SQLServer:Memory Manager Memory Grants Pending
SQLServer:Resource Pool Stats Pending memory grants count
SQLServer:Transactions Update Snapshot Transactions
SQLServer:Wait Statistics Memory grant queue waits
SQLServer:Wait Statistics Transaction ownership waits
SQLServer:Wait Statistics Workspace synchronization waits
SQLServer:Workload Group Stats Active parallel threads
SQLServer:Workload Group Stats Blocked tasks
SQLServer:Workload Group Stats Max request cpu time (ms)
SQLServer:Workload Group Stats Queued requests

 

Confirmed

Of the 140 realtime counters I captured, the remaining 94 have all been confirmed to be “truly realtime”. I will not list them here, but they are all counters of type 65792 that are not in one of the lists above. They were confirmed to be realtime due to their values bouncing up and down multiple times during the polling interval across all servers.

 

When I started this project to build a data warehouse of performance counters, I never thought this would get so messy. Hopefully this series of posts helps someone else out there trying to figure out the same problem as I’ve been doing.

Feedback, as always, is more than welcome!

sys.dm_os_performance_counters Revisited

It has been a while since I last blogged this subject, but today I found one particular item that needs amending. For cntr_type 1073874176 (cumulative ratio) I found several counters that don’t fit the “add ‘Base’ to the counter name” rule. They are:

Counter Object Counter Name Counter Base
SQLServer:Broker TO Statistics Avg. Length of Batched Writes Avg. Length of Batched Writes BS
SQLServer:Broker TO Statistics Avg. Time Between Batches (ms) Avg. Time Between Batches Base
SQLServer:Broker TO Statistics Avg. Time to Write Batch (ms) Avg. Time to Write Batch Base
SQLServer:Latches Average Latch Wait Time (ms) Average Latch Wait Time Base
SQL Server:Locks Average Wait Time (ms) Average Wait Time Base

I’m not kidding about the first one. Using “BS” instead of “Base” seems oddly self-descriptive of Microsoft’s naming rules and counter type usage in this DMV. The other four are pretty straightforward. Replace the “ (ms)” at the end of the counter name with “Base” and you’re good to go.

SQL Server “Denali”

Just finished watching the first keynote presentation from the SQL Server PASS Summit, including the announcement and demos of the next version of SQL Server currently code-named “Denali”. DBAs out there, hold onto your seatbelts, it’s going to be a heck of a ride. Live reports in a PPT slide deck? A completely new DR engine? Nifty cool.

Download it from Microsoft, here.

Okay, we got the sales pitch, took the test drive and we’re hooked … so how much will it cost?


(added) based on a couple of installations so far, the default install does not enable the TCP/IP protocol, meaning that Denali supports local connections only. To connect remotely, you need to enable TCP/IP using SQL Configuration Manager and then restart the SQL Server service.

It also appears from (very) limited testing that SQL Management Studio, even 2008 R2 version, does not connect to Denali properly.

SQL2008 on Win2003 Error and Fix

You gotta love Microsoft’s SO descriptive error messages. Ran into another cluster install problem today, this time on Windows2003. Same problem, same fix, different error message.

 

Problem: SQL Server setup fails after clicking “New SQL Server failover cluster installation”

Error message: SQL Server Setup has encountered the following error: Failed to retrieve data for this request..

Solution: Start the Remote Registry service (and set to auto-start if necessary)

The strange thing with this error is that it pops up only if Remote Registry is not running on OTHER nodes of the cluster. It seems to not really care (or at least not throw this error) if the service is not running on the node that you’re installing to. Again, thanks MS for taking the time to describe the error so precisely!

By the way, the double periods after the error message above is not a typo, or at least not on MY part.

Win2008 Cluster Validation Server Selection Error and Fix

I ran into a simple problem today setting up a Windows2008 two-node cluster (in preparation of installing SQL Server). It took me an unusual amount of time to find the answer on the web, so I’m posting it here briefly in hopes that someone else will benefit.

Problem: Cluster Validation error on Select Servers or a Cluster dialog. Attempting to add a server to the list results in an error message.

Error message: Failed to validate node ‘host.domain’. An error occurred while determining if you have administrator privileges on ‘host.domain’. The network path was not found.

Solution: Start the Remote Registry service (and set to auto-start if necessary)