Solved

in place upgrade  from SQL server 2005 with SP4 to SQL server 2008 R2

Posted on 2014-09-26
24
333 Views
Last Modified: 2014-10-06
Dear all,

any one tried to do in place upgrade from SQL server 2005 sp4 to SQL server 2008 R2 , how easy is it?

just click the "ugprade from previous edition" button on the SQL server 2008 installatoin wizard and it will just run without any problem ?
0
Comment
Question by:marrowyung
  • 14
  • 9
24 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40345889
That's true but be careful because with the deprecated features. I recommend you to run the SQL Server 2008R2 Upgrade Advisor before you start with the upgrade.
And of course, backup everything before.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40346133
so you have tried that before? where to download the SQL Server 2008R2 Upgrade Advisor?

also it seems that SQL server 2008 with SP2 can run on Windows 2003/2003 R2 X64 server?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40346177
No. Never did an in place upgrade but was an option that we turned it down because we also wanted to migrate to new machines so we opted to a side-by-side upgrade.
But even in a side-by-side upgrade it's recommended to run the upgrade advisor. You don't need to download it. You'll find it in SQL Server 2008R2 package (should be in redist folder, then you'll see the Upgrade Advisor folder and then double-click SQLUA.msi).
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40348280
"No. Never did an in place upgrade but was an option that we turned it down because we also wanted to migrate to new machines so we opted to a side-by-side upgrade."

that's what I did 1 year ago as new hardware always be an option when many years later, everything getting old, new hardare + new OS + new SQL server will come, then everyone would like that approach !

"But even in a side-by-side upgrade it's recommended to run the upgrade advisor. You don't need to download it. You'll find it in SQL Server 2008R2 package (should be in redist folder, then you'll see the Upgrade Advisor folder and then double-click SQLUA.msi). "

I think they will run it as part of the installation wizard on screen option, what I found out is it require us to install .net framework 4.5 but I hasn't let it start yet.

let wait for more option.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40349281
SQL server 2008 R2 SP3 just released, download it and have a try !
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40351511
administrator, can you help to bring more SQL server in place uggrade professional in and help me ?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40352736
How many confirmations do you require and more importantly how long is this going to go on for?  You have already got a valid answer, what else do you need?  Has it occurred to you that there has been no more contributions, because perhaps (just perhaps) there is nothing further to add.  Or perhaps they have noticed that you still have over 20 open questions.

But to answer your question, yes it is easy to do an in-place upgrade and any reputable DBA could do it in his/her sleep, provided they take precautions as suggested previously, including using the Upgrade Advisor and backing up all the databases.

However, there are some that do not recommend doing an in-place upgrade, for example Michael has a good blog on the subject Reasons to Avoid SQL Server In-Place Upgrades  But that was not your question ...
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40358927
you know the resuit from SQL upgrade advisor and the SQL instlalaetion wizard's check is totally different, this make my very uncomfortable !

the item prompt out from SQL upgrade advisor is hard to understand.

BTW, waiting for response doesn't hurt.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40358933
the resuit from SQL upgrade advisor and the SQL instlalaetion wizard's check is totally different
Should be different since they has different purposes and doing different things.
Upgrade Advisor only check the database and tells you if there's any issue if you migrate it to a more recent SQL Server version.
Installation Wizard checks the server where you are going to install the SQL Server for issues and will tell you if any prerequisite is missing or not.

If you are uncomfortable with this task then I recommend you to build a lab where you can make all tests that you need until you feel comfortable to advance with the upgrade.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40358945
"Should be different since they has different purposes and doing different things."

that one complain some but the upgrade wizard tells me that everthing was passed.

"If you are uncomfortable with this task then I recommend you to build a lab where you can make all tests that you need until you feel comfortable to advance with the upgrade. "

yeah, but we do not have any more test bed here and I can only start to upgrade on the server with very less amount of database there.

so right now, one is good and one has issue, so we have to trust the bad one ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40359031
so right now, one is good and one has issue
What's the issue?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40359058
basically now I am care more about the whole process of upgrading that, if the upgrade was completed, then I can leave the issue to the developer.

message appear after I run the upgrade advisor:
1) SQL server 2000 Meta Data Service Package are not supported.
2) SQL server 2000 DTS Designer components are required to edit DTS package.
3) no DTS package were found on the server but you selected to analyze.
4) SQL server 2000 data transformation service is deprecated.
5) upgrade advisor reports only on the most recent version of each DTS package.

here attached the result of analysis, we do not use SSIS usally but I can let the SSIS here and untouch it.

however analysis of the SSIS upgrade is passed.

from your point of view, what should I do ?
server-upgrade-test.csv
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:marrowyung
ID: 40359075
only test the upgrade of SSIS seems good but some SSIS error goes to the SQL server analysis side!
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40359077
3) no DTS package were found on the server but you selected to analyze.
If you don't have any DTS package in the SQL Server 2000 instance then you can ignore all those messages. They are informational only.

Migrating DTS to SSIS should be a developer task and not for a DBA. Anyway Microsoft provides a DTS tools for running DTS in SQL Server 2005 and 2008.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40359099
"If you don't have any DTS package in the SQL Server 2000 instance then you can ignore all those messages. They are informational only."

we don't have SQL 2000 anyway, why report this ! we only ahve the SQL 2005 at that box.

I will let developer found out that and if ... then I help them. they offer no information anyway.

on the other box which has a lot of database and full text search engine and analysis engine, we have this report, see attached. again, funny that it has red and yellow sign, so not just warning. the problme only focus on SQL server itself but not SSIS and SSAS.
server-upgrade-test.csv
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40359101
This is the photo:


other software upgrade result
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40359187
we don't have SQL 2000 anyway, why report this
It's because you chose the DTS components to be reported.

In the Upgrade Advisor report when you click in the '+' icon it says where the issue it's being used? If not, you don't need to bother with it.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40359406
"It's because you chose the DTS components to be reported."

But as long as I choose to check SSIS, this will prompt, right? we have this install but we don't use it .

"In the Upgrade Advisor report when you click in the '+' icon it says where the issue it's being used? If not, you don't need to bother with it. "

see attached.
detail-item.jpg
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40359434
If you don't use SSIS don't bother with those messages.
For the rest, Upgrade Advisor made all the work for you. You have the objects identified and presented to you solutions to how resolve those issues. You just need to follow what it says there.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40359607
then it is bad ! so many message!
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40359648
No one told you it was to be an easy job :)

What those messages says is that database are using deprecated features. That's why it's always good to read what's going to be deprecated every time that a new version is released so we need to be prepared for a future migration.

The Upgrade Advisor it's a good tool for identify those stuffs for us but it can do nothing about it. Now the hard work begins.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40361172
"No one told you it was to be an easy job :)"

yeah I knew, never do this before as I only do so much server to server migration job. not any in place upgrade before but keeping the user id there, in place upgrade is good !

"The Upgrade Advisor it's a good tool for identify those stuffs for us but it can do nothing about it. Now the hard work begins. "

let see ! I will find out DB will very less number of database and see what is the process.

"What those messages says is that database are using deprecated features. "

so this seems mean to me that we don't have to think about that at all IF Application side take into consideration already ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40363149
so this seems mean to me that we don't have to think about that at all IF Application side take into consideration already ?
I don't know what are your tasks there but this should me something to be talked between DBA's and development team.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40363192
I really has to try myself !
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now