replication type

[SQLScholar]

Hey all,

I currently have a SQL 2000 DB on another site, which is in continual use for business.

We have just had a VPN put in so that we can have a DB here so that the staff here can do some accounts stuff, and others can deal with business.

My question - what replication type??

The DB is quite large (50000 rows on the main table, 5 other main tables with 20,000 each).

If i chose transactional (so that we actually updated the remote server with the VB prog) then we have the problem of it probably running quite slow - but then locking is a problem. But it if the link goes down, so does our ability to add/ammend data.

If i chose merge, then there is a small possibility of 2 people changing the same record - and then we end up with data problems. And if the line does go down (possibily with no-one noticing for a few minutes) then there is the distinct possibility that we will have 2 people access the same record before they sinc.

Any ideas much welcome.

Dan


----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[mrdenny]

You shouldn't notice any slowdown with transactional replication. I've replicated tables with over 100 million records using transaction replication and it's worked fine.

With transaction replication the link between the servers does not need to be up for you to modify the data on the subscriber.

If you plan on updating data at the subscriber then transaction is the way to go. Merge is for rare updates at the subscriber (the name is miss-leading).

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[SQLScholar]

Sorry to re-raise this after so long.

If i do transactional, do i have to do 2 way (i.e. set publisher and subscriber at both ends) to get it so either server can be updated?

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[mrdenny]

Not a problem.

No, you setup a single publisher, and one or more subscribers. The two way replication is automatic with transaction replication.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[SQLScholar]

The test we have done doesnt seem to work as such.


----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[mrdenny]

Are the distribution agents running non-stop, or are they scheduled.

I've never had transaction replication setup where it only sends one way. The whole point of transactional replication is that is goes both ways.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[SQLScholar]

non-stop. Cant figure out why this isnt working.

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[Catadmin]

Transactional Rep says "Data is usally updated at the Publisher, and changes are sent incrementally to Subscribers."

I believe, however, this can be used as one way only or it can be set up two way. I can't remember the exact procedure, but ISTR seeing it when I was originally creating the publications. Something about Immediate Updating or Queued Updating.

Which means, if it's not working the other way, you need to disable and kill the publication, then re-create it.

I'd recommend seeing if you have a test machine you can play with this on before killing what you currently have. Then use all the advanced options in the Creating Publications wizard.

Let us know if that works or not.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???