Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving Your Database to a New Server


Moving Your Database to a New Server

Author
Message
Paul Mu
Paul Mu
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 391
Comments posted here are about the content posted at temp
liebesiech
liebesiech
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 854
Very well described! What I specially like is that this article covers also the approach of such an exercise and how to minimize the impact on the business which has high demands regarding availability. Once again very helpful and complete for my purposes.
Thanks
Rene
SQL-DBA
SQL-DBA
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 462
Watch out for default collations on the new server. That one got me recently b/c I like to use accent insensitive (AI) and the SQL 2000 default is AS.
Sandy Wood
Sandy Wood
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1
Great, detailed article especially helpful for a new DBA like myself. I've done a few database moves and I'm ashamed to admit that I've taken another way to accomplish the same task. I've detached and reattached the databases and they seem to work fine on the new servers. Any downsides to just doing it this way?
Christopher P. Kile
Christopher P. Kile
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 4

Your primary downside to detach/attach-to-new scenarios is that the new server might not be completely backward compatible. This has been the case in the past with some mighty well-known database offerings; on the plus side, it's usually well-documented in their release notes.

If you usually give up a great deal of time in stepping down from these scenarios to data transfer scenarios (backup/restore,bulk copy, etc.), detach/attach is a good candidate. I usually prefer to use a backup/restore, which insures, first, that I HAVE a backup and allows for the DBMS to reformat my database as it is restored, which feels to me to be more likely to work than a database either working perfectly in its new home or reformatting itself (if such is required) on attachment to a database server hosting a new version of the DBMS.


Sandy Wood
Sandy Wood
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1

Great points. We've been doing a bit of 2000 to 2005 moving and we never took the compatibilitly issues as serious as we should. We've always had the attitude that "Well the database is a Microsoft database and they said it would work in 2005......."

I like the backup/restore model you mention. It does seem to offer a better method for sucess.

Thanks again for the help.


TF-245998
TF-245998
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 62
I agree with SAM, sort order/collation should be a major consideration, if you're in anything but a tiny shop - we have 5 different flavors here. Putting a user database on a server having system databases of different sort/collation can give you plenty of problems.
eduardo Pin
eduardo Pin
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 1

This is one way to perform a server change, but using differential backups the system downtime may be long.

I Belive that using transactional log backups this operation is faster than using diferential backups.


john hill
john hill
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 68

The last time I had to move a heavily used database to a new server as part of an upgrade to SQL 2005, I took a full backup, restored it on the new server, later took a differential backup when I was getting close to migration and then copied tlogs over to the new server when I was ready to migrate.

Downtime was minimal while backed up, copied, and restored the last tlog... Good article.


joe debuzna
joe debuzna
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
Good article especially considering that there's a definite lack of concise, to the point articles out there on SQL Server migrations.

It may be good to note also that GoldenGate Software just graduated from SQL Server trigger-based replication to replicating right off the transaction logs. DataMirror has something as well. And Quest Software could very well be coming out with something too.

Another consideration from the "Understanding What is Involved" section may also be whether the OS environment is changing (i.e. going from 32-bit to 64-bit to get that extra RAM we're always starved for).


Kindest Regards,

joe d

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search