CODEWITHSUNDEEP
sql-serverlinq-to-sqlreplication
Mighty Z
Mighty Z

Reputation: 35

Client-side Replication for SQL Server?

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:

Any thoughts?

Upvotes: 0

Views: 381

Answers (2)

Cade Roux
Cade Roux

Reputation: 89671

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.

Upvotes: 2

Raj More
Raj More

Reputation: 48014

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.

Upvotes: 0

Related Questions