amalagamate sites

  • Thread starter Thread starter eddiec
  • Start date Start date
E

eddiec

hi everyone,

We are a family owned manufacturing company that has been bought by a
corporation. This corporation has gone from 1 site to 3 sites for this
particular manufacturing process and is interested in amalagamating data
from the SQL Server based Materials Managament System centrally to the head
office to enable central control and reporting with this system. All three
sites have different forests with no trusts between them (nor could they
have as only one of the sites is running W2K3). All sites are connected by
ATM links. The Materials Management Systems in all sites must be stored on a
local server as real time response is required for the production process.

I am looking for some pointers wrt how to manage this situation:

1) SQL Server: Should we replicate to head office overnight or should all
data remain local (each site has IT staff responsible for security and
backup)

2) What type of SQL Server authentication should / can we use

3) Within reports: We have an Access VBA frontend for the system. How will
the adp project pull information from all three locations if it can only be
connected to one database? Is it possible to have a proxy data connection
that will appear as one to the adp project and that will return amalagamated
result sets from all sites to the front-end.

4) How can the adp differentiate between / amalgamate information pulled
from all three locations?

5) AD: Permissions on the database are managed with AD Groups. Help! How can
this work with no trusts between the forests that users are logging on to? I
guess it can't. If we knock SBS out of the equation, can we form trusts
between different W2K forests or join existing domains to become part of one
forest.

Any assistance would be much appreciated.

TIA

EddieC
 
1) I would replicate everything centrally. This is a classic central
subscriber modle.
2) Try Windows Authentication - but the point is probably moot - what you
need to do is use passthrough authentication. Create an account on each
server called SQLServerReplicationAccount and give it the same password.
Then use the impersonate the SQL Server agent account on the publisher
everywhere and use push subscribers/subscriptions. This was the Publishers
in your branch office are authenticating against themselves.
3) You could use views for this - I am not sure if this is the best idea
or will work. What you can do is have the app connect to the central
office and do reporting from there, or either use log shipping or
replicate the consolidated data from teh central box to the local
reporting servers - or is reporting done centrally?
4) What most folks do for this is have a location identitifier key or
column added to their data with a default of the server name or location
name where the data originates form. So you might have a pk which consists
of an identity column and a column called locid (varchar(10)) with a
default of Darwin in Darwin, Melbourne in Melbourne, Sydney in Sydney, etc.
5) The logon permissions are managed through AD groups, but at the db
level it will be by dbuser. I think you will need to mograte to SQL Server
authentication to remove this problem or go to a trust relationships.
--
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
 
Back
Top