Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Design recomendations.

Posted on 1999-06-29
Medium Priority
Last Modified: 2010-03-19
Hi experts.
I'm new to the MS SQL DB world, and I need some recommendations. I'm involed in the designing of a site where we would like to place two SQL7 DBs on two machines. The DBs have to be a copy of each other, for redundency and performance. The questions are:
1. I heard that the SQL7 comes with tools for mirroring / replication, but they are not the best out there. What other options are there (3rd parties) and which is the best (cost/perf, prices)?
2. Should we perform transactions on both machines and have them constantly synchronizing, or would it be better to do that on one of them and have the second DB tracking that one all the time (what would gain better performance? relaibelity?).

Thank you in advance.
Question by:og
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 1095997
Is the main reason for having two databases "performance" or to minimize the risk of losing data?

Author Comment

ID: 1095998
Well, if I have to grade it, then the first priority is to minimize the risk of data lose, and then perfomance.

Expert Comment

ID: 1095999
I distrust that 3rd party replication tools can work faster than native SQL replication, and I'm sure that this'll be worse in data safety. MS SQL isn't  open enough to use external tools.

Read manual. If you have super fast network - "Two-phase commit" will help you, else - use "loose consistency" replication model.

Accepted Solution

rickbus101 earned 140 total points
ID: 2018096
In our company we use replication across a WAN with few serious problems. I therefore think that SQL replication isgood enough.

To answer your second question you must use one server as a publisher ( the main server where updates, inserts, deletes etc will be run) and make the other a subscriber(accepts changed data and makes the changes to the tables). You can then easily use the subscriber as a "reporting server" and the publisher as a "transactional server". This would then balance the workload.

Clear enough?

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question