Replication and always on

  • Admingod

    SSCertifiable

    Points: 5871

    Previously we used to use transactional replication and that would allow reporting database to be different name. However, with always on AG the database name on secondary cannot be different has to match the source db name and i tested this to ensure. Do you agree the db name cannot be different for secondary replica?

  • John Mitchell-245523

    SSC Guru

    Points: 148761

    I'd be very surprised if that weren't the case.  The idea of having a database in an availability group is so that it looks the same wherever it's running.  If the name is different, then the database won't look the same and you'd have to reconfigure your application every time the AG failed over.

    John

  • BrainDonor

    SSCoach

    Points: 19228

    You can't have the same database with different names across an AG. When you add a database to an AG, there are no options to cater for it having a different name across the AG - it is always the same name.

    Steve Hall
    Linkedin
    Blog Site

  • as1981

    SSCrazy

    Points: 2733

    Can I ask what you are trying to achieve by having a separate name? You maybe able to achieve your objective by using a listener and read-intent.

  • Admingod

    SSCertifiable

    Points: 5871

    Thanks! On a side note, I configured AG for reporting from Primary server. So the reporting is the secondary replica which is a different instance. I would like to create a different database on the secondary replica for inserts and updates from different source. I don't anticipate any issues. Do you know if there would be any problems?

  • as1981

    SSCrazy

    Points: 2733

    Unfortunately I can't give you a 'yes' or 'no' answer to the question of the separate database because it depends on too many surrounding items including, but not limited to, the capacity of your systems and business requirements.

    Two things that I can suggest you need to think about:

    a) The connections or network bandwidth use of the separate database could impact on the data being sent from the primary to the secondary instance. I'm not saying it will, it is just a possibility to consider.

    b) If the availability group 'fails over' so that the current secondary is the new primary then the users of both databases will be connecting to the one server. Can it cope with that load?

    There might be other things, these are just the two I thought of.

    I would suggest you make sure that you monitor the health of the availability group including the data replication status.

    Also is there any connection between the database in the availability group and the separate database? If there is then how is that going to work if a failover of the availability group occurs.

    Another point, and I apologise if I'm stating the obvious, make sure that whatever configuration you use that your backups and restores work in any situation. As an example if you have a failover of your availability group then do the backups still work OK and can you still restore the data. Availability groups increase system availability but they don't protect you from data deletion, viruses e.t.c.

    Are you using SQL server Enterprise or Standard?

    If you have any further questions then something that might be worth you doing is providing a listing of your setup. I am thinking of something like this:

    Servers:

    Server A - SQL Version 2017 Enterprise

    Server B - SQL Version 2017 Enterprise

    Databases:

    Database A - Basic availabililty group on servers A and B

    Database B - Basic availability group on servers A and B

    Database C - Only on server A

    Listeners:

    Listener A - For database A

    Listener B- For database B

     

    • This reply was modified 1 week ago by  as1981. Reason: "Read-intent configured" removed for listener A in the example layout because I used basic availability groups in the example and they don't support read-intent
  • Admingod

    SSCertifiable

    Points: 5871

    Thanks. I agree with you. All servers are enterprise edition.

    Server A - Primary

    Server B - Secondary (for reporting) - asynchronous

    Server C - Secondary (DR) - asynchronous

    Databases:

    Database A - Basic availabililty group on servers A,B and C

    Database C - Only on server B(I would like to create new database on server B only which is not part of AG)

    Listeners:

    Listener A - All servers are part of Listener

    The instance would fail over between Servers A & C, manual fail over to node C - and never fail over to Server B. For Server B - it does not have to have the same number of CPUs or memory but it is Enterprise Edition this where I would like to create a new database  secondary replica(Server B) for inserts and updates from different source which would be not part of AG.

    By adding Server B for reporting - I introduce added complexity. But I am trying to offload users from primary replica so it can be used for reporting and other data warehouse activities to extract data or performing large queries.

     

  • as1981

    SSCrazy

    Points: 2733

    Your system is more complicated than the ones that I've used so I can't share any experience with you.

    However I did notice one thing - You said that database A is part of a basic availability group on three servers. I think you must be using a normal availability group? I think a basic availability group can only use two servers? There is more information here https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/basic-availability-groups-always-on-availability-groups?view=sql-server-ver15.

     

  • Admingod

    SSCertifiable

    Points: 5871

    Thank you! Trying to clarify about normal availability group, does it mean AG without Windows server fail over cluster? Please correct me if I am wrong? Do you see any issues and concerns with my setup 1 primary and 2 secondary replica’s? It would be good to know if there are any?

    Let me rephrase and see if this make any sense to you? Sorry if there is any confusion. Like I said, The plan is to create new database called ‘Database C’  it will be created only on server B(Secondary Server) not on Server A(primary Server).

    So Grab data from another source system and dump it into ‘Database C’ which will be created on Server B then grab all the needed information out from 'Database B'(Part of AG group) and 'database C'. So my concern is do you see any issues hosting 'Database C' on the Server B only, so other than what you listed about latency between primary to secondary and load issue?

  • as1981

    SSCrazy

    Points: 2733

    Admingod wrote:

    Thank you! Trying to clarify about normal availability group, does it mean AG without Windows server fail over cluster? Please correct me if I am wrong?

    My understanding is that a Basic Availability Group only allows two servers and you are using three for your Database A so I don't think you are using a Basic Availability Group for Database A. It's possible I'm wrong.

    Admingod wrote:

    Do you see any issues and concerns with my setup 1 primary and 2 secondary replica’s? It would be good to know if there are any?

    I have not used that setup so I can't provide any experience.

    Admingod wrote:

    Let me rephrase and see if this make any sense to you? Sorry if there is any confusion. Like I said, The plan is to create new database called ‘Database C’  it will be created only on server B(Secondary Server) not on Server A(primary Server).

    So Grab data from another source system and dump it into ‘Database C’ which will be created on Server B then grab all the needed information out from 'Database B'(Part of AG group) and 'database C'. So my concern is do you see any issues hosting 'Database C' on the Server B only, so other than what you listed about latency between primary to secondary and load issue?

    I understood your question. I noticed that you are using asyncronous communication. My experience is with syncronous. With async communication latency and load might not be an issue for you. Sorry I can't help you with this because I don't have the experience. There is some information here https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups?view=sql-server-ver15 on the differences.

    I think you know that if Server B is not running you will not be able to use Database C. That could be OK in your setup. I am not saying it's wrong. I am only making sure you know that situation.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply