Data Sync between One Server to another Server

Question

I would like know what would be the best practice to do Sync data between Prd Sql server to another server(Reportal server)?

Currently, I'm taking DB backup and restore from prod to another server(Reportal server) every morning.

I was thinking to do Replication but it doesn't support if primary key isn't there in any of the table.

Is there another way to do Sync automatically instead setup SQL job?

Thank you

Answer 1

Hey,

As mentioned, there are multiple technologies, which can be used to replicate DB between multiple servers. Should help: https://learn.microsoft.com/en-us/sql/relational-databases/replication/types-of-replication?view=sql-server-ver16

Always On AG is the most powerful solution, which ensures high-availability and disaster-recovery of the databases. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server?view=sql-server-ver16

You can also configure SQL FCI using replicated storage. StarWind VSAN can be used in this scenario.
https://www.starwindsoftware.com/resource-library/starwind-virtual-san-installing-and-configuring-sql-server-2019-tp-failover-cluster-instance-on-windows-server-2016/

Cheers,

Alex Bykovskyi

StarWind Software

Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

Answer 2

Hi，DubHar

The following methods provide data replication without relying on the primary key in the table:

Snapshot Replication：Simple and practical, but suitable for databases with a small amount of data.

Merge Replication：You can use a row identifier (ROWGUID) as a substitute for the primary key.

Note: Replication is only used to achieve data synchronization. It synchronizes data in tables but does not synchronize other database objects such as database users, stored procedures, indexes, constraints, foreign keys, triggers, etc.

Log Shipping：Highly flexible, asynchronous transfer, and does not significantly impact the performance of the primary data.

Database Mirroring：Database mirroring is also based on transaction log replication. It offers high data synchronization, requires the database to be in full backup mode, and consumes the performance of the primary database. The contents of the replicated database cannot be directly viewed (database snapshots need to be created).

Always On Availability Groups： Very powerful, balancing data continuity and disaster recovery, but at a higher cost.

Data Transfer: Through a series of data transfer and synchronization projects, high-availability databases are realized. Similar technologies include SSIS, which certainly relies on your SQL job.

Recommendation: Choose from Log Shipping or Snapshot Replication. Snapshot Replication is recommended for its simplicity and reliability, but its limitations should also be considered.

Regards

Mikey Qiao

---

If you're satisfied with the answer, don't forget to "Accept it," as this will help others who have similar questions to yours.

Answer 3

I would like know what would be the best practice to do Sync data between Prd Sql server to another server(Reportal server)?

As they say, it depends. There are many ways to skin the cat, and what is the best depends on the local circumstances and requirements. Here is a list of options:

• Do as you do, copy a database every morning. Simple-minded, but if users are happy, why make it more complex. If reports benefit from indexes not in the OLTP database, you can add them as part of the RESTORE operation.
• Log shipping. By applying logs throughout the day, the report database can be more up to date. But every time you apply logs, users needs to be kicked out. You cannot add extra indexes.
• Database snapshot. This is the fastest way to provide the report database, since taking the snapshot is instantaneous. But the snapshot is on the same machine as the source database, so report queries will compete with the source system for CPU and memory. You cannot add extra indexes.
• Transactional replication. This permits you get a copy which is a near-realtime copy of the source database and you can add indexes supporting the report queries. If you are not a good boy and have PKs on tables that can be a bummer. Overall, Replication is complex to set up and maintain.
• Availability Groups with a readonly secondary. This gives you a report database which is even closer to a real-time copy of the source database. Also complex to set up and maintain, but maybe less than Replication. You cannot have local indexing. Requires Enterprise Edition, so if you have Standard Edition today, it's not a cheap solution.
• Some sort of home brew, built in triggers, Service Broker, Agent jobs, you name it. I can only see in point in engaging in something like this if you only want to replicate a subset of the data. (Of the techniques above, Replication is the only one that supports this approach.)