Is there anything that is similar to Oracle Real time Replication in SQL Server. RRS feed

  • Question

  • I would like to replicate database in real time  to another database. Is there anything available in SQL Server. A link to the information is helpful.

    Also what is the fault tolerance if we use a SQL AG between the active node and the replica node?  Sometime the replica can be out of synch from the primary database?


    • Edited by kimdav111 Monday, June 3, 2019 6:16 PM
    Monday, June 3, 2019 6:12 PM

All replies

  • Monday, June 3, 2019 8:55 PM
  • It depends on how you define "Real Time".

    Zero window in time from when a change occurred until it is reflected on the other node? Then you need to use a synchronous "mirroring/replication" tech, and those are Database Mirroring (deprecated) or Availability Groups. 

    If you can accept some window in time, then you have other options. Like async Db Mirroring/AG, Transactional Replication, Snapshot Replication, Merge Replication and Log shipping. The Replication technologies aren't primarily for HA/DR (IMO) so make sure you get to know them well before you use them for such. That, of course, go for any of the technology you decide to use!

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, June 4, 2019 5:28 AM
  • The reason for this question is that we have a need that the current SQL design does not meet.   With a 12 TB  SQL database in a mirrored to another SQL database (for HA because we do not have a SAN for a SQL cluster which is going to be depreciated in the future anyways), whenever there is congestion of data feeds to this SQL data warehouse this mirror breaks hence reestablishing this mirror is difficult due to the size of the databases.  This is why the question of latency is asked if the primary SQL database goes offline.  How much SQL data can be cached on the primary node without breaking this asynch replication in SQL AG?,... what is the tolerance?  How resilient in the SQL AG?   Or would we have to recreate this SQL AG from scratch?

    If the SQL AG breaks for some reason while this 12 TB database and we have to remove from the AG is the primary node still active?   Just want to confirm that we would have to a full backup on the primary node before adding to the AG (during this process we would resynch the database on the secondary replica ( we would delete the database on the secondary node before resynching to the secondary replica) ?  

    the database still stay live (can it still be in use while the database on the primary node is being back up )


    Tuesday, June 4, 2019 12:35 PM
  • This is more appropriate for the HA forum than the Data Warehousing forum.  Can a moderator please move this.

    Tuesday, June 4, 2019 12:45 PM