Monday, April 11, 2011

Your Training is Approved! How Do You Get It Again?

With the upcoming SQL Rally there has been a lot of discussion amongst the community of how to help people ask for training. 

One of the first things to go in a tight economy is being sent to Training.  There is a lot of great information out there for free.  There are great blogs, videos, SSUGs (SQL Server User Groups) and not to mention SQL Saturdays.  As a DBA if you are looking to improve yourself there are a lot of great resources available to you.

“So Ball’s do you want me to go to Training or what?”

Glad you asked Dear Reader, yes I do.  The one thing that free training lacks that in-person training provides is social interaction.  Not only does this help you personally but professionally as well.


Two words, simple concept, Social interaction.  There are a lot of things that makes up a DBA’s job, and I’m not just talking Job Description.  What sets apart a really great DBA from others?  It’s not just what they know, don’t get me wrong you have to be knowledgeable in your field.  What sets a great DBA apart is something more fundamental than that, it is their social skills.

Social skills are just like any other that we have, if you do not utilize them then they rust.  If you practice them then they flourish.  When you are at a SQL conference, SQL Saturday, or SQL User Group this is your opportunity to stay sharp.  There is a lot of good information being passed around, ask questions, make points, discuss what you’ve done, and learn something.  This will help you when you get back to your job.

I would ask you to close your eyes and think of a meeting you’ve had at your company.  There is a complex technical issue, perhaps a production environment is down, and the Users/Sales are impacted.  How do people work together?  Do ego’s come out?  Is your opinion listened to and openly discussed?

 I cannot tell you how many MVP, Authors, and in-general SQL Legends that I’ve met that are nice, humble, and willing to listen in a complex technical setting.  If you do not work with a team of DBA’s, or if that team is not a cohesive team, then this is a refreshing experience.  Being in person with people from the SQL community is a treat and can help you use Technical  Social Skills that you can utilize in other areas of your job.

How To Ask

For great blogs on HOW to ask for training check out Kendal Van Dyke (blog|twitter) and Brent Ozar (blog|twitter)

If you need help on how to approach your boss, Kendal wants to help he is very available through his blog, or also on twitter.  Anything short of him having to pay your way, and he’ll be glad to help J.

Brent presents a lot of wonderful ways for you to approach your manager.  He also answers his comments, (as you can see by reading them), so if you have a situation that you don’t feel he covered ask him!  He is also readily available on Twitter.

***************UPDATE 4/11/2011**********************************************

Later in the day while at work I discovered that the Awesome people putting together SQL Rally had a great ROI section that was posted in March.  Not only are there wonderful arguments listed as to why you should go, but there is also a great form letter that can help you ask to big boss to be able to attend.  I had to include the links to this.




I want to focus on the idea that you have been APPROVED to go.  Your SUPER EXCITED, and you should be!  But through the haze of the excitement you need to consider a few tings.  You don’t want this to be a one shot deal.  You want to go to training again.  So how do you make that happen?  You need a plan.  So ask yourself the following:

1.       What am I going to Learn?
2.       How Will I apply it when I get back?
3.       How do I show my Boss the Training was worth it?


So your approved to go, now is the time to look at the track’s being offered and determine what your 1st, 2nd, and 3rd choices are for each time slot.  But as you decide I’d like you to think of any issues or new technologies that are being used by your company.  Try to set yourself up to be a returning superstar.

You should think about the projects your working on, or the upcoming one’s you know you’ll be roped into working on. 

If you are working on a project to get your arms around your SQL Environment then look for Sessions on Policy Based Management, Auditing, and Powershell. 

If you are looking at adding High Availability to your environment look for Sessions on Disaster Recovery, Mirroring, Replication, and Clustering.  If Performance is where you need to concentrate look for Sessions on Indexing, Wait Stats, and Compression. 

The point is knowing where you are going and what you NEED to learn to get there will help you achieve some WINNING in the long run.


So now it’s Monday and your back at the office.  Your inbox is not Zero, you’ve got meetings, and a to-do list a mile long.  What did we get out of our trip?

While things are fresh it is easy to be excited and ready to go, back at the office reality sets in.  Now is when I like to make a list.  You should do a top 5 things that you learned from training that you want to apply to your job.  If you have co-workers that went to training with you, you can talk to see what their top 5 things are, and align them to work together.

We attended some training a couple months back, and when we returned I had a list of what I thought the top 5 things we could apply were.  The number 1 thing that I wanted to do was pretty low on the list.  After talking with all the other DBA’s it turned out that was the number 1 thing on their list, which they thought could benefit the company right away.

So now that you’ve got your list, and your co-workers are on board, set up a road map of how you will present this to your boss.

Let’s take Policy Based Management for example.
  1. Find a Dev, Test, or local SQL Server Instance on a Laptop that can Server as your Central Management Server.
  2.  Register the instances you would like to run Policy Based Management Against. 
  3. Work with your co-workers, determine policy’s that are most beneficial to your environment. 
  4. Set them up and Run them.
  5. Demo it for your Boss
There are so many features in SQL Server to take advantage of, that it is rare to find a place that has it all figured out.  There is probably something that could benefit your work place that you are not currently using:  Snapshots, Mirroring, Backup Compression, Filtered Indexes, Sparse Columns, Policy Based Management, Data Compression, Central Management Server the list is long. 

If you can find something to apply, and actually get it in place, then future training should be easy to obtain.

What if you didn't find anything new?  What if you were the only DBA to go?  When you come back try to share the wealth.  You just spent some time watching a lot of people present on topics, now is your chance to try with your co-workers.  Work up some of the topics where you learned or gained a clearer understanding of how something works.

  1. Work up a Summary of the Topics and some Demos (grab the decks and scripts if they are available for download)
  2.  Set up a meeting with your co-workers
  3. Present the topics
  4. Get Feedback, go back to the top and start working on your top 5 list with your co-workers on board


Here’s the big payoff.  The next time you try to go to a conference, when your boss hems or haws you say “Hey boss remember problem xyz, and how I fixed that after training.”  Or even better “Here’s how we improved things after training.”

Businesses are all about Return On Investment, ROI, if you can show them that sending you to training is an investment they will get a return on then you’re already on your way to your next round of training.

I hope to see you at SQL Rally this year and next!



Monday, April 4, 2011

MeMe Monday

When restoring Master
If corrupt make certain you
Enter -m; for start up

Tuesday, March 29, 2011

We Should Have A Talk: Your Database is Using ACID

I feel like a lot of my life as a DBA has been lived in reverse.  I’m started out my DBA life as an involuntary DBA.  My first real DBA issue was when my company’s production website was crashing.  The Database server was tossing errors that the Log file was full, as a matter of fact it had filled up the entire hard drive it was on.  Looking back at it now it seems like such a simple problem. 

One of the issues I’ve had with learning everything in reverse is I often find things and think “Why didn’t anyone tell me this!?”  There is so much to learn with being a DBA, Fundamentals, Internals, Troubleshooting, Monitoring, and Functionality of so many versions.   I was a certified MCITP for 2005, before the first time a guy named Paul Randal (blog|twitter) mentioned ACID in one of his blogs and I said, hey what’s that?  Chances are you’ve see this mentioned, but this is something we all need to know about.

So file this under Fundamentals.  Your Database is using, and while normally we’d say “Drugs are Bad m’kay”, we’re going with a little ACID is a good thing.

“But Balls”, you say, “ACID, Transactions, Fundementals why should I care about this, how is this going to help me impress my boss, or help me manage my SQL Servers?”

Well Dear Reader, I’m really glad you asked.  I’m of the firm belief that the more you know about how a database is supposed to work, the more you understand what it does.  The concepts that we will discuss Stretch across SQL Server. 


You’ve probably heard the term Transaction before.  Everything that occurs in SQL Server is a Transaction, the essential process of how data flows from end to end in SQL Server. In the 1970’s a very smart man named Jim Gray started working on theories about how a reliable transactional system should work.  He is literally the father of transactional databases, and he did quite a bit of work for Microsoft in helping to develop SQL Server. Jim Gray and Andreas Reuter wrote a book called Transaction processing: concepts and techniques,   this is the definitive book for explaining Transacactions, get a free preview from Google Books.

 In SQL Server you can start a statement by saying BEGIN TRANSACTION, and you can finish it by saying COMMIT or ROLLBACK.

Think of a Transaction like driving a car on the highway.  The Transaction is the car, and the highway is our SQL Server Instance.  The destination is our database.  Either we get there, think COMMIT, or we turn around and go back home without reaching our destination, think ROLLBACK.

There are two kinds of Transactions, Implicit and Explicit.  And Implicit Transaction is an implied Transaction.  SQL Server will always wrap a BEGIN and a COMMIT or ROLLBACK around a transaction.  Here’s a quick example, let’s say you type:


What you don’t see is that when SQL takes your command, it reads it as:



The BEGIN and the COMMIT are Implied, if you type them out then they are Explicit.  An Explicit Transaction is an unambiguous transaction.  We are specifying the beginning and the ending.


The term ACID was coined by Theo Haerder and Andreas Reuter in the academic paper Principles of Transaction Oriented Database Recovery.   ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability.  And it describes the way that a Transaction, behaves.   Here is a link to the MSDN article on transactions,  All of the definitions are from the MSDN article because these are the terms you will want to know, defined in the way you would want to understand them for SQL.

                A transaction must be an atomic unit of work; either all of its data modifications are performed, or none of them is performed.”

                When completed, a transaction must leave all data in a consistent state. In a relational database, all rules must be applied to the transaction's modifications to maintain all data integrity. All internal data structures, such as B-tree indexes or doubly-linked lists, must be correct at the end of the transaction.”

                Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions. A transaction either recognizes data in the state it was in before another concurrent transaction modified it, or it recognizes the data after the second transaction has completed, but it does not recognize an intermediate state. This is referred to as serializability because it results in the ability to reload the starting data and replay a series of transactions to end up with the data in the same state it was in after the original transactions were performed.”

                After a transaction has completed, its effects are permanently in place in the system. The modifications persist even in the event of a system failure.”

"But Balls", you say, "High level Balls, tell me from a High Level what does this acronym effect something I can see within SQL Server?"

Well Dear Reader, you see Atomicity in the way transactions behave as a unit of work.  Remember all of our work must be done or none of it.  This is accomplished by how our Transactions behave.  A wonderful example of Atomicity is the COMMIT or ROLLBACK operators that we discussed earlier.

There are two forms of Consistency that I can think of as examples.   First we can use Referential Integrity and Constraints in order to guarantee that a data field is not dropped that is the parent to child data.  You have a Datafile and a Transaction Log that make up your basic database files.  These are our physical file structures.  But we have internal file structures as well.  Internally when SQL Server writes or deletes a record, SQL makes sure that all pointers that would lead to that record are updates as well.  This ensures that we always have a Consistent view of our data accessible to our Users.

A good example of Isolation is the way SQL Server uses Locks.  In SQL Server some of our most common Locks are the Shared Lock and the Exclusive Lock.  By default in SQL Server’s Transaction Isolation Level, is READ COMMITED.  This means that if I have a simple table like below:

Huffy 28 in Bike
Light Saber
Star Wars Toy

And let’s pretend that there are a couple hundred rows.  Let’s give a simple example if I go to Delete or Update the Light Saber row, and a millisecond after my Transaction starts, someone else tries to Select the record, what would happen?  My Update would find my record with a Shared Lock, and then it would change to an Exclusive Lock on the record when I begin to Update my data.  The Select statement would attempt to take a Shared Lock on the same record and it would be Blocked, having to wait until my Update finished.

Now all of this would only take a fraction of a second, but it is Locking and Blocking function as intended.  This is a very basic level and we’ll cover Transaction Isolation Levels at a later date.

The best example I can think of for Durability is Recovery.  Durability means that you have an advanced logging mechanism that allows a Transaction that is COMMITED to persist, even if the computer is shut down at the second a COMMIT is received.  Likewise it would ROLLBACK any Transactions that may have been in progress, or In-Flight, that had not yet reached a COMMITTED state, ensuring that our database is Durable through unexpected shutdowns.    To see Recovery in Action open up your SQL Server Instance, go to Management, SQL Server Logs, and look at when your server came online. 

You’ll find that when the databases where brought online there is a record for Transactions Rolled Forward and for Transactions Rolled Back, and you will see the message “Recovery Complete” for databases.

We touched on a lot of topics today that each could be and deserve their own blog post if not series, Transaction Isolation Levels, Locking & Blocking, Internal File Structures, Logical File Structures, and Recovery.  To be honest we could have continued on for much longer, but what I want you to see is that ACID is fundamental to your database. 

So Dear Reader, I hope you can use this going forward.