replication SQLServer 2-way

We have 2 SQLServer-databases with the same table in it. In both databases the table can be updated by users.
Is is possible to replicate the updates in this table from the one database to the other and vice versa? We want to use transactional-replication.You can setup an updatable transactional replication. To do that, specify 'true' to the paramenter @.allow_sync_tran when you execute sp_addpublication. And @.update_mode = 'sync tran' when you execute sp_addsubscription.|||Just out of curiousity, why can't he use merge replication instead?
I'm still learning from this replication thing...is there any reasons for chosing transactional against merge for this purpose?|||My personal experience in both merge and updatable transactional is that merge is slower, and it take a lot more to setup and manage. You can treat updatable transactional just like the regular transactional replication with a couple of more parameters to setup. There are other minor reasons but the main thing is the simplicity and better performance of transactional replication.

Hope this helps.|||joejcheng,

We tried the solution you provided and it works fine now. Thank you for your information.

Replication over Dev Server

I got 2 servers running, Dev and Production
The Production one is the one that the users run on and it's working
with a Access adp project
The Dev one is the migration project that evolve and use VB as
interface.
So my test are made on the Dev version but, the way it work now is i
copy my database files and trasnfert it to my dev server, delete my
current database and relink new files and start working again. Im
doing so for a while now and it become pretty long to do with size
increment. So i think it's time to change to automatic process. I've
tried with DTS packages but DTS transform view into table and don't
transfer everything. So Replication seems the best thing. I need to
know if the current thing his possible and if it is, is there good
tutorial for such replication i could use ?
i would like to replicate my Prod server to Dev each morning and i
don't want Dev modification to propagate to Prod. so make a kind of
backup database in other word.
I would say that your current method is possibly the best. You could use
snapshot replication as an alternative, but if you want the whole database
and are already backing it up, it might be simpler to ship the backups and
restore on the DEV server. If you are going across the network and this is
slow, then you could investigate zipping and unzipping before the file
transfer. I have a similar setup, and even though it isn't out of the box, it
works well.
HTH,
Paul Ibison
|||On Apr 24, 4:28 am, Paul Ibison <Paul.Ibi...@.Pygmalion.Com> wrote:
> I would say that your current method is possibly the best. You could use
> snapshot replication as an alternative, but if you want the whole database
> and are already backing it up, it might be simpler to ship the backups and
> restore on the DEV server. If you are going across the network and this is
> slow, then you could investigate zipping and unzipping before the file
> transfer. I have a similar setup, and even though it isn't out of the box, it
> works well.
> HTH,
> Paul Ibison
Ill this this this morning and see how it goes. should be fast under
1gbit lan.
|||On Apr 24, 8:20 am, Franck <the_darkbl...@.hotmail.com> wrote:
> On Apr 24, 4:28 am, Paul Ibison <Paul.Ibi...@.Pygmalion.Com> wrote:
>
> Ill this this this morning and see how it goes. should be fast under
> 1gbit lan.
i manage a vb script that backup with t-sql the Prod server and after
does a little DOS copy command to the Dev server and T-sql again the
Restore, all that take only 35-40 seconds. restore being the longest
step.

Replication of users

Hi,
I have setup a process so I can replicate users and logins between my
publishers and subscribers. This I have done using the replication of stored
procedure execution.
Now the problem I have is to replicate the sp_revokedbaccess sp from
publisher to subscriber.
IT throws me error when I try to replicate the execution of sp saying that
this sp can not be part of a transaction.
Is there any other way to replicate this functionality.
Please advice..
Thank you
Sam
use sp_addscriptexec to replicate the commands to all subscribers deployed
via a unc.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"sam" <sam@.discussions.microsoft.com> wrote in message
news:576BFBFB-8C8C-4DCE-8510-BFC472F2C8F9@.microsoft.com...
> Hi,
> I have setup a process so I can replicate users and logins between my
> publishers and subscribers. This I have done using the replication of
stored
> procedure execution.
> Now the problem I have is to replicate the sp_revokedbaccess sp from
> publisher to subscriber.
> IT throws me error when I try to replicate the execution of sp saying that
> this sp can not be part of a transaction.
> Is there any other way to replicate this functionality.
> Please advice..
> Thank you
> Sam
|||Hi Hilary,
Thank you for your response.
I 'm still little confused about how to create a script on fly when a db
user is removed via application and so I want to replicate the same function
on subscriber.
I have to create a script on fly from the sql query execution at the
publisher when a user fires a sp_revokedbaccess command. And we have
xp_cmdshell is blocked on server for security reasons.
What do you think about this situation and how should I implement the
replication functionality?
Sam
"Hilary Cotter" wrote:

> use sp_addscriptexec to replicate the commands to all subscribers deployed
> via a unc.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "sam" <sam@.discussions.microsoft.com> wrote in message
> news:576BFBFB-8C8C-4DCE-8510-BFC472F2C8F9@.microsoft.com...
> stored
>
>
|||I'm sorry I either misunderstood your question or misread your post.
There is no way to dynamically sense the changing users or logins and
replicate this, other than by doing log shipping.
You have to know in advance what user has been added, dropped, modified, and
then encapsulate the commands to carry out this modification and use
sp_addscriptexec to replicate this command to all subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"sam" <sam@.discussions.microsoft.com> wrote in message
news:D1F7D6BA-6C2D-4ECE-8B05-BA1053EAB6E4@.microsoft.com...
> Hi Hilary,
> Thank you for your response.
> I 'm still little confused about how to create a script on fly when a db
> user is removed via application and so I want to replicate the same
function[vbcol=seagreen]
> on subscriber.
> I have to create a script on fly from the sql query execution at the
> publisher when a user fires a sp_revokedbaccess command. And we have
> xp_cmdshell is blocked on server for security reasons.
> What do you think about this situation and how should I implement the
> replication functionality?
> Sam
> "Hilary Cotter" wrote:
deployed[vbcol=seagreen]
that[vbcol=seagreen]