Sql server 2005 on sql server 2000

Question

I want to ask is there any issues or risks involved in installation of SQL Server 2005 Enterprise Edition on SQL Server 2000 Enterprise Edition in production server?

Please tell me the guidelines in installation...

Answer 1

2000 and 2005 can exist side-by-side, you just have to use a named instance for at least one of them (only one can be the default instance).

If you are talking about upgrading, here are some good starting points:

http://searchsqlserver.techtarget.com/generic/0,295582,sid87%5Fgci1269983,00.html

http://www.microsoft.com/sqlserver/2005/en/us/upgrading.aspx

Also get the 2005 upgrade advisor, which should highlight any potential issues in your specific environment:

http://www.microsoft.com/downloads/details.aspx?FamilyID=1470e86b-7e05-4322-a677-95ab44f12d75

Answer 2

If you're upgrading a SQL Server 2000 instance to Sql Server 2005 in place, I wouldn't recommend it. There are some differences in behavior that can cause applications to fail.

The one that I ran into had to do with default schemas and the SQL Server GUI. Before the 2005 version of SQL Server, I could use the GUI to create a database, create a user, make the user the owner of the database, and then the default schema for that database would be that user's username. In SQL Server 2005, I had to add the additional step of setting the default schema to that user's username. Without that, our tool for setting up new schemas failed.

Doing all of the setup in scripting worked the same as it had before, except that some of the names of the procedures had changed. That was much easier to track down than the default schema name issue, though.

In summary: major changes, so don't just do an upgrade-in-place.