Hello Sir, can we setup two node like, node1 as primary (read/write) node2 as secondary standby (for high availability). and node3 for read replica (for application) with this approach in SQL server Standard Edition ?
Hi In SQL Server Standard Edition, you can set up high availability and read replicas, but there are limitations compared to SQL Server Enterprise Edition. Here's how you can achieve your desired setup with SQL Server Standard Edition: Node 1 as Primary (Read/Write): Node 1 can be your primary database server that handles both read and write operations. Node 2 as Secondary Standby (High Availability): SQL Server Standard Edition supports basic high availability features like database mirroring and AlwaysOn Availability Groups, but there are some limitations compared to Enterprise Edition. Keep in mind that Standard Edition has limitations on the number of secondary replicas and features like automatic failover. Node 3 for Read Replica: SQL Server Standard Edition does not support readable secondary replicas in Availability Groups. I would like to recommend to do POC and test all scenarios and come to one conclusion. Thanks & Regards MS SQL DBA TECH SUPPORT
Hi, Thanks for quick replying. I just wanted to know that, Node 1 as Primary (Read/Write): Node 1 can be your primary database server that handles both read and write operations. Node 2 as Secondary Standby (High Availability): SQL Server Standard Edition using basic high availability features is working fine. only concern is Node3; So for Node 3 (read replica using transactional replication (publisher Node1 & Node2) ), can we make distributor & subscriber itself and use "sp_redirect_publisher" method ? if you can answer this will helpful to me.
Hi, Yes.you can set up a separate, standalone database instance on Node 3 and periodically replicate data from Node 1 (the primary) to Node 3 using methods like SQL Server Transactional Replication or log shipping. This way, you can achieve read scalability on Node 3. When fail over occurs node2 will act as primary and replication will also fine. i already done this kind of scenario and sharing here for your reference. ruclips.net/video/d9rFOniRWFQ/видео.htmlsi=VR3eXN9YWBHurwnJ Thanks & Regards MS SQL DBA TECH SUPPORT
@@MSSQLDBATechSupport how can we perform in workgroup environment, basically need to build database replication between 2 MSSQL Server please guide or share some docs
Let me clear... To configure only replication then no need to have domain environment. in your laptop only u can install the two instances and configure whatever replication type you want. if possible i will make a video on this.
How to move the replication jobs to secondary replica. After failover AG my replication is not working. I see all the replication jobs (Publisher) are in NODE1. there is no jobs in NODE2 (secondary). Could you please tell me how to move all the jobs to secondary replica.
In this video we showed the combination of always on with replication The publisher and its is also a primary replica... if fail over the distributor also connect the new primary replica and get the changes... Here we are telling the distributor who is your Publisher in fail over circumstances. Coming to Jobs especially i take Transaction replication No jobs will create in Publisher expect *Expired subscription clean up* job, snapshot & Log reader agent jobs will create on distributor server. *Expired subscription clean up* this job is not having much imp... If you want to you can script and create in new Primary replica... But in case fail over we only see business continuity... we cant run on new Primary server... like on weekends we will move back to old Primary Server. I have tested only with Transaction replication there are many things that needs to be explore With other Replication types like *Peer-To-Peer*, *Merge Replication*
This is a great video but I couldn't quite understand what exactly is the benefit of doing this or in other words what kind of situation could we possibly face in the real world in which this solution is suitable.
Hey there, Thank you for your kind words and for bringing up this important question! A few subscribers have asked about this scenario, and we just explored the possibilities. We have limitations on the number of replicas that we can add to Always On, and if you want to add an Always On database in this scenario, replication can help us to overcome this limitation to some extent. It also acts as an additional layer of availability and accessibility for the database. Thanks & Regards MS SQL DBA TECH SUPPORT
Excellent harsha the concept is really good👏
Thanks and explore the remaining videos as well.
Awesome very much thanks for sharing such a nice content 🎉
Thank you too
It was good session. Thank for the video.
Glad you enjoyed it!
Thanks for sharing sir 😊
Keep watching
Hello Sir,
can we setup two node like, node1 as primary (read/write) node2 as secondary standby (for high availability). and node3 for read replica (for application) with this approach in SQL server Standard Edition ?
Hi
In SQL Server Standard Edition, you can set up high availability and read replicas, but there are limitations compared to SQL Server Enterprise Edition. Here's how you can achieve your desired setup with SQL Server Standard Edition:
Node 1 as Primary (Read/Write):
Node 1 can be your primary database server that handles both read and write operations.
Node 2 as Secondary Standby (High Availability):
SQL Server Standard Edition supports basic high availability features like database mirroring and AlwaysOn Availability Groups, but there are some limitations compared to Enterprise Edition.
Keep in mind that Standard Edition has limitations on the number of secondary replicas and features like automatic failover.
Node 3 for Read Replica:
SQL Server Standard Edition does not support readable secondary replicas in Availability Groups.
I would like to recommend to do POC and test all scenarios and come to one conclusion.
Thanks & Regards
MS SQL DBA TECH SUPPORT
Hi, Thanks for quick replying.
I just wanted to know that,
Node 1 as Primary (Read/Write):
Node 1 can be your primary database server that handles both read and write operations.
Node 2 as Secondary Standby (High Availability):
SQL Server Standard Edition using basic high availability features is working fine.
only concern is Node3;
So for Node 3 (read replica using transactional replication (publisher Node1 & Node2) ), can we make distributor & subscriber itself and use "sp_redirect_publisher" method ?
if you can answer this will helpful to me.
Hi,
Yes.you can set up a separate, standalone database instance on Node 3 and periodically replicate data from Node 1 (the primary) to Node 3 using methods like SQL Server Transactional Replication or log shipping. This way, you can achieve read scalability on Node 3.
When fail over occurs node2 will act as primary and replication will also fine.
i already done this kind of scenario and sharing here for your reference.
ruclips.net/video/d9rFOniRWFQ/видео.htmlsi=VR3eXN9YWBHurwnJ
Thanks & Regards
MS SQL DBA TECH SUPPORT
Thanks a lot sir
Welcome
you have perform it in Domain Environment or Workgroup Environment
I have build a domain lab in vmware workstation.
@@MSSQLDBATechSupport how can we perform in workgroup environment, basically need to build database replication between 2 MSSQL Server please guide or share some docs
Let me clear... To configure only replication then no need to have domain environment. in your laptop only u can install the two instances and configure whatever replication type you want. if possible i will make a video on this.
@@MSSQLDBATechSupport Sure PLease make a video it will help a lot Brother
Once i get free i will do that
How to move the replication jobs to secondary replica. After failover AG my replication is not working. I see all the replication jobs (Publisher) are in NODE1. there is no jobs in NODE2 (secondary). Could you please tell me how to move all the jobs to secondary replica.
In this video we showed the combination of always on with replication
The publisher and its is also a primary replica... if fail over the distributor also connect the new primary replica and get the changes... Here we are telling the distributor who is your Publisher in fail over circumstances.
Coming to Jobs especially i take Transaction replication No jobs will create in Publisher expect *Expired subscription clean up* job, snapshot & Log reader agent jobs will create on distributor server.
*Expired subscription clean up* this job is not having much imp... If you want to you can script and create in new Primary replica...
But in case fail over we only see business continuity... we cant run on new Primary server... like on weekends we will move back to old Primary Server.
I have tested only with Transaction replication there are many things that needs to be explore With other Replication types like *Peer-To-Peer*, *Merge Replication*
thanks nice video, can we put the same name of the database on publisher and subscriber server?
Yup
Thanks, tomorrow morning I'll try. If l will face any problem, then I'll put the message
Okay
Tq sir
Welcome & explore more other videos as well
This is a great video but I couldn't quite understand what exactly is the benefit of doing this or in other words what kind of situation could we possibly face in the real world in which this solution is suitable.
Hey there,
Thank you for your kind words and for bringing up this important question!
A few subscribers have asked about this scenario, and we just explored the possibilities.
We have limitations on the number of replicas that we can add to Always On, and if you want to add an Always On database in this scenario, replication can help us to overcome this limitation to some extent. It also acts as an additional layer of availability and accessibility for the database.
Thanks & Regards
MS SQL DBA TECH SUPPORT
@@MSSQLDBATechSupport awesome thank you.
Welcome