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!

  • 3
    You are over complicating your situation. You should really find out from the client why clustering is out of question ? Your scenario is perfect for Always-ON availability group. Normally when you dont make proper arguments and provide valid reasons, people will tend to not use the technology that they are not comfortable with. – Kin Shah May 7 '15 at 18:53
  • Sounds like a nice 3 server always on cluster, you are using Enterprise so why not? – Richard Brown May 7 '15 at 19:29
  • I'm with @Kin. If the client's worry is about licensing, as long as the secondary is kept as a passive node, which should be the case of the DR, it is ok (note: a readable replica is an active node). So there is only the need for the A and B servers licensing. – JoseTeixeira May 8 '15 at 10:38
  • @Kin: We had a call and proposed them the proper advantages we can have for Always ON, but as per their norms, they don't have that budget and time for the set up of clustering the 3 servers, instead what seems to be a solution here is A to B (same network) transactional replication for reporting and A to C for (database mirroring as HA) on different network for the same DB on server A. Would there be any challenge? – KASQLDBA May 11 '15 at 16:22
  • Will there be any schema changes often ? – Kin Shah May 11 '15 at 16:32

Your Answer

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

Browse other questions tagged or ask your own question.