Different methods to upgrade SQL Server 2005 to 2008

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.

Who is Participating?

Improve company productivity with a Business Account.Sign Up

Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
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 CrossChief Technology OfficerCommented:
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:
crazywolf2010Author Commented:
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
Jim P.Connect With a Mentor Commented:
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

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
How to move databases between computers that are running SQL Server

* Forgive the hubris please -- it takes years to get there. ;-)
crazywolf2010Author Commented:
Pointers Provided
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.