title: SQL Server: Archiving old data
body: <p>I have a database that is getting pretty big, but the client is only interested in the last 2 years' data.  But they would like to keep the older data "just-in-case".</p>
  
  <p>Now we would like to archive the data to a different server over a WAN.</p>
  
  <p>My plan is to create a stored proc to:</p>
  
  <ol>
  <li>Copy all data from lookup tables, tables containing master data and foreign key tables over to the archive server.</li>
  <li>Copy data from transactional tables over to the archive DB.</li>
  <li>Delete transactional data from master db that's older than 2 years.</li>
  </ol>
  
  <p>Although the approach will teoretically meet our needs, the 2 main problems are:</p>
  
  <ol>
  <li><p>Performace: I'm copying the data over via SQL Linked Servers.  Some of the big tables are really slow as it needs to compare which records exist and then update them, and the records that doesn't exists needs to be created. Seems like it will run in 3-4 hours.</p></li>
  <li><p>We need to copy the tables in the correct sequence to prevent foreign key violations, and also the tables that have a relationship to itself (eg. Customers table with a ParentCustomer field), needs to be transferred without the ParentCustomer and then the ParentCustomer needs to be updated to prevent FK violations.  Thus it becomes difficult to auto generate my Insert and Update statements (I would like to auto generate my statements as far as possible).  </p></li>
  </ol>
  
  <p>I just feel there might be a better way of archiving data that I do not yet know about. SSIS might be an option, but not sure if it will prevent my existing challenges. I don't know much about SSIS, so I might need to find some material to study it if that's the way to go.</p>