Can anyone recommend best practices for maintaining a SQL Anywhere server and databases? I've searched quite a bit but have come up empty handed. For MS SQL Server, I plethora of suggestions can be found, but for SQL Anywhere, I can't find anything. With SQL Server, I would be running different varitions of DBCC and rebuilding indexes on a routine basis. What types of regular tasks would be good to run on SQL Anywhere to ward off inefficiencies and corruption?

asked 18 Jun '10, 21:08

Jeff%20Hewgley's gravatar image

Jeff Hewgley
511
accept rate: 0%

edited 19 Jun '10, 19:35

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674


One of the primary goals behind SQL Anywhere is to be self-administering and to not require a DBA to manage the database. As such, if the SA development team is achieving this goal then there really shouldn't be a lot of things that you should be required to do on a regular basis. I.e. Unlike other popular RDBMSes, SA tries to take care of itself.

The one thing that should be done is to put into place a well designed backup and recover strategy. There is lots of material on how to do this in the SA documentation - look under Backup and Recovery - and in various whitepapers that you can get online on the Sybase iAnywhere web site. E.g. See http://www.sybase.com/detail?id=47877 . Make sure that you test your backup and recovery procedure to make sure it works!

Having said the above, there have been some rare cases where some users have found that for their particular application needs that they have needed to add specific tasks to keep their database running at its top performance. Each of these cases are tuned to the specific needs of their application and as such it is difficult to describe any general practices. On top of that, the SA development team have used these cases as input to improve the SQL Anywhere server in ways that make these specific tasks no longer required once the user upgrades to the next version of SA.

permanent link

answered 19 Jun '10, 03:10

Mark%20Culp's gravatar image

Mark Culp
23.3k9132274
accept rate: 40%

3

And that's why we all love SQL Anywhere!

(19 Jun '10, 07:19) Vincent Buck
4

The one thing I would add here (though it is contained in the document Mark points to) is database validation. I guess most folks using SQL Anywhere run the DBVALID tool or the according SQL statements regularly - usually as part of the backup plan.

(19 Jun '10, 19:34) Volker Barth

Twice, permissions have "stopped working" on db objects. The solution was to remove a group's priveleges from the object and then apply the identical permissions again. In another db we are unable to Drop a table... SQLA hangs and we have to restart the service. The same thing happens when we try to unload data from the table. Previous versions of our databases have the same problems. I guess we will have to create new db, recreate all objects, and copy the data from one db to the other. In the future, it sounds like we need to start using dbvalid with our backup procedures.

(22 Jun '10, 18:18) Jeff Hewgley

@Jeff: Have you tried to solve those issues with the help of support or the newsgroups? Are the underlying problems identified? - I can't comment on those issues as I have not run into them but I have often get valuable help by asking in the newsgroups (or here).

(25 Jun '10, 09:31) Volker Barth

We have no clue as to what the underlying problems could be. We have run dbvalid on the databases and it reports nothing. What newsgroups are you referring to? I have found it very difficult to find resources for SQLA online so anything you can point me to would be appreciated :)

(28 Jun '10, 15:00) Jeff Hewgley

@Jeff: Have a look at the "links" link - the button is immediately left of the search box. The main newsgroup (and the one for those cases) is "sybase.public.sqlanywhere.general". Some folks (like me) would agree with you that the newsgroups are not easy to find. Once found, they are very helpful IMHO.

(28 Jun '10, 20:19) Volker Barth
More comments hidden
showing 5 of 6 show all flat view

I won't be claim to be an expert, but having used SQL Anywhere since 1995, here are the 3 things I have found useful: 1. For safety: Tested backup and recovery: our databases (we support 30 sites) are in use 24/7, so we use dbbackup to make twice-a-day hot backups, which then go to tape, and are stored off-site. In one of the daily backup jobs, we run dbValid as Volker noted. 2. For routine Performance: we expand our databases every year to create empty pages (alter dbspace system add xxmb) and then run defrag on the disk to get rid of file fragmentation. 3. For specific performance issues noted by users: we: a. use Breck Carter's performance monitor in Foxhound (see www.risingroad.com) since it seems simpler to me than the also excellent tool that ships with SQLA and correct any big issues. b. we use the Explain Plan and Index Consultant features of ISQL and/or Sybase Central to correct any big time killing queries.

Other than that, we spend our time on other more productive activities and depend on the database to just keep on running and running and running...

Bill

permanent link

answered 20 Jun '10, 20:27

Bill%20Aumen's gravatar image

Bill Aumen
2.1k334674
accept rate: 16%

2

Yes, ALTER DBSPACE is something we use now and then, too. And for (online) file fragmentation, we recommend to use the CONTIG tool from SysInternals when running on Windows. (I guess it is mentioned a fet times on this site, too.)

(20 Jun '10, 21:32) Volker Barth

Thanks Bill. I added a comment on Mark's answer which applies to you as well. I would rather be spending my time on more productive activities too :)

(22 Jun '10, 18:19) Jeff Hewgley

One additional thing to add:

I would recommend to check for EBFs frequently, cf. the Downloads page. I typically read the readmes of new EBFs to find out if there might be fixes that could affect our systems. BTW: It's easy to get noted for new EBFs by the according mail service.

Lots of folks seem to use only the GA versions of SQL Anywhere and never update to an EBF. That comes to a surprise for me, as even the SQL Anywhere software contains errors that have to be fixed now and then (but I'm much better at coding bugs:).

I guess different shops use different strategies to update EBFs - how often they do updates, if they try to use the "newest EBF" possible or older, more proven ones, how much testing is engaged and the like. - Personally, we run every EBF of interest in a test environment before production use.

However, I would generally not recommend to ignore the existence of EBFs completely.

permanent link

answered 25 Jun '10, 09:44

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674
accept rate: 33%

That's a good point. We do have a couple of EBF's in place, but I will check to see if any new one's are available.

(28 Jun '10, 15:02) Jeff Hewgley
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×23
×9
×6

question asked: 18 Jun '10, 21:08

question was seen: 2,079 times

last updated: 25 Jun '10, 09:44