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?
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.
answered 19 Jun '10, 03:10
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...
answered 20 Jun '10, 20:27
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.
answered 25 Jun '10, 09:44