SQL Anywhere Mobile - What do I need to buy?

Author

What do I need to buy?

Jack T.

2005-08-07, 8:23 pm

I've been working with SQL Anywhere since it was owned by Watcom. However,
I've never done any kind or replication with it. It seems like Mobilink is
the best fit, but I'm open to suggestions. I have a 10 user copy of SQL
Anywhere 8 Studio, and a developer version of 9 that I develop with.

Situation:
- There is a main office that has an internet connection. There will be
normal app to DB connections there.
- The Building Supervisors will be working disconnected and connected off
and on as they drive around and review their building projects.
- I need two-way replication of all tables.
- All databases can be SQL Anywhere databases. Perhaps this can simplify
things.
- I have a server in the DMZ that I field applications for vendors to use.
I could put the Mobilink server there as and if it helps the SQL Anywhere
consolidated database as well.

Questions:
- What do I have to buy to implement this? (Don't send me to iAnywhere. I
tried twice to get a straight answer.)
- What do I setup where.
- Do I have enough to test it already?
- What do I setup where?
- I don't understand ASA's licensing. It goes by licenses or CPUs. Hw do
these apply in this situation?
- I don't want to work on it after it's setup. I want it to be as
maintenance free as the database and not have it be a tinker's dream.
- I don't want to write and debug a bunch of code to make it work. I'm
hoping that the product is designed well enough to where it doesn't require
that. The project is not started yet, and I can make whatever modifications
are necessary to the tables such as two part PKs that include the
autoincrement row plus location, timestamps, etc.

Any advice appreciated

Thanks,
Jack T.

Jack T.

2005-08-07, 8:23 pm

PS: The Building Supervisors are using laptops
PS: I need column level replication so that if one person changes one
column, and one person another in the same row, there is not loss of
information.

Thanks,
Jack T.

Jack T.

2005-08-08, 3:25 am

PS: One more thing. When I make database structure changes, such as
altering or creating a table, I need those to get replicated also.

Thanks,
Jack T.

Breck Carter [TeamSybase]

2005-08-08, 7:24 am

Some responses below...

On 7 Aug 2005 16:57:08 -0700, "Jack T."
<71045. 3122_No_Spam_Here@co
mpuserve.com> wrote:

>I've been working with SQL Anywhere since it was owned by Watcom. However,
>I've never done any kind or replication with it. It seems like Mobilink is
>the best fit, but I'm open to suggestions. I have a 10 user copy of SQL
>Anywhere 8 Studio, and a developer version of 9 that I develop with.
>
>Situation:
>- There is a main office that has an internet connection. There will be
>normal app to DB connections there.
>- The Building Supervisors will be working disconnected and connected off
>and on as they drive around and review their building projects.
>- I need two-way replication of all tables.
>- All databases can be SQL Anywhere databases. Perhaps this can simplify
>things.
>- I have a server in the DMZ that I field applications for vendors to use.
>I could put the Mobilink server there as and if it helps the SQL Anywhere
>consolidated database as well.

All of the above sounds good. The actual remote communication
mechanism used is outside the scope of SQL Anywhere and MobiLink; if
you can get a TCP/IP connection to the internet, life is especially
good :)

>Questions:
>- What do I have to buy to implement this? (Don't send me to iAnywhere. I
>tried twice to get a straight answer.)

OK, I won't send you to iAnywhere. But most of the folks answering
questions on this forum are techs with no current knowledge of pricing
nuances. It *is* possible to get a straight answer, and I will try to
get someone to contact you. It is vacation time right now so patience
is recommended :)

Licencing a DBMS is somewhat less difficult than licencing a nuclear
reactor... however, it *is* more difficult than ordering a book from
amazon.com.

>- What do I setup where.
>- Do I have enough to test it already?
>- What do I setup where?

You need to run a central "consolidated" database server, probably the
network server dbsrv9.exe, plus at least one copy of the MobiLink
server dbsrv9.exe. If the database server is going to be heavily
loaded, run the MobiLink server on a separate box with a good network
connection to the database server.

You will also need to run the standalone database engine dbeng9.exe on
each remote computer, plus the MobiLink client dbmlsync.exe.

Except for *permission* (AKA licencing), you get all this stuff in the
Developer Edition. So you can code and test now, pay and deploy later.

>- I don't understand ASA's licensing. It goes by licenses or CPUs. Hw do
>these apply in this situation?
>- I don't want to work on it after it's setup. I want it to be as
>maintenance free as the database and not have it be a tinker's dream.

Distributed databases are more difficult to administer than a single
centralized database, that's a fact of life. It doesn't matter what
technology you use. MobiLink is designed to be centrally administered
and it is very good at that but nothing is perfect. The biggest
problem is dealing with failures caused by human error... in a
distributed environment there is no such thing as a perfect backup of
the *entire* replicating environment so recovering from a failure is
often difficult. FWIW MobiLink deals wonderfully well with "expected"
failures, e.g., dropped communications lines, but humans have an
infinite capacity for unexpected errors.

I'll be blunt: You will never find a distributed database setup that
is as easy to administer as a single database. So if that's a
showstopper for you, don't do it.

>- I don't want to write and debug a bunch of code to make it work.

Another possible showstopper... How *much* code you have to write and
debug depends on a lot of factors, mostly having to do with how your
business requirements affect the way synchronization must work.
MobiLink requires more initial setup effort (i.e., coding and testing)
than SQL Remote, for simple requirements, the difference being is that
MobiLink makes complex requirements *possible* whereas SQL Remote does
not.

Looking ahead at your next two postings, you have at least two
business requirements that will require writing and debugging code:
column-level conflict resolution, and centralized automation of remote
database schema changes.

Different organizations have a wide variety of rules governing
conflict resolution; MobiLink provides powerful mechanisms for coding
your solution, but you must still code your solution.

MobiLink also provides a mechanism for "publishing" schema changes,
but it is not (yet) as easy as SQL Remote's passthrough mechanism.
Also, neither MobiLink nor SQL Remote provide a mechanism for
reporting remote schema change failures back to the central database,
so that has to be coded and debugged... of course, that is not
necessary if you always test your remote schema changes *perfectly*
well :)

Experience shows that *other* business requirements often exist, that
affect synchronization... these requirements may not become apparent
until work begins, not because nobody knows about them (they do), but
because nobody realizes that they *affect synchronization*. It's the
number one reason I never offer a fixed-price quote on a
synchronization solution :)

>I'm
>hoping that the product is designed well enough to where it doesn't require
>that. The project is not started yet, and I can make whatever modifications
>are necessary to the tables such as two part PKs that include the
>autoincrement row plus location, timestamps, etc.

That's all good news too.

UNSIGNED BIGINT DEFAULT GLOBAL AUTOINCREMENT works very well, as do
TIMESTAMP DEFAULT TIMESTAMP columns.

>Any advice appreciated

You will get better results if you post one question per message, as
new postings. Sometimes people are reluctant to post *any* response if
they cannot answer *all* your questions... that's because the
appearance of any reply at all may cause yet other people to ignore
your posting because "it has already been dealt with" (yeah, I broke
that rule, didn't I? :)

Other approaches include: working through the tutorials in the Help,
reading Chapter 7 in the book (see below), and hiring some temporary
help to get you going.

Breck

--
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-08-08, 9:24 am

On 8 Aug 2005 05:18:37 -0700, "Breck Carter [TeamSybase]"
< NOSPAM__bcarter@risi
ngroad.com> wrote:

>plus at least one copy of the MobiLink
>server dbsrv9.exe.

Ooops that should read dbmlsrv9.exe :)

breck

--
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

Jack T.

2005-08-08, 11:25 am

Jack T.

2005-08-08, 11:25 am

>MobiLink requires more initial setup effort (i.e., coding and testing) than
>SQL Remote, for simple requirements, the difference being is that MobiLink
>makes complex requirements *possible* whereas SQL Remote does not.<

Does SQL Remote record changes at the column level or only the row level?

Bought the book, but won't see it for a day or two. Before I get the book,
what do I need in my rows? My guess is these are needed for intelligent
conflict resolution:
- Station_ID Tiny Int (PK1)
- Autoinc Int (PK2)
- LastModified TimeStamp
- Version int (Composed of Station_ID and incrementing number)
- At least one unique user column for each table. (E.G. customer code so
that nobody can enter two with the same information. In this case you would
want a conflict.)

*If I had an error once-in-awhile, it wouldn't be the end of the world. I
don't see much going to happen in the line of two people changing the same
column for the same row, but I do see different people changing different
columns for the same row.

Thanks,
Jack T.

Breck Carter [TeamSybase]

2005-08-09, 1:24 pm

SQL Remote replicates committed transactions in both directions, so if
only one column is changed that's what replicates.

MobiLink downloads rows according to the download_cursor script you
write. It uploads modified rows as they stand at the point of
synchronization; if you insert, update, delete, insert and update a
row, then sync, you will get an uploaded insert of the final row.

On 8 Aug 2005 07:34:38 -0700, "Jack T."
<71045. 3122_No_Spam_Here@co
mpuserve.com> wrote:

>
>Does SQL Remote record changes at the column level or only the row level?
>
>Jack T.
>
>PS: Bought the book, but won't see it for a day or two.
>

--
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

Jack T.

2005-08-09, 8:26 pm

> MobiLink downloads rows <

That helps a lot.

Thanks,
Jack T.

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:63rhf15vq588ghr
q55dlcu4lld2smmiim2@
4ax.com...
> SQL Remote replicates committed transactions in both directions, so if
> only one column is changed that's what replicates.
>
> MobiLink downloads rows according to the download_cursor script you
> write. It uploads modified rows as they stand at the point of
> synchronization; if you insert, update, delete, insert and update a
> row, then sync, you will get an uploaded insert of the final row.
>
> On 8 Aug 2005 07:34:38 -0700, "Jack T."
> <71045. 3122_No_Spam_Here@co
mpuserve.com> wrote:
>
>
> --
> 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-08-10, 11:26 am

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
news:63rhf15vq588ghr
q55dlcu4lld2smmiim2@
4ax.com of
sybase.public.sqlanywhere.mobilink:

BC>>
BC>>Does SQL Remote record changes at the column level or only the row
BC>>level?

Can I ask why you ask?

We can answer all sorts of technical questions, but we would prefer to
answer the real question (ie the key design choice).

Are you asking, "If two different users update the same row but
different columns and synchronize, what do each of the databases have
when each database has synchronized or replicated?"

You are obviously trying to determine which technology you should use.
This is a very important decision, so I do not want to see you make a
decision on some technical questions taken out of context of the
"big picture".

State what your requirements are, give us some idea of how data flows,
what users are allowed to update what data, volume, frequency,
connectivity and so on, and we will be able to help you to a much
greater degree.

--
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]

Jack T.

2005-08-10, 11:26 am

Thanks David,

>You are obviously trying to determine which technology you should use.
>This is a very important decision, so I do not want to see you make a
>decision on some technical questions taken out of context of the
"big picture".<

Thank you very much for your help. This is what I'm after.

Missioin:
The project involves managing a real estate development company. They
buy land, put in roads with lots, build homes and condos on them, and sell
them. The sites are geographically diverse. The office and external sites
will be working across the internet on applications hosted at the office.
This is real-time OLTP on the consolidated database. However, another group
that must be able to work disconnnected on their laptops are Construction
Supervisors and the Realtors. These are the real movers and shakers of the
data. They are generally not the ones that insert rows, but they perform
most of the maintenance on them thereafter.

Situation:
They refer to a single dwelling as a unit. The Unit table will contain rows
such as:
- Floorplan
- Address
- Construction phase (Foundation, Thru Framing, etc.)
- Available occupancy date
- Inspection Date
- Inventory Status (Available, First right of refusal, Awaiting Financing,
Sold, etc.
- Client's name and address information, etc., bank etc.

There is already a possible Realtor and Construction Supervisor conflict
here if I were to use row level replication.

At first blush, it may seem like SQL Remote is the way to go because from my
reading it appears to use the log file. That is way cool for sending out
schema changes too. However, a lot of application development software,
unless you scratch every line yourself in the code, writes the row even if
only one column changes. Doing entire apps like that means that it is
actually quite unmaintainable. I would think that this scenario would mess
up the SQL Remote scheme since it gets sent update column with the same
values. OTOH, you may have an idea to rectify this on the server end.

I'm not asking for perfect. The last guy in wins could work for me if it
were at the column level. It is far less likely in this situation that two
people will maintain the same information in the same row. However, I don't
see row replication working.

Resources:
- I can use ASA throughout or ASA plus MSSQL/MSDE. The latter saves me the
cost of the database because MSSQL is free these days with MS SBS. That
said, paying for ASA on the server normally more than pays for itself in
saved development time.

Control:
- I have no control over when remote users check in, but typically it would
be at least twice a day during work days.
- There is no way that I can catch up with these people when there is an
update. My install program can take care of offering web based updates. I
could may have the program make the changes to the local databases. That
way the local program and local data are always in step. Instances and
renames of columns in the consolidated database would be rare and require
special handling. Maybe you have an idea for this too.
- I see no reason not to have all tables replicated because the real movers
and shakers are the ones with the laptops that work disconnected.

>If two different users update the same row but different columns and
>synchronize, what do each of the databases have when each database has
>synchronized or replicated?<

Current Consolidated Database Units Table row 1:
- Construction phase: Foundation
- Inventory Status: Available

Suzi's Database Units Table row 1:
- Construction phase: Foundation
- Inventory Status: First right of refusal

Rob's Database Units Table row 1:
- Construction phase: Thru Framing
- Inventory Status: Available

Databases Units Table row 1 after Suzi and Rob sync:
- Construction phase: Thru Framing
- Inventory Status: First right of refusal

Thank you for your valuable insights,
Jack T.

"David Fishburn" <fishburn_spam@off.ianywhere.com> wrote in message
news:Xns96AE5FF00C80
7fishburnsybasecom@1
27.0.0.1...
> "Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
> news:63rhf15vq588ghr
q55dlcu4lld2smmiim2@
4ax.com of
> sybase.public.sqlanywhere.mobilink:
>
> BC>>
> BC>>Does SQL Remote record changes at the column level or only the row
> BC>>level?
>
> Can I ask why you ask?
>
> We can answer all sorts of technical questions, but we would prefer to
> answer the real question (ie the key design choice).
>
> Are you asking, "If two different users update the same row but
> different columns and synchronize, what do each of the databases have
> when each database has synchronized or replicated?"
>
> You are obviously trying to determine which technology you should use.
> This is a very important decision, so I do not want to see you make a
> decision on some technical questions taken out of context of the
> "big picture".
>
> State what your requirements are, give us some idea of how data flows,
> what users are allowed to update what data, volume, frequency,
> connectivity and so on, and we will be able to help you to a much
> greater degree.
>
> --
> 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]
>

Shuchit

2005-08-11, 3:25 am

"Jack T." <Jack. Toering_NO_SPAM_HERE
_@LeadingEdgeITA.com> wrote in
news:42fa165e@forums
-1-dub:

> I'm not asking for perfect. The last guy in wins could work for me if
> it were at the column level. It is far less likely in this situation
> that two people will maintain the same information in the same row.
> However, I don't see row replication working.
>
>

Let me explain how this would work in MobiLink. For every update begin sent
up by the remote database (the laptop users in your case) will have three
sets of values available while applying to the consolidated (the central
database).
1) values from the remote after the update
2) values from the remote from when it synchronized last
(so before the update)
3) current values in the consolidated

By comparing the columns between #1 and #2 your script can determine which
columns have changed. By comparing #2 and #3, you can determine which columns
have changed on the consolidated since this remote last synchronized.
Then based on your business rules you can apply any of these values to the
consolidated. This updated row will then be sent down to the remote in the
download phase of the synchronization and both the consolidated and remote
will now be in sync.

Shuchit

Jack T.

2005-08-11, 1:28 pm

Mobilink then is probably the right technology. I see a couple advantages
of Remote and one disadvantage.
Advantage:
- You can take row oriented changes and change them into column oriented
changes. This is big since truly a lot of programming tools work in rows
instead of columns unless you scratch the code yourself for your app.
- You have a place to analyze and adjust the logic to whatever you want to.
Disadvantage:
- Simpler setup
- Remote simply saves off it's transaction log with the changes. That's
simple and easy to replicate db structure changes although that isn't
necessarily real world. One would still have to post any DB changes before
the main app opened and after the database was up.

Jack T.

"Shuchit" <me@privacy.net> wrote in message
news:Xns96AED63D74F4
Fsvelkarprivacynet@1
27.0.0.1...
> "Jack T." <Jack. Toering_NO_SPAM_HERE
_@LeadingEdgeITA.com> wrote in
> news:42fa165e@forums
-1-dub:
>
>
> Let me explain how this would work in MobiLink. For every update begin
> sent
> up by the remote database (the laptop users in your case) will have three
> sets of values available while applying to the consolidated (the central
> database).
> 1) values from the remote after the update
> 2) values from the remote from when it synchronized last
> (so before the update)
> 3) current values in the consolidated
>
>
> By comparing the columns between #1 and #2 your script can determine which
> columns have changed. By comparing #2 and #3, you can determine which
> columns
> have changed on the consolidated since this remote last synchronized.
> Then based on your business rules you can apply any of these values to the
> consolidated. This updated row will then be sent down to the remote in the
> download phase of the synchronization and both the consolidated and remote
> will now be in sync.
>
> Shuchit

David Fishburn

2005-08-16, 1:27 pm

"Jack T." <Jack. Toering_NO_SPAM_HERE
_@LeadingEdgeITA.com> wrote in
news:42fa165e@forums
-1-dub of sybase.public.sqlanywhere.mobilink:

....
JT> Situation:
JT> They refer to a single dwelling as a unit. The Unit table will
JT> contain rows such as:
JT> - Floorplan
JT> - Address
JT> - Construction phase (Foundation, Thru Framing, etc.)
JT> - Available occupancy date
JT> - Inspection Date
JT> - Inventory Status (Available, First right of refusal, Awaiting
JT> Financing, Sold, etc.
JT> - Client's name and address information, etc., bank etc.
JT>
JT> There is already a possible Realtor and Construction Supervisor
JT> conflict here if I were to use row level replication.

So Shuchit explained how you have access to the same values as SQL
Remote to resolve conflicts. I have also posted some examples of
different techniques to resolve conflicts using MobiLink, see:

CodeXchange - Free samples
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]
Go to the MobiLink section, and look for "Conflict Resolution Without
Temporary Tables", this just gives you additional options.

JT> At first blush, it may seem like SQL Remote is the way to go because
JT> from my reading it appears to use the log file. That is way cool for
JT> sending out schema changes too.

SQL Remote and MobiLink assume a *static* schema. Schema changes are
*not* propogated to the remotes (out of the box).

Having said that, there are ways to do this with a little bit of code on
your part using MobiLink. The schema changes get placed in a LONG
VARCHAR column in a table. The table is synchronized. You use the
sp_hook_dbmlsync_sch
ema_upgrade hook and EXECUTE IMMEDIATE to apply the
changes to the remote.

JT> However, a lot of application
JT> development software, unless you scratch every line yourself in the
JT> code, writes the row even if only one column changes. Doing entire
JT> apps like that means that it is actually quite unmaintainable. I
JT> would think that this scenario would mess up the SQL Remote scheme
JT> since it gets sent update column with the same values. OTOH, you may
JT> have an idea to rectify this on the server end.

I think you are only referring to conflicts in this case. It would also
depend on how often you expect to be in a conflict scenario.

JT> I'm not asking for perfect. The last guy in wins could work for me
if
JT> it were at the column level. It is far less likely in this situation
JT> that two people will maintain the same information in the same row.
JT> However, I don't see row replication working.

This is the default for MobiLink and SQL Remote.

JT> Resources:
JT> - I can use ASA throughout or ASA plus MSSQL/MSDE. The latter saves
JT> me the cost of the database because MSSQL is free these days with MS
JT> SBS. That said, paying for ASA on the server normally more than pays
JT> for itself in saved development time.

ASA must be used as the remote. SQL Server can be used as a
consolidated.

JT> Control:
JT> - I have no control over when remote users check in, but typically it
JT> would be at least twice a day during work days.

Fine, whenever they sync works for the software.

JT> - There is no way that I can catch up with these people when there is
JT> an update. My install program can take care of offering web based
JT> updates. I could may have the program make the changes to the local
JT> databases. That way the local program and local data are always in
JT> step. Instances and renames of columns in the consolidated database
JT> would be rare and require special handling. Maybe you have an idea
JT> for this too. - I see no reason not to have all tables replicated
JT> because the real movers and shakers are the ones with the laptops
that
JT> work disconnected.

One of the reasons I mentioned the sp_hook_dbmlsync_sch
ema_upgrade.
Since you can deploy minor schema changes that way. If I was releasing
a new version of the product, I personally, would do that via some form
of setup.exe and have it executed on the remote.

JT>>If two different users update the same row but different columns and
JT>>synchronize, what do each of the databases have when each database
has
JT>>synchronized or replicated?<

Yes, this was covered by Shuchit and other samples.
If you have specific questions when you get around to implementing, post
away.

--
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]

Sponsored Links