Two weeks ago Paul Randal started a chain blog series on What three events brought you here?  He tagged Brent Ozar, who in turn tagged me in his post You may ask yourself, How did I get here?  So here’s my own take on this topic.

Event #1: I spent a year deployed in Iraq…

My events that lead me to where I currently am in life start much later than most peoples.  For me I’d say the biggest event that changed my direction in life was being deployed with the Army Reserves in early 2003 to Iraq.  Being forward deployed in a combat zone is always a life changing event, sometimes good, sometimes bad.  For me being deployed to Iraq was an amazing opportunity and became a major stepping stone in my current position in life. 

Combat Zones are also tax exclusion zones, and when I mobilized I was single, and therefore had no real expenses in life.  This allowed me to save almost every dollar I made while deployed.  However, unlike most young kids that return from being deployed, I didn’t run out to buy a new car, add a loud stereo to it, and hook it up with some 20’s.  Instead during my first six months in Iraq I played email tag with an admissions advisor at the University of Florida and got admitted to school to continue working on my bachelors upon return from my tour of duty overseas.

Event #2: I got married and my wife got accepted to grad school…

Fast forward two and a half years and I am now married for just over a year and a new father to boot.  My wife and I both had plans to attend graduate school.  She planned to continue and work towards her doctorate in Psychology, and I planned to apply to and attend Medical school to get my MD.  However, one of the funny parts of life is that plans are subject to change in a moments notice.  Our first child, my now 3.5 year old daughter, was never a part of our plans, but the best things in life sometimes happen by accident.

When I asked my father-in-law for permission to marry his daughter, the one thing I promised was that she would finish graduate school, no matter what.  Just over a year later I found myself defending our decision for my wife to continue her education and attend graduate school despite having a newborn baby to my father-in-law.  Again its really funny the way things work out.  My wife and I had debated about whether or not to continue our schooling, and the end result of those decisions was that I would continue to work in Database Development and she would go back to school, and then we would switch roles.

Part of this plan was based on her getting into school at the University of Florida, not her first choice, she is after all a die hard Seminole, but you work with what you’ve got sometimes.  In the end she was accepted into a program in Tampa, FL which meant that I would either telecommute for the company I was currently working for, or I had to find a new job in Tampa.  After being passed over for a promotion at my job, I opted to look for a new one which lead me to my first gig as a full time Database Administrator.

Event #3: I asked a question on the MSDN Forums…

Fast forward another year, and was stumped on a problem that had popped up on my own custom reindexing script in SQL Server 2005.  After a few weeks trying to figure it out, I turned to the MSDN Forums and posted my first question ever: Reorganizing/Rebuilding Index Results in more Fragmentation?  While I was on the site, I read a couple of the other questions that existed and found that I knew the answer to a few of them, so I posted some quick responses.  I also found a couple of questions that I didn’t know anything about and learned a couple of new things along the way.

Now if that was where the story ended, it would be great, but the next morning I woke up and found that some people I had answered the night before had posted follow up questions which brought me back to the forums to once again respond to those questions and once again, while I was there I picked up a few more questions I knew the answers to and posted some responses, and once again I got to read a few questions that I had no idea about and learned a couple of things.  I don’t know exactly when it happened, it was probably the first time I got a followup question but I was hooked and the forums quickly became like Crack for me.

About three months later I was engaged by Arnie Rowland on a thread about tracing and auditing in SQL Server where I was asked to join a new email list for Moderators, Answerers, and MVP’s that participated in the forums.  This was a great doorway to meeting and interacting with some of the best people I know in the business, Joe Webb, Arnie Rowland, Geoff Hiten, Alejandro Mesa, Sankar Reddy, Deepak Rangarajan, and Louis Davidson (whom I attempted and failed a number of times to debate database design topics with before I knew who he was and how much he knows) to name a few, and I now consider a number of them to be friends, in the truest meaning of the word.

Conclusion:

Life sometimes has great things for us that aren’t necessarily what we plan for.  I never could have foreseen being where I am in the SQL Server industry six years ago when I first got my start. 

Since I am so far behind the crowd on this one, I am not going to call anyone specifically out.  However, if you weren’t called out, consider this your call out and post a blog post about your three events.  Link back to here or post a comment with a link to your post and I’ll make sure to read it.

I asked this on twitter and now I am going to ask it on here to see what kind of responses I get.  If your SQL Server services run under a domain user account, is NT AUTHORITY\SYSTEM (Local System) a sysadmin in your SQL Server and if so, why?

