February 28, 2009 at 7:37 pm
Comments posted to this topic are about the item Upgrade Strategies for SQL Server 2008
March 2, 2009 at 12:21 am
Nice article.
After a doing side by side upgrade the databases are moved to the new instance by dettach/attach or backup/restore. Will this change the compatibility level of the database being moved to sql 2008 instance.
"Keep Trying"
March 2, 2009 at 2:39 am
In my experiment from Microsoft SQL Server 2005 - 9.00.3042.00 to Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 upgrade of user database using backup/restore and attach/detach, it didn't change the compatibility level.
Here are few lines from MSDN:
Database Compatibility Level After Upgrade
The compatibility levels of the tempdb, model, msdb and Resource databases are set to 100 after upgrade. The master system database retains the compatibility level it had before upgrade, unless that level was less than 80. If the compatibility level of master was less than 80 before upgrade, it is set to 80 after upgrade.
If the compatibility level of a user database was 80 or 90 before upgrade, it remains the same after upgrade. If the compatibility level was 70 or less before upgrade, in the upgraded database, the compatibility level is set to 80, which is the lowest supported compatibility level in SQL Server 2008.
More details can be found here:
March 2, 2009 at 4:10 am
Thanks.
In a perfect world should'nt the compatibility level also change?
"Keep Trying"
March 27, 2009 at 7:26 am
Has anyone else problems with DB copy wizard? Source is MSSQL 2000, method is SQL Management objects. I need to keep the source DBs online, so detach-attach is out of question.
Microsoft (R) SQL Server Execute Package Utility Version 10.0.1600.22 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 13:44:08
Progress: 2009-03-27 13:44:09.16
Source: mssql03_PKSQL2008_Transfer Objects Task
Task just started the execution.: 0% complete
End Progress Error: 2009-03-27 13:44:35.81
Code: 0x00000000
Source: mssql03_PKSQL2008_Transfer Objects Task
Description: ERROR : errorCode=-1073548784 description=Executing the query "CREATE VIEW [dbo].[PoctyOS] AS SELECT dbo.Se..." failed with the following error: "Invalid object name 'dbo.Servers'.".
This happens for any database I've tried - always it reports some "invalid object name" or "invalid column name" and of course the "invalid" object is there.
April 29, 2009 at 4:29 pm
We are doing a test run on updating SQL 2005 Standard Edition to SQL 2008 Standard Edition.
We are doing this on a fresh install of the server and SQL 2005 and in the process, we renamed the computer after 2005 was installed.
We did run the sp_dropserver and sp_addserver sprocs with the old name and new names and restarted our default instance.
We have made it to the step entitled Reporting Services Authentication.
We only have a default instance of 2005 installed. The text box labeled "Instance of SQL Server that hosts the report server database" is not editable and looks like it should contain the machine name, however it contains the old server name. Is there a chance that this value is coming from the RSReportServer.config file. Does anyone know of a way to check the contents of this file and of the best way to change the contents to reflect the new machine name?
Thanks.
June 3, 2009 at 11:55 pm
I have a question about the upgrade from 2005 to 2008... should you script all of your logins from master and script all the jobs and SSIS packages from MSDB and apply to the new 2008 (side by side update) or can you detach and attach your current Master and MSDB from 2005 to 2008?
Thanks...
March 6, 2010 at 5:31 am
I faced a similar issue but it was because the dba's had turned off dbo schema access in 2008 for developers.
March 6, 2010 at 5:42 am
gaurav.bhagat (3/6/2010)
I faced a similar issue but it was because the dba's had turned off dbo schema access in 2008 for developers.
Seems sqlservercentral forums need database tuning. I was trying to edit my above post to link it to 2000-2008 conversion post but I kept getting the default error page from the forums application -- looks like timeouts. Hopefully this one posts!!!
March 6, 2010 at 9:54 am
You need to script out jobs/logins and run the script on the SQL Server 2008. I think simply detach and attach of these system databases (master/msdb) will not work becuase of some internal table/sp name changes between versions.
March 30, 2010 at 2:20 pm
Very good article. I have a few questions after reading your article where I'm not clear on for my "Side by Side Upgrade." I'm going to perform the following "check list" of steps from a SQL 2000 SP4 to SQL 2008 in a side by side upgrade:
1. Install SQL 2008 on my new server.
2. Run the SSUA on the new server to find all the issues with the databases on the SQL 2000 server.
3. Clean up any issues reported by the SSUA.
4. Ensure database consistency by running DBCC CHECKDB on all the system and user databases on
the SQL 2000 server.
5. Backup the SQL 2000 databases including the system databases.
6. Restore all the databases including the system databases on the SQL 2008 server.
Question: System databases first then user databases?
Now this is where a big question came to mind when I was reading your "During upgrade" section. This section seems to imply that we are running Setup.exe from the DVD. Is this for an "In-place Upgrade? I got confused reading this section as it relates to my "Side by Side Upgrade" approach. As it is written it seems to imply that I installing SQL 2008 at this point.
7. Revisit SSUA recommendations
8. Update statistices
9. Run DBCC UPDATEUSAGE on all databases, including system databases.
I'm sure I'll find a few "fun" items along the way. Thank you in advance for answering my questions, and helping me get it all clear.
May 16, 2011 at 5:48 pm
Hi...
I have a SQL Server Cluster with 2 nodes configuration in Active / Active mode.
What is the current procedure to made the upgrade process???
I have Default instance in node A and de named Instance in the node B.
I want to upgrade only named instance, i have to failover named instance from node B to node A and run the setup on node B?
May 20, 2011 at 6:19 am
Good job Arshad. Really liked the article. Keep up the good work.
May 20, 2011 at 10:32 am
:w00t::hehe:
Nice article. One question though, I upgraded from 2000 to 2005 and now we are ready for 2008.
In the 2000 to 2005 I did rebuild the indexes after the upgrade was completed. Do you recommend to do the same from 2005 to 2008?
:w00t::hehe:
May 20, 2011 at 6:45 pm
You missed an upgrade method particularly applicable to 2000 to 2008 (R2) conversions:
Machine swap.
You have Server, with SQL2000/5.
You buy/scrounge/spin up a VM NewServer.
You install SQL2008(R2) on NewServer.
You write down the IP's of each, then swap their IP's, swap the names, rejoin the domain if applicable, and change the SQL Server name.
Wait N days, then get rid of Server (old).
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply