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:[vbcol=seagreen]
> <tech101@.gmail.com> wrote in message
> news:1149812382.243609.307830@.i40g2000cwc.googlegroups.com...
> 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.
>|||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:[vbcol=seagreen]
> <tech101@.gmail.com> wrote in message
> news:1149812382.243609.307830@.i40g2000cwc.googlegroups.com...
> 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.
>sql

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
> >

replication SQLServer 2-way

We have 2 SQLServer-databases with the same table in it. In both databases the table can be updated by users.
Is is possible to replicate the updates in this table from the one database to the other and vice versa? We want to use transactional-replication.You can setup an updatable transactional replication. To do that, specify 'true' to the paramenter @.allow_sync_tran when you execute sp_addpublication. And @.update_mode = 'sync tran' when you execute sp_addsubscription.|||Just out of curiousity, why can't he use merge replication instead?
I'm still learning from this replication thing...is there any reasons for chosing transactional against merge for this purpose?|||My personal experience in both merge and updatable transactional is that merge is slower, and it take a lot more to setup and manage. You can treat updatable transactional just like the regular transactional replication with a couple of more parameters to setup. There are other minor reasons but the main thing is the simplicity and better performance of transactional replication.

Hope this helps.|||joejcheng,

We tried the solution you provided and it works fine now. Thank you for your information.

Replication row Commit Size

Is there a way to get replication to commit records in batches instead of all at once? I am in a 24/7 shop and some of my updates end up being thousands of rows and it locks the subscriber table for a few minutes sometimes. If I could get it to commit say every 1000 rows it might give me some relief in this area..

Or am I thinking about this wrong? If this is possible, would it help at all...

I

transactional replication adheres to the ACID properties, so if you apply 10,000 commands in one trasaction at the publisher, the distribution agent will apply the same commands in one transaction at the subscriber.

You have a couple options:

1. commit your commands at the publisher in smaller batches. That means if your update statement will affect 100k rows, break it up by updating 1,000 at a time until all 100k are completed.

2. You can use the logreader agent parameter -MaxCmdsInTran, which will break the ACID properties, but allow you to do what you want to do. This has other performance impact, but if you're committing thousands and thousands (or millions) of rows in a single transaction, this can offer some relief. You can find more information about this parameter in Books Online.

I'd recommend #1 before #2.

|||

Thanks for that response... it was what I was expecting to hear but just wanted to check. My servers typically only get updated once a day with major updates and the updates can happen over time as they are not mission critical. The servers are only content related so as long as they get updated they are happy...

Thannks again for the answer.

Replication performing UPDATES as INSERTS and DELETES on the subscriber

I have transactional replication set up between two SQL Server 2000 databases. In some cases when I perform an UPDATE on a published table on the the publisher, SQL Server attempts to perform a DELETE followed by an INSERT on the subscriber using the stored procedures created during the initial snapshot.

Why does it do this?

How can I stop it doing this and force an UPDATE on the publisher to call the UPDATE procedure on the subscriber?

Thanks

Cut paste from a different thread, see if this helps:

>> If you can limit the update to unique columns as singleton (each update affects one row) , SQL2000 will replicate it as update. Otherwise for multi-row updates to unique columns you may consider replication of ‘proc exec’ instead.

>> You should also look at the parameter @.schema_option for sp_addarticle. There’s an option that marks FKs at the subscriber as Not For Replication, this may be what you’re looking for. Enabling this should work if you don’t have any other fancy business-triggers at the subscriber that are breaking due to the delete/insert.

|||

What's happening is called a deferred update. See kb 238254 for more info. This is a by design behavior.

Is this causing any problems in your scenario? Are subscriber-side triggers breaking? Are you getting referential integrity errors?

Two common workarounds are to replicate the update via proc exec, or enable schema option 0x20000 in sp_addarticle, which does not enforce RI constraints if raised by replication agent.

Replication over the internet problems

I have a situation where a customer has a SQL 2000 database at thier office
location, this is where they do data updates, it replicates to a SQL 2000
server located at a web hosting facility. Problem is there is a new IT guy
at the office location that has done something to the setup so that now the
database does not replicate. When at the office you can see the hosting
facility SQL in SQL Manager but you can not see the SQL server from the
office location in the hosting facility. The SQL ports are open on the
firewall, and we can telnet to the port but it does not replicate and we can
not see the server in SQL Manager from the hosting facility. What should I
look at first?
Lee - check to see that the alias has not been removed - see (iii) here:
http://www.replicationanswers.com/InternetArticle.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||I made the change on the ALIAS on the pull network and opened port 21 inbound
on the Push network. Replication seems successful on one Agent but there
seems to be an invalid Column error on the other. I am having the database
guys look at that. But this may have fixed the problem.
Thanks
|||The replication caused issues with the database. What I would like to do now
is remove everything and start from scratch. What things should I look for
first befoer removeing everything?
All we want to replicate is the data only, not schema or anything else.
Thansk.
|||Lee - if you have plain transactional replication using
sp_removedbreplication on the subscriber and dropping the publication at the
publisher should be enough.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Ok. I just got off the phone with Microsoft and I understand a lot now. Now
to test replication here is what we did:
Created a new database on the Publisher and added a table with columns and
data.
We then created a New Publication set. After creating this here is what
happened that has me stumped...
It created the database and the columns, but it did not replicate the data.
It gives and error: Generic Network error.
Some howit seems to be losing connection, maybe? Any ideas?
|||Lee,
I'd enable logging (http://support.microsoft.com/?id=312292) and see if this
gives more info. Also start ping -t before syncing to test connectivity.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com