a different identiy columns in replication question

sql2k sp3
Ive got a little bit of a Replication background but never
with "immediate updating with queued updating for
failover" like Im testing now. In fact Ive never even done
just "immediate updating". I seen lots of horror stories
here about identity columns causing replication problems
for people and was expecting to get them during my testing
this week but I havent. Im curious as to why and thought
I'd ask. Heres what I've done in testing:
1; Made one big Publication of all my tables.
2; Did a Backup/ Restore to the Subscriber.
3; Took the actions as outlined in KB 320499.
4; Took the actions as outlined in KB 320773.
Everything is up and running at this point. Replication
runs fine in both directions. Identity columns on both the
Pub and Sub are in place. I am NOT using the "Yes(Not for
Replication)" option on either box nor have I modified the
ranges on either box. This is why I thought I'd have
problems. I thought Id need to place different ranges on
them and use the "Not for Replication" option on them. But
I didn't and am having no problems. Why? Not that Im
complaining. I even did a failover test by turinng off the
Publisher and switching to Queued Updating. I did inserts
while it was in that mode and still had no problems.
Again Im not upset by my success. But just dont get why
others have the problems Ive read about and I dont? There
is something about my settings that is correct I am
curious to find out what it is.
TIA, ChrisR
Chris,
the errors people have reported come from a variety of causes. Often it is
incorrect range management - either manually or on behalf of SQL Server.
Sometimes the problems have been in using the standby server when the
internal identity value of a column hasn't been updated. In some cases
upating the identity value is not possible even through DBCC CHECKIDENT.
In your scenario as I understand it, there is no allowance for the publisher
and subscriber being allocated the same identity value. This may not be a
problem for you as yet, but if someone on the subscriber attempts to insert
a record and network connectivity is temporarily down, it'll go into the
queue and when the queue reader starts, there could be conflicts. To avoid
this you can have SQL Server allocate an identity range for you or you can
manually create the range. The latter is quite straightforward if you have
realtively few subscribers. EG if you had one subscriber, the publisher
could have a seed of 1 and increment of 2 (odd nos), while the subscriber
has a seed of 2 and increment of 2 (even nos).
HTH,
Paul Ibison
|||This may not be a
>problem for you as yet, but if someone on the subscriber
attempts to insert
>a record and network connectivity is temporarily down,
it'll go into the
>queue and when the queue reader starts, there could be
conflicts.
This is the scenario I did in testing. Turned off the
Publisher, inserted into the Subscriber. When the Pub was
back up there was no issues. You are saying there could be
conflict as I beleive you. Do you know what the
circumstances are that would amke this happen?
Thanks

