Replication stops after server reboot??

Hi,

I have transactional replication set up between two dedicated servers. Server A is the PDC and Server B is a BDC (they are both Win2000 boxes). Both the servers are brand new, and replaced the two that were running like clock work (replication wise) for the last 12 months. I never had this problem with the old servers...

When the servers are shut down (as the case was a couple of weeks back with a power failure) or just recently when they were move to another room. Both servers boot up at the same time. Server B (which is the server holding the db being replicated) boots quicker and as a result replication fails and is then 'sucessfully stopped'. Unless I am aware of the server being rebooted and can monitor this potential problem, within 2 days the logfile grows to large and everything comes to a crashing halt.

I just remove replication, truncate and shrink the log, reset replication and we're away... BUT I really need to know why it is happening in the first place. I figure there must be a setting that I have forgotten about or something.

Both servers are Win2000 (SP4) and SQL2000(SP3a).

Any help would be appreciated.

Thanks
CasperHave you tried restarting replication agents before removing replication? Make sure sql agent is set up as auto start. I assume the distribution db are sitting on the publisher (server B). When dist server couldn't find subscriber (box A), it will auto stop all distribution agetns, which caused the distribution db to grow.

You can set up a job monitoring log reader and dist agent to see if they are stopped. Then run sp_start_job to automatically restart them.|||When the log is full everything comes to a crashing halt. That's basically the reason why. You need to either backup the log or truncate it more often, at least once a day. Shrink it if need be. You really don't have to uninstall replicate to dump the log and shrink the log file. You can truncate the log and shrink it with replication running.|||Joejcheng - yes I already do that, the transaction log is backed up and truncated every hour. The problem was when replication stops the transaction log grows (because it is retaining all transactions waiting to be replicated).

Richard - thank you! I had not even thought about just restarting the jobs (doh!). However the 2 times this has happened the transaction log has been around 15GB and the database controls a production system and they cannot afford to be offline for long (I usually just get them up and running and then reset replication the following evening). Can I start the jobs and have them continue work whilst it is pushing all the transactions across - or would it just never catch up because of the size of the transaction log?

And also thank you for mentioning sp_start_job - I will most certainly put that in place.

However I would still really like to know why this is happening in the first place, but I think I may have worked it out...

I set up replication from a script created on the previous system. One of the replication owners is XXXSqlAdmin, however in the new system this user is just SQLAdmin. I have noticed the error raised by this in the past (ie XXXSqlAdmin does not exist in this db), but didn't think it would cause any problems. But now I think I was very wrong. I changed the script before I ran it last night, no errors were returned. I will monitor the DB for the next week and then we will do a test where we shut down the servers and boot server B well before we boot server A.

Thanks again for your help, much appreciated!

Casper :)

Replication Setup Issues

While trying to configure replication from and to SQL Server 2000 boxes, I
encountered the following error, which I was not able to get past. The
error is encountered after completing the "Push Subscription Wizard" which
is set to create the database and initialize the schema and data. The
database gets created, but the schema does not.
Does anyone have advice on how to get past this error?
If not, I have questions further on regarding my test environment...
The process could not connect to Subscriber '<SERVERNAME>'.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
(Source: <SERVERNAME> (Data source); Error number: 18452)
I am connected using windows authentication, and I am an administrator on
both boxes. I can connect to either database and perform any action I want,
but I still keep getting this error. I have not found any way to specify a
login for replication, I assume the job runs either under my login or as SA.
I changed the scheduled task to be owned by SA, and attempted to run it as
both "Self" and "DBO", with no luck. I kept getting the same error. Some
of the IDs that exist in our production database do not exist in the
database we are replicating to, although SA exists, as does my account.
Normally I do not use Enterprise manager for anything and I script
everything out, but the only instructions I had were for EM. Would I be
better off using a script in QA for setting up replication?
Regarding my test environment...
Now, after spending a couple of hours on this error, I decided to go back to
my test environment (where everything worked the first time) and make it
look more like my production environment so I could do another round of
testing. I restored the master database and my 3 database which will be
replicated, but the backups were form different dates and I ran into all
kinds of conflicts. Essentially, SQL Server said the replication
definitions were invalid, and would not let me remove them. Next week, when
time allows, I will be getting a cold backup of the entire database to
attach to, hopefully that will get my test database in a state where I can
do another round of testing.
Am I on the right track with my test environment? What is the proper way to
copy a replication database, and remove the replication?
Any advice will be greatly appreciated.
Are you using sql server logins for the distribution agent, and the
subscriber is set to only allow windows (trusted) authentication? This could
be one cause of the error.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Both of the databases are set up to allow both SQL Server and trusted
authentication. I confirmed by logging into each server as SA, just to be
sure.
I am, however, performing all of the setup using trusted authentication,
under my own account, which has administrative priviledges.
I never specified a logon for the distribution agent, however. Maybe that
is the problem? I was never actually prompted to do so, and was unable to
find where to specify it. I am the owner of the distribution agent
process, and the first process in the scheduled job is set to run as (Self),
so I assume it is running under my account. I tried changing this to DBA,
and I tried changing the owner to SA, neither made a difference.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:exM%23TihUHHA.3652@.TK2MSFTNGP04.phx.gbl...
> Are you using sql server logins for the distribution agent, and the
> subscriber is set to only allow windows (trusted) authentication? This
could
> be one cause of the error.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
|||Jim,
just testing all posibilities here so pls can you check what the following
script returns at the subscriber.
Use Master
go
Select @.@.Servername
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Excellent catch. My test database comes back with the production database
name. The question is, how do I correct this, or how do I get the test
database to match my production database configuration?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ua103$CVHHA.4784@.TK2MSFTNGP03.phx.gbl...
> Jim,
> just testing all posibilities here so pls can you check what the following
> script returns at the subscriber.
> Use Master
> go
> Select @.@.Servername
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>
|||ok, I found this in BOL explaining how to change the server name. The
question is, will this be enough to get the restored database in a testable
state, or am I going about this the wrong way? Tonight I intend to take a
full cold backup from production and reattach to all the databases on test.
How to change to the current server name in the SQL Server 6.5 master
database (ISQL/w)
To change to the current server name in the SQL Server 6.5 master database
1.. Start Microsoft SQL ServerT in minimal configuration mode. In a
command prompt window, from the \Mssql\Binn directory, run:
sqlservr -f
2.. On the Start menu, point to Programs /Microsoft SQL Server 6.5, and then
click ISQL/w.
3.. Enter the sa password, and then click Connect.
4.. Execute SELECT @.@.SERVERNAME to retrieve the former server name.
5.. Execute sp_dropserver to drop the former server.
6.. Execute sp_addserver to add the current server.
7.. Stop SQL Server. In the command prompt window, press Ctrl+C.
8.. Restart SQL Server.
9.. Execute SELECT @.@.SERVERNAME to verify the current server name.
Examples
--Start SQL Server in minimal configuration mode.
--Retrieve the former server name.
SELECT @.@.SERVERNAME
--Drop the server returned from the previous select.
sp_dropserver 'SERVER6X'
--Add the current server.
sp_addserver 'SERVER70', local
--Stop SQL Server.
--Restart SQL Server in minimal configuration mode.
--Verify the current server name.
SELECT @.@.SERVERNAME
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uDG$BKDVHHA.2212@.TK2MSFTNGP02.phx.gbl...[vbcol=seagreen]
> Excellent catch. My test database comes back with the production database
> name. The question is, how do I correct this, or how do I get the test
> database to match my production database configuration?
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:ua103$CVHHA.4784@.TK2MSFTNGP03.phx.gbl...
following
>
|||Jim,
this should be enough to get the subscriber set up. For step 6 you just have
to remember the 'local' switch.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Paul, thank you for the assistance. I'll post back in a couple of days to
let everyone know how I made out.
I have added your site to my online SQL Server library. I have a feeling it
will come in useful.
Thanks again,
Jim
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23Lh$XgDVHHA.4756@.TK2MSFTNGP06.phx.gbl...
> Jim,
> this should be enough to get the subscriber set up. For step 6 you just
have
> to remember the 'local' switch.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
|||I was finally able to get my test environment up and running, after renaming
my server, deleting the entries in the distribution database, and updating
the server name in the msdb.dbo.jobs table.
The test environment replicated without any issue, so I do not know why I am
having trouble in production.
Maybe I just need to remove the replication entirely and start from scratch?
Before I go that route, does anyone know what would be likely to cause this
error when trying to run the distribution agent?
The process could not connect to Subscriber '<SERVERNAME>'.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
(Source: <SERVERNAME> (Data source); Error number: 18452)
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:e5aCzMhUHHA.5012@.TK2MSFTNGP04.phx.gbl...
> While trying to configure replication from and to SQL Server 2000 boxes, I
> encountered the following error, which I was not able to get past. The
> error is encountered after completing the "Push Subscription Wizard" which
> is set to create the database and initialize the schema and data. The
> database gets created, but the schema does not.
> Does anyone have advice on how to get past this error?
> If not, I have questions further on regarding my test environment...
> ----
--
> --
> The process could not connect to Subscriber '<SERVERNAME>'.
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> (Source: <SERVERNAME> (Data source); Error number: 18452)
> ----
--
> --
> I am connected using windows authentication, and I am an administrator on
> both boxes. I can connect to either database and perform any action I
want,
> but I still keep getting this error. I have not found any way to specify
a
> login for replication, I assume the job runs either under my login or as
SA.
> I changed the scheduled task to be owned by SA, and attempted to run it as
> both "Self" and "DBO", with no luck. I kept getting the same error.
Some
> of the IDs that exist in our production database do not exist in the
> database we are replicating to, although SA exists, as does my account.
> Normally I do not use Enterprise manager for anything and I script
> everything out, but the only instructions I had were for EM. Would I be
> better off using a script in QA for setting up replication?
> Regarding my test environment...
> Now, after spending a couple of hours on this error, I decided to go back
to
> my test environment (where everything worked the first time) and make it
> look more like my production environment so I could do another round of
> testing. I restored the master database and my 3 database which will be
> replicated, but the backups were form different dates and I ran into all
> kinds of conflicts. Essentially, SQL Server said the replication
> definitions were invalid, and would not let me remove them. Next week,
when
> time allows, I will be getting a cold backup of the entire database to
> attach to, hopefully that will get my test database in a state where I can
> do another round of testing.
> Am I on the right track with my test environment? What is the proper way
to
> copy a replication database, and remove the replication?
> Any advice will be greatly appreciated.
>
|||OK, I removed the replication entirely and started over, with the same
problems as before. It turned out to be an NT account/permissions issue.
The account that runs the SQL Server Agent on the distributor needs to have
access to the subscriber server as well.
Just to repeat the error:
> ----
> The process could not connect to Subscriber '<SERVERNAME>'.
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> (Source: <SERVERNAME> (Data source); Error number: 18452)
I looked at the security event logs on the subscriber server and saw a login
failure when I was trying to push the subscription. The ID that was
erroring out was a local ID on the distributor. The SQL Server Agent was
being run under this local account. Creating the same local account on the
subscriber server and giving it the required permissions, corrected the
problem.

Replication Problems 32bit IIS 64 bit SQL 2005??

Hello, I have a several PPC app that use replication on seperate IIS
and SQL (2000) boxes with great success. We recently set up our first
2005 SQL install. It happens to be on a x64 machine. I am trying to
use the same IIS set up and just have it replicate with the same
database that has been migrated from 2000 to 2005.

>From the pocket IE browser I get the message "SQL Server Mobile Server
Agent 3.0" so all is good there. At the point where the replication
object in my app tries to synch I get the error message:
"An instance of the SQL Server Reconciler error object cannot be
created. Try reinstalling the replication components."
That is the only error. I have done a little digging and see mention
of repication not working between a 32 bit IIS box and a 64 bit SQL
2005 box. However the messages were from early 2006.
The snapshot share all seem to have the correct rights. I can creat my
publication with the correct articles. And as stated the hand held
gets the success message to PIE.
Can anyone shed a little light on this? Do I need a 32 bit version of
SQL 2005? Is there any other sort of tools I need to install on the
IIS or SQL side?
Any and all help greatly appreciated.
On Feb 21, 11:41 am, rplac...@.yahoo.com wrote:
> Hello, I have a several PPC app that use replication on seperate IIS
> and SQL (2000) boxes with great success. We recently set up our first
> 2005 SQL install. It happens to be on a x64 machine. I am trying to
> use the same IIS set up and just have it replicate with the same
> database that has been migrated from 2000 to 2005.
>
> Agent 3.0" so all is good there. At the point where the replication
> object in my app tries to synch I get the error message:
> "An instance of the SQL Server Reconciler error object cannot be
> created. Try reinstalling the replication components."
> That is the only error. I have done a little digging and see mention
> of repication not working between a 32 bit IIS box and a 64 bit SQL
> 2005 box. However the messages were from early 2006.
> The snapshot share all seem to have the correct rights. I can creat my
> publication with the correct articles. And as stated the hand held
> gets the success message to PIE.
> Can anyone shed a little light on this? Do I need a 32 bit version of
> SQL 2005? Is there any other sort of tools I need to install on the
> IIS or SQL side?
> Any and all help greatly appreciated.
No ideas at all?
Should I try posting to a different group?
Please help.
|||Please have a look at this thread form MS support and see if it helps:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=202038&SiteID=1
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||On Feb 22, 9:07 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> Please have a look at this thread form MS support and see if it helps:http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=202038&SiteID=1
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
Thanks for the reply. That is not my set up the one referenced in the
article is SQL 2000...but I have my systems guys adding the client
tools as the article suggests.
Can anyone confirm or refute that if IIS is running on at 32 bit
processor box and SQL 2005 is running on a 64 bit processor box that
replication will NOT work? I saw some post mentioning to try WOW and
SQL 2005 in 32 bit install. I'd like to get some MS confirmation that
SQL 2005 on a x64 box is not compatible first.
Thanks a ton!
|||On Feb 22, 1:47 pm, rplac...@.yahoo.com wrote:
> On Feb 22, 9:07 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
>
> Thanks for the reply. That is not my set up the one referenced in the
> article is SQL 2000...but I have my systems guys adding the client
> tools as the article suggests.
> Can anyone confirm or refute that if IIS is running on at 32 bit
> processor box and SQL 2005 is running on a 64 bit processor box that
> replication will NOT work? I saw some post mentioning to try WOW and
> SQL 2005 in 32 bit install. I'd like to get some MS confirmation that
> SQL 2005 on a x64 box is not compatible first.
> Thanks a ton!
I'm at the end of my rope here. But the powers that be do not want to
try a 32 bit version of SQL 2005 on a 64 bit box unless they know that
is the problem. I do not have the option of running 2005 on a 32 bit
box.
If anyone knows this answer or can point me in the right direction
please help. I just want to know if a 64 bit install of SQL 2005 will
not work with replication to a PPC device when IIS is on a seperate
machine running 32 bit.
Thanks!

Replication Newbe

Quick question – I have 2 SQL 2000 SP3 boxes. Server A is the distributor
Server B is the Subscriber.
If I make a change on server B why do those changes not replicate to server A
What am I missing
Also all sudden I have 2 of every table on the subscriber, can i just delete
the tables that don't contain the "rowguid"
HELP :-|
"BP" wrote:

> Quick question – I have 2 SQL 2000 SP3 boxes. Server A is the distributor
> Server B is the Subscriber.
> If I make a change on server B why do those changes not replicate to server A
> What am I missing
>
|||Have you definitely set up updatable subscriptions, or merge replication?
It sounds as if you haven't, or if you have, the queue-reader/merge agent
hasn't been run.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I'm not sure how this has happened Did you assign a different table name
or object owner for these tables? Also, what type of replication have you
set up?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)