Transactions and oh yeah, that First Kiss…

This is kinda a pain to read. I know, I re-read it before publishing. Well blame it on Tim Ford (blog | twitter) and this challenge. Tim asked, what is a transaction? How do you explain it in a non-technical way? He said “non-technical”…he didn’t say “non-cheesy”. It ain’t easy being cheesy so humor me.

Oh that First Kiss
Yes, that darned first kiss. His name was Ricky, the jerkmy first love. I knew it was eventually going to happen. I daydreamed about it, thought a lot about how to do it, asked my friends about it, and pretty much geeked (researched) my way to it. With all the thinking and feeling I did prior to “the event”, the whole kissing thing already seemed and felt so real. But experiences like this cannot be “just” imagined…it’s atomic…all or nothing. It either happens or it doesnt.

When he asked me to close my eyes, I thought, OMG, am I really going to do this? And the moment I did, I had that momentous FIRST kiss with that jerk my first love.

Everything else faded. Even the voice inside my head (not mine but my mom’s) stopped talking. It was actually happening. Just the two of us. We both reveled in the isolation.

And then it was over. That kiss changed me. I was in a consistent heaven state for a long time. It didn’t matter where I really was…in school, at home–I was in heaven.

Years have passed. Boyfriends have come and gone. There’ve been second, third, fourth and more kisses. But still, nothing beats that first kiss. There’s something about FIRSTs that just stays with us no matter what. Some durability that’s unique to all our FIRSTs.

Okay, enough. I’m stopping.
Yeah, that was kinda a pain to read huh? Come on, humor me. Aren’t you tired too of all those debit-credit bank examples to illustrate transactions??

So, how is this dorky kissing event even remotely related to what a transaction is?

Transactions are atomic.
It’s all or nothing. It either commits or aborts. Just like that kiss. It doesn’t matter if I practiced with a pillow a thousand times (yeah, looking back, that was disgusting). Until the kiss actually happened, it really actually didn’t happen. The kissing plans could be rolled back; the actual kiss? No way.

Transactions are isolated.
There could be a million other events going on while that kiss was happening. There could have been a guy wishing he was in Ricky’s place (oh-ho!) but nothing else could mess with that transaction…nobody else could take away that first. That transaction owned me!

Transactions are consistent.
That kiss left me in heaven–and if that’s not a logical state to be in–I don’t know what is. A kiss that special wouldn’t have allowed me to be in an illogical state. Happy, yes. Illogical, no.

Transactions are durable.
Even an earthquake couldn’t have stopped that kiss the moment it happened. In the past years, I’ve experienced a lot…but hey, the first kiss will always be the first kiss. I can’t destroy it nor recreate it. I can’t pretend that it didn’t happen because (thank God) it really did.

Conclusion
No conclusion. I’m already feeling so bad I wrote this. It’s (r)ICKY and cheesy…but, hey, it’s non-technical! And maybe, just maybe, I made it all up…:D

Ricky, please dont get ahead of yourself. I was really just forcing an analogy. And no, it wasn’t THAT good.

Of friendships, backups, and false analogies

Of Friendships
Today is a sad day. I had to say goodbye to a friend. It’s funny how common sense sometimes eludes even the smartest among all of us. We have friends who treat us like nulls or defaults and we bear it and pretend it is okay. Common sense says we deserve better but we somehow always remain optimistic that one day, these friends will realize that we are not nullable, or that even if we have defaults, it doesn’t mean we don’t deserve values.

But optimism doesn’t change the rules of the game: we can’t “just” alter people.

Of Backups
I had to write something related to backups for work the other day. I attempted to discuss it with my husband who doesn’t know anything about databases. If you are a member of the SQL Server community, know that someone who’s living with me thinks you’re an alien. The things we get excited about are reduced to “stuff”, the experts we admire are “oh-those-twitter-people”, and next to UFC, we’re like UFOs.

Anyway, I still like running things past him especially when I am preparing to explain the same concepts to non-technical people. He nods and says uh-huh in the right moments–so good enough. On good days, he even asks questions. To give him a background, I explained the different types of backups to him. He actually asked, “there’s more than one??!” I just ignored him, got a paper, and started explaining. I said…

Imagine a long path from point A to point E.

The goals are

-to get from point A to point D and
-to gather maps so you can revisit any point in the path

In each point, there are maps available: a full map, a diff map, or a tran map. (you want to explain file and filegroup backups? Be my guest.)

1) When you walk to point B, you can get a full map. “Nothing else?”, he asked. Yup, nothing else. The full map allows you to go back to point B later. You always start with a full map.
2) When you get to point C, you can get a diff map and a tran map. The diff map will bring you back to the same point you picked it at BUT ONLY if you picked a full map previously. A diff map is useless without a full map. And remember, you can’t just use any full map. It has to be a full map taken right before you take the diff map.
The tran map will bring you back to the same point and like the diff map, you can only use it to go back to C later if you picked the full map in B. You can also use the tran map to go anywhere between B and C.
“Can I pick all maps?”, he asked. I said yes.
3) When you get to point D, you can again get a diff map and a tran map. This is your second diff map and it will bring you to point D later. “Do I need the other diff map I got in C to go back to D?”. I said no. But you do need the full map you got in B. Diff maps always work in partnership with a full map. And as I said, it can’t be just any full map. It has to be…he said “yeah, yeah, I know. It has to be a full map taken right before this diff map. Right now, it’s the one I took in A”. The tran map will allow you to go back to D and in between C and D but—you need the previous tran map (in C) and the full map in B. Or you can use it with the full map in B and the diff map in C.

I asked him if he understood so far. He said kinda. I then showed him backup types as described in MSDN and he read the overview. He said “that’s actually simpler. Couldn’t you just have said it like that?”

Urgh! My teaching and analogy skills need work!

When we were about to sleep, my husband asked me, “So how was I supposed to go back to the starting point so I can use the maps?” Like a sore loser I replied, “Don’t ask me; ask MSDN.”

Disaster recovery? High Availability? Who cares?

When I was still a database developer, every time I received an email saying that the “dev(elopment server)’s down” or “prod(uction server)’s down”–I would 1) not panic and 2)not freak out. To me, it really just meant longer coffee breaks. (Yeah, time I could have spent studying or filling up my Journyx timesheet but heck, I was young, I was restless, and break times were good! The longer, the better.)

Customers on the other hand were screaming. Management and users were freaking out. And the DBAs were in deep s***. As long as I could test on my local db, I was okay. I didn’t even know about service level agreements. My only SLA, a term totally foreign to me then, was to fix bugs and complete enhancements on the day they were due. Life was easy.

For the DBAs, life wasn’t. I’m pretty sure they had some DR and HA strategies implemented. Were they good ones? Maybe. Maybe not.

I really didn’t care.

Thomas LaRock
In time, I did learn to care. And in time, I actually got paid to care. If I wasn’t careful though or believed everything I read, I’d be one of those saying this “If we have HA, then we don’t need DR”. And yes, you would probably be able to slap me with Thomas LaRock’s permission. High Availability (HA) and Disaster Recovery (DR): these two concepts get mixed up as often as Gordon Ramsay says BLIIIIP in Hell’s Kitchen. Seriously. Shouldn’t come as a surprise though. Afterall, some people think REPAIR_ALLOW_DATA_LOSS does not cause data loss.

So kudos to anyone who exerts effort to introduce concepts like HA and DR in a way so understandable you simply just can’t mess it up. Exactly as Thomas LaRock described them here: SQL University – HA/DR Week.

My friend once told me, if you put too many people up on that pedestal, it may get crowded. And sometimes, you may have to push people off it. When I read articles like this, I’m reminded why I pushed someone off the pedestal to get Thomas LaRock in (sorry, Mom!). Articles like this, introductory as they are, are what make an expert a teacher. When an expert goes down a few notches to explain something to make you understand and to simplify that which to you may be complex, it is then that he becomes a mentor and more than just another smart guy on the street. Smart techies are admired; mentors *inspire*. And when you’re in a community, isn’t that what really counts?

Teaching is a challenge. The responsibility is enormous. So to Thomas LaRock and everybody else in the SQL community who bother to make learning easy–thanks! One may say, “But Janice, that article was so basic. Why make a big deal out of it?” Ever heard of the saying “get the head right and the rest will follow”? You see, getting the head right is *not* easy. Giving a good foundation is difficult especially when you’ve been doing something a looong time. Basic concepts become “just” common sense. Experience convolutes definitions with best practices. Problem solving takes 2 steps instead of 20–and you instinctively just know what to do. Imagine having to step back and see what you know from the eyes of a beginner. Imagine having to figure out how to make learning things that you probably had to learn the hard way, fun. No, it’s not that easy. So yes, it is a big deal.

Getting the head right is tough but if you’re able to do it, the rest *will* follow. And it is oh-so-blip-worth-it.

CONCLUSION
Imagine a blank sheet of paper. Imagine that it’s absolutely critical that you write on it. If somebody spills water on that blank sheet of paper, your DR strategy is to leave it to dry. See the other blank sheets of paper you have in your drawer? That’s your HA strategy.

See? I get it.

Is restoring a transaction log backup a logged operation?

I was reading on logging recently and I wondered, when I restore a transaction log backup, do the rows get “re-logged” to the current log? I didn’t think they would be. At most, I thought, they’d be logged minimally. I doubted that the transactions would get logged again. I did a quick test.

1. First, I created a test database, set it to full recovery model, and created a full db backup.

USE master
go
--Create the test database
CREATE DATABASE TestDB;
GO

--Set the recovery model to full
ALTER DATABASE TestDB SET RECOVERY FULL;
GO

--Full database backup
BACKUP DATABASE TestDB TO DISK = 'c:BackupTestDB_Full.bak';
GO

2. Then, I checked how much space was used at this point.

DBCC SQLPERF(logspace)

And the result for TestDB was (not all columns included):

Log Size (MB)	
.5390625      

3. Then, I ran the following script to fill the transaction log:

--Fill the transaction log
USE TestDB;
go
create table myTable (myNo int, myDate datetime)

DECLARE @a INT

SET @a = 1

WHILE @a < 20000
    BEGIN
        INSERT  INTO myTable ( myNo, myDate )
        VALUES  (
                  @a,
                  GETDATE()
                )
        SET @a = @a + 1
	    End

4. I checked the log space again.

DBCC SQLPERF(logspace)

The result’s:

Log Size (MB)
11.17969

5. Next, I performed a transaction log backup.

--Perform a transaction log backup
BACKUP LOG TestDB TO  DISK = N'C:BackupTestDB.trn';
GO

6. Then I restored the full database backup and transaction log backup to a new database called TestDB2.

RESTORE DATABASE [TestDB2] FROM  DISK = N'c:BackupTestDB_Full.bak' WITH NORECOVERY,
      MOVE 'testdb' TO
'C:testdb2.mdf',
      MOVE 'testdb_log'
TO 'C:testdb2_log.ldf'
GO
RESTORE LOG [TestDB2] FROM  DISK = N'C:BackupTestDB.trn' WITH  RECOVERY
GO

7. And then I checked the log space again…

DBCC SQLPERF(logspace)

And the result was:

Log Size (MB)
11.17969

The size of the log after restore and the size of the log when we executed the fill-up script are the same. So it would seem the transactions do get re-logged during a restore.

Conclusion: (update)
The answer is Yes. A transaction log restore comprises of two steps: data is copied to the transaction log then rolled forward.