replication sqlserver2000 to sqlserver2005

Hi,
I want to replicate all Updates, Inserts and Deletes (possibly indexed
view changes) to a whole database from sql server2000 (live server) to
sql server2005 (reporting server), but I only want to do it every
night to at an interval that I choose. (Note I don't want to replicate
the whole database each time, just initially).
Is it easy to use replication between sql Server 2000
(publisher/distributor) and sql server 2005 (Subscriber)?
I want to use Merge replication, mainly because I can set it to only be
scheduled once a night (as opposed to Transactional where changes would
be sent for every complete transaction)... not interested in merging
any data, just sending all the changes across.
Are there any issues with this idea?
If this is the right way to do it? Or should I be using Data
Transformation Services?
If I am to use Data Transformation services, is there a way to
schedule/automate this task on both sides?
- The Bermused<tech101@.gmail.com> wrote in message
news:1149812382.243609.307830@.i40g2000cwc.googlegroups.com...
> Hi,
> I want to replicate all Updates, Inserts and Deletes (possibly indexed
> view changes) to a whole database from sql server2000 (live server) to
> sql server2005 (reporting server), but I only want to do it every
> night to at an interval that I choose. (Note I don't want to replicate
> the whole database each time, just initially).
> Is it easy to use replication between sql Server 2000
> (publisher/distributor) and sql server 2005 (Subscriber)?
I don't think you'll have issues here.
I wouldn't use Merge. I'd use transactional, but set it up to run
non-continously.
Another option btw, is to log-ship. This may ni fact work even better in
this case if you're not doing reports at night.
> I want to use Merge replication, mainly because I can set it to only be
> scheduled once a night (as opposed to Transactional where changes would
> be sent for every complete transaction)... not interested in merging
> any data, just sending all the changes across.
> Are there any issues with this idea?
> If this is the right way to do it? Or should I be using Data
> Transformation Services?
> If I am to use Data Transformation services, is there a way to
> schedule/automate this task on both sides?
> - The Bermused
>|||Thanks Greg.
I think that the transacitonal solution is a good way to go. With the
distributor being on the same box as the publisher initially, and when
we have more resources then move the distributor to a box in the same
network/domain as the publisher.
Have tried setting this replication up, but run into authentication
problems.
I think the problem is that the Publishing/Distributor sqlserver2000
(Prod/Live) machine is in a different domain to the subscribing
sqlserver2005 (Reporting) machine. Also there is no trust set up
between the domains, so an AD account will not work.
I have heard that impersonation may work in that:
* an agent that SQL Server2005(Subscriber) uses (one of many found in
Services) can run under a specified system account
* I supply the credentials of this account during the (transactional)
replication wizard on the SQL Server2000(Publisher)
This may work but, I am not sure which agent/service to apply the
account to - and I don't want to break things!
By default, services run under a local system account... not sure which
account is default though...
I can set up an SqlServer service to run under a particular account,
however, as mentioned, I am not sure which service to use.
Does this style of solution sound like it is the right way to go?
Also, would log shipping be a less technical/problematic solution for
this? Would it consume less resources on the live machine? And would
it be an incremental type solution that would not clog up the network?
Cheers,
Adam
Greg D. Moore (Strider) wrote:
> <tech101@.gmail.com> wrote in message
> news:1149812382.243609.307830@.i40g2000cwc.googlegroups.com...
> > Hi,
> > I want to replicate all Updates, Inserts and Deletes (possibly indexed
> > view changes) to a whole database from sql server2000 (live server) to
> > sql server2005 (reporting server), but I only want to do it every
> > night to at an interval that I choose. (Note I don't want to replicate
> > the whole database each time, just initially).
> > Is it easy to use replication between sql Server 2000
> > (publisher/distributor) and sql server 2005 (Subscriber)?
> I don't think you'll have issues here.
> I wouldn't use Merge. I'd use transactional, but set it up to run
> non-continously.
> Another option btw, is to log-ship. This may ni fact work even better in
> this case if you're not doing reports at night.
>
> > I want to use Merge replication, mainly because I can set it to only be
> > scheduled once a night (as opposed to Transactional where changes would
> > be sent for every complete transaction)... not interested in merging
> > any data, just sending all the changes across.
> >
> > Are there any issues with this idea?
> > If this is the right way to do it? Or should I be using Data
> > Transformation Services?
> >
> > If I am to use Data Transformation services, is there a way to
> > schedule/automate this task on both sides?
> >
> > - The Bermused
> >