SQL Server Clusterless Read Scale Availability Group Setup

Learn how to setup an SQL Server Clusterless Read Scale Availability group. Use the listener for client connections and see how to fail over the availability group. See the redirection in action for read only requests on the primary being redirected automatically to the secondary.

TSQL to return the read-only routing configuration

, AVGRL.routing_priority AS RoutingPriority
ORDER BY SourceReplica

[Bullbluegill]

Hey, I literally just saw this today, not sure why I wasn't notified. Thanks for putting up a video, I have yet to go thru but I will tomorrow

[mohammadedalatfard]

excellent video, thank you for your complete explanation.

[DeUraq]

Hi, that was a good vid. If i have 3 nodes setup where replica 1 & 2 in sync and replica 3 in async but dedicated for read-only then do i still need to create a new listener only for the 3rd replica or i must assume 1 or may be needed for read-only when 3 is not available. Basically i need to know if i should create multiple listeners.Thanks!

[RyazantsevNikolay]

On 18:42 you add a local server IP address as a listener IP. Shouldn't it be a separate IP instead? This IP certainly should not match with a cluster IP if it would be a cluster. Overall, this IP is VIP (virtual IP) but I'm not sure how it works in read-scale.

[Bullbluegill]

Ok, so my Sys and User database moves went fine on both servers. I setup AG on the two test servers, however I didn't use a listener or read-only routing. I also left the primary database setting for readable as NO. I'm not sure how the testing will go but it seems to return data i select on the secondary and DB's are "synchronized" with no data loss. My thought was I'll never fail over manually. Just the secondary needs to be readable in the AG. I also have no need for a listener or read-only routing.

Correct me if I'm wrong in my assumptions above. As long as the DB is replicating and reporting team can select the data they need on the secondary, that's all i need.

I set the primary as readable "no" because i never want the reporting team reading or selecting from that Prod db (primary). Thus the reason for this AG setup. I've yet to hand this over to the reporting team so I don't know if my settings are sufficient.

[bawoademinokan]

Hey man. YOur video are amazing. Can you do a video on Dynamic Data Masking? I would love to see your video on it as everyone out there i just talking and not teaching anything well.

[Bullbluegill]

I apologize in advance for this question as it's somewhat unrelated to this video for the most part. From my understanding, when going to AG you need to have the same file paths. One server was new and is 2019 and the old was 16 until i did an in-place upgrade. Correct me if I'm wrong, but I do not believe the upgrade gave me an option to set the path to the 15 MSSQLSERVER folder (old path was 13). If it did, i missed the ball there. I doubt it though because an installer isn't going to physically move the mdf and ldf (I was thinking out loud there)

I need to move the user DB's from the 13 to the 15 folder, I would also like to move the system DBs as well. So now my question is, is there an order in which i should move these? User DB's first, System first, Master First then the rest of system?? I can find a lot on the web about all of this except order in which it should be doner. So this is kind of an off the cuff question but maybe someone else down the line would have this same issue.

I figured i'd move the system as well simply for consistency moving forward.

[sqljobs]

Why not use a different Ip address for the Listener in RSAG other than primary to avoid changing DNS entries time and again after every failover . Does that not work ? sorry I have only set availability groups in FCI and not in RSAG.

[Bullbluegill]

Hello Sir, I'm back again with another question. I know when the DB is created it has the same name. Is there any way to change the secondary to a different name and still replicate properly? I know that doesn't make a ton of sense to have different names but it's how the report writers want it. for example, AdventureWorks (primary) and the secondary name AdvertureWorks_Prod?

[Bullbluegill]

Great video, well done! I do have a few questions though.

My objective is to create a read only duplicate on a different server for the report writers. I don't intend on doing any failovers ever. That said, when I select initial Data Synchronization, What should I choose? I will be backing the primary DB's up w a different tool on a hourly basis and do not care about the secondary obviously. I guess I'm not understanding all the options. I did restore a backup on my primary server and they are ready to roll. I'm into the wizard and on the "Select initial Data Sync" screen. I just don't know what the best option is for me. All I care about is that as data written/updated/deleted, it synchronizes the data on the replica.

I have added a listener, but I won't be using it I suppose considering i'm not doing failovers. Correct me if i'm wrong please.

[parvavlogs]

OdinTree, could you please provide me database schema, i want to practice thnks

[poem-saxophoneTV]

Believe in Jesus, you and your family will be saved.