Solved

SQL 2008r2 upgrade

Posted on 2013-11-14
3
232 Views
Last Modified: 2013-11-29
A client of ours needs SQL 2008r2 standard. Up until this point they have been running the MSDE version of SQL for a couple of databases.  They have a single server that we will be installing this on.  My question to you is will it simply upgrade the MSDE version to SQL2008r2 during the install or will we have to do anything to not mess up those databases?  Thanks for your help.
0
Comment
Question by:jruskey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 39650034
Are you sure it is MSDE? That essentially was declared dead in 2005 with the SQL 2005 Express edition. The restrictions were less on 2005 Express .

And a server that was running MSDE I would look at very closely as to whether it can handle 2008 Express let alone the Standard edition.

With that being said, I wold suggest having a replacement server. Shutdown the MSDE services and then copy the MDF and LDF files to the new server. Then attach the databases to the new SQL server instance.

After they attach use the sp_help_revlogin to transfer logins.

Note that once a higher version of SQL touches an mdf/ldf file they can never be used by the lower version again.
0
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
ID: 39650271
Hi,

I prefer to restore a backup rather than fiddling with the actual data files. You don't have to shut SQL down or disconnect the databases.

If they are all on the one server, I'd be inclined to install SQL 2008R2 as a named instance, and migrate on the one server.

In terms of a version upgrade/edition upgrade in place, I'd be a bit suspicious that it would work as you hope. Personally I wouldn't chance it, as worst case it could me restoring the server from tape to recover.

Note that there are several version of sp_help_revLogin, and different ways to invoke the procedure. As well as the logins, you do want to capture server roles, and default databases. (I suggest that if the default database is master you change it to tempdb. Its very rare that master is available and not tempdb, and if that is the case, then very little else will work properly anyway. For users with public access only it doesn't make much difference. For sysadmin users, it means that accidently executing a create table/procedure/function without selecting the database means master doesn't get all this bloated junk in it.)

If you do all this side-by-side, you can do this as a test system, get sign-off on the upgrade, then do the cutover.

HTH
  David
0
 
LVL 1

Author Closing Comment

by:jruskey
ID: 39685943
My other versions of SQL were named instances, so I was able to install the full version of SQL and not touch those other versions.  Thanks!
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Like many organizations, your foray into cloud computing may have started with an ancillary or security service, like email spam and virus protection. For some, the first or second step into the cloud was moving email off-premise. For others, a clou…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question