Author Archives: SQLGator
Update on Backup Tool Testing (Part 2)
After a day of testing, we were able to integrate our Ola Hallengren solution with the Red Gate SQL Backup Pro v7.2 turning a SQL compressed backup on one database from 1.2 GB to 700 MB using compression level 4. This is a nice reduction for us with storage space being at a premium. Today we will be testing the SQL HyperBac solution.
All that was needed for Ola’s script was to add the following:
@Compress = ‘Y’,
@BackupSoftware = ‘SQLBACKUP’,
@CompressionLevel = 4
For HyperBac the settings would be:
@Compress = ‘Y’,
@BackupSoftware = ‘HYPERBAC’
Enjoy!
Backup Tool Testing Begins (Part 1)
Today we are beginning our testing phase for comparing the default SQL Server backup solution to the Red Gate HyperBac and SQL Backup Pro 7. We have already purchased Red Gate, but simply have not had the time to implement this tool. We did some initial testing, but it has been over a year and there is a newer version available now. We are curious how well this integrates with our ultimate maintenance plan by Ola Hallengren. Any input?
Miscellaneous Backup Notes
This week I have been teaching my understudy about backups and restores. Here are some important things we went over this week:
- Checking “Verify backup when finished” does not ensure that the backup is good.
- Checking “Perform checksum before writing to media” does not ensure the backup is good.
- Checking both 1 and 2 also does not ensure that the backup is good.
- Checking both 1 and 2 and performing a RESTORY VERIFYONLY does not ensure that the backup is good.
- The only way to ensure without a shadow of a doubt that a backup is good is using 1 and 2 and then restoring it somewhere else and running a DBCC CHECKDB on the database.
- Unless you are regularly restoring your backups you do not have a backup solution.
- If you switch from simple recovery mode to full recovery mode then you need to kick off a backup to start the chain.
- In full recovery mode (or bulk-logged recovery) you must backup the transaction log regularly enough to keep the log from filling up. This will truncate the log and allow you to provide point in time recovery. Mileage may vary with how often to perform this backup depending upon the size and requirements of your database.
- When using full recovery mode and you need an ad hoc backup make sure that you check the “Copy-only backup” so that you do not disrupt the backup chain by moving that backup to another location. You will screw up your recovery options at that point.
- In the full recovery model, using differential backups can reduce the number of log backups that you have to restore.
Enjoy!
Roll of the Dice
My wife and I are confronted within the next year with an interesting quandary. Come August, we will be empty-nesters at a young age. Our children will be either on their own or in college. This puts us in a unique position to move onto the next stage of our life. We still have around twenty years left in our professional careers, but where do we want to spend that? Do we stay in our beloved small town?
At this point, it does not look like our children will stick around to start families here so there is really nothing other than a lifetime of friends holding us to this town. We will however, keep our house and possibly move back here for retirement. Do we take the plunge and move somewhere totally outside of our comfort zones and reap the rewards. Do we live the life of nomads moving every couple of years to a new town just for the sake of new and exciting opportunities? We almost feel like high school seniors trying to pick the right college. What do you think?
Microsoft License Advisor
Looking to upgrade to SQL Server 2012 but you are not sure how much it costs or how the licensing works? Try the Microsoft License Advisor. Even if you are not upgrading, this is a fun little utility especially if you want to see what everyone else is paying. If you want some real fun look at what charities and academia pays for their licenses. Enjoy!
Windows 8
Setting up a windows 8 Lenovo laptop for my wife today. She forgot the password and now we are recovering the machine. There was never an option to setup the USB recovery setup when we created the password. Weird because she used the same password she always used and it had her hint in there. This reminds me of the fact that passwords are useless because I just recovered the whole machine on reboot. There could’ve been an email reset because the password itself is not that secure so why bother? I think security is so far behind in terms of evolution. Will someone just put the retina scanners or voice recognition on here and lets be done with it? Enjoy!
Deadlock Graphs
Last night I learned about graphical deadlock graphs from Fargham Butt of Microsoft. I had simply used the traceflag and then read the error logs to find the information. Thus today I thought I would write a blog about the subject and decided to do some research first when I found an excellent blog post by Jonathan Kehayias (B|T) covering Handling Deadlocks in SQL Server. Jonathon is a Microsoft Certified Master and does a much better job than I can do explaining the whole subject, so I thought it more prudent to just refer you to his blog. Give it a whirl and enjoy!
Live from Tampa Hillsborough User Group meeting
Tonight I am blogging from our Hillsborough Tampa User Group meeting being held at Computer Associates. Fargham Butt from Microsoft is presenting on Profiler Tricks and this is the best turnout for this group since it started a couple of months ago. I am glad that Pam Shaw decided to create a second group as there are many new faces in the room of probably 40 people.
As of right now, I am going to go back and play with the deadlock graph feature as I have never seem that before. I did it the old way with the trace flag viewing the information in the error log. This is a fantastic visual and I may blog about it later.
If you are not going to your local user group then you need to get out there and get some free SQL learning and networking. It is worth the time investment. Enjoy!
Shrink That Database…Seriously?
Yesterday afternoon my storage administrator came to me and asked me to look at his Enterprise Vault database and make sure that they were receiving the proper maintenance because he was running a routine over the weekend to remove some e-mails that had exceeded their retention period and it was painfully slow. He was only about to delete about a half-million e-mails over the course of the entire weekend. It is even more interesting because when I checked the databases yesterday all of them combined did not exceed 100 GB.
The administrator proceeded to call Symantec and spoke with an engineer who directed him to the following page. He then forwarded the link and asked me to make sure I was following their best practices listed on their site. No problem, I would be glad to compare their recommendations to our maintenance regimen.
I have included a screen shot just in case the page disappears…
Maintenance Plan Recommendation from Symantec
Needless to say, I did not modify my maintenance regimen to include the deprecated Shrink Databases recommendation. If you are fine with this item, then you really need to read Paul Randall’s blog entry on why you should NEVER shrink your database. He explains much better than I do and he is the expert. Maybe someone out there knows someone at Symantec and we can get them to read the blog, attend a SQL Saturday, PASS Summit, or even Paul’s Immersion Event training. Someone please get them some help! Enjoy!
SQL Swampland 