May 3, 2007 at 9:12 am
I have a web server with ASP.Net 2.0 and SQL Server 2000 installed. Sudden client need has me needing a SQL 2005 feature (varchar(max)). I have a CD and license to install SQL 2005. There are no complexities in the setup (no backup plans etc, and only two databases, both of which have been tested with 2005). I do have Red Gate SQL Backup.
What's the best and simplest plan? Can I co-install SQL 2005, as a new instance, then change my setup to swing over the db in question to SQL 2005, or should I uninstall 2000 first?
What makes sense?
Thanks
May 3, 2007 at 10:45 am
I know there are plenty of people who upgrade from SQL 2000 to SQL 2005 in place (don't uninstall SQL 2000 first), but I've always preferred to install a separate instance and transfer everything so I still have the SQL 2000 instance to fall back to if something goes wrong.
Most recently, we installed SQL 2005 as a named instance on the same server where SQL 2000 is the default instance. Backed up the user databases and restored in the SQL 2005 instance. Scripted jobs, linked servers, and logins and created them in the SQL 2005 instance. Saved DTS packages as files and imported int SQL 2005 as legacy DTS packages. Ta da!
Greg
Greg
May 3, 2007 at 12:56 pm
Thanks for that. It confirms my intention to install SQL2005 as a named instance. I'll shut down SQL 2000 later.
During my testing, I have no recollection that I backed up the dbs and the resotred using 2005... what I recall is that I just opened them using SQL2005. Was that nuts, or what?
Phil
May 4, 2007 at 9:50 am
Was that after installing SQL 2005? If so, then they were upgraded during the installation. By doing that, you have no recourse if you want to go back to SQL 2000.
Greg
Greg
May 8, 2007 at 9:55 am
Sorry for the delay, looks like my post vaporized.
I don't know whether my db was upgraded during installation, but I can make a file copy of the db and then attach the original to SQL 2005. Will that upgrade it?
I really don't need to go back to 2000.
So I guess my plan is to install a new instance of SQL 2005, then detach the dbs from 2000, make file copies, then attach them to 2005. I have nothing in them except tables and stored procs, and I can change the connection strings I use and am reasonably confident I can create the required users.
In SQL 2000, I remember going to a lot of trouble to install using a low privileged user, do I need to do that?
Phil
May 8, 2007 at 10:20 am
Attaching the database files will upgrade but you'll still have to change the database compatability level afterward using sp_dbcmptlevel.
You'll still want to use least privileged service accounts to install. See "Security considerations for SQL Server installation" in SQL 2005 BOL.
Greg
Greg
May 8, 2007 at 10:51 am
I believe it is recommended to install both and do a side-by-side upgrade. The upgrade adviser is good to, it will let you know of any major pitfalls you may run into during the upgrade.
Good Luck!
-Ben
Ben Sullins
bensullins.com
Beer is my primary key...
May 8, 2007 at 11:08 am
Thanks, I'll post back when I get to this, which hopefully will be soon.
Phil
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply