August 7, 2015 at 12:49 am
Hi community!
Lets take the following setup for a replication scenario:
I have a SQL Server Express installation in the field [FDS] on a production system. The database collects trend data (environment data like temperature, humidity, ...) and supplies the UI client with "master data" information which is needed for the production (setup parameters for the production machine, recipes). The FDS has no high load. The complete DB has maybe 50 MB (including the trend data, index, ...)
The "master data" resides on a central SQL Server [CDS] (Standard upwards). The CDS replicates the master data. If the client changes the "master data" information and the CDS is online, the "master data" is stored on the CDS and should get replicated immediately to the FDS.
If the network connection between CDS and FDS is lost and master data must be changed, the change should happen on the FDS to continue with the production. As soon as the connection gets back online, the data is replicated again to the FDS and the earlier change to the master data is overwritten.
"Transactional replication, however, is sensitive to external changes to the target database. Any such change can potentially break replication so, for all intents and purposes, the target database should be considered read-only. There are exceptions to this rule, however. For example, you can modify objects in the target database that are not part of the replication setup." "Fundamentals of SQL Server 2012 replication" [Sebastian Meine, from Red Gate, page 32]
After reading the book, I would say that I should set up the scenario as transactional replication in the push mode. However, i am irretated by the above statement which would break the replication.
As the transactional replication is reading from the log writer, do I have to setup the CDS with the "Full" backup option?
How would you set up such a scenario?
Thank you for your input.
August 7, 2015 at 10:25 am
I'm not 100% sure what will happen if you change the replicated values. I'd think you're OK, but I'd have to test some things, and you should as well.
I had a similar scenario years ago, but replication was semi-unreliable then. We actually used a trigger to send changes from a master table to a staging table on the publisher. We replicated to a staging table on the subscriber and then had a trigger move changes to the table we used.
Publisher db
- Codes table - changes made by clients here.
|
------ Code_Repl - this is the table used in the article for replication
Subscribing DB
- Codes_Stage - subscribing table for Code_Repl
|
--- trigger - Send changes to Codes table
August 10, 2015 at 12:12 am
Hello Steve!
Thank you for the reply.
Your suggestion is a way I already thought about. But I do not know if using replication and triggers is accepted by the client. They are already reluctant about using stored procedures for their solutions.
August 10, 2015 at 12:29 am
August 10, 2015 at 12:52 pm
Jayanth_Kurup (8/10/2015)
SQL express cant be used as a publisher in replication
I don't believe this was the intention. It seems as though SQL Standard is the publisher, moving to a variety of Express subscribers.
August 11, 2015 at 12:01 am
I apologize if it was not clear in my first post.
The central server (standard edition) would be the publisher.
Sql Server Express edition would be the subscriber.
August 12, 2015 at 10:44 am
You could potentially use Merge replication using Windows Sync Manager. Although I have never set this up personally. Reading through these articles it sounds plausible. I have assumed that any updates that you do on FDS when its disconnected also gets applied on CDS. As when the two servers get reconnected, if you want CDS to overwrite then it has to have had an update AFTER FDS. Else the FDS update will just get pushed up to CDS.
https://technet.microsoft.com/en-us/library/ms151819(v=sql.105).aspx
https://technet.microsoft.com/en-us/library/ms151193(v=sql.105).aspx
Hope that gives you a starting point? It will be interesting to see how you get on.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply