SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Upgrading from MSDE to 2008 R2 question?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DaveGKeller
Starting Member

10 Posts

Posted - 01/05/2011 :  11:26:16  Show Profile  Reply with Quote
In a recent post I made here it was suggested I should upgrade all my database to R2 because of the 10 GB limit. I have a question on exactly how to do that. My existing is MSDE SP1. If I install 2008 R2, using a different InstanceName, can I create the same database name? Our current InstanceName is "DB2000". We are thinking about using a different InstanceName because "DB2000" shows up in a lot of examples out there. So my bosses said if we are switching databases we should use a new InstanceName. All of this is greek to me (not a d/b guy but I've been told to do it).

Can I install 2008 R2 on the same machine with MSDE, use a different InstanceName, and then copy or move the existing database to 2008 R2? I've seen in some examples Attach/Detach. I'm using the free Microsoft SQL Server Management Studio. I'm not sure if that matters.

I'm sorry it's such a noob question. As you can see I don't really know much about this but have no choice in being the one that has to do the job. I just really want it to go as smoothly as possible. Any help is appreciated and, if possible, please explain like I'm 4 years old

Thank you.

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/05/2011 :  12:07:28  Show Profile  Reply with Quote
"I should upgrade all my database to R2 because of the 10 GB limit"

If you have MSDE (i.e. SQL 2000) then I think your current limit is 2GB, not 10GB ?

If you install SQL 2008 (n the same machine, or on a new machine) you can just restore (or ATTACH) a SQL 2000 database.

I don't know if it is easy to install SQL 2008 alongside SQL 2000 on the same machine. We always do major version changes by migrating to a new machine as there is no chance of the new installation fouling up the existing one - and we can test on the new machine and then when happy take a fresh copy and "go live" on the new machine.
Go to Top of Page

DaveGKeller
Starting Member

10 Posts

Posted - 01/05/2011 :  22:05:27  Show Profile  Reply with Quote
quote:
Originally posted by Kristen

"I should upgrade all my database to R2 because of the 10 GB limit"

If you have MSDE (i.e. SQL 2000) then I think your current limit is 2GB, not 10GB ?


Right. In my previous post the responder said I should upgrade so the new d/b would be a 10 GB limit. I mentioned that to my boss and that's when he said to make the move. I probably should have kept my mouth shut

quote:
Originally posted by Kristen
If you install SQL 2008 (n the same machine, or on a new machine) you can just restore (or ATTACH) a SQL 2000 database.

I don't know if it is easy to install SQL 2008 alongside SQL 2000 on the same machine. We always do major version changes by migrating to a new machine as there is no chance of the new installation fouling up the existing one - and we can test on the new machine and then when happy take a fresh copy and "go live" on the new machine.


So would it be "better" to copy the .mdf and .ldf files from MSDE to a jump drive, install SQL 2008 on a different machine, copy the .mdf & .ldf to that machine and "attach"? Then when it's all running well and we're happy, then do the same on the real machine (after uninstalling MSDE)?

I'm going to be working on this tomorrow so I'm hoping to have a "plan" in place. Is SQL Server Management Studio Express capable of doing the Attach operation? I don't have it on this computer but it is on the machine that has the database.

Thank you again for all help. It is appreciated very much.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/06/2011 :  02:35:14  Show Profile  Reply with Quote
"I should upgrade so the new d/b would be a 10 GB limit"

Ah, OK, I understand now. SQL 2008 Express limit is 4GB (if you install the full-blown version there will be no practical limit)

Your plan of copy the MDF / LDF and Attach sounds fine (make sure that SQL Service on the current machine is STOPPED before you copy, or that you DETACH the databases before you copy - but then you will have to re-ATTACH them afterwards).

If the database needs to be online and stopping/detach+re-attach would be a problem, then restoring the last full Backup file instead would be fine for a test on Machine-B.

I imagine (but I don't know) that there is an UPGRADE route from MSDE to SQL2008 Express. So once you have done all your testing on Machine-B quite possibly you will be able to just "Upgrade" MSDE to SQL 2008 Express on Machine-A

For your test on Machine-B (and then again after the Upgrade on Machine-A) you ought to follow the steps for changing the compatibility level and rebuilding indexes etc (I'll post a link below). If changing the compatibility level gives you problems with your tests you can try putting the compatibly level back to "SQL 2000 / MSDE" (Compatibility Level = 80) and retesting - but, ideally, you would be better to be on the latest compatibility level because then you can take advantage of all the new features in SQL 2008

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000