Solved

SQL 2005 Server migration

Posted on 2013-10-22
6
403 Views
Last Modified: 2013-10-24
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?
0
Comment
Question by:sullend
  • 3
  • 2
6 Comments
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39594084
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
 
LVL 38

Accepted Solution

by:
Jim P. earned 300 total points
ID: 39596026
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
 
LVL 76

Assisted Solution

by:arnold
arnold earned 200 total points
ID: 39596058
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 300 total points
ID: 39596125
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
 
LVL 76

Assisted Solution

by:arnold
arnold earned 200 total points
ID: 39596167
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
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 300 total points
ID: 39596293
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
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.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

760 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

18 Experts available now in Live!

Get 1:1 Help Now