InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 19:06:40 GMT20http://www.sqlservercentral.com/Forums/Topic819840-1398-1.aspxThere is some good information here but corrections/clarifications that need to be made - [b]Item #1) Reporting Services is NOT a requirement to run the SQL Server Upgrade Advisor![/b]a. The location where you install SQL Server Upgrade Advisor depends on what you will be analyzing. Upgrade Advisor supports remote analysis of all supported components except Reporting Services. If you are scanning instances of Reporting Services, you must install Upgrade Advisor on the report server.b. If you are not scanning instances of Reporting Services, you can install Upgrade Advisor on any computer that can connect to your instance of SQL Server, and that meets the Upgrade Advisor prerequisites. Additional information - http://msdn.microsoft.com/en-us/library/ms144256.aspx[b]Item #3) Visual Studio 2008 - SP1 required for any v3.5 .NET components - NOT TRUE![/b]Visual Studio Sp1 is only required if you have Visual Studio installed and you plan on using Visual Studio after you upgrade to SQL Server 2008. If you don't plan on using it, then, technically, you don't have to upgrade it. If you have not installed Visual Studio 2008 RTM but are getting the error that VS SP1 is required, it is because Visual Studio 2008 Shell (integrated mode) is installed on your system but not at the SP1 level. This is probably because it was left over from a SQL Server 2008 CTP installation.The binaries needed from VS 2008 SP1 are not related to the .Net Framework 3.5 SP1 update. SQL Server 2008 does require the .Net Framework 3.5 SP1 update but for different reasons. SQL Server 2008 setup will install the released version of .Net Framework 3.5 SP1 if it detects it is not already installed. This is one of the first things SQL Server 2008 setup does when executed. Even if you have the .Net Framework 3.5 SP1 Beta, SQL Server 2008 will update it to the RTM version of .Net Framework 3.5 SP1.See this article for more information - http://blogs.msdn.com/joaol/archive/2008/08/29/sql-server-2008-visual-studio-2008-sp1-and-net-framework-3-5-sp1-explained.aspx[b]Finally, to answer the post by SSCrazy[/b] - yes, the SQL Server 2008 workstation tools are backwards compatible with SQL Server 2000 & 2005... of course, with limited functionality depending on the versions and features supported on the version you're connecting to.http://msdn.microsoft.com/en-us/library/ms174190.aspWed, 07 Apr 2010 21:56:40 GMTdanarivkhttp://www.sqlservercentral.com/Forums/Topic819840-1398-1.aspxThe SQL Server 2008 Upgrade advisor is designed to work with SQL Server 2005 Reporting Services.For more information look here:http://msdn.microsoft.com/en-us/library/bb677622.aspx Cheers!Brandon_Forest@sbcglobal.netThu, 25 Mar 2010 16:18:12 GMTBrandon Foresthttp://www.sqlservercentral.com/Forums/Topic819840-1398-1.aspx1[quote]) Reporting Services (RS) is required for the Upgrade AdvisorThis is a logical gotcha that kinda makes me mad. Microsoft Best Practices and our corporate security guidelines state that you should not put Reporting Services on the same server as an Operational Data Store (ODS). The crux of the matter is that the SQL Upgrade Advisor (SUA) has to be on the server that it is scanning. It makes much more sense that the SUA be installed on a separate RS server and pointed towards the target server with a connection object. I went ahead and installed RS on the first dev server and then ran the SUA scan.The scan revealed nothing I didn't already know. Secondly, the SQL upgrade program does a similar scan, which will reveal any show stoppers. I made a judgment call and decided not to run SUA on servers that don't already have RS on them.[/quote]Do we need to install SQL Server 2005 Reporting Services OR SQL Server 2008 Reporting Services on where we run SQL Server 2008 upgrade adviser?thanksThu, 25 Mar 2010 12:13:28 GMTpshashiphttp://www.sqlservercentral.com/Forums/Topic819840-1398-1.aspx[quote][b]mflynn-549529 (12/7/2009)[/b][hr]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.[/quote]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,SteveTue, 08 Dec 2009 21:41:45 GMTS. Kusenhttp://www.sqlservercentral.com/Forums/Topic819840-1398-1.aspxOne 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.Mon, 07 Dec 2009 11:30:05 GMTmflynn-549529http://www.sqlservercentral.com/Forums/Topic819840-1398-1.aspxMS 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![url]http://msdn.microsoft.com/en-us/library/ms189493.aspx[/url]Mon, 07 Dec 2009 05:28:00 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic819840-1398-1.aspxYep, I figured that out the hard way. I had a stored procedure called usp_ShrinkAllLogs, which used the BACKUP LOG &lt;LogName&gt; With Truncate_Only and DBCC SHRINKDATASE(&lt;databasename&gt;, 10). I changed it to instead to set the recovery model to simple, then use the DBCC SHRINKDATASE(&lt;databasename&gt;, 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!:-DFri, 04 Dec 2009 16:25:39 GMTBrandon Foresthttp://www.sqlservercentral.com/Forums/Topic819840-1398-1.aspxAnother 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 filebackup tran &lt;dbname&gt; with no_log--To Shrink the T-log container:dbcc shrinkfile(&lt;dbname&gt;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.Fri, 04 Dec 2009 14:18:39 GMTcfergus 84887http://www.sqlservercentral.com/Forums/Topic819840-1398-1.aspxI'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.Thu, 26 Nov 2009 07:04:35 GMTreuben.andersonhttp://www.sqlservercentral.com/Forums/Topic819840-1398-1.aspx[quote][b]S.K. (11/19/2009)[/b][hr]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. [/quote]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.Fri, 20 Nov 2009 04:04:38 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic819840-1398-1.aspxFor 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...Thu, 19 Nov 2009 20:29:26 GMTS. Kusenhttp://www.sqlservercentral.com/Forums/Topic819840-1398-1.aspxI 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.netThu, 19 Nov 2009 12:58:53 GMTBrandon Foresthttp://www.sqlservercentral.com/Forums/Topic819840-1398-1.aspxNever 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 ;-)Thu, 19 Nov 2009 12:54:32 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic819840-1398-1.aspx[quote][b]SQL Noob (11/18/2009)[/b][hr]i run SQL 2008 management tools on my PC's and 2005 in production. the client tools work great and offer more functionality than the 2005 Management studio. only thing is SSIS won't work. packages created in 2008 won't work on 2005 servers[/quote]thanks!Wed, 18 Nov 2009 07:41:33 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic819840-1398-1.aspxi run SQL 2008 management tools on my PC's and 2005 in production. the client tools work great and offer more functionality than the 2005 Management studio. only thing is SSIS won't work. packages created in 2008 won't work on 2005 serversWed, 18 Nov 2009 07:31:22 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic819840-1398-1.aspxrequiring RS for SUA seems a right royal pain. You have to ask yourself why.could 2008 clients have been rolled out before the server upgrades? Does 2008 client tools support earlier versions as 2005 does 2000?Wed, 18 Nov 2009 07:28:23 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic819840-1398-1.aspxIf you have VS already installed, you'll need SP1 to proceed. No need to install VS 2008 just for the SQL install.Hope that helps.Great little article, and the gotcha about the filestream fix should get added to the main article. I found that to be problematic.If you are using a slipstream install, I would also make sure to do the 1) installer 4.5, 2) .net 3.5 sp1, and 3)filestream fix, then a reboot, then do the SQL install.Take care,SteveTue, 17 Nov 2009 13:00:36 GMTS. Kusenhttp://www.sqlservercentral.com/Forums/Topic819840-1398-1.aspxBIDS is VS2008. CRM & MOSS2007 have the updated .NET framework.Tue, 17 Nov 2009 11:25:49 GMTBrandon Foresthttp://www.sqlservercentral.com/Forums/Topic819840-1398-1.aspxIf the DB server doesn't have Visual Studio installed, but [i]does[/i] have software that uses .NET 3.5, is it really necessary to install VS2008 SP1? Isn't there a .NET framework patch or something that will do the job?Seems like a lot to install VS just to upgrade SQL Server.Tue, 17 Nov 2009 09:37:08 GMTMatt Guthriehttp://www.sqlservercentral.com/Forums/Topic819840-1398-1.aspxwe might go to R2 next year, but will probably wait until 2011. for the 2005 upgrade we waited until SP2 was released and PSS was still swamped with support calls and the quality of the support was so so.i priced out some new hardware yesterday and it's amazing how much power you can get. but the 32nm CPU's come out next year and we'll probably wait for that as wellTue, 17 Nov 2009 07:01:48 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic819840-1398-1.aspxI recently upgraded a SQL Server 2005 Express installation to 2008 Express (with advanced).My gotchas were:1)Permissions on the new reporting services installation - much fiddling required to allow anonymous users to render reports without being prompted for username/password2)Reporting services on port 8080 - I had to make changes to the web app to allow a port to be specified in report URL's, I also had to get port 8080 opened on the firewall, because the server in question was locked down to only allow traffic on port 80.Tue, 17 Nov 2009 03:35:41 GMTgarethfhttp://www.sqlservercentral.com/Forums/Topic819840-1398-1.aspxAnother gotcha you need to add the hotfix for filestream regardless of whether you select to use it (Windows 2003) [url]http://support.microsoft.com/kb/937444/[/url].Also on a new install of a 2008 cluster no matter what you do the default collation installed willl be SQL_Latin1_General_CI_AS so to change it you need to rebuild the Master database, its OK if you upgrade from 2005 the collation won't be altered, so you won't have this issue.Tue, 17 Nov 2009 02:33:37 GMTCarolyn Richardsonhttp://www.sqlservercentral.com/Forums/Topic819840-1398-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/SQL+Server+2008/67066/">SQL Server 2008 Upgrade Gotchas &amp; Fixes</A>[/B]Tue, 17 Nov 2009 00:12:48 GMTBrandon Forest