Saturday, April 27, 2013

SQL Saturday 215 Deck and Demos Live

Hello Dear Reader!  I'm up in Jacksonville for SQL Saturday 215. Today I'm presenting SQL Internals, Backups, and Recovery Models!  OH MY!  

I'm also going to be the MC for the SQL Iron Chef BI presentation today.  Before the festivities being I wanted to upload my deck and Demo, a few tweeks from the last presentation I did up in Boston. One of the big differences, more data for the partitioning & Peicemeal Restore demo.  I got an SSD this week, and it's time to have some fun with it!

Early morning outside the main hall SQL Saturday 215 Jacksonville

I'll be in the DBA Auditorium from 1:15 - 2:15 today.  If you'd like a copy of the deck Click Here, if you'd like a copy of the demos Click Here.

Hope to see you there!

Wednesday, April 24, 2013

The 200 Billion Dollar Tweet




Hello Dear Reader!  Ever try to convince someone that they should be paying attention to Twitter?  A co-worker, Vice President of something or other, CMO, CEO, or CIO?  

I’ve had conversations over the years with many people as to why Twitter is important to their business.  If they don’t have a dedicated “Social Media” guru or their Corporate and Legal Policies do not yet “allow” an official Twitter presence, they can still effectively market, and monitor marketing, using Hash Tags.  It’s a way of dipping your toes in the water without really diving in.

Alas most of the conversations revolve around what “real” results they can gain from Twitter.  Most lack the vision or understanding to see how it could benefit them.  Tying a dollar amount to Twitter can be difficult.  Most companies don’t track or publish earnings related around a Twitter campaign.  They do with traditional advertising, but with social media it’s hard to tie a dollar amount to it.

I was a little busy yesterday with work.  Like most days I poked my head out on Twitter and checked several new sites.  I even received some push updates to my phone via USA Today, Fox News, and the New York Times.  So Imagine my surprise when this morning while standing in line at the Starbucks in the Charlotte airport that I see this.  Twitter Terror Hoax Rocks Wall Street, AARRRUUUU?

Learning about social media occurrences the next day via the new paper.  How old fashioned.  Loosing 200 Billion dollars over 80 typed characters, not so old fashioned.

Twitter first came out I laughed at it.  Why would anyone want to have a website version of Instant Messenger, remember AOL, limited to 140 characters?  Turn around several years and I have firmly flip flopped, Oxymoron, and have drunk the Twitter Kool-Aid.

What changed my mind?  Becoming a presenter in the SQL Server Community.  I went to my first SQL Saturday and caught the bug, read TSQL Tuesday #41 Becoming a SQL Server “Presenter” GETINVOLVED!  One of the things I did was examine the really good speakers and see what they were doing.  They blogged, they were on Linked-In, they were on Twitter.  So I did all of that. 

Only after the fact did I find #SQLHelp, the hashtag that allows people to ask whatever SQL question they have and get free advice from some of the Top SQL Talent in the world.  There were situations where I had production outages and we, my fellow DBA’s and managers, used Twitter to post questions and get deep technical answers quickly.

“So Balls”, you say, “YAY you’re on Twitter.  Now what about this 200 Billion dollar Tweet?!”

At 1:07 pm, East Coast Time, the Associated Press Twitter account, currently suspended, was hacked.  The 80 characters contained within the Tweet typed by the hackers seemed particularly potent given the turmoil of the last week.  “Breaking: Two Explosions in the White House and Barak Obama is injured”.  I’m not alone in my conversion, apparently the people trading on the New York Stock Market follow the Twitter as well.


By 1:09 pm the Dow had fallen over 137 points.  While it rebounded quickly, 1:12 pm when Sam Hananel (@SamHananelAP) Tweeted, “Please ignore AP Tweet on explosions we’ve been hacked”.  The market corrected.  But did it?


My Dad does a lot of day trading.  He’s studied up on it, and I’ve learned a tiny (very tiny) little bit about it vicariously through him.  You can take options out on Stocks termed calls, where you bet they will over or under perform.

Based on their performance you get your money back and a little extra.  The most notorious case of this came during 9-11 where some nefarious people had bet on the airlines to lose money.  In regular terms though this is used daily, sometimes you win, sometimes the company you bet on wins.  It's part of our system.

*It’s a lot more complicated than that, but we’ll stick with the very basics.*  Think betting, but using the stock market.  There are also automatic options that you can set through e-investing web sites.

So it could have affected Calls.  People could have lost money.

Another way that people could have lost money on this?  Using automated software to monitor stocks.  

Quick example, I like Disney.  So I go buy some Disney stock.  Disney is a non-essential good (basically).  They make entertainment, theme parks, dresses that my daughter likes to wear, movies my kids like to watch (DAD include on this).  I don’t NEED Disney stuff.  We get it with disposable income.  We NEED food.  The mortgage HAS to be paid.  Electricity, need that too!  So when there are hard times disposable good take a hit.  Look at the stock market when 9-11 happened, the Mortgage Crisis, and other such events.

So I buy 100 Disney stock at $35 a share for $3,500.  If something happens and Disney stock drops $10 per share, I could set up a pre-arranged sell order.  At $25 sell it all.  Let’s say the stock market tanks Disney stock because a Hacker gets into their official account and announces “Disney to go bankrupt, 10,000 employees to be laid off immediately”.

My Stock dips down to $23, while I’m at work.  My e-banking software kicks in and sells all my Disney shares.  My money is down to $2500 and I’ve just lost $1000.  When it is correctly reported that Disney is not going bankrupt and is having a record Quarter beating all earnings estimates, and the stock now corrects to $40 a share, I’m no longer a stock holder.  Not only did I take a loss, but I was out on the Win too.

I just lost $1000.  I just missed out on gaining $500.  There is no customer service department.  No receipt to be returned.  I’m out.  The market was correctly reported.  Based on my input a sell order happened.  Potentially A LOT of sell orders from all sorts of people happened. 

WHAT DOES THIS MEAN

Security and Reality.  First Security.  It means some real people lost real money yesterday.  We’ll have to see what the fallout of this is.  It also means that if you do have a Corporate Twitter account you probably need to look at changing the password every 90 days, and using something like KeePass to manage your password.

Warnings aside it also means we can now put a dollar amount on Twitter.  USA Today estimated that $200 billion dollars in broader market capital was lost when the Tweet occurred.  The market rebounded and that capitol was regained, with some people suffering losses.

Before I could even finish writting this blog, there was a new story.  SEC, FBI probe fake Tweet that rocked stocks.  And another Twitter working on two-step authentication

Now Reality.  The financial world is following Twitter and most other forms of social media.  The importance of effectively using this channel to communicate with customers and the world at large is only going to gain importance as time goes on.  The most expensive Tweet in the world so far is now worth $200 Billion dollars.  How much will the next one be worth?

As always Thanks for stopping by.

Thanks,

Brad


Friday, April 12, 2013

DBA Question of The Day: What is SAVE TRANSACTION?



 Hello Dear Reader!  I was teaching a class in Chicago last week and I got a lot of really great questions.  

We only had two days so time was short.  I got an email reminder today.  So this is the first blog that will answer some questions that I didn’t get to cover in depth in class, and I thought it would be an Excellent Question of the Day!  

The Question that I got was “What is the T-SQL SAVE TRANSACTION statement used for?”

The short answer is it is a way of setting a save point in multiple Nested Explicit Transactions.  For the Long answer we’ll look a little deeper.

REFRESHER
Back in a little over two years ago I wrote a blog, We Should Have A Talk: Your Database is Using ACID.  If you are unfamiliar with ACID go read up on it, because it is fundamental to what we are doing here.

In that same blog I talk about Explicit, Implicit, and Nested Transactions.  In case you miss the ACID reading let’s cover those.

Every Transaction in SQL Server is an Implicit Transaction, unless otherwise specified.  You can turn off Implicit Transactions but that’s a talk for another time.  Implicit means implied, rather than expressly stated. 

As with the picture we don’t have to say it.  Hopefully things end a little better for us.

 SQL Server works the same way.  No, not like a cat driving a car.  Transactions are just as Implicit.

Every single ALTER TABLE, CREATE, DELETE, DENY, DROP, FETCH, GRANT, INSERT, OPEN, REVOKE, SELECT, TRUNCATE TABLE, or UPDATE is a going to be wrapped in a BEGIN TRANSACTION and end with either a COMMIT or a ROLLBACK.

Explicit means fully and clearly expressed or demonstrated; leaving nothing merely implied.



There is no mistake about it.  The following SQL Statement is an Explicit Transaction.

BEGIN TRANSACTION

     INSERT INTO dbo.myTable1 DEFAULT VALUES

COMMIT TRANSACTION

So we’ve covered Implicit and Explicit, now we get to NestedNested Transactions are multiple levels of Explicit Transactions.


For Example:



Here we have 3 levels.  However, if any one of them fail or ROLLBACK then they all Rollback.  

Some companies use Explicit Transactions as part of their error handling.  Nothing wrong with that, but wouldn’t it be nice if you could commit part of the statement, even if a later part fails?

SAVED!






Saved Transactions allow us to save our success as we go in Nested Transaction, so that a Rollback doesn’t wipe out all of our progress.  

Depending how your code works, you MIGHT be able to use this.  Test the logic very soundly.


So let’s look at how this works.  First we’ll create a Table to test out our transaction.

if exists(select name from sys.tables where name='myTable1')
begin
     drop table dbo.myTable1
end
go
create table dbo.myTable1(
          myid int identity(1,1)
          ,mychar char(50)
          ,mychar2 char(50)
          ,constraint pk_myid_mytable1 primary key clustered(myid)
          )

Now let’s create a transaction to insert some data and roll it all back.

BEGIN TRANSACTION
     INSERT INTO dbo.myTable1(mychar, mychar2)
     VALUES('some data 1', 'some more data 1')
     BEGIN TRANSACTION
          INSERT INTO dbo.myTable1(mychar, mychar2)
          VALUES('some data 2', 'some more data 2')
          BEGIN TRANSACTION
              UPDATE dbo.myTable1
              set mychar='some new data 1'
              WHERE myid=1
              BEGIN TRANSACTION
                   SELECT * FROM dbo.myTable1
              COMMIT TRANSACTION
          COMMIT TRANSACTION
     COMMIT TRANSACTION
ROLLBACK TRANSACTION

Here’s the Output from our SSMS Windows.



/*
OOPS did I say ROLLBACK!?
*/
SELECT * FROM dbo.myTable1



We had a ROLLBACK at the end.  So the work isn’t going to stick.  Sure enough another select from the table and everything is gone.  

Now let’s try the same code again, but now we’ll create a SAVE TRANSACTION and name is testSavePoint.  Note the syntax change.  *We need to call this in the ROLLBACK otherwise the whole thing will ROLLBACK again.*  ACID properties are still in play, so we need a COMMIT AFTER the ROLLBACK  
/*
Let's Try this again
*/
BEGIN TRANSACTION
     INSERT INTO dbo.myTable1(mychar, mychar2)
     VALUES('some data 1', 'some more data 1')
     SAVE TRANSACTION testSavePoint
     BEGIN TRANSACTION
          INSERT INTO dbo.myTable1(mychar, mychar2)
          VALUES('some data 2', 'some more data 2')
          save transaction testSavePoint
          BEGIN TRANSACTION
              UPDATE dbo.myTable1
              set mychar='some new data 1'
              WHERE myid=3
              BEGIN TRANSACTION
                   SELECT * FROM dbo.myTable1
              COMMIT TRANSACTION
          COMMIT TRANSACTION
     COMMIT TRANSACTION
ROLLBACK TRANSACTION testSavePoint
COMMIT TRANSACTION


Here’s our output from SSMS again.  This looks very similar to last time with only changes in the identity key.


/*
OOPS did I say ROLLBACK AGAIN!?

But did we save anything
*/
SELECT * FROM dbo.myTable1



This time however, only our Update Statement was rolled back.

“So Balls,” you say, “That’s cool, but will we actually use this?  If we are using Nested Transactions would we only want to save ½ of it?”

Excellent Question Dear Reader!  Let me answer that with a real world scenario.

I was working with some friends earlier this year.  We changed an update job that ran for hours from row-by-row logic to set based logic.  The result was minutes instead of hours.  There were two parts of this job that we worked on. 

It was a four step process.  First a range of data was selected from one table.  Second we looked for missing data and inserted anything that was missing based on matching natural keys.  Third we then looked at everything that matched and updated any rows that needed the information.  Fourth we updated the range selected in another table.  We needed all four steps to run.  A Saved Transaction wouldn’t work for us using their current logic.

However the job failed when we first ran it, because there was an error on saving the range table.  Insert and Updates worked just fine.  The job would look at the range table and try the same range.  Even though it was failing the job was reporting success because the error handling did not catch the rollback.  It was explicitly called, but executed on a GO TO on error statement.

While we would have still had to fix the error on the range table, we could have used SAVE TRANSACTION to save the insert and update.  Then moved the range table update to its own Transaction that would have better error handling.  It also would save retry operations on the job by not processing expensive Updates, Inserts, and Rollbacks.

Alright Dear Reader, there it is.  SAVE TRANSACTION and how it works with Explicit Transactions.  Thanks for the questions, keep them coming!

As always Thanks for stopping by.

Thanks,

Brad

Tuesday, April 9, 2013

T-SQL Tuesday #41 Becoming a SQL Server “Presenter” GET INVOLVED!




Hello Dear Reader!  It’s been a while since I participated in a T-SQL Tuesday, and this month Bob Pusateri (@SQLBob|Blog) is hosting it.    Bob is very active in the SQL Community, I met Bob briefly at the PASS Summit this year, and occasionally say Good Morning to him on Twitter.  His topic is one that really interested me, because it’s a story I’ve been meaning to tell but just hadn’t gotten around to.  Without further ado here’s Bob in his own words to describe the topic:

This month the prompt is how did you come to love presenting? What was the first time you gave a presentation in front of a group and really enjoyed it? Was it something that was required of you in school? Something you did in the workplace? Were you inspired by other SQL community members and thought “I think I can do that too”? Whatever your story is, I’d love to hear it. Not a presenter? Not a problem! Feel free to chime in with whatever you like that’s related to either presenting or SQL Server in general.

Sometimes I look at the things I’ve been able to do in my career and I wonder how did I get here?  First & foremost.  I have a tremendous support system.  Even to this day without the help of my ex-wife, friends within the SQL Community, and my co-workers (past and present) I wouldn’t be where I am today.  That support has helped more than I can say, which is probably why a lot of my blogs start with Thank You.  That support allows me to write the rest of this blog.

IN THE BEGINNING


Not so long ago, I didn’t know what a SSUG was.  Two years and six months ago (roughly), I attended my first SQL Saturday.    I did not have a blog.  I did not tweet.  If you would have asked me who SQLBalls was, I would have probably said, “That sounds like a serious medical condition you should have checked out.”  I had left the Office of the President and went to work for Publix.

Over the years I had moved the family a lot for my career.  This would be the move where we didn’t have to move anymore.  I had attended the first 24 hours of PASS, I posted on the forums at SQLServerCentral.com, but that was it.  Did I mention, I didn’t know what a SSUG was.  Didn’t know where my local one was, Kendal Van Dyke(@SQLDBA | Blog) was still putting together MAGICPASS.  An interesting thing had happened when I went to the 24 hours of PASS.  I saw a presentation on a subject I was pretty knowledgeable in.  There were several things that I had done that the speaker didn’t mention, and I wondered “did he know about them”?
I looked at the speakers like some secret Fraternity, I’m just a regular guy.  I had no idea how you got to speak at one of those things.  So I let it go.  While working for Pulix my buddy Dan Taylor (@DBABulldog| Blog) told me about SQL Saturday #49 Orlando.  If I liked the 24 Hours of PASS, then I’d love this.  So away I went.

“So Balls,” you say, “Who did you go see?”

Great question Dear Reader!  I started off strong.  My first live SQL Presentation ever was watching  Tom LaRock(@SQLRockstar | Blog) talk about Wait Stats, What Are You Waiting For? Next up Argenis Fernandez (@DBArgenis | Blog) Multi-Server Management with SQL Server 2008 +, then Nathan Heaivilin (@SQLHeaven ) Introduction to Execution Plans, Lunch on the Lawn where I met my friend Noel McKinney (@NoelMcKinney | Blog), next Jeff Garbus (LinkedIn) Choosing Indexes for Performance, Jorge Segarra(@SQLChicken | Blog) Policy-Based Management in a Nutshell, and Kevin Boles(@TheSQLGuru) SQL Server Partitioning from A-Z.

Yes I remember it like yesterday.  A fog had lifted.  I found people that not only understood what I did everyday but faced the same issues I did.  It was like a support group for DBA’s, only we have beers after our meetings instead of coffee and donuts (those are for SQL Saturday’s in the morning).

The after event was just as important.  I knew no one.  I was a stranger.  Even though I’m social I was quite nervous to just walk up to this gathering and invite myself in.  It is there that I met Jack Corbett (@UncleBiguns| Blog) ½ of my future Law Firm of Biguns & Balls.  Jack is a nice and welcoming guy.  We struck up a conversation immediately.  I found Noel right after that, Patrick Leblanc (@PatrickDBA | Blog), and Jack joined us and we talked for over an hour about nothing and everything.  I found my community that day.  And it was good.

THEN THERE WAS TAMPA


I wanted to get involved.  The next event that came around I wanted to submit for.  I figured I would get rejected, but I didn’t understand what SQL Saturday was for.  To grow local speakers, to give first time speaker’s a chance.  Little did I know my first shot was coming up.  I looked at what other speakers were doing and prepared.  Most had blogs, I set up the one you are reading now, got on Linkedin, Twitter.

The hardest part was coming up with the name I would use for Branding.  Several people in the community had written about that and I knew it was important.   It was a full days worth of work on the Saturday.  For the cost of the URL, signing up with Blogger, setting up a gmail account, and all the other stuff it was time well spent.  I’ve tweeked things over time, but it was quick and easy compared to the time it takes to blog J.

All of that underway I submitted to SQL Saturday 62 Tampa.  I don’t know that I’ve ever Thanked Pam Shaw(@PamShaw ) properly.  You see most of my presentations where not the final version I have now, and I tweek them constantly.  I find the more I present on a topic the more I learn about it.  She asked me for three topics, I gave her my three.  She picked the one topic that I didn’t expect.  Compression.  Whatever topic I present on, Compression will always be my first.  It set me on a very good path.

At the encouragement of Jack I submitted to SQL Rally.  I lost out on the voting but was a close second.  I got invited to present in a Wild Card spot.  I submitted to 5 more SQL Saturday’s and presented before the year was over.  I did 3 webinars, 2 on Compression (THANKS PAM!!!!), and on a whim submitted to the PASS Summit that year.

No way would I get selected.  It would be good experience.  I’d get rejected, I’d keep practicing, and next year I’d get my shot.  I submitted a Deep Dive that I had yet to finish writing.  Apparently they thought I was ready.  I got selected!

Me I’m still just a regular guy.  There are real stars out there in our community and they are awesome.  That’s not this story, Brent Ozar(@BrentO | Blog)  tells that one really good though, read Rock Stars, Normal People, and You by Brent.  It’s okay I’ll wait.  It’s worth the read.

WHATS YOUR BEGINNING


So that’s me.  That’s how I started out.  It doesn’t happen overnight.  It can happen quicker than you expected.  So where do you start?  Go to your local User Group.  Go to your local SQL Saturday.  Email a guy that works with a company that put’s on Webinars every Tuesday and Thursday at 11 am East Coast time.  *Cough* *Cough* <points to self> *Cough* *Cough*.

Tell me what you’ve been doing in the community, tell me where you’ve presented before.  If I’ve never met you have you done a webinar?  They aren’t always right for first time presenters, but give yourself a couple go’s and you’ll be ready.  Get in front of a crowd, let people ask you questions, take those questions and research them.  Write blogs, hop on forums, answer posts, research the ones you don’t know, and write blogs.  Sensing a trend?

Your story, if it hasn’t already started, can start here.  You Dear Reader have the ability to talk about something right now, that I would want to know about.  You have some insight that I would like to have.  The daily work you do gives you a perspective that no one else has.  That is valuable.  Your time, your work, your knowledge all make up who you are.

Every single one of us started somewhere.  If you’re well along that road Good Job, I hope it is paying off.  If you are just starting out, then you are in good company, because not all that long ago I didn’t even know what a SSUG was.

As always Thanks for stopping by.

Thanks,

Brad


Saturday, April 6, 2013

SQL Saturday 203 Boston, Deck and Demos Live!

Hello Dear Reader!

A quick note I'm presenting today at 1:30 pm for SQL Saturday Boston #203.

I'll be giving my presentation on the 4th Floor!  The topic today is SQL Internals, Backups, and Recovery Models!  OH MY!

Here's a quick link to the deck and the demo's, all live on the Resources page.

Also I'll be giving away a seat or two to the Pragmatic Works Performance Tuning Workshop here in Boston in June this year!  Make sure to stop by!

Hope to see you there!

Thanks,

Brad