We have a SQL server and we want to seperate it into 2 servers. All the tables and SPs will be same. All the tables will be updated seperately except only one. This table should be updated at the same time in both servers. When this table is updated in one server updated record should be written to the other server at the same time. Is there a way to do it ?

It is not accepting the 4 part naming convension as you described. 4 part naming convension can be used only for Stored procedures asservername.Databasename.owner.SPname which we can run a SP in a remote server ? Please clarify.

I wouldn't use sql server replication for this as it will give you a lot of administrative overhead for something simple - and may not even fulfil the requirement.Also what do you do if you restore one of the databases?

You say you want them updated at the same time - i.e. you don't want the process to complete until both databases are updated? This means that if the remote server goes down you cannot update the local one either.If that is the case then you are letting yourself in for a very difficult future and replication won't support this anyway. Better to redesign the system so that you can allow for a little lag between the two systems - a minute or so will make things a lot easier.

If you need the two in step then create an insert,update, delete SPs on the remote database

do similar things for deleted/updated.The example here is for single PK field - will work for compound though.Can build script to auto-generate this if you need it for other tables.

If you can cope with a slight delay in synchronisation though it is better to transfer the data via staging tables. This will have a minimal impact on the source server, give an audit trail of what has been sent across and also be self recovering for the destination server - you just have to be careful about restoring the source server.Create a table on the local and remote servers with the same structure as the table to transfer. The local one has and additional identity column (id) and action column and also a status field default ' '. The remote has the same but the id column is not identity.The triggers just insert into this table and include the action I,U,D.An SP on the local server gets records to transfer given an id.