Ready Or Not, Here I Come!

A couple of months back, I conducted my first "training", kicking off a series of presentations to our internal development and QA teams.  I noted then how much fun I had, not only preparing the presentations, but giving them as well.  Since then, I've done two more sessions for the internal audience, the response to which has been very positive.

Time to step things up a bit...

The next few weeks will be full of firsts for me, as I step, no, let's say LEAP, well outside of my comfort zone.  Or what I thought was my comfort zone.  I say "thought" because I'm finding that I really enjoy doing these presentations.

The first first will come on March 21st at 1:00pm CST, as I participate in a discussion panel with Karen Lopez, Grant Fritchey, Tim Ford, Jorge Segarra, Stacia Misner, and Mike Walsh as part of the upcoming "24 Hours Of PASS" event.  This will be my first ever public "speaking" event, and I'm very much looking forward to it.

The second first occurs on April 21st in Madison, Wisconsin.  No webcam for protection this time - I'll be stepping out in front of a live audience as I take part in SQL Saturday #118.  I'll be presenting a condensed version of the presentation that I've done for my developer/QA audience, "10 Ways To Abuse T-SQL".  Small potatoes for the people that do this all the time, but a huge deal for a first-timer.  I can't wait!

T-SQL Tuesday #28: Why I'm Special(ized)

It's T-SQL Tuesday time, and the assignment this time is to describe what makes me special, err, specialized, in my career (hint, it's not my wardrobe).


Like many of you reading this, I can do a lot of IT-related things.  I can write HTML.  I can build a web server and setup a web site.  I can build and manage a network.  I can configure a router.  These are all skills that I've picked up along the way throughout my career.  I've been the proverbial "jack of all trades", even gone so far as being "the" IT guy at a previous employer.  In fact, at one point in the late 90's, I was planning to become a network administrator, and even obtained my MCSE on NT 4.0.

Around the same time, I was beginning to discover this great product known as SQL Server.  I had been a long-time database developer, working with cutting edge technologies like Foxpro, Clipper, and the ever-popular dBase.  One of my creations was a web-based help desk, running traditional ASP talking to a Foxpro database.  This worked well for a handful of concurrent users, but it quickly became apparent that something different would be needed.  Enter SQL Server 6.5.

The help desk app was modified to run against SQL Server, and I became fascinated with tuning the beast.  I thought Visual Foxpro was cool, but this was taking things to a whole new level.  It wasn't long before we upgraded to SQL Server 7, and I was completely hooked.  This was how I wanted to spend my time.  Unfortunately, beyond the help desk app, there wasn't going to be a lot of opportunity for me to focus on SQL at the current employer, so I decided to part ways.  I interviewed for, and was offered, a position as a pre-sales engineer for a web portal product that used SQL Server as its backend database platform.  I accepted, and packed up the family for the 900 mile move from Ohio to Minneapolis.

That was 13 years ago.  Since then, I have continued to sharpen my skills with SQL Server.  I'm now the senior DBA for a 24x7x365 global company, with a very busy OLTP system, backed by seperate SQL and Oracle data warehouses.  I've come a long way since the Foxpro days, I've become quite skilled with T-SQL and the SQL Server platform, but there is SOOOO much more to learn.  If I were to rank myself on a scale from 1 to 10, I'd put myself at a 6.  There are parts of SQL Server that I've never touched, simply because we have no use for them in our environment.

These past few months have brought about an unexpected shift for me.  I've found myself playing the role of mentor, teacher, trainer, not only to the other DBA's on my team, but to the entire development and QA groups within the company.  Equally unexpected, I'm branching out into public speaking!  I'll be participating in the upcoming 24 Hours of PASS (more on that later), and presenting at least once at a SQL Saturday event.  Seemingly overnight, I've taken huge strides down the path towards becoming a SQL Server specialist.  It's where I wanted to go all along, but I'm not sure I saw it coming.  It has been, and will continue to be, a heckuva ride so far!

Building (for FREE) a Virtual Server for SQL Demos

Did you see the news this week?  SQL 2012 has gone reached RTM!  

Have you downloaded a copy yet?  

If/when you do, where will you install it?  

Do you have an extra machine lying around that you can use?

If you're like me, you don't have.  Instead, you'll spin up another virtual machine on your laptop or desktop, install windows 2008 server for the 100th time, then install SQL Server.  The power and flexibility offered by virtual machines is incredible, but it can also be incredibly tedious trying to organize and manage them all.

