Upgrading Database Server to SQL Server 2005 Standard Edition for DNN websites
i have been using multiple instances of SQL Server Express 2005 to provide backend services to a few websites. SQL Server Exress 2005 provided a quick and FREE way of running the database. however, since it is meant for testing purposes the Express edition doesn't scale well and gave reliability issues from time to time. also, using Management Studio Express locked the DB exclusively rendering the site inaccessible.
Today i spent some time upgrading the database to full fledged SQL Server 2005 Standard Edition. the installation was effortless and I immediately applied the SP2. however, when i tried to use the client tools they were still the ones from Express Editions. trying to install the full version of client tools always ended with an error - "Your upgrade is blocked because the existing component is a later version than the version you are trying to upgrade to". also, there seemed no direct way to uninstall the express editions that i had running.
Researching the first issue of installing the client tools including SQL Server Management Studio brought me to:
This provided me with all the information but I had to change a single step - In Step 7, I needed to uninstall the entire "Client Components" section rather than any one section within it.
The second issue was solved by going to Add/Remove -> SQL Server 2005 and clicking on Remove button. With that I was able to choose the specific instance of SQL Server Express to uninstall.
With SQL Server in place, Iwent to upgrade the DNN site as follows:
- Detached the DB from the SQL Express instance running the Database. Earlier on, I had realised that if you are using SQL Express it is better to create a separate instance for each database rather than using a single instance for all the DBs.
- Attached the DB to the newly installed instance of SQL Server 2005. Renamed the Database to something more meaningful. i also took this opportunity to shrink the DB and back it up.
- Opened the web.config of the DNN folder and changed the connection string. I was going to be using trusted connection and hence the change was extremely simple. Save the file with the changes.
The sites continued to work fine after the upgrade!
Cheers to better reliability with SQL Server 2005 SP2 Standard Edition....