question

MAXKA avatar image
MAXKA asked

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!
sql-serverreplicationlog-shipping
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
When the client rejects clustering, does that include availability groups? What you propose is the canonical example for availability groups.
0 Likes 0 ·

1 Answer

·
Phil Factor avatar image
Phil Factor answered
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!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.