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


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?
Once the primary server comes back online, is it a difficult process to switch back to the primary database server?
Will changing the Recovery model to Full (from simple) have any issues?
Will my existing backups (using Symantec Backup Exec) be affected by enabling log shipping and switching to a full recovery model?
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 :




  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.