Solved

SQL 2005 Server migration

Posted on 2013-10-22
6
404 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Move SQL 2005 Express to Server 2012R2 19 100
Trasfering FSMO roles 8 95
User profile Size Report 3 63
Strange msg in the SSMS pane 13 47
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
On July 14th 2015, Windows Server 2003 will become End of Support, leaving hundreds of thousands of servers around the world that still run this 12 year old operating system vulnerable and potentially out of compliance in many organisations around t…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

929 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

11 Experts available now in Live!

Get 1:1 Help Now