| Author |
Topic |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2012-08-28 : 17:44:43
|
| I'm going to use log shipping as a quick way (i.e. very little downtime) to migrate my databases to a new server in a different location. Since I can't afford to lose any transactions, my plan is to make the databases read-only on the primary server, then take the last transaction log backup, then copy it to the secondary server and restore it there. Will making the primary databases read-only screw up my transaction logs in any way? Is there a better way to ensure that no data modifications occur on the primary that aren't captured in the t-log backups? (Assume that my apps are in a million places and use a million servers talking to SQL Server.) |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-08-28 : 17:47:32
|
| Why not use mirroring, and just switch roles and break the miroring session when you are ready to migrate?-Chad |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2012-08-28 : 18:03:54
|
| I should use mirroring even if the two servers are separated by 800 miles and 25 ms of latency? |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-08-28 : 18:05:51
|
| Yep!-Chad |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2012-08-28 : 18:15:58
|
| This page says I can run mirroring in high-performance mode, which I would do because I can't afford to let transactions get any slower.http://msdn.microsoft.com/en-us/library/ms189852(v=sql.105).aspxBut it says there could be data loss, which I don't understand. Would there only be data loss if the principal went down and the mirror didn't receive the last transactions? |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-08-28 : 18:19:16
|
| You should run it in high performance mode until you are ready to officially switch roles. Then switch to Sycronous Mode, and switch the roles.The reference to data loss is because the Principal commits without receiving acknowledgement from the Mirror, so if the principal fails, there is no guarantee that the mirror is up to date. This is why you have to switch to syncrnous mode before you switch roles, so you make sure they are both in sync.-Chad |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2012-08-28 : 18:19:56
|
| Looks like Brian Egler recommends log shipping in the case of WAN's.http://www.networkworld.com/community/node/56351 |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2012-08-28 : 18:20:38
|
| I do have Enterprise. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
log shipping cutover and missed transactions