HA and DR strategy: REPLICATION , LOG-SHIPPING or BOTH
Hello Experts, I need a advise on one of our projects where: We have 3 SQL database servers A, B and C: A is the live production server with around 200 users with a database of size 1.5 TB hosted. B is going to be used for reporting purpose only so we would implement transactional replication on this server B. C is just going to be used for DR purpose, in case A goes OUT we can use c to recover. Clustering is not proposed by client so that is out of question. All above servers are SQL sever 2014 enterprise edition: So my question is: 1) Should i go with option of setting replication on this server C as well, that is one PUBLISHER A and two subscribers B and C. If yes, what points should be taken in consideration or i need to take care of before moving ahead with this? 2) We have to anyhow set up replication on server B, but for server C if point 1 cannot be achieved can we implement log shipping , that means a same database on server A can be used both for replication and Log shipping both. is that possible, then we are looking to implement log shipping from server A to C for every 15 minutes. Note: The DB on server A is 24*6 usage. (Sunday off) Please advise, thanks!
If your client doesn't want clustering or any other HA solution, I'd suggest creating a hot standby on C just using log shipping. Remember that it has the disadvantage that if the primary on A fails, automatic failover doesn't happen. you have to promote C manually and reconfigure the clients to connect to it. You would also need to reconfigure the replication, of course!