#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2010
    Posts
    8
    Rep Power
    0

    Upgrading a db from sql2000 to sql2005


    I have just upgraded 2 databases from SQL2000 to SQL2005.
    Both are used for running 2 websites (both DotNetNuke sites from different servers).

    The first one was upgraded sucessfully. However, I have a problem with the second one. The website doesn't load when trying to connect to my SQL2005 database.
    Error message just says: Site Unavailable (default DotNetNuke message).

    I followed the very same steps both databases:

    1. Backed up sql2000 db.

    2. Placed a copy of the above .BAK file on the SQL2005 server (in the same path/location as above.

    3. Created a new database on the SQL2005 server (with the same name)

    4. Restored the database (on SQL2005) using the copied .BAK file from the SQL2000 back up (from step 2 location)

    5. Re-created the database user (which is the same for both, lets call the user NIGEL) and given dbo rights to the database user

    6. Checked for any orphaned users, by using this script below:
    EXEC sp_change_users_login 'Report'

    7. Used this script to fix orphaned users:
    EXEC sp_change_users_login 'update_one', 'NIGEL', 'NIGEL'

    NOTE: in both databases - I did get NIGEL listed as an orphaned user, and the above script fixed it.

    8. Re-run the script in step 6 again to check that orphaned users have gone.

    9. Changed the connection string to point to the IP address of the new SQL2005 server (in web.config file).

    SQL2005 INFO:
    * Both databases have the db user 'NIGEL' listed under the database >> security >> users.

    * Under the properties of this user on General Tab the username and default Schema is the same and db_owner is checked.

    * Under the Securables Tab >> Execute permissions have been granted.

    SECURITY >> LOGINS >> PROPERTIES >>
    >> SERVER ROLES: Public is checked
    >>User Mapping : User and Default Schema is the same for both
    >> Status: Grant and enabled - are selected.

    FURTHER INFO:
    * I can ping the SQL2005 server ok from both web servers.
    * If I replace the IP address in web.config back to the old SQL2000 address the site runs ok.
    * tcp/ip is enabled on web server

    I don't know what to check next.
    Any ideas?

    thanks in advance,
    mark.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2010
    Posts
    8
    Rep Power
    0

    sorted - firewall issue


    Sorted.
    it turns out it was indeed a firewall issue - it was outside our DMZ and wasn't allowed to talk to the new sql server.
    the first database upgrade was inside our DMZ so didn't have this problem.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Location
    Kansas City
    Posts
    13
    Rep Power
    0
    How did you like converting all your DTS packages to SSIS?

IMN logo majestic logo threadwatch logo seochat tools logo