Question about SQL 2000 TLOG replication

Hi to all,
i have 2 questions about SQL replication :
1- If i configure a trasaction replication between 2 SQL server 2000 (online
and subscriber) and after i schedule the distributor agent for start onlyone
to week
this maybe generated a problem as more incresase of code and so slowing sql
or other proiblem ?
2- Exists any to the tail for replication ?
3- if i manually insert a data only on subscriber database can i generte any
replication problems ?!?! Problems as stop of replica or other problem ?!!?
4- What happened if i do a bckup log with truncate only for online databse
or distributor ?
Thanks in advance.
I count 4 questions not 2, but whose counting?
1) This won't work unless you set the history retention to 14 days or so.
Once you do this you will find performance degrades after several days
depending on your load. Ideally you would run the agents continually.
2) What do you mean by the tail of replication?
3) if the data you enter on the subscriber conflicts with the data entered
on the publisher it will cause a problem. Use the continue on data
consistency error profiler to skip this.
4) This should work ok. Replication will work fine after a truncate.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<io.com@.newsgroup.nospam> wrote in message
news:%23bZSDbI4GHA.1848@.TK2MSFTNGP06.phx.gbl...
> Hi to all,
> i have 2 questions about SQL replication :
> 1- If i configure a trasaction replication between 2 SQL server 2000
> (online and subscriber) and after i schedule the distributor agent for
> start onlyone to week
> this maybe generated a problem as more incresase of code and so slowing
> sql or other proiblem ?
> 2- Exists any to the tail for replication ?
> 3- if i manually insert a data only on subscriber database can i generte
> any replication problems ?!?! Problems as stop of replica or other
> problem ?!!?
> 4- What happened if i do a bckup log with truncate only for online
> databse or distributor ?
>
> Thanks in advance.
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eoPWojI4GHA.4820@.TK2MSFTNGP06.phx.gbl...
>I count 4 questions not 2, but whose counting?
Sorry the questions are 4 :-)

> 1) This won't work unless you set the history retention to 14 days or so.
> Once you do this you will find performance degrades after several days
> depending on your load. Ideally you would run the agents continually.
No i would schedule the agent for example every week at 02:00

> 2) What do you mean by the tail of replication?
Sorry i would tell : Exists any problem if i schedule the agent for
distribution only every 2 week and the distribution database and the data to
replicate are more grows ?

> 3) if the data you enter on the subscriber conflicts with the data entered
> on the publisher it will cause a problem. Use the continue on data
> consistency error profiler to skip this.
Ok thanks.

> 4) This should work ok. Replication will work fine after a truncate.
Ok thanks.
Thanks in advance.
|||answers inline.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<io.com@.newsgroup.nospam> wrote in message
news:eW$E0CJ4GHA.2424@.TK2MSFTNGP06.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eoPWojI4GHA.4820@.TK2MSFTNGP06.phx.gbl...
> Sorry the questions are 4 :-)
>
;)
>
> No i would schedule the agent for example every week at 02:00
>
> Sorry i would tell : Exists any problem if i schedule the agent for
> distribution only every 2 week and the distribution database and the data
> to replicate are more grows ?
>
Ideally you would replicate as frequently as possible, having the agent run
once or twice a week will not offer good performance if there is significant
load.

> Ok thanks.
>
> Ok thanks.
> Thanks in advance.
>
sql

Question about SQL 2000 TLOG replication

Hi to all,
i have 2 questions about SQL replication :
1- If i configure a trasaction replication between 2 SQL server 2000 (online
and subscriber) and after i schedule the distributor agent for start onlyone
to week
this maybe generated a problem as more incresase of code and so slowing sql
or other proiblem ?
2- Exists any to the tail for replication ?
3- if i manually insert a data only on subscriber database can i generte any
replication problems ?!'! Problems as stop of replica or other problem ?!!?
4- What happened if i do a bckup log with truncate only for online databse
or distributor ?
Thanks in advance.I count 4 questions not 2, but whose counting?
1) This won't work unless you set the history retention to 14 days or so.
Once you do this you will find performance degrades after several days
depending on your load. Ideally you would run the agents continually.
2) What do you mean by the tail of replication?
3) if the data you enter on the subscriber conflicts with the data entered
on the publisher it will cause a problem. Use the continue on data
consistency error profiler to skip this.
4) This should work ok. Replication will work fine after a truncate.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<io.com@.newsgroup.nospam> wrote in message
news:%23bZSDbI4GHA.1848@.TK2MSFTNGP06.phx.gbl...
> Hi to all,
> i have 2 questions about SQL replication :
> 1- If i configure a trasaction replication between 2 SQL server 2000
> (online and subscriber) and after i schedule the distributor agent for
> start onlyone to week
> this maybe generated a problem as more incresase of code and so slowing
> sql or other proiblem ?
> 2- Exists any to the tail for replication ?
> 3- if i manually insert a data only on subscriber database can i generte
> any replication problems ?!'! Problems as stop of replica or other
> problem ?!!?
> 4- What happened if i do a bckup log with truncate only for online
> databse or distributor ?
>
> Thanks in advance.
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eoPWojI4GHA.4820@.TK2MSFTNGP06.phx.gbl...
>I count 4 questions not 2, but whose counting?
Sorry the questions are 4 :-)
> 1) This won't work unless you set the history retention to 14 days or so.
> Once you do this you will find performance degrades after several days
> depending on your load. Ideally you would run the agents continually.
No i would schedule the agent for example every week at 02:00
> 2) What do you mean by the tail of replication?
Sorry i would tell : Exists any problem if i schedule the agent for
distribution only every 2 week and the distribution database and the data to
replicate are more grows ?
> 3) if the data you enter on the subscriber conflicts with the data entered
> on the publisher it will cause a problem. Use the continue on data
> consistency error profiler to skip this.
Ok thanks.
> 4) This should work ok. Replication will work fine after a truncate.
Ok thanks.
Thanks in advance.|||answers inline.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<io.com@.newsgroup.nospam> wrote in message
news:eW$E0CJ4GHA.2424@.TK2MSFTNGP06.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eoPWojI4GHA.4820@.TK2MSFTNGP06.phx.gbl...
>>I count 4 questions not 2, but whose counting?
> Sorry the questions are 4 :-)
>
;)
>> 1) This won't work unless you set the history retention to 14 days or so.
>> Once you do this you will find performance degrades after several days
>> depending on your load. Ideally you would run the agents continually.
> No i would schedule the agent for example every week at 02:00
>> 2) What do you mean by the tail of replication?
> Sorry i would tell : Exists any problem if i schedule the agent for
> distribution only every 2 week and the distribution database and the data
> to replicate are more grows ?
>
Ideally you would replicate as frequently as possible, having the agent run
once or twice a week will not offer good performance if there is significant
load.
>> 3) if the data you enter on the subscriber conflicts with the data
>> entered on the publisher it will cause a problem. Use the continue on
>> data consistency error profiler to skip this.
> Ok thanks.
>> 4) This should work ok. Replication will work fine after a truncate.
> Ok thanks.
> Thanks in advance.
>

Question about SQL 2000 TLOG replication

Hi to all,
i have 2 questions about SQL replication :
1- If i configure a trasaction replication between 2 SQL server 2000 (online
and subscriber) and after i schedule the distributor agent for start onlyone
to week
this maybe generated a problem as more incresase of code and so slowing sql
or other proiblem ?
2- Exists any to the tail for replication ?
3- if i manually insert a data only on subscriber database can i generte any
replication problems ?!'! Problems as stop of replica or other problem ?!!?
4- What happened if i do a bckup log with truncate only for online databse
or distributor ?
Thanks in advance.I count 4 questions not 2, but whose counting?
1) This won't work unless you set the history retention to 14 days or so.
Once you do this you will find performance degrades after several days
depending on your load. Ideally you would run the agents continually.
2) What do you mean by the tail of replication?
3) if the data you enter on the subscriber conflicts with the data entered
on the publisher it will cause a problem. Use the continue on data
consistency error profiler to skip this.
4) This should work ok. Replication will work fine after a truncate.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<io.com@.newsgroup.nospam> wrote in message
news:%23bZSDbI4GHA.1848@.TK2MSFTNGP06.phx.gbl...
> Hi to all,
> i have 2 questions about SQL replication :
> 1- If i configure a trasaction replication between 2 SQL server 2000
> (online and subscriber) and after i schedule the distributor agent for
> start onlyone to week
> this maybe generated a problem as more incresase of code and so slowing
> sql or other proiblem ?
> 2- Exists any to the tail for replication ?
> 3- if i manually insert a data only on subscriber database can i generte
> any replication problems ?!'! Problems as stop of replica or other
> problem ?!!?
> 4- What happened if i do a bckup log with truncate only for online
> databse or distributor ?
>
> Thanks in advance.
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eoPWojI4GHA.4820@.TK2MSFTNGP06.phx.gbl...
>I count 4 questions not 2, but whose counting?
Sorry the questions are 4 :-)

> 1) This won't work unless you set the history retention to 14 days or so.
> Once you do this you will find performance degrades after several days
> depending on your load. Ideally you would run the agents continually.
No i would schedule the agent for example every week at 02:00

> 2) What do you mean by the tail of replication?
Sorry i would tell : Exists any problem if i schedule the agent for
distribution only every 2 week and the distribution database and the data to
replicate are more grows ?

> 3) if the data you enter on the subscriber conflicts with the data entered
> on the publisher it will cause a problem. Use the continue on data
> consistency error profiler to skip this.
Ok thanks.

> 4) This should work ok. Replication will work fine after a truncate.
Ok thanks.
Thanks in advance.|||answers inline.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<io.com@.newsgroup.nospam> wrote in message
news:eW$E0CJ4GHA.2424@.TK2MSFTNGP06.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eoPWojI4GHA.4820@.TK2MSFTNGP06.phx.gbl...
> Sorry the questions are 4 :-)
>
;)
>
> No i would schedule the agent for example every week at 02:00
>
> Sorry i would tell : Exists any problem if i schedule the agent for
> distribution only every 2 week and the distribution database and the data
> to replicate are more grows ?
>
Ideally you would replicate as frequently as possible, having the agent run
once or twice a week will not offer good performance if there is significant
load.

> Ok thanks.
>
> Ok thanks.
> Thanks in advance.
>

Question about sp_addscriptexec

Hi,
I'm running SQL Server 2000 EE SP3 on Windows Server 2003.
I have multiple named pull subscribers to a Transactional replication model.
I have written a script that adds and drops fields from several tables using
sp_repladdcolumn and sp_repldropcolumn. I also have some commands in my
script that are removing tables from the replication model. I also have some
commands that are adding new tables to the replication model.
After the commands that are adding and removing the fields and tables, I am
issuing a sp_refreshsubscriptions.
I am then starting the snapshot agent so that the subscribers will get the
new tables that I have added.
I then issue an sp_addscriptexec to run a script on the subscriber.
My question is why does this script that I am executing via the
sp_addscriptexec run on the subscriber before the snapshot ever gets applied
to the subscriber? I need to have the script run after the snapshot has been
created and delivered to the subscriber.
Thanks in advance,
Stephen
Probably because the sp_addscriptexec is added to the distribution database
before the snapshot is generated and the sync commands make it there, You
should perhaps use the post snapshot command for 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
"Stephen Schissler" <StephenSchissler@.discussions.microsoft.com> wrote in
message news:9CD0B3BB-6EE6-46DF-8CDE-C8B860890EE1@.microsoft.com...
> Hi,
> I'm running SQL Server 2000 EE SP3 on Windows Server 2003.
> I have multiple named pull subscribers to a Transactional replication
model.
> I have written a script that adds and drops fields from several tables
using
> sp_repladdcolumn and sp_repldropcolumn. I also have some commands in my
> script that are removing tables from the replication model. I also have
some
> commands that are adding new tables to the replication model.
> After the commands that are adding and removing the fields and tables, I
am
> issuing a sp_refreshsubscriptions.
> I am then starting the snapshot agent so that the subscribers will get the
> new tables that I have added.
> I then issue an sp_addscriptexec to run a script on the subscriber.
> My question is why does this script that I am executing via the
> sp_addscriptexec run on the subscriber before the snapshot ever gets
applied
> to the subscriber? I need to have the script run after the snapshot has
been
> created and delivered to the subscriber.
> Thanks in advance,
> Stephen
|||I already have a post snapshot command tied to my replication publication.
Since I am already replicating to the subscriber and just adding new tables
and fields, it seems as though the post snapshot command that was orginally
tied to the publication does not get run. I'm saying this as I already have
a post snapshot script applied to my publication and it is not getting run
when I regen the snapshot to create the definitions of the new tables.
So, I do not think that adding the additional script as a post script will
get run either, but in any case, I do not want to tie this script to the
publication definition as I do not want this script to be run whenever a
snapshot has been applied.
Thanks again,
Stephen
"Hilary Cotter" wrote:

> Probably because the sp_addscriptexec is added to the distribution database
> before the snapshot is generated and the sync commands make it there, You
> should perhaps use the post snapshot command for 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
> "Stephen Schissler" <StephenSchissler@.discussions.microsoft.com> wrote in
> message news:9CD0B3BB-6EE6-46DF-8CDE-C8B860890EE1@.microsoft.com...
> model.
> using
> some
> am
> applied
> been
>
>
|||You might want to set your distribution job to run scheduled as opposed to
continuous, stop the log reader agent, and then have the sp_addscriptexec
command run as your final job step. Stop and start your distribution agent.
After the distribution agent has stopped, remove this last step, start up
your log reader agent.
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
"Stephen Schissler" <StephenSchissler@.discussions.microsoft.com> wrote in
message news:E7C9D59C-D82B-412E-9B5B-55DA93574C79@.microsoft.com...
> I already have a post snapshot command tied to my replication publication.
> Since I am already replicating to the subscriber and just adding new
tables
> and fields, it seems as though the post snapshot command that was
orginally
> tied to the publication does not get run. I'm saying this as I already
have[vbcol=seagreen]
> a post snapshot script applied to my publication and it is not getting run
> when I regen the snapshot to create the definitions of the new tables.
> So, I do not think that adding the additional script as a post script will
> get run either, but in any case, I do not want to tie this script to the
> publication definition as I do not want this script to be run whenever a
> snapshot has been applied.
> Thanks again,
> Stephen
> "Hilary Cotter" wrote:
database[vbcol=seagreen]
You[vbcol=seagreen]
in[vbcol=seagreen]
my[vbcol=seagreen]
have[vbcol=seagreen]
I[vbcol=seagreen]
the[vbcol=seagreen]
has[vbcol=seagreen]

Question about snapshot replication and monitoring of msrepl_transactions in the dist.db

OK.
We have this 1 publisher db that is pushing out to two separate
subscribers (one is transactional the other is snapshot). In the
distribution db the publisher_database_id is going to be the same. We
monitor this transaction count by publisher id with the following (
Select count(1) from dbo.MSrepl_transactions where
publisher_database_id = 17). Now that we have added snapshot
replication to one subscriber and transactional to another we have
noticed that this transaction count is not correct. It always says that
there are like 100+ in there waiting, but this is not true. The
transactional publications are always up to date with 0 latency.
It was my understanding that snapshot replication literally just makes a
bcp and push's it over at whatever time you tell it to push. However,
it looks like these extra transactions sitting in my distribution
database have everything to do with the snapshot replication and not the
transactional replication. Does snapshot replication work differently
then i thought? Does snapshot replication actually post transactions to
the distribution db?
Thanks
-comb
what shows up in sp_browsereplcmds. I suspect what you will see there are
the commands which are used for the sync - i.e. to build the tables and
other objects on the subscriber, and the bcp the data there. IIRC the
distribution clean up agent does not clean these up until you are past the
retention period.
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
"combfilter" <asdf@.adsf.com> wrote in message
news:MPG.1e4953894a6972969896eb@.news.newsreader.co m...
> OK.
> We have this 1 publisher db that is pushing out to two separate
> subscribers (one is transactional the other is snapshot). In the
> distribution db the publisher_database_id is going to be the same. We
> monitor this transaction count by publisher id with the following (
> Select count(1) from dbo.MSrepl_transactions where
> publisher_database_id = 17). Now that we have added snapshot
> replication to one subscriber and transactional to another we have
> noticed that this transaction count is not correct. It always says that
> there are like 100+ in there waiting, but this is not true. The
> transactional publications are always up to date with 0 latency.
> It was my understanding that snapshot replication literally just makes a
> bcp and push's it over at whatever time you tell it to push. However,
> it looks like these extra transactions sitting in my distribution
> database have everything to do with the snapshot replication and not the
> transactional replication. Does snapshot replication work differently
> then i thought? Does snapshot replication actually post transactions to
> the distribution db?
> Thanks
> -comb
|||In article <Oq2WvQxJGHA.3064@.TK2MSFTNGP10.phx.gbl>,
hilary.cotter@.gmail.com says...
> what shows up in sp_browsereplcmds. I suspect what you will see there are
> the commands which are used for the sync - i.e. to build the tables and
> other objects on the subscriber, and the bcp the data there. IIRC the
> distribution clean up agent does not clean these up until you are past the
> retention period.
>
why would the bcp data be in the distribution db? wouldn't it just push
that across the net to the subscriber straight up as a .bcp file? why
would a snapshot need to put anything in the distribution db other then
"hey you need to push this .bcp across".
ok so what you are saying is that snapshot replication DOES actually
post transactions to the distribution db? I thought it just generated
the .bcp, idx and whatever that 3rd file is and pushed those across the
net with just a few instructions on where to put them.?
sp_browsereplcmds times out for me. We have a lot of subscribers on
this box. Is there anyway I can use that sp and just look at a certain
db id?
thanks.

Question about setuping replication

Hi Guys,
I have two questions regarging setuping replication.
1. Do I need to configure Recovery Model as full when I want use
transactional replication.
2. Do I need to setup linked server when I use more than two servers for
replication.
3. Is there any doc about how to setup replication?
Thanks.
(1) No
(2) No but look at this:
http://support.microsoft.com/default.aspx?scid=kb;en-us;274098
(3) BOL, Hilary's book and some general links on my site (eg
http://www.mssqlcity.com/Articles/Replic/Replic.htm)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Question about restore replication database

Hi Guys,
I got a trouble that I tried to restore a database that has a replication
created on and I got an error after restore. In log reader agent and
distribution agent became red color and error message like "Could not execute
sp_repldone/sp_replcounters.." Sorry I cannot remember error message
exactly, and I am unable to restart the agent. I guess the reason might be
that I didn't restore distributiuon because I didn't backup this database. Do
I need to restore distribution databse when I restore publication database?
thanks.
Ideally your publication and distribution database would be in lock step by
using the sync with backup option.
Otherwise you should use sp_replrestart to get around problems like 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
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:63B4A13A-87EC-48B3-8867-EBD74B228A24@.microsoft.com...
> Hi Guys,
> I got a trouble that I tried to restore a database that has a replication
> created on and I got an error after restore. In log reader agent and
> distribution agent became red color and error message like "Could not
> execute
> sp_repldone/sp_replcounters.." Sorry I cannot remember error message
> exactly, and I am unable to restart the agent. I guess the reason might be
> that I didn't restore distributiuon because I didn't backup this database.
> Do
> I need to restore distribution databse when I restore publication
> database?
> thanks.
>
|||What is "sync with backup option"? Also, I looked in BOL and could not find
sp_replrestart. I am interested in this because we are seeting up a new
merge publication and am interested in how it affects backup/restore.
Thanks.
David
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23GOSOVNOHHA.780@.TK2MSFTNGP03.phx.gbl...
> Ideally your publication and distribution database would be in lock step
> by using the sync with backup option.
> Otherwise you should use sp_replrestart to get around problems like 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
>
> "Iter" <Iter@.discussions.microsoft.com> wrote in message
> news:63B4A13A-87EC-48B3-8867-EBD74B228A24@.microsoft.com...
>
|||This is for transactional replication and not merge replication.
for more info on sync with backup consult -
http://msdn2.microsoft.com/en-us/library/aa237094(SQL.80).aspx
for more info repl_restart consult
http://msdn2.microsoft.com/en-us/library/ms174390.aspx
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 Chase" <dlchase@.lifetimeinc.com> wrote in message
news:%234Q6oLPOHHA.2232@.TK2MSFTNGP02.phx.gbl...
> What is "sync with backup option"? Also, I looked in BOL and could not
> find sp_replrestart. I am interested in this because we are seeting up a
> new merge publication and am interested in how it affects backup/restore.
> Thanks.
> David
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23GOSOVNOHHA.780@.TK2MSFTNGP03.phx.gbl...
>

Question about replication issue

Question about replication issue
Actually I'm using replication with Interbase/Firebird, I have a problem and
I wanted to know if someone knows how to solve this, and if people from SQL
Server and Oracle replication knows if this happens too with that engines (I
hope not)
I start describing a the tables for the example:
/* Table: TABLE1 */
CREATE TABLE TABLE1 (
ID INTEGER NOT NULL,
NAME VARCHAR (40) CHARACTER SET NONE COLLATE NONE,
FK_TABLE2 INTEGER);
ALTER TABLE TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY (ID);
ALTER TABLE TABLE1 ADD CONSTRAINT R_T2_T1 FOREIGN KEY (ID) REFERENCES TABLE2
(ID);
/* Table: TABLE2 */
CREATE TABLE TABLE2 (
ID INTEGER NOT NULL,
DESCRIPTION VARCHAR (40) CHARACTER SET NONE COLLATE NONE);
ALTER TABLE TABLE2 ADD CONSTRAINT PK_TABLE2 PRIMARY KEY (ID);
That gives me two tables, one with a relation to the other with a foreign
key.
I'm replicating with a one-way schema, and the replication manager creates
the needed triggers and tables in the master database. In one of that
tables, the trigger saves information about the insert, update or delete
occurred. The problem is that in the table (REPL_LOG), only stays a
reference to the record inserted (for example), not the real data inserted.
I give an example:
I insert a record into TABLE1:
ID = 1
NAME = 'My Name'
FK_TABLE2 = Null
The trigger in TABLE1 saves in REPL_LOG = 'I' (Insert), 1 (the PK of the
Table), TABLE1 (an internal ID of the TABLE1 inside the internal dictionary
of the replication schema)
Now, I insert a record into TABLE2:
ID = 1
DESCRIPTION = 'My Description'
The trigger in TABLE2 save in REPL_LOG = 'I', 1, TABLE2
Now, comes the problematic part. I update TABLE1, setting the FK to TABLE2.
FK_TABLE2 = 1 (the PK value of the inserted record in TABLE2)
This creates a record in REPL_LOG like this: 'U' (Update), 1, TABLE1
Up to this, It's all OK with the master database. But the problem comes when
It's time to replicate. Supposing that the replication does not occur
between the prior changes to the database, and all of that comes after
modifying the master database:
The replication fires:
The server finds all records of REPL_LOG in master database, so It first
tries to insert a record in TABLE1, but, here's the problem: the replicator
doesn't know what fields the real insert filled, and it tries to insert ALL
fields (FK_TABLE2 too). So, the replicator server tries to insert a record
like this:
ID = 1 (OK)
NAME = 'My Name' (OK)
FK_TABLE2 = 1 (Ohhh, problem, FOREIGN KEY CONSTRAINT ERROR, It doesn't exist
a record in TABLE2 with PK = 1), (Yet)
So the replication fails...
I can think that in a following step, I can be solved, but I find this
behavior a BIG problem, because this can happen in a lot of situations.
My question is if there is a solution or workaround to solve this with IB
Replicator, and I wanted to know if SQL Server and Oracle replication works
the same as Interbase one.
Are they smart enough to get rid of this problem?
Thank you very much.
Daniel Alvarez
IMS Soluciones Tecnolgicas S.A.
Buenos Aires - Argentina
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.729 / Virus Database: 484 - Release Date: 27/07/2004
Daniel,
in merge, problems like this can be alleviated using the NOT FOR REPLICATION
attribute for the foreign key (see
http://support.microsoft.com/default...b;EN-US;308266).
For transactional, if the articles are part of the same publication, then
AFAIK the changes should be applied in the same order on the subscriber.
HTH,
Paul Ibison

Question about Replication

I have a server that replicate to another machine at 6am, noon and 6pm everyday. The problem is that on Thursdays it should only replicate at noon.
Is it possible to do this and how?
LystraMy gut feeling is that this ought to be possible, but I've never tried it. Unfortunately, I don't see much chance of my having time to try it anytime soon, so I hope that someone else knows the answer for you.

-PatP|||Try this idea - two schedules:

-- Script generated on 8/25/2004 4:37 PM

BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'[Uncategorized (Local)]'

-- Delete the job with the same name (if it exists)
SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'test')
IF (@.JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @.JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''test'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @.job_name = N'test'
SELECT @.JobID = NULL
END

BEGIN

-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT , @.job_name = N'test', @.owner_login_name = N'NCBI_NT\Lozitski', @.description = N'No description available.', @.category_name = N'[Uncategorized (Local)]', @.enabled = 1, @.notify_level_email = 0, @.notify_level_page = 0, @.notify_level_netsend = 0, @.notify_level_eventlog = 2, @.delete_level= 0
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 1, @.step_name = N'step', @.command = N'select getdate()', @.database_name = N'master', @.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 0, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 0, @.on_success_action = 1, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID, @.start_step_id = 1

IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job schedules
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name = N'one', @.enabled = 1, @.freq_type = 8, @.active_start_date = 20040825, @.active_start_time = 60000, @.freq_interval = 111, @.freq_subday_type = 8, @.freq_subday_interval = 6, @.freq_relative_interval = 0, @.freq_recurrence_factor = 1, @.active_end_date = 99991231, @.active_end_time = 180000
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name = N'two', @.enabled = 1, @.freq_type = 8, @.active_start_date = 20040825, @.active_start_time = 115900, @.freq_interval = 16, @.freq_subday_type = 1, @.freq_subday_interval = 0, @.freq_relative_interval = 0, @.freq_recurrence_factor = 1, @.active_end_date = 99991231, @.active_end_time = 235959
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID, @.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:sql

question about Replication

Hello, i am try to build a Pocket PC database program. Using replication, i can sync the data. I want to build a program to record some data and some picture attach to it. Can those picture sync to SQL server by replication? And how to do that on the application? ThanksYes, they can. Just store the data in an image column and replicate that column. It will plug into the replication engine just like any other data.|||question about that image column. how to input an image as a value to that datarow? will it just adding an description showing the path of the file?|||you can search the web for examples.

question about Replication

Hello, i am try to build a Pocket PC database program. Using replication, i can sync the data. I want to build a program to record some data and some picture attach to it. Can those picture sync to SQL server by replication? And how to do that on the application? ThanksYes, they can. Just store the data in an image column and replicate that column. It will plug into the replication engine just like any other data.|||question about that image column. how to input an image as a value to that datarow? will it just adding an description showing the path of the file?|||you can search the web for examples.

Question about Replication

Hi
Let's suppose we have the following design (everything on the same server):
Database1 (Publisher)
Database2 (Publisher)
Database1 and Database2 have the same structure.
Now I create 2 subscribers on a database called 'DatabaseDest'
Subscriber 1-> Source: Database1, Destination: DatabaseDest
Subscriber 2-> Source: Database2, Destination: DatabaseDest
I want DatabaseDest to have data from Database1 and Database2.
Is this possible?
I'm having problems if for example, I'm transferring a record from
Database1 which has the same Primary Key of another record on Database2.
What do you guys suggest for this kind of architecture?
ThanksIf they are on the same server, how about using stored procedures or
triggers to keep the databases synchronized.
If you must use replication, read the BOL very carefully. It has examples
of how to handle PK issues and so forth.
Rick Sawtell
MCT, MCSD, MCDBA|||Rick Sawtell wrote:
> If they are on the same server, how about using stored procedures or
> triggers to keep the databases synchronized.
> If you must use replication, read the BOL very carefully. It has examples
> of how to handle PK issues and so forth.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
Right now is on the same server because I'm just testing. However, in
the real world will be different servers.
I thought that Replication would take case of PK issues automatically.
Isn't that true?|||> Right now is on the same server because I'm just testing. However, in
> the real world will be different servers.
> I thought that Replication would take case of PK issues automatically.
> Isn't that true?
>
That depends. Take a look at the NOT FOR REPLICATION option and so forth.
What type of replication are you looking to do? Merge, Transactional...
Are your subscribers going to be updating back to the publishers? Etc.
Rick Sawtell|||> That depends. Take a look at the NOT FOR REPLICATION option and so forth.
> What type of replication are you looking to do? Merge, Transactional...
> Are your subscribers going to be updating back to the publishers? Etc.
> Rick Sawtell
Ok, I'll take a look right now. I have been testing the 3 methods, but I
have noticed that at least for Snapshot and Merge, SQL server is always
overwriting everything, so I cannot have data from Publisher1 and
Publisher2 at the same time, if I have data with the same PK.
My destination database will be read-only, so the traffic will be always
one way, from the Publishers to the Subscriber. Which type of
replication do you think is the best in my case?
Thanks for your help.|||>
> My destination database will be read-only, so the traffic will be always
> one way, from the Publishers to the Subscriber. Which type of
> replication do you think is the best in my case?
>
Transactional would be best for that scenario.
Rick Sawtell
MCT, MCSD, MCDBA

Question about replication

You are the administrator of a SQL Server 2000 computer at your company's
warehouse. All product orders are shipped from this warehouse. Orders are
received at 30 sales offices. Each sales office offers a range of products
specific to its region.
Each sales office contains one SQL Server 2000 computer. These servers
connect to the warehouse through dial- up connections as needed, typically
once a day. Each sales office needs data pertaining only to its region. You
need to replicate inventory data from the server at the warehouse to the
servers at the sales offices. You want to minimize the amount of time needed
to replicate the data. Which three actions should you take? (Each correct
answer represents part of the solution. Choose three.)
A. Create one publication for each Subscriber.
B. Create one publication for all Subscribers.
C. Enable horizontal filtering.
D.Enable vertical filtering.
E. Use pull subscriptions.
F. Use push subscriptions.
pls. help.
I would create one pub for all subscribers, do horizontal filtering ( so
only the data a subscriber needs would be sent.), and go with pull
subscriptions ( because the work would be done at the subscriber, not at the
publisher)..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:069B5539-A863-446C-9D04-C6879CA20E5B@.microsoft.com...
> You are the administrator of a SQL Server 2000 computer at your company's
> warehouse. All product orders are shipped from this warehouse. Orders are
> received at 30 sales offices. Each sales office offers a range of products
> specific to its region.
> Each sales office contains one SQL Server 2000 computer. These servers
> connect to the warehouse through dial- up connections as needed, typically
> once a day. Each sales office needs data pertaining only to its region.
You
> need to replicate inventory data from the server at the warehouse to the
> servers at the sales offices. You want to minimize the amount of time
needed
> to replicate the data. Which three actions should you take? (Each correct
> answer represents part of the solution. Choose three.)
> A. Create one publication for each Subscriber.
> B. Create one publication for all Subscribers.
> C. Enable horizontal filtering.
> D.Enable vertical filtering.
> E. Use pull subscriptions.
> F. Use push subscriptions.
> pls. help.

Question about replication

Do I need to buy license for replication in SQL Server 2005 Express version?
Or it is totally free?
Roy,
as far as I know it is entirely free to use, whether to replication or
otherwise. However, for our purposes it is somewhat limited - subscriber
only, 1GB RAM, 1 processor and no sql server agent.
Regards,
Paul Ibison, SQL Server MVP (www.replicationanswers.com)
sql

Question about replication

Does SQL Server 2005 Express Edition support replication? How about MSDE 2000?
Its can be a subscriber to merge and transactional publications.
Here is a link which describes the limitations for MSDE and replication:
http://msdn.microsoft.com/library/de...limpl_89cy.asp
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
"Roy" <Roy@.discussions.microsoft.com> wrote in message
news:D24D40E1-B209-4448-A90C-7E1A7DF44504@.microsoft.com...
> Does SQL Server 2005 Express Edition support replication? How about MSDE
2000?

Question about replicating stored procedure execution

I am doing some research in advance of setting up replication. I've
read about replicating stored procedure execution and I need
clarification on what it is exactly.
My understanding is that, for example, if you have a stored procedure
on the publisher that modifies 10,000 rows then replication will not
replicate the 10,000 modifications to the subscriber but will rather
pass along the execute command which will then be executed locally at
the subscriber. Do I have that correct?
Is this a common practice in a replication scenario?
Thanks
Exactly. Its not commonly used because most people don't know about it, and
secondly sometimes the tables referenced by the stored procedures are
different on the publisher and subscriber and therefore you get different
dml on either side.
Other people want the proc run within a serializable transaction for
consistency, which is another option. Is the proc is not run within a
serializable transaction, the DML is replicated instead of the proc.
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
<pshroads@.gmail.com> wrote in message
news:1139532907.083047.89490@.z14g2000cwz.googlegro ups.com...
>I am doing some research in advance of setting up replication. I've
> read about replicating stored procedure execution and I need
> clarification on what it is exactly.
> My understanding is that, for example, if you have a stored procedure
> on the publisher that modifies 10,000 rows then replication will not
> replicate the 10,000 modifications to the subscriber but will rather
> pass along the execute command which will then be executed locally at
> the subscriber. Do I have that correct?
> Is this a common practice in a replication scenario?
> Thanks
>
|||I have tried this feature and worked OK. I have a nightly purge job that
delete lots of data and the processes can be separately on each server (it is
based on creation timestamp of the rows, which was replicated). My
situation, this worked well. I think the serialization consistency will only
gnerate more locks.
However, I ran into problem when try to verify the replication. It treated
the SP as a table and verification failed. Try it, see if you got the same
error. Thk !!

question about red cross icon

Hi guys,
I just re-create replication and everything seems like ok. I can replicate
data into subscriber. Only on the replication monitor icon, it has red cross
icon. under it, in the treeview, each item has red color with cross icon. I
check the history, no error, and data has been transfer successfully. Can
some one tell me how to get rid of it? Thanks.
Please don't cross-post, or at least post to the groups at the same time
(enter multiple entries in the newsgroups section). I've replied to this in
the replication newsgroup.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

question about red cross icon

Hi guys,
I just re-create replication and everything seems like ok. I can replicate
data into subscriber. Only on the replication monitor icon, it has red cross
icon. under it, in the treeview, each item has red color with cross icon. I
check the history, no error, and data has been transfer successfully. Can
some one tell me how to get rid of it? Thanks.Please don't cross-post, or at least post to the groups at the same time
(enter multiple entries in the newsgroups section). I've replied to this in
the replication newsgroup.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

question about red cross icon

Hi guys,
I just re-create replication and everything seems like ok. I can replicate
data into subscriber. Only on the replication monitor icon, it has red cross
icon. under it, in the treeview, each item has red color with cross icon. I
check the history, no error, and data has been transfer successfully. Can
some one tell me how to get rid of it? Thanks.Please don't cross-post, or at least post to the groups at the same time
(enter multiple entries in the newsgroups section). I've replied to this in
the replication newsgroup.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Question about Merge Replication

How is it possible to use single database as a pull subscriber to one Merge Publication and as a Merge Publisher to few push subcriptions?! Actually i have a 3 tiers heirachy in which i need to update data in all directions. For this i need to use Merge replication at all layers but the problem is at the middle layer ( intermidiate Database ).

Please help me.

Regards,

Sorry, it's not clear what you're asking. Are you asking how merge handles changes at differenet nodes, or are you asking about internal details? And I'm not clear what you mean by "middle layer", or intermediate database.

I'm not sure what a three tier hierarchy in your scenario is, but you can have one machine be a publisher/distributor, and the other two machines be subscribers. Changes made at the publisher will flow down to the two subscribers. Changes made at the subscribers will flow up to the publisher, and if necessary, back down to the other subscriber.

|||Sorry if the question was not clear. In Simple words i need to implement a "Publishing-Subscriber Model". Means i need my subscriber be publisher for another subscriber.

Regards,|||What is the reason for needing to republish? You should only want to do this if you need to scale out. Is this what you're trying to accomplish?