Last Friday we upgraded our two database servers to MS SQL 2005. Our co-worker Matt Horrell found some scripts online that helped us a lot, like the one that reads all the users and generates another script to create them back on the new server, and the other that reads all the linked servers and do the same.
The whole process took 2:30 hs and it was smoothly done.
I’m posting here the steps to help anybody else.
- SQL: First we ran the scripts and saved the results.
- IIS: created an “Under Construction Site” and set it to all available IP addresses (no host headers)
- IIS: stopped all web sites leaving only the “Under Construction” on
- SQL: detached all the databases on both machines
- SQL: uninstalled SQL 2000 on both machines
- SQL: installed SQL 2005 on both machines
- SQL: ran the script to create all users back on
- SQL: ran the script to link the servers back on
- SQL: attached all the database on both servers
- IIS: restarted all the web sites
As I told before, it took about 2.5 hours total, dealing with around 65 databases.