| Author |
Topic |
|
chedderslam
Posting Yak Master
223 Posts |
 Posted - 2008-04-04 : 15:43:31
|
| I may be put on a project involving the migration of a SQL Server 2000 database from one physical location to another. I've never done something like this so any guidance would be appreciated.My plan is to:1. Backup the live database.2. Do a restoration at the new location.3. Set up transactional replication between the two databases.4. Update records to point to the new db.Are there any problems with doing it this way? Is there a better solution? I am trying to do this without any downtime, or as little as possible.Thanks for any help. |
|
|
jhocutt
Constraint Violating Yak Guru
385 Posts |
Posted - 2008-04-04 : 16:08:17
|
| Yes there may be problems.1) Replication will add columns to all of the replicated tables.2) Replication will add stored pocedures to each database replicated, for insert/update and delete for each table replicated.3) If this is a high voulme database you will need a pretty good pipe between the 2 databases.4) After shutting down the current server you would need to remove all of the replication stuff(Actually before shutting it down).5) Only tables with a primary key can be replicated.(Yeah I know they all "should" have one)6) Don't forget to copy DTS packages and scheduled jobs, user ids etc. and make sure that they are only running on one server at a time.As for is there a better way? Not that I can think of without down time."God does not play dice" -- Albert Einstein"Not only does God play dice, but he sometimes throws them where they cannot be seen." -- Stephen Hawking |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-04 : 18:00:54
|
| Check this out:http://vyaskn.tripod.com/moving_sql_server.htm |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-04 : 18:19:21
|
quote:
Originally posted by jhocutt
Yes there may be problems.1) Replication will add columns to all of the replicated tables.2) Replication will add stored pocedures to each database replicated, for insert/update and delete for each table replicated.
1. Not necessarily. We replicate and none of our tables have extra columns.2. Only if you keep it at the default of using stored procedures. You can use direct DML statements.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-04 : 18:20:41
|
| I'd get the second server ready by doing a restore of a full backup with norecovery. I'd apply tlogs with norecovery until the switch is ready to be made. Then I'd apply the final tlog with recovery and be done with that database.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-05 : 22:48:10
|
| >> Replication will add columns to all of the replicated tables.Sql only adds timestamp column, if table doesn't have any, when you set bi-directional replication. |
|
|
|
chedderslam
Posting Yak Master
223 Posts |
Posted - 2008-04-06 : 08:01:37
|
| Thanks for all the input. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-06 : 11:03:11
|
| Replication will add GUID column in Merge replication and transactional replication with updatable subscription and Queued updating.They are triggers thats tracks the changes and replicate to publisher and subscribers. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-06 : 16:21:09
|
| >> Merge replication and transactional replication with updatable subscription and Queued updating.They are bi-directional replications. |
|
|
|
|
Migration to another physical location