Blog Archives
SQL Features Discovery Report
While going through my preparation for the MCSA exam I stumbled upon this gem that I always seem to forget about so I thought I would pass it along to you. For a quick an easy way to determine what is installed on an instance of SQL Server, run the SQL Server Installation Center and select tools. Then select Installed SQL Server features discovery report, as shown in Figure 1. This will produce a nice report as shown in Figure 2.
Enjoy!
Figure 1 – SQL Server Installation Center
Figure 2 – Setup Discovery Report
MAP Toolkit Licensing Continued
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!
Figure 1 – Edition Upgrade
Licensing Issues with the MAP Toolkit
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!
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!
Sunday Funday
Today’s installment of Sunday Funday brings you some fun with T-SQL by Nick Jacobsen, the classic 99 Bottles of Beer T-SQL style. Enjoy!
SQL Cluster Failover Issues
Today’s topic is ‘oh crap, I manually failed over my SQL Server cluster during a lunch-time scheduled maintenance window and SQL Server and SQL Server Agent did not come back online.’ The key words in their being OH CRAP!
Looking at the ‘oh so informative cluster events’ I see the following:
The Cluster service failed to bring clustered service or application ‘SQL Server (MSSQLSERVER)’ completely online or offline. One or more resources may be in a failed state. This may impact the availability of the clustered service or application.
That did not help, let’s move onto the Windows Event Viewer. It gave us: “The specified account’s password has expired.” Wait a minute! These are service accounts created by our active directory administrator that are supposed to never expire. Hmmm, I better investigate further. I look at a big group of my SQL Server service accounts and noticed about half of them are set this way. When I questioned the AD administrator, he indicated that he was training a new person and he must have done the half that was incorrect. Problem solved. Thus, it is a good idea to always check to make sure that your accounts are setup properly before you use them in SQL Server.
Enjoy!
Sunday Funday
Today’s installment of Sunday Funday brings you a great little Tic-Tac-Toe game with T-SQL exercise by Adam Haines found on SQL Server Central. Enjoy!
Sunday Funday
Today’s installment of Sunday Funday brings you a great little drawing with T-SQL exercise by Roji P. Thomas. Enjoy!
Sunday Funday
Today’s installment of Sunday Funday is brought to you by the Code Project introducing the spatial datatypes available in SQL Server 2008: SQL Battleship 2008. Enjoy!
What’s In A Datetime Anyway?
Last week we talked about my Oracle migration to SQL Server and the vendor’s use of datetime over datetime2. Today, I thought I would discuss the differences between the two datatypes.
First of all, datetime2 is recommended by Microsoft as opposed to datetime as it provides a larger data range, a larger default fractional precision as well as the optionally defined user precision level. Datetime2 is also ANSI and ISO 8601 compliant, whereas datetime is not. Which would you use?
Example:
Datetime2: 2012-06-25 12:45:10.1234567
Datetime: 2012-06-25 12:45:10.123
SQL Swampland 