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

Thursday, May 31, 2012

Cross Database Views and Schema Binding


One of the most interesting things I've found as a DBA is learning about features in other RDBMS platforms.  Sure we all work with and love SQL Server, but quite often there are features that you hear about that you learn about that make you go, "Wow, wouldn't that be cool!".  Sybase's ability to have multiple tempdb's, create a tempdb for a particular database to use, or create a tempdb  for a specific user login come to mind.  Oracles ability to make a metadata copy of a base table without causing locking or blocking on the original is another.  But as cool as these features are sometimes they create confusion when working with the business.  Often times you need to understand the internals of how SQL Server works to explain why a particular feature for another RDBMS platform doesn't work in SQL.


CROSS DATABASE VIEWS & SCHEMA BINDING
http://www.flickr.com/photos/incrediblehow/5714219510/

One I ran into recently was Cross Database views that allow update's and inserts on the base tables, (I'm not a Sybase guy so to my Sybase friends please feel free to correct me if I'm wrong).  I was working on creating a distributed topology for two systems that are currently intertwined, that for performance reasons we are tying to separate.  Part of the plan that was proposed was to have a number of cross database views that would allow us to avoid code changes in an application for this first phase of the project.


 So when we started to discuss the actions that would take place against these views, very quickly it was discovered that we wanted to have inserts and updates used against these views.  In SQL Server in order to update a base table from a View that View must be created specifying WITH SCHEMABINDING.  The problem with the request is that the base table and the View are in two different databases.  In SQL this doesn't work, in Sybase (which we are transitioning off of) it does.

I worked up this demo to show my friends this limitation for Views and just wanted to pass it along to you Dear Reader.

/*
First Let's Create our Database
that will hold our base table
*/
IF EXISTS(SELECT name FROM sys.databases WHERE name='test1')
BEGIN
    DROP DATABASE test1
END

CREATE DATABASE test1


/*
Now let's create our base table
*/
USE test1
GO
IF EXISTS(SELECT name FROM sys.tables WHERE name='myTable1')
BEGIN
    DROP TABLE dbo.myTable1
END
CREATE TABLE myTable1(
             myID INT IDENTITY(1,1)
             ,mychar CHAR(500) NOT NULL DEFAULT 'a'
             ,CONSTRAINT pk_myid_1 PRIMARY KEY CLUSTERED(myID)
             )

/*
Let's insert some rows
into our base table
*/
DECLARE @i INT

SET @i=0

WHILE (@i<15000)
BEGIN
    INSERT INTO dbo.mytable1
    DEFAULT VALUES
   
    SET @i=@i+1
END


/*
Now let's create our second database
that will hold our view pointing to
the base table, dbo.myTable1, in our
test1 Database
*/

IF EXISTS(SELECT name FROM sys.databases WHERE name='test2')
BEGIN
    DROP DATABASE test2
END

CREATE DATABASE test2

/*
Now let's create our view
*/
USE test2
GO
IF EXISTS(SELECT name FROM sys.objects WHERE name='v_myTable1')
BEGIN
    DROP VIEW dbo.v_myTable1
END
GO
CREATE VIEW v_myTable1
AS
SELECT
    myid
    ,mychar
FROM
    test1.dbo.myTable1
GO
  
/*
Our Regular View is created successfully
and we can do a select from it and see
that data is returned successfully
*/
SELECT
    *
FROM
    dbo.v_myTable1

 Our view returns just fine.  And if the business only wanted to perform read operations against the view, this would have met our requirements just fine.  However we need to create a view that allows updates and inserts.  
   
/*
In order to make a view that can
recieve inserts and updates we
need to re-create our view
and specify WITH SCHEMABINDING
(This will fail in a cross database view)
*/
USE test2
GO
IF EXISTS(SELECT name FROM sys.objects WHERE name='v_myTable1')
BEGIN
    DROP VIEW dbo.v_myTable1
END
GO
CREATE VIEW v_myTable1
WITH SCHEMABINDING
AS
SELECT
    myid
    ,mychar
FROM
    test1.dbo.myTable1
GO

When you run this statement it fails with the following error.

Msg 4512, Level 16, State 3, Procedure v_myTable1, Line 4
Cannot schema bind view 'v_myTable1' because name 'test1.dbo.myTable1' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.


Schema Binding only allows for two part names.  This means that we can only schema bind within our database.  This also means that if we wanted to use an Indexed View on the Cross Database View, we could not do that either. 


IT ALL MAKES SENSE


http://www.flickr.com/photos/dach_art/7126010381/
If you think about it, a View is just a select statement standing by waiting to be executed.  When you allow Schema Binding you allow that view to be a pass through to the base table.  This means you would need to give one database ownership of objects within another database.  This is not how SQL Server works currently.  Objects are allocated within a database, in SQL 2012 this is taken a step further with Contained Database.

When looking at Indexed Views it becomes even clearer.  An Indexed View is essentially a Materialized View.  All of the data in the view instead of existing as a select statement is persisted to the physical disk.  The way the data is read for an index view is quicker because you are performing a seek or a scan against one object that is dependent upon its base table.

You couldn’t have a Materialized View dependent upon Base tables within another database.  If a database when offline or they entered redo and recovery at different points you could potentially have transactions that were at different states within different databases, slight chance but still the implications are head-ache inducing.

So the long and short of it, you cannot do a cross database view using Schema Binding in SQL Server.

Thanks again for stopping by.

Thanks,

Brad



Thursday, May 10, 2012

SQL Rally Deck's and Demo's Up

Hello Dear Reader!  I'm coming to you live from the wonderful SQL Rally in Dallas Texas.  I have two sessions today, and the Slide Deck's and Demo's are live on the Resource Page.  Feel free to download them and see if you'd like to come join me, or download them and follow along!

"So Balls," you say "What are you presenting on?"

Glad you asked Dear Reader, and away we go!



TRANSPARENT DATA ENCRYPTION INSIDE AND OUT IN SQL 2012




The great thing about this session is even though we are using SQL 2012 99.999% of this is the same as SQL 2008 & 2008 R2.  So come and learn about TDE and leave with scripts that will help you deploy this if you are interested. I hope you'll stop by at 10:15 am I'm in room 302/303

Here's the Abstract:

Security is a very important part of your job and in how data is utilized. We have many tools to make data more secure, and starting in SQL Server 2008, we were able to add Transparent Data Encryption to that list. Find out what it does and doesn't do, how it effects read-only filegroups, performance, and compression (backup and row/page), what the X.509 encryption standard is and why you should be careful of what you store and where, and other advanced features and management tips.


SQL INTERNALS, RECOVERY MODELS, AND BACKUPS! OH MY!

This is a fun session that is all about learning.  We have demo's and we have fun, but a lot of getting better and advancing your career in SQL Server is knowing the concepts. We won't be Deep Diving but we will touch on ACID, B-Tree's, Transaction Isolation Levels, Transaction Log Internals, Recovery Models, and Backups.  I hope you'll come join me at 4:00 in room 302/303.


Here's the Abstract


The more you know about SQL Server, the more you understand how it works. SQL Server is a product we use every day, and most of us know the big concepts. At the 10,000-foot view, we know what databases, tables, and columns are. But what makes up those databases, tables, and columns? What are records, pages, extents, and allocation units? What are Full, Simple, and Bulk-Logged recovery models? What are the differences between Full, Transaction Log, Differential, and Filegroup backups? What is a piecemeal restore? This is an introduction to these concepts using SQL Server 2012. In this session, you will learn about the internal structure, recovery models, and backups and be better prepared for future learning and managing SQL Server.


WRAP IT UP


I hope you enjoy your day out here there is A LOT of SQL Learning to be had!


Thanks,


Brad

Monday, February 27, 2012

I’m Going to SQL RALLY!




Hello Dear Reader, I’ve just received great news I’M HEADED TO SQL RALLY 2012! But I didn’t get here on my own, I have you to Thank.  And I would like to do just that.  Thank You to everyone who voted for me as part of the recent Community vote!  I really appreciate it.  It is always an honor to be picked to participate in a SQL event, but it means a lot when your peers vote you in.

I promise that you will not be let down, I’ve got not one, but TWO amazing sessions that made it through the voting.

“So Balls,” you say, “What are you presenting on?”

Great question Dear Reader, and away we go!


Transparent Data Encryption Inside and Out in SQL 2012

Security is a very important part of your job and in how data is utilized.  We have many tools to make data more secure, and starting in SQL 2008 we were able to add Transparent Data Encryption to that list.  Find out What it does and What it doesn’t do, How it effects Read-Only Filegroups, Performance, Compression (Backup and Row/Page), What the X.509 Encryption Standard is and Why you should be careful of what you store and where, and other Advance Features as well as some tips on how to manage it.


I’ve had a lot of fun presenting on this topic in the past.  Transparent Data Encryption is a wonderful technology that we were able to start using in SQL 2008.  I was lucky very early on that as soon as I started working with 2008 I was working with TDE. 

When you use TDE there are some very important things to know and consider the first is what TDE does and doesn’t do, the second what physical changes actually occur within your database, and finally how you manage certificates and how they affect your recovery scenarios for your databases.

I’ve done a Lightening round version of this presentation for OPASS, the Orlando SQL Server User Group, my friends at Publix, and for SQL Saturday 79 South Florida this past year.  This year I start off the year with a Bang presenting on this topic at SQL Connections in Las Vegas, and now I’ll get to present on this at SQL Rally 2012 as well!


SQL Internals, Recovery Models, and Backups! OH MY!

The more you know about SQL Server the more you understand how it works.  SQL Server is a product we use every day, and most of us know the big concepts.  At the 10,000 foot view we know what  Databases, Tables, and Columns are.   But what makes up those Databases, Tables, and Columns.  What are Records, Pages, Extents, and Allocation Units?  What are Full, Simple, and Bulk-Logged Recovery?  What are the differences between  Full, Transaction Log, Differential, or Filegroup backups?  What is a Piecemeal Restore?  This is an introduction to these concepts using SQL 2012.  In this session you will learn about the internal Structure, Recovery Models, and Backups and be better prepared to for Future Learning and Managing SQL!


When I first put together this presentation it was meant to be a leap frog session. 

“So Balls,” you say “I’m not playing leap frog with you.”

No worries Dear Reader, I’m not playing a game, but what I mean by leap frog is I want you to leap ahead.  As I’ve studied SQL Server I’ve noticed that there are some fundamental concepts that you encounter over and over again.

The reason because they are all interrelated.  A.C.I.D., Transaction Isolation Levels, the internal components of SQL Server’s Relation & Storage Engine, B-Tree Structures, Pages, Allocation Units, Transaction Log management, Recovery Model’s, and Backups (that’s a mouth full OH MY!).  I’m not going to make you an expert, but I’m going to arm you with knowledge and concepts to allow you to go forth and be better prepared for future learning. 

But what we cover will apply directly to how you would choose the type of backups that are appropriate for the database systems that you manage.

THANK YOU

Once again Dear Reader and Dear SQL Community, I would just like to say Thank You.  I hope to see you and I hope to see you at SQL Rally 2012!

Thanks,

Brad

Tuesday, December 6, 2011

You Should Never Use Backup Log Truncate_Only Except...



http://www.flickr.com/photos/paulhagon/369576792/
I want to start this blog out with a Warning Dear Reader.  The technique that I am going to describe to you today is how to use Truncate_Only to NUKE your transaction log.  This is a topic that a quick search on will find you this Blog by Brent Ozar(@BrentO| Blog) and this MSDN forum conversation that has a link to a Paul Randal (@PaulRandal | Blog) Blog on why you should not use this as well.

"So Balls", you say, "Why are you blogging about something we should not use?"

Great question Dear Reader, the answer lies in the details.   With the one place that I would say this is okay to use.

WHY THIS IS BAD

http://www.flickr.com/photos/pawprintscharming/5077934899/
So let's start off first on why this is bad, so you will understand that you should use this with caution.  Your Transaction log is an essential part of your database.  This is an understatement.   This is like saying your Brain is an important part of your body. 

You cannot operate without it.  Period.  It remembers everything that you have done in your life, Transactions that have been committed and flushed to disk for the Database.  If you get knocked out when you wake up you start piecing what you did and how you got there.  In the database work we call this Recovery.

You cannot recover your database if your transaction log is gone, the database won't start up.   Not only that if you have a very important database, you cannot loose any of the transactions that you commit, ie you need to remember everything.   If you have a pharmacy database that track prescriptions, a bank database that tracks customer deposits, or a hospital database that tracks what was given to a patient and when.   You cannot lose any of that information. 

When your database is this important it SHOULD be in Full Recovery Model and we should be backing up our transaction logs in accordance with our Service Level Agreements, SLA's,  in order to meet our Recovery Time Objective, RTO.   If you use the command I will speak about in a moment on a Fully logged database it will wipe out your Transaction Log, good bye memories.  The structure will still be there, but this will make it to where you lose your ability to recover to a point in time.

CAN I GET A CHECKPOINT

"So Balls", you say, "What's a Checkpoint and what in the SQL Internals does it have to do with my database?"

Great question Dear Reader, man you are on top of your game today!  A checkpoint is a process that flushes Dirty pages from the buffer pool to the disk.   Sticking with the biology terms it takes what your eyes are seeing and flushes it to your brain to make, commit, the memory.

In SQL 2000 there was a known issue where occasionally the checkpoint process would go to sleep.  So why is this a bad thing?  Well your transaction log tracks the transactions that are flushed to disk, and when checkpoint went to sleep the transaction log would continue to grow and fill.

In a situation like this what should you do?  Simple open up Query Analyzer or SSMS and simple go to the database in question and type "Checkpoint".  That simple.  Checkpoint the database, execute a DBCC SQLPERF(LOGSPACE) and examine the free space for your database log to see that you now have plenty of room.

THE COMMAND THAT SHALL NOT BE NAMED

So when should you use the unspeakable command that I'm speaking of? 

You get a call for a server, it just so happens to be a SQL 2000 server on SP 4 (awe hotfix or security patch that takes you up to build (8.0.2055 i've seen it in both).  You've gotten reports that the TempDB has run out of log space.

You log in when you type:
USE tempdb
GO
CHECKPOINT

You get an error stating that a Checkpoint could not be committed because the transaction log was full.  At this point you have queries that are failing because tempDB is used, ALOT.  In order by, sort, and group by queries.  By Queries that need temp tables, variable tables, and work space tables.

Your only option at this point would be to restart your instance.  Why restart your instance because TempDB is destroyed and Re-Created each time your instance is restarted.

So before you do that, what can you do?  You can execute the following command.  Again only on a database where you do not care AT ALL about the transactional consistency of the database log.  Also you want your database to be in Simple Recovery Model by default.  And you are on a critical SYSTEM DATABASE that is destroyed and recreated on each restart.  Before you take an outage of your system try this.
Backup Log Tempdb with truncate_only

This command was depriceated in SQL 2005 and is not there in SQL 2008.  There is a work around, but I'm not posting the work around for SQL 2008.  Why because in my humble opinion Dear Reader you would only want to do this when the checkpoint has gone to sleep in the tempdb.  While I have see this A LOT in 2000.  I have yet to see it in 2008 and above.

So once again, be cautious and my recommendation is to only ever use this as a last ditch method of freeing up TempDB space as a means to avoid a reboot.


Thanks,

Brad  

Tuesday, November 1, 2011

Presenting on SQL Internals, Recovery Models, & Backups! OH MY Today


 Today I’m presenting “SQL Internals, Recovery Model’s, and Backups. OH MY!”, for Pragmatic Works.  I did this presentation once before for SQL Saturday 79 and it was a big hit.  I had a lot of great feedback from the people in the audience that day, and my friend Kendal Van Dyke (@SQLDBA | Blog) attended and gave me some great notes and tips. 

My goal is not to make you an expert, but to give you a good base knowledge so you can continue to learn on the subject.  There is a lot to cover and we will scratch the surface of a lot of topics.

“So Balls”, you say, “Why should I come listen to you talk about Internals if I’m not going to become an expert?”

Great question Dear Reader, let’s dive right in!

WHY LEARN ABOUT INTERNALS?

I’m not a mechanic, but if I was I would expect that I could look at that picture to the right and tell you what I see.  And I’m not talking, it’s a car and an engine, but what are the components.  What is that pink thing?   What about the blue thing?  I look at that picture and I know the basics and that’s about it.

I am a DBA, and if someone shows me a SQL Instance, a Database, or asks me to perform a task then I should be able to tell them a thing or two about it.  The more you learn about internals the more you know about what you use every day.

As I’ve continued to learn about SQL I’ve noticed some common terms, some information that formed a common baseline.  I want to pass that information on to you because l want to make it easier on you.  I want you to go out and learn, and this information will help you.

You need to understand how a Transaction Log works, that SQL has internal components, what the data hierarchy is, what Recovery Model’s are, how they affect the backups you will take, and how that will affect Service Level Agreements you have with your user. 

From ACID to Transaction Isolation Level’s we are going to make a run at it.  I’ll upload the deck and the Demo’s when the presentation is over.  Click HERE to go to my Resource Page and get a copy of the presentation and the Scripts. 

Click HERE to sign up and join me today!  I hope to see you at 11 am.

Thanks,

Brad


Monday, August 15, 2011

SQL Saturday 79 Recap – (THANK YOU SQL SATURDAY 79)


It is hard to believe that SQL Saturday 79 has come and gone.  It was held in the Beautiful Carl DeSantis Building on Nova Southeastern University.  The day started out with a 3:30 am wake up.  I wanted to make sure that I had everything posted on the blog before I headed down so if anyone was looking for downloads or slides I could send them to the Resource Page for it.  After a quick shower and grabbing a cup of coffee for the ride, I hit the road about 4:30 am. 

The trip down is 187 miles from the new house in Winter Haven to Nova Southeastern, with registration starting at 7:30 and the keynote at 8 I wanted to make sure to be there in plenty of time.  It was a nice drive down, I always like watching the sun rise in the car.  I used to see it all time during the drive in to DC & on vacations driving from Virginia to Florida before the move down here.   So with the start of a beautiful day and a peaceful drive, I get to the parking garage and run into my good friend Kendal Van Dyke (@SQLDBA | Blog) on the way into the building. 

After finding registration, finding Coffee, and the restrooms it was off to the speaker room to get a Speaker Shirt.  I don’t normally spend a lot of time in the Speaker room.  I love to meet new people at SQL Saturday’s, but I didn’t make it down for the Speaker Dinner the night before so this was a nice chance to meet some of the other Speakers like Mike Hillwig (@mikehillwig | Blog), Herve Roggero (@hroggero | Blog)  , Bradley Schacht (@bradleyschacht | Blog) and it was a chance to say Hi to others that I’ve met before.  Andy Warren (@SQLAndy | Blog), Mike Davis (@MikeDavisSQL | Blog),  Mike Antonovich (@MPAntonovich| Blog ), Jose Chinchilla (@SQLJoe | Blog), Rodney Landrum (@SQLBeat | Blog), and Thomas LaRock (@SQLRockStar | Blog).

Tom LaRock made my day by remembering me.  I have been very lucky and fortunate to meet some really awesome people in the SQL Community.  There are a lot of people that I’ve gotten to hang out with on a regular basis (point’s to the list of names up above), that I just grin ear to ear thinking about.   It wasn’t that long ago that I set in one of Tom’s sessions for SQL Saturday 49, and later read Noel McKinney’s(@NoelMcKinney | Blog) Blog about it.  At the time thinking I want to get in on this, I want to present, I want to blog, I want to take the next step in my life as a DBA.   And now I say “Hi Tom I’m Brad Ba…”, and he say’s “Hi Brad I remember meeting you before.”  Awesome.

“So Balls,” you say “We get it your there, speakers are there, your gushing like a 14 year old at a Justin Bieber Concert, What about the SESSIONS?”

Well Dear Reader, I missed the first one because I was reviewing all my presentations, cutting some material and adding a little more to others.  But after that, I jumped right in!

RODNEY LANDRUM T-SQL ALPHABET SOUP

Rodney is a SQL MVP and has been working with SQL Server for over 12 years.  There are four letters that describe Rodney’s T-SQL Alphabet Soup, no none of the dirty ones, A.W.L.D.    A is for Awesome, W wait for it, L Ledgend ……. D DAIRY.   Ahhh…..Barney Stintson would be so proud.


I would normally blog details, but Rodney’s Presentation is very interactive and a lot of fun.  He goes one letter at a time and asks the audience to guess what command he will use.  This is a great mental exercise for those of us that use SQL, and it is a lot of fun to participate in.   And it will be one of the presentations given at SQL Saturday 85 J , CLICK HERE to look at the full line up for that great event.

BRADLEY BALL PAGE & ROW COMPRESSION HOW, WHY, AND WHEN
My Early Birds! We had another 15 minutes to fill the room after this picture!

Compression is a great topic.  Pictured above is my room as people started coming in 15 minutes before the session started.  I had one of the best crowds I’d ever had.  We cover a lot when I do this presentation, and I had a great group of people. By the time the presentation got started we had almost filled the room.  People asked questions, we had a really good back and forth.

The main thing that I tell people is that when you apply Compression you do not want to do it blindly.  I have a Monday Morning Checklist on my Resources Page and it lists all the steps you can take to determine the tables in your database that are candidates for compression.   I like to think of Compression the way that you would Indexes.  You wouldn’t just toss an index on a table and never check to see if it effected your query plans/query response times, over head for different operations (updates/inserts), or if the data was actually worth indexing.  And Compression needs the same consideration to make sure you do it right.

Compression is also very Dynamic, with the way that Allocation Units work within tables, Indexes, and Partitions you can apply Compression in a lot of different ways and we cover the full gambit.  This was a great start on the presentations and I want to say a BIG Thank You to everyone who attended this was a lot of fun to present and you were a great crowd!

LUNCH

Lunch was served by Azteca Real Mexican Restaurants and it was delicious.  The line was vast, the picture you see above is after waiting for 30 minutes and then going to the back of the line, the sever table was on the opposite end from me.  There were a lot of people to feed and the SQL Saturday team did a great job of getting everyone fed, with hefty portions. 

Quick thinking on their part led them to open a line on either side of the serving table to get the crowd through as quickly as possible.  I chatted with folks in line, sat with Serge & Javier (two new acquaintances) and talked about the emergence of Business Intelligence, the free training offered by the Great Folks over at Pragmatic Works on a weekly basis, and a whole host of other topics.

With my stomach full it was off to see a SQL Rockstar!

THOMAS LaROCK MONITORING DATABASES IN A VIRTUAL ENVIRONMENT

I can’t speak for you Dear Reader, but I’m seeing a lot of Virtualization going on.  There are some databases that will not translate well, that must have physical hardware.  But these databases are normally the exception and not the rule.  We all know that Virtualization is a great way to reduce your footprint, go green, yadda yadda yadda. 

So you’ve gone Virtual, now what.  Now you are still going to have users coming up to you saying “The Application is running slow, can you fix the database?”  And you will still need to ask the same questions.  Slow compared to what?  Do you have baselines, do you know how your server is behaving, Do you know how your Host & Virtualized instance are working in tandem? 

Ah I got you with the last one, and Tom got me as well.  Tom does a great job of stepping through the different pain points that you have, CPU, Memory, Disk I/O, and Network Latency.  These are all very important things to consider, after all they were important when it was just Physical Hardware.  Now you have virtualized Hardware don’t you want to make sure that you know what is going on?

The presentation is shaped around VM Ware.  The terminology is specific to it and if you are running VM Ware and have Server Engineers talking about crazy sounding stuff like ESX Hosts, then you need to know crazy things like VM Ready Time, Reservations, Limits, and Shares.  You need to know that Swapping and Ballooning can be bad, and if you understand how SQL Server uses memory it translates pretty quick.

Tom talks about his (Patten Pending) BMFF Best Metric Friend Forever.  And when it comes to troubleshooting your VM’s these are things you need to know.  If your working with Hyper-V this is still a great presentation to attend because it will get you thinking about the questions you should be asking, and the terminology that you should be learning to better support your environment.

BRADLEY BALL TRANSPARENT DATA ENCRYPTION INSIDE AND OUT

I’ve worked a lot with Transparent Data Encryption.  It is a great technology and was a great addition to SQL Server 2008.  It is a very simple technology to turn on and off.  And like my friend Colossus to the left it affects the Physical Structure only.


During this presentation one of the cool thing we do is taking an unencrypted backup tossing it into a Hex editor and looking at the data you can start to pull out.  The fact is there is a lot of meta data and header information, but there is also all of your other data stored in plain text.

Then take that same database, apply Transparent Data Encryption, back it up, and place that backup in a Hex Editor.  Aside from some header information that cannot be encrypted so that SQL can still read it, the difference is like night and day.

We do that in this session and then I cover some tips on how to automate the backup and on disk management of your Certificates as well as the impact this will have on Advanced Features such as Mirroring and Log shipping, as well as the additional considerations you should account for when planning your DR strategy.

Once again a very interactive crowd, and a very BIG Thank You to everyone who attended!   Some of the most gratifying moments of the day came in the conversations after this session and my next one.


BRADLEY BALL SQL INTERNALS, RECOVERY MODELS, & BACKUPS! OH MY!


3rd Presentation of the Day we are now in the Home Stretch
This was the first time I had done this presentation.  My good friend Kendal Van Dyke sat in and gave me some great pointers for the next time I do this, and I’m really excited for next time.

I think this is the year where a lot of things clicked for me and I grew the most as a DBA.  I remember reading Paul Randal’s (@PaulRandal | Blog ) about the internal contents of a Page a couple years ago.  And it took me several reads to process it to the point I could make flash cards on it.  This year the internal make up of a Page has become familiar like the view out of a window.   And as I’ve continued on with learning I’ve noticed that there are a lot of things that come up time and time again.

Sometimes I stop and go, “How have I lived my WHOLE DBA life and not known this!”  So this presentation is not about becoming an expert at internals.  It is about getting to the point where you can start that journey.  In it we covered ACID, SQL’s Internal Components, the Data Hierarchy, Transaction Log Internals, Recovery Models, how Recovery Models relate to SLA’s & DR scenarios, the different types of Backups, and what a Piecemeal Restore really does. 

I had a LOT of great questions.  And even though I was able to answer all of them I think a lot of them deserve follow up blogs.  I pointed people repeatedly to the great free resources that are out there, How Do You Learn & Top 5 Reasons You Should Be On Twitter, and encouraged them to do free and paid training where they can.  The point was to better themselves, which is always a good thing.

Another BIG Thank You to everyone who attended this presentation.  I had a great time, and really appreciated all the questions.  After this presentation I had someone come up to me and say "I've been learning on topic X and the way you explained it everything just clicked and I got it."  That is one of the best thing's I've ever been told after a presentation!

WRAP UP

And with that Dear Reader we called it a day.  I sat and talked with Kendal, Andy, and Mike and watched as the raffles began.  It was around 5 pm, a little under 12 hours since I had awoke, and I decided to call it a day, with a 3 hour car ride and another 187 miles to home.  I got some Tweets about the after party, and I wish I had made it.  Maybe next year we will make a family vacation out of this, or even better just me and Mrs. Balls, so we can stay a little longer.

This was a great event and a great day despite all the difficulties the team hit during the day (10 speakers no showed the event)!  We had a lot of great people that filled slots, the team worked tirelessly to get it all pulled together, and by the end of the day I saw a lot of smiling faces.

I Can’t wait till next year.  Now off to SQL Saturday 85 in Orlando September 24th J!

As Always Thanks for Reading!

Thanks,

Brad

Sunday, August 7, 2011

SQL SATURDAY 79


http://www.flickr.com/photos/14529149@N07/1492197386/
This weekend Saturday August the 13th, I will be trekking down to beautiful Ft. Lauderdale to speak at SQL Saturday 79.  I applied to speak back in March, and at the time I submitted 5 different sessions. I was brain storming at the time.  I’ve done a lot of presenting on Compression, and I love to present on it, but I had a couple other topics that I wanted to present on as well.  And that is one of the great things about SQL Saturday, it is all about giving Attendees the chance to learn, and giving Speakers the chance to present.  And will I ever be presenting!  I will be speaking not once, not twice, but THREE times this Saturday!

“So Balls”, you say, “What will you be presenting on?”

So Glad you asked Dear Reader J, and away we go!

PAGE & ROW COMPRESSION HOW, WHY, AND WHEN
http://www.flickr.com/photos/meegs108/4792232575/


I’ve presented on this topic at SQL Saturday 62 & 74, SQL Rally, MagicPASS, and Tuesday August 9th at OPASS, the SQL Server User Group for North Orlando.  Compression is a great topic, and is a technology that I believe will only increase in usage.

So often people confusing Compression, with squeezing more into one place.  It is much more like efficiently packing what you have in the best logical order.  Understanding how compression works, and what you should be compressing, and why compression could help you is key to benefiting from the cost of an Enterprise Edition License for SQL 2008 and up.   

I’m presenting a Deep Dive on this topic at the PASS Summit in October, and this presentation is the perfect preparation for that Deep Dive.  If you get a chance to stop by I’d love to have you, here is the abstract for my session.

“Page and Row Compression are powerful new tools. Vardecimal shipped with SQL 2005 SP2, Page & Row with SQL 2008 RTM, and Page & Row with Unicode Compression with SQL 2008 R2. Get an overview into how each version of compression works internally. Learn how your Allocation Units will determine if your data is a candidate for compression. Understand how your tables Update and Scan pattern’s affect the compression types you should consider. And what you should you take into consideration for additional overhead.”

Next up a little Transparent Data Encryption

TRANSPARENT DATA ENCRYPTION INSIDE & OUT


In the wake of all the hacking scandals that we’ve seen recently, Security is at the forefront of many people’s minds.  If you haven’t had your CIO, Bosses Boss, or your Boss ask you about what you can do to “better secure” your databases, you will at some point.

 If you are paying for an Enterprise Edition License for SQL Server 2008 and above you have Transparent Data Encryption available to you.  It is really easy to enable, but you need to understand how it works, what it does and what it doesn’t do, additional backup considerations, what the impact will be to advanced features, and how this will add to disaster recovery scenarios.   We will cover all of that.  I’ve set up TDE with Mirroring, Log Shipping, on Vendor Databases, and on Custom Databases. 

When we talk about TDE it is physical hardening, like Colossus up above, and if you work with sensitive data this could be a real benefit to you.  I’ve got this chocked full of info.  I love questions so if you’ve got them get them ready because I’d love to help with an answer.  And if I don’t have an answer I’ll research it and blog about it!

Here’s the abstract for this presentation.

“Security is a very important part of your job and in how data is utilized. We have many tools to make data more secure, and starting in SQL 2008 we were able to add Transparent Data Encryption to that list. Find out What it does, What it doesn’t do, how it effects Read-Only Filegroups, Performance, Compression (Backup and Row/Page), and other Advance Features as well as some tips on how to manage it.”
And now a little fun with Internals!

SQL INTERNALS, RECOVERY MODELS, & BACKUPS! OH MY!


http://www.flickr.com/photos/keolson81/413390058/
Listing to Paul Randal's (@PaulRandal | Blog) MCM videos, he talks about spelunking in the Database engine. 

As someone who has rock climbed, caved, and generally spent a lot of time hiking around the mountains I really like the mental image of a couple guys lowering themselves deep into the SQL Engine with Hard Hat’s and Light’s on their heads. 

“Hey Paul you ever been here before?”  “Yeah, loads of time check out the granite stalagmites by the Access Methods!”  And if you are looking for spelunking that is what I’m doing, but instead of going caving we are spelunking around the kiddie pool.

Why should you come to this session?  Especially after you’ve just sat through my two other sessions J?  Because Dear Reader I want you to learn.  There is so much to learn when it comes to internals that people can spend weeks, and do, learning about one particular section and still have more to learn.  But as you continue your learning there is a lot of vocabulary you will need to know.

You need to know what the internal Data Hierarchy looks like.  You should know what the difference between a record, a page, an extent, an allocation bitmap, and Allocation Units/IAM Chain’s are.  You should know how your Transaction Log effects your Recovery Model.  You should know the different Recovery Model’s and how they relate to the different type of backups, and how backups relate to Service Level Agreements, SAL’s, and Disaster Recovery, DR.

But unlike any other presentation that I have ever done, this is just a talk, a conversation.  You could do demo’s with this, but that is a lot to squeeze into an hour & ½ .  I want to make sure that when you leave the room you know enough to feel informed.  But that you also know enough, to realize how much you do not know.  And you’re not alone. 

I don’t know it all.  Not even close.  But there are a lot of amazing experts out there that I’m still learning from, and probably will continue to learn from until they retire or I do.  But this will set you up with knowledge you might not have already known, and will make sure you are poised for future learning.  Here’s the abstract.

“The more you know about SQL Server the more you understand how it works. SQL Server is a product we use every day, and most of us know the big concepts. At the 10,000 foot view we know what Databases, Tables, and Columns are. But what makes up those Databases, Tables, and Columns. What are Records, Pages, Extents, and Allocation Units? What are Full, Simple, and Bulk-Logged Recovery? What are the differences between Full, Transaction Log, Differential, or Filegroup backups? This is an introduction to these concepts. In this session you will learn about the internal Structure, Recovery Modes, and Backups and be better prepared to for Future Learning and Managing SQL!”

So what are you waiting for Dear Reader, Click HERE to go register, come up and say “Hi!” and I hope to see you there!

Thanks,

Brad