SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2008 Upgrade Gotchas & Fixes


SQL Server 2008 Upgrade Gotchas & Fixes

Author
Message
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90770 Visits: 17960
Never mind the upgrades, with SQL Server 2008 the real gotchas come in when installing a clustered instance. All sorts of little nasties to contend with there ;-)

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Brandon Forest
Brandon Forest
SSChasing Mays
SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)

Group: General Forum Members
Points: 651 Visits: 446
I upgraded four clustered server instances and found it fairly straight forward, but that's just my experience. I'd be interested in hearing about your Gotchas. Everyone has different challenges to overcome when upgrading. Oops, just re-read your post, yup installing a clustered instance from scratch is challenging. I'd still like to hear about your gotchas, as it would be valuable thread to expose to others going down that road.

Brandon_Forest@sbcglobal.net
S. Kusen
S. Kusen
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4507 Visits: 1128
For starters: the syspolicy_purge_history job -- needs to be updated for the virtual server name of the sql instance; if you install node A on the D:\program files\... path, when you add the B node to to the failover cluster, it will put the binaries automatically on the C:\program files\... path. So, when a failover occurs, that job will look for the SQLPS.exe file in the D:\ path, but it won't be there. Haven't opened a case with MS or posted a connect on this one, but its annoying. The concept of using powershell out of the box for SQL Server disturbed me a bit...
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90770 Visits: 17960
S.K. (11/19/2009)

For starters: the syspolicy_purge_history job -- needs to be updated for the virtual server name of the sql instance;

if you install node A on the D:\program files\... path, when you add the B node to to the failover cluster, it will put the binaries automatically on the C:\program files\... path. So, when a failover occurs, that job will look for the SQLPS.exe file in the D:\ path, but it won't be there.


these are just a couple. There's also the licence key issue and the famous "Current SKU invalid" problem. When addding a cluster node and getting to the point where the licence key appears if you dont delete the key first and click next then go back and re type the key you get a message informing the current sku is invalid and setup shuts down.

I have also found problems when supplying the password details for the service accounts.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
reuben.anderson
reuben.anderson
Old Hand
Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)

Group: General Forum Members
Points: 375 Visits: 224
I'm not sure what you've done.. but I've found I don't need to install Reporting Services to run the Upgrade Advisor, and it seems to work just fine against remote servers too.

Very good point about planning the reboot(s) for the pre-requisites. The .Net3.5 can take a while.
cfergus 84887
cfergus 84887
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 7
Another major gotcha for SQL 2008 is the fact that you can no longer truncate and or shrink your database transaction logs. The queries to perform this task are deprecated in SQL 2008.

The deprecated queries (which are supported in SQL 2000 and SQL 2005, but no longer supported in SQL 2008) are as follows:

--To truncate the log file
backup tran <dbname> with no_log

--To Shrink the T-log container:
dbcc shrinkfile(<dbname>empty_log)

The only way to keep the Transaction log small, is to do incremental backups (multiple times during the day) of the transaction logs.
Brandon Forest
Brandon Forest
SSChasing Mays
SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)

Group: General Forum Members
Points: 651 Visits: 446
Yep, I figured that out the hard way. I had a stored procedure called usp_ShrinkAllLogs, which used the BACKUP LOG <LogName> With Truncate_Only and DBCC SHRINKDATASE(<databasename>, 10). I changed it to instead to set the recovery model to simple, then use the DBCC SHRINKDATASE(<databasename>, 10) to recover the disk space, and then set the recovery model back to FULL. Of course you need to do a full database backup after that to reset the Log Sequence Number. Do a search on usp_ShrinkAllLogs in the scripts section and you'll find my code.

Cheers!

:-D
george sibbald
george sibbald
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39152 Visits: 13702
MS want the correct procedure to truncate logs to be setting recovery mode to simple, hence deprecation of no_log and truncate_only.

However dbcc shrinkfile is still available, it would be a shame to have to use to shrink database if you only want to shrink the log!


http://msdn.microsoft.com/en-us/library/ms189493.aspx

---------------------------------------------------------------------
mflynn-549529
mflynn-549529
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 58
One gotcha that got us was testing 2005 databases.

The test we wanted to do was see how a large database performed on the new 2008 machine. We detached it from the 2005 server, attached it to the 2008 server, did some tests and then detached it to move it back to the 2005 server. The 2005 server would NOT let us attach the DB saying that the file version was above what it expected -- ie the files had been changed to the 2008 version, even though we were running in 2005 compatibility mode. It's a one way street...

Had to restore from backups, which were luckily rather fresh.
S. Kusen
S. Kusen
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4507 Visits: 1128
mflynn-549529 (12/7/2009)
One gotcha that got us was testing 2005 databases.

The test we wanted to do was see how a large database performed on the new 2008 machine. We detached it from the 2005 server, attached it to the 2008 server, did some tests and then detached it to move it back to the 2005 server. The 2005 server would NOT let us attach the DB saying that the file version was above what it expected -- ie the files had been changed to the 2008 version, even though we were running in 2005 compatibility mode. It's a one way street...

Had to restore from backups, which were luckily rather fresh.


This has been true since at least 2005, so be sure that you don't try doing this going from 2000 to 2005 either. Normally, my peers and I at my company do a backup and restore to 2008, then we effectively have two copies running. Obviously, you'd need enough storage to do that. We also don't like detaching and reattaching because they are the only files for the DB in that case, and we view that as too risky when simply testing out functionality.

Take care,
Steve
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search