Sybase HA and DR solution .

hi,

Any one can tell me what kind of HA , DR solution Sybase is using ? only replication  ?

latest version of Sybase I tried is ASE 15.7!

What monitoring tools , admin and development tools you guys can introduce  for Sybase ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
You should definitely look at ver 16 which offers additional Replication Modes (https://blogs.sap.com/2016/09/19/hadr-availability-on-sap-adaptive-server-enterprise-160/).

The evaluation version is available for 90 days with an extension up to 1 year (https://www.sap.com/products/sybase-ase.html).

ASE 16 is also available in the cloud: https://aws.amazon.com/marketplace/pp/B0786YQK2K

DBA tools are listed here: https://blogs.sap.com/2015/09/25/which-dba-tool-to-use-with-ase-and-when/

Data modelling tools are available for more platforms obviously: https://dbmstools.com/data-modeling-tools/sap-ase

You may also look at Toad: https://www.quest.com/products/toad-for-sap-solutions/
Joe WoodhousePrincipal ConsultantCommented:
ASE used to have a premium licensed HA option that integrated with OS clustering. In my opinion it was never very good. It's not supported anymore anyway.

ASE 15.7 introduced Cluster Edition. In my opinion this creates more problems than it solves. Don't bother.

ASE out of the box integrates well with OS clustering and shared disk. It's a reasonable solution for host-level HA... but shared disk means the database itself is still a single point of failure. There are some tips like using local not shared tempdb disk devices.

ASE also works out of the box with disk replication technologies like SRDF. Again, don't replicate tempdb. Synchronous disk replication protects against loss of host and loss of ASE... but it slows down performance greatly, and because it is a physical copy it doesn't protect against database corruption because the corruption will be copied.

Sybase Replication Server copies transactions not data, so it isn't a physical copy, so it protects against loss of host, loss of ASE, and against database corruption. It isn't an HA solution though because client connections will have to be remapped to use the replicate if the primary goes down. The replication is asynchronous so it can go to a different data centre, but asynchronous means latency and the possibility of data loss.

The only solutions that address all these issues is either some combination of the above (eg. local cluster with OS clustering and disk replication combined with replication off-site), or the new ASE 16 HADR or "Always On" option.

This is basically a local cluster that uses Sybase replication in a new synchronous way for zero latency and zero data loss. It is a robust HA solution and can be combined with third site DR or external replication. It is fairly complicated to setup and requires the latest patches to do a good job - but it works well.

Re. monitoring: Sybase has always been light on this. There are some monitoring procs built in but any serious monitoring needs custom scripts or third party products.

You don't really need any development tools to develop SQL. Text editor is fine. Run in the built-in GUI tools for development but all serious SQL work will be in procs and/or OS scripts anyway.
marrowyungSenior Technical architecture (Data)Author Commented:
pcelba,

"You should definitely look at ver 16 which offers additional Replication Modes"

you mean Near-Synchronous Replication Mode (Hot Standby) ? which only need one of the member commit the replicated data but not all of the replication member?

Joe Woodhouse,

"ASE 15.7 introduced Cluster Edition. In my opinion this creates more problems than it solves. Don't bother. "

is it like oracle RAC? does it do horizontal scale out like MS SQL, MySQL and MariaDB ? what problesm it created ?

this cluster edition still like Oracle RAC which MUST relies on shared storage ?

"There are some tips like using local not shared tempdb disk devices.
"

what is that mean sorry ?

"ASE also works out of the box with disk replication technologies like SRDF. Again, don't replicate tempdb."

is that mean all replication license included even on each site we need to setup replication, there are no need to by sybase replication server?  replication license included?

 I think it just STILL looks like MS SQL, system DB do not replicate, right?

"Sybase Replication Server copies transactions not data, so it isn't a physical copy, so it protects against loss of host, loss of ASE, and against database corruption. It"

looks the same as MS SQL replication solution and all other replication solution.


so "new ASE 16 HADR or "Always On" option. " this works well ? it has always on feature like MS SQL ? what good is ASE 16 HADR ?

"You don't really need any development tools to develop SQL. Text editor is fine. Run in the built-in GUI tools for development but all serious SQL work will be in procs and/or OS scripts anyway."

from my understanding, sybase only offer iSQL out of the box as the admin and developement tools, am i right?

what other tools you can suggest ?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

pcelbaCommented:
This blog post contains better comparison between 15.7 and 16: https://blogs.sap.com/2015/08/02/ase-157-spxxx-or-ase-160xx-which-one-should-i-choose/

And just remember any HADR solution depends on the OS and HW stability and reliability.
marrowyungSenior Technical architecture (Data)Author Commented:
sir,

I mean architecture diff, not upgrade path diff, any to share?

what is the multi master solution for Sybsae? e..g what is the sybase solution close to Oracle RAC solution ?

what is the capability diff between Sybase replication and Sybase cluster ?
Joe WoodhousePrincipal ConsultantCommented:
A complete answer to all your questions would be a three day training course. :) I'll give you brief answers here.


"You should definitely look at ver 16 which offers additional Replication Modes"

you mean Near-Synchronous Replication Mode (Hot Standby) ? which only need one of the member commit the replicated data but not all of the replication member?

That is one of the modes available in ASE 16 HADR option. It is the HADR option that is new to ASE 16.


"ASE 15.7 introduced Cluster Edition. In my opinion this creates more problems than it solves. Don't bother. "

is it like oracle RAC? does it do horizontal scale out like MS SQL, MySQL and MariaDB ? what problesm it created ?

this cluster edition still like Oracle RAC which MUST relies on shared storage ?

It is a bit like Oracle RAC, just like Sybase ASE is a bit like Oracle. It's best to treat it as its own thing rather than try to translate Sybase in terms of Oracle.

Cluster Edition is not intended for scale out. It is purely there for HA. Scale out is a clustering solution for performance once the limits of one-box hardware is reached.

In terms of problems, ASE Cluster Edition makes all DBA tasks more difficult; it is a different codeline so patches for ASE CE lag behind patches for the main ASE product. It's just a headache. When there are better methods for achieving what it does, without the additional DBA work it creates, there is no business case for using it in my opinion.


"There are some tips like using local not shared tempdb disk devices.
"

what is that mean sorry ?

It means that all disk replication technologies should be used carefully and only to replicate what is actually needed to ensure business continuity. You would not backup the TEMP directory and we should not replicate temporary areas of ASE.



"ASE also works out of the box with disk replication technologies like SRDF. Again, don't replicate tempdb."

is that mean all replication license included even on each site we need to setup replication, there are no need to by sybase replication server?  replication license included?

 I think it just STILL looks like MS SQL, system DB do not replicate, right?

SAP licensing is very complex and the only person who can tell you for sure is your SAP Account Manager.

ASE and Replication Server are separate products that must be separately installed, configured, and patched.

There is one particular ASE license that allows cores to be swapped between ASE and RS: the ASE "Platform Edition". If this was the license you were using, you would not need to separately license RS. If you use any other kind of ASE license then yes you need to separately license RS.

Again, don't try to translate Sybase architecture in terms of Oracle or MS SQL. ASE + RS is not the same as MS SQL. It is possible to replicate the master database in order to replicate logins etc.


so "new ASE 16 HADR or "Always On" option. " this works well ? it has always on feature like MS SQL ?

Yes. It works well if you use the latest patches for ASE 16 and RS 16. It is intended to provide synchronous replication with zero data loss and is "always on" in that it can survive the loss of the active host.


"You don't really need any development tools to develop SQL. Text editor is fine. Run in the built-in GUI tools for development but all serious SQL work will be in procs and/or OS scripts anyway."

from my understanding, sybase only offer iSQL out of the box as the admin and development tools, am i right?

No, you are wrong. It offers command-line text isql and the ASE client offers a GUI Interactive SQL. There is also the SAP GUI DBA Cockpit for GUI administration.



what other tools you can suggest ?

I don't suggest any other tools because no-one needs any other tools. :) Anything in Production must be run by scripts, we would never use a GUI for manual commands. GUI tools are there to impress managers who walk past our desks to check if we are working. :) Nobody uses any GUI tool for any serious work.

There are many other tools out there but save your money, you do not need them.


I mean architecture diff, not upgrade path diff, any to share?

what is the multi master solution for Sybase? e..g what is the sybase solution close to Oracle RAC solution ?

what is the capability diff between Sybase replication and Sybase cluster ?

First... I really think you should stop thinking in terms of Oracle. :) The products are different. If you want a solution that is like Oracle RAC then I suggest you use Oracle RAC.