>--Original Message--
>Chris,
>the errors people have reported come from a variety of
causes. Often it is
>incorrect range management - either manually or on behalf
of SQL Server.
>Sometimes the problems have been in using the standby
server when the
>internal identity value of a column hasn't been updated.
In some cases
>upating the identity value is not possible even through
DBCC CHECKIDENT.
>In your scenario as I understand it, there is no
allowance for the publisher
>and subscriber being allocated the same identity value.
This may not be a
>problem for you as yet, but if someone on the subscriber
attempts to insert
>a record and network connectivity is temporarily down,
it'll go into the
>queue and when the queue reader starts, there could be
conflicts. To avoid
>this you can have SQL Server allocate an identity range
for you or you can
>manually create the range. The latter is quite
straightforward if you have
>realtively few subscribers. EG if you had one subscriber,
the publisher
>could have a seed of 1 and increment of 2 (odd nos),
while the subscriber
>has a seed of 2 and increment of 2 (even nos).
>HTH,
>Paul Ibison
>
>.
>
|||Chris,
to test this you can force failover, or more easily set up an alternative
test system with just a queue. Stop the queue reader agent and distribution
agent. Insert a record into the publisher and subscriber, and the 2 new
records will have the same identity value.
When starting the queue reader there will be a conflict registered which is
viewable i the conflict viewer.
This is not an error like in some of the other posts, but it is a problem of
lost data whch can be avoided by partitioning the identity range.
HTH,
Paul Ibison
|||With immediate updating you are guananteed not to have identity range
problems.
The reason is that any update that happens on the subscriber is first
applied on the publisher where the publisher's identity range rules.
The problem of course is your publisher/subscriber must be well connected
and the publisher must always be online. If so, updates on your subscriber
are rolled back. If the link between the publisher and subscribers goes
down, updates can still occur on the publisher.
With queued, when your publisher is offline, all updates happen on the
subcsriber, so again, no identity problems as no updates happen on the
publisher.
As queued is an asynchronous process when the publisher comes back on line
unless you revert back to immediate you can have identity range problems
unless you are using automatic identity range management.
Automatic Identity Range Management is basically trouble free. You run into
problems with it when you have a range of lets say 100, and a batch update
that updates 1000 rows (or really anything over the 100 range). The procs
which do the automatic range management don't have time to work during the
batch and you get the problem.
So pick a range which is large. Many dba's pick very large ranges which they
know will not be blown for the lifetime of their replication solution. This
option is called set it and forget it. It works very well.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:2ef4e01c46b7b$643c0120$a301280a@.phx.gbl...
> sql2k sp3
> Ive got a little bit of a Replication background but never
> with "immediate updating with queued updating for
> failover" like Im testing now. In fact Ive never even done
> just "immediate updating". I seen lots of horror stories
> here about identity columns causing replication problems
> for people and was expecting to get them during my testing
> this week but I havent. Im curious as to why and thought
> I'd ask. Heres what I've done in testing:
> 1; Made one big Publication of all my tables.
> 2; Did a Backup/ Restore to the Subscriber.
> 3; Took the actions as outlined in KB 320499.
> 4; Took the actions as outlined in KB 320773.
> Everything is up and running at this point. Replication
> runs fine in both directions. Identity columns on both the
> Pub and Sub are in place. I am NOT using the "Yes(Not for
> Replication)" option on either box nor have I modified the
> ranges on either box. This is why I thought I'd have
> problems. I thought Id need to place different ranges on
> them and use the "Not for Replication" option on them. But
> I didn't and am having no problems. Why? Not that Im
> complaining. I even did a failover test by turinng off the
> Publisher and switching to Queued Updating. I did inserts
> while it was in that mode and still had no problems.
> Again Im not upset by my success. But just dont get why
> others have the problems Ive read about and I dont? There
> is something about my settings that is correct I am
> curious to find out what it is.
> TIA, ChrisR
|||Thanks Hillary and Paul. I just realized from reading your responses that Im
not totally positive if this box will be used just for fail over if the
Publisher goes down or not. Im not sure, but I dont think the two of them
will ever be used at the same time and the Subscriber will be written to
only if the Pub is off line. If this is the case, I don't think I will have
the identity range problem will I? Come to think of it, should I switch the
whole plan over just to Queued Updating if this is the case? Would I benifit
from that in any way?
Thanks alot you guys for your help.
ChrisR
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:#4F46$$aEHA.3420@.TK2MSFTNGP12.phx.gbl...
> With immediate updating you are guananteed not to have identity range
> problems.
> The reason is that any update that happens on the subscriber is first
> applied on the publisher where the publisher's identity range rules.
> The problem of course is your publisher/subscriber must be well connected
> and the publisher must always be online. If so, updates on your subscriber
> are rolled back. If the link between the publisher and subscribers goes
> down, updates can still occur on the publisher.
> With queued, when your publisher is offline, all updates happen on the
> subcsriber, so again, no identity problems as no updates happen on the
> publisher.
> As queued is an asynchronous process when the publisher comes back on line
> unless you revert back to immediate you can have identity range problems
> unless you are using automatic identity range management.
> Automatic Identity Range Management is basically trouble free. You run
into
> problems with it when you have a range of lets say 100, and a batch update
> that updates 1000 rows (or really anything over the 100 range). The procs
> which do the automatic range management don't have time to work during the
> batch and you get the problem.
> So pick a range which is large. Many dba's pick very large ranges which
they
> know will not be blown for the lifetime of their replication solution.
This
> option is called set it and forget it. It works very well.
>
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:2ef4e01c46b7b$643c0120$a301280a@.phx.gbl...
>
|||queued and bi-directional transactional replication are options. If you
expect schema changes I would use queued as opposed to bi-directional
transactional,
Queued will add a guid column to all tables you are replicating however.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <chris@.noemail.com> wrote in message
news:OQ4yT8BbEHA.3524@.TK2MSFTNGP12.phx.gbl...
> Thanks Hillary and Paul. I just realized from reading your responses that
Im
> not totally positive if this box will be used just for fail over if the
> Publisher goes down or not. Im not sure, but I dont think the two of them
> will ever be used at the same time and the Subscriber will be written to
> only if the Pub is off line. If this is the case, I don't think I will
have
> the identity range problem will I? Come to think of it, should I switch
the
> whole plan over just to Queued Updating if this is the case? Would I
benifit[vbcol=seagreen]
> from that in any way?
> Thanks alot you guys for your help.
> ChrisR
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:#4F46$$aEHA.3420@.TK2MSFTNGP12.phx.gbl...
connected[vbcol=seagreen]
subscriber[vbcol=seagreen]
line[vbcol=seagreen]
> into
update[vbcol=seagreen]
procs[vbcol=seagreen]
the
> they
> This
>
|||Ive done a bit of schema changes on replicated tables in the past. How does
queued benifit the cause?
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:u3h6VFHbEHA.1656@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> queued and bi-directional transactional replication are options. If you
> expect schema changes I would use queued as opposed to bi-directional
> transactional,
> Queued will add a guid column to all tables you are replicating however.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "ChrisR" <chris@.noemail.com> wrote in message
> news:OQ4yT8BbEHA.3524@.TK2MSFTNGP12.phx.gbl...
that[vbcol=seagreen]
> Im
them[vbcol=seagreen]
> have
> the
> benifit
> connected
> subscriber
goes[vbcol=seagreen]
> line
problems[vbcol=seagreen]
> update
> procs
> the
which
>
|||with bi-directional transactional replication you have to drop both
publications, make changes on both sides and rebuild. You can't use
sp_repladdcolumn or sp_repldropcolumn when you are doing bi-directional
transactional replication.
You can use these stored procedures when you are using transactional
replication with queued updating subscribers.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <chris@.noemail.com> wrote in message
news:O$rxpQJbEHA.1732@.TK2MSFTNGP09.phx.gbl...
> Ive done a bit of schema changes on replicated tables in the past. How
does[vbcol=seagreen]
> queued benifit the cause?
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:u3h6VFHbEHA.1656@.TK2MSFTNGP09.phx.gbl...
> that
the[vbcol=seagreen]
> them
to[vbcol=seagreen]
switch[vbcol=seagreen]
range[vbcol=seagreen]
first[vbcol=seagreen]
> goes
the[vbcol=seagreen]
the[vbcol=seagreen]
on[vbcol=seagreen]
> problems
run[vbcol=seagreen]
during[vbcol=seagreen]
> which
solution.
>
|||Thanks Hilary. Ive used sp_repladdcolumn in the past in transactional repl
but it wasnt bi-directional. I assumed I could use it now as well. Good to
know. I'll find out all the requirements this weeks and will now be more
informed on which road to take. Thanks again.
CR
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:e$1Ix$LbEHA.2216@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> with bi-directional transactional replication you have to drop both
> publications, make changes on both sides and rebuild. You can't use
> sp_repladdcolumn or sp_repldropcolumn when you are doing bi-directional
> transactional replication.
> You can use these stored procedures when you are using transactional
> replication with queued updating subscribers.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "ChrisR" <chris@.noemail.com> wrote in message
> news:O$rxpQJbEHA.1732@.TK2MSFTNGP09.phx.gbl...
> does
you[vbcol=seagreen]
however.[vbcol=seagreen]
> the
written[vbcol=seagreen]
> to
will[vbcol=seagreen]
> switch
> range
> first
rules.[vbcol=seagreen]
> the
> the
> on
> run
batch[vbcol=seagreen]
The
> during
> solution.
>

A cursor performance related question

Hi All,
I have 800 K records need to be processed one by one in a large table daily
using cursor way to update the default columns. The rest of queries are only
read information from this table. The records in this table could be very
large later on.
My question is what's the best way to use cursor to duel with this table.
here is my options:
1.Use a BIG cursor to lock all the un-processed records and use a singal
connection from Query Analyzer
2. Try to break /subgroup them with flags and run the same procedure above
with muti-Query Analyzer Connections. Each connection only duel part of the
record set.
I only have one SQL server . The testing result is Option 1 has the best
performance.
Is SQL server not good at running query parallelly with muti-connections? or
I need to improve the SQL server hardware staff by adding more memory ?
Any expert can point me to the right way?
Many Thanks,
Stevenews.microsoft.com (stevenxiu@.yahoo.com) writes:
> I have 800 K records need to be processed one by one in a large table
> daily using cursor way to update the default columns. The rest of
> queries are only read information from this table. The records in this
> table could be very large later on.
> My question is what's the best way to use cursor to duel with this
> table. here is my options:
> 1.Use a BIG cursor to lock all the un-processed records and use a singal
> connection from Query Analyzer
> 2. Try to break /subgroup them with flags and run the same procedure
> above with muti-Query Analyzer Connections. Each connection only duel
> part of the record set.
> I only have one SQL server . The testing result is Option 1 has the best
> performance.
> Is SQL server not good at running query parallelly with
> muti-connections? or I need to improve the SQL server hardware staff by
> adding more memory ?
With only this abstract narrative it is impossible to say very much. One
possibility is that you have poor indexing, cause the multiple connections
to block each other.
To get any accurate response you would need to post:
o CREATE TABLE and CREATE INDEX statements for the table.
o The code for the two options you are using.
o Some background on what the code is actually doing.
However, there is fair chance that the answer is option 0: don't use a
cursor at all, but apply set-based logic. This usually improves performance
with magnitudes.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"news.microsoft.com" <stevenxiu@.yahoo.com> wrote in message
news:u9WHOCx8FHA.3044@.TK2MSFTNGP10.phx.gbl...
> I have 800 K records need to be processed one by one in a large table
> daily
> using cursor way to update the default columns.
Chances are that you don't need to do it "one by one" and that it will be
more efficient without a cursor. We won't know for sure unless you post a
better description of your problem. See:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
But based on a few decades with SQL, I have written only five cursors
and I know that I could have avoided three of them if I had the CASE
expression back in the old days.
The fact that you do not know the differences between records and rows
tells me your approach is probably not relational and that yoiur
mindset is still stuck in sequential file processing.|||You should avoid using cursors to perform updates. Set-based updates
perform better for a number of reasons:
(1) Set-based updates write to the transaction log more efficiently. Each
update incurs a certain amount of overhead in the transaction log to mark
the start and end of each write to each affected object in the database.
This includes not only writes to the heap or clustered index, but also
writes to each nonclustered index. In addition, if the same index page is
updated more than once during the thousands of individual updates, then that
page will be recorded in the transaction log once for each related update,
causing the log to grow faster than is necessary. Frequent disk allocations
can hugely affect performance. (This last shouldn't be a problem if you
pre-allocate log space.)
(2) With set-based updates, indexes can be updated en-mass--meaning fewer
costly page splits and fewer writes to each affected database object thus
reducing the frequency of disk head ss.
(3) Locking is more efficient. Set-based updates obtain locks on all of the
affected rows before beginning the write, and then release them as soon as
the changes have been committed. Thousands of individual updates requires
the server to go through the process of obtaining each individual exclusive
lock on each individual row. With set-based updates, locks are more likely
to be escalated when necessary, thereby reducing overhead.
(4) With set-based updates, the system is tasked with writing and writing
only. Thousands of individual updates usually means that several additional
reads are interspersed within the writes. This can cause a lot more disk
activity and in particular, a significant increase in costly disk ss.
If you must use a cursor (I'm not of the opinion that they are always bad;
however, they should only be used as a last resort.), then you should cache
the updates in a temp table or table variable and then flush them using
set-based updates. Inserting individual rows into a table with no indexes
(a heap) or appending to a table with only a clustered index performs pretty
well, and if you have enough memory, temp tables and table variables remain
for the most part in memory. (Writes to tempdb are eventually flushed out
to disk, provided the affected rows and objects still exist by the time the
system gets around to initiating the write.) By appending, I mean that
inserts occur in the same order as the clustered index key--that is, with an
ascending index, each row inserted has a key value that is greater than the
key value in any existing row in the table.
"news.microsoft.com" <stevenxiu@.yahoo.com> wrote in message
news:u9WHOCx8FHA.3044@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I have 800 K records need to be processed one by one in a large table
> daily
> using cursor way to update the default columns. The rest of queries are
> only
> read information from this table. The records in this table could be very
> large later on.
> My question is what's the best way to use cursor to duel with this table.
> here is my options:
> 1.Use a BIG cursor to lock all the un-processed records and use a singal
> connection from Query Analyzer
> 2. Try to break /subgroup them with flags and run the same procedure above
> with muti-Query Analyzer Connections. Each connection only duel part of
> the
> record set.
> I only have one SQL server . The testing result is Option 1 has the best
> performance.
> Is SQL server not good at running query parallelly with muti-connections?
> or
> I need to improve the SQL server hardware staff by adding more memory ?
> Any expert can point me to the right way?
> Many Thanks,
> Steve
>sql

A basic question: Removing duplicate results from Max function

Hi,

Say I have a table Job with columns name, date, salary . I want to get
the name ,date and salary for the date when that person earned maximum
salary. I am using something like

SELECT X.name,X.date,X.salary
FROM job X
WHERE X.salary IN
(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);

The problem is ; if a person earns maximum salary on two dates, both of
the dates are printed. I just want to get any one of those two rows.
I tried

SELECT X.name,Min(X.date),X.salary
FROM job X
WHERE X.salary IN
(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);

but it gives error.
Can anybody please suggest a solution?

Regards,
Aamir(aamircheema@.gmail.com) writes:
> Say I have a table Job with columns name, date, salary . I want to get
> the name ,date and salary for the date when that person earned maximum
> salary. I am using something like
>
> SELECT X.name,X.date,X.salary
> FROM job X
> WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
>
> The problem is ; if a person earns maximum salary on two dates, both of
> the dates are printed. I just want to get any one of those two rows.
> I tried
> SELECT X.name,Min(X.date),X.salary
> FROM job X
> WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
> but it gives error.

SELECT a.name, a.date, a.salary
FROM tbl a
JOIN (SELECT b.name, date = MAX(b.date)
FROM tbl b
JOIN (SELECT name, salary = MAX(salary)
FROM tbl
GROUP BY name) c ON c.name = b.name
AND c.salary = b.salary
GROUP BY a1.name) b ON a.name = b.name
AND a.date = b.date

