Solved

Different methods to upgrade SQL Server 2005 to 2008

Posted on 2011-09-12
5
268 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Pointers Provided
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

771 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

15 Experts available now in Live!

Get 1:1 Help Now