I'm don't know what kind of replication should I use

[aref_pena]

In the company that I work there are two offices, one is outside the city and the other is in the city. They are connected trough a DS0 using frame relay. The thing is that the comunication is very slow (the SQL server is in the city) and we want to add a new server at the office outside the city and we want at the end of the day replicate the data between the two servers so every server has the actual data. We didn't use primary keys, instead of that we used identities (autoincremental value administered by the server). What kind of replication should I use and why? (I'm new with SQL Server). There are 3 kinds: Snapshot, Transactional and Merge.

[Stephen]

Without a primary key, you will not be able to use transactional replication.

Merge replication can present a high load on your servers depending on which service pack you are using (I had big problems using SP1).

That leaves snapshot replication. You don't say how big your tables. With a slow link you may run into problems because there is not enough time in the night.

You should have a think about the design of your tables. A lack of primary keys indicates to me that the design process needs to be revisited. I would be surprised if there is no candidate for a primary key (at the very least you have the identity columns).

Once you have a primary key, you will be able to use transactional replication - this is likely to be your best option. I also assume that the data on the destination is read only. If not then merge replication is you only choice. If this is the way you need to go, be careful. Test the application well in advance of production deployment and be aware that you must manage conflicts.

Good luck
Stephen

[rmiao]

What's sql version do you have? By the way, you can use transactional replication for two way replication by enabling 'update subscriber immediately'.

[Stephen]

Be careful about immediate updating subscribers. The two servers are some distance apart. It will require the comms link to be reliable otherwise updates will fail - updates on the subscriber must be applied to the publisher (by replication) within the same transaction. If the publisher is offline then this is not possible and the transaction will not be successful

Cheers
Stephen

[rmiao]

You can use queued updating in sql2k.