Today I discovered that the sunbscriptions were missing from my replication
setup.
I tried to rebuild replication, but when I tried to drop the publications, I
received a message saying:
SQL Server Enterprise Manager could not create publication 'TFWallChart'
from database 'TFWallChart'.
Error 14005: Could not drop publication. A subscription exisits to it.
What should I do?
Thanks
MG
You probably have some subscriptions which have expired. Change your history
retention to match your transaction retention - you should use something
greater than 3 days-to account for long weekends.
Then right click on your the publications folder, and ensure the show
anonymous subscribers is checked. Do any subscribers show up now?
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
"MGeles" <michael.geles@.thomson.com> wrote in message
news:0304FFFF-3490-4443-88A4-422B382BFDCE@.microsoft.com...
> Today I discovered that the sunbscriptions were missing from my
replication
> setup.
> I tried to rebuild replication, but when I tried to drop the publications,
I
> received a message saying:
> SQL Server Enterprise Manager could not create publication 'TFWallChart'
> from database 'TFWallChart'.
> Error 14005: Could not drop publication. A subscription exisits to it.
> What should I do?
> Thanks
> --
> MG
|||I think that I know where to go to set the subscription retention.
Right click on the publication, goto the general tab and set the retention
there.
Where do I need to go to set the transaction retention?
Thanks
MG
"Hilary Cotter" wrote:
> You probably have some subscriptions which have expired. Change your history
> retention to match your transaction retention - you should use something
> greater than 3 days-to account for long weekends.
> Then right click on your the publications folder, and ensure the show
> anonymous subscribers is checked. Do any subscribers show up now?
> --
> 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
> "MGeles" <michael.geles@.thomson.com> wrote in message
> news:0304FFFF-3490-4443-88A4-422B382BFDCE@.microsoft.com...
> replication
> I
>
>
|||Hilary,
When we go to the publication folder, there is no entry in there at all.
The problem is
"Hilary Cotter" wrote:
> You probably have some subscriptions which have expired. Change your history
> retention to match your transaction retention - you should use something
> greater than 3 days-to account for long weekends.
> Then right click on your the publications folder, and ensure the show
> anonymous subscribers is checked. Do any subscribers show up now?
> --
> 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
> "MGeles" <michael.geles@.thomson.com> wrote in message
> news:0304FFFF-3490-4443-88A4-422B382BFDCE@.microsoft.com...
> replication
> I
>
>
|||Hilary,
I right click on the publisher folder (primary), and chose Configure
Publishing, subscribers, and distribution. Then I click subscriber and
uncheck the subscriber. and click apply.
Now when I try to recreate a new transactional publication again w/ the same
name, I get a different error msg:
Error 14294: Supply either @.job_id or @.job_name to idendity the job.
Please help
john
"Hilary Cotter" wrote:
> You probably have some subscriptions which have expired. Change your history
> retention to match your transaction retention - you should use something
> greater than 3 days-to account for long weekends.
> Then right click on your the publications folder, and ensure the show
> anonymous subscribers is checked. Do any subscribers show up now?
> --
> 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
> "MGeles" <michael.geles@.thomson.com> wrote in message
> news:0304FFFF-3490-4443-88A4-422B382BFDCE@.microsoft.com...
> replication
> I
>
>
|||right click on replication monitor, go to distributor properties, click the
properties button.
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
"MGeles" <michael.geles@.thomson.com> wrote in message
news:04D33046-F2BF-4FBC-82C8-265A2EFABA66@.microsoft.com...[vbcol=seagreen]
> I think that I know where to go to set the subscription retention.
> Right click on the publication, goto the general tab and set the retention
> there.
> Where do I need to go to set the transaction retention?
> Thanks
> --
> MG
>
> "Hilary Cotter" wrote:
history[vbcol=seagreen]
publications,[vbcol=seagreen]
'TFWallChart'[vbcol=seagreen]
|||there is something wrong here. Can you query select * from syspublications
in your publication database?
If there is nothing returned from this query someone must have deleted the
publications.
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
"John" <John@.discussions.microsoft.com> wrote in message
news:62B7D3AA-72AE-459F-9ECF-4D4913740392@.microsoft.com...[vbcol=seagreen]
> Hilary,
> When we go to the publication folder, there is no entry in there at all.
> The problem is
> "Hilary Cotter" wrote:
history[vbcol=seagreen]
publications,[vbcol=seagreen]
'TFWallChart'[vbcol=seagreen]
|||sounds like there is some residual meta data in some of the system tables
which you will have to clean up.
I would get the scripts and re-edit them making sure you are using different
log reader, snapshot, and distribution agent name, or delete the parameters
and their values.
Also change the publication name slightly. For example change it from pubs1
to NewPubs1
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
"John" <John@.discussions.microsoft.com> wrote in message
news:7BBE201F-256D-431E-96DB-B8F5F9800F5D@.microsoft.com...
> Hilary,
> I right click on the publisher folder (primary), and chose Configure
> Publishing, subscribers, and distribution. Then I click subscriber and
> uncheck the subscriber. and click apply.
> Now when I try to recreate a new transactional publication again w/ the
same[vbcol=seagreen]
> name, I get a different error msg:
> Error 14294: Supply either @.job_id or @.job_name to idendity the job.
> Please help
> john
>
> "Hilary Cotter" wrote:
history[vbcol=seagreen]
publications,[vbcol=seagreen]
'TFWallChart'[vbcol=seagreen]
Showing posts with label replication. Show all posts
Showing posts with label replication. Show all posts
Monday, March 26, 2012
Friday, March 23, 2012
Rebuilding Identity Seeds
Ok I'm sorry for all the questions. But we had a replication that was deleted
by accident and I've been trying to rebuild it and I'm getting all sorts of
problems.
Currently I've rebuild an anonymous merge replication for SQL CE devices.
I've setup 100,000 identity ranges so they will not have to renew often and
collide.
For some reason when I insert on the handheld it says can't insert a
duplicate identity field etc etc.
I think replication somehow screwed up the ranges for each handheld perhaps
because the old replication was deleted and still settings lingering I don't
know. I need someone to help me understand whats going on.
Basically I have a table right now that the current max through query
analyzer reports: 6700324
But when I execute this:
DBCC CHECKIDENT ('table', NORESEED)
I get:
Checking identity information: current identity value '70338', current
column value '6700324'.
Does that mean that all my handhelds got ranges 100,000 starting at 70338?
No wonder I'm getting duplicate keys right?
Further investigation in the MSrepl_identity_range table shows 3 lines like
this:
objid: 597577167
next_seed: 500000
pub_range: 100000
range: 100000
max_identity: 2147483647
threshold: 80
current_max: 500000
objid: 949578421
next_seed: 200000
pub_range: 100000
range: 100000
max_identity: 2147483647
threshold: 80
current_max: 200000
objid: 1029578706
next_seed: 200000
pub_range: 100000
range: 100000
max_identity: 2147483647
threshold: 80
current_max: 200000
I'm not sure where to go from here. Do I have to somehow manually call a
stored procedure to bump/reseed these subscribers?
Please any help would be greatly appreciated.
Thanks in advance.
Given the last two lines there it looks like two of your tables have started
to use the same identity range.
DBCC CHECKIDENT (TableName ) should reset the identiy fields. So will
truncating the table but obv at a fairly major cost!
"Synced" wrote:
> Ok I'm sorry for all the questions. But we had a replication that was deleted
> by accident and I've been trying to rebuild it and I'm getting all sorts of
> problems.
> Currently I've rebuild an anonymous merge replication for SQL CE devices.
> I've setup 100,000 identity ranges so they will not have to renew often and
> collide.
> For some reason when I insert on the handheld it says can't insert a
> duplicate identity field etc etc.
> I think replication somehow screwed up the ranges for each handheld perhaps
> because the old replication was deleted and still settings lingering I don't
> know. I need someone to help me understand whats going on.
> Basically I have a table right now that the current max through query
> analyzer reports: 6700324
> But when I execute this:
> DBCC CHECKIDENT ('table', NORESEED)
> I get:
> Checking identity information: current identity value '70338', current
> column value '6700324'.
> Does that mean that all my handhelds got ranges 100,000 starting at 70338?
> No wonder I'm getting duplicate keys right?
> Further investigation in the MSrepl_identity_range table shows 3 lines like
> this:
> objid: 597577167
> next_seed: 500000
> pub_range: 100000
> range: 100000
> max_identity: 2147483647
> threshold: 80
> current_max: 500000
> objid: 949578421
> next_seed: 200000
> pub_range: 100000
> range: 100000
> max_identity: 2147483647
> threshold: 80
> current_max: 200000
> objid: 1029578706
> next_seed: 200000
> pub_range: 100000
> range: 100000
> max_identity: 2147483647
> threshold: 80
> current_max: 200000
> I'm not sure where to go from here. Do I have to somehow manually call a
> stored procedure to bump/reseed these subscribers?
> Please any help would be greatly appreciated.
> Thanks in advance.
>
|||Well the ranges being the same on the last 2 are fine since their 2 seperate
tables and 2 seperate identity fields, so should have no problem sharing
ranges as their not related at all.
Could someone please comment. I've gotten further as I've gotten the insert
to work after doing a DBCC CHECKIDENT ('table', RESEED)
But the problem is when I inserted a new record, the ID was very low
compared to my highest ID. Should this be happening? I don't think
replication is smart enough to find out there was an empty chunk lower in the
ID fields and gave a handheld this range. In my experience in the past it
just always gave it a new range up top and the ID's escalate fast if your
redoing the publication alot.
Does this mean I'm eventually going to collide again once the numbers raise
because it gave my handheld a low range?
Please any help would be greatly appreciated. I'm stuck in a jam.
"Synced" wrote:
> Ok I'm sorry for all the questions. But we had a replication that was deleted
> by accident and I've been trying to rebuild it and I'm getting all sorts of
> problems.
> Currently I've rebuild an anonymous merge replication for SQL CE devices.
> I've setup 100,000 identity ranges so they will not have to renew often and
> collide.
> For some reason when I insert on the handheld it says can't insert a
> duplicate identity field etc etc.
> I think replication somehow screwed up the ranges for each handheld perhaps
> because the old replication was deleted and still settings lingering I don't
> know. I need someone to help me understand whats going on.
> Basically I have a table right now that the current max through query
> analyzer reports: 6700324
> But when I execute this:
> DBCC CHECKIDENT ('table', NORESEED)
> I get:
> Checking identity information: current identity value '70338', current
> column value '6700324'.
> Does that mean that all my handhelds got ranges 100,000 starting at 70338?
> No wonder I'm getting duplicate keys right?
> Further investigation in the MSrepl_identity_range table shows 3 lines like
> this:
> objid: 597577167
> next_seed: 500000
> pub_range: 100000
> range: 100000
> max_identity: 2147483647
> threshold: 80
> current_max: 500000
> objid: 949578421
> next_seed: 200000
> pub_range: 100000
> range: 100000
> max_identity: 2147483647
> threshold: 80
> current_max: 200000
> objid: 1029578706
> next_seed: 200000
> pub_range: 100000
> range: 100000
> max_identity: 2147483647
> threshold: 80
> current_max: 200000
> I'm not sure where to go from here. Do I have to somehow manually call a
> stored procedure to bump/reseed these subscribers?
> Please any help would be greatly appreciated.
> Thanks in advance.
>
|||Sorry, mis-read the question. The actual value is 6700324 and the identity
value is 70338. Right.
Are there any gaps in the actual figures? i.e. Are there really 6.7 million
records in the table or not?
Your choice seems to be that you need to renumber the field to get rid of
any gaps.
First of all, get rid of the identity property on the field. Then use the
following code :
declare @.intCounter int
set @.intCounter = 0
update myTable
SET @.intCounter = myTableField = @.intCounter + 1
The field will then have an incremented value for a value, you can then
stick the identity value back on. That should reset it for you.
MCT MCDBA MCSE MCSD MC yadda yadda yadda. Oracle Certified Associate
Remarc Technologies Ltd
Officially Number 1 for SQL 2005 in EMEA.
"Synced" wrote:
[vbcol=seagreen]
> Well the ranges being the same on the last 2 are fine since their 2 seperate
> tables and 2 seperate identity fields, so should have no problem sharing
> ranges as their not related at all.
> Could someone please comment. I've gotten further as I've gotten the insert
> to work after doing a DBCC CHECKIDENT ('table', RESEED)
> But the problem is when I inserted a new record, the ID was very low
> compared to my highest ID. Should this be happening? I don't think
> replication is smart enough to find out there was an empty chunk lower in the
> ID fields and gave a handheld this range. In my experience in the past it
> just always gave it a new range up top and the ID's escalate fast if your
> redoing the publication alot.
> Does this mean I'm eventually going to collide again once the numbers raise
> because it gave my handheld a low range?
> Please any help would be greatly appreciated. I'm stuck in a jam.
>
> "Synced" wrote:
|||Ok well I think your still misunderstanding the problem. The problem is with
the identity ranging allocation. Nothing to do with the current data.
It doesn't matter to us if the ID's have gaps. Basically the replication is
allocating ranges that are either going backwards, or already used ID's
instead of going forwards.
My questions are based on the replication not the actual ID field itself.
So I would hugely greatly appreciate some help from someone who has done
merge replication with identity ranges and knows this stuff. Please I would
be very greatful as I want to understand what is wrong and how to fix it.
Thanks in advance.
"dmdavies" wrote:
[vbcol=seagreen]
> Sorry, mis-read the question. The actual value is 6700324 and the identity
> value is 70338. Right.
> Are there any gaps in the actual figures? i.e. Are there really 6.7 million
> records in the table or not?
> Your choice seems to be that you need to renumber the field to get rid of
> any gaps.
> First of all, get rid of the identity property on the field. Then use the
> following code :
> declare @.intCounter int
> set @.intCounter = 0
> update myTable
> SET @.intCounter = myTableField = @.intCounter + 1
> The field will then have an incremented value for a value, you can then
> stick the identity value back on. That should reset it for you.
> --
> MCT MCDBA MCSE MCSD MC yadda yadda yadda. Oracle Certified Associate
> Remarc Technologies Ltd
> Officially Number 1 for SQL 2005 in EMEA.
>
> "Synced" wrote:
by accident and I've been trying to rebuild it and I'm getting all sorts of
problems.
Currently I've rebuild an anonymous merge replication for SQL CE devices.
I've setup 100,000 identity ranges so they will not have to renew often and
collide.
For some reason when I insert on the handheld it says can't insert a
duplicate identity field etc etc.
I think replication somehow screwed up the ranges for each handheld perhaps
because the old replication was deleted and still settings lingering I don't
know. I need someone to help me understand whats going on.
Basically I have a table right now that the current max through query
analyzer reports: 6700324
But when I execute this:
DBCC CHECKIDENT ('table', NORESEED)
I get:
Checking identity information: current identity value '70338', current
column value '6700324'.
Does that mean that all my handhelds got ranges 100,000 starting at 70338?
No wonder I'm getting duplicate keys right?
Further investigation in the MSrepl_identity_range table shows 3 lines like
this:
objid: 597577167
next_seed: 500000
pub_range: 100000
range: 100000
max_identity: 2147483647
threshold: 80
current_max: 500000
objid: 949578421
next_seed: 200000
pub_range: 100000
range: 100000
max_identity: 2147483647
threshold: 80
current_max: 200000
objid: 1029578706
next_seed: 200000
pub_range: 100000
range: 100000
max_identity: 2147483647
threshold: 80
current_max: 200000
I'm not sure where to go from here. Do I have to somehow manually call a
stored procedure to bump/reseed these subscribers?
Please any help would be greatly appreciated.
Thanks in advance.
Given the last two lines there it looks like two of your tables have started
to use the same identity range.
DBCC CHECKIDENT (TableName ) should reset the identiy fields. So will
truncating the table but obv at a fairly major cost!
"Synced" wrote:
> Ok I'm sorry for all the questions. But we had a replication that was deleted
> by accident and I've been trying to rebuild it and I'm getting all sorts of
> problems.
> Currently I've rebuild an anonymous merge replication for SQL CE devices.
> I've setup 100,000 identity ranges so they will not have to renew often and
> collide.
> For some reason when I insert on the handheld it says can't insert a
> duplicate identity field etc etc.
> I think replication somehow screwed up the ranges for each handheld perhaps
> because the old replication was deleted and still settings lingering I don't
> know. I need someone to help me understand whats going on.
> Basically I have a table right now that the current max through query
> analyzer reports: 6700324
> But when I execute this:
> DBCC CHECKIDENT ('table', NORESEED)
> I get:
> Checking identity information: current identity value '70338', current
> column value '6700324'.
> Does that mean that all my handhelds got ranges 100,000 starting at 70338?
> No wonder I'm getting duplicate keys right?
> Further investigation in the MSrepl_identity_range table shows 3 lines like
> this:
> objid: 597577167
> next_seed: 500000
> pub_range: 100000
> range: 100000
> max_identity: 2147483647
> threshold: 80
> current_max: 500000
> objid: 949578421
> next_seed: 200000
> pub_range: 100000
> range: 100000
> max_identity: 2147483647
> threshold: 80
> current_max: 200000
> objid: 1029578706
> next_seed: 200000
> pub_range: 100000
> range: 100000
> max_identity: 2147483647
> threshold: 80
> current_max: 200000
> I'm not sure where to go from here. Do I have to somehow manually call a
> stored procedure to bump/reseed these subscribers?
> Please any help would be greatly appreciated.
> Thanks in advance.
>
|||Well the ranges being the same on the last 2 are fine since their 2 seperate
tables and 2 seperate identity fields, so should have no problem sharing
ranges as their not related at all.
Could someone please comment. I've gotten further as I've gotten the insert
to work after doing a DBCC CHECKIDENT ('table', RESEED)
But the problem is when I inserted a new record, the ID was very low
compared to my highest ID. Should this be happening? I don't think
replication is smart enough to find out there was an empty chunk lower in the
ID fields and gave a handheld this range. In my experience in the past it
just always gave it a new range up top and the ID's escalate fast if your
redoing the publication alot.
Does this mean I'm eventually going to collide again once the numbers raise
because it gave my handheld a low range?
Please any help would be greatly appreciated. I'm stuck in a jam.
"Synced" wrote:
> Ok I'm sorry for all the questions. But we had a replication that was deleted
> by accident and I've been trying to rebuild it and I'm getting all sorts of
> problems.
> Currently I've rebuild an anonymous merge replication for SQL CE devices.
> I've setup 100,000 identity ranges so they will not have to renew often and
> collide.
> For some reason when I insert on the handheld it says can't insert a
> duplicate identity field etc etc.
> I think replication somehow screwed up the ranges for each handheld perhaps
> because the old replication was deleted and still settings lingering I don't
> know. I need someone to help me understand whats going on.
> Basically I have a table right now that the current max through query
> analyzer reports: 6700324
> But when I execute this:
> DBCC CHECKIDENT ('table', NORESEED)
> I get:
> Checking identity information: current identity value '70338', current
> column value '6700324'.
> Does that mean that all my handhelds got ranges 100,000 starting at 70338?
> No wonder I'm getting duplicate keys right?
> Further investigation in the MSrepl_identity_range table shows 3 lines like
> this:
> objid: 597577167
> next_seed: 500000
> pub_range: 100000
> range: 100000
> max_identity: 2147483647
> threshold: 80
> current_max: 500000
> objid: 949578421
> next_seed: 200000
> pub_range: 100000
> range: 100000
> max_identity: 2147483647
> threshold: 80
> current_max: 200000
> objid: 1029578706
> next_seed: 200000
> pub_range: 100000
> range: 100000
> max_identity: 2147483647
> threshold: 80
> current_max: 200000
> I'm not sure where to go from here. Do I have to somehow manually call a
> stored procedure to bump/reseed these subscribers?
> Please any help would be greatly appreciated.
> Thanks in advance.
>
|||Sorry, mis-read the question. The actual value is 6700324 and the identity
value is 70338. Right.
Are there any gaps in the actual figures? i.e. Are there really 6.7 million
records in the table or not?
Your choice seems to be that you need to renumber the field to get rid of
any gaps.
First of all, get rid of the identity property on the field. Then use the
following code :
declare @.intCounter int
set @.intCounter = 0
update myTable
SET @.intCounter = myTableField = @.intCounter + 1
The field will then have an incremented value for a value, you can then
stick the identity value back on. That should reset it for you.
MCT MCDBA MCSE MCSD MC yadda yadda yadda. Oracle Certified Associate
Remarc Technologies Ltd
Officially Number 1 for SQL 2005 in EMEA.
"Synced" wrote:
[vbcol=seagreen]
> Well the ranges being the same on the last 2 are fine since their 2 seperate
> tables and 2 seperate identity fields, so should have no problem sharing
> ranges as their not related at all.
> Could someone please comment. I've gotten further as I've gotten the insert
> to work after doing a DBCC CHECKIDENT ('table', RESEED)
> But the problem is when I inserted a new record, the ID was very low
> compared to my highest ID. Should this be happening? I don't think
> replication is smart enough to find out there was an empty chunk lower in the
> ID fields and gave a handheld this range. In my experience in the past it
> just always gave it a new range up top and the ID's escalate fast if your
> redoing the publication alot.
> Does this mean I'm eventually going to collide again once the numbers raise
> because it gave my handheld a low range?
> Please any help would be greatly appreciated. I'm stuck in a jam.
>
> "Synced" wrote:
|||Ok well I think your still misunderstanding the problem. The problem is with
the identity ranging allocation. Nothing to do with the current data.
It doesn't matter to us if the ID's have gaps. Basically the replication is
allocating ranges that are either going backwards, or already used ID's
instead of going forwards.
My questions are based on the replication not the actual ID field itself.
So I would hugely greatly appreciate some help from someone who has done
merge replication with identity ranges and knows this stuff. Please I would
be very greatful as I want to understand what is wrong and how to fix it.
Thanks in advance.
"dmdavies" wrote:
[vbcol=seagreen]
> Sorry, mis-read the question. The actual value is 6700324 and the identity
> value is 70338. Right.
> Are there any gaps in the actual figures? i.e. Are there really 6.7 million
> records in the table or not?
> Your choice seems to be that you need to renumber the field to get rid of
> any gaps.
> First of all, get rid of the identity property on the field. Then use the
> following code :
> declare @.intCounter int
> set @.intCounter = 0
> update myTable
> SET @.intCounter = myTableField = @.intCounter + 1
> The field will then have an incremented value for a value, you can then
> stick the identity value back on. That should reset it for you.
> --
> MCT MCDBA MCSE MCSD MC yadda yadda yadda. Oracle Certified Associate
> Remarc Technologies Ltd
> Officially Number 1 for SQL 2005 in EMEA.
>
> "Synced" wrote:
rebuild the replication
Easiest way is to script out replication on the publisher
using enterprise manager, modify the server names (change
Server X to Server Y and vice-versa) and keep this script
for failover. Once server X goes down, you apply the
script on Server Y.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Is there a way that I can rebuild the database in such a way that it becomes
original publisher and the subscriber can then push all changes been made so
far to it to publisher?
Here is what I want to do. Tell me if I 'm thinking wrong.
Server X is publisher and server Y is subscriber with merge replication set.
Now when X is up and Y is standby then all transactions are done at X and
been replicated to Y.
Now let's say X goes down by any reason (Fault or Disaster).
So I make Y as the main server for all user connections and transactions. I
still don't change the replication and keep it as subscriber.
Now how should I rebuild X so that I can make it publisher the way it was
and when I establish the connection, it merges all changes been made at
subscriber and take over again as publisher?
Is this something doable ?
Should we do this or not?
Please advice.
Sam
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1faf01c51bed$77819fb0$a601280a@.phx.gbl...
> Easiest way is to script out replication on the publisher
> using enterprise manager, modify the server names (change
> Server X to Server Y and vice-versa) and keep this script
> for failover. Once server X goes down, you apply the
> script on Server Y.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
using enterprise manager, modify the server names (change
Server X to Server Y and vice-versa) and keep this script
for failover. Once server X goes down, you apply the
script on Server Y.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Is there a way that I can rebuild the database in such a way that it becomes
original publisher and the subscriber can then push all changes been made so
far to it to publisher?
Here is what I want to do. Tell me if I 'm thinking wrong.
Server X is publisher and server Y is subscriber with merge replication set.
Now when X is up and Y is standby then all transactions are done at X and
been replicated to Y.
Now let's say X goes down by any reason (Fault or Disaster).
So I make Y as the main server for all user connections and transactions. I
still don't change the replication and keep it as subscriber.
Now how should I rebuild X so that I can make it publisher the way it was
and when I establish the connection, it merges all changes been made at
subscriber and take over again as publisher?
Is this something doable ?
Should we do this or not?
Please advice.
Sam
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1faf01c51bed$77819fb0$a601280a@.phx.gbl...
> Easiest way is to script out replication on the publisher
> using enterprise manager, modify the server names (change
> Server X to Server Y and vice-versa) and keep this script
> for failover. Once server X goes down, you apply the
> script on Server Y.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
Wednesday, March 21, 2012
Rebuild indexes on subscriber?
In a transactional replication scenario, should i consider rebuilding
indexes on the subscriber articles? Does that place a lock on the
subscriber? most of those tables are used only for select queries.
Thanks.
IN SQL 2000 is does, with online=on in SQL 2005 EE it does not.
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
"rt" <rtemple3@.gmail.com> wrote in message
news:1171914789.341188.155130@.l53g2000cwa.googlegr oups.com...
> In a transactional replication scenario, should i consider rebuilding
> indexes on the subscriber articles? Does that place a lock on the
> subscriber? most of those tables are used only for select queries.
> Thanks.
>
|||Just to add to Hilary's anwser, you could use dbcc indexdefrag if concerned
about table locks required when using dbcc dbreindex for SQL Server 2000. In
SQL Server 2005, as well as the Alter Index versions of these DBCC
statements, there is the posibility of using READ_COMMITTED_SNAPSHOT
isolation in the client queries.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
indexes on the subscriber articles? Does that place a lock on the
subscriber? most of those tables are used only for select queries.
Thanks.
IN SQL 2000 is does, with online=on in SQL 2005 EE it does not.
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
"rt" <rtemple3@.gmail.com> wrote in message
news:1171914789.341188.155130@.l53g2000cwa.googlegr oups.com...
> In a transactional replication scenario, should i consider rebuilding
> indexes on the subscriber articles? Does that place a lock on the
> subscriber? most of those tables are used only for select queries.
> Thanks.
>
|||Just to add to Hilary's anwser, you could use dbcc indexdefrag if concerned
about table locks required when using dbcc dbreindex for SQL Server 2000. In
SQL Server 2005, as well as the Alter Index versions of these DBCC
statements, there is the posibility of using READ_COMMITTED_SNAPSHOT
isolation in the client queries.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
Tuesday, March 20, 2012
Rebuild and Reorganize
will maintenance tasks like rebuilding and reorganizing indexes be replicated in transactional replication, or do i have to setup these management tasks on the subscribers as well?
No. These operations are not replicated.
Peng
|||so if i set these tasks up on the subscriber will it affect replication in a negative way?|||As far as I know, there should be no negative impact. Of course, you need to test on your machines to see if the extra workload affects the performance much.Rebooting Server for Replication to work
At times, we have come across issues around replication such as high CPU for
transactional replication in SQL 2000 that requires a reboot to fix it.
Do we know why a reboot fixes ? Right now, we just reboot the server, but
dont know how it resolves and what it resolves under the hood ?
We are on SQL SP4. It doesnt happen all the time, just maybe once in 6
months or so.
Thanks
I suspect you are bumping into this.
http://support.microsoft.com/default.aspx/kb/812263
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
"Hassan" <Hassan@.hotmail.com> wrote in message
news:ukenRgwFHHA.1232@.TK2MSFTNGP05.phx.gbl...
> At times, we have come across issues around replication such as high CPU
> for transactional replication in SQL 2000 that requires a reboot to fix
> it.
> Do we know why a reboot fixes ? Right now, we just reboot the server, but
> dont know how it resolves and what it resolves under the hood ?
> We are on SQL SP4. It doesnt happen all the time, just maybe once in 6
> months or so.
> Thanks
>
|||Well maybe I wasnt too clear.. It does work, but it just causes a high CPU
like 90% and things start to slow down on the server. Stopping the SQL Agent
helps but the minute you start it again, it starts to peg again due to the
replication agents.
Any other thoughts ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23iEjI2wFHHA.1232@.TK2MSFTNGP05.phx.gbl...
>I suspect you are bumping into this.
> http://support.microsoft.com/default.aspx/kb/812263
> --
> 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
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:ukenRgwFHHA.1232@.TK2MSFTNGP05.phx.gbl...
>
|||Hassan,
as soon as this occurs, you could do some investigation and post back. Use
sp_who2 and DBCC INPUTBUFFER to see what the agent is doing and if there is
blocking involved. Also, use sp_browsereplcommands and MSDistribution_status
to see how what commands are in the queue.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||One more thing Hilary, do you think this bug is fixed in SP4 ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23iEjI2wFHHA.1232@.TK2MSFTNGP05.phx.gbl...
>I suspect you are bumping into this.
> http://support.microsoft.com/default.aspx/kb/812263
> --
> 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
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:ukenRgwFHHA.1232@.TK2MSFTNGP05.phx.gbl...
>
|||Probably a good idea to move distributor to dedicated box... just 1
occurence in 12 mo. period is justification for this...
Chris MCDBA MCSE
MSSQLConsulting.com
"Hassan" wrote:
> At times, we have come across issues around replication such as high CPU for
> transactional replication in SQL 2000 that requires a reboot to fix it.
> Do we know why a reboot fixes ? Right now, we just reboot the server, but
> dont know how it resolves and what it resolves under the hood ?
> We are on SQL SP4. It doesnt happen all the time, just maybe once in 6
> months or so.
> Thanks
>
>
|||It is unclear if it is or not. It seems that I am bumping into it all the
time on my laptop. I kill the log reader agents and this seems to solve it.
Can you tell if it is the agent which consumes all cpu or SQL Server?
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
"Hassan" <Hassan@.hotmail.com> wrote in message
news:eV6jC9zFHHA.4588@.TK2MSFTNGP05.phx.gbl...
> One more thing Hilary, do you think this bug is fixed in SP4 ?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23iEjI2wFHHA.1232@.TK2MSFTNGP05.phx.gbl...
>
|||I'll check the next time it occurs..
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23VqLoW7FHHA.1276@.TK2MSFTNGP04.phx.gbl...
> It is unclear if it is or not. It seems that I am bumping into it all the
> time on my laptop. I kill the log reader agents and this seems to solve
> it.
> Can you tell if it is the agent which consumes all cpu or SQL Server?
> --
> 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
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:eV6jC9zFHHA.4588@.TK2MSFTNGP05.phx.gbl...
>
transactional replication in SQL 2000 that requires a reboot to fix it.
Do we know why a reboot fixes ? Right now, we just reboot the server, but
dont know how it resolves and what it resolves under the hood ?
We are on SQL SP4. It doesnt happen all the time, just maybe once in 6
months or so.
Thanks
I suspect you are bumping into this.
http://support.microsoft.com/default.aspx/kb/812263
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
"Hassan" <Hassan@.hotmail.com> wrote in message
news:ukenRgwFHHA.1232@.TK2MSFTNGP05.phx.gbl...
> At times, we have come across issues around replication such as high CPU
> for transactional replication in SQL 2000 that requires a reboot to fix
> it.
> Do we know why a reboot fixes ? Right now, we just reboot the server, but
> dont know how it resolves and what it resolves under the hood ?
> We are on SQL SP4. It doesnt happen all the time, just maybe once in 6
> months or so.
> Thanks
>
|||Well maybe I wasnt too clear.. It does work, but it just causes a high CPU
like 90% and things start to slow down on the server. Stopping the SQL Agent
helps but the minute you start it again, it starts to peg again due to the
replication agents.
Any other thoughts ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23iEjI2wFHHA.1232@.TK2MSFTNGP05.phx.gbl...
>I suspect you are bumping into this.
> http://support.microsoft.com/default.aspx/kb/812263
> --
> 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
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:ukenRgwFHHA.1232@.TK2MSFTNGP05.phx.gbl...
>
|||Hassan,
as soon as this occurs, you could do some investigation and post back. Use
sp_who2 and DBCC INPUTBUFFER to see what the agent is doing and if there is
blocking involved. Also, use sp_browsereplcommands and MSDistribution_status
to see how what commands are in the queue.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||One more thing Hilary, do you think this bug is fixed in SP4 ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23iEjI2wFHHA.1232@.TK2MSFTNGP05.phx.gbl...
>I suspect you are bumping into this.
> http://support.microsoft.com/default.aspx/kb/812263
> --
> 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
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:ukenRgwFHHA.1232@.TK2MSFTNGP05.phx.gbl...
>
|||Probably a good idea to move distributor to dedicated box... just 1
occurence in 12 mo. period is justification for this...
Chris MCDBA MCSE
MSSQLConsulting.com
"Hassan" wrote:
> At times, we have come across issues around replication such as high CPU for
> transactional replication in SQL 2000 that requires a reboot to fix it.
> Do we know why a reboot fixes ? Right now, we just reboot the server, but
> dont know how it resolves and what it resolves under the hood ?
> We are on SQL SP4. It doesnt happen all the time, just maybe once in 6
> months or so.
> Thanks
>
>
|||It is unclear if it is or not. It seems that I am bumping into it all the
time on my laptop. I kill the log reader agents and this seems to solve it.
Can you tell if it is the agent which consumes all cpu or SQL Server?
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
"Hassan" <Hassan@.hotmail.com> wrote in message
news:eV6jC9zFHHA.4588@.TK2MSFTNGP05.phx.gbl...
> One more thing Hilary, do you think this bug is fixed in SP4 ?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23iEjI2wFHHA.1232@.TK2MSFTNGP05.phx.gbl...
>
|||I'll check the next time it occurs..
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23VqLoW7FHHA.1276@.TK2MSFTNGP04.phx.gbl...
> It is unclear if it is or not. It seems that I am bumping into it all the
> time on my laptop. I kill the log reader agents and this seems to solve
> it.
> Can you tell if it is the agent which consumes all cpu or SQL Server?
> --
> 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
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:eV6jC9zFHHA.4588@.TK2MSFTNGP05.phx.gbl...
>
Saturday, February 25, 2012
Real time Replication with ms sql
hello,
I want to create real time replication of my databaser server.I have two database srevers,one is master which having real time data and another slave,where i want to replicate data from master .
plz help me to configure solution
Thanks,
Chetan S. Raut.
what you need is Transactional Replicaton. Read about this in BOL
http://www.databasejournal.com/features/mssql/article.php/1438201
http://www.mssqlcity.com/Articles/Replic/SetupTR/SetupTR.htm
Madhu
Real time replication over geographically divers sites
Hi,
Myself and a colleague have been charged with making two SQL servers
(SQL Server 2000 STD) replicate in real time, this is to allow a
helpdesk system to be updated in either the UK or South Africa.
Unfortunately as the helpdesk system we use is not by our own design
we have to use the same reference range.
Our fear is that a user would enter data in the UK taking the next
available reference at the same time as one in SA which would result
in clashing data!
Considering that the latency between these sites can be as high as
500ms what do you think would be our best solution?
Many thanks for any advice offered.
John...
What happens if the internet goes down?
You could run Merge replication every 5-10 minutes with no chance of data
clashes for tables that use identities.
Jim.
"John Norton" wrote:
> Hi,
> Myself and a colleague have been charged with making two SQL servers
> (SQL Server 2000 STD) replicate in real time, this is to allow a
> helpdesk system to be updated in either the UK or South Africa.
> Unfortunately as the helpdesk system we use is not by our own design
> we have to use the same reference range.
> Our fear is that a user would enter data in the UK taking the next
> available reference at the same time as one in SA which would result
> in clashing data!
> Considering that the latency between these sites can be as high as
> 500ms what do you think would be our best solution?
> Many thanks for any advice offered.
> John...
>
|||That depends on what the cost of latency is.
For something like this you should probably look at a citrix solution where
the UK and SA users both logon to the same database at the same time.
However, you can probably do this using replication if you relax the latency
requirements to a minute or more, and then use bi-directional transactional
replication, if and only if you can partition your data - possibly by using
dbcc checkident to create different ranges in the UK and SA.
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
"John Norton" <JNorton@.DPS-integro.co.uk> wrote in message
news:a2f128be.0503310645.16813d3d@.posting.google.c om...
> Hi,
> Myself and a colleague have been charged with making two SQL servers
> (SQL Server 2000 STD) replicate in real time, this is to allow a
> helpdesk system to be updated in either the UK or South Africa.
> Unfortunately as the helpdesk system we use is not by our own design
> we have to use the same reference range.
> Our fear is that a user would enter data in the UK taking the next
> available reference at the same time as one in SA which would result
> in clashing data!
> Considering that the latency between these sites can be as high as
> 500ms what do you think would be our best solution?
> Many thanks for any advice offered.
> John...
Myself and a colleague have been charged with making two SQL servers
(SQL Server 2000 STD) replicate in real time, this is to allow a
helpdesk system to be updated in either the UK or South Africa.
Unfortunately as the helpdesk system we use is not by our own design
we have to use the same reference range.
Our fear is that a user would enter data in the UK taking the next
available reference at the same time as one in SA which would result
in clashing data!
Considering that the latency between these sites can be as high as
500ms what do you think would be our best solution?
Many thanks for any advice offered.
John...
What happens if the internet goes down?
You could run Merge replication every 5-10 minutes with no chance of data
clashes for tables that use identities.
Jim.
"John Norton" wrote:
> Hi,
> Myself and a colleague have been charged with making two SQL servers
> (SQL Server 2000 STD) replicate in real time, this is to allow a
> helpdesk system to be updated in either the UK or South Africa.
> Unfortunately as the helpdesk system we use is not by our own design
> we have to use the same reference range.
> Our fear is that a user would enter data in the UK taking the next
> available reference at the same time as one in SA which would result
> in clashing data!
> Considering that the latency between these sites can be as high as
> 500ms what do you think would be our best solution?
> Many thanks for any advice offered.
> John...
>
|||That depends on what the cost of latency is.
For something like this you should probably look at a citrix solution where
the UK and SA users both logon to the same database at the same time.
However, you can probably do this using replication if you relax the latency
requirements to a minute or more, and then use bi-directional transactional
replication, if and only if you can partition your data - possibly by using
dbcc checkident to create different ranges in the UK and SA.
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
"John Norton" <JNorton@.DPS-integro.co.uk> wrote in message
news:a2f128be.0503310645.16813d3d@.posting.google.c om...
> Hi,
> Myself and a colleague have been charged with making two SQL servers
> (SQL Server 2000 STD) replicate in real time, this is to allow a
> helpdesk system to be updated in either the UK or South Africa.
> Unfortunately as the helpdesk system we use is not by our own design
> we have to use the same reference range.
> Our fear is that a user would enter data in the UK taking the next
> available reference at the same time as one in SA which would result
> in clashing data!
> Considering that the latency between these sites can be as high as
> 500ms what do you think would be our best solution?
> Many thanks for any advice offered.
> John...
Monday, February 20, 2012
REAL REAL Experts needed for pretending to be replication_agent
Hi All,
We know that, "NOT FOR REPLICATION" option makes it possible for
replication agents ,let's say, to insert rows without being
constrained by IDENTITY restriction or check constraints(And also it
does not change SEED value in the table).
That means, being a replication agent is like having a PASS CARD that
allows them to get rid of constraint checks. (But disabling
constraints is not a solution)
But, during conflict resolution coding, I need to INSERT some data to
some tables (related tables), but I am facing CONSTRAINTS that merge
agent does not face.
Is there any way of pretending to be like merge_agent for constraint
overcoming? THIS IS THE CORE QUESTION.
To be more specific, is there any way of setting sessionproperty for
replication_agent? That is: when you execute the following query in a
normal connection;
select sessionproperty('replication_agent')
you get "0".
but for replication_agent that function returns "1". Microsoft guys
somehow set connection property for agent, and how they do it is
scritly undocumented.
I tried setting context_info in sysprocesses table to "8" but it did
not work. That SP also is not enough by itself
:sp_MSsetcontext_replagent
Please help, if any way of pretending to be merge agent.
PS:I event wrote an application with ReplMerg.exe process name
guessing that SQL may know agent from its process name.
And also please don't suggest disabling constraints because it is not
enough, e.g. replication_agent does not affect SEED value for
inserts that it does..
I hope some REAL expert will hear my SCREAM.
Thanks alot in advance,
Nury SWORD
MCDBA - MCSD
Toronto
I suggest you contact PSS for an answer to your question.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Nury SWORD" <nurysword@.hotmail.com> wrote in message
news:16342dc6.0409170707.65ef87e1@.posting.google.c om...
> Hi All,
> We know that, "NOT FOR REPLICATION" option makes it possible for
> replication agents ,let's say, to insert rows without being
> constrained by IDENTITY restriction or check constraints(And also it
> does not change SEED value in the table).
> That means, being a replication agent is like having a PASS CARD that
> allows them to get rid of constraint checks. (But disabling
> constraints is not a solution)
> But, during conflict resolution coding, I need to INSERT some data to
> some tables (related tables), but I am facing CONSTRAINTS that merge
> agent does not face.
> Is there any way of pretending to be like merge_agent for constraint
> overcoming? THIS IS THE CORE QUESTION.
> To be more specific, is there any way of setting sessionproperty for
> replication_agent? That is: when you execute the following query in a
> normal connection;
> select sessionproperty('replication_agent')
> you get "0".
> but for replication_agent that function returns "1". Microsoft guys
> somehow set connection property for agent, and how they do it is
> scritly undocumented.
> I tried setting context_info in sysprocesses table to "8" but it did
> not work. That SP also is not enough by itself
> :sp_MSsetcontext_replagent
> Please help, if any way of pretending to be merge agent.
> PS:I event wrote an application with ReplMerg.exe process name
> guessing that SQL may know agent from its process name.
> And also please don't suggest disabling constraints because it is not
> enough, e.g. replication_agent does not affect SEED value for
> inserts that it does..
> I hope some REAL expert will hear my SCREAM.
> Thanks alot in advance,
> Nury SWORD
> MCDBA - MCSD
> Toronto
|||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
|||Nury,
as far as I understand, it is not possible to take the context of the merge
agent. Are you sure that this is how the component works?
Do you have to 'tell' the component what table you are working with? If so,
it might just be doing some dynamic SQL:
exec('set identity_insert tcompany on;insert into tcompany(id, companyname)
values(34,''test'');set identity_insert tcompany off')
Can you send me the details of this consultant as I'd like to ask him about
it.
Regards,
Paul Ibison (SQL Server MVP)
"Nury" <nurysword@.hotmail.com> wrote in message
news:1106863095.810873.7130@.z14g2000cwz.googlegrou ps.com...
> 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
>
We know that, "NOT FOR REPLICATION" option makes it possible for
replication agents ,let's say, to insert rows without being
constrained by IDENTITY restriction or check constraints(And also it
does not change SEED value in the table).
That means, being a replication agent is like having a PASS CARD that
allows them to get rid of constraint checks. (But disabling
constraints is not a solution)
But, during conflict resolution coding, I need to INSERT some data to
some tables (related tables), but I am facing CONSTRAINTS that merge
agent does not face.
Is there any way of pretending to be like merge_agent for constraint
overcoming? THIS IS THE CORE QUESTION.
To be more specific, is there any way of setting sessionproperty for
replication_agent? That is: when you execute the following query in a
normal connection;
select sessionproperty('replication_agent')
you get "0".
but for replication_agent that function returns "1". Microsoft guys
somehow set connection property for agent, and how they do it is
scritly undocumented.
I tried setting context_info in sysprocesses table to "8" but it did
not work. That SP also is not enough by itself
:sp_MSsetcontext_replagent
Please help, if any way of pretending to be merge agent.
PS:I event wrote an application with ReplMerg.exe process name
guessing that SQL may know agent from its process name.
And also please don't suggest disabling constraints because it is not
enough, e.g. replication_agent does not affect SEED value for
inserts that it does..
I hope some REAL expert will hear my SCREAM.
Thanks alot in advance,
Nury SWORD
MCDBA - MCSD
Toronto
I suggest you contact PSS for an answer to your question.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Nury SWORD" <nurysword@.hotmail.com> wrote in message
news:16342dc6.0409170707.65ef87e1@.posting.google.c om...
> Hi All,
> We know that, "NOT FOR REPLICATION" option makes it possible for
> replication agents ,let's say, to insert rows without being
> constrained by IDENTITY restriction or check constraints(And also it
> does not change SEED value in the table).
> That means, being a replication agent is like having a PASS CARD that
> allows them to get rid of constraint checks. (But disabling
> constraints is not a solution)
> But, during conflict resolution coding, I need to INSERT some data to
> some tables (related tables), but I am facing CONSTRAINTS that merge
> agent does not face.
> Is there any way of pretending to be like merge_agent for constraint
> overcoming? THIS IS THE CORE QUESTION.
> To be more specific, is there any way of setting sessionproperty for
> replication_agent? That is: when you execute the following query in a
> normal connection;
> select sessionproperty('replication_agent')
> you get "0".
> but for replication_agent that function returns "1". Microsoft guys
> somehow set connection property for agent, and how they do it is
> scritly undocumented.
> I tried setting context_info in sysprocesses table to "8" but it did
> not work. That SP also is not enough by itself
> :sp_MSsetcontext_replagent
> Please help, if any way of pretending to be merge agent.
> PS:I event wrote an application with ReplMerg.exe process name
> guessing that SQL may know agent from its process name.
> And also please don't suggest disabling constraints because it is not
> enough, e.g. replication_agent does not affect SEED value for
> inserts that it does..
> I hope some REAL expert will hear my SCREAM.
> Thanks alot in advance,
> Nury SWORD
> MCDBA - MCSD
> Toronto
|||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
|||Nury,
as far as I understand, it is not possible to take the context of the merge
agent. Are you sure that this is how the component works?
Do you have to 'tell' the component what table you are working with? If so,
it might just be doing some dynamic SQL:
exec('set identity_insert tcompany on;insert into tcompany(id, companyname)
values(34,''test'');set identity_insert tcompany off')
Can you send me the details of this consultant as I'd like to ask him about
it.
Regards,
Paul Ibison (SQL Server MVP)
"Nury" <nurysword@.hotmail.com> wrote in message
news:1106863095.810873.7130@.z14g2000cwz.googlegrou ps.com...
> 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
>
ReadWriteDeleteMessageFile FAILURE with SQL Server Mobile Server Agent Diagnostics
Hi all.
Is there a difference with sql replication between sql server 2005 64bit and sql server 2005 32bit?
Both are on a Windows 2003 server. One is 32 bit and the other one is 64 bit.
The first time I set up sql replication (test environment), it was on a 32 bit sql server. This worked fine.
The second time I wanted to set up replication (live environment), it was on a 64 bit sql server. This didn't worked fine.
I can't call the sqlcesa30.dll file on the 64bit server with IIS. So I called the dll via a remote IIS server.
This worked but gave me the following error when calling the following URL:
http://domain/PDASYNC/sqlcesa30.dll?diag
SQL Server Mobile Server Agent Diagnostics
2007/07/31 14:26:55
After seeing this I looked up what ReadWriteDeleteMessageFile could mean. It appeared to be some NTFS acces problem. But when I checked the security settings, it all seemed to be ok.
Has anyone any idea what else to check? Or is sure that I must have done something wrong?
Thanks in advance,
Arjan
Hello,
Currently there are some issues with server tools on 64 bit machines. I think, we can get 64 bit SQL CE, sometime, in near future.
Thanks
Udaya.
|||Ok, thnx.
We currently got it working through a 32bit webserver
Subscribe to:
Posts (Atom)