tools for Sybase.

marrowyung
marrowyung used Ask the Experts™
on
hi,

I am MS SQL DBA and I am now going to study Sybase feature.

1) did sybase has horizontal scale out feature like MS SQL always on?
2) what is the tools (cost/free) tools that detect problematic query ? slowest query at run time?
3)  any tools to detect which part of the slowest query cause the problem?
4) what is the monitoring tools you all use can monitor most of the Sybase problem.
5) any tools help on detecting Sybase query anti pattern ?
6) any tools help on checking missing index and unused index?
7) what is the best backup tools for Sybase.
8) any read only round robin load balancing operation across sybase node?
9) admin and developement tools usually used by sybase DBA and why that?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Joe WoodhousePrincipal Consultant
Most Valuable Expert 2012
Commented:
That's about two weeks of training courses... :)
Principal Consultant
Most Valuable Expert 2012
Commented:
1a. MS SQL Always On isn't horizontal scale out, it's an HA solution. These are not the same things.
1b. Sybase ASE does not have horizontal scale out because it doesn't need to. In the latest version there is 99+% scalability even up to 256 cores. Unlike MS SQL, Sybase ASE is not limited to Windows platforms and the hardware limits of those platforms.
1c. Sybase ASE has an Always On HA/DR solution.

2. ASE includes the "MDA" stored procedures for free. There is also the sp_sysmon and sp_object_stats stored procedures. You don't need anything else. Every third party solution whether free or paid is built on top of these and all they give you are pretty pictures which are not actually useful for solving real problems.

3. As above.

4. As above.

5. As above.

6. As above, plus also "set showplan on".

7. The best backup solution for Sybase is Sybase. The Sybase Backup Server is included with ASE and provides fully online backups that are full, incremental, and incremental-since-full also known as cumulative.

8. Sybase ASE doesn't need nodes to load balance between because see #1 above - it isn't limited to Windows platforms and so a single ASE can use 256 cores and 4Tb of RAM.

9. The admin tools usually used by Sybase DBAs are the command line, scripts, and stored procedures, combined with OS scheduling tools like crontab or (if you must run on Windows) the Windows AT command. The development tools usually used by Sybase developers are text editors. Really. You don't need anything more than these tools.

I think the main thing someone coming to Sybase from MS needs to think about is that the Windows world is very very different to UNIX and Linux. UNIX/Linux servers almost never have GUI environments. Everything is command line and text editors. Servers do nothing but act as servers and so don't need any GUI or desktop capabilities. All UNIX/Linux Production environments run from the command line and text scripts. I don't think most sysadmins have, use, or want GUI tools, and it's my experience that very few DBAs do either. About all the GUI tools are useful for is "at a glance" desktops that can show you red/yellow/green... but if you are part of a team looking after 100s or 1000s of servers all running 100s or 1000s of users, GUI tools are useless.

I think the biggest challenge for you is to stop thinking in terms of MS Windows and MS SQL. It is a very different world when you move away from the limits of the platform and the hardware the platform runs on. Approach Sybase like it is something entirely new rather than trying to make it look and work like MS SQL. The free Sybase manuals on the SAP site are very helpful. Good luck and welcome to Sybase!
marrowyungSenior Technical architecture (Data)

Author

Commented:
"1a. MS SQL Always On isn't horizontal scale out, it's an HA solution. These are not the same things."

ok, this is the first time I hear that! so what makes AlwaysON diff from HA ? HA and DR SQL server already has it long time ago..

from your point of view,do MySQL and MariaDB has horizontal scale out ?

what horizontal scale out is diff from HA and DR?

"1b. Sybase ASE does not have horizontal scale out because it doesn't need to. In the latest version there is 99+% scalability even up to 256 cores. Unlike MS SQL, Sybase ASE is not limited to Windows platforms and the hardware limits of those platforms."

since SQL server 2017, it already ready for linux, container..

"ASE includes the "MDA" stored procedures for free."

any complete guideline about thsi ?

"6. As above, plus also "set showplan on"."

same as MS SQL

"7. The best backup solution for Sybase is Sybase. The Sybase Backup Server is included with ASE and provides fully online backups that are full, incremental, and incremental-since-full also known as cumulative. "

any compression and encryption for backup?

"8. Sybase ASE doesn't need nodes to load balance between because see #1 above - it isn't limited to Windows platforms and so a single ASE can use 256 cores and 4Tb of RAM. "

oh, CPU Core ?
 
but load balancing can make it even better, agree? what if that node is down time and othe nodes can keep serving ?

" The development tools usually used by Sybase developers are text editors. Really. You don't need anything more than these tools.
"
text editor can do a lot more than, e.g. , MS SQL SSMS e.g. ?

"I think the main thing someone coming to Sybase from MS needs to think about is that the Windows world is very very different to UNIX and Linux. "

yes !

"but if you are part of a team looking after 100s or 1000s of servers all running 100s or 1000s of users, GUI tools are useless."

why ?

" Approach Sybase like it is something entirely new rather than trying to make it look and work like MS SQL

as MS SQL is build from Sybase, the logical structure still looks like Sybase, so I think from that way.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Joe WoodhousePrincipal Consultant
Most Valuable Expert 2012

Commented:
Experts Exchange wants me to ask you to ask these each as new top-level questions here as we are well beyond the scope of what a single question is intended to cover.

Everything I've mentioned is well documented in the free ASE manuals.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Experts Exchange wants me to ask you to ask these each as new top-level questions"

EE should ask me directly !

Once you answer them that I will close this question quickly.

or you can simply give me some resource/URL you trust and I will read it myself based on the topic above.
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks. but please explain more, e.g., what horizontal scale out defination is in your mind. why MS SQL server is not.?
marrowyungSenior Technical architecture (Data)

Author

Commented:
so 3) and 5) you always run MDA to find it out?

so which part of the queries takes longest time will be shown by MDA? do you think information returned by MDA is too much and not highly focus and you need a tools to consolidate that?
Joe WoodhousePrincipal Consultant
Most Valuable Expert 2012
Commented:
There are third party tools to run the MDA queries for you. They do not add any value and are just sending the same SQL to the ASE. Often they are actively dangerous to run because in order to have a useless but pretty GUI they constantly refresh... meaning the MDA SQL is sent to ASE every 5 seconds or something crazy like that.

There are many good presentations around the internet for how MDA works and with sample queries. When I search for "Sybase MDA" the first page of results has several useful links. MDA is also written up in one of the Performance & Tuning manuals.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"There are third party tools to run the MDA queries for you. They do not add any value and are just sending the same SQL to the ASE. "

agree ! very likely .

"Often they are actively dangerous to run because in order to have a useless but pretty GUI they constantly refresh... meaning the MDA SQL is sent to ASE every 5 seconds or something crazy like that."

yeah, but Sybase can handle it right ? all monitoring tools operate in this way.

"When I search for "Sybase MDA" the first page of results has several useful links. "

ha, of course i know, but i have to dig into more detail.

in MS SQL , there are also very powerful but free tools with GUI to tell you which part of the query jet up everything and how to solve it.
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks man.
Joe WoodhousePrincipal Consultant
Most Valuable Expert 2012
Commented:
To clarify more on my earlier statement: the overhead of running MDA queries depends on exactly which tables are queried, but guidance from SAP is that they should not be run more often than once a minute (for the lowest impact) or every 5 minutes (for the highest). Many GUI tools refresh every 5 seconds which absolutely will impact the ASE monitored.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"but guidance from SAP is that they should not be run more often than once a minute (for the lowest impact) or every 5 minutes (for the highest). "

ok. I think should be the same effect of the same type of tools I run in MS SQL, as the tools also use a lot of cursor loops ! which shouldn't be!

but that tools i use in MS SQL is slow, this mean the DB is the bottleneck.

 I usually refresh the tools in new second, it is quite ok. in a very busy system I usually only run once and will get all information I need.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial