Web Server - stage insert/update and post to SQL server asynchronously/lazily

A requirement has been added to a project to implement versioning/journaling. I want to handle this requirement without adding latency to requests so I would like to stage data related to successful inserts/updates and trickle the data to the database server when traffic is low. I suppose what I want to do is sortof analogous to database replication but I'm trying to understand if SQL server can facilitate an automated migration of data from one local SQL server instance to another .. is this question clear enough?

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green commented · Feb 07, 2020 at 09:18 AM

I take it from the above, that you want to have a "write" SQL server where the web does all the INS/UPD/DEL and a "read" server where it does all its SELECTs?

This is possible through Availability Groups, where you can enable "readable secondary's", double the license costs, will potentially need some dev work on connection strings etc.

You set the write server as an async copy to the read server. Set the connection strings as "read intent only" so the selects go to the readable secondary and the writes go to the other server.

You could look at things like change tracking, change data capture and put routines to move data from A to B in place like SSIS copies etc but you need to ensure everything is re-played in turn for consistency etc.

0 ·