Information for Column-Level Merge Replication

Please provide me a column-level merge replication in SQL Server 2005. In the SQL Sever books online, I could able to get very few informaiton about this topic. Please provide me a best practices for using this kind of conflict resolution in Merge-replication.

Provide me a best solution for this problem

Following are my tables structure

Organization (top-level) AddressID -> ID Address

Employee (top-level) AddressID -> ID Address

Organization and employee in this scenario are isolated from each other, in terms of transactions. However, they contain linked data which lives in an Address table. In order to configure logical records in this example, the Address table would be the top-level, and the organization and employee tables would include join filters referencing it. The problem is, Organization and Employee are not really part of a logical record with Address, at least in terms of all three tables. Instead, Address and Organization participate in a logical record. Address and Employee also should be represented with a logical record.

what would be the suggested implementation approach for managing this type of relationship? I don't want to risk situations where conflicts in the employee table disallow updates to the organization table.You can try to get logical record scenario in this case.
I havent tried it myself, will do so shortly. Meanwhile go ahead and give it a try.
Make Address table the base table. Have Organization and Employee Tables join with the Address table in a Logical Record format.

Will post more after I try it out.|||Thanks for your reply. We will try to do that. Please kindly provide the solution if you achieved at your end.|||This cannot be configured with Logical records in the fashion that you desire. The address table has to be the top-level and you are bound to get conflicts in the entire logical record including organization if there was an error in employee table.
You can have logical record on one (address-employee) or the other (address-organization) but not both and address has to be the defined as the top article due to potential conflicts. This is a restriction in logical records.

Info on data replication on sql server 2000.

Hi,
I'm in need of detailed information on how data replication works on the
sql server 2000. If someone knows about a book or articles that
describes how data replication works with transactions, stored
procedures and/or merging databases I would like to be pointed in the
right direction.

/Zero_Addiction.Hi

This is a new release from MVP, Hilary Cotter, and I haven't had a chance to
read it myself yet, but it does says there are 45 pages of information on
internals!

http://www.nwsu.com/0974973602.html

John
"zero_addiction" <j-e@.mail.com> wrote in message
news:cmlng6$6mh$1@.news.cybercity.dk...
> Hi,
> I'm in need of detailed information on how data replication works on the
> sql server 2000. If someone knows about a book or articles that describes
> how data replication works with transactions, stored procedures and/or
> merging databases I would like to be pointed in the right direction.
> /Zero_Addiction.

indices and replication

Will leaving these indexes there cause any
>problems?
No.
I
>could add indexes to the publisher via the normal EM gui
method and just
>re-apply the new snapshot to get this new index to
subscribers... Correct?
Correct

>3) using sp_addscriptexec can be used to add indexes to
a published article
>*without* having to re-apply a new snapshot to
subscribers. Just like
>sp_replAddColumn adds columns.. Correct?
Dont know.
2 outta 3 aint bad. ;-)

>--Original Message--
>first: forgive me, is it indices or indexes?
>I had previously asked about adding indexes to tables
that are a part of
>merge replication (sql 2000 servers). Paul Ibison
reccomended checking out
>sp_addscriptexec. I honestly have not done that yet as I
was just doing a
>little initial digging on the subject at that point. I
have a couple other
>questions surrounding this issue:
>1) I had someone add some indexes to replicated tables on
the subcriber end
>of a merge replication scheme by using the usual
Enterprise Manager GUI
>method. They did not replicate to the publisher. I assume
that is normal. I
>actually did not think EM would have let the change be
done since it was
>done on a replication article. Will leaving these indexes
there cause any
>problems? I don't actually need them on the publisher end
right now anyway.
>2) I see that by default indexes are included when a new
snapshot is applied
>to a subscriber. So, if re-applying a new snapshot is a
doable solution, I
>could add indexes to the publisher via the normal EM gui
method and just
>re-apply the new snapshot to get this new index to
subscribers... Correct?
>3) using sp_addscriptexec can be used to add indexes to
a published article
>*without* having to re-apply a new snapshot to
subscribers. Just like
>sp_replAddColumn adds columns.. Correct?
>any info is greatly appreciated. Thanks!
>
>.
>
2 out of 3 ain't bad at all... Thanks! ;)
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:1b6801c4a19e$1ef9d250$a401280a@.phx.gbl...[vbcol=seagreen]
> Will leaving these indexes there cause any
> No.
> I
> method and just
> subscribers... Correct?
> Correct
>
> a published article
> subscribers. Just like
>
> Dont know.
> 2 outta 3 aint bad. ;-)
>
> that are a part of
> reccomended checking out
> was just doing a
> have a couple other
> the subcriber end
> Enterprise Manager GUI
> that is normal. I
> done since it was
> there cause any
> right now anyway.
> snapshot is applied
> doable solution, I
> method and just
> subscribers... Correct?
> a published article
> subscribers. Just like
|||I agree with Chris and (3) yes you can use sp_addscriptexec to send over the
create index statements - or use EM, or scripts etc.
The reason sp_addscriptexec is particularly useful is if you have a lot of
subscribers or if your subscribers are pull subscriptions - you can be sure
everyone gets the change. Be sure to try the script on the publisher first.
A recent poster found that his distribution agent failed due to an incorrect
script. If this happens, then you'll need to edit the script in the repldata
share.
BTW for (2) This is usually a lot less work than recreating a snapshot and
synchronizing.
Rgds,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uYFfuGaoEHA.2864@.TK2MSFTNGP12.phx.gbl...
> I agree with Chris and (3) yes you can use sp_addscriptexec to send over
the
> create index statements - or use EM, or scripts etc.
> The reason sp_addscriptexec is particularly useful is if you have a lot of
> subscribers or if your subscribers are pull subscriptions - you can be
sure
> everyone gets the change. Be sure to try the script on the publisher
first.
> A recent poster found that his distribution agent failed due to an
incorrect
> script. If this happens, then you'll need to edit the script in the
repldata
> share.
> BTW for (2) This is usually a lot less work than recreating a snapshot and
> synchronizing.
> Rgds,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Indicating the end of replication

Hi,
Is there a way one can tell when the Merge Replication between the
publisher and subscriber is done completely? Because, when replicating
large tables, it does take considerable amount of time, and I want to
somehow ensure that the replication is completed before disconnecting
the subscriber.
I appreciate any help in this regard!
Thanks!
Jane.
Jane,
if you're using the the activeX controls, they have a status event.
You could use the On Success action of the Run agent step of the merge job.
Alternatively you could check the latest record for this agent in
MSmerge_history (if you're not in continuous mode, you should get the
message 'No data needed to be merged.' at some time).
HTH,
Paul Ibison

indexing/backup issue with large table - transactional replication

Hello,
I have a table on a database that contains over 6 million rows. There
is one clusted and two non-clustered indexes on the table.
Counter decimal(18, 0) IDENTITY (1, 1) NOT NULL,
Machine varchar (60) NULL,
LogEntry varchar (1000)NULL,
Active varchar (50) NULL,
SysInfo varchar (255),
Idle varchar (50) NULL,
IP varchar (15) NULL,
KioskDate datetime NULL,
KioskTime varchar (22) NULL,
ServerDate datetime NULL,
ServerTime datetime NULL,
Application varchar (15)NULL,
WebDomain varchar (50)NULL ,
NSCode varchar (10) NULL
pk_1 Clustered on Counter
pk_2 NC on NSCode
pk_3 Machine, KioskDate, KioskTime, NSCode
These indexes work well.
A trans log backup runs nightly apart from sunday morning when a full
backup runs. The table is also part of a transactional replication
subscription (along with two other tables). The problem occurs when
replication fails as the database is being fully backed up at the
weekend. The replication also fails when I try to run DBCC REINDEX on
the table. I have tried to remove the table from the subscription
then run the DBCC command, however this also doesn't work. I have
used DBCC INDEXDEFRAG (successfully) however would my indexes still be
effective as using DBCCREINDEX?
The solution is to ensure the table is backed up, the indexes do not
become ineffective and replication continues to work.
All ideas greatly appreciated!
Thanks
ScottAs far as the INDEXDEFRAG, it should *eventually* be as
effective as a full reindex. Because it essentially works
with smaller pieces of the index, it takes much longer to
run.
What I would highly recommend is using filegroups and
splitting off the non-clustered indexes onto a separate
disk array. That should speed up the performance of a
full reindex. I'd also reindex each index as a separate
step, rather than all of the indexes in one job.
I'd also recommend splitting the full backup into backing
up individual files or filegroups more frequently rather
than a single full backup.
Implementing something like SQL Litespeed can drastically
increase the speed at which a backup finishes.
It sounds like you just don't have a large enough
maintenance window (time). In the end, you have two
options -- decrease the maintenance or increase the time
window. Consider the latter as a possibility.
Hope that helps.
>--Original Message--
>Hello,
>
>I have a table on a database that contains over 6 million
rows. There
>is one clusted and two non-clustered indexes on the table.
>Counter decimal(18, 0) IDENTITY (1, 1) NOT NULL,
>Machine varchar (60) NULL,
>LogEntry varchar (1000)NULL,
>Active varchar (50) NULL,
>SysInfo varchar (255),
>Idle varchar (50) NULL,
>IP varchar (15) NULL,
>KioskDate datetime NULL,
>KioskTime varchar (22) NULL,
>ServerDate datetime NULL,
>ServerTime datetime NULL,
>Application varchar (15)NULL,
>WebDomain varchar (50)NULL ,
>NSCode varchar (10) NULL
>
>pk_1 Clustered on Counter
>pk_2 NC on NSCode
>pk_3 Machine, KioskDate, KioskTime, NSCode
>These indexes work well.
>A trans log backup runs nightly apart from sunday morning
when a full
>backup runs. The table is also part of a transactional
replication
>subscription (along with two other tables). The problem
occurs when
>replication fails as the database is being fully backed
up at the
>weekend. The replication also fails when I try to run
DBCC REINDEX on
>the table. I have tried to remove the table from the
subscription
>then run the DBCC command, however this also doesn't
work. I have
>used DBCC INDEXDEFRAG (successfully) however would my
indexes still be
>effective as using DBCCREINDEX?
>The solution is to ensure the table is backed up, the
indexes do not
>become ineffective and replication continues to work.
>All ideas greatly appreciated!
>
>Thanks
>
>Scott
>.
>

Indexing on large table kills Transactional Replication

Hi,
SQL Server 2K Enterprise Ed, running Transactional replication, four
articles being replicated, 1 article containing over 15 million rows.
That table has 4 indexes on it. I created a job that executes a DBCC
INDEXDEFRAG statement against each of the indecies, I run the job at
the weekend, the job never errors, however it kills the Trans
replication.
What am I doing wrong? I used INDEXDEFRAG because of its online
capabilities, but the replication still fails.
Cheers
Scott
Are you having a problem with the Log Reader agent?
If so the problem is that Index Defragging is a logged operation and your
Tlog will balloon. This puts stress on your log reader agent and you will
see that it will experience time outs. The best way to fix this is to change
your Log Reader Agent's PollingInterval - set it to 1, and change the
ReadBatchSize - probably to 50 when you are doing the defragging. When you
not, use the defaults. Using profiles is an excellend way to do 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
<quackhandle1975@.yahoo.co.uk> wrote in message
news:1108472684.342558.177210@.z14g2000cwz.googlegr oups.com...
> Hi,
> SQL Server 2K Enterprise Ed, running Transactional replication, four
> articles being replicated, 1 article containing over 15 million rows.
> That table has 4 indexes on it. I created a job that executes a DBCC
> INDEXDEFRAG statement against each of the indecies, I run the job at
> the weekend, the job never errors, however it kills the Trans
> replication.
> What am I doing wrong? I used INDEXDEFRAG because of its online
> capabilities, but the replication still fails.
> Cheers
> Scott
>

Indexes stop working

We have about 10 sites using SQL Server 2000 SP1 or 3 or 3a, across two servers. The publisher/distributor performs transactional replication on three databases to the 'secondary' server.

Nearly all of the sites, at one time or another have experienced the indices on one of the tables simply stop working. Its always the same indices on a table that can have between 70,000 and a 300, 000 rows.

Before I start posting schema or index details anyone come across this before?Only once. Actually, now that I think about it, not even once. Do you have Auto-Stats set on for these databases? Or does UPDATE STATISTICS tablename resolve the problem?|||Yeah, that's happened to me a couple of times. Well two or three times, anyway. Or once. Maybe once. No, not even once. What the heck do you mean "stopped working", anyway? Are they not showing up in the mornings, or are your indexes just sitting in their cubes staring at the screensaver?|||auto update statistics was on so switched this off and ran update statistics again manually to no avail. The table in question is as follows:

CREATE TABLE [dbo].[rawdata] (
[evtime] [datetime] NOT NULL ,
[type] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[srcid] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[rawinfo] [varchar] (80) COLLATE Latin1_General_CI_AS NULL ,
[msrepl_tran_version] [uniqueidentifier] NOT NULL ,
[seqno_yr] [int] NOT NULL ,
[seqno] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE INDEX [IX_rawdata] ON [dbo].[rawdata]([type], [evtime]) ON [PRIMARY]
GO
CREATE INDEX [IX_rawdata_1] ON [dbo].[rawdata]([srcid], [evtime]) ON [PRIMARY]
GO
CREATE INDEX [IX_rawdata_2] ON [dbo].[rawdata]([evtime]) ON [PRIMARY]
GO

The problem, when it starts happening, means the following query stops returning data. Using different criteria by matching exactly rather than on a range in evtime works proving the data is there.

select * from rawdata where evtime between '2004-11-01 00:00:00.000' and '2004-11-02 00:00:00.000'

I am not sure whether transactional replication has anything to do with the cause. Because this data is only needed occasionally, we can get away with dropping the subscription, dropping the publication then dropping and recreating the indexes. We then publish and create a subscription to the data. It lasts for about 3 or 4 days then it goes again.

There are no maintenance plans set for this database which is possibly the problem. Running DBCC SHOWCONTIG never completes - it just sits there.

Help!|||You will want to leave Auto-stats on.

How many rows in the table? And how long has DBCC Showcontig gone for, before you give up on it? Have you run DBCC CHECKTABLE on this table?

Indexes being replicated

I have set up transactional replication between two SQL Server 2000 boxes. I
am replicating a table. On the publisher this table has a primary key, a
clustered index, number of non clustered indexes and a check constraint.
I want to replicate only the table structure and data, but none of the other
objects associated with the table.
When I use Enterprise Manager to set the snapshot options I want to uncheck
"Include declared referential integrity", "Clustered Indexes" and
"Nonclustered indexes" in the "Copy objects to destination" section .
However, the "Nonclustered indexes" checkbox is greyed out and ticked. If I
check "Include declared referential integrity" then I can uncheck
"Nonclustered indexes". So far so good, but then when I uncheck "Include
declared referential integrity" the "Nonclustered indexes" box gets
autmoatically checked and greyed out.
Why is it not possible to uncheck both "Include declared referential
integrity" and "Nonclustered indexes"?
Is there something I'm misunderstanding here?
Thanks
Stephen
Stephen,
only a few of the possible permutations are covered in this dialog box. For
a more fine control, you can set the @.schema_option argument of
sp_addarticle.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

indexes and merge replication

just putting out a feeler on this since I think I am about to be asked to do
this.
Can I add/remove indexes on replicated tables? If so, will that schema
change replicate to subscribers (sql server 2000 subscribers)?
I have used the sp_repladdcolumn stored procedure. Hopefully there is one
for indexes as well?
any info is appreciated. Thanks.
Thanks Paul. I'll checkout sp_addscriptexec in sql server books online.
I appreciate the direction.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1d3f01c49a59$3a4fed60$a501280a@.phx.gbl...
> DJC,
> you can use sp_addscriptexec to replicate index changes.
> HTH,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>