Regarding History tables in Replication...

Hi,

How frequently will the history tables get deleted in Merge Replication and Transactional Replication with updatable subscriptions?

When transactional replication with updatable subscriptions is running in the continuous mode, will the history tables get deleted frequently?

Parameters :

Version : SQL Server 2005 SP1

Mode : Continuous running mode

Subscription type : Pull (for transactional)

Regards,

Swapna.B.

There's a history cleanup job that runs and will clean up rows based on your history retention.

Regarding drop and restore replication database

Hi Guys,
I have two questions regarding replication:
1, If I don't continue to use replication at all, how can I drop replication
completely? Because too many command, e.g. sp_removedbreplication or
sp_dropplication and so on. Which one can I use?
2. If I want to retore the replication database and want to keep replication
so that I can continue to use replication, do I need to setup some steps
before restore database?
Thanks.
To remove replication, use sp_removedbreplication. There might be a few
orphaned objects left behind but these can be deleted by hand or simply left
there as they won't cause any problems.
To restore a replicated database, pls have a look in BOL for the topic
"replication, backup and restore operations".
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thank you very much. I use sp_removedbreplication to remove the replication,
it is ok. But like what you said, there are many orphaned objects and mark as
crose with read color, it is very uncomfortable. I use sp_dropdistpublisher
and sp_dropsubscriber to remove those objects and it is still marked even
though there isn't those objects. How to get rid of red color? thanks.
"Paul Ibison" wrote:

> To remove replication, use sp_removedbreplication. There might be a few
> orphaned objects left behind but these can be deleted by hand or simply left
> there as they won't cause any problems.
> To restore a replicated database, pls have a look in BOL for the topic
> "replication, backup and restore operations".
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||The replication monitor gets its info from
tempdb.dbo.MSreplication_agent_status and running
sp_MSload_replication_status refreshes this table. As restarting the SQL
Server Service causes the tempdb to be recreated from the model database,
this also removes the problem.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Regarding Bulk Copy in Transactional Replication..

Hi,
I got typical scenario in transacrtional replication. i want to avoid bulk
copy during replication.
Let me explain my scenario.
I’m doing transactional replication between two databases.
When publisher and subscriber created the data going to be bulk copied from
publisher table to subscriber table.
My main intension was to create replication between different tables with
different fields in which I got succeeded.
But main problem is I want to stop this bulk copy from publisher to
subscriber.
Scenario 1: my subscriber table may contain some previous data which will be
replaced with publisher data due to bulk copy.
I don’t want this .I want to avoid this bulk copy and wants to create
procedures(for insert, update and delete transactions) in subscriber which
will take care of replication.
I achieved almost everything but not able to avoid this bulk copy during
the creation of subscriber.
As I know the only way I can stop bulk copy is by creating subscription
without subscription agent. But here without subscription agent the
procedures(for insert, update and delete transactions)
won’t get created in subscriber.
Help me regarding the above scenario and I need it urgently.
Regards
Baji Prasad
I'm not entirely clear about your scenario, but it seems to me that you are
talking about nosync initialisations. Please have a look here and see if this
applies:
http://www.replicationanswers.com/NoSyncInitializations.asp
HTH,
Paul Ibison
|||hi paul..
im very thankfull for ur reply.
yes it helped me a lot atleast i got an idea how to avoid bulk copy.
again thanks man...
Regards
Baji Prasad
"Paul Ibison" wrote:

> I'm not entirely clear about your scenario, but it seems to me that you are
> talking about nosync initialisations. Please have a look here and see if this
> applies:
> http://www.replicationanswers.com/NoSyncInitializations.asp
> HTH,
> Paul Ibison
>
sql

Regarding Bulk Copy During Transactional Replication

Hi ,

I got a problem in regarding Transactional Replication.

Let me explain my scenario.

I’m doing transactional replication between two databases.

When publisher and subscriber created the data going to be bulk copied from publisher table to subscriber table.

My main intension was to create replication between different tables with different fields in which I got succeeded.

But main problem is I want to stop this bulk copy from publisher to subscriber.

Scenario 1: my subscriber table may contain some previous data which will be replaced with publisher data due to bulk copy.

I don’t want this .I want to avoid this bulk copy and wants to create procedures(for insert, update and delete transactions) in subscriber which will take care of replication.

I achieved almost everything but not able to avoid this bulk copy during the creation of subscriber.

As I know the only way I can stop bulk copy is by creating subscription without subscription agent. But here without subscription agent the procedures(for insert, update and delete transactions)

won’t get created in subscriber.

Help me regarding the above scenario and I need it urgently.

