• Some rough ideas:

    1) if the requirement that each table contains everything inserted in the other table up to the point where the server supporting that other table crashes, you need to update the remote server in the same transaction as the local one; that is potentially poisonous, because it means that the transaction (which will hold at least one page lock as it's updating a page) will hang up until it knows the other server is dead, which means other rows can't be inserted until the remote access finally fails and maybe that delay is unacceptable, and you may have to commit the transaction even though the remote part has failed if insertion order is important(ie if that row must not get an identifier that implies it was later than any other rows that were already queued on the page lock at the time the remote failure was discovered). Also, transactions originating in the other machine will hold page locks on this machine, which may cause similar problems when the remote machine fails - so you want the remote machine to be inserting on a page that the local machine won't use, and so that that won't cause performance issues (too much head movement) the table wants to be partitioned so that the two machines hit different partitions which are held on different drives.

    2) If the requirement mentioned at 1 above is slacker - so that some rows that the remote machine created before a crash may not get to the local machine until the remote machine has recovered as long as any row created more than a couple of seconds ago is on the local machine - distributed transactions are not needed, which makes everything much easier. Each machine can have a process that polls the remote machine for new rows (recognised by identity value) in one transaction and then inserts them in the local table (using identity insert on) in a separate transaction. In this case you may be able to afford for the local rows and the remote rows to hit the same page, but it's probably still safer to try to separate them.

    3) identitity values can be used to indicate which machine a row was created on, and also if desired to partition the table. Using identity(0,-1) on one machine and identity(1,1) on the other is the obvous way to do this. If on the other hand you don't want partitioning because it's an unneccessary complication but still want to avoid excessive head movement, or need to partition on something completely different, you could use identitity(0,2) on one machine and identity(1,2) on the other and cluster on identity.

    4) Maybe each machine could have two tables: stuff it created and stuff the other machine created, for the purpose of inserting stuff; while everything else (including all reporting) sees a view which is the union of the two tables. You could position these two tables on different filegroups. That's simpler than partitioning.

    Tom