on 2010 Jun 18 10:08 PM
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
And that's why we all love SQL Anywhere!
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 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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.)
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
66 | |
10 | |
10 | |
10 | |
10 | |
8 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.