Posted by: Colin Smith
Sybase ASE Database Administration training class day 2 was a busy day and we again covered 5 chapters. We covered Database Devices, Creating Databases, Disk resource strategies, Auto Expansion, and finally the big one TempDB. I am not going to go into too much detail about these as I think you should take the class. Anyway lets star with Devices.
Database Devices are essentially files or chunks of disk in a raw format that ASE will use to store Databases and Logs. This is a pretty basic concept and the biggest thing that came out of this section are the direction and DSYNC db options. If you want to guarantee that your disk writes, in unix and linux, actually make it out to disk then you must turn one of these on. DirectIO is more for Linux and you will most likely see a performance increase in Linux when using DirectIO and not DSYNC.
Creating Databases was next and this is also very simple. This creates the database and the log on the devices that are now set up. This essentially creates a database with no data in it and puts all the needed entries in all the needed system tables for this database.
Disk strategies is also not to difficult. Most of the documentation talks about separating log and data on different physical devices but this is not always easy to do since in most cases you are storing the devices on a RAID or a SAN. Just make sure that you separate log onto a device or set of devices and put data on a separate device or set of devices. Just make sure that you do not mix data and log for any of your databases. This is a bad idea.
Auto Expansion was the next thing on the list and this is a pretty cool feature but I think it requires more work in set up then it is really worth. You can set up thresholds and threshold actions that will see that the space is running low and the system will grow your file or raw device, if possible, and then alter the database on that device. I am of the philosophy that I, as the DBA, want to be in control of my disk and now when it has grown. I do look for thresholds and I am notified of them. At that time I can decide if I need to add a new device. I do not grow my devices they are all the same size. If I need more space then I have to request the device from our Unix admin and then I can init the device and alter the database on that new device.
Finally we talked about TempDB. This is a broad topic and I am only going to mention one thing that I got out of the class that I think will be very useful. Basically we talked about setting up additional TempDB’s and binding users to them. I can see that this may be useful to bind the DBA accounts, SA, to a separate TempDB where users can not touch it. I think that this could be very useful in Test and Dev environments where some process may eat up all of TempDB. This will ensure that the DBA can still log in to the Database and do some things to prevent having to shut the server down to clear this out.
I will continue to post about this training class over the next few days.