We have a database running with about 40 users per branch (6 branches). The replication we are running at the moment (MERGE) often causes our systems to deadlock, upon which the only way to fix it is to kill the BLOCKING process.
I would like to set up an alert that can notify me when a deadlock occurs, but so far the only success i have is on a normal lock - which occus hundreds of times per second.
If anyone can help me out it will be of great help. My e-mail address is it@.bex.co.za
Thanx,
NeilRE: We have a database running with about 40 users per branch (6 branches). The replication we are running at the moment (MERGE) often causes our systems to deadlock, upon which the only way to fix it is to kill the BLOCKING process.
I would like to set up an alert that can notify me when a deadlock occurs, but so far the only success i have is on a normal lock - which occus hundreds of times per second.
If anyone can help me out it will be of great help. My e-mail address is it@.bex.co.za
Thanx,
Neil
From EM:
(Expand out to Alerts)
Sql Server Instance:
Management:
Alerts:
(right click Alerts, select)--> New Alert:
Name: [Deadlocks/sec]
Type: (select from dropdown)--> Sql Server Performance conditon alert
Object: (select from dropdown)--> YourInstanceName:Locks
Counter: (select from dropdown)--> Number of Deadlocks/sec
Alert if counter: (select from dropdown)--> rises above
Value: [0]
Showing posts with label replication. Show all posts
Showing posts with label replication. Show all posts
Tuesday, March 20, 2012
Monday, March 19, 2012
aInformation on dbreplicator
I cannot find any information in msdn or in sql server online books on
dbreplicator.
When I turned on the replication, the user profile which sql server switched
over to dbreplicator with an unknow default password. We want to lock down
the servers but do not want to restrict replication. Is there documentation
on this anywhere?
I have never heard of dbreplicator. Are you prehaps referring to
repl_distributor?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Wayhip" <Wayhip@.discussions.microsoft.com> wrote in message
news:17F589FE-D3CD-410D-B4A1-66CB3FEE4D85@.microsoft.com...
> I cannot find any information in msdn or in sql server online books on
> dbreplicator.
> When I turned on the replication, the user profile which sql server
switched
> over to dbreplicator with an unknow default password. We want to lock down
> the servers but do not want to restrict replication. Is there
documentation
> on this anywhere?
|||To clarify:
When I install sql server on a machine I usually default to having it run
under the system account. This is the main OS account which many processes
run under. However, I have noticed that when I set up replication that there
is another account , local to the machine, called ".\dbreplicator" which
suddenly appears with a password which I did not provide. I have looked for
information but it seems undocumented. On one of my servers I have since
flipped the setting back to using system with apparently no ill effects. So I
am looking for more information and when dbreplicator would be important.
"Hilary Cotter" wrote:
> I have never heard of dbreplicator. Are you prehaps referring to
> repl_distributor?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
|||still don't see this account. Sorry.
Are you running sybase sql anywhere?
http://groups.google.com/groups?hl=e...3DN%26tab%3Dwg
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Wayhip" <Wayhip@.discussions.microsoft.com> wrote in message
news:E211DF9D-30B4-4AAD-84C8-D3B990EE4EE0@.microsoft.com...
> To clarify:
> When I install sql server on a machine I usually default to having it run
> under the system account. This is the main OS account which many
processes
> run under. However, I have noticed that when I set up replication that
there
> is another account , local to the machine, called ".\dbreplicator" which
> suddenly appears with a password which I did not provide. I have looked
for
> information but it seems undocumented. On one of my servers I have since
> flipped the setting back to using system with apparently no ill effects.
So I
> am looking for more information and when dbreplicator would be important.
>
> "Hilary Cotter" wrote:
>
dbreplicator.
When I turned on the replication, the user profile which sql server switched
over to dbreplicator with an unknow default password. We want to lock down
the servers but do not want to restrict replication. Is there documentation
on this anywhere?
I have never heard of dbreplicator. Are you prehaps referring to
repl_distributor?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Wayhip" <Wayhip@.discussions.microsoft.com> wrote in message
news:17F589FE-D3CD-410D-B4A1-66CB3FEE4D85@.microsoft.com...
> I cannot find any information in msdn or in sql server online books on
> dbreplicator.
> When I turned on the replication, the user profile which sql server
switched
> over to dbreplicator with an unknow default password. We want to lock down
> the servers but do not want to restrict replication. Is there
documentation
> on this anywhere?
|||To clarify:
When I install sql server on a machine I usually default to having it run
under the system account. This is the main OS account which many processes
run under. However, I have noticed that when I set up replication that there
is another account , local to the machine, called ".\dbreplicator" which
suddenly appears with a password which I did not provide. I have looked for
information but it seems undocumented. On one of my servers I have since
flipped the setting back to using system with apparently no ill effects. So I
am looking for more information and when dbreplicator would be important.
"Hilary Cotter" wrote:
> I have never heard of dbreplicator. Are you prehaps referring to
> repl_distributor?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
|||still don't see this account. Sorry.
Are you running sybase sql anywhere?
http://groups.google.com/groups?hl=e...3DN%26tab%3Dwg
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Wayhip" <Wayhip@.discussions.microsoft.com> wrote in message
news:E211DF9D-30B4-4AAD-84C8-D3B990EE4EE0@.microsoft.com...
> To clarify:
> When I install sql server on a machine I usually default to having it run
> under the system account. This is the main OS account which many
processes
> run under. However, I have noticed that when I set up replication that
there
> is another account , local to the machine, called ".\dbreplicator" which
> suddenly appears with a password which I did not provide. I have looked
for
> information but it seems undocumented. On one of my servers I have since
> flipped the setting back to using system with apparently no ill effects.
So I
> am looking for more information and when dbreplicator would be important.
>
> "Hilary Cotter" wrote:
>
Saturday, February 25, 2012
Agents not appearing in Replication Monitor (SQL 2000)
During an upgrade, we had problems with PK errors on sp_MSget_repl_commands. I recreated the SP with the varbinary characteristic as we have seen in many posts and it was sucessful in starting. However, the sp now appears as a 'user' sp, not a 'system' sp, however I an not sure that is a real issue.
The problem I am seeing is that none of the agents are appearing in replication monitor under their agent folders. Replication appears to be working fine and the publications are showing on the distributor in the Replication Monitor - Publication folder under the appropriate publisher. But all of the agent specific folders (i.e. Snapshot agent, distribution agent, etc) are empty.
Another interesting point, is that if replication fails, the failure 'X' shows up on the Agents folder as well as the publisher, but again, no sign of the agent in the folder.
Thoughts?
this could be an upgrade bug where the underlying replication tables weren't updated properly, let me figure out what procs replication monitor calls to see what tables have wrong values.Agents failed to load
Using SQL2005 EE SP1
I'm trying to set up a simple transactional replication with initial
snapshot (1st time on this server). I create the publisher and it says it's
successful, however the error log shows:
Job cannot run because LogReader subsystem failed to load. Job has been
suspended.
Job cannot run because Snapshot subsystem failed to load. Job has been
suspended.
Did I not install the proper components during the SQL2005 install?
Thanks
Ron
Is it possible that you have Express Edition on this box and this is the one
you are using? This is installed with Visual Studion and sometimes people
have got the editions confused. Pls can you run select SERVERPROPERTY (
'edition') to double check, and just in case you are connected to the EE in
SSMS, have a look at your list of services to see what services SQL server
appears under. If this is not the case then I'll see what other things we
could look for.
HTH,
Paul Ibison
|||Paul, it's definitely SQLServer2005 "Enterprise Edition" SP1.
"Paul Ibison" wrote:
> Is it possible that you have Express Edition on this box and this is the one
> you are using? This is installed with Visual Studion and sometimes people
> have got the editions confused. Pls can you run select SERVERPROPERTY (
> 'edition') to double check, and just in case you are connected to the EE in
> SSMS, have a look at your list of services to see what services SQL server
> appears under. If this is not the case then I'll see what other things we
> could look for.
> HTH,
> Paul Ibison
>
|||I'm all set now, I've got it working. Turns out there were earlier errors
from a couple of months ago that stated: Subsystem "LogReader" could not be
loaded
as well as the subsystems.
I googled that and the fix was to update the msdb.dbo.syssubsystems table to
point to the correct DLL and executable.
Thanks anyway.
"Ron" wrote:
[vbcol=seagreen]
> Paul, it's definitely SQLServer2005 "Enterprise Edition" SP1.
> "Paul Ibison" wrote:
|||Hi Ron - thanks for the update. I've found a potential cause of this issue
here: http://support.microsoft.com/kb/914171.
I'll add this to my website.
Cheers,
Paul Ibison
I'm trying to set up a simple transactional replication with initial
snapshot (1st time on this server). I create the publisher and it says it's
successful, however the error log shows:
Job cannot run because LogReader subsystem failed to load. Job has been
suspended.
Job cannot run because Snapshot subsystem failed to load. Job has been
suspended.
Did I not install the proper components during the SQL2005 install?
Thanks
Ron
Is it possible that you have Express Edition on this box and this is the one
you are using? This is installed with Visual Studion and sometimes people
have got the editions confused. Pls can you run select SERVERPROPERTY (
'edition') to double check, and just in case you are connected to the EE in
SSMS, have a look at your list of services to see what services SQL server
appears under. If this is not the case then I'll see what other things we
could look for.
HTH,
Paul Ibison
|||Paul, it's definitely SQLServer2005 "Enterprise Edition" SP1.
"Paul Ibison" wrote:
> Is it possible that you have Express Edition on this box and this is the one
> you are using? This is installed with Visual Studion and sometimes people
> have got the editions confused. Pls can you run select SERVERPROPERTY (
> 'edition') to double check, and just in case you are connected to the EE in
> SSMS, have a look at your list of services to see what services SQL server
> appears under. If this is not the case then I'll see what other things we
> could look for.
> HTH,
> Paul Ibison
>
|||I'm all set now, I've got it working. Turns out there were earlier errors
from a couple of months ago that stated: Subsystem "LogReader" could not be
loaded
as well as the subsystems.
I googled that and the fix was to update the msdb.dbo.syssubsystems table to
point to the correct DLL and executable.
Thanks anyway.
"Ron" wrote:
[vbcol=seagreen]
> Paul, it's definitely SQLServer2005 "Enterprise Edition" SP1.
> "Paul Ibison" wrote:
|||Hi Ron - thanks for the update. I've found a potential cause of this issue
here: http://support.microsoft.com/kb/914171.
I'll add this to my website.
Cheers,
Paul Ibison
agents db rights for trans. repl
We are about to deploy transactional replication with queue updating
subscribers. Both the publisher and distributor are on server A and the
publisher is on server B. We have one publication on the publisher which
will PUSH a small database (around 51 articles) to the subscriber. The
MSSQLSERVER service and the SQLSERVERAGENT services run in the context of
a windows domain account. These services are configured with different
domain accounts on the 2 servers.
- Do the domain accounts require specific rights/permissions on both the SQL
servers and the databases?
- In the properties page for the snapshot agent, log reader agent, queue
reader and distribution agent there is an OWNER setting. Does this has to be
set to the domain accounts for the MSSQLSERVER and SQLSERVERAGENT services
so replication across servers can occur?
Sysadmin on the publisher, dbo on the subscriber for push, for pull sysadmin
on the subscriber and in the pal on the publisher.
I make the job owner sa. It works better for me, when I do this as opposed
to using another account.
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
"newsgroupuser" <abraxas1969@.hotmail.com> wrote in message
news:OAQA%23EPRFHA.1564@.TK2MSFTNGP09.phx.gbl...
> We are about to deploy transactional replication with queue updating
> subscribers. Both the publisher and distributor are on server A and the
> publisher is on server B. We have one publication on the publisher which
> will PUSH a small database (around 51 articles) to the subscriber. The
> MSSQLSERVER service and the SQLSERVERAGENT services run in the context
> of
> a windows domain account. These services are configured with different
> domain accounts on the 2 servers.
>
> - Do the domain accounts require specific rights/permissions on both the
> SQL
> servers and the databases?
>
> - In the properties page for the snapshot agent, log reader agent, queue
> reader and distribution agent there is an OWNER setting. Does this has to
> be
> set to the domain accounts for the MSSQLSERVER and SQLSERVERAGENT services
> so replication across servers can occur?
>
>
subscribers. Both the publisher and distributor are on server A and the
publisher is on server B. We have one publication on the publisher which
will PUSH a small database (around 51 articles) to the subscriber. The
MSSQLSERVER service and the SQLSERVERAGENT services run in the context of
a windows domain account. These services are configured with different
domain accounts on the 2 servers.
- Do the domain accounts require specific rights/permissions on both the SQL
servers and the databases?
- In the properties page for the snapshot agent, log reader agent, queue
reader and distribution agent there is an OWNER setting. Does this has to be
set to the domain accounts for the MSSQLSERVER and SQLSERVERAGENT services
so replication across servers can occur?
Sysadmin on the publisher, dbo on the subscriber for push, for pull sysadmin
on the subscriber and in the pal on the publisher.
I make the job owner sa. It works better for me, when I do this as opposed
to using another account.
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
"newsgroupuser" <abraxas1969@.hotmail.com> wrote in message
news:OAQA%23EPRFHA.1564@.TK2MSFTNGP09.phx.gbl...
> We are about to deploy transactional replication with queue updating
> subscribers. Both the publisher and distributor are on server A and the
> publisher is on server B. We have one publication on the publisher which
> will PUSH a small database (around 51 articles) to the subscriber. The
> MSSQLSERVER service and the SQLSERVERAGENT services run in the context
> of
> a windows domain account. These services are configured with different
> domain accounts on the 2 servers.
>
> - Do the domain accounts require specific rights/permissions on both the
> SQL
> servers and the databases?
>
> - In the properties page for the snapshot agent, log reader agent, queue
> reader and distribution agent there is an OWNER setting. Does this has to
> be
> set to the domain accounts for the MSSQLSERVER and SQLSERVERAGENT services
> so replication across servers can occur?
>
>
Friday, February 24, 2012
Agent History?
Hi,
I Have merge replication on SQL 2000, it was setup to run each 1 minute.
I changed the schedule to be continuous instead if 1 minute.
before the change I was able to see all the history in the merge history agent,(an entry each 1 minute).
In the new schedule :All the history is being displayed for one of the merge agent. (I have more then 1 replication)
in another merge agent there's only one entry in the history agent (No data to be merged), When I double click that entry I see all the history.
anyone have any idea how to show all the history in the agent history?
any Help will be appreciated!
Abdu.
A session is basically the period between the merge agent starting and
stopping. The merge history is logged as one entry per session.
So, if you run a continuous merge agent then that is seen as one session
and unfortunately it is logged as one too.
Reinout Hillmann
SQL Server Product Unit
This posting is provided "AS IS" with no warranties, and confers no rights.
Abdu wrote:
> Hi,
> I Have merge replication on SQL 2000, it was setup to run each 1 minute.
> I changed the schedule to be continuous instead if 1 minute.
> before the change I was able to see all the history in the merge history agent,(an entry each 1 minute).
> In the new schedule :All the history is being displayed for one of the merge agent. (I have more then 1 replication)
> in another merge agent there's only one entry in the history agent (No data to be merged), When I double click that entry I see all the history.
> anyone have any idea how to show all the history in the agent history?
> any Help will be appreciated!
> Abdu.
>
I Have merge replication on SQL 2000, it was setup to run each 1 minute.
I changed the schedule to be continuous instead if 1 minute.
before the change I was able to see all the history in the merge history agent,(an entry each 1 minute).
In the new schedule :All the history is being displayed for one of the merge agent. (I have more then 1 replication)
in another merge agent there's only one entry in the history agent (No data to be merged), When I double click that entry I see all the history.
anyone have any idea how to show all the history in the agent history?
any Help will be appreciated!
Abdu.
A session is basically the period between the merge agent starting and
stopping. The merge history is logged as one entry per session.
So, if you run a continuous merge agent then that is seen as one session
and unfortunately it is logged as one too.
Reinout Hillmann
SQL Server Product Unit
This posting is provided "AS IS" with no warranties, and confers no rights.
Abdu wrote:
> Hi,
> I Have merge replication on SQL 2000, it was setup to run each 1 minute.
> I changed the schedule to be continuous instead if 1 minute.
> before the change I was able to see all the history in the merge history agent,(an entry each 1 minute).
> In the new schedule :All the history is being displayed for one of the merge agent. (I have more then 1 replication)
> in another merge agent there's only one entry in the history agent (No data to be merged), When I double click that entry I see all the history.
> anyone have any idea how to show all the history in the agent history?
> any Help will be appreciated!
> Abdu.
>
Agent Deadlock Errors
I have been getting numerous deadlock errors in my Queued Reader agent and
Distribution clean up agent.
We are running transactional replication with immediate updating
subscribers-with queued updating as failover. We have one primary server
with 9 subscribers.
My Queue Agent is set to run continuously.
My Distribution clean up agent is set to run every 5 minutes.
I have also implemented a scheduled job that runs every two hours to update
the statistics in the distribution database.
Is there any way to eliminate these deadlocks?
Thanks,
David Grau
Surprise & Delight
Queued replication is not really scalable beyond 10 subscribers, you may be
hitting its upper limit. I would suggest you run the queued agents on a
schedule, every 5 minutes or so and perhaps stagger their schedules. Also
Close down Enterprise Manager as much as possible as this causes locking
with the replication agents (actually its the refresh rate which does this).
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
"David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
news:DD11621D-2E56-42C6-B1B2-7AC8B8234F5F@.microsoft.com...
>I have been getting numerous deadlock errors in my Queued Reader agent and
> Distribution clean up agent.
> We are running transactional replication with immediate updating
> subscribers-with queued updating as failover. We have one primary server
> with 9 subscribers.
> My Queue Agent is set to run continuously.
> My Distribution clean up agent is set to run every 5 minutes.
> I have also implemented a scheduled job that runs every two hours to
> update
> the statistics in the distribution database.
> Is there any way to eliminate these deadlocks?
> Thanks,
> David Grau
> --
> Surprise & Delight
|||Thanks for your answers.
Is it appropriate to regularly update the statistics on tables in the
distribution database? We have an very active environment, so I figured it
wouldn't hurt. What do you think?
By the way, I read your book on Trans. Replication from cover to cover.
Great information in there. Is your book on Merge Replication out yet?
Surprise & Delight
"Hilary Cotter" wrote:
> Queued replication is not really scalable beyond 10 subscribers, you may be
> hitting its upper limit. I would suggest you run the queued agents on a
> schedule, every 5 minutes or so and perhaps stagger their schedules. Also
> Close down Enterprise Manager as much as possible as this causes locking
> with the replication agents (actually its the refresh rate which does this).
> --
> 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
> "David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
> news:DD11621D-2E56-42C6-B1B2-7AC8B8234F5F@.microsoft.com...
>
>
|||Thanks - I don't like to update statistics as it causes locking. Statistics
is necessary for updating the execution plan - but this plan should be
pretty static for replication operations.
The merge book is in the works still.
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
"David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
news:FA06ADC2-0441-491A-BB8E-32B32F063DC8@.microsoft.com...[vbcol=seagreen]
> Thanks for your answers.
> Is it appropriate to regularly update the statistics on tables in the
> distribution database? We have an very active environment, so I figured
> it
> wouldn't hurt. What do you think?
> By the way, I read your book on Trans. Replication from cover to cover.
> Great information in there. Is your book on Merge Replication out yet?
> --
> Surprise & Delight
>
> "Hilary Cotter" wrote:
|||Just where in the hell are you getting THAT information? Where's your proof
that it doesn't scale beyond 10 subscribers? That would mean one of my
customers implementations is completely impossible, because they have 98
subscribers doing queued updating right now.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uxQSahuEGHA.2292@.tk2msftngp13.phx.gbl...
> Queued replication is not really scalable beyond 10 subscribers, you may
> be hitting its upper limit. I would suggest you run the queued agents on a
> schedule, every 5 minutes or so and perhaps stagger their schedules. Also
> Close down Enterprise Manager as much as possible as this causes locking
> with the replication agents (actually its the refresh rate which does
> this).
> --
> 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
> "David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
> news:DD11621D-2E56-42C6-B1B2-7AC8B8234F5F@.microsoft.com...
>
|||If you use Enterprise Manager, make sure that you are NOT polling for
status. Painting that little red X in EM causes a LOT of blocking which
contributes to your deadlock problems. If you are using queued updating as
a failover, I wouldn't run it on a continuous basis. That's because all of
your transactions are going through immediate updating. If you move your
queued agent to run on a staggered schedule to the distribution clean up,
most of your deadlocks are going to disappear.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
news:DD11621D-2E56-42C6-B1B2-7AC8B8234F5F@.microsoft.com...
>I have been getting numerous deadlock errors in my Queued Reader agent and
> Distribution clean up agent.
> We are running transactional replication with immediate updating
> subscribers-with queued updating as failover. We have one primary server
> with 9 subscribers.
> My Queue Agent is set to run continuously.
> My Distribution clean up agent is set to run every 5 minutes.
> I have also implemented a scheduled job that runs every two hours to
> update
> the statistics in the distribution database.
> Is there any way to eliminate these deadlocks?
> Thanks,
> David Grau
> --
> Surprise & Delight
|||Phil Vaughn and experience.
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
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:uWt1i2JFGHA.3984@.TK2MSFTNGP14.phx.gbl...
> Just where in the hell are you getting THAT information? Where's your
> proof that it doesn't scale beyond 10 subscribers? That would mean one of
> my customers implementations is completely impossible, because they have
> 98 subscribers doing queued updating right now.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uxQSahuEGHA.2292@.tk2msftngp13.phx.gbl...
>
|||Thank you both for responding. The Agent failures have dropped in frequency,
but have not been eliminated.
Here is what I have now changed:
1. Run a scheduled job once a day, at 12:00 noon to update the statistics
in the distribution database.
2. Changed the History Cleanup Agent to run every 10 minutes, starting at
12:01.
3. Changed the Distribution Cleanup Agent to run every 5 minutes, starting
at 12:02
4. Changed the Replication Agent Checkup to run every 10 minutes, starting
at 12:03
5. Changed the Queued Reader Agent to run every 5 minutes, starting at 12:04
6. Turned off the auto-refresh of the Replication Monitor on my Enterprise
Manager and also on the server's Enterprise Manager.
I'm still getting the random deadlock failure of the Distribution Cleanup
Agent a half-dozen times a day. Even though it restarts, it is annoying to
those of us on-call folks that have to respond to the failures. Have I done
everything possible?
In our environment, the publisher and distributor are on the same server.
I'm interested in both your opinions about keeping them on the same server or
moving the distributor to a separate dedicated server. What metrics should I
be looking at to decide when it's time to separate them?
Thanks,
David Grau
Surprise & Delight
"Michael Hotek" wrote:
> If you use Enterprise Manager, make sure that you are NOT polling for
> status. Painting that little red X in EM causes a LOT of blocking which
> contributes to your deadlock problems. If you are using queued updating as
> a failover, I wouldn't run it on a continuous basis. That's because all of
> your transactions are going through immediate updating. If you move your
> queued agent to run on a staggered schedule to the distribution clean up,
> most of your deadlocks are going to disappear.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
> news:DD11621D-2E56-42C6-B1B2-7AC8B8234F5F@.microsoft.com...
>
>
|||Thank you both for responding. The Agent failures have dropped in frequency,
but have not been eliminated.
Here is what I have now changed:
1. Run a scheduled job once a day, at 12:00 noon to update the statistics
in the distribution database.
2. Changed the History Cleanup Agent to run every 10 minutes, starting at
12:01.
3. Changed the Distribution Cleanup Agent to run every 5 minutes, starting
at 12:02
4. Changed the Replication Agent Checkup to run every 10 minutes, starting
at 12:03
5. Changed the Queued Reader Agent to run every 5 minutes, starting at 12:04
6. Turned off the auto-refresh of the Replication Monitor on my Enterprise
Manager and also on the server's Enterprise Manager.
I'm still getting the random deadlock failure of the Distribution Cleanup
Agent a half-dozen times a day. Even though it restarts, it is annoying to
those of us on-call folks that have to respond to the failures. Have I done
everything possible?
In our environment, the publisher and distributor are on the same server.
I'm interested in both your opinions about keeping them on the same server or
moving the distributor to a separate dedicated server. What metrics should I
be looking at to decide when it's time to separate them?
Thanks,
David Grau
Surprise & Delight
"Hilary Cotter" wrote:
> Thanks - I don't like to update statistics as it causes locking. Statistics
> is necessary for updating the execution plan - but this plan should be
> pretty static for replication operations.
> The merge book is in the works still.
> --
> 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
> "David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
> news:FA06ADC2-0441-491A-BB8E-32B32F063DC8@.microsoft.com...
>
>
|||I am not sure about updating the statistics in the distribution database.
sp_MSdistribution_cleanup (the proc behind the distribution clean up agent)
does this anyway. I think all this will do is cause more blocking as it is
being executed.
I would also try run profiler to determine what process the distribution
clean up agent is locking with. Note that the distribution clean up agent is
always going to be the victim in a deadlock situation. You can modify this
behavior by altering the proc and commenting out the set deadlock_priority
low statement. This might raise the visibility of the process which is doing
the locking. Sp_who2 should also reveal this.
How many commands are flowing back and forth? If it is significant I might
also bump up the min_retention from 0 to 1 hour or so and study the impact
of this.
It sounds like your monitoring software is too sensitive and the
distribution cleanup agent can be noisy. Is there any way to make the
software less sensitive to this agent failure. Note that the replication
alerts can be configured to only report every 1 day or so. However you can't
be selective about which agent you want to dampen. You could also write some
logic in the proc used by the distribution clean up agent to only report
once a day.
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
"David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
news:39CE2CC8-8DA4-4F5C-B37C-ACA922FE5108@.microsoft.com...[vbcol=seagreen]
> Thank you both for responding. The Agent failures have dropped in
> frequency,
> but have not been eliminated.
> Here is what I have now changed:
> 1. Run a scheduled job once a day, at 12:00 noon to update the statistics
> in the distribution database.
> 2. Changed the History Cleanup Agent to run every 10 minutes, starting at
> 12:01.
> 3. Changed the Distribution Cleanup Agent to run every 5 minutes,
> starting
> at 12:02
> 4. Changed the Replication Agent Checkup to run every 10 minutes,
> starting
> at 12:03
> 5. Changed the Queued Reader Agent to run every 5 minutes, starting at
> 12:04
> 6. Turned off the auto-refresh of the Replication Monitor on my
> Enterprise
> Manager and also on the server's Enterprise Manager.
> I'm still getting the random deadlock failure of the Distribution Cleanup
> Agent a half-dozen times a day. Even though it restarts, it is annoying
> to
> those of us on-call folks that have to respond to the failures. Have I
> done
> everything possible?
> In our environment, the publisher and distributor are on the same server.
> I'm interested in both your opinions about keeping them on the same server
> or
> moving the distributor to a separate dedicated server. What metrics
> should I
> be looking at to decide when it's time to separate them?
> Thanks,
> David Grau
> --
> Surprise & Delight
>
> "Hilary Cotter" wrote:
Distribution clean up agent.
We are running transactional replication with immediate updating
subscribers-with queued updating as failover. We have one primary server
with 9 subscribers.
My Queue Agent is set to run continuously.
My Distribution clean up agent is set to run every 5 minutes.
I have also implemented a scheduled job that runs every two hours to update
the statistics in the distribution database.
Is there any way to eliminate these deadlocks?
Thanks,
David Grau
Surprise & Delight
Queued replication is not really scalable beyond 10 subscribers, you may be
hitting its upper limit. I would suggest you run the queued agents on a
schedule, every 5 minutes or so and perhaps stagger their schedules. Also
Close down Enterprise Manager as much as possible as this causes locking
with the replication agents (actually its the refresh rate which does this).
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
"David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
news:DD11621D-2E56-42C6-B1B2-7AC8B8234F5F@.microsoft.com...
>I have been getting numerous deadlock errors in my Queued Reader agent and
> Distribution clean up agent.
> We are running transactional replication with immediate updating
> subscribers-with queued updating as failover. We have one primary server
> with 9 subscribers.
> My Queue Agent is set to run continuously.
> My Distribution clean up agent is set to run every 5 minutes.
> I have also implemented a scheduled job that runs every two hours to
> update
> the statistics in the distribution database.
> Is there any way to eliminate these deadlocks?
> Thanks,
> David Grau
> --
> Surprise & Delight
|||Thanks for your answers.
Is it appropriate to regularly update the statistics on tables in the
distribution database? We have an very active environment, so I figured it
wouldn't hurt. What do you think?
By the way, I read your book on Trans. Replication from cover to cover.
Great information in there. Is your book on Merge Replication out yet?
Surprise & Delight
"Hilary Cotter" wrote:
> Queued replication is not really scalable beyond 10 subscribers, you may be
> hitting its upper limit. I would suggest you run the queued agents on a
> schedule, every 5 minutes or so and perhaps stagger their schedules. Also
> Close down Enterprise Manager as much as possible as this causes locking
> with the replication agents (actually its the refresh rate which does this).
> --
> 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
> "David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
> news:DD11621D-2E56-42C6-B1B2-7AC8B8234F5F@.microsoft.com...
>
>
|||Thanks - I don't like to update statistics as it causes locking. Statistics
is necessary for updating the execution plan - but this plan should be
pretty static for replication operations.
The merge book is in the works still.
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
"David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
news:FA06ADC2-0441-491A-BB8E-32B32F063DC8@.microsoft.com...[vbcol=seagreen]
> Thanks for your answers.
> Is it appropriate to regularly update the statistics on tables in the
> distribution database? We have an very active environment, so I figured
> it
> wouldn't hurt. What do you think?
> By the way, I read your book on Trans. Replication from cover to cover.
> Great information in there. Is your book on Merge Replication out yet?
> --
> Surprise & Delight
>
> "Hilary Cotter" wrote:
|||Just where in the hell are you getting THAT information? Where's your proof
that it doesn't scale beyond 10 subscribers? That would mean one of my
customers implementations is completely impossible, because they have 98
subscribers doing queued updating right now.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uxQSahuEGHA.2292@.tk2msftngp13.phx.gbl...
> Queued replication is not really scalable beyond 10 subscribers, you may
> be hitting its upper limit. I would suggest you run the queued agents on a
> schedule, every 5 minutes or so and perhaps stagger their schedules. Also
> Close down Enterprise Manager as much as possible as this causes locking
> with the replication agents (actually its the refresh rate which does
> this).
> --
> 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
> "David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
> news:DD11621D-2E56-42C6-B1B2-7AC8B8234F5F@.microsoft.com...
>
|||If you use Enterprise Manager, make sure that you are NOT polling for
status. Painting that little red X in EM causes a LOT of blocking which
contributes to your deadlock problems. If you are using queued updating as
a failover, I wouldn't run it on a continuous basis. That's because all of
your transactions are going through immediate updating. If you move your
queued agent to run on a staggered schedule to the distribution clean up,
most of your deadlocks are going to disappear.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
news:DD11621D-2E56-42C6-B1B2-7AC8B8234F5F@.microsoft.com...
>I have been getting numerous deadlock errors in my Queued Reader agent and
> Distribution clean up agent.
> We are running transactional replication with immediate updating
> subscribers-with queued updating as failover. We have one primary server
> with 9 subscribers.
> My Queue Agent is set to run continuously.
> My Distribution clean up agent is set to run every 5 minutes.
> I have also implemented a scheduled job that runs every two hours to
> update
> the statistics in the distribution database.
> Is there any way to eliminate these deadlocks?
> Thanks,
> David Grau
> --
> Surprise & Delight
|||Phil Vaughn and experience.
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
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:uWt1i2JFGHA.3984@.TK2MSFTNGP14.phx.gbl...
> Just where in the hell are you getting THAT information? Where's your
> proof that it doesn't scale beyond 10 subscribers? That would mean one of
> my customers implementations is completely impossible, because they have
> 98 subscribers doing queued updating right now.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uxQSahuEGHA.2292@.tk2msftngp13.phx.gbl...
>
|||Thank you both for responding. The Agent failures have dropped in frequency,
but have not been eliminated.
Here is what I have now changed:
1. Run a scheduled job once a day, at 12:00 noon to update the statistics
in the distribution database.
2. Changed the History Cleanup Agent to run every 10 minutes, starting at
12:01.
3. Changed the Distribution Cleanup Agent to run every 5 minutes, starting
at 12:02
4. Changed the Replication Agent Checkup to run every 10 minutes, starting
at 12:03
5. Changed the Queued Reader Agent to run every 5 minutes, starting at 12:04
6. Turned off the auto-refresh of the Replication Monitor on my Enterprise
Manager and also on the server's Enterprise Manager.
I'm still getting the random deadlock failure of the Distribution Cleanup
Agent a half-dozen times a day. Even though it restarts, it is annoying to
those of us on-call folks that have to respond to the failures. Have I done
everything possible?
In our environment, the publisher and distributor are on the same server.
I'm interested in both your opinions about keeping them on the same server or
moving the distributor to a separate dedicated server. What metrics should I
be looking at to decide when it's time to separate them?
Thanks,
David Grau
Surprise & Delight
"Michael Hotek" wrote:
> If you use Enterprise Manager, make sure that you are NOT polling for
> status. Painting that little red X in EM causes a LOT of blocking which
> contributes to your deadlock problems. If you are using queued updating as
> a failover, I wouldn't run it on a continuous basis. That's because all of
> your transactions are going through immediate updating. If you move your
> queued agent to run on a staggered schedule to the distribution clean up,
> most of your deadlocks are going to disappear.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
> news:DD11621D-2E56-42C6-B1B2-7AC8B8234F5F@.microsoft.com...
>
>
|||Thank you both for responding. The Agent failures have dropped in frequency,
but have not been eliminated.
Here is what I have now changed:
1. Run a scheduled job once a day, at 12:00 noon to update the statistics
in the distribution database.
2. Changed the History Cleanup Agent to run every 10 minutes, starting at
12:01.
3. Changed the Distribution Cleanup Agent to run every 5 minutes, starting
at 12:02
4. Changed the Replication Agent Checkup to run every 10 minutes, starting
at 12:03
5. Changed the Queued Reader Agent to run every 5 minutes, starting at 12:04
6. Turned off the auto-refresh of the Replication Monitor on my Enterprise
Manager and also on the server's Enterprise Manager.
I'm still getting the random deadlock failure of the Distribution Cleanup
Agent a half-dozen times a day. Even though it restarts, it is annoying to
those of us on-call folks that have to respond to the failures. Have I done
everything possible?
In our environment, the publisher and distributor are on the same server.
I'm interested in both your opinions about keeping them on the same server or
moving the distributor to a separate dedicated server. What metrics should I
be looking at to decide when it's time to separate them?
Thanks,
David Grau
Surprise & Delight
"Hilary Cotter" wrote:
> Thanks - I don't like to update statistics as it causes locking. Statistics
> is necessary for updating the execution plan - but this plan should be
> pretty static for replication operations.
> The merge book is in the works still.
> --
> 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
> "David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
> news:FA06ADC2-0441-491A-BB8E-32B32F063DC8@.microsoft.com...
>
>
|||I am not sure about updating the statistics in the distribution database.
sp_MSdistribution_cleanup (the proc behind the distribution clean up agent)
does this anyway. I think all this will do is cause more blocking as it is
being executed.
I would also try run profiler to determine what process the distribution
clean up agent is locking with. Note that the distribution clean up agent is
always going to be the victim in a deadlock situation. You can modify this
behavior by altering the proc and commenting out the set deadlock_priority
low statement. This might raise the visibility of the process which is doing
the locking. Sp_who2 should also reveal this.
How many commands are flowing back and forth? If it is significant I might
also bump up the min_retention from 0 to 1 hour or so and study the impact
of this.
It sounds like your monitoring software is too sensitive and the
distribution cleanup agent can be noisy. Is there any way to make the
software less sensitive to this agent failure. Note that the replication
alerts can be configured to only report every 1 day or so. However you can't
be selective about which agent you want to dampen. You could also write some
logic in the proc used by the distribution clean up agent to only report
once a day.
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
"David Grau" <DavidGrau@.discussions.microsoft.com> wrote in message
news:39CE2CC8-8DA4-4F5C-B37C-ACA922FE5108@.microsoft.com...[vbcol=seagreen]
> Thank you both for responding. The Agent failures have dropped in
> frequency,
> but have not been eliminated.
> Here is what I have now changed:
> 1. Run a scheduled job once a day, at 12:00 noon to update the statistics
> in the distribution database.
> 2. Changed the History Cleanup Agent to run every 10 minutes, starting at
> 12:01.
> 3. Changed the Distribution Cleanup Agent to run every 5 minutes,
> starting
> at 12:02
> 4. Changed the Replication Agent Checkup to run every 10 minutes,
> starting
> at 12:03
> 5. Changed the Queued Reader Agent to run every 5 minutes, starting at
> 12:04
> 6. Turned off the auto-refresh of the Replication Monitor on my
> Enterprise
> Manager and also on the server's Enterprise Manager.
> I'm still getting the random deadlock failure of the Distribution Cleanup
> Agent a half-dozen times a day. Even though it restarts, it is annoying
> to
> those of us on-call folks that have to respond to the failures. Have I
> done
> everything possible?
> In our environment, the publisher and distributor are on the same server.
> I'm interested in both your opinions about keeping them on the same server
> or
> moving the distributor to a separate dedicated server. What metrics
> should I
> be looking at to decide when it's time to separate them?
> Thanks,
> David Grau
> --
> Surprise & Delight
>
> "Hilary Cotter" wrote:
Agent & Replication - missing and missed!
I can't believe this. MSDE was a great product and I'm sure that SQL Express is even better. The difference is that I can't migrate any of my company's applications to it because they rely on a Replication architecture (requiring Replication Publication). And SQL Agent, how are you supposed to keep the database in good shape if you can't run Maintenance Plans at scheduled intervals?
I know, there are third party products out there to compensate for the lack of the SQL Agent, but they don't solve the replication problem. Besides, the Agent still exists in other editions - it's not like it has been dropped completely. Why do we have to lose functionality and features? We have already lost the use of a second CPU and memory > 1GB. Why do they need to keep taking stuff out? Why don't they just limit access to the new feature-set?
I would rather lose the new features such as Report Server and Full Text Indexing and get the SQL Agent and Replication Publication back. I can almost understand that with a free product, they don't want to keep piliing new features in just to give them away - even though I'm sure the sale of the larger editions more than covers the R&D investment in their construction. Why can't we chose? Agent or Report Server, Replication or Full Text Indexing? I don't need some of these new features (others might), I just want the features that I used to have. Perhaps SQL 2005 setup can be made to choose 2 out of the 4 features?
Whats more, I can't even say that I'll just stick with MSDE, as it now has a limited support life. Microsoft announced that any SQL Server version prior to 2005 SP1 will NOT be supported on Vista or Longhorn Server (nor does it support anything less than Windows 2000 SP4 or Windows XP SP2 where MSDE does).
OK - let me get this right. We had something that has been taken away in the new version. Perhaps I don't deserve the new version if I don't pay for it - fine - I'll stick to the old one I already have. BUT wait... I can't use that on the new platforms coming out.
Are my entry-level customers going to pay for the Workgroup Edition (add AUD$1200 to my entry level edition application of AUD$1500) to replace the Replication Publisher and provide a Maintenance plan? Is anyone going to pay for the re-architecting and re-construction of my application to work around these new limitations?
I guess that will be me. Is this what happens for being loyal to MS products?
Please - give back the SQL Agent and Replication publication.
Joe
Joe, I completley agree with you, and its really sad that no one from microsoft has bothered to answer your comments.
Agent & Replication - missing and missed!
I can't believe this. MSDE was a great product and I'm sure that SQL Express is even better. The difference is that I can't migrate any of my company's applications to it because they rely on a Replication architecture (requiring Replication Publication). And SQL Agent, how are you supposed to keep the database in good shape if you can't run Maintenance Plans at scheduled intervals?
I know, there are third party products out there to compensate for the lack of the SQL Agent, but they don't solve the replication problem. Besides, the Agent still exists in other editions - it's not like it has been dropped completely. Why do we have to lose functionality and features? We have already lost the use of a second CPU and memory > 1GB. Why do they need to keep taking stuff out? Why don't they just limit access to the new feature-set?
I would rather lose the new features such as Report Server and Full Text Indexing and get the SQL Agent and Replication Publication back. I can almost understand that with a free product, they don't want to keep piliing new features in just to give them away - even though I'm sure the sale of the larger editions more than covers the R&D investment in their construction. Why can't we chose? Agent or Report Server, Replication or Full Text Indexing? I don't need some of these new features (others might), I just want the features that I used to have. Perhaps SQL 2005 setup can be made to choose 2 out of the 4 features?
Whats more, I can't even say that I'll just stick with MSDE, as it now has a limited support life. Microsoft announced that any SQL Server version prior to 2005 SP1 will NOT be supported on Vista or Longhorn Server (nor does it support anything less than Windows 2000 SP4 or Windows XP SP2 where MSDE does).
OK - let me get this right. We had something that has been taken away in the new version. Perhaps I don't deserve the new version if I don't pay for it - fine - I'll stick to the old one I already have. BUT wait... I can't use that on the new platforms coming out.
Are my entry-level customers going to pay for the Workgroup Edition (add AUD$1200 to my entry level edition application of AUD$1500) to replace the Replication Publisher and provide a Maintenance plan? Is anyone going to pay for the re-architecting and re-construction of my application to work around these new limitations?
I guess that will be me. Is this what happens for being loyal to MS products?
Please - give back the SQL Agent and Replication publication.
Joe
Joe, I completley agree with you, and its really sad that no one from microsoft has bothered to answer your comments.
AGAIN Merge Replication with alternate synchronization partner
Hello I'm asking this without having answer:
I followed these instructions to set up replication:
http://support.microsoft.com/default...roduct=sql2k#3
From what I read in the help and everywhere this is soppoused to set the
PublisherB as the alternate synch parter. If I turn off the server
PublisherA and I need Publisher B and Subcriber A to replicate between
themselves while the Publisher A if off, what do I need to set up? What else
do I need to do? Because I turn off the server Publisher A and the
information is not being replicated between the other two servers. Again
we're having a huricane and we need to turn off the server and the other two
sites need to work normally as nothing happens.
Thanks and please help
Jennyfer
you need to set up server b with a copy of the database on server a. Then
you need to create the identical merge publication there as well.
Then you need to configure server b as an alternate publisher of server a.
You do this on server a using the sp_addmergealternatepublisher proc.
Then push your subscription to server c as a pull subscription.
When server a goes offline, you need to point server c to pull from server
b. You do this by right clicking on the merge pull agent, and change the
merge agent parameters to -Publisher ServerB -PublisherDB
PublisherDatabase -Distributor ServerB -SyncToAlternate 1
then restart your merge agent.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jennyfer J Barco" <pdwhitt@.nospam.wdsinc.com> wrote in message
news:OQjj0OrlEHA.3520@.tk2msftngp13.phx.gbl...
> Hello I'm asking this without having answer:
> I followed these instructions to set up replication:
>
http://support.microsoft.com/default...roduct=sql2k#3
> From what I read in the help and everywhere this is soppoused to set the
> PublisherB as the alternate synch parter. If I turn off the server
> PublisherA and I need Publisher B and Subcriber A to replicate between
> themselves while the Publisher A if off, what do I need to set up? What
else
> do I need to do? Because I turn off the server Publisher A and the
> information is not being replicated between the other two servers. Again
> we're having a huricane and we need to turn off the server and the other
two
> sites need to work normally as nothing happens.
> Thanks and please help
> Jennyfer
>
I followed these instructions to set up replication:
http://support.microsoft.com/default...roduct=sql2k#3
From what I read in the help and everywhere this is soppoused to set the
PublisherB as the alternate synch parter. If I turn off the server
PublisherA and I need Publisher B and Subcriber A to replicate between
themselves while the Publisher A if off, what do I need to set up? What else
do I need to do? Because I turn off the server Publisher A and the
information is not being replicated between the other two servers. Again
we're having a huricane and we need to turn off the server and the other two
sites need to work normally as nothing happens.
Thanks and please help
Jennyfer
you need to set up server b with a copy of the database on server a. Then
you need to create the identical merge publication there as well.
Then you need to configure server b as an alternate publisher of server a.
You do this on server a using the sp_addmergealternatepublisher proc.
Then push your subscription to server c as a pull subscription.
When server a goes offline, you need to point server c to pull from server
b. You do this by right clicking on the merge pull agent, and change the
merge agent parameters to -Publisher ServerB -PublisherDB
PublisherDatabase -Distributor ServerB -SyncToAlternate 1
then restart your merge agent.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jennyfer J Barco" <pdwhitt@.nospam.wdsinc.com> wrote in message
news:OQjj0OrlEHA.3520@.tk2msftngp13.phx.gbl...
> Hello I'm asking this without having answer:
> I followed these instructions to set up replication:
>
http://support.microsoft.com/default...roduct=sql2k#3
> From what I read in the help and everywhere this is soppoused to set the
> PublisherB as the alternate synch parter. If I turn off the server
> PublisherA and I need Publisher B and Subcriber A to replicate between
> themselves while the Publisher A if off, what do I need to set up? What
else
> do I need to do? Because I turn off the server Publisher A and the
> information is not being replicated between the other two servers. Again
> we're having a huricane and we need to turn off the server and the other
two
> sites need to work normally as nothing happens.
> Thanks and please help
> Jennyfer
>
Again a "Login failed for user 'null"
Hi,
I'm trying to setup replication between 2 SQL server on 2 different sites.
SQL server A is a W2k SP4 server with SQL7.0 with SQL2000 client tools
SQL server B is a W2k SP4 server with SQL2000
Servers are in mixed mode.
Servers both have MDAC version 2.6 sp2
Username used to login to the server is the administrator and for connection
to SQL is use the sa account.
When I try to start a snapshot agent I get the message "Login failed for
user 'null'. Reason: Not associated with a trusted SQL Server connection"
How can I solve this problem.
kind regards
Michiel BoerHow are you launching the snapshot agent? through command line or sql server
agent?
What is the exact command that you are using?
What account is the SQL agent service running under?
Thanks,
Bala.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Michiel" <meme@.ikkus.com> wrote in message
news:403b5fc8$0$32978$e4fe514c@.dreader15
.news.xs4all.nl...
> Hi,
> I'm trying to setup replication between 2 SQL server on 2 different sites.
> SQL server A is a W2k SP4 server with SQL7.0 with SQL2000 client tools
> SQL server B is a W2k SP4 server with SQL2000
> Servers are in mixed mode.
> Servers both have MDAC version 2.6 sp2
> Username used to login to the server is the administrator and for
connection
> to SQL is use the sa account.
> When I try to start a snapshot agent I get the message "Login failed for
> user 'null'. Reason: Not associated with a trusted SQL Server connection"
> How can I solve this problem.
> kind regards
> Michiel Boer
>
>
I'm trying to setup replication between 2 SQL server on 2 different sites.
SQL server A is a W2k SP4 server with SQL7.0 with SQL2000 client tools
SQL server B is a W2k SP4 server with SQL2000
Servers are in mixed mode.
Servers both have MDAC version 2.6 sp2
Username used to login to the server is the administrator and for connection
to SQL is use the sa account.
When I try to start a snapshot agent I get the message "Login failed for
user 'null'. Reason: Not associated with a trusted SQL Server connection"
How can I solve this problem.
kind regards
Michiel BoerHow are you launching the snapshot agent? through command line or sql server
agent?
What is the exact command that you are using?
What account is the SQL agent service running under?
Thanks,
Bala.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Michiel" <meme@.ikkus.com> wrote in message
news:403b5fc8$0$32978$e4fe514c@.dreader15
.news.xs4all.nl...
> Hi,
> I'm trying to setup replication between 2 SQL server on 2 different sites.
> SQL server A is a W2k SP4 server with SQL7.0 with SQL2000 client tools
> SQL server B is a W2k SP4 server with SQL2000
> Servers are in mixed mode.
> Servers both have MDAC version 2.6 sp2
> Username used to login to the server is the administrator and for
connection
> to SQL is use the sa account.
> When I try to start a snapshot agent I get the message "Login failed for
> user 'null'. Reason: Not associated with a trusted SQL Server connection"
> How can I solve this problem.
> kind regards
> Michiel Boer
>
>
Sunday, February 19, 2012
After SP4 I got 'no such interface supported' in snapshot agent
Hi All,
After SP4 I cannot run merge replication.
Snapshot agent try 10 times to start and on the end
give me a message 'No such interface supported. blalblabla'.
Do You have suggestion ?
Regards,
Tomasz Skorupa
Hi Tomasz,
Assuming you were talking about SQL2000 SP4, such errors can typically be
resolved by doing the following:
regsvr32 "%PrograFiles%\Microsoft SQL Server\80\COM\replagnt.dll"
"%PrograFiles%\Microsoft SQL Server\80\COM\snapshot.exe" /RegServer
"%PrograFiles%\Microsoft SQL Server\80\COM\logread.exe" /RegServer
"%PrograFiles%\Microsoft SQL Server\80\COM\distrib.exe" /RegServer
"%PrograFiles%\Microsoft SQL Server\80\COM\qrdrsvc.exe" /RegServer
"%PrograFiles%\Microsoft SQL Server\80\COM\replmerg.exe" /RegServer
If the above didn't resolve the issue, you may want to check the version of
sqlrepss.dll in the Binn directory of your sql server instance. (Post the
version number here if you don't know whether it looks right or not.)
Now even if you manage to resolve the issue, the bigger mystery here is why
you managed to get your machine in the current state in the first place. It
would be great if you can let us know of any recent unusual activity on the
machine that might have led to the problem. For example, were there any error
messages when you ran the SP4? Have you ever install SQL2005 on the machine
(note: SQL2005 express comes with VS2005 beta also)?
-Raymond
"Tomasz Skorupa" wrote:
> Hi All,
> After SP4 I cannot run merge replication.
> Snapshot agent try 10 times to start and on the end
> give me a message 'No such interface supported. blalblabla'.
> Do You have suggestion ?
> Regards,
> Tomasz Skorupa
>
>
|||Hi Raymond,
First of all, thank You for Your support.
"Raymond Mak [MSFT]" <RaymondMakMSFT@.discussions.microsoft.com> wrote in
message news:E827B5F7-D25A-40B0-AF97-B4D461395139@.microsoft.com...
<...>
> Assuming you were talking about SQL2000 SP4
Yes of course.
> resolved by doing the following:
<...>
> "%PrograFiles%\Microsoft SQL Server\80\COM\replmerg.exe" /RegServer
I have the same problem on other machine with similar software and hardware
configuration.
I reregister this files on both but it not correct problem.
> If the above didn't resolve the issue, you may want to check the version
> of
> sqlrepss.dll in the Binn directory of your sql server instance. (Post the
> version number here if you don't know whether it looks right or not.)
It is 2000.80.2039.0 on both computers.
> Now even if you manage to resolve the issue, the bigger mystery here is
> why
> you managed to get your machine in the current state in the first place.
> It
> would be great if you can let us know of any recent unusual activity on
> the
> machine that might have led to the problem. For example, were there any
> error
> messages when you ran the SP4? Have you ever install SQL2005 on the
> machine
> (note: SQL2005 express comes with VS2005 beta also)?
Our last install before SQL SP4 was MS Virtual PC.
On virtual pc we have SQL Server 2005 and Visual Studio .Net 2005
(Technology Preview).
On both computers SP4 was installed without any error or warning message.
Regards,
Tomasz Skorupa
After SP4 I cannot run merge replication.
Snapshot agent try 10 times to start and on the end
give me a message 'No such interface supported. blalblabla'.
Do You have suggestion ?
Regards,
Tomasz Skorupa
Hi Tomasz,
Assuming you were talking about SQL2000 SP4, such errors can typically be
resolved by doing the following:
regsvr32 "%PrograFiles%\Microsoft SQL Server\80\COM\replagnt.dll"
"%PrograFiles%\Microsoft SQL Server\80\COM\snapshot.exe" /RegServer
"%PrograFiles%\Microsoft SQL Server\80\COM\logread.exe" /RegServer
"%PrograFiles%\Microsoft SQL Server\80\COM\distrib.exe" /RegServer
"%PrograFiles%\Microsoft SQL Server\80\COM\qrdrsvc.exe" /RegServer
"%PrograFiles%\Microsoft SQL Server\80\COM\replmerg.exe" /RegServer
If the above didn't resolve the issue, you may want to check the version of
sqlrepss.dll in the Binn directory of your sql server instance. (Post the
version number here if you don't know whether it looks right or not.)
Now even if you manage to resolve the issue, the bigger mystery here is why
you managed to get your machine in the current state in the first place. It
would be great if you can let us know of any recent unusual activity on the
machine that might have led to the problem. For example, were there any error
messages when you ran the SP4? Have you ever install SQL2005 on the machine
(note: SQL2005 express comes with VS2005 beta also)?
-Raymond
"Tomasz Skorupa" wrote:
> Hi All,
> After SP4 I cannot run merge replication.
> Snapshot agent try 10 times to start and on the end
> give me a message 'No such interface supported. blalblabla'.
> Do You have suggestion ?
> Regards,
> Tomasz Skorupa
>
>
|||Hi Raymond,
First of all, thank You for Your support.
"Raymond Mak [MSFT]" <RaymondMakMSFT@.discussions.microsoft.com> wrote in
message news:E827B5F7-D25A-40B0-AF97-B4D461395139@.microsoft.com...
<...>
> Assuming you were talking about SQL2000 SP4
Yes of course.
> resolved by doing the following:
<...>
> "%PrograFiles%\Microsoft SQL Server\80\COM\replmerg.exe" /RegServer
I have the same problem on other machine with similar software and hardware
configuration.
I reregister this files on both but it not correct problem.
> If the above didn't resolve the issue, you may want to check the version
> of
> sqlrepss.dll in the Binn directory of your sql server instance. (Post the
> version number here if you don't know whether it looks right or not.)
It is 2000.80.2039.0 on both computers.
> Now even if you manage to resolve the issue, the bigger mystery here is
> why
> you managed to get your machine in the current state in the first place.
> It
> would be great if you can let us know of any recent unusual activity on
> the
> machine that might have led to the problem. For example, were there any
> error
> messages when you ran the SP4? Have you ever install SQL2005 on the
> machine
> (note: SQL2005 express comes with VS2005 beta also)?
Our last install before SQL SP4 was MS Virtual PC.
On virtual pc we have SQL Server 2005 and Visual Studio .Net 2005
(Technology Preview).
On both computers SP4 was installed without any error or warning message.
Regards,
Tomasz Skorupa
Thursday, February 16, 2012
After restore db, transactional replication can't delete.
Hi,
Can someone help me with the following situation?
1) I did dump for database test with transactional replication set up on it,
push data from test to test1 db on the same server.
2) Drop the transactional replication of publication for test database.
3) Drop the subscriber db test1.
4) Restore the dump to test db. After that I can't delete the Subscription
at Subscriber 'Server Name' in database 'test1'. Microsoft SQL Server Error:
20032.
Could not delete Publication 'test'. Could not update the distribution db
subscription table. The Subscription status could not be chaned.
How can I do right now? Please help. I want drop them even this is test
server.
The whole steps are for the DR test.
Regards!
Chen
did you try sp_removedbreplication on both the publisher and subscriber in
the publication and subscription dbs?
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
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:5BF8BF2C-49A9-426B-BA50-5E89DA910EAD@.microsoft.com...
> Hi,
> Can someone help me with the following situation?
> 1) I did dump for database test with transactional replication set up on
> it,
> push data from test to test1 db on the same server.
> 2) Drop the transactional replication of publication for test database.
> 3) Drop the subscriber db test1.
> 4) Restore the dump to test db. After that I can't delete the Subscription
> at Subscriber 'Server Name' in database 'test1'. Microsoft SQL Server
> Error:
> 20032.
> Could not delete Publication 'test'. Could not update the distribution db
> subscription table. The Subscription status could not be chaned.
> How can I do right now? Please help. I want drop them even this is test
> server.
> The whole steps are for the DR test.
> Regards!
> Chen
|||Hi Hilary,
I did following steps:
use master
go
-- Remove replication objects from the subscription database on MYSUB.
DECLARE @.subscriptionDB AS sysname
SET @.subscriptionDB = N'test1'
-- Remove replication objects from a subscription database (if necessary).
USE master
EXEC sp_removedbreplication @.subscriptionDB
GO
use distribution
go
exec sp_removedistpublisherdbreplication @.publisher = 'SQMA-AT01'
, @.publisher_db = 'test'
But I still see the test:test
[SQMA-AT01].[test1]
under Local Publications and when I try to delete the same errors occur. How
can I do? Actually this is the SQL Server 2005 with SP1.
Regards!
Chen
"Hilary Cotter" wrote:
> did you try sp_removedbreplication on both the publisher and subscriber in
> the publication and subscription dbs?
> --
> 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
>
> "Chen" <Chen@.discussions.microsoft.com> wrote in message
> news:5BF8BF2C-49A9-426B-BA50-5E89DA910EAD@.microsoft.com...
>
>
|||Hi Hilary,
Problem has been solved after I did sp_removedbreplication on both the
publisher and subscriber.
Thank you so much!
Chen
"Hilary Cotter" wrote:
> did you try sp_removedbreplication on both the publisher and subscriber in
> the publication and subscription dbs?
> --
> 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
>
> "Chen" <Chen@.discussions.microsoft.com> wrote in message
> news:5BF8BF2C-49A9-426B-BA50-5E89DA910EAD@.microsoft.com...
>
>
Can someone help me with the following situation?
1) I did dump for database test with transactional replication set up on it,
push data from test to test1 db on the same server.
2) Drop the transactional replication of publication for test database.
3) Drop the subscriber db test1.
4) Restore the dump to test db. After that I can't delete the Subscription
at Subscriber 'Server Name' in database 'test1'. Microsoft SQL Server Error:
20032.
Could not delete Publication 'test'. Could not update the distribution db
subscription table. The Subscription status could not be chaned.
How can I do right now? Please help. I want drop them even this is test
server.
The whole steps are for the DR test.
Regards!
Chen
did you try sp_removedbreplication on both the publisher and subscriber in
the publication and subscription dbs?
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
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:5BF8BF2C-49A9-426B-BA50-5E89DA910EAD@.microsoft.com...
> Hi,
> Can someone help me with the following situation?
> 1) I did dump for database test with transactional replication set up on
> it,
> push data from test to test1 db on the same server.
> 2) Drop the transactional replication of publication for test database.
> 3) Drop the subscriber db test1.
> 4) Restore the dump to test db. After that I can't delete the Subscription
> at Subscriber 'Server Name' in database 'test1'. Microsoft SQL Server
> Error:
> 20032.
> Could not delete Publication 'test'. Could not update the distribution db
> subscription table. The Subscription status could not be chaned.
> How can I do right now? Please help. I want drop them even this is test
> server.
> The whole steps are for the DR test.
> Regards!
> Chen
|||Hi Hilary,
I did following steps:
use master
go
-- Remove replication objects from the subscription database on MYSUB.
DECLARE @.subscriptionDB AS sysname
SET @.subscriptionDB = N'test1'
-- Remove replication objects from a subscription database (if necessary).
USE master
EXEC sp_removedbreplication @.subscriptionDB
GO
use distribution
go
exec sp_removedistpublisherdbreplication @.publisher = 'SQMA-AT01'
, @.publisher_db = 'test'
But I still see the test:test
[SQMA-AT01].[test1]
under Local Publications and when I try to delete the same errors occur. How
can I do? Actually this is the SQL Server 2005 with SP1.
Regards!
Chen
"Hilary Cotter" wrote:
> did you try sp_removedbreplication on both the publisher and subscriber in
> the publication and subscription dbs?
> --
> 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
>
> "Chen" <Chen@.discussions.microsoft.com> wrote in message
> news:5BF8BF2C-49A9-426B-BA50-5E89DA910EAD@.microsoft.com...
>
>
|||Hi Hilary,
Problem has been solved after I did sp_removedbreplication on both the
publisher and subscriber.
Thank you so much!
Chen
"Hilary Cotter" wrote:
> did you try sp_removedbreplication on both the publisher and subscriber in
> the publication and subscription dbs?
> --
> 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
>
> "Chen" <Chen@.discussions.microsoft.com> wrote in message
> news:5BF8BF2C-49A9-426B-BA50-5E89DA910EAD@.microsoft.com...
>
>
After replication how to TRUNCATE the LOG
After replication, my log has grown upto 300 MB and my Database size is of 20 MB.I want to truncate my LOG.Please let me know how to do it.
Help Needed
Thanks & Regards
Asif
Asif,
have a look at http://support.microsoft.com/?id=272318
hth,
Paul
Help Needed
Thanks & Regards
Asif
Asif,
have a look at http://support.microsoft.com/?id=272318
hth,
Paul
Sunday, February 12, 2012
After corrupted publisher
Hi All,
I am fairly new to MS SQL Replication. I have two servers one is setup
as the publisher and the other is setup as both the subscriber and the
distributor.
Due to server reboots, the publicher DB became corrupted therefore we
pointed all activity to the subscriber DB. Now we would like to bring
back the publisher online, but we do not have a backup.
My Question is, can we restore the publisher DB from the backup of the
subscriber and reinstate replication? if yes could someone please
explain the process as I am new to SQL and replication.
Question two, someone metioned while re-establishing replication I can
regenerate the DB by doing a snapshot. How can I do that on the
publisher.
If you have any other method that will help bring back the publisher
and copy the updated data from the suscriber to the publisher and
re-instate replication that would be great.
PS: the reason why we know the publisher is corrupted is due to a DBCC
CHECKDB(XXX) which gave us a lot of errors.
Thank you much,
Ron
For Question 1: prevent all access to the subscriber database and take a
backup of the publisher and subscriber's database.
Script out the replication setup off the publisher if it is accessible.
Restore on the publisher.
Run sp_removedbreplication on each database.
Obtain your replication script to recreate the publication and subscription
(do you have one?) and edit it to ensure the publication is set up as a
nosync one (for SQL 2000:
http://www.replicationanswers.com/No...lizations.asp, for SQL 2005:
http://www.replicationanswers.com/NoSyncOn2005.asp).
Initialize as per normal.
This means that any tables you had in the publisher's database and not in
the subscriber database will be replaced - is this OK? If not, restore the
publisher database as another name and transfer over the tables/objwects
selectively using DTS.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Ron - answers inline:
> 1: Can I set the subscriber DB to read only?
(1) yes, but when you restore it on the publisher you'll need to set to be
RW to add the publication
> 2: Why do I need a backup of the publisher as it is corrupted.
(2) In case there are tables which aren't in the publication as you'll need
to copy them over into the restored subscriber database later on, assuming
it is addressable in some form or other. Or more simply, just rename it
after scripting out the replication setup.
> 3: By Scripting out the replication off the publisher do you mean right
> click on the on the replication tree in the server where the publisher
> is located and click on Generate SQL Script than click on preview and
> copy the script?
(3) Yes.
> 4: By restore on the publisher do you mean I can restore the backup of
> the subscriber to the publisher?
(4) Yes. When restoring it, you'll give it the same name as the published
database you had previously (the corrupted one). After that, you'll need to
assign permissions to it and then copy in any extra tables.
> 5: You said to run sp_removedbreplication on each database, does that
> mean both publisher and subscriber? are there any other option I
> should use with the sp_removedbreplication?
Yes. No - it's pretty straightforward and just takes one parameter - the
database name.
> 6: Using the replication script how do I recreate the publication and
> subscription? How do I set it up as a nosync on SQL 2000 I did not
> really understand the article.
The replication script should be able to be used as it is with one slight
change: set the @.sync_type = none parameter value of sp_addsubscription ie
change 'Automatic' to 'None'. After that, run
sp_scriptpublicationcustomprocs 'publicationname' at the publisher and
execute the results on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
I am fairly new to MS SQL Replication. I have two servers one is setup
as the publisher and the other is setup as both the subscriber and the
distributor.
Due to server reboots, the publicher DB became corrupted therefore we
pointed all activity to the subscriber DB. Now we would like to bring
back the publisher online, but we do not have a backup.
My Question is, can we restore the publisher DB from the backup of the
subscriber and reinstate replication? if yes could someone please
explain the process as I am new to SQL and replication.
Question two, someone metioned while re-establishing replication I can
regenerate the DB by doing a snapshot. How can I do that on the
publisher.
If you have any other method that will help bring back the publisher
and copy the updated data from the suscriber to the publisher and
re-instate replication that would be great.
PS: the reason why we know the publisher is corrupted is due to a DBCC
CHECKDB(XXX) which gave us a lot of errors.
Thank you much,
Ron
For Question 1: prevent all access to the subscriber database and take a
backup of the publisher and subscriber's database.
Script out the replication setup off the publisher if it is accessible.
Restore on the publisher.
Run sp_removedbreplication on each database.
Obtain your replication script to recreate the publication and subscription
(do you have one?) and edit it to ensure the publication is set up as a
nosync one (for SQL 2000:
http://www.replicationanswers.com/No...lizations.asp, for SQL 2005:
http://www.replicationanswers.com/NoSyncOn2005.asp).
Initialize as per normal.
This means that any tables you had in the publisher's database and not in
the subscriber database will be replaced - is this OK? If not, restore the
publisher database as another name and transfer over the tables/objwects
selectively using DTS.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Ron - answers inline:
> 1: Can I set the subscriber DB to read only?
(1) yes, but when you restore it on the publisher you'll need to set to be
RW to add the publication
> 2: Why do I need a backup of the publisher as it is corrupted.
(2) In case there are tables which aren't in the publication as you'll need
to copy them over into the restored subscriber database later on, assuming
it is addressable in some form or other. Or more simply, just rename it
after scripting out the replication setup.
> 3: By Scripting out the replication off the publisher do you mean right
> click on the on the replication tree in the server where the publisher
> is located and click on Generate SQL Script than click on preview and
> copy the script?
(3) Yes.
> 4: By restore on the publisher do you mean I can restore the backup of
> the subscriber to the publisher?
(4) Yes. When restoring it, you'll give it the same name as the published
database you had previously (the corrupted one). After that, you'll need to
assign permissions to it and then copy in any extra tables.
> 5: You said to run sp_removedbreplication on each database, does that
> mean both publisher and subscriber? are there any other option I
> should use with the sp_removedbreplication?
Yes. No - it's pretty straightforward and just takes one parameter - the
database name.
> 6: Using the replication script how do I recreate the publication and
> subscription? How do I set it up as a nosync on SQL 2000 I did not
> really understand the article.
The replication script should be able to be used as it is with one slight
change: set the @.sync_type = none parameter value of sp_addsubscription ie
change 'Automatic' to 'None'. After that, run
sp_scriptpublicationcustomprocs 'publicationname' at the publisher and
execute the results on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
After corrupted publisher
Hi All,
I am fairly new to MS SQL Replication. I have two servers one is setup
as the publisher and the other is setup as both the subscriber and the
distributor.
Due to server reboots, the publicher DB became corrupted therefore we
pointed all activity to the subscriber DB. Now we would like to bring
back the publisher online, but we do not have a backup.
My Question is, can we restore the publisher DB from the backup of the
subscriber and reinstate replication? if yes could someone please
explain the process as I am new to SQL and replication.
Question two, someone metioned while re-establishing replication I can
regenerate the DB by doing a snapshot. How can I do that on the
publisher.
If you have any other method that will help bring back the publisher
and copy the updated data from the suscriber to the publisher and
re-instate replication that would be great.
PS: the reason why we know the publisher is corrupted is due to a DBCC
CHECKDB(XXX) which gave us a lot of errors.
Thank you much,
RonFor Question 1: prevent all access to the subscriber database and take a
backup of the publisher and subscriber's database.
Script out the replication setup off the publisher if it is accessible.
Restore on the publisher.
Run sp_removedbreplication on each database.
Obtain your replication script to recreate the publication and subscription
(do you have one?) and edit it to ensure the publication is set up as a
nosync one (for SQL 2000:
http://www.replicationanswers.com/N...alizations.asp, for SQL 2005:
http://www.replicationanswers.com/NoSyncOn2005.asp).
Initialize as per normal.
This means that any tables you had in the publisher's database and not in
the subscriber database will be replaced - is this OK? If not, restore the
publisher database as another name and transfer over the tables/objwects
selectively using DTS.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Hi Ron - answers inline:
> 1: Can I set the subscriber DB to read only?
(1) yes, but when you restore it on the publisher you'll need to set to be
RW to add the publication
> 2: Why do I need a backup of the publisher as it is corrupted.
(2) In case there are tables which aren't in the publication as you'll need
to copy them over into the restored subscriber database later on, assuming
it is addressable in some form or other. Or more simply, just rename it
after scripting out the replication setup.
> 3: By Scripting out the replication off the publisher do you mean right
> click on the on the replication tree in the server where the publisher
> is located and click on Generate SQL Script than click on preview and
> copy the script?
(3) Yes.
> 4: By restore on the publisher do you mean I can restore the backup of
> the subscriber to the publisher?
(4) Yes. When restoring it, you'll give it the same name as the published
database you had previously (the corrupted one). After that, you'll need to
assign permissions to it and then copy in any extra tables.
> 5: You said to run sp_removedbreplication on each database, does that
> mean both publisher and subscriber? are there any other option I
> should use with the sp_removedbreplication?
Yes. No - it's pretty straightforward and just takes one parameter - the
database name.
> 6: Using the replication script how do I recreate the publication and
> subscription? How do I set it up as a nosync on SQL 2000 I did not
> really understand the article.
The replication script should be able to be used as it is with one slight
change: set the @.sync_type = none parameter value of sp_addsubscription ie
change 'Automatic' to 'None'. After that, run
sp_scriptpublicationcustomprocs 'publicationname' at the publisher and
execute the results on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
I am fairly new to MS SQL Replication. I have two servers one is setup
as the publisher and the other is setup as both the subscriber and the
distributor.
Due to server reboots, the publicher DB became corrupted therefore we
pointed all activity to the subscriber DB. Now we would like to bring
back the publisher online, but we do not have a backup.
My Question is, can we restore the publisher DB from the backup of the
subscriber and reinstate replication? if yes could someone please
explain the process as I am new to SQL and replication.
Question two, someone metioned while re-establishing replication I can
regenerate the DB by doing a snapshot. How can I do that on the
publisher.
If you have any other method that will help bring back the publisher
and copy the updated data from the suscriber to the publisher and
re-instate replication that would be great.
PS: the reason why we know the publisher is corrupted is due to a DBCC
CHECKDB(XXX) which gave us a lot of errors.
Thank you much,
RonFor Question 1: prevent all access to the subscriber database and take a
backup of the publisher and subscriber's database.
Script out the replication setup off the publisher if it is accessible.
Restore on the publisher.
Run sp_removedbreplication on each database.
Obtain your replication script to recreate the publication and subscription
(do you have one?) and edit it to ensure the publication is set up as a
nosync one (for SQL 2000:
http://www.replicationanswers.com/N...alizations.asp, for SQL 2005:
http://www.replicationanswers.com/NoSyncOn2005.asp).
Initialize as per normal.
This means that any tables you had in the publisher's database and not in
the subscriber database will be replaced - is this OK? If not, restore the
publisher database as another name and transfer over the tables/objwects
selectively using DTS.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Hi Ron - answers inline:
> 1: Can I set the subscriber DB to read only?
(1) yes, but when you restore it on the publisher you'll need to set to be
RW to add the publication
> 2: Why do I need a backup of the publisher as it is corrupted.
(2) In case there are tables which aren't in the publication as you'll need
to copy them over into the restored subscriber database later on, assuming
it is addressable in some form or other. Or more simply, just rename it
after scripting out the replication setup.
> 3: By Scripting out the replication off the publisher do you mean right
> click on the on the replication tree in the server where the publisher
> is located and click on Generate SQL Script than click on preview and
> copy the script?
(3) Yes.
> 4: By restore on the publisher do you mean I can restore the backup of
> the subscriber to the publisher?
(4) Yes. When restoring it, you'll give it the same name as the published
database you had previously (the corrupted one). After that, you'll need to
assign permissions to it and then copy in any extra tables.
> 5: You said to run sp_removedbreplication on each database, does that
> mean both publisher and subscriber? are there any other option I
> should use with the sp_removedbreplication?
Yes. No - it's pretty straightforward and just takes one parameter - the
database name.
> 6: Using the replication script how do I recreate the publication and
> subscription? How do I set it up as a nosync on SQL 2000 I did not
> really understand the article.
The replication script should be able to be used as it is with one slight
change: set the @.sync_type = none parameter value of sp_addsubscription ie
change 'Automatic' to 'None'. After that, run
sp_scriptpublicationcustomprocs 'publicationname' at the publisher and
execute the results on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
After corrupted publisher
Hi All,
I am fairly new to MS SQL Replication. I have two servers one is setup
as the publisher and the other is setup as both the subscriber and the
distributor.
Due to server reboots, the publicher DB became corrupted therefore we
pointed all activity to the subscriber DB. Now we would like to bring
back the publisher online, but we do not have a backup.
My Question is, can we restore the publisher DB from the backup of the
subscriber and reinstate replication? if yes could someone please
explain the process as I am new to SQL and replication.
Question two, someone metioned while re-establishing replication I can
regenerate the DB by doing a snapshot. How can I do that on the
publisher.
If you have any other method that will help bring back the publisher
and copy the updated data from the suscriber to the publisher and
re-instate replication that would be great.
PS: the reason why we know the publisher is corrupted is due to a DBCC
CHECKDB(XXX) which gave us a lot of errors.
Thank you much,
RonFor Question 1: prevent all access to the subscriber database and take a
backup of the publisher and subscriber's database.
Script out the replication setup off the publisher if it is accessible.
Restore on the publisher.
Run sp_removedbreplication on each database.
Obtain your replication script to recreate the publication and subscription
(do you have one?) and edit it to ensure the publication is set up as a
nosync one (for SQL 2000:
http://www.replicationanswers.com/NoSyncInitializations.asp, for SQL 2005:
http://www.replicationanswers.com/NoSyncOn2005.asp).
Initialize as per normal.
This means that any tables you had in the publisher's database and not in
the subscriber database will be replaced - is this OK? If not, restore the
publisher database as another name and transfer over the tables/objwects
selectively using DTS.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Hi Paul,
Thank you for your response.
I have a few question and pardon my lack of understanding as I am new
to SQL and replication.
1: Can I set the subscriber DB to read only?
2: Why do I need a backup of the publisher as it is corrupted.
3: By Scripting out the replication off the publisher do you mean right
click on the on the replication tree in the server where the publisher
is located and click on Generate SQL Script than click on preview and
copy the script?
4: By restore on the publisher do you mean I can restore the backup of
the subscriber to the publisher?
5: You said to run sp_removedbreplication on each database, does that
mean both publisher and subscriber? are there any other option I
should use with the sp_removedbreplication?
6: Using the replication script how do I recreate the publication and
subscription? How do I set it up as a nosync on SQL 2000 I did not
really understand the article.
Thank you very much for you help.
Ron.
------
Paul Ibison wrote:
> For Question 1: prevent all access to the subscriber database and take a
> backup of the publisher and subscriber's database.
> Script out the replication setup off the publisher if it is accessible.
> Restore on the publisher.
> Run sp_removedbreplication on each database.
> Obtain your replication script to recreate the publication and subscription
> (do you have one?) and edit it to ensure the publication is set up as a
> nosync one (for SQL 2000:
> http://www.replicationanswers.com/NoSyncInitializations.asp, for SQL 2005:
> http://www.replicationanswers.com/NoSyncOn2005.asp).
> Initialize as per normal.
> This means that any tables you had in the publisher's database and not in
> the subscriber database will be replaced - is this OK? If not, restore the
> publisher database as another name and transfer over the tables/objwects
> selectively using DTS.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Hi Ron - answers inline:
> 1: Can I set the subscriber DB to read only?
(1) yes, but when you restore it on the publisher you'll need to set to be
RW to add the publication
> 2: Why do I need a backup of the publisher as it is corrupted.
(2) In case there are tables which aren't in the publication as you'll need
to copy them over into the restored subscriber database later on, assuming
it is addressable in some form or other. Or more simply, just rename it
after scripting out the replication setup.
> 3: By Scripting out the replication off the publisher do you mean right
> click on the on the replication tree in the server where the publisher
> is located and click on Generate SQL Script than click on preview and
> copy the script?
(3) Yes.
> 4: By restore on the publisher do you mean I can restore the backup of
> the subscriber to the publisher?
(4) Yes. When restoring it, you'll give it the same name as the published
database you had previously (the corrupted one). After that, you'll need to
assign permissions to it and then copy in any extra tables.
> 5: You said to run sp_removedbreplication on each database, does that
> mean both publisher and subscriber? are there any other option I
> should use with the sp_removedbreplication?
Yes. No - it's pretty straightforward and just takes one parameter - the
database name.
> 6: Using the replication script how do I recreate the publication and
> subscription? How do I set it up as a nosync on SQL 2000 I did not
> really understand the article.
The replication script should be able to be used as it is with one slight
change: set the @.sync_type = none parameter value of sp_addsubscription ie
change 'Automatic' to 'None'. After that, run
sp_scriptpublicationcustomprocs 'publicationname' at the publisher and
execute the results on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
I am fairly new to MS SQL Replication. I have two servers one is setup
as the publisher and the other is setup as both the subscriber and the
distributor.
Due to server reboots, the publicher DB became corrupted therefore we
pointed all activity to the subscriber DB. Now we would like to bring
back the publisher online, but we do not have a backup.
My Question is, can we restore the publisher DB from the backup of the
subscriber and reinstate replication? if yes could someone please
explain the process as I am new to SQL and replication.
Question two, someone metioned while re-establishing replication I can
regenerate the DB by doing a snapshot. How can I do that on the
publisher.
If you have any other method that will help bring back the publisher
and copy the updated data from the suscriber to the publisher and
re-instate replication that would be great.
PS: the reason why we know the publisher is corrupted is due to a DBCC
CHECKDB(XXX) which gave us a lot of errors.
Thank you much,
RonFor Question 1: prevent all access to the subscriber database and take a
backup of the publisher and subscriber's database.
Script out the replication setup off the publisher if it is accessible.
Restore on the publisher.
Run sp_removedbreplication on each database.
Obtain your replication script to recreate the publication and subscription
(do you have one?) and edit it to ensure the publication is set up as a
nosync one (for SQL 2000:
http://www.replicationanswers.com/NoSyncInitializations.asp, for SQL 2005:
http://www.replicationanswers.com/NoSyncOn2005.asp).
Initialize as per normal.
This means that any tables you had in the publisher's database and not in
the subscriber database will be replaced - is this OK? If not, restore the
publisher database as another name and transfer over the tables/objwects
selectively using DTS.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Hi Paul,
Thank you for your response.
I have a few question and pardon my lack of understanding as I am new
to SQL and replication.
1: Can I set the subscriber DB to read only?
2: Why do I need a backup of the publisher as it is corrupted.
3: By Scripting out the replication off the publisher do you mean right
click on the on the replication tree in the server where the publisher
is located and click on Generate SQL Script than click on preview and
copy the script?
4: By restore on the publisher do you mean I can restore the backup of
the subscriber to the publisher?
5: You said to run sp_removedbreplication on each database, does that
mean both publisher and subscriber? are there any other option I
should use with the sp_removedbreplication?
6: Using the replication script how do I recreate the publication and
subscription? How do I set it up as a nosync on SQL 2000 I did not
really understand the article.
Thank you very much for you help.
Ron.
------
Paul Ibison wrote:
> For Question 1: prevent all access to the subscriber database and take a
> backup of the publisher and subscriber's database.
> Script out the replication setup off the publisher if it is accessible.
> Restore on the publisher.
> Run sp_removedbreplication on each database.
> Obtain your replication script to recreate the publication and subscription
> (do you have one?) and edit it to ensure the publication is set up as a
> nosync one (for SQL 2000:
> http://www.replicationanswers.com/NoSyncInitializations.asp, for SQL 2005:
> http://www.replicationanswers.com/NoSyncOn2005.asp).
> Initialize as per normal.
> This means that any tables you had in the publisher's database and not in
> the subscriber database will be replaced - is this OK? If not, restore the
> publisher database as another name and transfer over the tables/objwects
> selectively using DTS.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Hi Ron - answers inline:
> 1: Can I set the subscriber DB to read only?
(1) yes, but when you restore it on the publisher you'll need to set to be
RW to add the publication
> 2: Why do I need a backup of the publisher as it is corrupted.
(2) In case there are tables which aren't in the publication as you'll need
to copy them over into the restored subscriber database later on, assuming
it is addressable in some form or other. Or more simply, just rename it
after scripting out the replication setup.
> 3: By Scripting out the replication off the publisher do you mean right
> click on the on the replication tree in the server where the publisher
> is located and click on Generate SQL Script than click on preview and
> copy the script?
(3) Yes.
> 4: By restore on the publisher do you mean I can restore the backup of
> the subscriber to the publisher?
(4) Yes. When restoring it, you'll give it the same name as the published
database you had previously (the corrupted one). After that, you'll need to
assign permissions to it and then copy in any extra tables.
> 5: You said to run sp_removedbreplication on each database, does that
> mean both publisher and subscriber? are there any other option I
> should use with the sp_removedbreplication?
Yes. No - it's pretty straightforward and just takes one parameter - the
database name.
> 6: Using the replication script how do I recreate the publication and
> subscription? How do I set it up as a nosync on SQL 2000 I did not
> really understand the article.
The replication script should be able to be used as it is with one slight
change: set the @.sync_type = none parameter value of sp_addsubscription ie
change 'Automatic' to 'None'. After that, run
sp_scriptpublicationcustomprocs 'publicationname' at the publisher and
execute the results on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Thursday, February 9, 2012
advise on data replication
Hi,
I have the following situation and would like some advise.
I am using SQL server 2005. I have smart clients access the database
over the internet through web services.
I currently have 1 data warehouse type database. It contains about 95%
read only data which is apended to nightly.
Since the queries are long and complex running on millions of rows of
data, queries are run asyncronously. I use Service Broker to queue and
run the queries.
I have the following read/write tables in the database:
A table to store the results of the queries.
A table to store the status of the request. Front the front end, I can
see if the query is queued, running or completed.
Since the query request itself is very complex, I store the request in
a series of other tables before the request is queued.
Now, for scalability reasons they want to go to 2 database servers,
allowing more requests to be run at the same time. Obviously, in
regards to the read/write tables described above, I need to somehow
replicate the data between the servers. Any thoughts as to which
replication method would best fit my scenario?
Any thoughts would be appreciated.
Thanks in advance
James
Maybe use Partitioned Views for this instead?
"James" <jamesdfriesen@.hotmail.com> wrote in message
news:1142885711.716158.206300@.v46g2000cwv.googlegr oups.com...
> Hi,
> I have the following situation and would like some advise.
> I am using SQL server 2005. I have smart clients access the database
> over the internet through web services.
> I currently have 1 data warehouse type database. It contains about 95%
> read only data which is apended to nightly.
> Since the queries are long and complex running on millions of rows of
> data, queries are run asyncronously. I use Service Broker to queue and
> run the queries.
> I have the following read/write tables in the database:
> A table to store the results of the queries.
> A table to store the status of the request. Front the front end, I can
> see if the query is queued, running or completed.
> Since the query request itself is very complex, I store the request in
> a series of other tables before the request is queued.
> Now, for scalability reasons they want to go to 2 database servers,
> allowing more requests to be run at the same time. Obviously, in
> regards to the read/write tables described above, I need to somehow
> replicate the data between the servers. Any thoughts as to which
> replication method would best fit my scenario?
> Any thoughts would be appreciated.
> Thanks in advance
> James
>
I have the following situation and would like some advise.
I am using SQL server 2005. I have smart clients access the database
over the internet through web services.
I currently have 1 data warehouse type database. It contains about 95%
read only data which is apended to nightly.
Since the queries are long and complex running on millions of rows of
data, queries are run asyncronously. I use Service Broker to queue and
run the queries.
I have the following read/write tables in the database:
A table to store the results of the queries.
A table to store the status of the request. Front the front end, I can
see if the query is queued, running or completed.
Since the query request itself is very complex, I store the request in
a series of other tables before the request is queued.
Now, for scalability reasons they want to go to 2 database servers,
allowing more requests to be run at the same time. Obviously, in
regards to the read/write tables described above, I need to somehow
replicate the data between the servers. Any thoughts as to which
replication method would best fit my scenario?
Any thoughts would be appreciated.
Thanks in advance
James
Maybe use Partitioned Views for this instead?
"James" <jamesdfriesen@.hotmail.com> wrote in message
news:1142885711.716158.206300@.v46g2000cwv.googlegr oups.com...
> Hi,
> I have the following situation and would like some advise.
> I am using SQL server 2005. I have smart clients access the database
> over the internet through web services.
> I currently have 1 data warehouse type database. It contains about 95%
> read only data which is apended to nightly.
> Since the queries are long and complex running on millions of rows of
> data, queries are run asyncronously. I use Service Broker to queue and
> run the queries.
> I have the following read/write tables in the database:
> A table to store the results of the queries.
> A table to store the status of the request. Front the front end, I can
> see if the query is queued, running or completed.
> Since the query request itself is very complex, I store the request in
> a series of other tables before the request is queued.
> Now, for scalability reasons they want to go to 2 database servers,
> allowing more requests to be run at the same time. Obviously, in
> regards to the read/write tables described above, I need to somehow
> replicate the data between the servers. Any thoughts as to which
> replication method would best fit my scenario?
> Any thoughts would be appreciated.
> Thanks in advance
> James
>
Subscribe to:
Posts (Atom)