Solved

SQL 2005 Server migration

Posted on 2013-10-22
6
406 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 77

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 77

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

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.
Learn about cloud computing and its benefits for small business owners.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

791 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