in sp_addsubscription, look at paramter @.sync_type, you can specify 'replication support only' and 'initialize with backup'. That allows you to skip the bcp. You can find more information about the latter here: http://msdn2.microsoft.com/de-de/library/ms147834(SQL.90).aspx.

Regarding Bulk Copy During Transactional Replication

Hi ,

I got a problem in regarding Transactional Replication.

Let me explain my scenario.

I’m doing transactional replication between two databases.

When publisher and subscriber created the data going to be bulk copied from publisher table to subscriber table.

My main intension was to create replication between different tables with different fields in which I got succeeded.

But main problem is I want to stop this bulk copy from publisher to subscriber.

Scenario 1: my subscriber table may contain some previous data which will be replaced with publisher data due to bulk copy.

I don’t want this .I want to avoid this bulk copy and wants to create procedures(for insert, update and delete transactions) in subscriber which will take care of replication.

I achieved almost everything but not able to avoid this bulk copy during the creation of subscriber.

As I know the only way I can stop bulk copy is by creating subscription without subscription agent. But here without subscription agent the procedures(for insert, update and delete transactions)

won’t get created in subscriber.

Help me regarding the above scenario and I need it urgently.

in sp_addsubscription, look at paramter @.sync_type, you can specify 'replication support only' and 'initialize with backup'. That allows you to skip the bcp. You can find more information about the latter here: http://msdn2.microsoft.com/de-de/library/ms147834(SQL.90).aspx.

reg. Prob with replication

HI,
Im using Mssql 2000, We are migrating the server from one server to
another.
i will list out what i have done, so pls let me know what wrong i did
and how to solve.
1. install mssql s/w
2. restore master db
3. restore users db's.
4. Run the replication script (extracted from old server)
im getting the errors, as follows
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure sp_adddistributiondb,
Line 164
Invalid object name 'msdb..MSdistributiondbs'.
Server: Msg 14040, Level 16, State 1, Procedure sp_addsubscriber, Line
95
The server 'ADCPWSQLZ7' is already a Subscriber.
.............
.......
pls help me to solve the error.
thks
if you had restored your distribution and msdb databases you might have had
a chance of getting this to work. At this point your best bet is to restore
the publication database again using the keep_replication switch, script out
the publications and subscriptions, then drop the publications and
subscriptions, disable and then re-enable replication, and then recreate
your publications and subscriptions again from the script.
http://www.zetainteractive.com - Shift Happens!
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
"calms" <calmsqurill@.gmail.com> wrote in message
news:54ef32ea-e652-41ce-a1ad-f2b90e6cb2ab@.i29g2000prf.googlegroups.com...
> HI,
> Im using Mssql 2000, We are migrating the server from one server to
> another.
> i will list out what i have done, so pls let me know what wrong i did
> and how to solve.
> 1. install mssql s/w
> 2. restore master db
> 3. restore users db's.
> 4. Run the replication script (extracted from old server)
> im getting the errors, as follows
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure sp_adddistributiondb,
> Line 164
> Invalid object name 'msdb..MSdistributiondbs'.
> Server: Msg 14040, Level 16, State 1, Procedure sp_addsubscriber, Line
> 95
> The server 'ADCPWSQLZ7' is already a Subscriber.
> ............
> ......
> pls help me to solve the error.
> thks

reg. Prob with replication

HI,
Im using Mssql 2000, We are migrating the server from one server to
another.
i will list out what i have done, so pls let me know what wrong i did
and how to solve.
1. install mssql s/w
2. restore master db
3. restore users db's.
4. Run the replication script (extracted from old server)
im getting the errors, as follows
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure sp_adddistributiondb,
Line 164
Invalid object name 'msdb..MSdistributiondbs'.
Server: Msg 14040, Level 16, State 1, Procedure sp_addsubscriber,
Line
95
The server 'ADCPWSQLZ7' is already a Subscriber.
.............
.......
pls help me to solve the error.
thks
If you wanted a hope of getting this to work you should have restored the
msdb and distribution databases as well and then renamed the new server with
the name of the old server.
you need to disable replication and then recreate it and then try to re-run
your scripts on this server.
http://www.zetainteractive.com - Shift Happens!
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
"calms" <calmsqurill@.gmail.com> wrote in message
news:ddb85046-94b1-4e43-a006-f3d3a24bca5a@.s12g2000prg.googlegroups.com...
> HI,
> Im using Mssql 2000, We are migrating the server from one server to
> another.
>
> i will list out what i have done, so pls let me know what wrong i did
> and how to solve.
>
> 1. install mssql s/w
> 2. restore master db
> 3. restore users db's.
> 4. Run the replication script (extracted from old server)
>
> im getting the errors, as follows
>
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure sp_adddistributiondb,
> Line 164
> Invalid object name 'msdb..MSdistributiondbs'.
> Server: Msg 14040, Level 16, State 1, Procedure sp_addsubscriber,
> Line
> 95
> The server 'ADCPWSQLZ7' is already a Subscriber.
> ............
> ......
>
> pls help me to solve the error.
>
> thks
>
sql

reg. Prob with replication

HI,
Im using Mssql 2000, We are migrating the server from one server to
another.
i will list out what i have done, so pls let me know what wrong i did
and how to solve.
1. install mssql s/w
2. restore master db
3. restore users db's.
4. Run the replication script (extracted from old server)
im getting the errors, as follows
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure sp_adddistributiondb,
Line 164
Invalid object name 'msdb..MSdistributiondbs'.
Server: Msg 14040, Level 16, State 1, Procedure sp_addsubscriber,
Line
95
The server 'ADCPWSQLZ7' is already a Subscriber.
............
......
pls help me to solve the error.
thksIf you wanted a hope of getting this to work you should have restored the
msdb and distribution databases as well and then renamed the new server with
the name of the old server.
you need to disable replication and then recreate it and then try to re-run
your scripts on this server.
http://www.zetainteractive.com - Shift Happens!
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
"calms" <calmsqurill@.gmail.com> wrote in message
news:ddb85046-94b1-4e43-a006-f3d3a24bca5a@.s12g2000prg.googlegroups.com...
> HI,
> Im using Mssql 2000, We are migrating the server from one server to
> another.
>
> i will list out what i have done, so pls let me know what wrong i did
> and how to solve.
>
> 1. install mssql s/w
> 2. restore master db
> 3. restore users db's.
> 4. Run the replication script (extracted from old server)
>
> im getting the errors, as follows
>
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure sp_adddistributiondb,
> Line 164
> Invalid object name 'msdb..MSdistributiondbs'.
> Server: Msg 14040, Level 16, State 1, Procedure sp_addsubscriber,
> Line
> 95
> The server 'ADCPWSQLZ7' is already a Subscriber.
> ............
> ......
>
> pls help me to solve the error.
>
> thks
>

reg. Prob with replication

HI,
Im using Mssql 2000, We are migrating the server from one server to
another.
i will list out what i have done, so pls let me know what wrong i did
and how to solve.
1. install mssql s/w
2. restore master db
3. restore users db's.
4. Run the replication script (extracted from old server)
im getting the errors, as follows
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure
sp_MSupdate_agenttype_default, Line 12
Invalid object name 'msdb..MSagent_profiles'.
Server: Msg 208, Level 16, State 1, Procedure sp_adddistributiondb,
Line 164
Invalid object name 'msdb..MSdistributiondbs'.
Server: Msg 14040, Level 16, State 1, Procedure sp_addsubscriber,
Line
95
The server 'ADCPWSQLZ7' is already a Subscriber.
............
......
pls help me to solve the error.
thksIf you wanted a hope of getting this to work you should have restored the
msdb and distribution databases as well and then renamed the new server with
the name of the old server.
you need to disable replication and then recreate it and then try to re-run
your scripts on this server.
--
http://www.zetainteractive.com - Shift Happens!
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
"calms" <calmsqurill@.gmail.com> wrote in message
news:ddb85046-94b1-4e43-a006-f3d3a24bca5a@.s12g2000prg.googlegroups.com...
> HI,
> Im using Mssql 2000, We are migrating the server from one server to
> another.
>
> i will list out what i have done, so pls let me know what wrong i did
> and how to solve.
>
> 1. install mssql s/w
> 2. restore master db
> 3. restore users db's.
> 4. Run the replication script (extracted from old server)
>
> im getting the errors, as follows
>
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure
> sp_MSupdate_agenttype_default, Line 12
> Invalid object name 'msdb..MSagent_profiles'.
> Server: Msg 208, Level 16, State 1, Procedure sp_adddistributiondb,
> Line 164
> Invalid object name 'msdb..MSdistributiondbs'.
> Server: Msg 14040, Level 16, State 1, Procedure sp_addsubscriber,
> Line
> 95
> The server 'ADCPWSQLZ7' is already a Subscriber.
> ............
> ......
>
> pls help me to solve the error.
>
> thks
>

Referential integrity during replication

Hello forum

I like to build a custom replication application for a databasee.
To questions concering this. The idea is to use a mechanim, which updates table by table (in sequence).

During the replication proccess I update a table (table1), which has 1 to 1 refenceto a second table(table2). This new record is unsing a reference which hasn't been entered by this time into table 2. This is hurts the referencial integrity, right?

Would it help to use a transaction for the update in table 1 and table 2, when is the integrity checked? At the end of the transaction, by the command commit transaction?

Is it possible to switch on/off the referential integrity check while a database is running? If yes, are there any side effects beside that the integrity is not propre controlles anymore?

Thanks for your help in advance!

DominikWhat about this:

drop table test1
drop table test2
go
create table test2(id int primary key)
create table test1(id int, rid int)
ALTER TABLE test1 WITH NOCHECK ADD CONSTRAINT
FK1 FOREIGN KEY(rid) REFERENCES dbo.test2(id)
go
insert test2 values(1)
insert test1 values(1,1)
go
ALTER TABLE dbo.test1 NOCHECK CONSTRAINT FK1
go
insert test1 values(1,11) -- works

Re-establish replication

I had Merge replication working fine between two SQL 2000 servers for months... then the subscriber server had a hard drive crash and we rebuilt it... I am now having trouble re-establishing the replication with the subscriber. On the publisher I have de
leted the subscription and reestablished it... but I never see the database being pushed to the subscriber, and the Merge agent always fails because it says it cannot find the database on the subscriber.
What am I missing here? Any advice would be greatly appreciated.
Thanks,
Jeff
Jeff,
you say "I never see the database being pushed to the subscriber" but the
database isn't created by the snapshot synchronization. Can you check to see
if it exists on the subscriber? If it does, then on the publisher
properties, subscriptions tab does the subscription database name appear?
Cheers,
Paul Ibison
|||what is the error message you are getting?
"dev@.mycompany.com" <anonymous@.discussions.microsoft.com> wrote in message
news:DEFD3DDB-3465-44E2-B1EB-1050147A2BC7@.microsoft.com...
> I had Merge replication working fine between two SQL 2000 servers for
months... then the subscriber server had a hard drive crash and we rebuilt
it... I am now having trouble re-establishing the replication with the
subscriber. On the publisher I have deleted the subscription and
reestablished it... but I never see the database being pushed to the
subscriber, and the Merge agent always fails because it says it cannot find
the database on the subscriber.
> What am I missing here? Any advice would be greatly appreciated.
> Thanks,
> Jeff
|||Paul,
Thanks for the response... you reminded me that I had to manually create the database on the subscriber... then replciation agenst would populate it for me... which they did... and I thought all was fine (I tested on table and changes on both sides propog
ated properly to the other side)... but after a few days, we noticed that new records in another table on the publisher would not only NOT get replicated to the subscriber, but would be deleted from the publisher. The Merge logs showed conflicts... how c
ould there be conflicts with a new record being added on the publisher?
Also, when I try to manually add a record to the same tabel on the subscriber, I get a stored proc error... I am guessing related to replication.
What did I forget here?
1) Existing replication working fine
2) Subscriber machine failed
3) Rebuilt subscriber from scratch, reloaded SQL
4) Set-up SQL User list, and created databse that si used for replication
5) Deleted old subsciption on publisher, and created a new one
6) Then the story above took place...
Jeff
|||What triggers do you have on the tables?
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||I have no triggers on the tables at all. The only triggers that fire should be those that SQL Replication uses.
|||Jeff,
there is definitely something extra/abnormal/odd going on here. I think your best bet would be to use profiler and try to trace the event sequence that leads to this behaviour. Alternatively, if you don't want to do this detective work, then you could set
up the whole thing from scratch (drop publisher, remove replication on publishing db and subscribing db, add publisher and subscriber, initialize).
HTH,
Paul Ibison
|||Paul,
Thanks for your help... I deleted the subscription and the publication and started from scratch... set up the publication... ok... set up the subscription... then I get an error saying the publisher could not log-in to the subscriber.
I have the SQL server and SQL Agent set to use a Windows account on both machines... same account ID/PW. But the Merge agent error says "Error logging in to subscriber. sa login failed".
What am I missing here? I thought the Merge agent used the security settings set for the SQL Agent (set to a Windows account)... so why is the merge agent trying to use the sa login at the subscriber?
I know I am just forgetting something very simple here. Any advice would be greatly appreciated.
Thanks,
Jeff
|||Jeff,
right-click the replication monitor and go to properties. Using the elipsis,
make sure that your agents are using trusted security and not sql server
security.
HTH,
Paul
|||Paul,
Under Distributor properties, Subscribers tab, ellipsis next to subscriber, it shows Agent connection to subscriber as "Impersonate the SQL Server Agent account on 'PublisherName' (trusted connection)"
Why else would the Merge agent be trying to connect with sa instead of the Windows account that both SQL Agents are running under on both publisher and subscriber?
Jeff

