|
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. 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. 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. @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). 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 :) @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. More comments hidden
|
|
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. 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. |