title: SQL Server Database Optimization Strategy
body: <p>I am starting a new project using SQL Server for a medical office. Their current database (SQL Server 2008) have over 500,000 rows that span across 15+ tables. Currently they are complaining that their data entry application is very slow to generate reports and insert new data.</p>
  
  <p>For my new system I was thinking of developing a two tiered database approach where the primary used SQL Server 2012 will only contain 3 months worth of rows and the second SQL Server 2012 would maintain all the data for the system. This way when users insert new data it will be entered into a much smaller system and when they query recent data the query should execute much faster. This system will also have reporting, but I think the reports will have to be generated from the larger data set.</p>
  
  <p>My questions are as follows</p>
  
  <ol>
  <li>Will a solution like this improve the overall performance of the database</li>
  <li>Are there any scalability concerns with this solution?</li>
  <li>What is the best way to transfer that data between the two servers each night?</li>
  </ol>
  
  <p>If my solution makes no sense please feel free to offer any other solutions.</p>