Solved

Different methods to upgrade SQL Server 2005 to 2008

Posted on 2011-09-12
5
272 Views
Last Modified: 2012-05-12
Hi All,
I have number of Oracle & SQL server 2005/2008 databases. I used to work with SQL server part time but seems I need to take care of them full time on our new upgrade project.
The latest work stack demands upgrading SQL server databases from 2005 to 2008. I have number of systems ranging from 10 GB to 200 GB and I am looking for a practical advice. I have done upgrade of some small dev 2005 databases by using detach/attach technique but not sure if I should try that on big database systems?

Is it also possible to upgrade the PROD systems with almost no downtime?
We use SQL server Analysis/Integration & reporting services. What do I need to do for them besides upgrading the software?
Can I run SQL server 2005/2008 on same box? I am thinking this because in case the upgarde fails I can go back to old 2005 version easily.

Thanks
0
Comment
Question by:crazywolf2010
  • 2
  • 2
5 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36522500
Yes, you can have multiple instances on the same server and those instances can be different versions; therefore, one question is do you need for the new instance names to match the old ones for application purposes?

If not, you could install new SQL 2008 instances and then restore backups of your current production databases there for testing. Once you feel comfortable everything will work and/or create the necessary upgrade scripts, i.e., ALTER statements you can take an outage and move or use same method (take new snapshot/backup and restore) thus retaining SQL 2005 instance that you can just stop or disable logins to.

This may also come in handy if your new SQL 2008 instance meets the criteria:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_7251-Database-Copying-Wizardry.html
0
 

Author Comment

by:crazywolf2010
ID: 36528252
Hi There,
I have come across such methods but tell me how this could be done with almost zero downtime?

Even though I have taken backup of SQL server database, the SQL server installation is dependent on the windows registry. Will Vmware server snaphot/backup will do? I never used vmware backup for database systems and not sure how it works? Looking for some practical advice.

Many Thanks
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 36531228
You don't want to backup the server, i.e., the registry for this, you just want to backup the database unless I am mistaking your intent. If you use the SMO option on the Copy Database Wizard it will leave the current database online ... but that is not necessarily a good thing as that means you can get more changes after the copy that you need to account for. Therefore, at some point you will need to take an outage from users perspective to ensure that they do not make any more changes. That way you can cleanly cut over without losing data. If you do an in place upgrade, you will have to take an outage, If you do detach and attach, you will have to take an outage. If you build a whole new server, but then want to swap out the server name/ip addresses after, you will need to take an outage. You get the point. You are doing a server migration. Unfortunately that involves some outage or impact to the users .

An alternative that comes to mind is to bring up the new system and the users run in parallel, so you do have a cut over still but the users will have contiguous flow though with lots of effort on their part to load new client and ensure they dual enter information unless you take an outage on the original box once they are up and running on the new one and do a final copy. Depending on how much the data being entered needs to be looked at real-time, this may be viewed as an outage.

Hope that helps. Sorry it is not what you wanted to hear, but its how I know this to be done. Since you are on VMWare, there may be some VM-level tricks you can use to spin up a new instance that is identical but given you have to do a SQL install/upgrade at some point, i.e., you need an upgrade copy and not just a copy, that likely has a small downtime window as well ...

Good luck!

Kevin
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 250 total points
ID: 36533734
I'm going to throw in a few pertinent points to this:

Have you checked with all the software vendors and ensured that they can work with SQL 2008?  

My company's SW is still not SQL 2008 compatible even now. We have a small dev team that is caught between working on the upgrade path and being compliant with government regulations by certain dates. That leads to conflicts.

Some databases can not simply be detached and reattached. An example is the Dynamics Great Plains. They need to be restored and the userid's reset. There are other applications that require similar care and feeding to be moved.

Also check with your vendors -- as an example, our application moves need to be handled by us, the SW company.  Also having been on the production DBA side, I have messed up moves that should have been handled by the vendor. I became good enough at my last job to do the moves for the vendors. But I am a full up Production DBA with a foot in the dev side so I could do it.*

You have to consider moving or preserving both the SQL and Windows logins:
How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008
http://support.microsoft.com/kb/918992/

I don't suggest upgrading in place. That leaves you limited back-out options. You can shut down the SQL Server Services and then just copy the database files across without actually doing a detach operation. But again some SW requires a restore to work correctly. So even if you copy across and attach databases, they may still need things like replication resets, mirroring setups, specialized passwords and others to be properly set up.

Another thing that we did at my last company was to modify the DNS to add CName records to point to the actual servers names.  So there were MyApp1 and MyApp2 pointing at MyServer1.  If I had to move a database to MyServer2, I would just point the CName at the the new server. Even if it was an instance we still did it. It would be MyAppServer1\MyApp.

Now to the downtime issue: There is no way to move databases without downtime. But you don't have to do it all at once. If you go through and create the CName -- change the clients to point to the CName and then move the database and the CName -- you would have minimal downtime for that app. Then do the next move/upgrade. Rinse and repeat as needed.

Some bookmarked pages:
How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
How to move databases between computers that are running SQL Server
http://support.microsoft.com/kb/314546

* Forgive the hubris please -- it takes years to get there. ;-)
0
 

Author Closing Comment

by:crazywolf2010
ID: 36535056
Pointers Provided
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now