Monthly Archives: December 2012
With the deadline to submit a presentation for SQL Saturday Tampa looming over the next two weeks, I am faced with a dilemma. Do I scrap my previous presentation and start over or come up with another presentation? I was not overly satisfied nor happy with my new presentation in Orlando back in October. I was happy with my presentation in Pensacola in June, however. I have a few ideas for some new presentations, but I think I might try those out at the user group level first. Now that I think about it, I may just fine tune my current presentation and work out the kinks. Thanks for listening while I worked this out in my head. You are the best listener.
In addition, Thomas Larock (b|t) tweeted a link this morning about the 12 Most Deadly Public Speaking Sins, check it out if you are thinking about speaking. Please register for SQL Saturday Tampa to be held on March 2, 2013. See you there!
If you have been keeping score at home, I have been trying to work out the issues of implementing the Red Gate SQL Backup Pro and Virtual Restore. This series may sound like a bunch of complaints, they are not. I am just working through the issues and hoping to help others struggling and hopefully improve the product if possible. If some of these items are easily fixed, I could not find the information easily on their website otherwise I would not have posted it. Or it could be quite possibly that I am just an idiot.
I was able to resolve one issue from yesterday post, the weekly regular SQL Server copy-only backup. By tweaking the HyperBac Configuration Manager settings for the BAK extension and disabling HyperBac compression (see Figure 1) we were able to get default SQL Server backups running once a week in the midst of using SQL Server Backup Pro for our daily backups.
We also noticed a weird error in our SQL Monitor tool when using the Virtual Restore (see Figure 2). The drive is only 300 GB where we store our Virtual Restores. It looks like the Virtual Restores raise a drive space error based upon the “actual” size of the restore instead of the virtual size.
Just in case you are wondering, I compiled my long list of quirks and submitted it to Red Gate support. I am confident that they will be able to help as they have been excellent in the past. Personally, I just like to figure things out before I talk with support, which many probably find as a character flaw. Maybe it is male ego or pride but I like to solve problems without help if at all possible. On the other hand, I am never afraid to ask for help, I just want to make sure I have tried everything before hand. Enjoy.
Last week I blogged about my experiences implementing the Red Gate Backup Pro and Virtual Restore. Today I have a few more observations and items that I need to investigate.
- We setup a weekly copy-only backup using default SQL Server compression to give my supervisor ‘warm and fuzzies’ over switching to a proprietary backup format. We noticed this weekend that the servers with the HyperBac/Virtual Restore tools installed failed on a regular backup. When we manually stopped the HyperBac service and ran the backup, it completed successfully. When we re-enabled the service the backup failed again. We decided to forgo using HyperBac, I may try deactivating that part of the license and see if that alleviates the issue.
- Some of our restores fail when we script them and run fine when we run them manually.
- Some of the restores report DBCC CHECKDB issues and when we run DBCC CHECKDB on the actual database there are no issues.
- I would like to script the real Virtual Restore product, but as of yet have been unable to.
I will keep everyone posted as to my progress. Enjoy!
Over the last few months, I have talked to a few different DBAs who tell me that their environments are setup in such a manner that many DBAs are involved yet no one ‘owns’ any of the servers. When I ask who is responsible the answer is either everyone or no one in particular. Obviously the DBAs in question are high quality administrators working within a system that they have no control over. Maybe this is the norm for large organizations, ones with hundreds or thousands of servers. Personally, I have never worked on such an economy of scale.
I understand that there are a large number of servers, but I would think that somewhere those numbers would be sliced up into smaller groups of administrators allowing for redundancy but incorporating ownership by a small group. Then that group takes ownership and is responsible for the bank of servers.
My style is such that I love and care for my servers as well as taking ownership of them. I know their history, their quirks, their specs, and if it is a detail that I cannot memorize then I track it. I have 32 SQL servers with almost 300 databases, therefore it is impossible for me to memorize all of the nuances but I have them tracked and the information easy to find such that I can tell you within a minute or two if you ask. How can you do that or do you even need to do that when you have thousands of servers?
In addition, ownership allows me to make those servers a priority and hopefully nothing slips through the cracks. However, if it does then it is my mistake and mine alone. I am responsible. End of story. In those organizations no one is really responsible from what I understand. How do you accomplish or do you even care in a larger organization? Is this much ado about nothing?
Today I received word, after a lengthy four-month process, that my petition to return to the University of Florida after 23 years has been approved. This was one of the major goals that I set for myself last year and this year and now it has been accomplished. I have always felt a gaping hole inside having never completed my degree, not because I really needed it, but because it has always burned inside of me as a personal failure. At this stage in my career it is probably not necessary, but it is something I set out to do and never accomplished. It will be completed, I will not leave this world willingly without accomplishing this goal.
Continuing yesterday’s post regarding the licensing issues I discovered using the MAP Toolkit, I was able to resolve the issue without much difficulty. The license was showing in the MAP Toolkit as developer edition. We have volume licensing so I am sure now that wrong installer was selected at the time of install because we do not use keys as they are populated by the installer.
To correct the problem, we ran the installer on the server effected and selected ‘Edition Upgrade’ and selected the media. When prompted for a product key, we had to fool the installer with some trickeration. On a separate VM, we installed SQL Server using our Enterprise media which generated a key code in the box at the same point in the installation. We then typed that key into our real installation and proceeded with the ‘Edition Update’ thus allowing the installer to realize that we were licensed for a different version. This successfully updated our edition to Enterprise as this was the license purchased for this server.
However, after stopping and restarting SQL Server to get the new version to register we noticed that the SQL Agent was failing with no errors noted in the SQL error log or the Agent log. We ran the ‘Repair’ from the installation center (shown in figure 1) to make sure that there was no corruption with the previous edition. That did not resolve the problem. When running the agent from the command line we got a different error that pointed us to the login being incorrect. Somehow running the Edition Update locked out the AD account which is used only by this service on this server so a user could not have been to blame (or should not be able to). Once unlocking that account, SQL Server Enterprise Edition was running smoothly! Enjoy!
Today, I ran the MAP toolkit (Microsoft Assessment and Planning) to identify our licensing structure and found an unwelcome surprise. The four new cluster nodes I installed earlier this year showed up as developer edition and not enterprise edition. Our administrator who handles the license keys, contracts, and downloads from MS gave me the license key and the ISO. When I pointed it out today, she said there was no key for an enterprise installation. That means she gave me the wrong ISO and/or she did not know it did not require a key back ten months ago. The point of this blog is not to assess blame. But to point out a pitfall in your installation through my lesson learned.
Now the real question is how do I fix it? A friend told me that I could change a registry key, but some others have indicated that I need to do an in-place upgrade. I will keep you posted. Enjoy!
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!
- 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!
I have been debating internally for months on the decision to buy a Mac. I have been wanting one for a while now mainly because I want to learn to write iOS apps and return to my programming roots. After having an iPhone and iPad for several years this is the next logical step, obviously.
I have programmed in Unix, Linux, DOS scripting, Windows with Progress, Delphi, C++, and Visual Basic as well HTML. Do not get me wrong my passion is SQL Server, but I enjoy creating things as a hobby now mostly.
I think I will buy one but I think the timing is off, probably better to wait until after Christmas. Enjoy!