Bruce Jackson's Blog

Across our development, test, and production SQL Servers, we have hundreds of databases to keep track of.  Plus, we would like to track additional metadata such as links to our data vocabulary or our rules engine.  Excel no longer scales to our expanding infrastructure.

To help us get started tracking our systems, I developed a cool little tool that will use SQL SMO (SQL Management Objects) to “walk” a SQL Server and report on:  instances, databases, tables, and columns.  This is extensible and with SMO, it’s easy to track any data elements that can be viewed in the SQL Server Management Studio (“SSMS”).

What makes this tool powerful is the search feature in SharePoint 2010.  After we walk our SQL Servers and let the indexer run, it’s easy to find a specific column or table across all of our system.

You can download the code and the SharePoint Site template from here.  The following will be a step-by-step on how to get this installed and working – you can watch a (no-audio) screencast on YouTube by pressing here.

First of all, this works great on the “free” edition of SharePoint Foundation or any of the other Enterprise editions.  Once SharePoint is installed, you need to install the DBWalker site template.

Open up the default site in SharePoint, and press the Site Actions in the upper left corner and then the Site Settings from the pop-up menu.

Under Galleries, press the Solutions link. NOTE: if you don’t see the Solutions link as illustrated next, you are probably not at the top web site so navigate there and try again.


We want to Upload our template but a “feature” (a.k.a. a BUG) of SharePoint is that the Upload button doesn’t show up unless you do some navigation funny stuff. So if your page looks like this:

You need to press the Browse menu-item and then press the Solutions menu-item again.  Once you do that, your Upload button should show up and look like this:



Upload the DBWalker.WSP file and make sure you Activate it so it shows up in the new site options.

Back under Site Actions, click on New Site and you should see the DBWalker template as an option.  Select that and call your site whatever you want – we’ll use DBWalker for this demo.



Now, you’re ready to get started.  On the Server List, just input the DNS (network) name for the SQL Server(s) you want to “walk”.  The tool will input the rest of the information such as memory and CPUs.

Once you have your server list complete, you can launch the DBWalker application.  You should provide the URL for the site that hosts the DBWalker web site you just created.  You can optionally set a true or false parameter to walk other instances on the server.  Note that you must have remote registry access for the tool to discover the instances on a given server.

I input one server, the server running the web site and a sample is pasted next and you can see that the tool has input the OSVersion, the Physical Memory, and the Processor Count.



A nice feature of SharePoint 2010 is the ability to link lists (tables under the covers).  For our template web site, the main page illustrates this by allowing the user to select an item and the child items listed below are filtered by that item. For example, in the next screen shot, the SharePoint named instance is selected and the Database list is automatically filtered to show only the three databases on that server\instance.  Similarly, when the top database is selected, only the tables for that database are listed.



This is not a commercial application but a tool to help manage large SQL Server farms.  All lists can be expanded to allow further metadata to be collected either automatically through SMO or through user input.  But again, the power of this comes from the Search feature.  With SharePoint 2010 search, we can tag the results with custom tags and easily find columns, databases, etc.  For example, I searched for “buildversion” and the following screenshot shows the results.

I had an unexpected visit to the ED last week and my wife fortunately had the presence of mind to bring my “health record binder'” where I keep a hard copy of all my doctor visits and labs and stuff.  But, I have all these stored on a development SharePoint Foundation site on my home network and it would be nice to be able to point clinicians to the searchable web site containing my PHR instead of the paper copies if that happens again.  So I embarked on a process to secure the site using SSL and open it up to the Internet.

The first step of course was to put the medical record under SharePoint.  Using both printed and electronic images of our health record, I scanned or stored them in a Picture Library in SharePoint.  With no special configuration, SharePoint will read the text from the image (using OCR) and then index the content.  Very cool.  Now, my doctor can quickly search for a term or test across all my health records.  But I need to get the content where he can access it from the web.

The next step was to create an internet addressable name.  For this illustration, I’ll use a bogus name that would be a sub-domain of my brucejackson.info domain:  MyMedRecord.BruceJackson.Info. 

I use NameCheap as my Domain Registrar because they allow Dynamic DNS which I use to host domains behind a DHCP home Internet connection.  Like most home users, I have a single IP address that can change at any time so I need the dynamic DNS to allow the name to IP maps to be updated when the home IP changes.  To enable my new sub-domain, I went to Namecheap and added the DNS record pointing my new sub-domain name to my current IP address.

With the new domain, I next need to let SharePoint know that this is an alternate name now allowed for the site.

• Open SharePoint Central Administration and click the “Application Management” link in the left column.
• In the right column, you should now see a “Web Application” section.  Underneath that heading you will see an option “Configure Alternate Site Mappings”
  
• After you click the “Configure Alternate Site Mappings” link, you will see a list of valid URLs.    However, if the “Alternate Access Mapping Collection” is not pointing to the SharePoint site you would like to configure, then click on the “No Selection” drop down and select the appropriate web site.
  
• Input the new URL in the Internet text box and then press the Save button.  Once you complete, your list should look something like this:
  

We next need to add a certificate to the web site so logins will be encrypted.  IIS 7+ has the facility to use a self-signed certificate; however the self-signed certificate created by IIS is mapped to the server name which prevents Host Headers from working.  For example, the server below is named SVR08R2BASE and I’m unable to set the Host Name using the “MyMedRecord” self-signed certificate in IIS Manager.

To get around the problem, I created my own self-signed certificate using the MakeCert utility.  On my system, this utility was located in my Visual Studio tools directory, the Windows SDK, and the Fiddler2 folder and is likely available with the “free” version of Visual Studio Express.

Copy over the MakeCert tool to the server hosting the SharePoint site.  For my server, I created a “star” certificate which is a wildcard sub-domain certificate using a command that looks like this:

makecert "c:\temp\mycert.cer" -a sha1 -n "cn= *.BruceJackson.info" -sr LocalMachine -ss My -pe -len 2048 -r -sky exchange

Once the certificate is successfully created, I go back into IIS and select the SharePoint site from the list of web sites in IIS manager, then select the Bindings, and then add a new binding for https using the certificate I just created.  As you can see from the next screenshot, I can now input the Host Name.

One problem with using this “free” self-signed certificate is that the client cannot verify the trust of the issuer (no surprise there).  But, since I would presumably be supplying my own username and password to the attending physician, the only credentials at risk are mine.

The final step was to configure the appropriate Port Mapping at the Internet Router to route the traffic to this server and site.

I probably will not leave the site up all the time since I hope to not need again any time soon.  However, it is nice to know that either my wife or I can launch the virtual machine (this was hosted on a development machine initially as a proof-of-concept) if we need it and our health data is readily available.