June 5, 2019 at 9:57 am
Which one is the best solution to synchronize from one SQL Server to another SQL Server? such as replication?log shipping ?mirroring and etc, or maybe different solution applies different scenario?
June 5, 2019 at 10:22 am
Each solution has it's advantages.
I personally use transactional replication as it's usually only 3 or 4 seconds latency and both copies of the data are queryable. Plus you can filter just the tables, colums and rows you need
log shipping is great for making a cold standby (each time you restore a log file the database is read only) - typically I use this for a server that runs reports at the weekend and we can scrap it afterwards.. but you have to take the entire database (which is why I like replication)
mirroring is good, but only on enterprise edition where you can use high performance mode (high safety has issues when your standby goes down... it takes your primary down too) - and you will have to create snapshots of your mirror to query it.
you could also use Redgate's SQL Data compare if it's a one off synchronisation
MVDBA
June 5, 2019 at 12:45 pm
You're missing Availability Groups and then all the third party solutions through software and hardware. Also not mentioned, just backing up the database on one server and restoring to another. To arrive at the "best" we have to define the requirements as thoroughly as possible. Then, suggestions can be made for a possible solution, with the understanding that each of the suggested solutions also has shortcomings and far-reaching issues that affect other aspects of what you do.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 12, 2019 at 2:47 am
Thanks MVDBA and Grant Fritchey for your time and good advice.
Very Sorry for no repond for a long time!
We have one ERP system, one SQL Server database is an OLTP server for user daily operation, such as insert?update?delete, the other SQL Server database is an OLAP server for user reporting server. the data of OLTP server will be synchronized into OLAP server and the 20 seconds of data latency can be acceptable. for this requirement, can we use transactional replication to do the synchronization ? if not ok, could you please tell me which solution best and how to do ? many thanks!
August 12, 2019 at 8:08 am
for 20 seconds latency I cannot see anything but transactional replication working....
Grant - feel free to give me another option as I'm out of ideas for a system to get data to a reporting server within 20 seconds. 🙂
If you do go down the route of Transactional replication, please look at the scripting.. sp_addsubscription, sp_addarticle etc . they are so much better than the User interface
MVDBA
August 12, 2019 at 9:09 am
Availability groups should, under most circumstances, operate within 20 seconds. It's that or transactional replication. No other choices at all.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 12, 2019 at 10:55 am
I tried this once using mirroring with snapshots... the report started the snapshot which worked really well.
except.... we had to run 2 16 core enterprise editions. (you couldn't run the high performance mirroing in standard and you can't create snapshots on a mirror in standard) - very expensive
I find a lot of my solutions now are based on our licencing costs 🙁
fortunately sql 2017 standard does support "basic availability groups"
MVDBA
August 13, 2019 at 1:13 am
Thanks Grant Fritchey and MVDBA!
if I use transactional replication or to fulfil this requirement (sync data from OLTP database into OLAP database), how about the latencey of them? thanks
August 13, 2019 at 9:44 am
Latency is a dependency of the volume and distance. There's not a hard and fast measure here. It depends on too many factors. However, standard Availability Groups, not hopping across the continent, but within a data center, usually has a very low latency, 5-10 seconds at the most. Again though, you can't reiterate this enough, there are dependencies on the hardware, the distance, disk configurations, etc., etc.
Can this be done? Yes. Can it be done with your hardware and setup? We don't know because we don't know what the situation is.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 13, 2019 at 11:10 am
I've found with Transactional replications I've measured our on premise database in England replicated to an azure database in Ireland at normally 6 seconds
1-2 seconds from publisher to distributor and then 4-5 from distributor to subscriber - be aware that these are measured using tracer tokens in replication monitor, I didn't conduct my own testing.
We do hit Issues when there is a bulk update of 1 million rows of data and our latency goes up to 6-7 minutes.. and recovering from an outage at the subscriber can take an hour if we have to re-snapshot and re-initialise
Grant is 100% right, there are too many unknowns...you have to give it a try and see, then you might find your bottleneck and change your solution.
MVDBA
August 14, 2019 at 1:50 am
Many Thanks Grant Fritchey and MVDBA !
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply