SQL Anywhere Mobile - Migrating from SQL Remote

Author

Migrating from SQL Remote

Stefan Lobet

2005-08-10, 7:25 am

Hi all

We are looking into migrating to Mobilink as our current solution (SQLRemote
between SQL Anywhere client databases and an ASE consolidated database) will
no longer be supported later this year (actually, we may start to replicate
to a Microsoft SQL Server consolidated DB so Sybase has shot itself in the
foot there...). So far my tests seem promising but I have one question that
I can't find an answer to in the documentation.

Our setup is that ALL transactional information (sales etc) is generated at
the remote sites. To ensure primary key uniqueness, we use composite
primary keys such as (remote_site_id, sales_no) where each remote site has a
unique id, and the sales_no is ever incrementing at each remote site. Not
only does this solution provide PK uniqueness, we also use the
remote_site_id to partition the table between remote sites.

Now my question: in the discussion about PK uniqueness in the Mobilink
documentation, nowhere does it suggest this solution as an option, though it
seems intuitive to me. Can we continue to use this setup? Or do we HAVE to
move to one of the solutions in the documentation (UUID, global
autoincrement, key pools). Each of these solutions will increase our
migration effort and we'd prefer to keep things as they are.

Thoughts and opinions appreciated.

Thank you,

Stephane Lobet

Eric Verhorstert

2005-08-10, 7:25 am

We use the same techniique where all tables have a column kd_instal
(data entered at consolidated site). Some tables have a column kd_instal
+ farmno (data entered at remote site). We have also just started using
mobilink and are testing it. Till now everything went fine. If you meet
any problems with this setup we would like to here them.

If I may ask: why did your company decide to switch to Microsoft SQL
server as consolidated database instead of ASA?

Eric

Stefan Lobet wrote:
> Hi all
>
> We are looking into migrating to Mobilink as our current solution (SQLRemote
> between SQL Anywhere client databases and an ASE consolidated database) will
> no longer be supported later this year (actually, we may start to replicate
> to a Microsoft SQL Server consolidated DB so Sybase has shot itself in the
> foot there...). So far my tests seem promising but I have one question that
> I can't find an answer to in the documentation.
>
> Our setup is that ALL transactional information (sales etc) is generated at
> the remote sites. To ensure primary key uniqueness, we use composite
> primary keys such as (remote_site_id, sales_no) where each remote site has a
> unique id, and the sales_no is ever incrementing at each remote site. Not
> only does this solution provide PK uniqueness, we also use the
> remote_site_id to partition the table between remote sites.
>
> Now my question: in the discussion about PK uniqueness in the Mobilink
> documentation, nowhere does it suggest this solution as an option, though it
> seems intuitive to me. Can we continue to use this setup? Or do we HAVE to
> move to one of the solutions in the documentation (UUID, global
> autoincrement, key pools). Each of these solutions will increase our
> migration effort and we'd prefer to keep things as they are.
>
> Thoughts and opinions appreciated.
>
> Thank you,
>
> Stephane Lobet
>
>

David Fishburn

2005-08-10, 11:26 am

"Stefan Lobet" <stephane.lobet@pretpret.com> wrote in
news:42f9cdd8@forums
-1-dub of sybase.public.sqlanywhere.mobilink:

....
SL> Our setup is that ALL transactional information (sales etc) is
SL> generated at the remote sites. To ensure primary key uniqueness, we
SL> use composite primary keys such as (remote_site_id, sales_no) where
SL> each remote site has a unique id, and the sales_no is ever
SL> incrementing at each remote site. Not only does this solution
provide
SL> PK uniqueness, we also use the remote_site_id to partition the table
SL> between remote sites.
SL>
SL> Now my question: in the discussion about PK uniqueness in the
Mobilink
SL> documentation, nowhere does it suggest this solution as an option,
SL> though it seems intuitive to me. Can we continue to use this setup?
SL> Or do we HAVE to move to one of the solutions in the documentation
SL> (UUID, global autoincrement, key pools).

Unique is unique.
If you can generate an ID you know will not collide with another
user/database, wonderful. MobiLink simply syncs inserts, updates and
deletes between the databases.

There are many, many ways to do this in practice. It is difficult to
present all of these options without hopelessly confusing first time
users. So we decided on an approach and describe it.

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

> If I may ask: why did your company decide to switch to Microsoft SQL

I would be interested in knowing that too.

I work a lot in MSSQL. I avoid MSSQL for at least these reasons:
- No distinct DATE TIME types makes time reporting a pain and ugly with any
tool
- DDL RI is full of limitations. Because of this, you will have to enforce
several critical FK relationships in your database with hand written
triggers.
- Trigger information cannot be leveraged by a query optimizer, only DDL
relationships.
- You cannot reverse engineer to maintain a database where relationships are
based on triggers.
- MSSQL costs twice what ASA does.
- ASA is much easier to restore from a failure or an oops.
- MSSQL 2005 uses the .NET framework and the tools are terrible compared to
MSSQL 2000(And MSSQL 2000 tools are inferior to ASA's and a lot less user
friendly.) People are unhappy on the 2005 forums and asking for WIN32
tools.
- MSSQL 2005 fixes the distinct DATE TIME type problem, but it still has the
exact same DDL RI limitations that MSSQL 2000 suffers from, meaning it will
be another 2 to 5 years before MSSQL catches up to where ASA was in 1989.
MSSQL Express 2005, the freely distributable replacement for MSSQL's 2000
MSDE, is too large to distribute. Along with Frame Work 2.0, it takes as
long to install as the OS.

I have vertical market apps based on ASA and all of the demos are
MSDE/MSSQL. If ASA had an MSDE type product, or even a single user database
that was freely distributable for demos etc., I wouldn't use MSSQL/MSDE at
all. I do a lot in MSSQL, but what drives it are other factors, such as a
large investment in business information and applications currently using
MSSQL. Giving the customer ASA out of my own pocket is less expensive than
paying for the extra development time and using their free copy of MSSQL
that comes with their MS SBS. It is seldom that I have to recommend MSSQL
over ASA.

From a stature standpoint, I rate MSSQL as better than any free DBMS, and
not as good as any that you have to pay money for.

Thanks,
Jack T.

"Eric Verhorstert" <ontsnapt@hotmail.com> wrote in message
news:42f9d726$1@foru
ms-2-dub...[color=darkred]
> We use the same techniique where all tables have a column kd_instal (data
> entered at consolidated site). Some tables have a column kd_instal +
> farmno (data entered at remote site). We have also just started using
> mobilink and are testing it. Till now everything went fine. If you meet
> any problems with this setup we would like to here them.
>
> If I may ask: why did your company decide to switch to Microsoft SQL
> server as consolidated database instead of ASA?
>
> Eric
>
> Stefan Lobet wrote:

Stefan Lobet

2005-08-10, 11:26 am

Wow... got a bit more than I bargained for here!

I am personally not a fan of all things Microsoft, although some of their
software is actually quite good these days (and some of it REALLY bad). The
situation is that most IT staff here (= all except me) is very MS oriented
(I try to make them see the light, to no avail...). Except for one important
application, ALL of our systems run against MS SQL Server databases. The one
application that doesn't was written in-house about 8 years ago (in PB), it
runs against SQL Anywhere DB's in our remote sites that replicate to an ASE
box in head office (the only ASE box we have).

So, strategically thinking, it makes sense to consolidate everything on the
MS platform. We do want to keep replication though, because we cannot afford
to have our remote sites without their core application if the broadband
connection to head office goes down (which happens enough to be a concern).
If we can save ourselves the yearly ASE cost and move to something we're
already paying for, it would be music to the ears of our IT director.

I am worried about MSSQL 2005 being .NET based (We've got a few new
applications we did in VB.Net and boy you have to be extremely careful or
performance is terrible). I'm also interested in your statement about DDL RI
limitations. I'm not an SQL Server expert, so could you point me to some
examples? Might help my case to switch to Linux and ASE...

Cheers

Stefan

"Jack T." <Jack. Toering_NO_SPAM_HERE
_@LeadingEdgeITA.com> wrote in message
news:42fa0547$1@foru
ms-1-dub...
>
> I would be interested in knowing that too.
>
> I work a lot in MSSQL. I avoid MSSQL for at least these reasons:
> - No distinct DATE TIME types makes time reporting a pain and ugly with
any
> tool
> - DDL RI is full of limitations. Because of this, you will have to
enforce
> several critical FK relationships in your database with hand written
> triggers.
> - Trigger information cannot be leveraged by a query optimizer, only DDL
> relationships.
> - You cannot reverse engineer to maintain a database where relationships
are
> based on triggers.
> - MSSQL costs twice what ASA does.
> - ASA is much easier to restore from a failure or an oops.
> - MSSQL 2005 uses the .NET framework and the tools are terrible compared
to
> MSSQL 2000(And MSSQL 2000 tools are inferior to ASA's and a lot less user
> friendly.) People are unhappy on the 2005 forums and asking for WIN32
> tools.
> - MSSQL 2005 fixes the distinct DATE TIME type problem, but it still has
the
> exact same DDL RI limitations that MSSQL 2000 suffers from, meaning it
will
> be another 2 to 5 years before MSSQL catches up to where ASA was in 1989.
> MSSQL Express 2005, the freely distributable replacement for MSSQL's 2000
> MSDE, is too large to distribute. Along with Frame Work 2.0, it takes as
> long to install as the OS.
>
> I have vertical market apps based on ASA and all of the demos are
> MSDE/MSSQL. If ASA had an MSDE type product, or even a single user
database
> that was freely distributable for demos etc., I wouldn't use MSSQL/MSDE at
> all. I do a lot in MSSQL, but what drives it are other factors, such as a
> large investment in business information and applications currently using
> MSSQL. Giving the customer ASA out of my own pocket is less expensive
than
> paying for the extra development time and using their free copy of MSSQL
> that comes with their MS SBS. It is seldom that I have to recommend MSSQL
> over ASA.
>
> From a stature standpoint, I rate MSSQL as better than any free DBMS, and
> not as good as any that you have to pay money for.
>
> Thanks,
> Jack T.
>
> "Eric Verhorstert" <ontsnapt@hotmail.com> wrote in message
> news:42f9d726$1@foru
ms-2-dub...
(data[color=darkred]

generated[color=dark
red]
though[color=darkred
]
HAVE[color=darkred]
>

Stefan Lobet

2005-08-10, 11:26 am

Thanks, this answers my question.

Stefan

"David Fishburn" <fishburn_spam@off.ianywhere.com> wrote in message
news:Xns96AE620BD441
7fishburnsybasecom@1
27.0.0.1...
> "Stefan Lobet" <stephane.lobet@pretpret.com> wrote in
> news:42f9cdd8@forums
-1-dub of sybase.public.sqlanywhere.mobilink:
>
> ...
> SL> Our setup is that ALL transactional information (sales etc) is
> SL> generated at the remote sites. To ensure primary key uniqueness, we
> SL> use composite primary keys such as (remote_site_id, sales_no) where
> SL> each remote site has a unique id, and the sales_no is ever
> SL> incrementing at each remote site. Not only does this solution
> provide
> SL> PK uniqueness, we also use the remote_site_id to partition the table
> SL> between remote sites.
> SL>
> SL> Now my question: in the discussion about PK uniqueness in the
> Mobilink
> SL> documentation, nowhere does it suggest this solution as an option,
> SL> though it seems intuitive to me. Can we continue to use this setup?
> SL> Or do we HAVE to move to one of the solutions in the documentation
> SL> (UUID, global autoincrement, key pools).
>
> Unique is unique.
> If you can generate an ID you know will not collide with another
> user/database, wonderful. MobiLink simply syncs inserts, updates and
> deletes between the databases.
>
> There are many, many ways to do this in practice. It is difficult to
> present all of these options without hopelessly confusing first time
> users. So we decided on an approach and describe it.
>
> --
> 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

>I'm also interested in your statement about DDL RI limitations. I'm not an
>SQL Server expert, so could you point me to some examples?

Consider this scenario:
1. You have a Customers file, (one) that is related to your Quotes file
(many). If you delete the Customers row, it will cascade and delete the
rows for that customer in the Quotes file.
2. Next you add a Jobs file to the database. The Jobs file is a child of
both the Customers file and the Quotes file because you quoted the Job you
got.
- Customers, Jobs
- Customers, Quotes, Jobs

ASA - You can set it up just like you can in the CW dictionary. It works
perfectly, gives no trouble, and you never think about it again. ASA has
had this since 1989 along with separate DATE and TIME types.

MSSQL - You cannot even define the relationships because the Customers file
RI hierarchy intersects from two directions at the Jobs file. They can be 1
table separated or 20 tables separated, but if they meet, you cannot define
the relationship. It happens commonly on join tables.

How often does this situation occur? Wait until you start writing. I'm not
a Microsoft hater. However, I am a show-me guy. Here is what I've seen.
Microsoft has always had problem with databases for some reason. It took
them 6 years to come up with the Jet 1 engine used in Access. They bought
stuff ever since. In version 2000 they introduced RI because they had been
quite a laughing stock for not having it. Of course it doesn't work to this
day, nor does it in MS SQL 2005.

ASA is flat out boring. It never gives me a problem even on large sites,
and the customers understand how it works. In that regard, it's not good
for business. MSSQL is solid too. But lets talk about life. You get a
call that someone did something stupid and wiped out a bunch of data. With
ASA, you come down, convert the log file to SQL, read through the script,
find the catastrophe, delete that section, and save the script. Next, you
delete their current database, bring back the last backup. Next you run the
world's longest SQL script. Nothing is lost except the oops. They don't
have to re-key data since the last backup. You can get hot backups with
MSSQL too, but there are a lot more considerations when it comes to backing
up or restoring. I spend hours "scenarioizing" backup strategies and
restores. In fact, I write it into my programs and call the MSSQL API so
that they don't get it wrong.

Try this experiment with MSSQL. Go into one of your active tables while
people are inputting data and type, DELETE FROM MyTable;COMMIT;. Wait a few
minutes and then tell people to stop inputting data. If the backups were
done right, and if you do it right you can. In ASA, it's what we call a no
brainer. The odds are that you and the "not easy to find stuff" ASA manual
will be able to beat any 6 of them working on their MSSQL restore.

Catastrophes aren't a daily occurrence, so no matter what, you won't
remember the procedure. With ASA, it won't take you long even if you don't
have a clue.

Other:
Let's try something else that is really simple. Let's say that you just did
an insert row into the database, and because this row has children, you need
the row number of the row that you just inserted to use in the child rows as
the FK value. If you are thinking of using @@IDENTITY to return the last
autoincremented value, it won't work reliably because MSSQL doesn't promise
to return the last row that YOU inserted with THIS process. There is no
Get_Identity in MSSQL to get a new value and reserve it for you. You can
use MSSQL's Scope_Identity, but you can't use it from within your app like
IDENTITY. So, there are lots of work-arounds. Probably the easiest is to
force have another unique column in the row that you can do a fetch on to
bring the row back.

Like I said earlier, I'm not a Microsoft hater. I use a lot of their stuff,
and some things are really cool. If you ever seen MSMQ, it is like being
able to snap in your own message replication ring that will work for
anything. I can't find any spreadsheet that I like better the Excel.
However, I know of no other company that has the capacity to intentionally
mislead and misrepresent their own products and that of others like
Microsoft. In this brief conversation we've seen the claim that they have
both DDL RI and IDENTITY, but it does not meet the industry standard
definition. They said that they had native IP when Netware didn't. The
fact is, Netware had it long before they did. Prior to that Netware was
encapsulating IPX and NCP and Microsoft was encapsulating Net Bios and SMB.
It means that they may have good products, but their word is worth nothing.
Microsoft said that they would never run unprotected like Netware does in
the safer areas for performance. As soon as Microsoft figured out how, they
started running their video there, the riskiest of all, and it has been
there since. You couldn't possibly have read an unbiased review of their
products since the release of NT 4.0 because it is written into their
licenses that all reviews must be approved by them. Spreading FUD has
proven effective in keeping them in the game where their products are not.
The will get people to wait until 2009 to get what ASA had in 1989. They
speak of possible future capabilities in the present tense. They start new
buzzwords and add the definitions later. I guess I shouldn't beat around
the bush and state plainly, you shouldn't ignore them, but they are masters
in deception because they have a lot of practice.

Regards,
Jack T.

>Might help my case to switch to Linux and ASE...<
Or switch to ASA

>
> Cheers
>
> Stefan
>
>
>
> "Jack T." <Jack. Toering_NO_SPAM_HERE
_@LeadingEdgeITA.com> wrote in message
> news:42fa0547$1@foru
ms-1-dub...
> any
> enforce
> are
> to
> the
> will
> database
> than
> (data
> generated
> though
> HAVE
>
>

Jack T.

2005-08-10, 8:25 pm

PS: One more thing. MSSQL is not and will never be multi-platform. What
if you want to run it on a dedicated web server on an internet backbone
somewhere? ASA and most other DBMSs run about anywhere. With ASA, the same
db and log file work on all so you can test in one environment and copy to
another. In fact that is exactly what I do. With NT, I can tell when I
have an index missing or a bad plan. With the others, I don't know until
it's in production. Why pay money to get put in that position?

Greg Fenton

2005-08-10, 8:25 pm

Stefan Lobet wrote:
>
> So, strategically thinking, it makes sense to consolidate everything on the
> MS platform. We do want to keep replication though, because we cannot afford
> to have our remote sites without their core application if the broadband

Why not consolidate on the ASA platform? Your remotes are ASA, why not
make your consolidated ASA too? That argument should hold with your IT
managers, shouldn't it?

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Sponsored Links