Allen White

The Finish Line is in Sight

I've been taking college classes, and if all the planets align themselves properly I'll graduate in May, 2008 with a Bachelor's Degree in Computer Science from Baldwin-Wallace College in Berea, Ohio.

A little background - 35 years ago I attended Kent State University, as a Theatre major, and lasted about two years. After about six months as a receiving clerk in a home appliances store and as a door-to-door salesman I attended the local community college and got an Associates Degree in Data Processing, which started my career in this business.

About 20 years ago I went back to college to pursue Electrical Engineering, because I was spending a lot of my time at work troubleshooting data communications processes. That lasted about a year and a half. I then went back to school 5 years after that, this time pursuing something I thought I'd enjoy - Journalism. That lasted a couple of years, but ended when I changed jobs, because the new job took too much time for school.

Two years ago I decided I needed to focus on school, and Baldwin-Wallace has a program to give you college credit for the things you've learned in life, a program they call Prior Learning Assessment. I started taking classes, and this week I completed the PLA portfolio, requesting credit for six Computer Science classes for which I believe I have sufficient business experience to equate to the concepts they teach in these classes, plus a class in Public Speaking. Those of you who know me will giggle at that. If all the classes are accepted that will give me 23 hours towards my degree.

This summer I'm taking three classes, two more in the fall and then three in the spring, and once those are complete I will have achieved that Bachelor's Degree I started working on 35 years ago.

Maybe the discussion is premature, but it's nice to see that I've got a defined end point now.

Allen

Considering Encrypted Data Requirements into your Disaster Recovery Plan

I have the biggest problem trying to get a handle on data encryption. So, when it came time to test a log shipping solution I'd implemented for one of our websites the initial testing failed because I hadn't done a couple of basic, but very important steps.

First, and this is really important, back up the service master key and database master key for each database which uses SQL Server encryption. It's not hard - here are the steps:

use master
GO
BACKUP SERVICE MASTER KEY FROM FILE = 'C:\MyDirectory\ServerSMK.key' ENCRYPTION BY PASSWORD = 'UD58ss6r'
GO
use MyDatabase
GO
BACKUP MASTER KEY FROM FILE = 'C:\MyDirectory\MyDatabaseMK.key' 
    ENCRYPTION BY PASSWORD = 'UD58ss6r'
GO

Copy these files to somewhere safe, where you know you'll be able to find them should you find you need to recover the data in these databases.

Now, should you find that you need to recover the database, you can restore the database from backup, then issue the following commands to restore full access to the encrypted data:

use master
GO
RESTORE SERVICE MASTER KEY FROM FILE = 'C:\MyDirectory\ServerSMK.key' DECRYPTION BY PASSWORD = 'UD58ss6r'
GO
use MyDatabase
GO
RESTORE MASTER KEY FROM FILE = 'C:\MyDirectory\MyDatabaseMK.key' 
    DECRYPTION BY PASSWORD = 'UD58ss6r'
    ENCRYPTION BY PASSWORD = 'ccH4QvQCp8Ry6nYSsVxZ5oU'
	FORCE
GO

As I mentioned, I learned this while testing a site where we've implemented log shipping to another city's data center in the event of a disaster in our Cleveland data center, and after restoring the service master key and database master key, the application on the alternate site worked as it does here in Cleveland. Of course, disaster recovery without testing doesn't amount to much, so it was a good exercise for many reasons.

I'm still not real clear on all the encryption technology, but I'm much more comfortable with it than I was a week ago.

Allen

Tech Ed 2007 SQL Server High Availability

I've been registered for TechEd for months now, but I just registered for Kim Tripp and Bob Beauchemin's pre-conference session called Leveraging SQL Server Always-On Technologies to Achieve High Availability and Scalability.

There are a lot of changes going on at Advanstar, where I've been the SQL Server DBA for four years now. The company has been bought, and that's always interesting. We're going to be changing our name, because people have trouble spelling Advanstar, and the building I work in has been sold, so we'll be moving to a new location in the early fall.

Since uptime is critical for web sites where people purchase tickets to our trade shows, we need to minimize the downtime while we move our data center to the new location when we move, so the timing of this conference session couldn't be better.

Allen

Factoring Log Shipped databases into the automated backup programs

I had two articles published in April on the Simple-Talk web site (Automate your Database Maintenance using SMO and Alert-based Transaction Log Backups - Automate your database Maintenance Part 2), and after they were published a need popped up here requiring me to set up log shipping for a couple of my production databases. Well, my automated transaction log backups would prevent the successful updates via transaction log backups in the log shipping databases, so I added the following code in both programs, within the loop through the databases:

    ' Log Shipping generates its own set of transaction log backups, and doing log backups
    ' outside of that process would break the chain of log backup files used by the remote
    ' databases, so we do NOT want to backup transaction logs for databases that are using
    ' log shipping.
    If bolProcessDB = True Then
	Dim jobLogShipBackup As Job
	jobLogShipBackup = srvMgmtServer.JobServer.Jobs("LSBackup_" & dbDatabase.Name)
	If Not jobLogShipBackup Is Nothing Then     ' A Log Ship backup job exists for this database
	    bolProcessDB = False                    ' so do not include it in the transaction log backup
	End If
    End If

I added comments after the articles yesterday, but thought I'd reiterate here both the code change and the reason. Note that this code does expect you to use the default naming for the log shipping backup job name. If you use a different naming convention you'll need to modify your program accordingly.

Allen

Using PowerShell to browse SMO objects

I'm trying to solve a problem with one of my applications, and need to browse the SMO objects for a particular database. In the past I'd have fired up VB.Net and gone into the debugger and poked around that way. Not so any more with PowerShell! I open up a PowerShell session and enter these commands to establish the environment, connect to the server, and grab the database object:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer'
$db = $s.databases['AdventureWorks']
$db

Now, I can see the list of values in each of the properties at the Database level. If I want to drill down, say, into the DatabaseOptions properties, it's this simple:

$dbo = $db.DatabaseOptions
$dbo

And now I can see the values for each of the properties in the DatabaseOptions object. Very cool.

Allen