Monthly Archives: February 2014

Runaway Transaction Log: part 1

Miscellaneous Tech Explorations

Last week I wrote about an idea to create labs for when things go wrong in SQL Server. They are partly inspired by the TechNet Virtual Labs, which are a great resource available for free. Another motivating factor is a desire to do something with Windows Azure. I’m new to the virtualization world and want to learn more about it. For someone like me who doesn’t have a lot of lab resources at home, it offers some exciting opportunities. Finally, I’m just plain frustrated with the fact that most DBA training involves frantically struggling when something goes wrong in production.

Today, we are going to create a SQL Server virtual machine. Later on we will misconfigure it with a runaway transaction log. This article covers just the basics. If you want to learn more, you may want to read  Getting Started with SQL Server in Windows Azure Virtual Machines

First log into your Azure account. If you don’t have one, you can create one pretty easily and you will have $200 in credits to play with the first month. Be aware that after the first month you will be paying for storage, even if your machines are turned off. Once you are logged in, go to virtual machines:

image

Then on the bottom left corner, select NEW –> Compute –> Virtual Machine –> From Gallery

image

The gallery has a lot of options available and even more if you know the cheat codes to find the hidden VM Depot. If we take a look at the SQL server category, we see images available for 2008 R2, 2012 and 2014 CTP.  This lab should work for any of those, but in this case we will be picking SQL Server 2012 Standard.

image

Once you’ve chosen your image, click on the next arrow. On the next screen you are going to pick some configuration details. I would pick the latest release date. Medium size should work for out needs regarding compute. WARNING: Be aware that Standard on a medium instance will cost you $0.73 per hour.  If you accidently leave it running all day, that can be $18 per day. I’ve done that before, so be careful.

image

Next we need to set the cloud service. You can think of this as a sort of container for the VM’s. In our case, we’ll make a new cloud service. You are going to want to pick a region close to you.

image

Finally, we are going to set the endpoints so we can talk to the machine.

image

Once you are done with all of that, it’ll take about 10 minutes to provision the machine. Go take a walk and come back.

Are you back? Okay, let’s go back to virtual machines and take a look at our machine.

image

It looks like it’s up and running.  Next, we are going to create a very small data disk for us to fill up. If you want more information, this article covers attaching a disk in more detail.

Select the machine and click attach at the very bottom. Then select attach empty disk.

image

We want to select 1 GB as this is the smallest size we are allowed.

image

Now we have everything we need to log in and start breaking the machine. Next week we’ll configure the machine to run into some transaction log issues!

An idea: SQL Server crisis labs

SQL Server

Many people in the SQL Server world have become DBAs by accident, myself included.  The problem with being an accidental DBA is that you usually learn things the hard way: when something is on fire. At that point, you are under intense pressure to solve everything NOW. It’s a real pain.

Of course, there are a number of great resources if you are learning SQL Server from scratch.  Here are two of them that have helped me:

These will lead you in the right direction, but the real challenge is learning how to deal with problems while under pressure. Reading about a problem isn’t the same as dealing with it first hand. What I think is really exciting is the idea of a safe environment where you can solve a problem without any risk.

Enter Azure (or any virtualization solution). Azure provides a really fun opportunity, in my mind. You can spin up a virtual machine with SQL in a matter of minutes. Then you are free to break things to your heart’s content. So why not take that a step further? Why not create labs where things are intentionally broken and your job is to fix it. Your only guides are a vague error message and your Google-Fu. Just like in real life.

So, I’m going to start a series of posts on how to create intentionally broken VM’s to train your junior DBA’s on. Next, I’ll start creating Powershell scripts to automate the creation process. Finally, we’ll take a look at consolidating those scripts to create a single SQL machine with a lot of problems that need fixed.

For the first lab, I’m going cover how to create a machine with an out of control transaction log. Please feel free to suggest any ideas or provide any comments.