Re: Best replication architecture?

Thanks for your reply Hilary.

Each server in our system is an installation.
The system we are providing will have these installations in different
physical locations at different times. Sometimes it is envisaged that each
installation will be stand alone in its own domain and at others the user
will be able to link one or more installations to each other within a single
domain. The design proposed in the project plan is that all servers within a
domain (where there is more than one) would replicate to each other and a
bridgehead server would be nominated that would replicate to the other
domains. This sounds similair to what you have suggested but your suggestion
sounds more efficient than having all-to-all replication going on within a
domain. Would the master server be very heavily loaded or could that be
solved by using pull subscriptions?

Sorry if my questions are naive but I am still in the very early stages of
trying to read and understand this problem.

Thankyou,
Lauren

"Hilary Cotter" wrote:

Answers inline.

--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

"Lauren" <Lauren@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D00BC0D8-6990-4968-93F2-F9502508A9F6@xxxxxxxxxxxxxxxx
Hi,

I have just started on a new project and have the responsibility of
setting
up the SQL Server replication architecture. I wonder if anyone can give me
advice on the way to go?
We have a setup of 6 SQL Server 2005 servers which should all be
replicating
to each other, connected to these will be a number of smaller groups of
machines (1 "server" and 9 "clients") which will all be running SQL Server
Express. The Express "server" has at least 80Gb and a reasonably powerful
processor. The Express "clients" only have a 4Gb Hard drive which is a bit
of
a limitation to say the least but non-negotiable. The Express machines may
go
offline deliberately but the user still needs to be able to work when this
happens and so conflicts will need to be resolved on re-connect. The 6 SQL
Server machines all need to be up to date so that the Express "server" can
connect to any one that is available to it and see the latets information.
Oh, and the tiny Express "clients" will only require a subset of the
overall
data applicable to their user domain. How best to achieve this?

The designer on the project has been inclined towards peer-to-peer
transactional replication, but as this does not handle conflict resolution
he
has tried to impose a nauseating check in/out process (and the concept of
a
master server for data) on the user in order to prevent concurrent
updates.
This is not the way we want to proceed now that real development is taking
place and we have decided to go with an optimistic locking mechanism with
replication handling conflicts (latest is best or user role priority
etc.).

You need to do merge replication between the express clients and the main
SQL Servers. Use filtering, possibly join filtering or dynamic join
filtering so the express clients only get what they need.

Queued replication will log the conflicts but you will be unable to roll
them back.

I further think, although it is not competely clear to me that you want to
set up a merge replication heirarchy between your main sql servers, ie one
will be the master, all others subscribers to the master. Then you will each
of these SQL Servers will publish to the SQL Express clients.

Can anyone offer me advice on this - I have been reading a lot about
replication (which is not something I have ever implemented before) and
overall am leaning towards transactional with queuing and updateable
subscriptions but I don't know if Express supports subscriber updateable
or
not and there are lots of features of merge replication which seem to fit
our
architecture? I am currently trying to get some machines set up to try
things
out for real but am having difficulty as our IT provider has clamped down
our machines so much that I can't even get Express to install!

TIA,

Lauren



.

Relevant Pages

  • Re: SBS 2003 and Replication Errors with Remote DC
    ... I just promoted the remote DC last week, so I still have time to solve the replication issues. ... Domain Controller Diagnosis ... Connecting to directory service on server alpha. ... Performing upstream analysis. ...
    (microsoft.public.windows.server.sbs)
  • Re: SBS 2003 and Replication Errors with Remote DC
    ... alpha server as soon as you can to get things going. ... A simple DNS replication test is to create a host record in the SBS server ... Domain Controller Diagnosis ...
    (microsoft.public.windows.server.sbs)
  • Re: SBS 2003 and Replication Errors with Remote DC
    ... I did make the changes that you suggested on the DNS of my alpha server and rebooted. ... I did run the simple DNS test that you suggested by adding a host record to my SBS server. ... A simple DNS replication test is to create a host record in the SBS server and wait till it shows up in the remote server. ...
    (microsoft.public.windows.server.sbs)
  • Re: How to Replicate an SQL Server 2000 Database
    ... Looking for a SQL Server replication book? ... actual server name) enterprise manager should associate the database with ...
    (microsoft.public.sqlserver.replication)
  • Re: How to Replicate an SQL Server 2000 Database
    ... Looking for a SQL Server replication book? ... actual server name) enterprise manager should associate the database with ...
    (microsoft.public.sqlserver.replication)
Loading