Clustering and replication solve completely different problems. I think it is strange and not helpful to try to compare the capabilities of these two very different things.

I think you need to be more clear on the differences between HA and DR.

HA is about "hardening" the systems or making it less likely that there will be a failure or outage. RAID is a HA solution because it can survive the loss of a disk. Clustering is a HA solution because it can survive the loss of a host.

DR is about "recovery" or reducing the time and effort necessary to deal with a failure or outage. Taking backups is a DR solution because it makes it faster to restore from loss of a database. Replication is a DR solution because it makes it easier to survive the loss of a host (even a host cluster) because clustering and other HA solutions must be in the same data centre, but replication can (and should) be in a different data centre.

Here is the dirty secret that Oracle RAC does not want you to know: you don't need it. Ordinary Oracle plus OS level clustering solutions are enough. The same is true for Sybase ASE. You do not need ASE Cluster Edition. It is enough to use ordinary ASE plus OS level clustering solution - in fact it is better!

This is how the largest banks in the world do it:

They run ASE (normal version) on a host. They use OS clustering like Veritas to have a second host. Should the primary host fail then resources like IP addresses and disks are unmounted from the primary and remounted on the secondary. There is a brief outage while this happens. If literally zero outage is acceptable to the business then they use other methods but they all involve some way to detect a failure and remap connecting clients to another host. As of ASE 16 there is now ASE 16 HADR "Always On" which can do this with zero data loss and zero outage.

Confusingly many sites call this a DR box, but it is really an HA box because it is in the same data centre and it is intended to reduce the chance of an outage. These sites will then replicate out of this HA pair to a DR box in a different data centre. This is DR because it is intended to reduce the impact of an outage, not reduce the chance of an outage.

This is probably about as much detail as you can expect for free from volunteers. :)
marrowyungSenior Technical architecture (Data)Author Commented:
"Cluster Edition is not intended for scale out. It is purely there for HA. Scale out is a clustering solution for performance once the limits of one-box hardware is reached."

tks.

"If you want a solution that is like Oracle RAC then I suggest you use Oracle RAC. "

what i mean is multi master solution, all nodes can accept incoming connection and write operation . of cource RAC is not going to ALL write operation at the same time!

"Clustering and replication solve completely different problems. I think it is strange and not helpful to try to compare the capabilities of these two very different things."

I know they are very diff! I just can't see the explaination from web site ! the sybase web site SEEMS describing they are the same, and I don't trust that as Sybase should not be that weak ! a lot of other DB can , but why it can't  ?

so I come to EE and see if what i saw is right  or not !

"HA is about "hardening" the systems or making it less likely that there will be a failure or outage. RAID is a HA solution because it can survive the loss of a disk. Clustering is a HA solution because it can survive the loss of a host.

DR is about "recovery" or reducing the time and effort necessary to deal with a failure or outage. Taking backups is a DR solution because it makes it faster to restore from loss of a database. Replication is a DR solution because it makes it easier to survive the loss of a host (even a host cluster) because clustering and other HA solutions must be in the same data centre, but replication can (and should) be in a different data centre."

in here, this topic is not describe in this way ! cluster is HA and you are right that DR need across data center.

in here HA mainly handled by cluster, in the same site! RAID is a subset of it but is more than a backup and restore solution.

replicaiton can be refer to DR solutoin I agree but nowaday it is about HA + DR,.

"OS level clustering solution - in fact it is better!"  
"Ordinary Oracle plus OS level clustering solutions are enough.


what is OS level clustering solution?

where oracle DBa will say why should we buy Oracle if it is not about multi write master role it has !

"They run ASE (normal version) on a host. They use OS clustering like Veritas to have a second host."

so it is a software replication that replciate all data to another nodes ? and each node run a copy of vertias ?

"There is a brief outage while this happens"

this method yes !

" As of ASE 16 there is now ASE 16 HADR "Always On" which can do this with zero data loss and zero outage"

any topology diagram with explaination on how it works ?

it shoudl be the same as the SQL server always on solution with listener handling the SQL routing

"This is DR because it is intended to reduce the impact of an outage, not reduce the chance of an outage."

and also this  is in separate site, usually far ways from primary site when primary site total failed !
marrowyungSenior Technical architecture (Data)Author Commented:
are you daliy Sybase ONLY DBA ?  what kind of web resource you will use that give you step by step instruction on how to set thing up in Unix ?
Joe WoodhousePrincipal ConsultantCommented:
I've worked exclusively with Sybase for over 23 years. :)

The free manuals really are the most helpful guide here. They will give you everything you need - there is a Getting Started manual, and then an Installation Guide for each platform, and a Configuration Guide for UNIX. I'd suggest you need take a look at the System Administration Guide,

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
"You may also look at Toad: https://www.quest.com/products/toad-for-sap-solutions/"

do you think Toad is the best for Sybase ?

how toad bought by dell, the product seems not better !

did you try Aqua Data studio before? one product for all DB man! am not sure if it is better than Toad for Sybase.

what is the Sybase topology and architecture URL you can suggest ?

"I've worked exclusively with Sybase for over 23 years. :)"

good and I think you can explain the HA, DR and always on feature Sybase here. this is what I need in this question.
marrowyungSenior Technical architecture (Data)Author Commented:
hi tks.

but please explain more.
Joe WoodhousePrincipal ConsultantCommented:
I don't use Toad. Don't need to. I don't use Aquadata Studio. Don't need to. All real work is done in scripts. If I am writing adhoc SQL I do it in a text editor. If for some reason I am feeling super lazy and want to use a GUI I use the free GUI that comes with the ASE client. There truly isn't any need for anything else. If you are determined to do everything in a GUI then it doesn't matter which you use as they're all sending the same text to the database in the end.

"Using SAP Failover in a High Availability System" is an excellent introduction to how to setup ASE to work with OS clustering.
https://help.sap.com/viewer/e5ef9cdd9a774067a200af13ce794dee/16.0.3.6/en-US

"HADR Users Guide" discusses the new Always On.
https://help.sap.com/viewer/efe56ad3cad0467d837c8ff1ac6ba75c/16.0.3.6/en-US
marrowyungSenior Technical architecture (Data)Author Commented:
"use the free GUI that comes with the ASE client."

iSQL ?

what kind of proactive monitoring tools you use for Sybase ? idera or surbeilance DB? or still use script?

"All real work is done in scripts. If I am writing adhoc SQL I do it in a text editor"

you also use script script to monitor for all sybase DB? how many Sybase DBs you are monitoring ?

""Using SAP Failover in a High Availability System" is an excellent introduction to how to setup ASE to work with OS clustering.
https://help.sap.com/viewer/e5ef9cdd9a774067a200af13ce794dee/16.0.3.6/en-US

"HADR Users Guide" discusses the new Always On.
https://help.sap.com/viewer/efe56ad3cad0467d837c8ff1ac6ba75c/16.0.3.6/en-US"

both link is empty for me !  need login to their portal ?
Joe WoodhousePrincipal ConsultantCommented:
Those links do not require a login and go directly to the manuals.

And yes, GUI tools are only useful if we are monitoring one ASE. Maybe a few. Impossible to adequately monitor dozens of databases in dozens of ASEs by having to manually be in front of a moving picture. And how is trend analysis possible unless reports are written to file and retained? I keep saying it and you keep not believing me - all serious work is not done in GUI.

Any large site has scripts running on crontab to regularly check on all ASEs, often in combination with stored procedures. Alerts are emailed. All reports are archived.

There are a number of third party tools for monitoring Sybase, but as I have already said, they do not do anything that can't be done with stored procedures and scripts. Really. They are running the same SQL under their pretty GUI. They do not add any value and are a waste of money - even the free ones.
marrowyungSenior Technical architecture (Data)Author Commented:
"Those links do not require a login and go directly to the manuals."

might be internet problem and today i can access it.

tks.

"I keep saying it and you keep not believing me - all serious work is not done in GUI."

nono. I believe on you . usually for large scale monitoring I will use GUI tools to send me alert as soon as once it happened.

then we go inside that server to troubleshoot using script !

so in unix large scale monitoring you still prefer using script? any link to share the script you use ?

"Any large site has scripts running on crontab to regularly check on all ASEs, often in combination with stored procedures. Alerts are emailed. All reports are archived."

yeah you prefer in this way, any URL can share the whole experience on this ?
marrowyungSenior Technical architecture (Data)Author Commented:
by the way, you don't even use GUI tools to admin and development on Sybase ?
Joe WoodhousePrincipal ConsultantCommented:
Sybase was always light on bundled tools for management, and that hasn't changed much since it was bought by SAP.

ASE includes the command line isql and the GUI dbisql for running queries. dbisql has most of the features offered by other third party tools. Many people like "sqsh" which is a freeware tool that extends command line isql capabilities to be more like a UNIX shell (hence the name - SQl SHell). For management there is ASE Cockpit which is very similar to the third party tools like DBArtisan etc.

I am just not convinced that it's worth paying for any of the paid tools.

There isn't really a standard set of scripts, so what you tend to find is that most very large sites have built their own over time. It's a bit scandalous really that there must be tens of thousands of backup and monitoring scripts scattered around the Sybase world! This is one of the reasons that it's possible for third party vendors to offer their tools at a price, because there is no standard set of scripts included by SAP. This isn't an accident - SAP doesn't want to be liable if something goes wrong, I think.

So to answer your specific question: normally if I am working at a site they already have their in-house scripts which they've been using for years. I might be able to make improvements to the scripts but I won't be allowed to completely rewrite or replace them. I think only once or twice have I been in a position where I was the one creating scripts where none had existed before.

It is very easy to write our own set of scripts that handle about 80% of what's needed. The final 20% is what everyone does differently. But yeah I haven't used a GUI tool to manage Sybase environments in years. I think the last time was maybe around 2007! :)

Likewise for development I don't need anything fancy. I'm a long time user of UNIX so things like vi are simple for me. If I was completely new to UNIX/Linux command lines I doubt I'd be happy about using it though, but even then the built-in dbisql would probably be all I wanted. If I absolutely wanted a modern editor that understood syntax and auto-indenting etc. I'd probably use Atom which I was introduced to when I started learning Python.
marrowyungSenior Technical architecture (Data)Author Commented:
"ASE includes the command line isql and the GUI dbisql for running queries"
excellent!
tks.

"There isn't really a standard set of scripts, so what you tend to find is that most very large sites have built their own over time. It's a bit scandalous really that there must be tens of thousands of backup and monitoring scripts scattered around the Sybase world! "

when I work on it hopefully I can find it easily ! any good site you use daily  for Sybase for tools ? still Sybase books online ?

"There isn't really a standard set of scripts, so what you tend to find is that most very large sites have built their own over time. It's a bit scandalous really that there must be tens of thousands of backup and monitoring scripts scattered around the Sybase world! "

yeah.
marrowyungSenior Technical architecture (Data)Author Commented:
tks all.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.