what is the advantage and disadvantage for the different data synchronizing

  • 892717952

    SSC Veteran

    Points: 237

    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?

  • MVDBA

    SSC-Insane

    Points: 20353

    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

  • Grant Fritchey

    SSC Guru

    Points: 395394

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 892717952

    SSC Veteran

    Points: 237

    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!

     

     

  • MVDBA

    SSC-Insane

    Points: 20353

    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

  • Grant Fritchey

    SSC Guru

    Points: 395394

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • MVDBA

    SSC-Insane

    Points: 20353

    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

  • 892717952

    SSC Veteran

    Points: 237

    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

  • Grant Fritchey

    SSC Guru

    Points: 395394

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • MVDBA

    SSC-Insane

    Points: 20353

    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

  • 892717952

    SSC Veteran

    Points: 237

    Many Thanks  Grant Fritchey  and MVDBA !

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply