I/O Queue Length

Running transactional replication, dedicated server for distributor. While performance in terms of latency is excellent (usually 1 sec, almost never higher than 4) the disk queue length on the distributor is extremely high (over 6 usually). Is this typical? On any other server I would be very concerned, but cpu and memory usage are excellent and as said, latency is good. what is recommended config for distributor? others see high queue length?

Possibly. How many disks are part of the array which your distribution database is on?

You have to multiply the queue by the number of spindles which form your array. So if you have 5 disks in your raid 5 array anything beyond a disk queue length of 10 is a danger area.

Currently on one of my main distributors my queue length is pushing 80 - raid 5 (5 disks).|||

Thanks Hillary. ha ha i just found something out (just inherited this by the way) ... straight mirrored sets. no raid config. only thing on server is system dbs, but still...wow. at least logs are on seperate physical set.

i wanna do Replication

Hi,
I am very new to replication . Can anybody give some initial steps to do it.
regards Ar
There are very good descriptions in Book On Line under replication. You can
look it up on MSDN if BOL is not installed
--
Mary Bray [SQL Server MVP]
Please only reply to newsgroups
"AR" <aneesh.r@.eostek.com> wrote in message
news:%23TyjB$B3EHA.4028@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am very new to replication . Can anybody give some initial steps to do
> it.
> regards Ar
>
|||On 2004-12-07, Mary Bray <no@.spam.com> wrote:
> There are very good descriptions in Book On Line under replication. You can
> look it up on MSDN if BOL is not installed
Where is in BOL explained what tables to put in publication? The design
philosophy of replication. Why to have several instead of one publication,
etc, etc, etc.
I find BOL extremley useful when you know what you're looking for. But I
dont' think that MSSQL concepts are well explained.
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
|||This is a good observation on BOL. Its like a dictionary, great as a
reference, but not very good when you are tying to learn a language.
In general you put all the tables you want to replicate in a single
publication. However, you may want to seperate the tables into their own
publications for administrative, logistical, performance or deployment
reasons.
All tables related by pk fk relationships should be in the same publication.
Large tables could be in seperate publications as if the snapshot fails you
only have to resnapshot and deploy the large table, rather than starting
from scratch again. If you group your tables by DRI, use the independent
agent option, you could have multiple publications deployed to a single
subscriber with multiple distribution agents which will lead to performance
increases.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Mario Splivalo" <majk@.fly.srk.fer.hr> wrote in message
news:slrncrbe33.360.majk@.fly.srk.fer.hr...
> On 2004-12-07, Mary Bray <no@.spam.com> wrote:
> Where is in BOL explained what tables to put in publication? The design
> philosophy of replication. Why to have several instead of one publication,
> etc, etc, etc.
> I find BOL extremley useful when you know what you're looking for. But I
> dont' think that MSSQL concepts are well explained.
> Mike
> --
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
> Mario Splivalo
> msplival@.jagor.srce.hr
|||On 2004-12-07, Hilary Cotter <hilary.cotter@.gmail.com> wrote:
> This is a good observation on BOL. Its like a dictionary, great as a
> reference, but not very good when you are tying to learn a language.
> In general you put all the tables you want to replicate in a single
> publication. However, you may want to seperate the tables into their own
> publications for administrative, logistical, performance or deployment
> reasons.
> All tables related by pk fk relationships should be in the same publication.
> Large tables could be in seperate publications as if the snapshot fails you
> only have to resnapshot and deploy the large table, rather than starting
> from scratch again. If you group your tables by DRI, use the independent
> agent option, you could have multiple publications deployed to a single
> subscriber with multiple distribution agents which will lead to performance
> increases.
>
But, how do I make initial snapshot to create foreign key constraints to
tables that aren't in the publication?
For instance, i have these 'key' tables:
l_tax_rates
l_orgs
l_stocks
l_curr_rates
l_banks
l_accounts
...
All of those are rarely changed. Updates are almost never to be happened,
inserts maybe once a year. I put all of those in snapshot replication, and I
have as many subscriptions to that publication as much as I have
subscribers.
Then, I have other tables that are related to above mentioned tables.
Invoices, stock documents, financial stuff of various names I'm unable to
say even in croatian.
When I put those tables in publication, initial snapshot won't create
foreign key constraints to the tables in snapshot publication. I'm not sure
how to avoid this. I'm setting the snapshot options of a merge publication
so that the snapshot leaves the tables on subscribers as they are. So, I
need to take care that tables all have rowguids, that all have constraints
(with NOT FOR REPLICATION option, of course), and that they're empty. Only
aster that I can push the initial snapshot and enable merge replication.
Or is there a better way?
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
|||If the tables are in the subscriber independantly but not in the publication
and you desparately want the FK relationships set up, you can create a
script that runs post snapshot to do the FK creation.
Have a look at "Executing Scripts Before and After the Snapshot is Applied"
in Books-On-Line!!
--
Mary Bray [SQL Server MVP]
Please only reply to newsgroups
"Mario Splivalo" <majk@.fly.srk.fer.hr> wrote in message
news:slrncrbp3t.360.majk@.fly.srk.fer.hr...
> On 2004-12-07, Hilary Cotter <hilary.cotter@.gmail.com> wrote:
> But, how do I make initial snapshot to create foreign key constraints to
> tables that aren't in the publication?
> For instance, i have these 'key' tables:
> l_tax_rates
> l_orgs
> l_stocks
> l_curr_rates
> l_banks
> l_accounts
> ...
> All of those are rarely changed. Updates are almost never to be happened,
> inserts maybe once a year. I put all of those in snapshot replication, and
> I
> have as many subscriptions to that publication as much as I have
> subscribers.
> Then, I have other tables that are related to above mentioned tables.
> Invoices, stock documents, financial stuff of various names I'm unable to
> say even in croatian.
> When I put those tables in publication, initial snapshot won't create
> foreign key constraints to the tables in snapshot publication. I'm not
> sure
> how to avoid this. I'm setting the snapshot options of a merge publication
> so that the snapshot leaves the tables on subscribers as they are. So, I
> need to take care that tables all have rowguids, that all have constraints
> (with NOT FOR REPLICATION option, of course), and that they're empty. Only
> aster that I can push the initial snapshot and enable merge replication.
> Or is there a better way?
> Mike
>
> --
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
> Mario Splivalo
> msplival@.jagor.srce.hr
|||On 2004-12-08, Mary Bray <no@.spam.com> wrote:
> If the tables are in the subscriber independantly but not in the publication
> and you desparately want the FK relationships set up, you can create a
> script that runs post snapshot to do the FK creation.
> Have a look at "Executing Scripts Before and After the Snapshot is Applied"
> in Books-On-Line!!
Where do I enter the srcipt I want? In create publicatino wizzard there is
no option for doing so.
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr

i need to cancel conflict when replication

Dear Sir,
i need to make merge application.
the problem is ,i need to make the replication every 3 hours,if there are a
conflict it will deletes the conflicting rows for the tables
that have primary keys.
i need each row for the same table at publisher and subscriber will be
replicated to each other,
but what happen is one of them deleted !!!!!
I can not use identity ranges to solve this problem that because of the
business needs of the software which i implement.
Thanks alot
Tamer
From what you describe it sounds like you want the Susbcriber to "win" primary key conflicts.
To do this you would need to click on the articles properties (the three dots to the right of your table name) in the specify articles dialog when creating your merge publication, and then in the resolver section, select the Use this custom resolver secti
on and select "Microsoft SQL Server subscriber always wins conflict resolver.".
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Tamer Nagi" wrote:

> Dear Sir,
> i need to make merge application.
> the problem is ,i need to make the replication every 3 hours,if there are a
> conflict it will deletes the conflicting rows for the tables
> that have primary keys.
> i need each row for the same table at publisher and subscriber will be
> replicated to each other,
> but what happen is one of them deleted !!!!!
> I can not use identity ranges to solve this problem that because of the
> business needs of the software which i implement.
> Thanks alot
> Tamer
>
>
|||Thanks for your reply.
But what i need exactly is,
the rows of publisher inserted at publisher and subscriber AND
the rows of subscriber inserted at publisher and subscriber
without delete any rows if conflict happening because of the existance of
the primary key in the table.
Example:
if i have Table name= Department
and i insert department="support" at the publisher with primary key=1 and
department="marketing" with primary key=1 at the subscriber
i need the two department inserted at the publisher and the subscriber with
department="support" has primary key=1 and department="marketing" has
primary key=2
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:CD5D0265-6440-4695-8BA1-769E87B1BD00@.microsoft.com...
> From what you describe it sounds like you want the Susbcriber to "win"
primary key conflicts.
> To do this you would need to click on the articles properties (the three
dots to the right of your table name) in the specify articles dialog when
creating your merge publication, and then in the resolver section, select
the Use this custom resolver section and select "Microsoft SQL Server
subscriber always wins conflict resolver.".[vbcol=seagreen]
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "Tamer Nagi" wrote:
are a[vbcol=seagreen]
|||Tamer,
If your subscriber insert needs to hold, but the PK column has to be altered
then you'll have to avoid the conflict as this precludes bith inserts
remaining. As you mention in your first post, this is normally done by
partitioning the data, eg using identity ranges. I would use this
methodology, and have another column which has an item counter number for
use in your business application.
hth,
Paul Ibison
|||Tamer,
Probably you need to implement your own custom conflict resolver.
Raj Moloye
|||I think the suggestion of separate PK seed and increment values is the best
way to go. A popular example of this is to start the Publisher with a seed
of 1, an increment of 2. Then start the Subscriber with a seed of 2 and an
increment of 2. This makes the odds on the Publisher, and the evens on teh
subscriber. Then everything replicates both ways just fine. New records
from both sides replicate without any conflicts. Hope this helps.
"Raj Moloye" wrote:

> Tamer,
> Probably you need to implement your own custom conflict resolver.
> Raj Moloye
>
>

I need help with Transactional Replication

I am just beginning with transactional replication, and I have a publisher
with a primary key on the identity column (UID), some data, then an adddate
(varchar 8) column iwth ccyymmdd data in it. I want to replicate this table
to a server based on the adddate value (all records pushed since a given
adddate). My replication attempts seem to only want to work off of the
primary key column. How do I get it to work off of the adddate column, or do
I even really want to?
Thank you for any help in advance
The publisher (and subscriber) tables look like this:
create table temp (
uid bigint,
data_1 varchar(20),
adddate varchar(8)
)
Carl,
TR will only replicate the changes to this table since the last
synchronization. If new records have been added, they'll automatically be
picked up. With this in mind you can probably forget about filtering the
dddate column, unless I am misunderstanding you.
rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||ok, perhaps I have a different problem, then. When I update the publisher by
inserting numerous new rows, I manually run the distributer and it claims
that there are no rows to replicate. How can I get the distributor to see the
new rows?
"Paul Ibison" wrote:

> Carl,
> TR will only replicate the changes to this table since the last
> synchronization. If new records have been added, they'll automatically be
> picked up. With this in mind you can probably forget about filtering the
> dddate column, unless I am misunderstanding you.
> rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Carl,
are you using a filter on the publication?
Is the log reader agent running?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||There are no filters being used, and the log reader agent shows no errors.
"Paul Ibison" wrote:

> Carl,
> are you using a filter on the publication?
> Is the log reader agent running?
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Carl,
make a change to a row then run sp_browsereplcmds in the
distribution database to see if the rows are reaching
there.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

I need away to show the pending transaction from transaction replication in a user friendly form

I want to list out the pending transaction for transaction replication by publication.

Help needed.

Are you looking for the pending trasactions in the transaction log or on the distributor? If it's the later, you can use sp_browsereplcmds to retrieve pending transactions.

http://msdn2.microsoft.com/en-us/library/ms176109.aspx

Thanks,

Dennis