This presumes that (name, date) is unique, and a person does not have
two salaries the same day.

The inner selects are derived tables - sort of virtual temp tables within
the query. A very powerful tool to write complex queries. A derived table
is independent of the outer query, and this why the alias b can be reused.
Note that they are not necessarily computed in whole - the optimizer often
recast computation order for a very very effceient query plan.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||aamircheema@.gmail.com wrote:
> Hi,
> Say I have a table Job with columns name, date, salary . I want to get
> the name ,date and salary for the date when that person earned maximum
> salary. I am using something like
>
> SELECT X.name,X.date,X.salary
> FROM job X
> WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
>
> The problem is ; if a person earns maximum salary on two dates, both of
> the dates are printed. I just want to get any one of those two rows.
> I tried
> SELECT X.name,Min(X.date),X.salary
> FROM job X
> WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
> but it gives error.
> Can anybody please suggest a solution?
> Regards,
> Aamir

In SQL Server 2005:

WITH j AS
(SELECT name, date, salary,
RANK() OVER (PARTITION BY name ORDER BY salary DESC, date DESC) rnk
FROM job)
SELECT name, date, salary
FROM j
WHERE rnk = 1 ;

That assumes the combination of (name,salary,date) is unique. If it
isn't then just add other columns to the ORDER BY specification to make
a key.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||On 10 Jun 2006 07:48:38 -0700, aamircheema@.gmail.com wrote:

(snip)
>I tried
>SELECT X.name,Min(X.date),X.salary
>FROM job X
>WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
>but it gives error.
>Can anybody please suggest a solution?

Hi Aamir,

Here's a third suggestion:

SELECT X.name,Min(X.date),X.salary
FROM job X
WHERE X.salary IN
(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name)
GROUP BY X.name, X.salary;

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP|||
Thanks Everybody. That helped a lot