can any one suggest the best way of database sync as i have two servers one is primary and the other one is secondary i would require the database sync and both db's can be accessible already i have checked few once where i am not able to get complete database in snapshot method . and in transcation log method am unable to access the database
SQL Server Mirroring or Availability Groups
Which version of SQL Server are you using? Which edition? Is it Enterprise? Are the servers clustered or standalone?
Thanks in advance i am using sql 2014 and its not in cluster mode and both are at different locations and i have network connectivity as i had made as for backup server
Which edition of SQL Server 2014?
it is sql Enterprise edition and there is no cluster in it
I would recommend Availability Groups then. Depending on the specific details, the second server could be setup as a synchronous commit replica. But that would need to be tested. For geographically dispersed groups, often times the other site is using asynchronous replicas.
probably async unless you can guarantee a very low latency between sites
Yes probably async, but sync can be used if tolerated. We mostly have async replicas at the DR site, but we do have 2-3 sync replicas there. And the DR site is like 300 miles away. It's all about the tolerance level for that specific application.
But for the most part, the sync replicas are all at the same site and async replicas are at the DR site.