November 26, 2007 at 2:47 am
Hi
I am pretty new to SQL Server so I ask you to be forgiving if any of this sounds stupid 🙂
I am trying to set up the following:
We have 1 server machine with SQL server 2005 and 12 client computer with SQL Server 2005 Express
On the server there is a database which I need to replicate to the clients. This is simple enough
but the problem is that the clients also add data to one of the tables (in the local db).
If I set up a transactional publication with update subscriptions the data that the clients add
will also flow back to the server from the clients. This is all good but I don't want the
data that the clients add to flow back to the other clients
e.g.
1) client 1 adds 100 rows to local db.
2) 100 rows are sent to master db
3) 100 rows are sent to clients 2,2,3,4,5,6,7,8,9,10,11,12
The table looks something like this so you see there is no possibility of conflict
(client #1 only adds rows with client_nr field = '1')
client_nr datafield
---------------------------
10 'datadatadatadata'
11 'more data data data'
So the big question is. Can I add a filter so that the data from this one table
flows only from the subscriber to the publisher but not the other way?
I realise this could be solved by creating a publication on the clients but since
they are running sql express that is not possible.
Help please!
Best regards
Agust
November 26, 2007 at 5:23 am
I am not sure I would use transactional replication for what you are doing, but you do have a few options if you really want to do this.
The simplest would be to add another column to your table and create individual publications (transactional / updatable) for this table to each client. The new column would indicate the client and each publication would include a filter that only included records added by the client in question.
Now, this is a lot of work for simply updating your central server with data from each client. This updatable transactional replication is really just creating a trigger in your individual databases that updates the central server. You could simply create that trigger. If you do not know how, create the publication and subscription and see what the wizard created.
Finally, I still do not know if using a trigger is a great solution either. What happens if the central database is down, should your client databases fail to work? They will with what you have done. Ask yourself: "How much latency is acceptable for getting the data back to the central server?" Then, ask yourself: "What would be the best way to update two databases from a single application?" Ignore the fact that you have replication coming from the central database to your SQL Express databases for a minute. It is good to take everything into account when you come up with a solution, but this may be a time when you need to focus just on the individual task.
If you do not have another solution after asking those questions, post the answers and I may be able to help.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply