SQL Internals & MCM Training

Brad is currently presenting on how to be an exceptoinal DBA.  This is a live blog of the presentation.  Brad starts off with a very good slide.  I’ll present the slide, and my thoughts on the matter.

You must:

-Enjoy Technology:  If you hate computers, what are you doing here?  I, personally, as a technogeek (Ali speaking here) always ask potential hires ‘what’s the first thing they do when they get home?’  If they answer ‘turn on the computer’, I know I got this part covered, but if they don’t answer that, it doesn’t mean they don’t love technology, it means they might have a life and love technology

-Enjoy Challenges:  You should look at a problem and instead of thinking ‘oh God no’, you should think ‘oh good, something new to solve and make better!’

-Enjoy Problem Solving:  SQL Server requires us to think on the architect level and that requires problem solving every day, whether it’s fire fighting (bad!) or process improvement (good!).

Embraces Change:  You must embrace change and not be afraid of it.  Bad DBA’s are afraid of change because it makes their skill set obsolete, and they have to spend time to learn new things to stay competitive.  If you look at that with a ‘oh God’ look on your face, then it’s time to re-evaluate why you’re in that situation or what you can do to change it.

Trustworthy: We deal with data more than any other field in I.T.  We must be trusth worthy no matter what.

Dependable:  You can’t be wishy washy and vulnerable spot.
You need good people skills:  We have to work with developers, sys admins, end users, report writers, and the business people.  Remember to take The Smiling DBA’s approach, a smart DBA is always smiling because we know we can solve the issues.  We must listen well and ask the right questions.  You must be present in your meetings and not be afraid to speak, as well as ask questions.

Brad did make a very good point on setting realistic, short term goals.  Remember that many short term goals translate to great long term successes, as long as you keep working on new short term goals.  This is one take away I got from this session for sure, I never set the goals but just focused on learning and helping over all.  Now with setting short term goals, we should see bette rreturns that are measurable.  Ah yes, MARKM strikes again.

Please go to www.red-gate.com/books to pick up Brad’s book “How to Become an Exceptional DBA”

Lately security has been a bigger deal than the previous decade.  MySQL got hacked by forgetting to enable one of their best features which disables ad-hoc queries, Sony was knocked out for weeks, and credit card companies finally got sick of waiting on Congress to act, so they created their own standard for businesses.  As a result, companies are scrambling more to secure their databases and one of the core requirements is to disable SQL Server authentication completely, and audit users access to databases.

As a result, some reactive or accidental DBA’s forget to check what databases and jobs are owned by the accounts they are about to disable.  Everything seems to be fine and dandy after disabling it, until the service restarts or the DB goes offline/online, and nothing works.

You should always check database and job owners before turning off that account!  Simply execute:

SELECT name, suser_sname(owner_sid)

FROM sys.databases

This will return every database owner with the DB name.

This was the first tiny baby step towards security and being proactive.  Save your company downtime by following some best practices and also check Brent Ozar’s Blitz script.  It’s the best bang for your buck.

EDIT:

My co-worker just tested this and showed me that it doesn’t work like that in 2008, the DB will remain online!  However, I did some more testing and noticed that if SA owns the jobs, or the disabled user, the jobs will still fail.  EXECUTE AS also showed issues.

So I started working as the production DBA for an up and coming CDN (Content Delivery Network) which is already handling 5% of all internet traffic in the world. The solutions they come up with are robust and very creative. I must say, I’m excited to work for an internet company finally. For those of you stuck working for non technical people, let me tell ya, working for an internet start-up company is great!

Before when I wanted to even look at a server in production I had to read up on advanced negotiations (thank you Brent Ozar), practice hypnosis, and pray that the business people were having a good day and will allow me to log into the server or connect to it through SSMS (Don’t RDP to a server and open up SSMS, do it from a non production machine). When I asked ‘hey, can I connect and review your very critical production server with multi-terabyte OLTP databases on it being ran 24/7′ the answer was ‘Well.. I don’t know, can you??’ Sweet.

You will let me log into the production server. You will let me log in the production server. You are getting sleepy.

Then the CEO/Chairman took me to his office to introduce himself. Within an hour and a half I saw him SSH into Unix to answer some of my questions, bring up some programs he was reviewing, and discuss their logarithmic growth in depth and at the same time, quiz me! Yes folks, the CEO quizzed me on technical questions. WOW! Awesome.

A few days later we had a database they wanted me to review, and I needed to see some table structures. I reviewed some information and saw the table was already at 60gb….yikes. I then saw it had never had DBCC CHECKDB ran across it (at least, not on that server, maybe they were backed up and checked elsewhere like I did), and the backups have not been tested recently. Thus I stated, ‘hey, I’m not partitioning this table until I check it for corruption and test the backups’.  We  fixed a few minor problems I won’t get into, and I asked them for a ‘test server’ to restore backups to and to check the backups for data corruption. What did I get the next day? A Dell R710 with 2 E5620 CPUs, 48gb of ram, and 2.5 TB of disk space… the very next day. WOAH!! I decided to press my luck and said ‘you know, we could use a DBA sand box, can we get a ESXi environment here so I can take snapshots for my testing, and roll back?’ I expected an answer from Oliver Twist resembling “MOAAR? YOU WANT MOAR??” except the answer was…. ‘sure! check with us later’. Right this minute, as I am staying late in the office, I hear them configuring it for me.

Please sir, can I have moar Internets?

Bloody awesome. Stay tuned for more adventured in CDN land.

Lesson learned today:

-Always ask for Windows Enterprise Edition when provisioning test servers.  The 32gb RAM limit sucks.

Reminder of the day to the readers:

-Always be thoughtful of licensing.  Make sure that your test servers are not counted in the annual license count.  Don’t forget that failover nodes in clusters do *not* require a license count.  Take good advantage of that.

DMV Queries for SQL Artists

Science: Something which will consistently show the same results regardless of environment.  Completely predictable and set events.  Science occurs on the proper usage of syntax rules for best performance due to the set way in which they perform against the data internally.  If you ever recreate those exact conditions again, that same query will always perform as it did before.  There is no creativity in this realm, only set formulas, algorithms, assembly language, and finally binary.

The Arts: The way we look at the problem, draw upon our experience and knowledge, find ways to conceptually develop the product and design, is the art.  Creativity reigns supreme in this realm.  You might be set in your tools and how you use them, but your ability to come up with creative solutions and to what end would be the art.

Today I saw a great presentation on DMVs by Glenn Barry, and I thought to myself “why am I watching another presentation on DVMs?  I already know how they work.”  I decided to listen anyways since Glenn is so knowledgeable, but half way into his presentation it clicked.  The new generation of DBAs get to unleash their creative solutions now, it lets the DBA spend more time being an artist than a techie.   Sure the time saved by having the DMV stats recorded is great, but *how* you can slice and dice the data completes it.  Now instead of just looking at boring stats all day, we can easily join multiple tables to give us completely unique and fresh views at our database engine.  I would expect those DBAs that have a passion for B.I. would share an extra bit of I.T. giddyness.

It reminded me of my youth when I used to (attempted to) run a music studio and sign artists to my brand.  I learned early on that I have to have all of the technology in tip top shape and backup solutions ready to be brought out within enough time for a quick 5 minute break.  If I failed in delivering that to my artists, then the creativity in the room died and the product that was created wasn’t so hot.  When creativity is flowing and you are on the right track to a better way of doing something, you can’t let technical details derail you and take your focus away.  In a way, we can’t let ourselves be overwhelmed by technicalities all day, and being able to gain knowledge out of the data is a lot easier than it was in SQL 2000.

The art portion is in the human mind.  After seeing Glenn’s presentation today, I saw that he has a totally different way of thinking than I do, and I like it!  Now isn’t that what great classical art does?  Expose the mind of the artist to you, the dear viewer?  Then a good viewer will learn about how others think, and develop their own thinking further.  I got a insight into the presenters mind in how they look at data on a birds eye view and fine detailed levels.  Seeing how they would tackle the same issues I fixed, and understanding some of the thinking behind it, made me see different ways to work with SQL server using the same tools.

Catch Glenn’s blog post for the content, and as soon as the live meeting video is up, I will link it.  Well worth your time and share your thoughts on what the difference is between Art and Science with SQL server.

Troubleshooting & verifying SQL backups is a critical but often overlooked function of the accidental DBA. Most videos train you on how to click the correct order in SSMS, but do not show you how to verify your backups or how to diagnose & fix backup problems. Many people believe they have backups, but have never tested them and learn when they need it the most, for a critical restore.

