Greetings,
I have developed custom replication software that I have been running
in house for 2 years now. During replication operations I have been
SETting IDENT_INSERT OFF and then back ON again (after replication
completes). I have also been disabling and re-enabling my
CONSTRAINTS. I have since learned that it is better to use the "NOT
FOR REPLICATION" option in these cases to avoid the additional
overhead.
As I understand the "NOT FOR REPLICATION" option, it will do
everything that I want it to do during my replication process so long
as I have logged on as a replication agent.
1. What do I have to do in my custom program to authenticate it as a
replication agent? (My goal is for the NOT FOR REPLICATION settings
to "kick in" only during the scheduled execution of my program.) I am
coding in C# using .NET.
2. Will I have to somehow create a replication agent profile or is
there a default one that I can use?
3. Since my program runs all day in the background, will it be
necessary to "log off" as a replication agent during dormant periods?
(I don't want my program to interfere with the smooth runnings of
daily business operations occurring during normal business hours.)
I have spent much time looking for this answer both online and in the
SQL Server help. Your recommendation will be greatly appreciated.
Mario T. Lanza
Clarity Information Architecture, Inc.
2004.09
Mario,
if you use queued updating subscribers, the Identity (Yes, not for
replication) property is sent to the subscriber during the initial snapshot.
The insert stored procedure that the distribution agent uses is different to
the normal one, and at the start has the statement:
BEGIN
set identity insert "tablename" on
....
As you see, this is no different to what you are already using, and is not
inbuilt into the replication agent as such.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I'm sorry but I am using a custom application that handles all aspects
of replication. It builds parameterized insert/update SQL
dynamically. Although I could set identity insert ON then OFF before
and after each row, I would rather do it before and after all the rows
of the table have been inserted/updated. The issue lies in the fact
that the application may be manually or abnormally terminated prior to
my clean up code.
One of the main things my clean up code does is:
1. Restores the identity insert to OFF
2. Restores all CHECK constraints
I have a CHECK constraint on each table which throws an exception if
the proposed identity value is outside of the assigned range at the
store location.
As such, I'm not sure you answered my question.
What do I do so that the "NOT FOR REPLICATION" settings are observed
only when my *custom* replication agent application is running.
Bear in mind I am not using anything provided by Microsoft for
replication.
Thanks.
Mario
|||Mario,
I was comparing your situation to transactional queued
updating subscribers. In this replication setup there is
no global identity insert setting for the agent - it is
set on an individual row insert, in the text of a stored
procedure. However, another poster has mentioned the same
thing in the context of merge replication, where the
agent itself appears to be able to make a global setting
of identity insert. He wanted to be able to execute his
code and 'pretend' to be the merge agent in order to take
advantage of tihs setting, but was unable to do so. Quite
how this is enabled is undocumented and not in the public
domain AFAIK.
Regards,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||That's unfortunate. I was hoping that someone had overcome this issue.
In any case, thanks for responding. I appreciate your effort.
Mario
|||I had the same problem and could not find any solution. I finally found
a merge replication guru guy. He is actually a contractor in Toronto
and selling a special component which is totally able to pretend to be
merge agent.
It is simple to use but may be a little bit expensive for start-up
companies. Since we desperately needed that feature the company just
paid for it instead of digging for weeks over weeks.
It gets SQL Server credentials as class properties and you call
ExecuteSQL method, it executes it as if it is merge agent.
For example I can execute the following SQL against my DB using that
component:
INSERT myTable (IdentityField, column1, column2) VALUES (5, 'test',
test')
and it works!!
you do not need to say SET IDENTITY_INSERT ON/OFF or disable any
constraints.
If you need to contact that merge replication consultant just send me
an email.
Nury Sword
NurySword@.hotmail.com
MCSD - MCDBA
Toronto
to all