Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
barnsley
Starting Member
34 Posts |
 Posted - 2012-05-25 : 07:11:09
|
| 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 user6. 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 SQL200 address the site runs ok.I don't know what to check next.Any ideas?thanks in advance,mark.mark. |
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-06-01 : 04:05:48
|
| Did you use the upgrade adviser that comes up with the SQL Server 2005 setup ?After Monday and Tuesday even the calendar says W T F .... |
 |
|
|
barnsley
Starting Member
34 Posts |
Posted - 2012-06-01 : 04:36:49
|
| sorted. it turns out it was 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. cheers, mark.mark. |
|
|
|
Anne1234
Starting Member
3 Posts |
Posted - 2012-06-20 : 03:09:03
|
| Useful information shared..I am very happy to read this article..Thanks for giving us nice info. Fantastic walk-through. I appreciate this post.anne Shirley |
|
|
|
|
|
|
Upgrading a db from sql2000 to sql2005