I don’t like it, Carruthers. It’s just too quiet.
Well, I’ve done the pre-production server, the main live server and the Reporting/BI server with remarkably little trouble. Pre-production and live were rebuilds. I failed live over to our log shipping standby for the duration, which has a gotcha I blogged about before. When I failed back to the primary live server again, it was very quick to bring the databases online. I understand the databases don’t actually get upgraded until you recover them but there was no noticable delay. It’s gone from 2005 Workgroup – limited to 4GB of memory – to 2008 R2 Standard so it can now use nearly all of the 30GB in the server. It’s soo much faster.
The reporting/BI server I upgraded in situ. This took a while but, again, went smoothly. Just watch out, because the master database was left at compatibility level 90. Also the upgrade decided to use the reporting service’s credentials for database access when running reports. It didn’t preserve the existing credentials and I had to go into the Reporting Configuration Manager to put them back in. Make sure you know what credentials your server is using before you upgrade.
All things considered, a fairly painless experience. Now I just have to upgrade and reset our log shipping standby server again!
I just had some fun with a new server I set up. I configured Database Mail but it wouldn’t send mail. In the Database Log was an error message I’ve never had before:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2010-02-15T16:58:54). Exception Message: Could not connect to mail server. (A non-recoverable error occurred during a database lookup). )
I looked around and found this forum thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139909. I looked into the anti-virus software and firewall but neither of those helped. Then I noticed the last post where the original poster said “oops, I put an @ in the name of the SMTP server”. How I laughed. At least I didn’t do something that silly. Did I? Better check, just to be on the safe side. SMTP Server Name: smtp@domain. Doh!
So, rbarlow, thanks very much for making the same silly mistake first so I could find the answer.
If you’re using log shipping you need to be aware of some small print. The general idea is to upgrade the secondary server first and then the primary server because you can continue to log ship from 2005 to 2008R2. But this won’t work if you’re keeping your secondary databases in STANDBY mode rather than IN RECOVERY. If you’re using native log shipping you’ll have some work to do. If you’ve rolled your own log shipping (ahem) you can convert a STANDBY database to IN RECOVERY like this:
restore database [dw] with norecovery;
and then change your restore code to use WITH NORECOVERY instead of WITH STANDBY. (Finally all that aggravation pays off!)
You can either upgrade the secondary server in place or rebuild it. A secondary database doesn’t actually get upgraded until you recover it so the log sequence chain is not broken and you can continue shipping from the primary. Just remember that it can take quite some time to upgrade a database so you need to factor that into the expectations you give people about how long it will take to fail over.
For more details, check this out: http://msdn.microsoft.com/en-us/library/cc645954(SQL.105).aspx.