Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2012
    Posts
    120

    Unanswered: upgrade from SQL Server 2005 Express to Enterprise

    Hi all,
    I need a smart procedure, not too much time consuming, to upgrade my MSSQL 2005 Express instance containing 2 application databases to Enterprise (2005 or 2008).
    It's not important if the instance name remains the same, it's important that the procedure is not too complex and the application will continue to run (eventually changing some parameters).
    The application connects to the DBs via ODBC.

    Thank you very much for your help!

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Ok. I will bite.

    1. Stand up a test server running 2005 Express or a test instance.
    2. Restore a backup from your "live" 2005 instance to your test instance.
    3. Connect your application to this test instance.
    4. Run a wide and varied series of tests.
    5. Upgrade your test instance.
    6. Rerun all tests from step 4.
    7. If all is gravey, right before the upgrade on the live instance, shut off all access to the database from the application and take another full backup.
    8. Run the upgrade on the live instance.
    9. Rerun all tests from step 4.

    Backout plan...
    If something goes sideways during step 8 or 9, uninstall the version you are upgrading to, reinstall the 2005 express, and restore the backup from step 7.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Mar 2012
    Posts
    120
    Thanks,
    what I'm mostly interested in is the mssql upgrade part (your point 5).
    How would you do that?
    I see many links in the Internet talking of In pace vs Side-by-side upgrade, parameters to use during the upgrade and I'm little confused about what is the smarter procedure to actually perform this upgrade.

    Thanks

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    It is largely a matter of running the installation disk or the downloaded iso file. The options you choose and whether you do this side by side or in place largely has to do with the requirements of your system and the environment, what resources, such as hardware, you have available to you and what your back out plan is. As for "side by side" or "in place", there are pros and cons to both. An in place deployment, saves you the hassle of transferring logins, fixing connection strings, moving any SQL Agent jobs, and a number of other features that reside in SQL Server but not the user databases. However, the side by side lets you do parallel testing, and makes it easier to rollback your upgrade if something goes sideways during installation, setup or testing. I am sure there are some pros and cons I have left out. But there are plenty of people on the internet, that will give you their two cents. As you can see here...

    Let me google that for you

    As with many things, the answer is "it depends".
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Oh yeah, your backout plan needs to account for every thing in your current SQL Server installation (logins and jobs etc...) that resides outside of your user database backups, so my little plan above needs a little more fleshing out.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Mar 2012
    Posts
    120
    If I backup my user dbs with SQL Server 2005 Express, uninstall, install 2005 Enterprise and restore the dbs, will it work in your opinion?

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    It all really depends. Personally I would not uninstall unless it becomes necessary. You will lose logins and such. I have never upgraded from Express to Enterprise, so it might be necessary. I do not know. I have upgraded from 7 to 2000 and 2000 to 2005 and 2005 to 2008.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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