title: SQL Server Bi-Directional Transactional Replication - Is it a good use-case?
body: <p>We're having a problem with scaling out with SQL server.  This is largely because of a few reasons: 1) poorly designed data structures, 2) heavy lifting and business/processing logic is all done in T-SQL.  This was verified by a Microsoft SQL guy from Redmond  we hired to perform an analysis on our server.  We're literally solving issues by continually increasing the command timeout, which is ridiculous, and not a good long term solution.  We have since put together the following strategy and set of phases:</p>
  <h3>Phase 1: Throw hardware/software at the issue to stop the bleeding.</h3>
  <p>This includes a few different things like a caching server, but what I'd like to ask everyone here about is specifically related to implementing bi-directional transactional replication on a new SQL server.  We have two use-cases for wanting to implement this:</p>
  <ol>
  <li><p>We were thinking of running the long running (and table/row locking) SELECTs on this new SQL &quot;processing box&quot; and throwing them into a caching layer and having the UI read them from the cache.  These SELECTs are generating reports and also returning results on the web.</p>
  </li>
  <li><p>Most of the business logic is in SQL.  We have some <em>LONG</em> running queries for SELECTs, INSERTs, UPDATEs, and DELETEs which perform processing logic.  The end result is really just a hand-full of INSERTs, UPDATEs, and DELETEs after the processing is complete (lots of cursors).  The thought would be to balance the load between these two servers.</p>
  </li>
  </ol>
  <p>I have some questions:</p>
  <ol>
  <li><p>Are these good use-cases for bi-directional transactional replication?</p>
  </li>
  <li><p>I need to ensure that this solution is going to &quot;just work&quot; and not have to worry about conflicts.  Where would conflicts arise within this solution?  I have read a few articles about resetting the increment on your identity seed in order to prevent collisions, which makes sense, but how does it handle UPDATEs/DELETEs or other places where conflicts might occur?</p>
  </li>
  <li><p>What other issues might I run into and we need to watch out for?</p>
  </li>
  <li><p>Is there a better solution to this problem?</p>
  </li>
  </ol>
  <h3>Phase 2: Rewrite the logic into .NET, where it should be, and optimize SQL stored procedures to perform only set-based operations, as it should also be.</h3>
  <p>This will obviously take a while, which is why we wanted to see if there were some preliminary steps we could take to stop the pain our users are experiencing.</p>
  <p>Thanks.</p>