November 17, 2022 at 11:26 pm
Do I have to setup listener for always on availability group?
If not necessary, and I don't setup listener, what is the connection string from web application points to? does it just point to the cluster name be good enough?
Thanks,
November 18, 2022 at 9:51 am
Yes you need a listener so that SQL knows where to route the connections when the group fails over.
You can create that as a traditional VNN listener or as a newer DNN listener depending on your architecture, operating system, cloud vs on-prem etc.
November 18, 2022 at 4:27 pm
We work on premise. The cluster is two nodes, one node is at our office building which is main data center, the other node is in another location in our city.
In this case, do we need listener and do we need to change connection string to use listen?
Thanks
November 18, 2022 at 6:38 pm
Do you want automatic failover - or will you only ever manually failover to the secondary node? For automatic failover you must have a listener - for manual you can manage that outside of SQL.
If the secondary is set to manual failover and asynchronous (assuming this because it is in another location), you can define a DNS alias that is set to the primary nodes IP address. On failover, update the DNS alias to the secondary's IP address.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 18, 2022 at 7:49 pm
This is for disaster recovery only, so we can have some down time and do manual failover.
Yes it is in another location.
Currently we have the SQL server running standalone ServerA, in order to setup Alwayson Availalbity group, we want to set up a windows cluster and add the existing SQL server ServerA as node1, and create a new node -node 2 ServerB in another location and be part of the cluster. I hope we don't need to change all the connection strings from front end applications. Does that work? I understand adding listener we will have to change connection string. If we don't need to add the listener, is it easy to switch over manually to Node2? THanks
November 21, 2022 at 1:34 pm
IF it is for a DR activity, why don't you user Log shipping or Mirroring?- Just for clarification.
The same code changes will apply here too.
Regards
Durai Nagarajan
November 21, 2022 at 4:18 pm
This is for disaster recovery only, so we can have some down time and do manual failover.
Yes it is in another location.
Currently we have the SQL server running standalone ServerA, in order to setup Alwayson Availalbity group, we want to set up a windows cluster and add the existing SQL server ServerA as node1, and create a new node -node 2 ServerB in another location and be part of the cluster. I hope we don't need to change all the connection strings from front end applications. Does that work? I understand adding listener we will have to change connection string. If we don't need to add the listener, is it easy to switch over manually to Node2? THanks
in a DR situation you will need to update the connection string if you do not use a listener so that the apps connect to serverB instead?
so what is going to be more painful, changing the connection string now or changing it in a disaster scenario?
if it was me I would build new and migrate. Move your dbs into a new AOAG setup with a listener.
then create a DNS CNAME of name ServerA which points to the listener so then you don’t need to go change the applications either now or in a dr scenario
November 21, 2022 at 7:47 pm
IF it is for a DR activity, why don't you user Log shipping or Mirroring?- Just for clarification.
The same code changes will apply here too.
I know mirroring is deprecated.
For log shipping, it seems it is a relative less costly approach. I would like to try that too, but since we have 3 databases need to do the disaster recovery, I understand log shipping is one database need one setup, would 3 threads of transaction log shipping affect primary prod server performance? Also it seems for each log shipping need to create 4 sql jobs, which is not big deal. Another issue I concerned is log shipping does not allow transaction log backup in regular maintainance plan backup, it has its own process of backups. Disaster recovery i after all rarely happen, I don't want to risk in normal days, there is a transaction log missing for regular maintainance plan in case I want to recover primary production database instance.
Thanks,
November 21, 2022 at 8:58 pm
Regardless of whether you have an AOG listener, you can connect directly to either the primary or secondary using a connection string similar to that below. If connecting to the secondary, you will need to add "ApplicationIntent=ReadOnly".
"Data Source=$ServerName; Initial Catalog=$DatabaseName; Integrated Security=True; ApplicationIntent=ReadOnly"
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 21, 2022 at 11:06 pm
Regardless of whether you have an AOG listener, you can connect directly to either the primary or secondary using a connection string similar to that below. If connecting to the secondary, you will need to add "ApplicationIntent=ReadOnly".
Thank you much! so the $serverName is the nodes name not the cluster name, correct? Any where I can find documentation or articles regarding connection string for Alwayson Availabity group.
November 21, 2022 at 11:26 pm
then create a DNS CNAME of name ServerA which points to the listener so then you don’t need to go change the applications either now or in a dr scenario
Not sure I understand :DNS name ServerA which points to the listener?
Also is there any issue with creating a cluster and add existing serverA as one node, and create a new SQl instance and add as node 2 instead of migrating?
November 22, 2022 at 1:51 pm
So the issue you have is DNS / connection strings.
What do YOU want to happen in a DR situation?
When you invoke DR do you want to go and change all your application connection strings to now be ServerB?
When you invoke DR do you just want ServerB to think it is ServerA?
What level of responsibility do you want to put in yourself when the proverbial is well and truly hitting the fan?
November 22, 2022 at 5:49 pm
Regardless of whether you have an AOG listener, you can connect directly to either the primary or secondary using a connection string similar to that below. If connecting to the secondary, you will need to add "ApplicationIntent=ReadOnly".
"Data Source=$ServerName; Initial Catalog=$DatabaseName; Integrated Security=True; ApplicationIntent=ReadOnly"
You only need the ApplicationIntent=ReadOnly when using the listener to connect to the AG and the connection string specifies a database that is a member of that AG. When you use that - the listener will determine if there is a read-only secondary available and configured appropriately for read-intent access, and then redirects the connection to that secondary. If there isn't a secondary available - the connection will be directed to the primary.
You can connect directly to a secondary at any time - if that secondary is configured for read only access and your login is a user in that database then you can access that database with the permissions that have been granted to that user in that database. The application intent doesn't need to be specified here since you are not attempting to redirect a connection for read only operations.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 22, 2022 at 9:05 pm
Mirroring is still available in 2019.
Log backup has to be done in sequence , if you want the log backup files for primary server recovery too, take the folder to backups.
Hope this is clear.
Regards
Durai Nagarajan
November 22, 2022 at 9:09 pm
Mirroring is still available in 2019.
Log backup has to be done in sequence , if you want the log backup files for primary server recovery too, take the folder to backups.
Hope this is clear.
Thank you, what does it mean when you say take the folder to backups? Thanks
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply