May 11, 2006 at 8:45 am
I'm planning a migration from SQL Server 2000 to SQL Server 2005. To do this migration I have the contraint that I can't buy new machines. So I'm trying to determine what options I have. First let me set the stage a little. For a given SQL Server production machine I may have from 10-50 databases/application running. So I'm looking at the following options. Are there others? What are the risks of each of these. If you were me which way would you go, consider the constraint above.
Option 1: Upgrade In Place - This option would upgrade all database to from SQL Server 2000 to SQL server 2005 at the same time by SQL Server machine.
Option 2: Run a named instance of SQL Server 2005 - This option would install a named instance of SQL Server 2005 on the same machine that is running the default instance of SQL Server 2000. Databases would be migrated one at a time, or a handful at a time, until all database have been migrated. Once all databases have been migrated, SQL Server 2000 would be shutdown.
What are your opinions of how successful would each of these options be? What are this risks of each option? Does running two instanances of different versions of SQL Server cause any issue?
Gregory A. Larsen, MVP
May 11, 2006 at 2:06 pm
Greg,
Running a named instance is a bigger problem than running 2 versions:
1. Named Instances don't support all features
2. Each service has to run on its own Port Or IP Address. You can not have 2 services listening on the same IP and Port. So if one will use 1433 another will have to use another port. Network Admins and Web Developers don't like when you change ports and IP addresses
3. Named Instances are accesses by MachineName\Instancename, not all applications support that. Data Sources and Connection Strings have to be reconfigured too
Regards,Yelena Varsha
May 12, 2006 at 6:22 am
I'm thinking of doing the same thing. I have a question about your response, Yelena.
re. 1. What features do named instances not support? I can't think of anything from SQL Server 2000 not supported. This would kind of throw a wrench in clustering...
May 12, 2006 at 8:24 am
For our migration we have the same issue. Our plan is to (at high level)
1) Detach all production databases and backup master,model,msdb
2) Migrate Sql Server 2005. If the install goes as planned and the server is operating properly then step 3
3) Reattach each database to the server.
If the initial migration failes you can uninstall and reinstall SQL 2000.
The only issue I found with this strategy is you invalidate Logins if their default is not set to master before the detach, so before the migration, script the current default logins, set them to master then reset them after all databases have been reattached.
Good luck!!
May 12, 2006 at 12:30 pm
To Chris:
I can only refer to SDK documentation in regards what is not supported for named instances in 2000:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_cs_9i5u.asp
Working with Multiple Instances
The isql utility does not support named instances.
I remember there was sort of a table, I will add notes when I find it.
Regards,Yelena Varsha
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply