Category Archives: Database Administration Tasks
RTM: Let’s Get Ready to Rumble
Today Microsoft announced the RTM for SQL Server 2012 with general availability set for April 1st! The SQL Universe and twitter #SQLFamily is all ablaze with excitement.
I personally will wait a few hours to let the servers catch a breath with some restored bandwidth. What about you?
Personally I will be setting up a Windows 2008R2 virtual lab running SQL Server 2012 and migrating the VMDK to my desktop and laptop probably. What are your plans? Do you plan to download 2012? Are you excited about Denali going RTM?
Optimizing TempDB Performance
Today I thought I would blog about TempDB performance. On one of my new servers, with 24 cores, I allocated eight TempDB files as that should be enough to distribute the load, reduce contention and improve performance. However, I forgot to restart the server after hours and it went on for a week and performance did not improve. It was at this point that I discovered that the files were not be proportionately filled. A simple Server restart resolved the issue and now it is running like a champ.
For more advice, check out Microsoft’s guidelines for Optimizing tempdb Performance.
Enjoy!
Backup and Recovery
On the drive in this morning, I was reflecting on what I needed to go over with my junior DBA today in his learning path (channeling Yoda). Backup and Recovery is so vitally important to what we do on a daily basis that I really need to drive that home to him. Therefore, I thought it would be a good topic to discuss and to blog about.
Then I remembered attending a Grant Fritchey (blog | twitter) webinar recently by Red Gate concerning this very same topic. If you missed the webinar, don’t worry as they put it up for all to see. There is really no need for me to reinvent the wheel here especially when Grant does it much better than I would. Thanks, Grant. Enjoy!
Clustered Nuts?
While looking at performance in some of my databases, I noticed there was a high number of FreeSpace scans going on. Aaargh! They are called clustered indexes, people use them! Performance, performance, performance!
In most cases it is best practices to start with a clustered index on every table created. There are rare instances when this is not the optimal setup, but for the large majority of standard databases put in a clustered index on each table. Your shooting yourself in the foot, people!
Enjoy!
My Mistake For Your Enjoyment
This morning when an aggravated user contacted me that they could not get into a new database that I setup on Friday, I discovered that I had made a mistake. Well that’s never happened before, well not today anyway.
Alright, here we go. I created three new logins that would access development and acceptance copies of a production database that I had just setup on an acceptance server using Red Gate‘s SQL Compare and SQL Data Compare (which I will blog about soon, excellent product). In my possession, actually a sharepoint list, I had the password for each of these three new logins. Today I found out that the password I had listed was out of date. No problem, easy fix!
Using SSMS I would easily change the passwords and then update my sharepoint list to the new passwords, this is DBA 101 stuff. Not so fast my friend! SSMS then presented an error stating that the password did not meet our ultra-rigid fort knox password policy (see posts on security). No problem, I forgot to uncheck the ‘Enforce password policy’ check box. That happens all the time when I get in a hurry. Now it is unchecked and SSMS presents the following error:
The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON.
There was my real mistake committed on Friday; I forgot to uncheck the ‘User must change password at next login’ check box when I created the logins. It was a rookie mistake, but I was in a hurry to get things done so I could go and get my root canal in the afternoon. Luckily for me this was not a resume updating event. In order to fix this we need to do the following:
USE MASTER
GO
ALTER LOGIN [userlogin] WITH PASSWORD ‘original password’
GO
ALTER LOGIN [userlogin] WITH CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF;
Then I was able to change the password, which I used ALTER LOGIN since I had it in the query window. What a way to start the week off. Enjoy!
My Attempt To Install SSMS on Citrix….Pass (Part Two)
Yesterday I told you about my failed attempt to install SSMS for SQL Server 2008 R2 as a Citrix application and the failure that ensued. My apologies for making you wait a day for the solution, but I was rushed with another production issue yesterday and decided to break this into two posts for dramatic effect.
Drumroll please!
I found the following solution on several websites including Microsoft:
You need to change the following registry key locations:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DevDiv\VC\Servicing\9.0
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DevDiv\VC\Servicing\9.0\RED\1033
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\DevDiv\VS\Servicing\9.0
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\DevDiv\VS\Servicing\9.0\VSR\1033
Change the values of SP and SPIndex to 1 and SPName to SP1. The original values in my case were 0 and RTM, respectively. This is a strange issue because Visual Studio 2008 was never installed on this machine.
I hope this helps! Enjoy!
My Attempt To Install SSMS on Citrix….Fail
I had a goal in mind: install SSMS on Citrix so that if I had to look at one of my SQL servers, I would not need to remote desktop into my desktop or the server itself, in case I needed to look at multiple servers. I wanted just to set up SSMS on the Citrix server as an application so that I could quickly assess problems and avoid redecorating my three monitors when the icons get squished down into one RDP session.
I downloaded the 2008 R2 SSMS installer from Microsoft instead of using my media. After running the installer, I get the following error:
SSMS Install Error
Cleaning Up SQL Database Mail Logging Records
This morning in my production cluster I noticed MSDB had grown to almost 10gb. After some help from my friends at #SQLHelp, I ran one of the built-in reports, which are great…thank you Microsoft, ‘Disk Usage by Top Tables’ to tell me which table(s) had grown since I last checked this system database. I think I found a winner: sysmail_mailitems had an outrageously high amount of records. Why aren’t these being cleaned up in a job similar to sp_delete_backuphistory or sp_purge_jobhistory?
In order to clean these items up, use the following code:
USE MSDB
EXEC sysmail_delete_log_sp @logged_before=’2012-01-13 12:00:00′
You could script this out to run nightly or weekly and remove the last 30 days. Enjoy!
sysmail_mailitems
Configuration Data Best Practice
After my recent Health and Risk Assessment visit from my Microsoft Premier Field Engineer, I learned some new and exciting SQL Server best practices and I thought that I would share them with you in this blog space. Our topic-o-choice today is all about backing up your configuration data. I must admit that I have not given this topic a second thought in the past and luckily it has never bitten me before. Microsoft defines configuration data as your sp_configure, SSIS packages and your replication topologies. These items are not usually backed up in your nightly backups and may not be contained in your system databases.
Microsoft recommends scripting your SQL Server configuration data to a file using SELECT * FROM sys.configurations anytime you make changes to your server. I would then include this in your disaster recovery walk-away bag or remote location destinations. Personally, I would recommend documenting these settings if at all possible in a Word document explaining why you have chosen the settings and if they relate to your department’s best practices so that when you get hit by a bus, your replacement DBA will understand the intricacies of your choices and not chalk them up to the ‘this guy was an idiot who knew not the ways of the force.’
In addition, if you are using replication then you should script out all of the components for your disaster recovery. It is also a good idea to back up your master and MSDB databases for the publisher, distributor and all of your subscribers. I personally have never done that, but Microsoft recommends it…so do IT! Enjoy!
Database Snapshots, A Polaroid Into Your Soul
Database snapshots were introduced with SQL Server 2005 and are only available with Enterprise and Developer edition. Disclaimer is out-of-the-way, now onto the good stuff!
You can use snapshots to provide a snapshot into the soul of your data at a particular point in time. The snapshot is read-0nly, of course and is great for reporting or auditing purposes. Snapshots are much quicker than the previous method of restoring a backup to another database and setting it to read-only access. There is also some considerable space savings in a snapshot versus another copy of your MDF/LDFs.
Keep in mind the following limitations if you do decide to explore snapshots:
- Snapshots cannot be taken for system databases (not that big of a deal, in my opinion)
- Snapshots can only be created on an NTFS file system.
- As mentioned above, snapshots are read-only copies of the database, so changes to the data are obviously not p0ssible.
- Snapshots are I/O intensive and this should always be considered especially with limited resources.
- Snapshots are not supported for filestreams!
- Snapshots cannot be backed up or restored.
- Snapshots cannot be detached or attached.
- The source database cannot be detached, dropped or restored while there are snapshots present.
- If the source database is unavailable, then so will the snapshot be unavailable.
I feel like I am leaving off a couple of more limitations, but these are the ones I deal with mostly. I will blog more on this subject at a later time. Enjoy!
SQL Swampland 