Upgrade from 2000 to 2005

[micha123]

Assuming that all the databases and applications were successfully checked by the Upgrade Advisor, what method would you choose to upgrade SQL SERVER 2000 Instance on cluster with 110 databases to SQL 2005:

1) Install 2005 on a new cluster machine, transfer logins, upgrade and test DB by DB and than transfer jobs, DTS (if possible), etc

2) Install SQL Server 2000 on the new cluster machine, copy all databases (including system databases) and than upgrade everything in-place

Of course, assuming also that all the 2005 installation pre-requisites are taken care of before the installations...

?

Thanks.

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[Catadmin]

We tested all our databases & jobs on a Sandbox server for a couple of months before we moved everything up to 2k5. I do NOT recommend doing an inplace upgrade because some views & jobs (especially those that use ActiveX scripts) can and do break if you're not prepared for them. Also, testing before you move gives you a comfort level an inplace upgrade just can't accomplish. And there's the whole establishing the shares, making sure the DTS packages will successfully make the move to SSIS, etc etc etc.

However, in the end, we installed 2k5 on brand new servers, detached our DBs, copied (not cut-n-paste) the data & log files over, and reattached them to 2k5 which auto-upgraded the DBs.

Does that help you out?


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[micha123]

But if I make sure that there are no more DTS packages with ActiveX scripts (I will develop new packages using SSIS prior to the upgrade) and I will repair the problematic issues while testing the databases after the upgrade(i.e: the problematic views), do you still think that side-by-side upgrade is better than in-place upgrade on a copied version on the new server?

Thanks.

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[Catadmin]

Yes, I still think a side-by-side upgrade is better, but this my personal preference.

Remember to get rid of all the Dynamic Properties tasks in your DTS packages also (if you're upgrading to SSIS). DP isn't supported in Integration Services. Or, if you're going to run as DTS, make sure you download the tool from MS that allows running of legacy packages.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

A few months ago we did a major upgrade from SQL 2000 to SQL 2005. We did a side by side upgrade moving from a single machine to a cluster. In addition to running the upgrade advisor you also need to go through a code review to check that all your code meets the more strict ANSI standards that SQL 2005 requires.

Our upgrade schedule was very agressive. We started the project in Janunary and did the production deployment at the end of April. The time in between was spent doing a full code review and full regression testing.

Because of all the testing that we did in both development and QA the production rollout was a complete and total success.

There are other little things that can trip you up when you upgrade. If you use the nolock hint the with keyword is required now, where it used to be optional. If you have a print statement above a select statement within a stored procedure and you connect and run the procedure via ADO (or weblogic, and probably other connection methods) you'll get an error saying that the column you are looking for isn't found. This is because SQL 2005 is returning an empty record set along with the print statement where SQL 2000 didn't return the record set.

Issues like this require a full test plan before jumping in and upgrading the production systems.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[micha123]

Thank you guys for the tips!

Denny, did you also see performance problems during the testing process?

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[mrdenny]

Nope. We saw a performance improvment moving from SQL 2000 to SQL 2005.

In our performance testing we saw a ~30% improvment with OLTP commands when moving from SQL 2000 to SQL 2005.

To test we took like machines (HP DL580s with 4 procs, 4 Gigs of RAM, identicle disks, etc) and installed SQL 2000 on one, and SQL 2005 on another. We then ran some code that I wrote to test how fast SQL can process selects, inserts and deletes. The SQL 2005 server was about 30% faster. In order to exclude the disks as a possible slow point, everything was done in memory via a table variable.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

Denny,

FYI. We tested the NOLOCK issue during our upgrade and didn't have a problem using it without the WITH statement. In fact, one of my managers yelled at me for telling him WITH had to be used for all the hints, including NOLOCK, without "checking the facts" first.

Nevermind the fact that Microsoft has been saying "use WITH on all hints" since they rolled out SQL 2005... @sigh.

Oh, another caveat, micha123, most of the SQL 2k5 items require Visual Basic .Net (SSIS & Reporting Services for instance) to program in, not VB Script, and there are little items that require C# symbols (like using == for equals instead of =). Not to mention XP_CmdShell is off by default and so is Remote connecting to the server. So, as MRDenny said, you'll want to check EVERYTHING before you move it.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[avanderlaan]

Jeez. You folks are so cautious. I came in on a Monday morning last May and discovered that corporate IT had upgraded the production SQL2000 server in place without running the advisor or providing previous notice to the dbowner (me). Amazingly we only lost permissions to 5 stored procedures (out of 200+).

 

[mrdenny]

When you are upgrading production systems that process billions of dollars in loan applications per year, you have to be a little causios. A 1 day window to rebuild the server can cost the company tens of thousands to hundreds of thousands of dollars.

Cat,
We showed the same thing during our testing. However Microsoft says to add it so we did. Each version of SQL Server appears to be moving closer to being fully ANSI compliant, and WITH on your index hints is one of the rules.

Tell your manager that ANSI standard codeing is the recommended option as that will make it easier to upgrade the future versions in the future. And that I told him to shove it for b*tching that you are being extra carefull with the largest most complex upgrade since the upgrade from SQL 6.5 (16bit) to SQL 7 (32bit).

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

Ditto on the losing money issue. Corporate is very very picky about unscheduled downtime and people tend to lose their jobs if the downtime was preventable by doing it correctly. As Albert Einstein once said, "If you don't have time to "Do It Right" the first time, what makes you think you'll have time to fix it?".

Thanks for the backup, Denny. I appreciate that it's not just the little DBA shop I work in that believes in making sure the developers code things correctly for the db. @=) I also didn't know if you knew MS had "missed" that particular hint when they rebuilt.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[avanderlaan]

Cautious about changes? Nah. Mind you these IT folks wield the sword of Sarbanes-Oxley to restrict all access to the production servers to IT personnel. As the dbowner I have to request an IT person to make changes to my production database.