|
Home > Archive > SQL Anywhere Mobile > November 2005 > MobiLink with MS SQL Server - IDENTITY column
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
MobiLink with MS SQL Server - IDENTITY column
|
|
|
| Hi,
I have a question on MobiLink synchronization with Microsoft
SQL Server.
My source table in Microsoft SQL server has an IDENTITY
column. How do I map this column to the remote database?
Does Mobilink handle
- conflicts due to primary keys for IDENTITY coumns?
Thanks,
Esak.
| |
| Greg Fenton 2005-11-01, 7:23 am |
| Esak wrote:
> My source table in Microsoft SQL server has an IDENTITY
> column. How do I map this column to the remote database?
> Does Mobilink handle conflicts due to primary keys for IDENTITY coumns?
>
Though there are a few ways to handle the duplicated keys, they are
often extremely complicated and error prone.
The best way is to design your distributed database in such a way that
you do not end up with duplicates. There are a few techniques to
accomplish this, all documented in the SQLAnywhere online docs (see
"Synchronization Techniques" in the MobiLink Administration Guide).
The technique we recommend primarily is to use GLOBAL AUTOINCREMENT in
your remote databases to generate keys unique to each remote and to
establish the global_database_id's
for each remote such that it will not
clash with the range of keys generated at the consolidated using the
IDENTITY function.
Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/
| |
|
| Hi Greg,
If there is an existing table schema in Microsoft SQL Server
with IDENTITY column, how does MobiLink work in a
synchronization setup?
I seem to feel that IDENTITY column gets reseeded and
applications working with Microsoft SQL server seem to fail
causing problems.
Do you have a workable scenario that can be tried with
MobiLink?
Thanks,
Esak.
> Esak wrote:
>
> Though there are a few ways to handle the duplicated keys,
> they are often extremely complicated and error prone.
>
> The best way is to design your distributed database in
> such a way that you do not end up with duplicates. There
> are a few techniques to accomplish this, all documented
> in the SQLAnywhere online docs (see "Synchronization
> Techniques" in the MobiLink Administration Guide).
>
> The technique we recommend primarily is to use GLOBAL
> AUTOINCREMENT in your remote databases to generate keys
> unique to each remote and to establish the
> global_database_id's
for each remote such that it will not
> clash with the range of keys generated at the consolidated
> using the IDENTITY function.
>
> Hope this helps,
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/
| |
| Breck Carter [TeamSybase] 2005-11-02, 7:23 am |
| Can you give the MSS column a very large seed so it effectively uses a
very high "partition" for inserts on the consolidated? Then uploaded
inserts of values in lower partitions won't disturb the current
identity value. DEFAULT GLOBAL AUTOINCREMENT is used on the remotes to
specify the partitions.
Breck
On 1 Nov 2005 14:11:25 -0700, Esak wrote:
[color=darkred]
>Hi Greg,
>
>If there is an existing table schema in Microsoft SQL Server
>with IDENTITY column, how does MobiLink work in a
>synchronization setup?
>
>I seem to feel that IDENTITY column gets reseeded and
>applications working with Microsoft SQL server seem to fail
>causing problems.
>
>Do you have a workable scenario that can be tried with
>MobiLink?
>
>Thanks,
>Esak.
>
>
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
|
| Hi,
Re:
>Can you give the MSS column a very large seed so it
> effectively uses a very high "partition" for inserts on
> the consolidated? Then uploaded inserts of values in lower
> partitions won't disturb the current identity value.
This will not solve my reported issue. Say, I give a large
seed value for the identity column on Microsoft SQL server
to 10,000,000 (10 million).
On the remote database, a value of say 1001 is inserted, and
sync'ed to the server. During sync, after 1001 is inserted
into MS SQL server, then the seed value of the identity is
reset. The next identity value for the application using MS
SQL server table will be 1002.
Why should an insert on the remote database sync'ed to the
server reseed the identity value on MS SQL server?
So, synchronizing thru MobiLink will cause a problem. If I
missed something, let me know.
> Can you give the MSS column a very large seed so it
> effectively uses a very high "partition" for inserts on
> the consolidated? Then uploaded inserts of values in lower
> partitions won't disturb the current identity value.
> DEFAULT GLOBAL AUTOINCREMENT is used on the remotes to
> specify the partitions.
>
> Breck
>
> On 1 Nov 2005 14:11:25 -0700, Esak wrote:
>
> Server >with IDENTITY column, how does MobiLink work in a
> fail >causing problems.
> IDENTITY >> > column. How do I map this column to the
> remote database? >> > Does Mobilink handle conflicts due
> to primary keys for >> > IDENTITY coumns?
> keys, >> they are often extremely complicated and error
> prone. >>
> There >> are a few techniques to accomplish this, all
> documented >> in the SQLAnywhere online docs (see
> "Synchronization >> Techniques" in the MobiLink
> Administration Guide). >>
> keys >> unique to each remote and to establish the
> not >> clash with the range of keys generated at the
> consolidated >> using the IDENTITY function.
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
>
http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
| |
| Graham Hurst 2005-11-03, 8:29 pm |
| > This will not solve my reported issue. Say, I give a large
> seed value for the identity column on Microsoft SQL server
> to 10,000,000 (10 million).
>
> On the remote database, a value of say 1001 is inserted, and
> sync'ed to the server. During sync, after 1001 is inserted
> into MS SQL server, then the seed value of the identity is
> reset. The next identity value for the application using MS
> SQL server table will be 1002.
That seems odd.
You might have more luck giving the MSS consolidated the range *below*
the remote ranges, i.e. use a seed of 0 for the consolidated and don't
assign a global_database_id of 0 for any of the remotes.
Cheers,
Graham
| |
| Breck Carter [TeamSybase] 2005-11-03, 8:29 pm |
| What happens if you actually insert a row in the high partition,
before allowing an uploaded row with a lower value? My understanding
is that you can "fill in gaps" with MSS identity columns without
disturbing the current "next value".
Breck
On 2 Nov 2005 13:48:39 -0700, Esak wrote:
[color=darkred]
>Hi,
>
>Re:
>
>This will not solve my reported issue. Say, I give a large
>seed value for the identity column on Microsoft SQL server
>to 10,000,000 (10 million).
>
>On the remote database, a value of say 1001 is inserted, and
>sync'ed to the server. During sync, after 1001 is inserted
>into MS SQL server, then the seed value of the identity is
>reset. The next identity value for the application using MS
>SQL server table will be 1002.
>
>Why should an insert on the remote database sync'ed to the
>server reseed the identity value on MS SQL server?
>
>So, synchronizing thru MobiLink will cause a problem. If I
>missed something, let me know.
>
>
>
>http://www.amazon.com/exec/obidos/A...7/risingroad-20
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
| Breck Carter [TeamSybase] 2005-11-03, 8:29 pm |
| If you actually *insert* a value using the high seed, explicitly
uploading lower values will not reset the current identity value.
Here is the final result of a test, where row 999999000000001 was
inserted in an MSS consolidated database, then row 1000000001 was
inserted in an ASA remote database and uploaded, and then
999999000000002 was inserted in the consolidated. The uploaded row did
not affect the next value used on the consolidated.
1> select key_1 from t1
2> go
key_1
-----------------
1000000001
999999000000001
999999000000002
Syntactic details available on request :)
Breck
On 2 Nov 2005 13:48:39 -0700, Esak wrote:
[color=darkred]
>Hi,
>
>Re:
>
>This will not solve my reported issue. Say, I give a large
>seed value for the identity column on Microsoft SQL server
>to 10,000,000 (10 million).
>
>On the remote database, a value of say 1001 is inserted, and
>sync'ed to the server. During sync, after 1001 is inserted
>into MS SQL server, then the seed value of the identity is
>reset. The next identity value for the application using MS
>SQL server table will be 1002.
>
>Why should an insert on the remote database sync'ed to the
>server reseed the identity value on MS SQL server?
>
>So, synchronizing thru MobiLink will cause a problem. If I
>missed something, let me know.
>
>
>
>http://www.amazon.com/exec/obidos/A...7/risingroad-20
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
| Breck Carter [TeamSybase] 2005-11-03, 8:29 pm |
| As far as I can tell, you have use SET IDENTITY_INSERT ON/OFF in the
upload_insert script to get it to work at all... otherwise the
uploaded row is rejected if it attempts to insert an explicit value
into an IDENTITY column.
Then, if the uploaded value is higher than the current identity value
on the MSS consolidated, it will reset that value... at least,
according to the MSS 2000 Help...
=====
SET IDENTITY_INSERT
Allows explicit values to be inserted into the identity column of a
table.
....
If the value inserted is larger than the current identity value for
the table, SQL Server automatically uses the new inserted value as the
current identity value.
=====
Hence the suggestion to use a giant seed in the IDENTITY clause, and
use lower partitions for ASA databases. This is opposite to the
practice commonly used with Oracle.
(sigh... autoincrement is such a straightforward concept, why are
there so many hosed-up implementations? :)
Breck
On 2 Nov 2005 14:08:02 -0700, Graham Hurst
< spam_guard_hurst@ian
ywhere.com> wrote:
>
>That seems odd.
>
>You might have more luck giving the MSS consolidated the range *below*
>the remote ranges, i.e. use a seed of 0 for the consolidated and don't
>assign a global_database_id of 0 for any of the remotes.
>
>Cheers,
>
>Graham
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
| David Fishburn 2005-11-03, 8:29 pm |
| "Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
news:63him1pbgr351mn
u6ek0fj6jjsfm0019nk@
4ax.com of
sybase.public.sqlanywhere.mobilink:
BC> What happens if you actually insert a row in the high partition,
BC> before allowing an uploaded row with a lower value? My understanding
BC> is that you can "fill in gaps" with MSS identity columns without
BC> disturbing the current "next value".
The way I have done this is in the past is:
1. Setup a GLOBAL AUTOINCREMENT value for the remotes and choose a
partition size (lets say 1000).
1. Figure out how many remotes, plus resets I would need. Lets say it
will be 10,000.
1. See the SQL Server to 10,000 * 1000 so the next insert IN SQL SERVER
will be at the appropriate range.
1. At the remote, use the global_database_id to assign a range to the
remote that will generate a value BELOW what we just seeded SQL
Server to.
1. In your ML scripts, use the SET IDENTITY_INSERT ON/OFF before and
after the upload events for this table.
Now any new insert in SQL Server will use the next highest value which
will always be > 10,000 * 1000. The remotes will fill in the holes as
expected.
--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).
EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm
Developer Community / Whitepapers
http://www.ianywhere.com/developer
CaseXpress - to report bugs
http://casexpress.sybase.com
CodeXchange - Free samples
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]
|
|
|
|
|