Separating databases that share common data -- how???

• Mike Byrd

  Default port

  Points: 1458

  More actions

  August 6, 2009 at 6:50 am

  #213132

  We have an OLTP (for lack of better terminology) SQL Server 2005 database where 90% of the inbound data comes from files (text, csv, xml, etc) and 10% roughly from a web app. We are having performance issues and would like to divide our current single database into at least 2 databases separating the web app application from the file based application. The bad news is that the web app shares about 40% of the tables with the file based application and each is very dependent on being current (real-time). We would also like to separate the 2 databases on different servers.

  Consider that we had a client table with appropriate child client tables. If data is updated in the file app system for a client we would want that data immediately available in the web based system -- and vice-versa.

  With that in mind, what is the current thinking of maintaining data currency between 2 databases on different servers. Obviously real time bidirectional transaction replication is a consideration, but are there alternatives?

  Our ultimate goal is that the web-based system never see the performance issues we are having on the file-based system (e.g., timeouts).

  I would appreciate any inputs from the gurus out there

  Mike Byrd

  Mike Byrd

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  August 6, 2009 at 7:24 am

  #1035345

  Size?

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.