none
Upgrade sql2000 to (sql2005 or sql2008) RRS feed

  • 問題

  • Hi

    The company I am working in is considering to upgrade the SQL Server from SQL 2000 to SQL 2005 or SQL 2008. I am not sure if there a lot of things that I need to change. In terms of datatype, UDF, Stored procedure ...etc.

    Would it be better to upgrade to 2008 directly or it would be easier to upgrade to 2005 then 2008?

    Could you give me some advice please?

    Many thanks

    Chi
    2008年5月7日 上午 08:46

解答

  • Maybe I need to ask you: why you are upgrading from SQL Server 2000 to newer version?  Of course, in Microsoft point of view, upgrading to new version is always recommended.  However, in end user benefit point of view, you need to take a balance between supportability and change involvement.

     

    For example, if you are not upgrading, SQL Server 2000 will cause supportability issue as time goes by.  If you upgrade, you need to perform careful audit to make sure existing line-of-business running without problem.

     

    Personally I am recommending to upgrade to SQL Server 2008 other than 2005, since as time goes by, SQL Server 2005 will carry the same supportability problem: newer application will definitely having longer life time.  If you don't want to perform the same complex audit after 2-3 years, you better choose SQL Server 2008.  (Of course, if you are not planning ahead for 2-3 years, that point becomes meaningless)

     

    To perform an upgrade, the safest way is to audit existing application, and all view/stored procedures and functions that is called by existing client application.  What you need to consider is whether there is any feature you are using becoming obselete in SQL2008.

     

    To work bottom up, you may scan through the command dependency from design document of client application/middle-ware developer.  If those information is not documented, you can still scan through the source code, or use SQL Server Profiler to capture the command being use in worse case.  However, in most of the case, most of the SQL language being used commonly will not be obselete.

     

    To work top down, you can set up an isolated environment with SQL2000 deployed and upgrade to SQL2008 , with production data, application and client populated to this environment to perform QA test.  Of course, you need to define a test script to make sure everything is effectively tested out.  If the client applcations behave correctly, you can safe to upgrade the productoin environment (with test plan, and fallback plan).  Else, you may need to modify the client application to adopt to new version, and work-out the test again.

     

    As you see, that's a comprehensive way to perform an upgrade of SQL server in production environment, thus I am not recommending to upgrade SQL server from 2000 to 2005, then to 2008 later due to the amount of work involved.  As well, after the upgrade this time, if you well maintain the documentation, subsequent upgrade/maintenance of SQL environment become more manageable.

     

    Lawrence

    2008年5月9日 下午 02:57

所有回覆

  • EIther way, you need fully test all apps that use sql db. Upgrading from sql2k5 to sql2k8 is no easier than from sql2k to sql2k8, but sql2k8 is not offically ready yet.

     

    2008年5月7日 下午 08:21
  • HI Rmiao

    Thanks for your information. I will try it out later

    Thanks
    2008年5月7日 下午 09:22
  • Maybe I need to ask you: why you are upgrading from SQL Server 2000 to newer version?  Of course, in Microsoft point of view, upgrading to new version is always recommended.  However, in end user benefit point of view, you need to take a balance between supportability and change involvement.

     

    For example, if you are not upgrading, SQL Server 2000 will cause supportability issue as time goes by.  If you upgrade, you need to perform careful audit to make sure existing line-of-business running without problem.

     

    Personally I am recommending to upgrade to SQL Server 2008 other than 2005, since as time goes by, SQL Server 2005 will carry the same supportability problem: newer application will definitely having longer life time.  If you don't want to perform the same complex audit after 2-3 years, you better choose SQL Server 2008.  (Of course, if you are not planning ahead for 2-3 years, that point becomes meaningless)

     

    To perform an upgrade, the safest way is to audit existing application, and all view/stored procedures and functions that is called by existing client application.  What you need to consider is whether there is any feature you are using becoming obselete in SQL2008.

     

    To work bottom up, you may scan through the command dependency from design document of client application/middle-ware developer.  If those information is not documented, you can still scan through the source code, or use SQL Server Profiler to capture the command being use in worse case.  However, in most of the case, most of the SQL language being used commonly will not be obselete.

     

    To work top down, you can set up an isolated environment with SQL2000 deployed and upgrade to SQL2008 , with production data, application and client populated to this environment to perform QA test.  Of course, you need to define a test script to make sure everything is effectively tested out.  If the client applcations behave correctly, you can safe to upgrade the productoin environment (with test plan, and fallback plan).  Else, you may need to modify the client application to adopt to new version, and work-out the test again.

     

    As you see, that's a comprehensive way to perform an upgrade of SQL server in production environment, thus I am not recommending to upgrade SQL server from 2000 to 2005, then to 2008 later due to the amount of work involved.  As well, after the upgrade this time, if you well maintain the documentation, subsequent upgrade/maintenance of SQL environment become more manageable.

     

    Lawrence

    2008年5月9日 下午 02:57
  • In addition, you can begin evaluting SQL2008, since a complete walkthrough of the steps I've mentioned (e.g. comprehensive testing) will definitely bring your upgrade timeline beyond launch day of SQL2008, or you may even join the Microsoft TAP (Technology Adaptation Program) to enjoy support from Microsoft professionals.   We need to know upgrading is usually a more complex task than put the DVD in the tray and run setup, even though MS platform product is already one of the best platform I've seen that provides viable upgrade path of product to end user

    2008年5月9日 下午 03:03
  • Hi Lawrence

    Thanks very much for your information. Could you give me some information about how to join Technology Adaptation Program for SQL 2008? I tried to look at SQL Server website, and I am not sure where it is (Do I need to pay for it ).  Is there anyway I could find out what function has been obsoleted from SQL 2008?


    Thanks very much for your Help.

    2008年5月12日 上午 07:38