SQL Server 2019 - cloud redundancy

• rogelio.vidaurri

  SSCrazy

  Points: 2117

  More actions

  January 6, 2023 at 5:32 pm

  #4134623

  We have two SQL server instances. Server A is running on Azure, and Server B is on AWS. Both servers are synchronized via SQL Replication, a bidirectional transactional replication to be accurate.

  Our website runs on Server A only, and in case there are issues on Azure we redirect our website to Server B.

  Are there any options for SQL Server, similar to Atlas MongoDB, to rely on a cloud provider to manage your SQL Server instances? We would like to have both servers A and B active (like a load balancer), and up and running but every time we try the replication breaks. It throws a "violation of primary key constraint..." error.

   

• Site Owners

  SSC Guru

  Points: 80330

  More actions

  January 7, 2023 at 6:10 pm

  #4134957

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.

• cyrusbaratt

  SSC-Addicted

  Points: 417

  More actions

  January 9, 2023 at 6:38 am

  #4135300

  You may use the following options:

  + A SQL server cluster with "both nodes active" or "One node active and once node inactive".

  No replication is required. The storage is shared between the two cluster nodes.

  + A windows server cluster with "One node active and once node passive".

  Please review the notes from Anton Kolomyeytsev in this forum:

  https://social.technet.microsoft.com/Forums/Lync/en-US/2f6385ed-8467-488a-a292-6be48b2442f2/2-nodes-activeactive-clustering-with-vdi-deployment?forum=winserverClustering

  The replication errors is described here: (good technical information always.)

  https://www.mssqltips.com/sqlservertip/4509/addressing-sql-server-replication-violation-of-primary-key-errors/

  Best

  DBASupport

• rogelio.vidaurri

  SSCrazy

  Points: 2117

  More actions

  January 10, 2023 at 10:52 pm

  #4136137

  where can I find documentation for this?

  + A SQL server cluster with "both nodes active" or "One node active and once node inactive".

  if both nodes are active on different SQL servers, what about seeding and latency to the storage? tons of questions

  thanks

• cyrusbaratt

  SSC-Addicted

  Points: 417

  More actions

  January 14, 2023 at 7:32 am

  #4137692

  You cannot go wrong with the Web site: https://www.mssqltips.com/

  https://www.mssqltips.com/sqlservertip/6586/clustering-sql-server-2019-step-by-step/

  Here is something for :  seeding and latency to the storage

  https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/automatic-seeding-secondary-replicas?view=sql-server-ver16

  You need to be concerned with capacity management which could include but not limited to :

  (I like this article for sharedpoint systems, it is useful in other platforms.)

  + Network capacity:

  • Clusters nodes being on the same subnet to reduce latency.
  • Good performance NIC
  • Dedicated ports (fiber optics/others)
  • Memory/CPU/Disk I/O/Network performance

    • A good combination of these features.

  • F5 load balancer.
  • Distributed processing using proper network topology.

    • Good balanced network topology.

  + Storage performance (consider raid 10 for good performance)

  • High capacity SAN / Raid 10 / others (using LUNS)
  • Good performance.
  • As always things work best when database is properly designed with good indexes.
  • Good maintenance plans.

  DBASupport

• cyrusbaratt

  SSC-Addicted

  Points: 417

  More actions

  January 14, 2023 at 7:36 am

  #4137693

  You may consider taking SQL server cluster setups to learn more about SQL server cluster quorum (shared storage):

  https://learn.microsoft.com/en-us/windows-server/failover-clustering/manage-cluster-quorum

  DBASupport

• cyrusbaratt

  SSC-Addicted

  Points: 417

  More actions

  January 14, 2023 at 7:37 am

  #4137694

  Apologies, I meant taking online classes

  .

  DBASupport