1

We want to set up a second instance/server of SQL Server such that both instances will be kept in sync. The reason is we need to separate some users and processes out to a separate server for business reasons.

More information (requested in comments):

  • Neither instance would be read-only
  • It does not need to be synced in realtime, but that would be nice
  • We have full control of the table schemas

We've done a lot of research, but most potential options are part of disaster recovery features, so we're not sure if those are right for this.

I'm not sure how to whittle down this list without actually trying the different options, but that would be very time consuming. If there's an option I'm missing, that would be very helpful.

Always On Availability Groups
Merge Replication
Database Mirroring
Synchronize Data
Windows Server Failover Clustering with SQL Server (Is this what is normally meant by clustering?)

Improve this question
17
  • No, there is nothing that will do this based on the load. Newer availability groups will round robin with multiple replicas, but round robin does not take into account load. dba.stackexchange.com/questions/171438/…
    – Tony Hinkle
    Commented Apr 23, 2020 at 18:03
  • Round robin may good enough. You like Always On Availability Groups as a solution for this?
    – user3071284
    Commented Apr 23, 2020 at 18:08
  • The term you're looking for is "load balancer".
    – mustaccio
    Commented Apr 23, 2020 at 18:13
  • 1
    If you have the technical expertise to handle clusters and availability groups, then yes, I like it. If you don't have anyone that's ever used clusters, and nobody that's touched availability groups, then maybe not. If you use an alias for readers to connect to, you could use DNS to round robin connections, but not queries, using replication. You can't read from a mirroring secondary, and mirroring is being deprecated, so wouldn't advise that.
    – Tony Hinkle
    Commented Apr 23, 2020 at 18:44
  • 1
    Why do you need to seperate some users and processes? What edition of SQL Server do you use? Is the second copy read-only? Does it need to be updated in near-realtime? Can you modify the table schema in the database if needed? Can you instead use the workload isolation features of a single instance (RCSI,Resource Governor, Secondary Columnstore Indexes)?
    – David Browne - Microsoft
    Commented Apr 25, 2020 at 16:16

0

Reset to default