none
Merge, Transaction (or other) which would best? RRS feed

  • Question

  • I have an application with two SQLExpress databases that need to be kept synchronised, to within a few minutes of each other. The same  applications (SCADA) runs on the two computers with one (the Primary) active almost all the time. If the Primary fails, the Standby takes over, within a second or so and the SQLExpress database needs to be ready for this eventuality at all times. When the Primary resumes, it takes over again and it's SQLExpress database needs to be-up-to date when that happens.

    The database records are reasonably small (say 100 bytes). The rate of inserts. updates and deletes is 1 to 10 per minute.

    There will of course be a SQL Server SE (or higher), hosted on another server, performing the replication functions and actions. I assume also that this becomes host to a "master" copy of the database and, in the event of replacement of one of the SCADA computers, Snapshot Replication would be be used to initialise the database on a replacement computer.

    Merge replication seems like a natural choice but I ham little wary of the Row GUIDs that will be added to each table. As I understand it, the use of sequential GUIDs would help maintain performance. Most, If not all tables already have an ID (primary key) column.

    I believe that it is possible to configure bi-directional Transactional Replication, is this worth consideration?

    This is obviously my decision to make, but any words of wisdom that can be offered would be appreciated,


    R Campbell



    Sunday, November 12, 2017 4:23 AM

Answers

  • For Mirroring and Always on both servers would need to be SE.   For Always On you would need to run SQL 2016 for BAG (Basic Availability Groups which is available in the  Standard Edition, otherwise you would need the Enterprise edition on both servers). Mirroring was available in SQL 2015 SP1.

    For merge and one way transactional replication one server would need to SE, the other could be Express. For bi-directional transactional both would need to be at least SE.

    • Marked as answer by Dick Campbell Tuesday, November 14, 2017 7:14 AM
    Monday, November 13, 2017 8:31 PM
    Moderator

All replies

  • Hi Dick Campbell,

     

    According to your description, you have two main requirements: synchronizing the database and failover (If the Primary fails, the Standby takes over, within a second or so and the SQLExpress database needs to be ready for this eventuality at all times). Did you have some more detailed requirement related to the failover? For instance, whether these two databases both requires read-write? Do you need a automatically failover?

     

    Actually, Merge and Transactional replication cannot support failover, the most suitable method in your scenario is database mirroring if you do not need read-write for two databases. You can refer to this document for more information: Database Mirroring (SQL Server). However, if both databases requires read-write, database mirroring is also not suitable here, in this scenario, you can use bi-directional Transactional Replication, but you will need to redirect the client manually for failover, it is not convenient.

     

    Besides, Replication and database mirroring cannot be deployed on two SQL Server Express instances, you requires at least using two Standard edition for database mirroring and one Standard, one Express for replication.

     

    Best Regards,

    Teige

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 13, 2017 2:55 AM
    Moderator
  • Thanks for the suggestion but I am not really looking for failover on the SQL database, the standby database just needs to be kept up to date within a minute or so.

    R Campbell

    Monday, November 13, 2017 8:52 AM
  • Teige's comments are valid. I would probably use SQL Server Service broker for something like this to be able to do it using the SQL Express version.

    Another option is to use the sync services framework.

    https://msdn.microsoft.com/en-us/library/mt490616

    This is in extended life support so it is not a good choice long run.

    Monday, November 13, 2017 4:10 PM
    Moderator
  • Thanks for both replies. I think that I need to keep an open mind at this stage. I wouldn’t be asking the questions if I didn’t have some reservations about replication. Having used it in the past, I have some idea what is involved in configuring and monitoring it. The documentation for SQL Mirroring says that this technology is to be replaced by Always On Availability Groups. I will need to investigate that as an option too I think. This first point being whether or not it is available for SQL 2012. The primary aim is to have the same data available to the Primary and Standby computers when they fail over. The simplest solution is probably to have a single SQL Server SE on a third computer shared by the the two SCADA computers. It is a question then of whether or not the resulting vulnerability to a single point of failure is acceptable. I think that SQL Mirroring or Always On Group Availability could be very applicable here, at additional expense. One advantage of a local SQL Express on each SCADA computer, is that there is no vulnerability to network failure. Having said that, SCADA systems have progessively become more dependent on network services over the years.

    R Campbell

    Monday, November 13, 2017 8:03 PM
  • Always on is not available in the express edition of SQL Server. Nor is mirroring.

    You will need a standard edition of SQL server in the mix if you want to do merge or transactional (with bi-directional transactional) replication.

    Monday, November 13, 2017 8:11 PM
    Moderator
  • Always on is not available in the express edition of SQL Server. Nor is mirroring.

    You will need a standard edition of SQL server in the mix if you want to do merge or transactional (with bi-directional transactional) replication.

    Thanks, yes it has been understood all along that a SQL Server SE (or even higher) server is required on a third computer for Replication, Mirroring, Always On etc.

    R Campbell

    Monday, November 13, 2017 8:19 PM
  • For Mirroring and Always on both servers would need to be SE.   For Always On you would need to run SQL 2016 for BAG (Basic Availability Groups which is available in the  Standard Edition, otherwise you would need the Enterprise edition on both servers). Mirroring was available in SQL 2015 SP1.

    For merge and one way transactional replication one server would need to SE, the other could be Express. For bi-directional transactional both would need to be at least SE.

    • Marked as answer by Dick Campbell Tuesday, November 14, 2017 7:14 AM
    Monday, November 13, 2017 8:31 PM
    Moderator