Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Moving Your Database to a New Server Expand / Collapse
Author
Message
Posted Friday, November 3, 2006 9:47 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 10:02 AM
Points: 339, Visits: 390
Comments posted here are about the content posted at temp
Post #320368
Posted Thursday, November 16, 2006 4:31 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:44 PM
Points: 131, Visits: 801
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
Post #323362
Posted Thursday, November 16, 2006 5:56 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:13 PM
Points: 80, Visits: 331
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.
Post #323384
Posted Thursday, November 16, 2006 8:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 11, 2007 10:24 AM
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?
Post #323470
Posted Thursday, November 16, 2006 9:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 22, 2008 12:29 PM
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.

Post #323501
Posted Thursday, November 16, 2006 9:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 11, 2007 10:24 AM
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.

Post #323508
Posted Thursday, November 16, 2006 10:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 9, 2008 9:25 AM
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.
Post #323554
Posted Thursday, November 16, 2006 11:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 28, 2007 3:12 PM
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.

Post #323583
Posted Thursday, November 16, 2006 12:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, February 15, 2014 10:40 AM
Points: 14, Visits: 66

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.

Post #323596
Posted Thursday, November 16, 2006 5:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 24, 2007 2:50 PM
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

Post #323712
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse