SQL Server 2008 "Not for Replication"

[kwvh]

Is it just me, or is the "Not for Replication" confusing to others?

If you put "Yes' in the field in table design, does that mean, "Yes, do NOT use for Replication", or does it mean "Yes, DO use for Replication"?

Which is it? Any help is GREATLY appreciated.

[HansV]

Setting "Not For Replication" to Yes means that some actions will NOT be performed when a replication agent performs an insert, update or delete operation.
See Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION.

[kwvh]

Hans,
THANKS! I am still not sure I understand everything in the link provided.

I created a Microsoft SQL Server database and had user install on their server. Their IT folks won't allow Microsoft Access to touch SQL Server databases. They will, however, replicate the data in read only allowing Access to read the replicated data. My predicament is I don't understand how the "Not for Replication" Yes/No works. To me, a simple "Replication" or "For Replication" and a Yes/No would seem logical. The NOT throws me for a loop.

So IF I want to use the field for replication, "Not for Replication" should be set to "No"? Is that correct?

Thank you for your patience with my ignorance.

[HansV]

I don't work directly with SQL Server myself, so I'm largely ignorant, but I think the issue is this. By default, an Identity field creates a new value for each new record. So if records are replicated, the replicated records would get new identity values instead of inheriting the value from the original record. By setting Not For Replication to Yes for the Identity field, you suppress the default behavior of creating a new value, so that replicated records keep the same Identity value.

[Wendell]

... So IF I want to use the field for replication, "Not for Replication" should be set to "No"? Is that correct? ...

I believe you will find if you create a new identity (or a new constraint or index) that parameter will default to "No" - it is rarely used in replication scenarios. The most common case is where data from several databases are being aggregated into a single master, and you may have relational integrity issues.

I presume what the IT folks have done is create a PUSH subscription and the updates to the subscriber database are not replicated to the main database. I'm not sure how useful that will be if the Access database cannot make updates to the SQL Server data, but I don't know enough about your situation to know whether you will be able to do what you need with a replica database.

[kwvh]

Wendell,

Thanks for the response. The purpose of the replicated data is to facilitate reporting. IT doesn't allow Access to touch a production SQL Server database. So the plan is to use the existing Access tool to report off of the replicated data, which is read only.

So in the push scenario you described, should the Not for Replication be set to No in the Master database?

Thanks again for your response.

[Wendell]

Since the replica is simply a copy of the database, the "Not for Replication" should be set to No.

[kwvh]

Wendell,

Thanks! That confirms my understanding of Hans' explanation. The double negative confuses me. (Not for/no)

Thanks for sharing your knowledge.

Respectfully,

Ken