SQL 2005 Server migration

I have a SQL 2005 Server that houses  DB's for several key applications.
what is the best way to go about replacing the Server With SQL 2008?
Is there any easy way to build the new server and then point the applications to the new server? Or do I need to treat each application that uses the server as a unique project and do them one at a time?
sullendAsked:
Who is Participating?
 
Jim P.Connect With a Mentor Commented:
A trick we used at our last company because we controlled our in house DNS servers was to create a CName record that was like MyAppName that pointed at the ServerName1. Then if we moved the DB to a different server we would just re-point the at the new server. That way we didn't have to touch the clients. It took a while to build up all the list and get all client machines readjusted, but once we did, we could do a change overnight and the users had no clue in the morning why everything  was working better.
0
 
ZberteocCommented:
Applications and db server are 2 different things. The DB server is independent and only stores data while application will be working with any db server it is pointed to from the connection strings, config files, etc. If you will use a new server than after the migration you will have to make sure that all connection info is updated with the one for the new server, like name or IP, port number, whatever you are using.
0
 
arnoldConnect With a Mentor Commented:
As was pointed out, you can install the OS, install the sql, export the logins from the existing database and create them on the new system.
Then depending on the applications, transition a set of databases per application via backup/restore and update the application to point to the new server.

If you have a test environment, you should test the application with sql 2008 as the backend (you may want to consider going to sql 2008 r2 if possible).
Once an application/databases transition, you move to the next one.

Changing DNS is an all or non proposition.  A staggered migration provides for a reasonable way to resolve issues on an application by application basis with controlled failure management.
I.e. Moving all and some may fail, you have no change everything back without regard on those that work without issues.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Jim P.Connect With a Mentor Commented:
Changing DNS is an all or non proposition.  A staggered migration provides for a reasonable way to resolve issues on an application by application basis with controlled failure management.

That is why I said to do the CName by AppName and not ServerName. So if you set AppName1 to point at ServerName1 and then set AppName2 at ServerName1 as well. Then you can modify AppName1 to point at ServerName2 and  AppName2 is still good. If the clients are looking for AppName1 and AppName2 the underlying ServerName doesn't matter. About the only time I could see it mattering is linked SQL servers transferring data. But it probably still wouldn't matter depending on authentication.

We did it many times. Again you need to have the ability to change the setup parameters. We worked for over six months of client upgrades to finally get all of them pointed at the AppName instead of ServerName.

<bragging>In that six months I automated the upgrade of 26 applications to the point that it happened in the login script in the morning, including reboots as needed, for over 250 clients.  We only had one legacy app that was used by about ten users that the DNS trick failed. The four terminal servers were also another exception. And they weren't all SQL Server apps. I have been gone from that company for over four years and they still use my stuff as far as I know. I've now built that into my current company's system.

An email sent today by a supervisor was: Jim was able to apply the 6.6.1 overnight to all the hosted servers. He also placed the available other app fixes on all hosted servers as well.

The reply sent by my local CEO at 9:30 tonight: This is fantastic news!  Thanks to Jim we make short work of the clean-up and correction process.  Thank you Jim!! </bragging>

So thinking about the process can take mundane work from the help desk and Level 1 techs to a full automation.
0
 
arnoldConnect With a Mentor Commented:
Fair enough, though adding another layer that masquerades the identity of the server.....

Prior to migration, every itching has to be verified and checked.
0
 
Jim P.Connect With a Mentor Commented:
Fair enough, though adding another layer that masquerades the identity of the server.....

I'm not going to say it is easy to build up the skills needed to do all this. But with reg read, understanding the msi/setup commands, understanding networks, and other stuff you get to the point that your view is that computers were built to work for us, not to make us work to fix them.

Prior to migration, every itching has to be verified and checked.

Also not disagreeing on that either. But as your skills build you know what to look for. We were the beta site for an application (i.e. we got version x.1.0 two weeks before they shipped). We caught 98% of the mistakes in the installation and use of the app. We also got a nice discount and found about 90% of the bugs. The company loved us and I got to talk to the developers on more than one occasion. One was a stupid one. They had made the desktop shortcut a default read-only in the prior version. It was a matter of adding in an attrib command prior to the install executing.
0
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.