title: Database Architecture, Database Split
body: <p>I have come across DB architecture which does not feel right to me. It is for a small team of developers... I would appreciate any opinion on this design.</p>
  
  <p>This is a simplified description of the system. All of the 3rd NF databases (Client, Accounting, Rate, Exposure)</p>
  
  <p>We have 4 Normal Form DBs:</p>
  
  <p>•   <em>Client DB</em>: maintain client &amp; organisation info</p>
  
  <p>•   <em>Rate DB</em>: getting exchange rates from a 3rd party system</p>
  
  <p>•   <em>Exposure DB</em>: contacting a 3rd party system for getting our bank account 
  and trade info</p>
  
  <p>•   <em>Accounting DB</em>: further calculation on the financial risk and forecasting</p>
  
  <p>We have the following databases for data wharehousing</p>
  
  <p>•   <em>SQL Server Analysis Services</em>: Star Schema</p>
  
  <p>•   <em>Cube</em></p>
  
  <p><strong>Database Split:</strong>
  Our 4 Databases (<em>Client</em>, <em>Rate</em>, <em>Exposure</em>, <em>Accounting</em>) are split amount 4 SQL Servers, but they all run on the same physical server. These databases need the data from eachother, for example we have an Organisation Table which is used in all DBs… or Rates are needed in other DBs.</p>
  
  <p><strong>Analysis Services:</strong>
  We have a star schema and Analysis Services. My understanding is that <em>Data Vault</em> could be used as a source for generating Start Schema…. But we are not using our <em>Data Vault</em> for that purpose. We use SSIS to read the data directly from <em>Client, Rate, Exposure and Accounting</em> DBs and populate start schema directly.</p>
  
  <p><strong>Question:</strong></p>
  
  <ol>
  <li><p>Is Splitting databases a good idea when we need to use the data within those splitted Databases?</p></li>
  <li><p>Is there a good Source/Blog to explain when it is a good idea for splitting a database?</p></li>
  <li><p>Is copying tables from source database to destination database a good solution? I feel cross DB queries would be much more simple and efficient than copying so many tables into multiple DBa.</p></li>
  </ol>