Category Archives: Maintenance
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!
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!
Corruption and Verifying Backups
This morning I had a conversation with a Subject Matter Expert and Application Administrator who asked me quite simply for a verified backup before he does an install on Saturday. My response was that I could test it manually for them and I was willing to do so but currently with our infrastructure the nightly backups are not tested without manual intervention. I have tried to change this policy for some time and unfortunately it may not occur until something bad happens. With this in mind, I do the best I can to minimize the risk given that I cannot automate a solution to restore databases on a regular basis as I would prefer. How can you minimize the risk?
- Use the WITH CHECKSUM option on your backup scripts. Read Paul Randall’s The Importance of Validating Backups article on SQL Server Central for more information.
- Use the RESTORE VERIFYONLY command (although we use Ola Hallengren’s Backup Solution which is the verify option which runs the command).
- Use the WITH CHECKSUM on the RESTORE command to recheck the page checksums in the backup as well as the backup file.
- Automate or manually restore your databases from scratch on a regular basis. (This is where we are lacking and where I want to take my servers).
- Once you have restored, then run DBCC CHECKDB against the restored database.
If you are not doing all five then you cannot say confidently that your backups are verified. However, if you are doing all five keep in mind that there is no fool proof guarantee against corruption, this merely minimizes the destruction by having viable backups. I hope this helps….Enjoy!
Nifty Code to Update Collation
Today, we interrupt our countdown series in progress to bring you some actual T-SQL coding. I had to bring out this snippet and dust it off for my purposes, to change some column-level collation in a database. To give you some background, we have a vendor product that requires case-insensitive collation at the server and table level, but case-sensitive on the column level. Yes, I know that is not best practices and seems like a weird design choice but I have to support it as the DBA.
Normally this would require a script from the vendor because it is their code, but the product allows you to create some custom tables (they default to the table level collation) in the database and those are our responsibility. So why would you need to change it you say? The developers were trying to write some reports against these tables and as you would expect they received an error (Msg 468, Level 16, State 9, Line 4 Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CS_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.) It would take forever to go table by table and column by column to change the collation, hence the need for the script!
DISCLAIMER: I have had this little snippet for a while and I do not know who wrote it originally, probably found on MSDN. I also know that it is not the most updated way of doing this (you should use the sys schema) but it works and sometimes that is easier than rewriting the code.
The first thing you should do is right-click on your query window in SSMS and set your results to text so that the script outputs you a new script that you will run in another query window. Keep in mind with some of your tables you might have to drop and recreate the indexes in order to alter the collation. Always take a backup before changing something like this and know what you are doing and changing before executing any script.
SELECT ‘ALTER TABLE ‘ + syso.name + ‘ ALTER COLUMN [‘ + sysc.name + ‘]’,
syst.name + ‘ (‘ ,
sysc.length ,
‘) COLLATE SQL_Latin1_General_CP1_CS_AS’ ,
CASE sysc.ISNULLABLE
WHEN ‘1’ THEN ‘NULL’
ELSE ‘NOT NULL’
END + CHAR(13) + CHAR(10) + ‘GO’
FROM sysobjects syso ,
syscolumns sysc ,
systypes syst
WHERE syso.id = sysc.id
AND syst.xtype = sysc.xtype
AND sysc.collation = ‘SQL_Latin1_General_CP1_CI_AS’
AND syso.type = ‘U’
AND syst.name != ‘text’
AND syso.name NOT IN ( ‘SYSDIAGRAMS’ )
Enjoy and I hope this helps you!
SQL Tool Evaluation, List of Tools
Thanks everyone for the feedback. Below are the items submitted thus far for me to evaluate, let me know within the next few days if you have any others to add to the list. Keep in mind that I am an Enterprise DBA, therefore I will not be evaluating any development tools.
- DBA xPress from Pragmatic Works
- SQL DBA Toolbelt from Redgate (we currently own)
- Ignite from Confio
- Performance Advisor for SQL Server from SQL Sentry
- SQL Server Index Analysis & Defrag from SQL Sentry
- SQL Diagnostic Manager from Idera
- SQL Admin Toolset from Idera
- Toad for SQL Server from Quest (we currently own)
- Foglight for SQL Server from Quest
- Spotlight on SQL Server from Quest (we currently own for Oracle)
- SQLCop from Less Than Dot.
Stay tuned for further details. I will have to process all of the EULAs and other paperwork in order to setup my test environment, but we have about five months left in order to complete this project. Enjoy!
SQL Swampland 