Client-side Replication for SQL Server?

Question

I'd like to have some degree of fault tolerance / redundancy with my SQL Server Express database. I know that if I upgrade to a pricier version of SQL Server, I can get "Replication" built in. But I'm wondering if anyone has experience in managing replication on the client side. As in, from my application:

• Every time I need to create, update or delete records from the database -- issue the statement to all n servers directly from the client side
• Every time I need to read, I can do so from one representative server (other schemes seem possible here, too).
• It seems like this logic could potentially be added directly to my Linq-To-SQL Data Context.

Any thoughts?

Answer 1

I have managed replication from an in-house client. My database model worked on an insert-only mode for all transactions, and insert-update for lookup data. Deletes were not allowed.

I had a central table that everything was related to. I added a field to this table for a date-time stamp which defaulted to NULL. I took data from this table and all related tables into a staging area, did BCP out, cleaned up staging tables on the receiver side, did a BCP IN to staging tables, performed data validation and then inserted the data.

For some basic Fault Tolerance, you can scheduling a regular backup.

Answer 2

Every time I need to create, update or delete records from the database -- issue the statement to all n servers directly from the client side

Recipe for disaster.

Are you going to have a distributed transaction or just let some of the servers fail? If you have a distributed transaction, what do you do if a server goes offline for a while.

This type of thing can only work if you do it at a server-side data-portal layer where application servers take in your requests and are aware of your database farm. At that point, you're better off just using a higher grade of SQL Server.