title: Pattern for updating slave SQL Server 2008 databases from a master whilst minimising disruption
body: <p>We have an ASP.NET web application hosted by a web farm of many instances using SQL Server 2008 in which we do aggregation and pre-processing of data from multiple sources into a format optimised for fast end user query performance (producing 5-10 million rows in some tables). The aggregation and optimisation is done by a service on a back end server which we then want to distribute to multiple read only front end copies used by the web application instances to facilitate maximum scalability.</p>
  
  <p>My question is about the best way to get this data from a back end database out to the read only front end copies in such a way that does not kill their performance during the process. The front end web application instances will be under constant high load and need to have good responsiveness at all times.</p>
  
  <p>The backend database is constantly being updated so I suspect that transactional replication will not be the best approach, as the constant stream of updates to the copies will hurt their performance.</p>
  
  <p>Staleness of data is not a huge issue so snapshot replication might be the way to go, but this will result in poor performance during the periods of replication.</p>
  
  <p>Doing a drop and bulk insert will result in periods with no data for user queries.</p>
  
  <p>I don't really want to get into writing a complex cluster approach where we drop copies out of the cluster during updating - is there something along these lines that we can do without too much effort, or is there a better alternative?</p>