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

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

588 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