July 8, 2013 at 1:07 pm
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.
July 8, 2013 at 1:37 pm
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
July 8, 2013 at 2:15 pm
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
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- 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
July 9, 2013 at 4:49 am
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 10, 2013 at 9:49 am
If you haven't already run it, upgrade advisor will be useful:
http://msdn.microsoft.com/en-us/library/ms144256(v=sql.100).aspx
July 10, 2013 at 12:15 pm
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.
July 10, 2013 at 1:10 pm
+1 more for the Windows upgrade. Go to W2K8 R2 or, preferably, W2K12.
Also, +1 for Johan's note. Backup restore for upgrades.
July 11, 2013 at 8:42 am
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...
July 11, 2013 at 9:20 am
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.
July 11, 2013 at 5:38 pm
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
July 12, 2013 at 9:45 am
This is a poorly written information request. I realized that with some of the responses. I did specify a side-by-side scenario initially but botched from there. Even in my last response - did it again. I do try to be careful however this came up way too short. My apologies to everyone.
Very simply - I want to move SQL Server 2000 databases to a SQL Server 2008 (side by side).
Not only consolidate the servers to one but upgrade the environment. Some of the SQL 2000
instances are on Windows 2000 - there's no support for the OS.
Copy SQL Server 2000 backup to the SQL Server 2008 instance. That'll do it.
My apologies to everyone again.
July 12, 2013 at 12:02 pm
no need to apologize and glad you got it figured out.
you've got the right solution. Move backups, restore.
Also, script out logins (sp_helprevlogin) and move those. Watch out for linked servers, replication, packages, things that might need to move from the instance level.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply