Showing posts with label SQL Fundamentals. Show all posts
Showing posts with label SQL Fundamentals. Show all posts

Friday, September 12, 2014

Professional Microsoft SQL Server 2014 Administration

Hello Dear Reader!  Last September I was approached with a tremendous opportunity to become the Managing Author for the Professional Microsoft SQL Server 2014 book by Wrox.  We worked throughout the CTP phase and solidified the book after RTM.  By the end of July all the pages were in, all the chapters proofed, and we all had a collective sigh of relief.

I was joined in this book by Steven Wort, Ross LoForte, Chad Churchwell (@chadchurchwell | blog), and Jorge Segarra (@SQLChicken | blog) from Microsoft and Adam Jorgensen (@ajbigdata | blog), Brian Knight (@BrianKnight), Kim Hathaway (@sqlkimh), Roger Wolter (@rwolter50 | blog), Dan Clark, and Kathy Vick (@MSKathyV | blog) from Pragmatic Works.

Tech reviewing the book we had Kathi Kellenberger (@auntKathi ), Jason Strate (@StrateSQL | blog), and my buddy Daniel Taylor (@DBABulldog | blog).

The initial goal was to just update the book.  It quickly became a project to gut and replace old ideas with newer material, a vision that continued to grow and should make the next release of the Pro Admin series drastically different even from this book.

I couldn't be prouder of this crew.  Roger Wolter is a former PM from Microsoft who helped write Service Broker, and has done some of the largest and most interesting implementations of it in the world.  You will see his handy work on Chapter 6 on Service Broker and Chapter 8 on Securing the Database Instance.

I worked with Jorge very closely before he joined Microsoft.  He is an amazing guy, with a thirst for new knowledge.  Jorge took Chapter 1 on the SQL Server Architecture and 24 on SQL Server Azure Administration and Configuration.

My friend Kim Hathaway and I teamed up Chapter 2 Installation Best Practices and Chapter 3 Upgrading SQL Server 2014 Best Practices.  Dan Clark, .NET coding wiz and all around BI knowledge base, lent his talents to Chapter 7 SQL Server CLR Integration.

Kathy Vick a former Microsoftie with two tours of duty, who has been working with SQL Server since it was still called Sybase prior to 4.2 has Chapter 13 on Performance Tuning T-SQL and Chapter 14 on Indexing your Database.

Bradley Schacht did more than can be mentioned for the BI side of the house in this book.  He wrote Chapter 23 on SQL Server and SharePoint Integration.  Chad is a smart and amazing PFE for Microsoft.  He joined at the last moment us to take over Chapter 16 on Clustering in SQL Server 2014 and provided a quick and solid contribution to help us over the finish line.

Steven Wort, Ross LoForte, Brian, and Adam all produced the work that is consistent with what we have expected over the years.  Superb.

Then there's this guy.  Mr. Balls. I was honored to be asked with working with this gifted crew.  I wrote Chapter 4 on Managing and Troubleshooting the Database Engine, Chapter 9 In-Memory OLTP (Hekaton), Chapter 10 Configuring the Server for Optimal Performance, and Chapter 11 Configuring the Server for Optimal Performance.  Hmmm....I sense a theme.

The link to the book on Amazon is here.  Just wanted to say Thanks again to the team that put this together!

Look Mom & Dad, I'm on a Book!!!

As always Dear Reader Thanks for stopping by.

Thanks,

Brad


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. 

TRANSACTIONS

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:

SELECT
    *
FROM
    dbo.myTable1

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

BEGIN TRANSACTION

SELECT
    *
FROM
    dbo.myTable1
   
COMMIT TRANSACTION

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.


ACID

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, http://msdn.microsoft.com/en-us/library/ms190612.aspx.  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.

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

CONSISTENCY
                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.”

ISOLATION
                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.”

DURABILITY
                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:

ID
Product
Description
1
Bike
Huffy 28 in Bike
2
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.

Thanks,

Brad