I recently had to rebuild my desktop, and decided to use that as an opportunity to start fresh with my VM collection.  What I had before was a loose assortment of VM's, not really organized in any way.  I wanted to do things differently this time.  I found an excellent article by Jonathan Kehayias, documenting exactly what to do to build an entire VM lab.  At first, this seemed like what I wanted, but after spending some time following his steps, I decided that this is overkill for my needs.  I don't need an entire lab, I just need an instance or two with which to build demos and maybe practice some skills for certification.  I took a step back and started over.

There is one nugget of particular value that I got from Jonathan's article - the concept of cloning a virtual machine.  This is something I was not doing with my VM's before.  The potential space savings is tremendous, as is the speed with which new VM's can be created.

What follows is the process that I followed to create a reusable base image for a SQL Server 2008 R2 instance running on Windows 2008 R2 Core.  I couldn't find the entire process documented from end-to-end, so I decided to do that here, perhaps someone will find it useful.

To start, you'll need to download some software:

Download Virtualbox
Download Windows 2008 R2 ISO from MSDN or from here
Download SQL Server 2008 R2 ISO from MSDN or from here
Download Adventureworks2008R2 (I'm using the version without FILESTREAM data)

Install Virtualbox, I left all options at their defaults.  Click the "New" button to create a new virtual machine, the "Create New Virtual Machine" wizard will appear.  The screenshots below show the process and the options that I chose:

My host machine is a quad-core box, so I allowed my VM to use four processors.

I changed the network adapter to a "Bridged Adapter", so that my virtual machine will be visible on my LAN just like any other machine.

The wizard will power on the new virtual machine, which launches the "First Run Wizard".

When prompted for the installation media, provide the location of the Windows 2008 Server ISO file that was downloaded.

Click "Start" on this screen will boot the VM, using the Windows 2008 Server ISO and the boot medium.  This will launch the setup for Windows 2008 Server.

I chose to install the core version of Enterprise Edition. I don't plan to need the GUI features of the operating system, and by using the core version, the patching requirements are greatly reduced, as is the overall footprint of the operating system.

Once the Windows install completes, the VM will reboot, and you'll find yourself being prompted to login to the operating system.  Since I chose the "core" version, my instructions from here on are targeted to that version.  Instructions for the other version of Windows may differ.

There are certain pre-requisites required before SQL Server can be installed.  These are simple to install, using the following commands:

dism /online /enable-feature /featurename:NetFx2-ServerCore

dism /online /enable-feature /featurename:NetFx3-ServerCore
dism /online /enable-feature /featurename:NetFx2-ServerCore-WOW64
dism /online /enable-feature /featurename:NetFx3-ServerCore-WOW64
dism /online /enable-feature /featurename:IIS-WebServerRole
dism /online /enable-feature /featurename:IIS-ISAPIFilter
dism /online /enable-feature /featurename:IIS-ISAPIExtensions
dism /online /enable-feature /featurename:IIS-NetFxExtensibility
dism /online /enable-feature /featurename:IIS-ASPNET

In order to access SQL Server from outside the VM, you'll need to allow connections through the Windows firewall, or disable it.  I chose to disable it entirely:

netsh advfirewall set allprofiles state off

We'll be using this VM as the basis for other ones, so we'll want to make sure this one is current on patches and updates. This is easily done through the configuration utility "sconfig", included in Windows core:

You can choose to install all Windows updates, or just the recommended ones. I opted for the recommended ones. After the updates have installed, the VM will reboot once again.

After the VM reboots, login again. Unmount the Windows ISO (Devices -> CD/DVD Devices -> Remove disk from virtual drive). We now need to install the Guest Additions for Virtualbox - these are needed so that we can access files on the host operating system. Install the Guest Additions (Devices -> Install Guest Additions). If the installer doesn't start, run it manually - on my VM, this was done by running D:\VBoxWindowsAdditions.exe.

Allow the VM to reboot again. Upon restart, you'll be prompted to login again - there's no need to login at this point.  You're finished with the Windows install, and now have a clean virtual machine running Windows 2008 R2 core.  Shutdown the VM cleanly, we're done with it.

 We can now use this clean Windows VM as the basis for any number of additional VM's that we want to create.  Let's do so now.  Right-click on the VM in the Virtualbox console, and choose Clone:

I named my new virtual machine "SQLDEMO":

This step is key to significant disk space savings.  A "linked" clone will share elements of the disk image from which the clone was created.

Once the clone has been created, we can power it up.

You'll be prompted to login to a Windows instance that looks remarkably like the first VM that we created. In fact, it's "virtually" the same instance, identical in every way.

Let's start by changing the computer name of this cloned VM. Run the sconfig utility, and choose the option to rename the computer.  Let's use SQLDEMO for the computer name, to match the name of the VM. You'll be prompted to reboot the VM, go ahead and do so.

We're now going to install SQL Server. To do that, we need to mount the ISO that was downloaded earlier. Click on Devices -> CD/DVD Devices -> Choose a virtual CD/DVD file, and navigate to the ISO file.

I'm going to assume that you're familiar with the installation procedure for SQL Server, and that you know which features you want. I'm not going to detail that installation process here. To being the installation, run the setup program (for me it was on drive D:):
D:\SETUP 

After the SQL Server installation has finished, the last thing to do is to attach a copy of AdventureWorks. You don't have to do this, but for me it was important. I want to be able to build demos that other people can use, and AdventureWorks is something we all have access to.

We've downloaded AdventureWorks already, it's sitting someplace on our host machine. We need to get access to that from within the VM, which is why we installed the Guest Additions.

My download of AdventureWorks is sitting in "C:\DVD_Images". From within the VM, create a shared folder mapped to that location (Devices -> Shared Folders), name it DVD_Images.

Return to the Windows command prompt, change to the SQL Server data folder (The default path is C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data).

Copy the AdventureWorks data file from the host location into the VM (copy \\vboxsrv\DVD_Images .). The name "vboxsrv" is an internal reference to the host machine.

Launch Management Studio (on the VM or on the host), login to your new virtual SQL Server instance, and attach the AdventureWorks data file:

CREATE DATABASE AdventureWorks2008R2
ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\AdventureWorks2008R2_Data.mdf')  -- change the drive and file path
FOR ATTACH_REBUILD_LOG;

Shut down the VM now, don't make any further changes to it.

At this point, you have a clean SQL Server 2008 R2 instance, running on a clean Windows Server 2008 R2 Core machine. As with the original Windows VM, you can now clone this SQL Server VM to create any number of SQL Server instances that you'd like.

See how easy that was? Want to experiment with mirroring? Simple, clone this VM to create two new VM's, change their names using sconfig, and experiment away. Want to practice upgrading from SQL 2008 to 2012? Create a clone and upgrade. Repeat as many times as necessary until you're comfortable with the upgrade process. The uses for this cloning technique are endless.

All Those Little Things

It's the first Monday of the month, which means it's time for Meme Monday.  The assignment this month is to list all of the things that we do as a SQL Server DBA.  This list will be unique for each of us, but there will be quite a lot in common.

Going into braindump mode now - in no particular order, here's my list:

• backups and backup verification
• monitor performance
• respond to after-hours problems
• repeatedly disprove "must be a database problem" reports
• install SQL Server instances
• tune inefficient T-SQL code
• mentor developers and peers
• assist developers with database changes
• import data
• argue database best practices with senior architects
• secure databases
• secure sensitive data within databases
• secure backups
• work with auditors during security and process audits
• work with network admins (log-shipping, mirroring, firewalls, other need)
• work with server/SAN admins (disk space, hardware upgrades)
• everything that I missed goes here

What about you?  What does your list contain?

Go Jump In A Lake!

Last year I crossed off one of my "bucket list" items - I took part in the Minnesota Polar Bear Plunge.  This is one of those events where a bunch of crazy brave people jump into a frozen lake, through a hole cut in the ice.  Turned out to be a lot of fun, both in the preparation for the event, soliciting pledges, the event itself, and the documentation of the act.  In the end, I raised over $750 for the Special Olympics, earned myself a great jacket, and put together a fun video that everybody got a laugh out of.

This year, I'm tapping into the awesome power of the SQL Server community.  We have hash tags for everything, so in keeping with that tradition, I've coined the tag "#sqlplunge" for this endeavor.  I've also formed a plunge team, Team #SQLPlunge, registered to jump in Eden Prairie on March 10th into Lake Calhoun on March 3rd.  I have tentative commitments from Jason Strate (blog | @StrateSQL) and Andy Lohn (blog | @SQLQuill), and will be actively trying to recruit other SQL Server professionals (or rookies, or pretty much anybody who wants in) to join the team.

My personal donation goal this year is $1000.  I haven't set one for the team, but I'm expecting big numbers in response from the #SQLFamily. in particular the local folks of @PASSMN.  We have FIVE WEEKS to pile up the donations.  Gentlemen (and ladies), start your pledges!

Come join the fun.  It's painless, you earn bragging rights, and just might get hooked.  You'll stand outside in the cold for a few minutes, then jump into relatively warm water (compared to the outside air, it feels like bathwater), and then make the mad dash back to the changing room before your clothes freeze.  It's a blast!

My personal pledge page:  http://www.plungemn.org/plunger/tracymckibben
The Team #SQLPlunge page:  http://www.plungemn.org/team/teamsqlplunge

If you're brave enough to join us, post a comment below linking to your pledge page, and be sure to join "Team #SQLPlunge" when you register.