June 29, 2020 at 11:43 pm
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?
June 30, 2020 at 8:42 am
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
June 30, 2020 at 10:24 am
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.
July 2, 2020 at 11:42 pm
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?
July 3, 2020 at 12:28 pm
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
July 3, 2020 at 9:29 pm
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.
July 5, 2020 at 8:53 pm
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.
July 6, 2020 at 11:21 pm
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?
July 7, 2020 at 12:40 pm
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.
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.
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply