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 12»»

Upgrade SQL Server 2000 to SQL Server 2008 Expand / Collapse
Author
Message
Posted Monday, July 8, 2013 1:07 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 11:39 AM
Points: 62, Visits: 570
I'm looking to upgrade 4-5 SQL Server 2000 database instances to 2008. Most are SP3 but one is RTM. Upgrade destination is SQL Server 2008 Enterprise edition (side by side upgrade). Three questions:

1. Is it necessary to upgrade SQL Server 2000 to SP4?
2. The supposed OS requirement before upgrading is Windows Server 2003, SP2. The upgrade candidates have Windows 2003 SP1 and Windows 2000 SP4. Is this a concern?
3. I want to copy the .bak file to the test SQL 2008 instance, then restore. Any issues or concerns here?

Any comments would be appreciated - thank you.
Post #1471332
Posted Monday, July 8, 2013 1:37 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 4,238, Visits: 3,258
1. I've never done an upgrade from SQL 2000 to 2008, so I'm going to leave that one alone.

2. I looked up the requirements for SQL 2008 and you're right - it does say Windows 2003 SP2 or above. I would definitely listen to this. Microsoft probably knows things about their operating system that we don't, so I would be safe rather than being sorry later.

3. As for backing up a database, moving it to another server and restoring it there - that's exactly what I would do. In fact, that's what I do when I have to copy databases from production to test. I think you'll find that this is a preferred method of copying databases from one server to another.

For the exact requirements: http://msdn.microsoft.com/en-us/library/ms143506%28v=SQL.100%29.aspx



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1471341
Posted Monday, July 8, 2013 2:15 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:43 AM
Points: 7,005, Visits: 8,453
let me answer Q1 for you : Yes

ref: Version and Edition Upgrades http://msdn.microsoft.com/en-us/library/ms143393%28v=sql.105%29.aspx

Q3 We prefer upgrading using the migration path (backup/restore).
With inplace upgrades, the instance is configured starting from the old version configuration; That may leave e.g. security holes opened which would have been closed with a regular install. In case of DRP you would also need to start recovery from the old version, upgrading once more to be sure all settings are in place. People have been bitten by this quiet often and only regret it at the moment they really could mis it.


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1471358
Posted Tuesday, July 9, 2013 4:49 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:10 AM
Points: 3,634, Visits: 5,283
jralston88 (7/8/2013)

2. The supposed OS requirement before upgrading is Windows Server 2003, SP2. The upgrade candidates have Windows 2003 SP1 and Windows 2000 SP4. Is this a concern?


Why aren't you considering Win 2008 or 2012?

Win 2003 is almost out of extended support at this time. We're in the process of trying to get systems off of it not migrating to it.

Edit: I would also like to suggest that when you upgrade the db you go full on and go with compatibility level 100 instead of keeping it at 80 because you think it will simplify the upgrade. I think you'll find very few incompatibilities at 100.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1471533
Posted Wednesday, July 10, 2013 9:49 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:41 PM
Points: 1,948, Visits: 3,221
If you haven't already run it, upgrade advisor will be useful:

http://msdn.microsoft.com/en-us/library/ms144256(v=sql.100).aspx
Post #1472221
Posted Wednesday, July 10, 2013 12:15 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 4,238, Visits: 3,258
Dwain.c raises a very good point. Granted, I never implement something in production until it's been out for a while, but Win Server 2003 is pretty close to end-of-life, which means no more support. I'd definitely consider a newer OS.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1472290
Posted Wednesday, July 10, 2013 1:10 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:57 PM
Points: 33,206, Visits: 15,361
+1 more for the Windows upgrade. Go to W2K8 R2 or, preferably, W2K12.

Also, +1 for Johan's note. Backup restore for upgrades.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1472309
Posted Thursday, July 11, 2013 8:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 11:39 AM
Points: 62, Visits: 570
I may have confused some of you:
I want to upgrade SQL Server 2000 instances. Their OS is Windows Server 2003 SP1. The instances have no SQL Server service pack or SP3.

Microsoft does not support anything less than SQL Server SP4 when upgrading to SQL Server 2008.
It was also my understanding that the OS on the SQL Server 2000 instances must be Windows Server 2003 SP2 before attempting to upgrade to SQL Server 2008. Is this correct?

Once this is complete, I want to copy their .bak files to the new SQL Server 2008 instance. That instance has Windows Server 2008 SP2.

Before copying the files over I will use the upgrade advisor. That's it. Sorry for the confusion...
Post #1472620
Posted Thursday, July 11, 2013 9:20 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:57 PM
Points: 33,206, Visits: 15,361
When you describe your situation, you're not always being clear and using pronouns which are somewhat ambiguous. We also have no idea if you are talking about in place or moving to new machines. Be careful of that.

You do need SP4 on SQL Server 2000 for an upgrade.

According to this, http://msdn.microsoft.com/en-us/library/ms143506%28v=sql.100%29.aspx, you need SP2 for Windows 2003.

However, if you have a new SQL Server 2008 instance, and are moving the files to that instance, why upgrade? You can restore a SQL Server 2000 (SP4) backup on a SQL Server 2008 instance.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1472646
Posted Thursday, July 11, 2013 5:38 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 5:57 AM
Points: 2,608, Visits: 1,545
Re your question 1.

You mention you wanted to do a side-by-side upgrade. So you don't need SQL 2000 to be on SP4 as a pre-requisite. You can perform a direct restore of a database backup on SQL 2000 RTM up to SQL 2008 R2 SP2. Upgrade Advisor SQL 2008 R2 will also work againsts a SQL 2000 RTM.


ref: Version and Edition Upgrades http://msdn.microsoft.com/en-us/library/ms143393%28v=sql.105%29.aspx

I reckon this link refers to an in-place upgrade. That is if you want to run SQL 2008 Setup against an existing SQL 2000 to upgrade the same SQL instance to SQL 2008, then it'll not work unless you're on SQL 2000 SP4 to begin with.

What's confusing is you mention you wanted to do a side-by-side upgrade (backup\restore SQL 2000 databases to another instance of SQL 2008), then you indicate you wanted to do an in-place upgrade (upgrade SQL Server 2000 instances). There are major differences between a db upgrade vs SQL instance upgrade

Side-by-side upgrade: i.e. you backup SQL 2000 db and restore it on SQL 2008 R2 somewhere else
In-place upgrade: i.e. you run SQL 2008 setup against an existing SQL 2000 instance. That SQL 2000 instance will be upgraded to SQL 2008 along with all the databases hosted. Read the mininum hardware and software requirement as per the link posted by Steve. SQL 2008 setup will not run successfully if you don't satify the minimum requirement whether its an in-place or a new install

One of the key step when the databases are on SQL 2008 is to run DBCC CHECKDB WITH DATA_PURITY. Database corruption on invalid values are not checked in SQL 2000 CHECKDB, and there's *no* CHECKDB option to check them on SQL 2000 (exception with a trace flag).

Hope this clarifies both scenario whether you wanted to perform a side-by-side or an in-place upgrade.


Simon Liew
Microsoft Certified Master: SQL Server 2008
Post #1472827
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse