My first time, lessons learned!

This past weekend, I had the privilege of doing a presentation at SQL Saturday #514 in Houston Texas.  I had been to other SQL Saturdays before as both a participant and a “volunteer”; but I had never have spoken before.

Speaking in front of people is usually no big deal to me.  I have been designing, presenting, hosting and administrating all types of training for the Boy Scouts for many, many years.  I have taught Scouting newbies how to tie a knot to presenting on topics like “Project Planning and SMART goals” (email me if you want to know what SMART goals are).  I have even coordinated an all day training event very similar to SQL Saturday for my Council called University of Scouting.

But what was different about this experience was the topic.  I have been active in the Boy Scouts since I was eleven.  I know Scouting information, concepts, and topics backwards and forwards. I have been facilitating trainings (presenting) since I was 15. So Scouting is 2nd nature to me.  But presenting on SQL Server topics was somewhat nerve wracking.  For me it was a test of not my presenting abilities; but my SQL DBA abilities.  As an “Accidental DBA” I have always questioned my abilities with SQL Server.  Everything I have learned about computers I have learned my self, so I am always 2nd guessing myself. 

As a “First timer”, I thought it would be best to cover Beginning Level topics.  My thought was 1) I knew the material and 2) after reading Tim Ford’s #EntryLevelChallenge it motivated me to remember my fellow newbies.

I won’t go into the topic of my presentation today as this is more about the experience.  The audience, which I had 14 in attendance, was very attentive and I don’t think I put anyone to sleep!

I do need to work on my demos.  They all worked as expected; but switching between laptop and projector, throwing SSMS on the projector screen, changing screen settings from duplicate to extend was all too much.  There has to be a better way to switch between PowerPoint in presentation mode and SSMS to do demo. So I will practice that.

I thought I did best on time management.  The time allotted was 60 minutes, I said “Thank you for your time” at 59 minutes and 30 seconds. 

Over all the experience was well worth it. By the responses I received from the speaker evaluations, I think I did an OK job.  I had no major criticisms except to slow down, I assure you that was nerves at the beginning. 

SQL Saturday

This Saturday is SQL Saturday #514 in Houston, TX. 

And this Saturday, I will be doing my first presentation at a SQL Saturday.

Luckily my presentation is in the morning so I can get it over with and enjoy the rest of the day. Otherwise, I probably would be stressing about it all day!

Hostile Takeover is my presentation on what do you do when handed the keys to a SQL server you have never seen.  What information would you want to know and more importantly HOW do you get that information!

I will show some easy to use tools and scripts developed by people way smarter than I am.  Heck, I will even show you how to hack into a SQL Server!  As far as my website goes, I will update my slide deck and scripts repository after SQL Saturday!  And hopefully I will find the time to upload the information to the SQL Saturday website!

The End of an Era

A Legend is dead.

SQL Server 2005 is no longer supported.  Today is the first day that DBAs and companies cannot call on Microsoft to help with or support a SQL Server 2005 Instance. (of course I am sure M$FT will gladly accept some money for additional support).

But many companies will still run with this stalwart and continue to use it until it dies.  SQL 2005 has proven over time to be a stable system capable of doing a yeoman’s job.

Nine years ago, SQL Server 2005 revolutionized the way DBAs interacted with SQL Server.  SQL Server Management Studio (SSMS) brought a new method of administering and programming SQL server.  The Graphic User Interface (GUI) based program was miles ahead of the old Microsoft Management Console (MMC) plugin, SQL Enterprise Manager.

SQL Server 2005 was the first major overhaul of the SQL Engine, it introduced among other things XML data type, Common Language Runtime integration, and most importantly Dynamic Management Views (DMVs). 

While some say “death to the dreaded GUI, long live T-SQL”, I for one, as a visual person, love the GUI.  Yes, I always find myself using the “script” feature to learn the actual T-SQL and to execute the T-SQL for faster execution, but some tasks are made simpler using the GUI instead of T-SQL.

It definitely seems the development of SQL Server 2005 was directed by actual SQL developers, administrators, and users. 

Over the years and versions, many features have been added to SSMS to even make it a better application: detailed graphical execution plans, intellisense, Central Management Server, the list could go on.  I for one am excited that the Server Group has handed off SSMS as a separate tool because hopefully it can be placed on a more aggressive update schedule.  Hopefully the development of SSMS will keep with pace of our needs.

So here’s to SSMS and it continued improvements and features.Drinking-Cheers

So to recap, here is a list of SQL Server version still under main stream support:

  • SQL Server 2012 – support ends 7/11/2017
  • SQL Server 2014 – support ends 7/9/2019

That’s it, two versions!!  (I did not include SQL Server 2016 on this list, because technically it has not been released for general use.)

Extended support, which only includes security updates is a little better:

  • SQL Server 2008 & 2008 R2 – extended support ends 7/9/2019
  • SQL Server 2012 – extended support ends 7/22/2016
  • SQL Server 2014 – extended support ends 7/9/2024

Knowing how long it takes to get capital expenditures approved and how expensive SQL Server can be, you better start budgeting  to replace SQL Server 2008 & 2008 R2 machines now! 

B2B: Maintenance Plans

This post is part of the challenge by  Tim Ford (b|t) #EntryLevelBlogChallenge.  Which of course is a personal challenge to technical bloggers to write one entry-level blog post a month over the next year and tag it on twitter with #iwanttoshare

All of by #EntryLevelChallenge post are tagged under “Back-2-Basics” category.

Today, I would like to talk about Maintenance Plans. This SQL feature is designed to allow DBA to automate maintenance  tasks.  Maintenance plans can be found under the “Management” folder in SSMS once you log into SQL Server.  Right Click on the “Maintenance Plans” folder and select “New Maintenance Plan”

Shot1

Shot2

 

 

 

 

 

 

 

 

The quickest and easiest way to set this up is to use the “Maintenance Plan Wizard”.  This is a step-by-step, check-box tool to setup everything possible with a database.  The First Step allows you to name your maintenance plan and allows you to create the schedule you want to plan to execute on.  This can either be one schedule for all jobs or individual schedules for each task.  I would recommend individuals schedules because of the maintenance tasks can interfere with other tasks.

The next step to the wizard is where you actually pick which tasks you would like to run.  Each task is given a brief description on the bottom in yellow.

  1. Check Database Integrity – uses the DBCC CHECKDB command to check the integrity of the database
  2. Shrink Database – AVOID USING THIS AT ALL COSTS.  Shrinking databases on a regularly schedule actually does more harm than good to a database.  If you are running out of drive space and shrinking databases must be done, please do it manually.
  3. Reorganzie Index – This task will defragment and compact your indexes on tables and views.  There can be a performance gain, but the down side to this task is it is an “all-in” mentality.  It will perform the task on an index whether it needs it or not.
  4. Rebuild Index – this task actually complete wipes index out and rebuild them so they are 100% new again.  This task is also “all-in” approach and can be very I/O intensive.
  5. Update Statistics – statistics are the back-bone of the how the SQL Engine finds data. The SQL Shot3Engine uses statistics to determine the distribution of data in the data files.  If statistics are out of date SQL may have to work harder to find data.
  6. Clean Up History – the execution and results of SQL Maintenance plans are stored in the MSDB system database.  If this database is not kept in check it is feasible that it can grow uncontrollably.
  7. Execute SQL Agent Job – if you have built your own SQL Agent Job to do maintenance then you can actually execute it during this plan.
  8. Back Up Database (Full) – this does exactly what it says.  This will perform a SQL Server FULL backup of the databases. This should be done on all databases including system databases.
  9. Back Up Database (Differential)—Differential backups are taken using this tasks.  This can only be used for databases in FULL or BULKED LOGGED recovery model.
  10. Back Up Database (Transaction Log) – transaction log backups are crucial to “point-in-time” recovery of your database.  It can only be used against databases in FULL or BULKED LOGGED recovery model.
  11. Maintenance Cleanup Task – this task should always be selected because it will cleanup any files left over by the execution of the plan.

The next step of the Wizard allows you to set the order of execution for the individual tasks you have selected.  I prefer to always do Database Integrity Checks first, then indexes, then backups.

Finally you get to select which databases you would like this maintenance plan will execute against.

Depending on what tasks you select, there maybe follow up secondary tasks that you need to complete.  Where to store reports, what files to clean up, etc.  And then, click FINISH!

The Maintenance Plan Wizard is a simple way to setup maintenance plan.  But do not fall into the trap of the “click-and-go” configuration.  This is where you click all the check boxes and just keep clicking NEXT.

Please follow up and learn what each setup does and determine if you really need all the tasks.  Many experienced DBAs will use custom scripts and custom maintenance plans for each type of database to ensure the SQL Engine does as little work as possible.  The less time your SQL Server does performing maintenance tasks, the more time and resources it has to perform data tasks.

Newer is not always better!

The other day working with a client, I was attempting to move the TEMPDB data files to a new Solid State Drive (SSD) drive!  I was stoked to see the performance gain with this because I have yet to work with SSD in a SQL Environment.

Of course I hit a problem or I wouldn’t be writing a blog post about it!

it is on a volume with sector size 8192. SQL Server supports a maximum sector size of 4096 bytes

Excuse me?  SQL Server can’t support a drive? Well needless to say this was problematic.  After extensive research, the client purchased the latest and greatest SSD.  But unfortunately neither of us paid attention to the details.  As always, the Devil is in the Details.

https://support.microsoft.com/en-us/kb/926930 

Apparently MS SQL Server cannot use a physical sector size larger than 4096.  It can use smaller, but not larger!  OK, next step what exactly are we working with.

Using FSUTIL command (fsutil fsinfo sectorinfo) I did confirm the physical sector size for the SSD was 8192.  Repeated attempts to re-partition and/or re-format was not successful and it should not have been because the “physical” sectors are 8192, not the logical sectors.

Physical sector size is determined upon manufacturing and firmware. My client had to go back to the vendor to see if firmware was available to change the physical sector to 4096! 

Once again, lesson learned!  Be specific and diligent in your hardware details when purchasing SQL Server hardware.

The Importance of SQL Documentation

The other day, I was perusing around the SQLPass website and came across something challenges_thumbinteresting: #EntryLevelBlogChallenge.  Which of course is a personal challenge to technical bloggers by Tim Ford (b|t).

The concept is simple, write one entry-level blog post a month over the next year and tag it on twitter with #iwanttoshare

Well, CHALLENGE ACCEPTED!

My series of blogs posts will be tagged/filed under the heading of “Back to Basics (B2B)”

My first blog post in this series, entitled “B2B: Documentation” will be out next week!

Dont-miss-the-boatBecause I missed the boat on January, I will make up for it by posting two in February.

Today’s post will discuss the importance of documentation and the best way I know of getting that information.  Next week, I will show you how to automate the collection of all this data.

SQL Documentation

One of the most important tasks you can do as an entry level Database Administrator (DBA), is to write down what you do, how you do it and what you did it to.  Documentation does at least two important things: 1) provides a history and 2) most people remember things if they write them down.

Why is knowing the history of your server important?

I’m glad you asked!  Data by its very nature is always evolving and growing which produces trends.  These trends if un-checked or undocumented will present problem in the future, I promise.  As data grows, hard drive space is consumed.  As data evolves complex queries are developed to pull data and resources are consumed.  A DBA must be able to monitor these aspects of SQL Server in order to provide optimal performance.

The first step in documentation is to determine what you have and what you are working with. One of the most effective ways of doing this is using Glenn Berry’s (b|t) SQL Diagnostic Information Queries (Jan 2016 version) to obtain all the server/hardware information you will need to document your servers. Glenn has developed his Dynamic Management View (DMV) queries based on SQL Server Version 2005, 2008, 2008 R2, 2012, 2014 and 2016.  Each new version provided different DMV functionality and Glenn has kept up with the times.

The queries are very extensive and provide a wealth of information.  At last count there were 70 queries included in the SQL Server 2016 January 2016 version.  That is a boat load of information.

The queries are numbered and designed to be executed one at a time.  Once you have started SQL Server Management Studio (SSMS) you would open the file in SSMS by either clicking the OPEN folder or selecting FILE >> OPEN >> Open File or simply pressing CTRL + O.

Then beginning at the top with the first query highlight the text of the selected query  and then click Execute or hit F5.HighlightExecute

If you have selected the correct version of the Information query then you should be seeing this message from SQL Server.

success

From this point, you highlight and execute the next query to see the results.

With each result you can go to the FILE >> Save Results As…>>> command and actually save the results to a Comma Delimited (.csv) for viewing later.  As effective as this is; this is a very tedious method for collecting the results for all 70 queries.

Next post, I will show you a method of collecting the results for all 70 queries into one single Excel spreadsheet!

Preventing a Disaster: My Methodology Part 4

This is my last post in a series called Preventing a Disaster: My Methodology. In Part 1 I discussed the importance of running DBCC CHECKDB on your databases and provided tips on how to do this in VLDB and very busy systems.  In Part 2: Backups, I discussed the importance of a DBA knowing the RPO/RTO (Recovery Point Objective/Recovery Time Objective) of the business.  It is the RPO/RTO of a company that should determine your backup policies and procedures.  The 3rd installments discussed Off-site Locations and the fact that your backup strategy is not complete until you have a copy of your backups off the physical site.

In this installment I would like to discuss my last point in Preventing a Disaster and that is to ask yourself, “are my backups valid?”  Earlier, in post 2, the concept of “verifying” your databases was introduced and it should be a part of your backup process.  This basically verifies that what was written to the disk is equal to what is in the database.

To properly validate your backups, a DBA must perform a RESTORE of that backup to ensure that 1) it can be done, 2) that the RESTORE process works and 3) to validate the backup process.

This can be done to a development box, your desktop, a VM server that you can blow away later, it really doesn’t matter.  The point is to restore the database to a SQL Server. Typically, after a restore is complete you should run DBCC CHECKDB on the database to validate the integrity of the database.

To properly test your entire backup procedures, a DBA should get a backup file or files from archive, tape or off-site: i.e from the final resting place and restore that backup.  Restoring last nights backups is only half the process.

I once worked in a shop where a SQL server backed up to a network share where the 3rd party file backup solution then archived it tape.  Their policy was to keep 1 month’s worth of tape.  So just for kicks, I asked to get access to a 29 day old backup file to test restores.  Unfortunately, the Backup Administrator did not know how to retrieve a file from tape and place it on a network share. He was competent in getting all the necessary files to write to tape; but was unsure how to retrieve data (in his defense, he was new and was never asked to do a restore from tape).  The “backup procedures” as a whole was broken.

In Summary

In Preventing Disasters: My Methodology, I hope I explained what DBAs should do and why it is important to not skip a step. Be aware of who else is involved in the process and work closely with them to execute and test the process.