Redundancy requiring Identity columns on Subscriber - please help

Hi
I am just getting up to speed on replication so please forgive me if I
am missing something simple here......
Scenario
Application server (call it Test) that has SQL Server 2000 database
I am setting up replication for backup pupopses
(If Server A dies - point Application (Test) to Server B )
My Problem -
Many tables in the database have the identiy column set to YES
As the TEST operates, many rows uniquely added to the db using Identity
column as PKeys
I was using snapshot replication for test purposes....(no license for
Transactional)
On subscriber - created blank DB with YES (Not for Replication) option
set for the tables in question.
The Snapshot I created from Publisher, when I push to the subscriber -
it resets the Identify (YES - NFR) back to No
I redireecte dteh Aopplication server to Server B an dit fails to work
corrcectly.- cannot insert into row s where Identity column is set to
No
Identify needs to be set to YES (or YES not for Repl) in order for my
application server to work correctly.
Any ideas on how I could best achieve this?
Thanks in advance........
Kevha
right click on your publication, select properties, in the articles tab
select the browse button to the right of your tables. Click on the snapshot
tab. In the name conflicts section, select the keep delete existing data.
Now return to your subscriber, fix things the way you want them, and then
rereun your snapshot.
It should work this time.
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
<kevha@.hotmail.com> wrote in message
news:1132178886.227006.217160@.o13g2000cwo.googlegr oups.com...
> Hi
> I am just getting up to speed on replication so please forgive me if I
> am missing something simple here......
> Scenario
> Application server (call it Test) that has SQL Server 2000 database
> I am setting up replication for backup pupopses
> (If Server A dies - point Application (Test) to Server B )
> My Problem -
> Many tables in the database have the identiy column set to YES
> As the TEST operates, many rows uniquely added to the db using Identity
> column as PKeys
> I was using snapshot replication for test purposes....(no license for
> Transactional)
> On subscriber - created blank DB with YES (Not for Replication) option
> set for the tables in question.
> The Snapshot I created from Publisher, when I push to the subscriber -
> it resets the Identify (YES - NFR) back to No
> I redireecte dteh Aopplication server to Server B an dit fails to work
> corrcectly.- cannot insert into row s where Identity column is set to
> No
> Identify needs to be set to YES (or YES not for Repl) in order for my
> application server to work correctly.
> Any ideas on how I could best achieve this?
> Thanks in advance........
> Kevha
>
|||Hi Hilary
I still can't seem to get the problem solved.
Let me take it back a step -
Server A (source)
Server B (subscriber)
I want to replicate server A to server B
I set up Server A with Snapshot Replicationa sPublisher and
Distributor.
Database created on Server A - some tables with Columns set with
Identity = Yes
I set up Snapshot Replication
In the SnapShot Properties/ Articles I set 'Keep Existing Table
unchanged' option on.
I added a custom script in the Snapshot Properties tab that - Drops the
tables, recreates them with the IDENTITY NOT FOR REPLICATION option
included during table creation.
When I conduct the Synchronize - I get the following error -
Violation of Primary Key constraint 'x' insert duplicate key in object
'x'
My replication could happen once an hour (not real time critical so any
option that keeps the Identity filed intact appreciated)

Re-do replication for new release

We alter the database schema that our production application uses with almost every release. Is there a way to basically remove the replication and re-do it for all tables / views at one time? Then we could just "rebuild" the replication with every release. We have about 200 tables and 200 views that have many dependencies. I'm sure it could be done with scripting and the stored procedures, but I'm new at this and not sure where to start. Any ideas? Thanks!

Yes, you could script out all the replication settings (publication, articles, jobs, subscriptions, etc) using the UI, then remove replication completely from the publisher as well as subscriber and after you do the schema changes to the databases, then use the scripts to setup replication again on the new release.

But however note that there are always differences between releases. There could be slight differences in the way they are scripted out and the way they are handled in the new release. With that in mind, you may have to slightly edit the scripts before running them on the newest version.

Also, I would first test it out on a test systems before trying it on the production servers.

Also note that With SQL Server 2005, schema changes to the tables are replicated to the subscriber. So for example, if you add a new column to one of the replicated tables, that new column will be replicated to the Subscriber when configured appropriately.|||Thank you Sir, for your suggestions. I will give them a try.