Category Archives: Database Administration Tasks
Red Gate Backup Pro Continued (Part 5)
Continuing in my series on the Red Gate Backup Pro 7.2 utility for SQL Server, I tried yesterday rather unsuccessfully to set up a development server to do all of my virtual restores in order to verify all of my backups for my production servers. It works great on this particular development server for the backups on this particular server. However, the ability to do this across servers is complicated with a complex array of directions with advanced security options that really obfuscates a simple idea. Can it be done, Red Gate says yes with their directions provided on their website amongst them this link and this link in addition to their security model.
Maybe I have missed something, I am not sure. But it looks to me that the security is far more advanced than it needs to be for the idea of a ‘virtual restore’ and I do not want a simple AD account to have so many privileges. Maybe I will step back and approach it again after the holidays.
However, if that falls through I will just set this up on each production server. I am also trying to script all of these virtual restores into a single script that grabs all of the databases instead of hard coding each individual database in the script. This will automate my maintenance such that when new databases are created, I will not have to worry about setting them up in the restore script. I have some ideas but have not been able to get them to work yet. Press on and enjoy!
Some Additional Findings on Red Gate Backups (Part 4)
I just wanted to point out a couple of things I noticed after a weekend of Red Gate SQL Backup Pro compressed backups and virtual restores on our development servers:
- The <database_name>_Restored database generates a few SQL Monitor alerts for all of the newly restored temporary databases that are eventually dropped after the DBCC CHECKDB is run against them. On our system we got up to five notifications per database:
- Database file size increased (Custom metric)
- Rate of write transaction/sec increased (Custom metric)
- Custom metric collection error
- Page verification
- Database unavailable
- When using a plain jane AD account to install and run the backups, you need to go into each individual database and add that user to the ‘db_backupoperator’ database role. This is not necessarily Red Gate’s fault as SQL Server does not have this at the instance level (I still do not understand why, but maybe I am just an idiot), however the Red Gate tool maybe should have warned me about this at some point instead of cryptic error message on failure, in my opinion. The Red Gate security model states that the account needs to be part of the sysadmin fixed server role which goes against best practices (if you go further down it does give a workaround like I suggested but I didn’t make it that far the first time I read the page, nor the second).
- There was a few servers that I had difficulty installing the agent onto unless I used an admin account even though they had the ‘log on as a service’ and sysadmin role on the regular account as well as permissions on all of the folders. I could then switch the service back to that account after installation and the service starts fine. They might have been missing logon locally. I will try that on the next server to test it.
I will keep this thread running as it appears there are some other issues. Enjoy!
Finalizing SQL Backup Assessment (Part 3)
After testing HyperBac yesterday in our SQL Backup tools series throughout this week, we realized that SQL Backup Pro is the way to go. The HyperBac compressed backups were not smaller than either SQL Server native compression or SQL Backup Pro. However, all three integrated well with our Ola Hallengren maintenance scripts. We also decided to do one native backup a week for disaster recovery purposes in case we cannot get the tool to work following a disaster. With a proprietary tool, my manager felt better in a DR scenario having at least one native backup, so ‘we gotta do what we gotta do’ to keep everyone happy.
I have recommended to Red Gate that they add more automation into the scheduling of Virtual Restore jobs to grab all of the databases in the job instead of individual jobs for each database. That I think is the only area I found to be lacking in the tool.
Enjoy your weekend.
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!
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!
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 