I was prompted to ask this question while configuring a server early today and after installing SQL Server 2008 on Windows Server 2008 R2, I noticed that despite being installed from default using a Domain Account for the Services, the Local System account was still a sysadmin in SQL Server.  In the past I have removed this account from my servers, and I did so today, but the thought crossed my mind, “What might I be breaking by doing this?”

Here is what I don’t want. 

1. I don’t want responses that can’t be backed up with documentation or proof of the need.  I am trying to find out the hard facts about why this account would have been left as a sysadmin in a fresh install of SQL Server and guessing just won’t cut it for this.
2. I don’t want stories about why SQL can be run as Local System, that’s not the point of this post or the question being asked.

What I do want:

1. Documented cases where Local System is needed.  For example K Brian Kelley(Blog/Twitter) offered that Full Text in 2000 required Local System to be a sysadmin in SQL Server 2000 if BuiltIn\Administrators had been removed from SQL Server.
2. Changes required if the Local System is removed.
3. Anything else pertinent to this account existing in SQL Server 2008.

This past weekend’s SQL Saturday #31 in Tampa, FL I had the need to send email from one of my Virtual Machines while doing my presentation on Event Notifications in SQL Server 2005/2008.  Generally speaking, I don’t need to send email from the virtual machines on my laptop.  However, in this case the point of the presentation was to show how to use Event Notifications to get near-real time notification of events in SQL Server.   Gmail provides standard SMTP access for sending email using an authenticated gmail account and its really easy to configure Database Mail to send using the Gmail SMTP Server.  The required settings are readily available in the Gmail help system.  However, to make things easier for those who might try this at a later point in time, the Database Mail Account screen below has the pertinent information filled out:

To handle this programmatically and prevent having to step through the GUI the following TSQL can be used:

EXEC sys.sp_configure N'Database Mail XPs', N'1'
GO
RECONFIGURE
GO
EXECUTE msdb.dbo.sysmail_add_profile_sp 
   @profile_name=N'Gmail Notification Account', 
   @description=N'Email Notifications from SQL Server using Gmail Account'
GO
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp 
   @principal_name=N'guest', 
   @profile_name=N'Gmail Notification Account', 
   @is_default=1
GO
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp 
   @profile_name=N'Gmail Notification Account', 
   @account_name=N'Gmail Notifications'
GO
EXECUTE msdb.dbo.sysmail_add_account_sp   
   @account_name='Gmail Notifications',
   @email_address='fakeaddress@gmail.com',
   @display_name='SQL Server Gmail Notifications', 
   @replyto_address='fakeaddress@gmail.com', 
   @description='Email Address for sending Notifications using Gmail', 
   @mailserver_name='smtp.gmail.com',
   @mailserver_type='SMTP',
   @port=587,
   @username='fakeaddress@gmail.com',
   @password'fakepassword',
   @use_default_credentials=0,
   @enable_ssl=1 


Make sure that you change the address and password information as these are just for the sake of this example.  For those interested, the same can also be done with Windows Live Mail accounts using the setting information contained in their help.

Tonight I got my session evaluations back from PASS for the first large presentation I have ever done.  I am somewhat surprised by the results, they are actually much better than I expected. 

I definitely have a number of things to work on as far as public speaking goes.  Paul Randal gave me a number of tips while I was still at PASS that I have made notes to try and pay attention to the next time I do a presentation.  Some things Paul pointed out were:

1. When someone asks a question, repeat it back so that the entire audience can hear it.  Then when you answer the question everyone knows what you are talking about.
2. Purchase a remote to control your slide deck, allowing you to step away from the computer.  One of the limitations on crowd interaction I had was that I was tied to the podium controlling my slide deck.  Paul Randal recommended a Laser Mouse.
3. Set the fonts inside of SQL Server Management Studio larger than the defaults.  I believe that 14 point was the recommended size, and it is definitely easier to see on a projector.
4. Even after doing the above, install ZoomIt by SysInternals and then learn how to use it effectively.  Most of my demonstrations were plagued by standard font sizes that were impossible to see unless you were sitting on the front row of the presentation.
5. Mix demonstrations into the talk at logical points instead of performing them all at the end.
6. Always have a watch and know the amount of time that is left in your session.  I was very lucky to have Andrew Kelly sitting on the front row of my session and he noticed that I had no idea about where I was time wise in the middle of my presentation.  He provided me fifteen, ten and five minute warnings so that I could complete my session timely, something for which I am extremely thankful.

At the recommendation of many people in the community, I have located a local Toastmasters group that I hope to work with to learn how to be a better public speaker.  One thing that I would like to be able to do is mix the occasional humor in with the technical content, something I am sure I could do, if I was more comfortable while speaking.  I don’t have any expectations that I would be anything like Buck Woody, but would like to be a bit less dry when presenting.

To those that attended my session and rated it, thank you.  Hopefully with a bit of work, I can transform this graph more towards the Excellent side of things.

Every couple of weeks on one of the forums someone will ask a question about how to restore a backup from SQL 2008 to SQL 2005.  The answer to the question is always, you can’t restore a backup to a lower version of SQL  Server.  If you need to migrate backwards it is an entirely manual process.  I learned this unfortunate truth a few years ago after upgrading a development server to SQL Server 2005 ahead of upgrading the production server for testing by the developers of our eCommerce application.  At the time there was a new database for another web project in development that didn’t exist in production, which was on SQL 2000 still, and when I had to move it to QA, also on SQL 2000 still, what was originally planned as a backup/restore turned into a “What do I do now?” moment.

There are a number of ways to downgrade a database in SQL Server, but in general it basically boils down to recreating the database from scripts on the older version and importing data back into the database.  A few years ago, I used SSIS to simplify this task with the Transfer Database Objects Task which can be used to move all the objects in a source database to a target database along with the data in a single step.

Last week during what was expected to be a routine upgrade of a vendor application to the latest version we ran into a problem with the upgrade failing reporting that the version of SQL Server was unsupported for the application.  Fire up profiler to see what the upgrade is doing and essentially this boiled Quick check and the server has been on SQL 2008 for well over a year, so we call up the vendor and after a few hours of discussion, we would have been better off talking to a brick wall, the end result was that the application wasn’t supported on SQL 2008 and they didn't care that it had been running there for over a year.

History repeats itself, or so they say, and once again I was faced with having to downgrade a SQL Server system.  Only this time, I wasn’t moving one database, I was going to have to downgrade just over eighty databases.  I opened up SSIS and soon realized I had no idea what I was doing.  I threw some comments/questions out on twitter.  My first problem was I couldn’t figure out how to loop over databases in a Foreach Loop Container and Andy Leonard threw me a few links and then followed up with a complete SSIS solution to look at.  Problem solved or so I thought. 

I knew from past experience that you could use variables to configure task steps dynamically so I figured that now armed with Andy’s example I would be able to quickly create a SSIS package to loop over the databases and use variables to configure the Source and Target Databases in the Transfer Database Objects Task to move the databases.  Well that is what I thought initially, and much to my frustration after a few hours playing around with this, I got an email back from Andy confirming that there was no known way to use variables in the Transfer Database Objects Task like I was trying to do.

(This is where this weeks TSQL Tuesday picks up)

Since I couldn’t configure a Foreach Loop Container to loop over each database using variables to set the source and destinations of a child Transfer Database Objects Task, I started out manually creating individual Transfer Database Objects Tasks for each of the databases.  For some reason I thought to test this about four databases into building the package and I realized that due to design issues of the database like schema objects not being fully qualified inside of view definitions I actually needed at least two transfer tasks per database.  It was at this point I decided that there was no way in the world that I was going to create that many SSIS objects.  Since SSIS works through SMO, I jumped out to PowerShell and began playing with a couple of things.  However, being impatient like I am, I got tired of having to constantly lookup properties and methods for the objects I was working with, so I jumped back in the Visual Studio and went to C# which has Intellisense built in and made finishing the code very fast.

The end result was a short (<100 lines of code) C# console app that moved the databases from 2008 back to 2005:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using System.Diagnostics;
using System.Collections.Specialized;

namespace Transfer2008To2005
{
    class Program
    {
        static void Main(string[] args)
        {

            string sourceserver = "SourceServerName";
            string destinationserver = "DestinationServerName";

            Server src = new Server(sourceserver);
            Server dest = new Server(destinationserver);

            string[] myarray = new string[] { "databasename", "otherdbname" };

            foreach (Database db in src.Databases)
            {
                if (db.IsSystemObject == false && db.Status == DatabaseStatus.Normal)
                {
                    Debug.WriteLine("Now moving " + db.Name);

                    if (dest.Databases.Contains(db.Name))
                        dest.KillDatabase(db.Name);

                    Transfer t = new Transfer(db);

                    t.DestinationServer = destinationserver;
                    t.DestinationDatabase = db.Name;
                    t.CreateTargetDatabase = true;
                    t.PreserveDbo = true;
                    t.TargetDatabaseFilePath = "D:\\SQLDATA\\";
                    t.TargetLogFilePath = "F:\\SQLLOGS\\";

                    t.CopyAllDefaults = true;
                    t.CopyAllFullTextCatalogs = true;
                    t.CopyAllRoles = true;
                    t.CopyAllRules = true;
                    t.CopyAllSchemas = true;
                    t.CopyAllStoredProcedures = true;
                    t.CopyAllSynonyms = true;
                    t.CopyAllTables = true;
                    t.CopyAllUserDefinedDataTypes = true;
                    t.CopyAllUserDefinedFunctions = true;
                    t.CopyAllUserDefinedTypes = true;
                    t.CopyAllUsers = true;
                    t.CopyData = true;
                    t.CopySchema = true;

                    t.CopyAllObjects = false;
                    t.DropDestinationObjectsFirst = true;

                    t.Options.WithDependencies = true;
                    t.Options.IncludeDatabaseRoleMemberships = true;
                    t.Options.Indexes = true;
                    t.Options.DriAll = true;
                    t.Options.Permissions = true;
                    t.Options.SchemaQualify = true;
                    t.Options.SchemaQualifyForeignKeysReferences = true;
                    t.Options.Statistics = true;
                    t.Options.TargetServerVersion = SqlServerVersion.Version90;
                    t.Options.WithDependencies = true;
                    t.Options.IncludeIfNotExists = true;
                    t.Options.FullTextIndexes = true;
                    t.Options.ExtendedProperties = true;

                    t.TransferData();

                    t = new Transfer(db);

                    t.DestinationServer = destinationserver;
                    t.DestinationDatabase = db.Name;
                    t.DestinationLogin = "schemalogin";
                    t.DestinationPassword = "schemapassword";
                    t.DestinationLoginSecure = false;

                    t.CopyAllViews = true;
                    t.CopyAllTables = true;
                    t.CopyAllObjects = false;
                    t.CopyAllDatabaseTriggers = true;
                    t.Options.Triggers = true;
                    t.CopyData = false;
                    t.CopySchema = true;
                    t.Options.IncludeIfNotExists = true;

                    t.TransferData();
                }
            }
        }
    }
}


Due to the schema issues, I had to double step the migration for a majority of the databases using the schema owner to connect and migrate the views backwards to SQL 2005.  This was still much easier than trying to actually work with the GUI for SSIS in Visual Studio.  However, I had to write a separate version of code to move one database based on the following observations I had, despite specifying the WithDependencies = true option.  Even when specifying this option the following must be considered:

• Create the Database and Roles first, the dependency walker doesn’t seem to take into account that a role must exist before a user can be assigned to it, and the database principals are generated in alphabetical order.
• Foreign Key Constraints and DML Triggers are problemattic when manually downgrading a database.  If possible, create both of these object types after the data has been loaded into the tables, or disable the objects before loading the data into the database.  DML Triggers will fire and can cause Primary Key Constraint violations, especially when attempting to load data into identity seed tables that have identical identity values as rows generated by triggers firing.

This required a three step process to move the database, but by setting the correct options anything is possible.

This year I set quite a few goals for myself both personally and professionally, and I blogged about them on my blog post Goals for 2009.  Looking back at them, I did really good accomplishing my primary goals for the year, dedicating a block of time to my family each day and graduating from the US Army Drill Sergeant Academy to further my military career.  I accomplished both of these goals, and my relationship with my family is stronger than I ever thought was possible, something that I am immensely proud of.

However, some of my other goals for the year were a bit more ambitious than I originally thought.

1.  Remain consistent in technical blog posts averaging 3 a week for the year.

• I failed here, but only by 1 blog post a week.  This year I wrote 113 blog posts, not counting this one, for an average of 2.17 posts a week.  I’d say I was pretty consistent, but not near 3 posts a week.

2.  Finish coding on the Extended Events Manager App to make it a one stop shop for creating, altering and consuming Extended Events in SQL Server 2008.

• I’d call this a success.  The state of this is well beyond what I would have ever imagined it would be a year ago.

3.  Continue to answer posts on the MSDN and SqlServerCentral Forums.

• I count this one as a success.  While my number of posts has decreased, I became more focused in what I was posting to, primarily Database Engine related threads, and not so much the TSQL related posts.

4.  Present at least one session at a major event, (ie TechEd, SQLConnections, PASS Summit, etc.)

• I count this one as a success as well, but only because I presented at PASS.  I need to work on becoming a better speaker in the future.

5.  Submit an article for publication at least once a quarter to an online site or magazine publication.

• I fell short here, missing the entire 3rd quarter of this year.  However, I have learned lately that some of my more complex blog posts could be turned into articles with a bit more work.  It really is a bit of a tradeoff between blogging for immediate publication and contributing to another source for delayed publication.

6.  Continue to present sessions at SQL Saturday and local Code Camp Events.

• I count this one as a success since I presented at SQL Saturday Orlando, as well as a couple of user group meetings.

7.  Complete my MCITP SQL Server 2008 Administrator Certification.

• This is another success, as I now hold the 2008 DBA and Dev certifications.

8.  Take the Oracle Certified Associate Database Administrator Certification test.

• This was an utter failure on my part.  I never even scheduled the exam, so no excuses for this one.  At this point I should have been working on my OCP or further, but my focus this year has been so much on SQL that I didn’t put the effort into this.  Was than an excuse?  Forgive me, I already said no excuses.

All in all, this year was a major success, and I look forward to planning my goals for 2010.

This year is almost over, and its sad and unfortunate that I can write a blog post about this topic, but there have been so many major examples of data loss from missing, damaged, or otherwise unusable backups this year. 

The year started out with a rollover from last years JournalSpace.com disaster that resulted in full loss of all of the blogs that existed on the site and their associated postings.  This was hotly talked about at the beginning of the year when the owners of JournalSpace announced that all attempts at recovery had failed, and they were not interested in trying to resurrect the site from scratch. 

JournalSpace Drama: All Data Lost Without Backup, Company Deadpooled
Brent Ozar - Why Backup? Ask JournalSpace

Not to long behind this at the end of January, the social bookmarking site Ma.gnolia suffered from data loss and corruption that ultimately led to its own demise.

Ma.gnolia Suffers Major Data Loss, Site Taken Offline
VOD-cast explaining the catastrophic nature of the data loss
Lessons Startups (and Users) can Learn From Ma.gnolia’s Crash
Brent Ozar - Backup Fail: Ma.gnolia goes under

Just as it became apparent that the ma.gnolia crash had caused unrecoverable data loss that ended the sites first implementation, it was announced that online backup company Carbonite had filed suit against it’s suppliers for the loss of customer data stored in its cloud based backup solution in 2007:

Online Backup Company Carbonite Loses Customers’ Data, Blames And Sues Suppliers (Updated)
Brent Ozar - Another backup failure: Carbonite
Brent Ozar - More On the Carbonite Backup Failures

Things were quiet for the most part over the summer, but then the Sidekick data loss issue occurred in October:

T-Mobile Sidekick Disaster: Danger’s Servers Crashed, And They Don’t Have A Backup

Then most recently online personality Jeff Atwood suffered a crash and significant data loss without a backup of his hosted virtual machines that he documented on his blog after getting it back online:

International Backup Awareness Day

A number of these were covered by Brent Ozar on his blog, but even Brent wasn’t able to escape the year without his own tale of failed backups in his virtual testing environment, though Brent’s scenario resulted in no actual data loss:

Brent Ozar - Bad News, Good News, Worse News

However, based on the events that transpired, Brent was able to put together a number of excellent blog posts that covered what the topic of backups:

Brent Ozar - Why Are You Backing Up?
Brent Ozar - Adding Reliability to Your Infrastructure
Brent Ozar - Mirrors aren’t backups

The major takeaway from all of these problems is that you don’t have a backup until you have tested the recovery of it, and there is no substitute for having an actual, “cold” backup of your environment that you can recover to a known point in time from.  Keep in mind that testing this backup doesn’t have to occur to equivalent hardware, a cheap and easy solution to testing your backups could be restoring it to a low cost commodity server, or in a worst case, desktop that has sufficient disk space to accept the restore.  At least using this methodology you know that your backup will be good when you need it the most.

As a DBA, my number one priority is ensuring that I have good valid backups that support my business SLA’s.  I manually run a report every Monday morning that checks my servers backup information using Multi-Server Query in SSMS 2008 to ensure that my backups over the weekend completed successfully, and in the event that I find a problem, this becomes my immediate focus until the report returns valid information to me.  After that I generally look into why I wasn’t notified of a failed backup if that actually occurred because I have automated alerting setup that should trigger an email notification to me if the backups fail on my servers.  To date I have been lucky in that I work for an employer that has understood the importance of backups.  However, I have done consulting work for a handful of customers where backups just aren’t the prevailing priority, despite having the risk explained to them.

Hopefully you’ve learned from the mistakes of this years disasters, I know that I have.