The other day I had to uninstall SQL Server 2005 Standard Edition from a machine and reinstall it.
We had previously done an in-place upgrade from 2000 and I was never 100% convinced the instance was sound. The server is our SSIS and SSRS server. It was originally supposed to be a DEV box. But it has become more of a PROD ETL and reporting box. The only user-facing application being hosted is SSRS.
Prior to the upgrade:
- I collected a handful of scripts to help with the process.
- Backed up my SSRS encryption SN key.
- Tested backing up the databases.
- Created scripts to recreate users, linked servers, backup devices and jobs.
- Documented database properties through screen captures of each screen in SSMS > Server >
- Right-click Properties. You can also use the SERVERPROPERTY function to get a bunch of different server properties but, aside from verifying version numbers throughout the upgrade process, I didn’t use that.
- Verified I had the right password for the domain account I was going to run SQL services under.
- Copied the SQL Server 2005 Standard Edition installer media along with SP1 and SP2 to the target machine to speed things along.
Going into it, my plan for the actual upgrade:
- Disable any jobs that might run.
- Stop the reporting web site.
- Stop reporting services.
- Backup all dbs.
- Test restore of one of the smaller dbs on another machine.
- Detach dbs.
- Uninstall db engine.
- Reinstall db engine.
- Install SP1, SP2 and any updates needed.
- Attach dbs and test.
- Recreate logins.
- Recreate linked servers.
- Recreate jobs.
A few things…
Instead of detaching the databases by clicking around in SSMS, it’s much faster to script out the detaches. Here is the syntax:
EXEC master.dbo.sp_detach_db
@dbname = N'somedb',
@keepfulltextindexfile=N'false'
GO
Using the SSMS interface you can reattach all the databases quickly so I didn’t script that out.
Although I had time to prepare, I knew a few things would shake out during the install as they always do.
I ran the install and the updates under my domain account. When I reattached all the databases, they were all owned by me. So I ran scripts to assign them all back to sa:
USE [somedb]
GO
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
GO
To enable AWE, you have to give the user you’re going to run SQL under the ‘Lock pages in memory’ privilege. To do this, go to Start > Run and then enter gpedit.msc. Navigate to Group Policy -> Computer Configuration -> Windows Settings -> Security Settings -> Local Policies -> User Rights Assignment. Double-click ‘Lock pages in memory’ and add your user. Click OK. I feel like I did another install recently where I didn’t have to do this step but I’m not sure. After you do this, connect to the db in SSMS and run the following:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
I wanted to change the user account that SSRS ran under. Updating the user account in the SSRS Configuration Manager leads to encryption problems. I was careful to backup the encryption key before I started so I was able to follow the process detailed here http://support.microsoft.com/kb/842421 and was up and running again with little trouble. For quick reference, these 2 commands allow you to stop/start IIS through the command line:
iisreset /stop iisreset /start
The server originally had SQL Server 2005 SP2 installed along with a bunch of other SQL compoments. When I reinstalled SQL Server and tried to apply SP1, SP1 bombed when trying to update the support files. I wish I recorded the exact error but I didn’t. I’ll probably repeat this process on another box soon and if I encounter the problem I’ll provide more detail here. The workaround:
- Go to Start -> Control Panel -> Add/Remove Programs.
- Uninstall Microsoft SQL Server Setup Support Files.
- Install SqlSupport.msi from SQL Server installation media.
- Try installing SP1 again.
SP2 installed without a hitch.
After installing the Service Packs I also decided to run the Security Baseline Analyzer on the machine to identify new fixes. It found one I needed so I installed it. Typically I’d test it in DEV first but since this was supposed to be a DEV box in the first place and I was doing well on time I installed it. It went in without a problem.