Wednesday, December 21, 2011

Who Done It?

Have you ever had a production application or database go offline for no apparent reason? What would you do?

I recently encountered a situation with a client where a production application was not running and the database appeared to be inaccessible. Predictably enough, the developers were surrounding the DBA trying to get him to restart SQL services or possibly the entire server. It fixed it last time…

sp_who2

sp_who2 had been run, but would not return positive results. Instead, it gave output similar to the following:
Msg 952, Level 16, State 1, Procedure sp_who2, Line 89
Database 'DATABASENAME' is in transition. Try the statement
later.

Then, when you tried to expand the view for this database in SSMS you would get the following error:

Database Not Accessible

dbcc checkdb had been run, and gave these results:
Msg 2520, Level 16, State 5, Line 1
Could not find database 'DATABASENAME'. The database either does
not exist, or was dropped before a statement tried to use it.
Verify if the database exists by querying the sys.databases
catalog view.

They were getting ready to look at restoring from a backup. Wait a second hoss…

DMVs to the Rescue

When I was asked to look at this, I went straight to the DMVs for help.  In fact, if you go back and read the error message, it tells you to do what I'm about to do next.

First, I queried sys.databases to verify the status of this database:

[sql]

-- check status of databases
select name, state_desc
from sys.databases
where name = 'DATABASENAME';
go

[/sql]

This confirmed for me that the database was present, but offline.

Suspicious Minds

I have to admit, I was suspicious of the developers; they kept letting out little hints that they knew the database was offline before anyone had looked at it. So I did a little more digging…

Next, I ran another DMV query to find out what activity was happening on the server:

[sql]

-- current activity on the server
select r.session_id, s.host_name, s.program_name, s.login_name,
DB_NAME(r.database_id) as 'Database', r.status,
r.command, r.percent_complete, st.text
from sys.dm_exec_requests r
join sys.dm_exec_sessions s
on r.session_id = s.session_id
cross apply sys.dm_exec_sql_text(r.sql_handle) st
where s.is_user_process = 1
and r.session_id <> @@SPID
order by r.session_id;
go

[/sql]

These results showed me that someone had just issued the following command:

[sql]

-- the offending command
alter database [DATABASENAME] set offline;
go

[/sql]

Connecting the Dots

I was able to tell that a user had run this command from a desktop computer, using SSMS, and was logged in with the application’s SQL login credentials.  Suddenly I'm thinking of CLUE...

The reason we were getting the ‘in transition’ error is because several developers had multiple sessions connected and were blocking each other from finishing.

Once we had this basic information, we were able to have the Network and Server teams track down who the user was. At this point in time, all the developers that were surrounding the DBA disappeared and disavowed all knowledge of anyone running anything.

Kill 'Em All, Let the DBA Sort 'Em Out

Next, we killed the offending session:

[sql]

-- kill the offending session
kill SESSIONID;
go

[/sql]

After the session was killed, it was time to check the consistency of the database.

Run checkdb:

[sql]

-- check database consistency
dbcc checkdb('DATABASENAME')
with all_errormsgs, no_infomsgs;
go

[/sql]

Everything looked good, so we were ready to bring the database back online:

[sql]

-- bring the database back online
alter database [DATABASENAME] set online;
go

[/sql]

And of course, run a new FULL backup and save off the existing FULL and LOG backups in case they were needed later.

Post Mortem

In the post mortem, we discovered a few things...

The login passwords for DEV and PRD were set to the same value. This allowed the developers to obtain front door access to the production servers. Needless to say, this was immediately changed.

This also prompted some interesting discussions around security best practices and the need to verify backup files in case you ever need them.

Remember, disaster can strike at any moment. The key is to remain calm and work your way through the problem. Take every setback as a learning experience, and use it to make the customer’s environment better.

Forgiveness

Also, resist the urge to throw someone under the bus. Rather, try to help each other understand the reasons for security and separation of duties and environments. Look at situations like this as a learning opportunity, not a burning opportunity.

Monday, December 19, 2011

Cloning a VirtualBox Guest

Cloning a Virtual Machine is a great way to leverage your virtualized environments and spin up test servers with ease.

First, create a base server to use for your clones. Try not to modify this one too much. Perhaps set the time zone and display resolution. If you change too much, you may end up modifying your clones later on.

Next, right-click on your base server and pick clone.

Clone the Base Guest VM

Give the clone a new name. Be sure to reinitialize the MAC address of your network cards.

Reinitialize the MAC Address

A full clone will make a separate copy of your base server. There will be no dependencies on the base server from this point on.

A linked clone will depend on your base, and will keep track of the differences between them. This can result in a great amount of disk space savings. However, your clone will be dependent on the base server.

Full Clone or Linked Clone

Hmmm...this is taking a while.  Let's go watch The 6th Day or perhaps The Island.

Wait of the Clones

OK, my clone is finished.  That was way easier than it is in the movies.

Your Clone is Ready

And there you have it!  Time for fun…

Wednesday, December 14, 2011

Highlight Important Emails

Do you find yourself overwhelmed by the volume of email you receive on a daily basis? Me too. Well, there’s not much I can do to help you stop it, other than say log off and stay logged off. Unfortunately, we all need to work to pay our bills, so we need another solution.

Here’s a quick tip I learned from LifeHacker that will help you filter the most important emails. This tip works on the theory that if an email is sent directly to you, then it is more important. If it was sent to a group that you are a member of, then it is less important.

While this heuristic may not cover everything, it does peel away a significant number of emails for me.

In Outlook, click the View menu, and then Click the View Settings button.

View Settings Menu

Next, click the Conditional Formatting button.

Advanced View Settings - Conditional Formatting

From there, you will be presented with the Conditional Formatting screen. Click Add to create a new Rule. I named mine ‘Highlight to me.’

Conditional Formatting

Next, click the Font button and decide what color you want to highlight your messages. I chose blue.

Choose the Font

Then, click the Condition button and setup the rule to filter your messages.

Define Your Condition

And voila, all messages that have my name listed in the To: or Cc: lines will highlight blue. Now, I can quickly scan and find all the messages that are of higher import.

Mail Highlighted Blue

I hope this tip helps you cut through the daily deluge of digital distractions that are depriving you of your destiny.  Code on!

Monday, December 12, 2011

Enabling Hardware Virtualization

Recently, I got a new server, and I've been dying to setup virtualization and create a new test lab.  I have 64-bit hardware and Windows 64-bit.  Then, I downloaded and installed both VirtualBox and VMWare Server.  Next, I downloaded the Windows 2008 R2 SP1 64-bit ISO.

Imagine my surprise when I got the following messages when trying to setup the Guest OS:

Virtualization Error

And this:

VirtualBox Error

Even though my server was 64-bit all the way, I still needed to enable Hardware Virtualization at the BIOS level if I wanted to create 64-bit Guest Operating Systems.

I got the same message when I tried again with VMWare Server:

VMWare Error

And I got the same thing when I used Microsoft's Hardware-Assisted Virtualization Detection Tool.  Whew, that's a mouthful!

Hardware-Assisted Virtualization Detection Tool Error

CPUID
The first thing I did was verify that my CPU actually supported Hardware Virtualization; just because you have a 64-bit machine does not mean it will handle Hardware Virtualization.

CPUID is fantastic, free tool that serves many purposes: it will identify Hyperthreading, CPU Throttling, and Hardware Virtualization.

CPUID VT-x Error

What I'm looking for here is the presence of the VT-x Instruction Set.  Looks like I have it, so I'm golden.  Now, I just need to figure out how to enable it.

Another way to verify if your CPU supports Hardware Virtualization is to check the manufacturer's website.  Both Intel and AMD list their processors and allow you to verify this.

BIOS

In order to enable Hardware Virtualization you will need to change some settings in your BIOS.  The problem here is, every computer's BIOS will be a little different.  You may have to hunt around in a non-obvious location to find it.  Mine was hidden under Security Settings.  I guess it's like they always say, it's in the last place you look...

Happiness is a Virtualized CPU

Once I took care of that, is was Blue Skies Again.

Hardware Assisted Virtualization Detection Tool Success

Now to work on creating my Virtual Sandbox...