tools for Sybase.

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

Joe WoodhousePrincipal ConsultantCommented:
That's about two weeks of training courses... :)
Joe WoodhousePrincipal ConsultantCommented:
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!

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:
"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.
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.

Joe WoodhousePrincipal ConsultantCommented:
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 ConsultantCommented:
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 ConsultantCommented:
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.
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.