We will use SSMS (SQL Server Management Studio) to create, test, correct, and execute our backup policy for full backups as a foundation. Verify that your backup policy aligns with the needs of the business, as shown how to do in part 1 of this presentation.

Must set the video to full screen and high definition mode or else the text looks blurry.  Click here to download the video.

Part 1 needs to be accompanied with this video.

HAIL Mythical SQL Server! We bring you gifts and pray for safe backups.

Does this picture resemble your SQL Server backup and recovery strategy?

If you are praying that your backups work, you’re doing it wrong.

Yikes! You’re a windows guy and have heard scary stories about hitting the wrong key and completely wiping out an entire SQL server. Some sysadmins prefer to treat SQL server like an ostrich would, by sticking their head in the ground and hope nothing goes wrong. I’m here to tell you, it’s going to be OK. By following some basic rules you can negate most of your nightmares. But you’re playing in the lions den if you stick your head in the ground and pray. The secret to a good SQL Server environment is to be proactive. Remember, you have 2 duties: (click to read more)

Woohoo!  Be sure to check out the Red Gate sponsored free full day of training in LA and London!  If I’m lucky, I can stay in London an additional 2 weeks after the Immersion event by SQLSkills, and check this out in LA *and* London.  

If that wasn’t enough to wet your appetite, there also is a ‘t-shirt’ size option at sign up.  Free T-Shirt?!? OH I’M IN. 

http://sqlinthecity.red-gate.com/register/

Which SQL In the City DBA are you?  I’d be that cute one, the brunette.

Here are some notable speakers from the event:

• Steve Jones, SQL Server MVP
  SQLServercentral.com
• Brad McGehee, SQL Server MVP
  bradmcgehee.com
• Grant Fritchey, SQL Server MVP
  ScaryDBA.com
• Denny Cherry, SQL Server MVP
  mrdenny.com

Was in my opinion a great success for us So Cal SQL geeks.  All in all, we had a good big and diverse crowd and there were lots of good questions asked.  I think I reached the question quota limit for Index Internals and was tempted to ask more at the after party!

I wish we had some video of the events to share with you all, but it didn’t happen!  Be sure to check out the trianing resources link for training resources instead since we don’t have anything to share from the event.

In less than 8 hours, 5 rooms filled with experts and MVPs will present top notch training usually found for thousands of dollars, for free.  This new direction SQLPASS.org is taking was spearheaded by Microsoft in producing the MCM videos which we have learned to love so much.  However, the SQLPASS team made it even more accessible by bringing the community leaders to the people through sponsorship deals.

*You can take a peek at SQL Saturday #33 on YouTube while

7:00am: I’m going to arrive early at 7am to meet some of the speakers that are causing quite a stir on #sqlsat73 and to get free bagels/danishes for breakfast (score!)

The full packed schedule has so many conflicting choices.  We have 5 tracks to pick from, and no video archive so you HAVE to pick the best one for you.

8:30am: I’m going to start with Diana Dee
Relational Database Design 101.  This sounds like a really simple class, but I’m already familiar with SSIS to go to that session instead.

9:45am: I’m torn between 2 good sessions right here.  I’d love to see Denny Cherry as he is a great speaker, but the Query Optimization session by far the most important topic discussed at that bracket.

1:30pm: It’s almost midnight the night before, and I still don’t know which of these 2 sessions I’m going to go to.  I’m determined to put Powershell in my skill set and finally replace the old command shell, but the partitioned tables query optimization & management seems more useful and harder to get information.  There’s a lot to consider when doing partitioning with data for archiving purposes to stop the query optimizer from doing a scan.  Most likely it’ll be the partitioning session.

2:45pm: The SQL panel discussion should be the best presentation as it’s so open ended.  People ask all kinds of questions you might not have normally thought of, so it’s good to get a different perspective in a open ended environment.

4:00pm: Denny Cherry, index internals, for sure, without a doubt.  I’m even listening to all the index internals modules again provided by SQLSkills.com while I drive down to the session in preparation.

5:00pm: Going to hang out with my new associates Michaels Sports Pub and Grill!

Anyone who is interested in becoming an MCM of SQL Server should view SQLSaturday.Com and sign up for any events near you.  I will write up any take aways I have from the event tomorrow night.  Look for tweets through out the event!