data replication from Always on groups


  • Hi In my environment has a requirement to replicate database to BIA env from  database running on always on availability group env. Database expect to sync near real time.

    What is best replication technology to use achieve this  . I understand transactional replication would be better good. But I  have few issues on this .When Always on availability group when fail over /fail back between servers , how the Transactional replication is working. Is this works if we configure transactional replication thorough "Group Listener"? I never know how its working is this setup. Very sensitive and sync breaks easily ??

    Secondly mirroring. As per my understanding Mirroring need same version level to mirror DBs. But When not sure how its working when   fail over /fail back between servers

    Any one can advice would be highly appreciated.


    Monday, November 19, 2018 1:34 AM

All replies

  • Hello Ashwan,

    Could you provide more details?

    1) What version and build of SQL Server you are using in your source and BIA environment.

    2) Provide the requirement as why you need to replicate data to BIA environment from you source? Is that for disaster recovery? Reporting on BIA? 

    3) Are you planning to replicate all user databases from Source to BIA? Do you need all tables within the user databases to be replicated to BIA?

    4) If this is for reporting on BIA. Will you just run select queries on BIA or do you run insert,delete and update on BIA? Are you planning to implement anything else on BIA like cdc, change tracking etc...

    5) What is the size of user databases that needs to be replicated to BIA? Do you know how much changes that these databases will have everyday?

    Vivek Janakiraman

    Monday, November 19, 2018 1:52 AM
  • Hi Vivek

    1. BIA environment - sql Server 2016 SP2 Cu1 (EE)

    Awayson Env : SQL Server 2016 SP2 Cu3 (EE) 

    2. BIA need pull data to their env from Production. Runing SSIs packages may have latency and jobs  delay +network as well. They need to have data through replication on same BIA env to run report quicker .

    Alternatively What you your propose solution ?

    3: Not required production users sitting on BIA. But need data one to one identical . Complete databases(two databases)

    4.No BIA only read only.

    5. 500GB . Around .I sm not sure how to mesure "how much changes that these databases will have everyday?"


    Monday, November 19, 2018 3:19 AM
  • Hello Ashwan,

    With the above details. I would test Alwayson Availability group with Async mode with a listener if thats acceptable . You can utilize the secondary replica for read workloads. Application connection string will use the listener. It should contain a readintent switch to separate workloads.

    But you will be paying license for both primary and secondary replica.

    -> I wouldn't recommend on a 500 GB database. There may be latency depending on the workload. Also you will see huge latency during weekend maintenance. Reinitialize using snapshot or backup will take time. Will require a separate connection string for your application.

    -> I will not think about this (Atleast me) for your scenario.

    -> Not recommended. You can configure a readonly destination and use your application to connect to it. Sessions may terminate during restore. Will require a separate connection string for your application.

    These are just some pointers. You should think which one will be good for your environment taking into account the features and cost.

    Vivek Janakiraman

    Monday, November 19, 2018 3:39 AM
  • 1) Yes, AG works fine with transactional replication. A database participating in AG can be replicated using transactional replication. Another good news is, Distributor database can also be part of AG (which was initially a 2017-only feature but they backported it to SQL 2016 SP2 CU3). 

    The following detailed article shows how you can set that up:

    2) Alternatively, if it's OK to make BIA part of the AG setup such that it'd be on one of the secondary replicas then users can directly use and query a readable copy of the database and there won't be any need of transactional replication. 

    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Monday, November 19, 2018 4:15 AM
  • Hi Mohsin Thank you for the reply .

    On option 1, do you think its works for large tables like over 22M records? Interesting. Will start reading  

    On option 2. If we join BIA env as a AG group, then we must maintain same sql server version and patch levels as well windows server same versions. Don't you think its may get more complicated mix up environments. I understand technically should do as long as same SQLversion. But I am preferred to systems should be independend if possible to reduce dependency.

    Thank you

    Tuesday, November 20, 2018 12:30 PM
  • Thank you Vivek Good information. In this situation SQL server not good to setup replication solution when the DB is bigger?
    Tuesday, November 20, 2018 12:32 PM
  • Hello Ashwan,

    I think you should opt for Alwayson for your requirement. Replication is a great feature, but sometimes will not be a good fit depending on the scenario. For a database of size 500 GB with all tables required. I think replication will comparably have latency issues and more during database maintenance. It is best if you can test these to come to a more detailed conclusion.

    Vivek Janakiraman

    Tuesday, November 20, 2018 10:53 PM