Backups – They Are Needed, Who Knew?

CollaborateImageBackups are essential for a successful business model. That statement may or may spark some topics for debate, but at the end of the day if the data professional does not have a form of backup in place for his/her business needs you may, no you will, feel the pain. It may not happen today, tomorrow, next week, but you can with 100% certainty guarantee that at some point in ones career you will need a backup of your database.

Importance

Let me start off this way and ask a very simple question, “Do I have to take a backup?” The answer to that is yes, yes you do. If you are a data professional than you should care about your data enough to take a backup of it in some form or fashion.

Types of Backups

Full Backup – this type of backup contains all the data for a specific database.

Differential Backup – think about this backup as what it’s name states; contains only the data since it’s last differential base backup; you can find these backups to be smaller in nature versus the full backup methodology

Transaction Log Backup (T-Log Backup) –  this type of backup is a record of all transactions that have been performed against the database since the transaction log was backed up. Most often times these types of backups are taken on a more frequent basis.

**Note** the differential and transaction log backups are both dependent upon the full backup initially being executed.

Disaster Recovery

Depending on how extensive your business model is some companies will rely on backups for their disaster recovery planning. Whether you log ship, utilize always on, restore databases periodically etc. backups can and will always be an essential part of disaster recovery.

Tuning Backups

Most people don’t realize that they can tune their backups. One of the ways you can do this is by turning on some trace flags and increasing some throughput. Below are two statements you can utilize.

DBCC TRACEON (3605, -1) and DBCC TRACEON (3213, -1)

What those two statements do is tell you (in your error log) what the settings are set to

clip_image002

The buffer count and maxtransfersize are the two settings you want to check. Make note of what the settings are initially; then when backing up your database, whether by a stored procedure or method of choice, you can include the following code.

WITH COMPRESSION
, BUFFERCOUNT = 800
, MAXTRANSFERSIZE=4194304

**NOTE – never take code from the web and execute it in production. Utilize this in a testing environment to see how it performs.

This little trick was picked up by watching the mighty Sean McCown at PASS Summit 2014 in one of his sessions.

Testing Backups

Wait, what? You mean I need to test my backups. Let me pose this question another way. If you take a solid backup and you store it for a certain period of time; then how do you know if you can restore it or not? Taking backups are only half the process; I used to think early on in my career that I was golden to have a backup versus the people who don’t take backups at all. Sure that is somewhat true but the flip side to that is I was missing the bigger picture; periodically test your backups. In a perfect world an automated process would restore backups to an isolated environment then fire off an alert if you find one that could not be restored. Most shops don’t or can’t go to that extent so at the minimum periodically test your backups for validity. Not only will it prove that your backups are working but will keep your skill set honed in the restoration process.

Wrap Up

Backups – they are important. As with anything in your data professional career; take this concept to be very important. If you aren’t backing up your data than I suggest you start. If you are backing up your data; then are you sure you can restore it? Are your backups taking forever; perhaps you can tune them? I tell you what…keep reading below and you can check out what some of my colleagues have to say around backups. Enjoy

On a SQL Collaboration Quest

Four SQL professionals gathered from the four corners of the world to share their SQL knowledge with each other and with their readers: Mickey Stuewe from California, USA, Chris Yates from Kentucky, USA, Julie Koesmarno from Canberra, Australia, and Jeffrey Verheul from Rotterdam, The Netherlands. They invite you to join them on their quest as they ask each other questions and seek out the answers in this collaborative blog series. Along the way, they will also include other SQL professionals to join in the collaboration.

SQL Sentry Plan Explorer – Don’t Leave Home Without It

CollaborateImageIf you’ve been around me long enough then you realize that I am a Red Gate fanatic. With that said I’ve grown accustomed to another utility called SQL Sentry Plan Explorer by SQL Sentry.

I’m a big Aaron Bertrand (B|T) fan and he is the one that first introduced me to this utility via his blog; if you aren’t following this man then you are missing out. He has some stellar commentary on his blog and twitter and has a deep passion for the SQL Community.

What is SQL Sentry Plan Explorer

One thing that I think of when the words SQL Sentry Plan Explorer are said is query optimization. Do you have that one go to script or utility when you are troubleshooting stored procedures and queries? If so, and this utility isn’t part of it then add it to your tool DBA tool belt.

There are two different versions that you can download; a free version and a Pro version. Both utilize SQL Server to help streamline and make optimization of queries become more efficient for the every day data professional.

What does the Free Plan offer

The free plan offers quite a bit. You can check out the overview here

  1. Enhanced plan diagrams
  2. Statements tree
  3. Plan trees
  4. Top operations
  5. Query columns
  6. Join diagrams
  7. Parameters
  8. Expressions
  9. I/O

These are just a few of the things you can expect to see with the free version

What does the Pro plan offer

In addition to what the free plan offers you will find some of the following:

  1. Wait Stats
  2. Full Query Call Stack
  3. Open Deadlock files
  4. Plan Filtering
  5. Rotate plans

…and much more

Personal Approaches

In the past I have found that the free plan has allowed me to:

  1. Quickly identify bottlenecks
  2. Review and compare statements
  3. Review costly operations
  4. Review index seeks and scans

I think one of the most beneficial items that SQL Sentry has done for this utility is adding it as an SSMS add in.

Utility Downloaded, Now What?

Like any other tool, the goal is to help streamline and make the data professional more efficient. With that said, the data professional has to know the “why’s” and how things function within SQL; don’t utilize the tool as a crutch nor should you utilize the tool as a means to solidify commands like a robot.

A SQL community member and MVP has taught me (thanks Tim Chapman (B|T) that just because you have all these tools at your disposal you, yourself have to be able to connect all the dots which means what guys – learn and know what you are reading.

You see an index seek; great….now why?

You see that Plan Explorer is showing you a critical alert in red on index cost…great; why?

Don’t just simply follow a tool; know what you are looking at.

Conclusion

I highly recommend SQL Sentry’s Plan Explorer; from personal experience it is helped me on more than one occasion. You can view all they have to offer here.

Well done SQL Sentry; well done indeed.

I tell you what; check out what my other colleagues have to say on the matter and how they have benefited from this utility:

If you want to read more about the newly released SQL Search, don’t forget to check out these blog posts:

On a SQL Collaboration Quest

Four SQL professionals gathered from the four corners of the world to share their SQL knowledge with each other and with their readers: Mickey Stuewe from California, USA, Chris Yates from Kentucky, USA, Julie Koesmarno from Canberra, Australia, and Jeffrey Verheul from Rotterdam, The Netherlands. They invite you to join them on their quest as they ask each other questions and seek out the answers in this collaborative blog series. Along the way, they will also include other SQL professionals to join in the collaboration.

 

C-R-U-D The Basics

C-R-U-D The Basics

What is CRUD? Well, there are a lot of things I can think of when I use the term CRUD; however within the wonderful world of technology CRUD is an acronym that surprisingly enough, when I polled, didn’t really give a straight answer. That really surprised me; so with that said I will walk you through the basics of the concept.

C-reate

The letter “c” stands for create; more specifically inserting some form of data by various methods or means into a repository or holding take. I want to take this down to its simplest form which the below example will depict:

 

Insert

 

Inserting data into the PastHist table is as simple as the T-SQL query; but don’t just limit the thought of creation by this simplest form. You could have data being created through front end apps, SSIS packages, or many other methods.

 

R-ead

Depends on who you ask or talk to. The letter “r” can stand for read or retrieve. A simple retrieve statement can be a Select as below:

 

Select

 

SelectResults

 

What is this he didn’t use a NOLOCK – don’t worry I can save that for another blog post but as you can see in its simplest form retrieving the data out of the table you just inserted into is not that cumbersome.

 

U-pdate

The letter “u” can mean update or modify if you will. Let’s see how easy it is to do a simple update statement below:

Let’s update the name for our record to be John where the id = 1

 

Update

 

UpdateResults

 

As you can see the data has been updated and John is now our data set in the name field column.

 

D-elete

The letter “d” stands for delete or destroy. Below is a simple script to delete the data out of the table:

 

Delete

 

There are some methods I could have used to remove the data; such as Truncate Table but this is not the time or place for me to distinguish between the two. I chose the delete method on the search criteria of id = 1

Results after the deletion:

 

DeleteResults

 

Conclusion

This is breaking down C-R-U-D into its simplest form. Please do not limit your thinking to these very basic simple queries. Expound on it and your thought process on what all C-R-U-D entails. As with anything you find on the internet DO NOT take queries and just execute them blindly on any environment. Doing so makes you assume the risk.

What else are others saying?

I tell you what; check out what my other colleagues have to say on something they learned recently around C-R-U-D:

 

CollaborateImageOn a SQL Collaboration Quest

Four SQL professionals gathered from the four corners of the world to share their SQL knowledge with each other and with their readers: Mickey Stuewe from California, USA, Chris Yates from Kentucky, USA, Julie Koesmarno from Canberra, Australia, and Jeffrey Verheul from Rotterdam, The Netherlands. They invite you to join them on their quest as they ask each other questions and seek out the answers in this collaborative blog series. Along the way, they will also include other SQL professionals to join in the collaboration.