Replication performance question

Hello to all,

I have a performance question: we have a cluster with 2 SQL instances on 1 node (another instance is on another node, but no link with my current problem!). Let's call them C1SQL1 and C1SQL2.

This node is a Hyperthreaded Xeon 2.8Ghz with 1 gig of memory.

These 2 instances are using transactionnal replication and are configured as the distributor and publisher. C1SQL1 is not using much power, it's a small replication with around 10 agents. C1SQL2 is a bit heavier, with around 100 distribution agents. C1SQL2 has around 50 subscribers in 12 publications, but not all subscribers are used in each publications.

Once in a while, this cluster node impacts our production environment (since it's also a production server) and we're wondering if performance wise, it's really not powerfull enough to be the distributor?

I've isolated C1SQL2 on it's own logical CPU, and in idle mode, the replication workload (history, checking if new transactions are made) peaks at around 15-50% each 4-10 seconds.

Can I have any input on this?

Thanks!

Can you give some more detail, I'm not clear on what the problem/symptoms are. Regardless, have you been monitoring disk, memory and CPU to rule out hardware bottleneck? The replication workload you mention in the last sentence, are you talking about logreader agent or distribution agent? Is this SQL 2000 or 2005? Are you running Repl MOnitor in the background?

Replication on Named Instance SS2K

Is it possible to create a Pull Subscription on a named instance in SQL Server 2000 Developer Edition? When I try, I get an error message 14234 saying that it can't find the value '@.server'. Its hint is to look for the server in sp_helpserver. When I go to QA and connect to that named instance and run sp_helpserver, I see it there.

Sincerely,

can you post the complete error message with all the details. can you also tell us what version of sql server you're connecting to (select @.@.version in query analyzer), as well as what version of sql server you have on the machine that has Enterprise Manager?

Replication merge instance is not find in Performance Viewer

Hi all,
I set the configuration for Merge replication for Subscription on
one database and
I have created the Merge replication for publication on another
machine.
And I updated columns of subscription database table and I find
publication machine database table also updated with subscription
database table, here my problem starts,
My problem is when I run replication for merge transaction, I have to
find instance of the Windows on my machine in Performance Viewer (to
see performance viewer goto control panel ->Administrator->performance
in this select performance counter as SQL Server: Replication merge)
but I am not finding the instance
Please some body help me out.
RasheedI am not exactly sure what you mean here. In performance monitor the
counters are by instance. The option to select an instance or all instances
is grayed out. This is by design.
--
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
<sk.rasheedfarhan@.gmail.com> wrote in message
news:1163512500.562695.183350@.h48g2000cwc.googlegroups.com...
> Hi all,
> I set the configuration for Merge replication for Subscription on
> one database and
> I have created the Merge replication for publication on another
> machine.
> And I updated columns of subscription database table and I find
> publication machine database table also updated with subscription
> database table, here my problem starts,
> My problem is when I run replication for merge transaction, I have to
> find instance of the Windows on my machine in Performance Viewer (to
> see performance viewer goto control panel ->Administrator->performance
> in this select performance counter as SQL Server: Replication merge)
> but I am not finding the instance
> Please some body help me out.
> Rasheed
>|||thanks for resopnse Mrs. Hilary cotter
I will explain......i am using SQL Server 2000, when I run replication
(it may be transaction or merge replication) windows components will
run internally.
1) when I was running replication transaction I can see instances in
Performance
viewer but
2) when I was running replication merge I cant see instances in
Performance
viewer
so my doubt is for replication merge can we have the Instance or not ?

Replication merge instance is not find in Performance Viewer

Hi all,
I set the configuration for Merge replication for Subscription on
one database and
I have created the Merge replication for publication on another
machine.
And I updated columns of subscription database table and I find
publication machine database table also updated with subscription
database table, here my problem starts,
My problem is when I run replication for merge transaction, I have to
find instance of the Windows on my machine in Performance Viewer (to
see performance viewer goto control panel ->Administrator->performance
in this select performance counter as SQL Server: Replication merge)
but I am not finding the instance
Please some body help me out.
Rasheed
I am not exactly sure what you mean here. In performance monitor the
counters are by instance. The option to select an instance or all instances
is grayed out. This is by design.
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
<sk.rasheedfarhan@.gmail.com> wrote in message
news:1163512500.562695.183350@.h48g2000cwc.googlegr oups.com...
> Hi all,
> I set the configuration for Merge replication for Subscription on
> one database and
> I have created the Merge replication for publication on another
> machine.
> And I updated columns of subscription database table and I find
> publication machine database table also updated with subscription
> database table, here my problem starts,
> My problem is when I run replication for merge transaction, I have to
> find instance of the Windows on my machine in Performance Viewer (to
> see performance viewer goto control panel ->Administrator->performance
> in this select performance counter as SQL Server: Replication merge)
> but I am not finding the instance
> Please some body help me out.
> Rasheed
>
|||thanks for resopnse Mrs. Hilary cotter
I will explain......i am using SQL Server 2000, when I run replication
(it may be transaction or merge replication) windows components will
run internally.
1) when I was running replication transaction I can see instances in
Performance
viewer but
2) when I was running replication merge I cant see instances in
Performance
viewer
so my doubt is for replication merge can we have the Instance or not ?

Replication merge instance is not find in Performance Viewer

Hi all,
I set the configuration for Merge replication for Subscription on
one database and
I have created the Merge replication for publication on another
machine.
And I updated columns of subscription database table and I find
publication machine database table also updated with subscription
database table, here my problem starts,
My problem is when I run replication for merge transaction, I have to
find instance of the Windows on my machine in Performance Viewer (to
see performance viewer goto control panel ->Administrator->performance
in this select performance counter as SQL Server: Replication merge)
but I am not finding the instance
Please some body help me out.
RasheedI am not exactly sure what you mean here. In performance monitor the
counters are by instance. The option to select an instance or all instances
is grayed out. This is by design.
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
<sk.rasheedfarhan@.gmail.com> wrote in message
news:1163512500.562695.183350@.h48g2000cwc.googlegroups.com...
> Hi all,
> I set the configuration for Merge replication for Subscription on
> one database and
> I have created the Merge replication for publication on another
> machine.
> And I updated columns of subscription database table and I find
> publication machine database table also updated with subscription
> database table, here my problem starts,
> My problem is when I run replication for merge transaction, I have to
> find instance of the Windows on my machine in Performance Viewer (to
> see performance viewer goto control panel ->Administrator->performance
> in this select performance counter as SQL Server: Replication merge)
> but I am not finding the instance
> Please some body help me out.
> Rasheed
>|||thanks for resopnse Mrs. Hilary cotter
I will explain......i am using SQL Server 2000, when I run replication
(it may be transaction or merge replication) windows components will
run internally.
1) when I was running replication transaction I can see instances in
Performance
viewer but
2) when I was running replication merge I cant see instances in
Performance
viewer
so my doubt is for replication merge can we have the Instance or not ?

Replication looking for errant sysserver links

We have replication successfully running but when I tried to set up a new test instance on completely different servers (Server A to Server B, say) the push agent to create the susbcription on Server B complains

'Could not find server 'Server C' in sysservers. Execute sp_addlinkedserver. . . .'

Server C was never configured as a subscriber for the publication in the first place. What's more, Server C is actually a working linked server and definitely is in sysservers for Server A.

I originally tried this on yet another server pair and got a similar message referencing a server name that no longer exists. I stopped messing with that one, figuring it was some historical naming issue I didn't want to mess with. But now I'm stumped and intrigued. Can anyone help?

Did you deploy your subscriber through a script? Perhaps you forgot to edit the script for the new topology.|||Unfortunately, no. I just used the Wizards, and specified everything. Bit bewildering.|||Do "select * from sysservers" to see if you have any rogue entries in there. You can remove the incorrect ones via sp_dropserver.|||

The supposedly unlinked server is definitely linked, and shows up correctly in the sysserver collection, which also looks fine.

It's wierd, but it's only a test instance I was trying to set up, Our live replication is working fine and we didn't really need the test this time.

I think these servers went through name changes. It probably had the server linked under its old name. Just a guess, but we had a lot of problems changing server names when our corporation was absorbed into a larger IT force and we had to follow a new naming convention. Replication broke, of course, and I recall we needed to clean out various tables before we could reimplement. So I'm guessing it's something along those lines.

Replication Isues After upgrading subscriber to 2005

Hi There

A while ago i migrated a 2000 database to a new instance of sql server 2005 and succesffully setup replication from a 2000 publisher.

However to experiment i took an existing 2000 instance with a subscriber databases and upgraded the default instance to 2005. (Upgraded from 2000 Enterprise to 2005 Enterprise)

After the upgrade the setup logs were all successful, i also run upgrade advisor before and made sure there were no issues.

To see what happens i did not drop the subscription before upgrade (is this an absolute neccessity ?)

All i did was stop the distribution agent before upgrade, to see what would happen, i did not expect it to work.

But since the upgrade i cannot delete the subscription on the subscriber after upgrade, i also cannot create a new subscriptions on the upgraded subscriber.

I get the following error:

Sql Server error 207: column job_step_uid not found.

I found the following link :

http://support.microsoft.com/kb/914781/en-us

but it only refers to desktop editions and express, they refer to the msdb database not upgrading, but the upgrades logs are clear i see no evidence of an issue when upgrading msdb.

What exactly is going on? How can i delete the old subsciption and start creating new ones in the upgraded subscriber ?

I have read the Upgrading replicated DB's topic in BOL, no mention of having to drop subscription before upgrade although i suspect so, all it mentions scripting out the replication , changing the scripts , dropping the subscription (no mention of before or after upgrade), and re-creating them. (all of these i am unable to do due to the above error.)

Please assist, thanx

Hi

Ok i have since ound out that job_step_uid is a new column on the MSdistribution_agents table in the distribution database.

But i have not upgraded the distributor and i dont have to, as mentioned i have setup replication successfully with a 2005 database to a 2000 publicationa nd distributor without any issues, i have no idea why this time is seem to think the distributor is 2005 ?

So i am still stumped i know where it thinks this column should be, but no idea why i am getting this error all of a sudden, no where during subscription setup is the publisher or distributor required to be 2005?

ANyone ?

|||I lost track - what version is your publisher, distributor and subscriber? The supported option is that the version of the distributor has to be greater than or equal to the version of the publisher. For Merge replication, it goes one step further, where the version of the publisher has to be greater than or equal to the subscriber. There's more information about this in Books Online.|||

Hi Greg

I have consequently found out through a microsoft case that sp_vupgrade_replication and sp_vupgrade_repl_metadata procs may have to be run after upgrade.

Will postback if this resolves the issue.

Thanx

|||Running these 2 procs resolved the issue.|||

greetings.

i am having the same problem you were facing. i am trying to re-setup a publication, but it will fail with an error 207, invalid column name 'job_step_uid'.

i have installed service pack 2, and it didn't solve anything. i was able to run the first proc mentioned, the second proc however, didn't seem to work (the text was black, as opposed to the other proc which was highlighted in crimson).

any ideas, guys?

thanks.

Replication Isues After upgrading subscriber to 2005

Hi There

A while ago i migrated a 2000 database to a new instance of sql server 2005 and succesffully setup replication from a 2000 publisher.

However to experiment i took an existing 2000 instance with a subscriber databases and upgraded the default instance to 2005. (Upgraded from 2000 Enterprise to 2005 Enterprise)

After the upgrade the setup logs were all successful, i also run upgrade advisor before and made sure there were no issues.

To see what happens i did not drop the subscription before upgrade (is this an absolute neccessity ?)

All i did was stop the distribution agent before upgrade, to see what would happen, i did not expect it to work.

But since the upgrade i cannot delete the subscription on the subscriber after upgrade, i also cannot create a new subscriptions on the upgraded subscriber.

I get the following error:

Sql Server error 207: column job_step_uid not found.

I found the following link :

http://support.microsoft.com/kb/914781/en-us

but it only refers to desktop editions and express, they refer to the msdb database not upgrading, but the upgrades logs are clear i see no evidence of an issue when upgrading msdb.

What exactly is going on? How can i delete the old subsciption and start creating new ones in the upgraded subscriber ?

I have read the Upgrading replicated DB's topic in BOL, no mention of having to drop subscription before upgrade although i suspect so, all it mentions scripting out the replication , changing the scripts , dropping the subscription (no mention of before or after upgrade), and re-creating them. (all of these i am unable to do due to the above error.)

Please assist, thanx

Hi

Ok i have since ound out that job_step_uid is a new column on the MSdistribution_agents table in the distribution database.

But i have not upgraded the distributor and i dont have to, as mentioned i have setup replication successfully with a 2005 database to a 2000 publicationa nd distributor without any issues, i have no idea why this time is seem to think the distributor is 2005 ?

So i am still stumped i know where it thinks this column should be, but no idea why i am getting this error all of a sudden, no where during subscription setup is the publisher or distributor required to be 2005?

ANyone ?

|||I lost track - what version is your publisher, distributor and subscriber? The supported option is that the version of the distributor has to be greater than or equal to the version of the publisher. For Merge replication, it goes one step further, where the version of the publisher has to be greater than or equal to the subscriber. There's more information about this in Books Online.|||

Hi Greg

I have consequently found out through a microsoft case that sp_vupgrade_replication and sp_vupgrade_repl_metadata procs may have to be run after upgrade.

Will postback if this resolves the issue.

Thanx

|||Running these 2 procs resolved the issue.|||

greetings.

i am having the same problem you were facing. i am trying to re-setup a publication, but it will fail with an error 207, invalid column name 'job_step_uid'.

i have installed service pack 2, and it didn't solve anything. i was able to run the first proc mentioned, the second proc however, didn't seem to work (the text was black, as opposed to the other proc which was highlighted in crimson).

any ideas, guys?

thanks.

Replication in cluster environment problem

1) I have the Active/Passive cluster environment (using Microsoft cluster service).
2) SQL2000 is installed on the cluster (Virtual instance).
3) Virtual instance of SQL Server is used as Publisher in Transactional replication with updatable subscribers.
4) Subscribers are passing updates to subscriber using MSMQ
5) The problem is : after I establish replication , the Queue Agent fails with following error (Failed to initiate search for MSMQs)Did you define your MSMQ for your queue agent? My guess is not. That's why you got the error.|||I have defined MSMQ resource at "Cluster Managager".
The settings that i was used were taken from microsoft article.
MSMQ have dependeny the same "Network name" as SQL Server.
So if the question "Did you define your MSMQ for your queue agent"
is about that , the answer is yes.
Probably i have not understood your question.