Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SQL Server 2008 Upgrade Gotchas & Fixes Expand / Collapse
Author
Message
Posted Thursday, November 19, 2009 12:54 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:26 PM
Points: 5,961, Visits: 12,847
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"
Post #821903
Posted Thursday, November 19, 2009 12:58 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:02 PM
Points: 64, Visits: 360
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
Post #821907
Posted Thursday, November 19, 2009 8:29 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:28 PM
Points: 962, Visits: 940
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...
Post #822058
Posted Friday, November 20, 2009 4:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:26 PM
Points: 5,961, Visits: 12,847
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"
Post #822233
Posted Thursday, November 26, 2009 7:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 17, 2014 3:29 AM
Points: 37, Visits: 148
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.


Post #825214
Posted Friday, December 04, 2009 2:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 15, 2010 8:26 PM
Points: 2, 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.
Post #829270
Posted Friday, December 04, 2009 4:25 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:02 PM
Points: 64, Visits: 360
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!


Post #829307
Posted Monday, December 07, 2009 5:28 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:25 PM
Points: 5,849, Visits: 12,586
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


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

Post #829762
Posted Monday, December 07, 2009 11:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 12:33 PM
Points: 5, Visits: 42
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.
Post #830121
Posted Tuesday, December 08, 2009 9:41 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:28 PM
Points: 962, Visits: 940
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

Post #831155
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse