question

jeromystewart avatar image
jeromystewart asked

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?

replicationsql servermigrationversion-controlwebinterface
1 comment
10 |1200

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

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 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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