title: Incremental data import in Solr from MSSQL normalized table with complex joins
body: <p>Working on Solr incremental data import from an existing normalized mssql database. I'm unable to decide on the strategy I need to implement, or not knowing whether there are existing tools to do the same, so I don't need to reinvent the wheel.</p>
  
  <p>I need to import a document into Solr 3.6 to build a Solr Data, which is saved in MSSQL in heavily normalized fashion. To retrieve the data for single document, there are many joins required which is killing performance. I have appx. 1 million such document in db. So full import into Solr is not an option for me.</p>
  
  <p>While deciding the approach I have two issues to consider:</p>
  
  <ul>
  <li>Incremental data import, so that SQL server doesn't have heavy load while fetching data from db.</li>
  <li>Updating data that has been changed in SQL Server into Solr data once a day</li>
  </ul>
  
  <p>I am looking after your help in deciding the strategy and tool for incremental data import into Solr. I think, I have following options:</p>
  
  <ol>
  <li><p>Custom develop application to fetch data from MSSQL and pass it to Solr. I need to keep track of data as what all records are inserted into Solr and what are pending. Again, 2% data records in MSSQL keeps updated on daily basis, so need to track what data has changed since then, and then update them again at some point of time into Solr.</p></li>
  <li><p>Use any existing tool or utility in Solr to do the same, like DIH. I'm not sure how this will address both of the issue of incremental data retrieval and how it will track what data has change in SQL server? Again, not sure how DIH will handle complex joins requires to fetch data from db.</p></li>
  <li><p>Or use something like Lusql with DIH, bust still not sure about how it will address both the issues. Although Lusql will give ability to do complex joins in db, so I hope this might fit my purpose.</p></li>
  </ol>
  
  <p>I'm in favor of using LuSQL with DIH in Solr, if it can fit the purpose, but still not sure how it keep track of what data has change? Or for this part I have to manage manually by maintaining the document id where the change is made, and then supplying it to LuSQL to fetch data from SQL and import into Solr.</p>
  
  <p>I am also looking forward for your suggestions beyond this to handle this kind of situations.</p>