SQL Server Daily Health Check page and Powershell script update

I recently created a new page for my SQL Server Daily Health Check Powershell script download and documentation. I updated the script to version v0.8.2 which refactored the original script into a function to eliminate repeating code and make it easier to customize with additional checks. I will add additional documentation and instructions as I get time.

Automating SQL Server Daily Health Checks with PowerShell

Last night I presented this PowerPoint presentation and Powershell script Demo to fwPASS members.

Automating sql server daily health checks from Dean Willson

Visual Studio Productivity Tools

This is the shell of a presentation I created to highlight some Visual Studio extensions that I have found helpful for improving my coding productivity in Visual Studio 2012.

The extensions can be downloaded from NUGET using Tools – Extensions and Updates – Online – Visual Studio Gallery:

• Productivity Power Tools 2012 by Microsoft
• Web Essentials 2012 by Mads Kristensen

Visual Studio 2012 Productivity Tools from Dean Willson

SQL Server Management Data Warehouse

The Management Data Warehouse introduced in SQL Server 2008 is a comprehensive performance monitoring and reporting system. It is a handy way to consolidate performance monitoring and data for all your servers on a single system.

A couple of minor gotcha’s to consider when setting up the data collector. The setup is so easy it is tempting to do it on auto-pilot. Don’t.

1. This is an obvious one, but I’m pretty sure it happens more than you would expect. Make sure to create a blank database on your monitoring server where you will store the aggregated data collection sets. Don’t accidentally pick one of your production databases or it will add all the tables, views, stored procedures, functions, types, schemas, and other logging objects into that database (there are a lot of them). It will also mark that database as a data collection warehouse which will show up in the database list when you configure data collection on your other servers.
2. Make sure to create the temp folder that will be used to cache the performance data until the periodic upload to the Management Data Warehouse. If the folder is not created, the upload jobs will fail.

photo credit: Nick Saltmarsh / CC BY 2.0

Vista Backup Stopped Working - Fix

I’ve been using Windows Vista’s built-in Backup and Restore functionality daily for over three years now, with good success until recently. I recently started experiencing a backup error every day for the incremental backups as well as when I attempt to create a new full backup.

The backup did not complete successfully. The file or directory is corrupted and unreadable (0x80070570)

This error message was not very helpful because it didn’t indicate which file or directory was corrupt. It also didn’t indicate whether it was my backup source Operating System partition, Files partition, or the backup destination device.

The Fix

I ran numerous utilities including the Seagate Tools (manufacturer of the source and destination hard drives) on both drives which didn’t detect any problems. The final fix was opening a Command Prompt and running “CHKDSK C: /R” where the C-drive was my primary OS disk. Because the Operating System was in use, the Check Disk utility couldn’t run until the next restart.

Backup Redundancy

The Complete PC Backup, where it creates a VHD image file of each of the backup drives, was working the entire time which provided some level of comfort while troubleshooting the traditional backup process.

photo credit: ground.zero / CC BY 2.0

Top Four Uses for Remote Desktop

1. Manage multiple PC’s or servers from a remote computer or location.
2. Keep your data on a desktop in a secured premises and access it remotely. If your laptop gets lost or stolen, the data is still safely tucked away on your desktop at the home base.
3. Fix a friend or relative’s PC without them lugging their equipment to your house or requiring a house call.
4. Access your laptop from another computer when the laptop display or desktop monitor stops working.

photo credit: Locutis CC BY-SA 2.0

Using Virtualization in a Development Environment

I’ve been using virtual desktop environments in the development and testing arena for years, primarily through Microsoft Virtual PC and Sun VirtualBox. However, they bring with them as many challenges as advantages.

Advantages

• When your PC replacement cycle is every two or three years, it can dramatically reduce the time it takes to migrate to your shiny new hardware. Just load the virtualization software and point to your virtual machine configuration file/hard drive image.
• If you don’t use the same resource hungry applications and services every day, you are keeping your host machine operating at a higher average performance. Example: You don’t need to have SQL Server 2000, 2005, and 2008 along with all the SQL Agents, and other peripheral services consuming resources in the background if you only do database development 25% of the time. It is easier to spin up a development instance than manually turn off all those background services.
• It can reduce the risk of data exposure due to theft or loss. For example, if I’m traveling to make a presentation, I can leave my development environment back at the office to minimize the chance that if my laptop is stolen my work and data is compromised. According to the Open Security Foundation and DataLossDB.org 21% of data loss in 2009 was from stolen laptops/PC (incidents by breach type).
• For testing software or InstallShield builds on multiple versions of Operating Systems, it takes much less time to spin up a Virtual PC or Virtual Server than the old way of re-imaging a PC using Norton Ghost.

Disadvantages

• Performance of applications on the guest is rarely as good as running natively on the host operating system.
• You need to have a license for each operating system and licensed applications that you run in a guest OS in addition to the ones you might need on the host. 
• Backups configured in the guest only run when the guest in powered on during the scheduled backup times.
• Options for running 64 bit guests are much more limited and often more costly than 32 bit guests. Must have a minimum of dual core and sometimes requires special hardware based virtualization on the Processor, such as VT-enabled processors.
• Opening the save Virtual Hard Drive image on a different machine can cause Windows OS’s to require re-activation if they sense certain hardware changes. You can only activate Windows OS’s a limited number of times via the internet before you need to call Microsoft to get an alternate activation code.
• If you have a static hard drive size configured, re-sizing can present a real challenge.

This list is not comprehensive. Virtualization is a powerful technology, but it requires careful consideration when putting it into real world application. These are just a few considerations.

SSMS Tools Pack – Handy SQL Server Management Studio Utilities

I ran across the SSMSToolsPack while reading the comments from a post on dynamically generating CRUD Stored Procedures. It has some other nice features like saving snapshots of the execution plan to the clipboard.

It can also generate data insert statements from query resultsets, tables, or a database. That can be pretty handy for a number of reasons like:

• populating lookup tables in the initial deploy scripts of a new application or application update where a lookup table is added
• dropping tables and recreating test data for testing installation scripts and application testing

 

photo attribution http://www.flickr.com/photos/jesse_sneed/2383953694/ CC BY-ND 2.0