We are pretty new to setting up replication but need to do this now.

We have a client who wants to implement replication across 2 sites. Site A is the main site and publisher where pretty much all tables will be replicated on Site B, the subscriber. There are also some tables at site B which will be updated via a web interface which need to replicated back to A. So transactional replication with updateable subscribers method of replication looks to be the route to take.

The problem we have is that when we implement the suggested method, the wizard wants to create new columns namely a uniqueidentifier and quite possibly a timestamp field too. We don’t want this and can’t have it as the front end program which accesses the database will crash. Also we already have fields on each table which are unique and timestamp log fields also which could be used instead.

My question is - is it possible to setup transactional replication with updateable subscribers and selecting our own fields as unique identifiers rather than having new fields created and if so how?

I'm not familiar enough with replication to know the answer for the question asked, however...

Why would your application crash if these columns are added to the database? Perhaps some changes are in order to reduce the fragility of the relationship between the application and the database structure.

-Peter
compiledthoughts.com

Chubnut,

Replication adds the uniqueidentifier columns to the database for replication purposes only. They will not, and are not, displayed to the end user. Nor will your application even know about them. Regardless of your replicaiton method (merge, transactional, etc) these columns are added and necessary for a successful replication.

If the database is crashing (and I'd like to know what you mean when you say "crashing") then you have bigger issues. Replication will not cause this.

The columns that replication ads has nothing to do with your application. they are application INDEPENDENT. Your applicaiton does not even know they exist.

========================
Scott Klein
Author of:
Professional SQL Server 2005 XML
Professional WCF Programming: .NET Development with the Windows Communication Foundation
Professional LINQ
========================

Many thanks for the responses.

The application has gone through various transitions over many years and as you can imagine has become a bit of a beast. The application uses a data dictionary to validate the tables and the information it displays out to the end user. The data dictionary makes comparisions to the database and the table structure and therefore if they are out of sync fails.

Obviously, any changes to the database and table structure need to be reflected in the data dictionary and also out to the program even though it's not veiwed by the end user. It's not the right way to do it but it is the way it is at the moment. We are talking about changing it, but it will require some major work which will require investment and we need to get our clients to buy into the new version (which will also cost them) and as you can imagine, they don't like paying :-). Also, the replication issue we are looking at is only for one client at the moment, which also means that the view being taken is - if it isn't going to work then they can't have it at the moment.