Forum Discussion
Synchronize SQL database between two servers
Depends on what you mean with "Synchronize".
If you require a (1) replica for High Availability and/or a (1) replica for Disaster Recovery, their license is included with the primary replica, assuming you have "Software Assurance" or a subscription, and your replicas are "passive". See licencing guide available from SQL Server 2022—Pricing | Microsoft.
A passive SQL Server replica is one that is not serving SQL Server data to clients or running active SQL
Server workloads. The passive failover instances can run on a separate server. These may only be used to
synchronize with the primary server and perform the following maintenance-related operations for the
permitted passive fail-over Instances:
• Database consistency checks
• Log Back-ups
• Full Back-ups
• Monitoring resource usage data
Customer may also run primary and the corresponding disaster recovery replicas simultaneously for brief
periods of disaster recovery testing every 90 days.
Note:
• High Availability replica is defined as a passive replica setup as synchronous replica with automated failover.
• Disaster Recovery replica is defined as a passive replica setup as asynchronous replica with manual failover.
If you require read access to your replica, then you need to use "Always on Availability groups", all replicas have to be Enterprise, and the readable replica does not count as passive so requires full licensing.
This set of articles provide a comprehensive guide on most things you'd require to set up and configure always on availability groups, including VMs, Active Directory and SQL Server itself.
A comprehensive guide to SQL Server Always On Availability Groups on Windows Server 2016
It assumes you're setting up a test environment, so the first article is how to set up a dev environment. I recommend browsing the Table of Contents for the articles that you need.
This article goes into Read only replicas
Make the most of secondary replicas in SQL Server Always On Availability Groups
I highly recommend looking into the new (as of SQL Server 2022) "Contained Availability Group" (CAG), as they solve some of weaknesses Availability Groups that are less obvious to newcomers, by also replicating SQL Logins and SQL Server agent jobs created within the CAG.
What Is a Contained Availability Group? - SQL Server Always On | Microsoft Learn
Thank you.
Sometimes it's hard to ask the right question on the first try (from many aspects of the complexity of the topic and demands changed in the time and base on knowledge in the topic).
Try to describe prerequisities in more detail by your message:
How to synchronize SQL database between 3 or 4 servers. All servers are in same domain, but in different locations.
Server1 with SQL (version 2022 Enterprise, 64bit) database XYZ is now only one, where work all users. Now are required to make copy and sync SQL database XYZ to other locations. Then users may move and work connected (depend on location) to SQL database XYZ to server1 or server2, ... , server4.
1. Is required to have SQL Enterprise in all servers, for to be able the synchronization?
Or will be enough to have SQL Enterprise in server1. And then only SQL Express, please?
2. Which requirements are required to fulfil or prepare for to be able synchronize SQL database between two servers, please?
3. Which possibilities (when are more possible solutions) have existed for synchronize SQL database (totally awaiting between four servers), please?
4. Exist a manual for reading an synchronization of SQL server - settings and steps?
5. Any other notes will be appreciated.
