Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2008

    Unanswered: Migrating to SQL 2005

    Hey y'all, I'm looking to migrate several databases on SQL 7 and SQL 2000 to a new SQL 2005 box. Has anyone done such a migration? What would you recommend as a good methodology for it? Also, should I expect stored procedures, triggers, indexes, other complementary stuff to break? I'd love to hear about your migration experiences.

  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    The only sql code that might break migrating from 2000 to 2005 would be some references to system objects.
    Also, you will need to migrate any DTS packages to SSIS, as DTS is no longer supported. I have heard that the wizard for upgrading DTS to SSIS is a bit klunky.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  3. #3
    Join Date
    Jun 2004
    Long Island
    You can install the "DTS Designer for SQL 2000" as an add on for SQL 2005, and it is fully backward compatible with DTS Packages, you just cannot create new packages, but you can edit, correct, execute existing packages with the tool, from what I've encountered so far, it works. I have some pretty complex DTS packages with ActiveX scripts and calls to O/S that work with no problems.

    I've restored 2000 DBs to 2005 with no problem, just get the new sp_help_revlogin sproc for SQL 2005 (if you extract logins). I ran a SQL 2000 generated file against 2005 with no problem to create users and existing passwords from a 2000 box.

    system tables/views, some have change it sysobjects sys.objects, etc.
    if you use xp_cmdshell, you need to enable it via "SQL Server Surface Configuration" program (don't understand why they put it here, but it's here).

    Although old style joins are compatible, I would update all SQL Code to use Ansi style join syntax, instead of in the where clause. A lot of nice, what I consider "Oracle-like", upgrades with index rebuilding, stats update, etc.
    Last edited by PMASchmed; 06-09-08 at 14:46.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts