1

I have a few questions regarding Log Shipping in SQL Server 2012.

  1. If the primary database server dies, is it possible to allow users to connect to the secondary database so as to continue working with Read/Write access?

  2. Once the primary server comes back online, is it a difficult process to switch back to the primary database server?

  3. Will changing the Recovery model to Full (from simple) have any issues?

  4. Will my existing backups (using Symantec Backup Exec) be affected by enabling log shipping and switching to a full recovery model?

2

1) If the primary database server dies, is it possible to allow users to connect to the secondary database so as to continue working with Read/Write access?

You have to manually failover to the secondary server as logshipping does not have a built-in mechanism to support automatic failover.

Once you failover, your application/s (provided they are using ADO.NET or SQL Native Client) can leverage the Failover Partner=Secondary_server

Brent has blogged about failover partner:

you don’t need to use database mirroring to use Failover Partner. Whether you’re using database mirroring, replication, log shipping, or duct tape, much like the honey badger, your applications don’t care. They’ll just try to connect to the Failover Partner name whenever the primary server is down.

.

2) Once the primary server comes back online, is it a difficult process to switch back to the primary database server?

Traditionally, depending on the size and number of database that are invloved in logshipping, it may or may not be difficult.

Database size and your network bandwidth matters a lot. Normally, you just have to resetup log-shipping. Meaning - point all your apps to primary and in background, do a full backup of primary, copy that to secondary server and restore it with no-recovery with subsequent log backups.

To minimize the downtime, you can use "Reverse logshipping" will prove to be a huge help.

3) Will changing the Recovery model to Full (from simple) have any issues?

Yes. Changing recovery model breaks the log chain. Paul Randall talks in his myths section. Also, see below chart :

enter image description here

4) Will my existing backups (using Symantec Backup Exec) be affected by enabling log shipping and switching to a full recovery model?

Once you setup logshipping, there is no need to take any additional log backups. In fact, any adhoc log backups will break the log chain. Use COPY_ONLY backups.

As I mentioned above, any change to the recover model should be followed by a FULL backup. That will be your base backup. Any adhoc backups (even full) should be taken with a COPY_ONLY option as if you are relying on differential backups then it will be an issue if someone takes an adhoc full backup.

As a side note, always fully document, test, test and test + automate (as much possible) your disaster recovery strategy. See Paul's DR Poster for more details.

  • One point is missed about users connecting to Secondary database: You must mention that users cannot immediately connect because of orphaned users scenario and unless they have build in script to take care of this even after when database comes online users would not be able to connect. You should also include how to resolve orphaned users that would complete your answer – Shanky Oct 14 '14 at 17:38
1

Kin provided some good details but just to address the questions directly:

3) Well, you can only log ship a database in full recovery model, so not sure how to answer that one.

1) If the primary database server dies, is it possible to allow users to connect to the secondary database so as to continue working with Read/Write access?

Yes, you just have to restore the secondary WITH RECOVERY.

2) Once the primary server comes back online, is it a difficult process to switch back to the primary database server?

No, you can just re-initialize the process from the new primary. It's:

  • a full backup;
  • start log backups (which you should be doing anyway, even without log shipping);
  • then find a server that's up to serve as the new secondary and set up similar jobs there to perform the restores.

3) Will changing the Recovery model to Full (from simple) have any issues?

Well, you can only log ship a database in full recovery model, so not sure how to answer.

4) Will my existing backups (using Symantec Backup Exec) be affected by enabling log shipping and switching to a full recovery model?

Hard to answer without knowing exactly what you are using Symantec for. As Kin noted, you don't want this thing to try to take log backups outside of your log shipping mechanism.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.