Dec 31 2008 4:12PM GMT
Posted by: Colin Smith
So in this economy I thing that just about everyone has seen someone close to them, hopefully not you, be laid off from work. I know that at my employeer we have had a couple rounds of layoffs and I have been very lucky. this last round one person from our Systems team was laid off and he was an asset to the company. He was a Unix Admin but also had skills working with Sybase Databases and other third party tools such as Veritas.
When I was in my training class, the instructor told us that a DB Admin is a good spot during these times. He mentioned that there is a shortage of ASE Administrators on the West Coast. That is very good for us in the DB Game. I have also seen that here in Arizona, Database Admins are some of the highest paid and most needed people in IT at this time. Good news for all of us out west but it is still a scary time. So please do what you can to keep your job and start polishing your skills as well as learning new skills. Good luck out there and I am knowcking on wood for myselfe as well.
Dec 26 2008 6:15PM GMT
Posted by: Colin Smith
Database,
Sybase
Sybase DBCC or Database Consistency Checks. This was something that was talked about in my training class and so I thought I would implement this when I got back to work. I mentioned it to my team and they both said that I sounded just like them when they got back from class and they laughed a bit but said go ahead and see what happens. They told me that I would get a ton of errors that were meaningless and that to find out that they were meaningless errors I would have to call Sybase Support. They made it sound like it was more hassle then it was worth but I figured that I might want to do it any way just to get the experience and just what if I did find an error. I could be the Hero and I l love to be the Hero when I can.
I requested a couple devices from our Unix guys for the DBCC Database. I did an sp_plan_dbccdb and determined that I needed 5 gigs of space for data and only about 20 MB for log but I figured I would do 1 Gig for log. I got the devices and I created the DBCCDB and I ran the installdbcc script and boom I have DBCCDB and I am ready to go. I did also set up a named cache and bound it to DBCCDB just to make the DBCC commands run a bit faster. I also made sure that my number of worker process was sufficient for the task.
After getting everything set up I did it, I ran dbcc checkstorage on my DB and guess what. 0 Errors were found. Nothing at all to worry about and not even one spurious error that my teammates said I would have tons of. I am pretty sure that they have not run dbcc checkstorage for a couple releases and in 12.5.4 they added some error checking into the dbcc that makes it report very few if any spurious errors. Since this is the case I am going to regularly schedule this and perhaps one day it will really save us.
The dbcc does take about an hour to run on my DBA server that we load from Production once a week. I will continue to run he dbcc on this server so that I will not hinder the production server at all. Sybase does say that dbcc checkstorage is very un-intrusive but I would rather not give the application team any reason to blame the Database or my team for the application running slow. Based on this I would recommend that you run this as often ass possible as it may save you from getting in big trouble at some point. I hope to never see this report any errors but if it does I will be happy to catch it early. If you have any questions or comments please let me know here or go to www.sysadminsmith.com and send me an email.
Dec 26 2008 6:14PM GMT
Posted by: Colin Smith
Database,
Sybase
Sybase DBCC or Database Consistency Checks. This was something that was talked about in my training class and so I thought I would implement this when I got back to work. I mentioned it to my team and they both said that I sounded just like them when they got back from class and they laughed a bit but said go ahead and see what happens. They told me that I would get a ton of errors that were meaningless and that to find out that they were meaningless errors I would have to call Sybase Support. They made it sound like it was more hassle then it was worth but I figured that I might want to do it any way just to get the experience and just what if I did find an error. I could be the Hero and I l love to be the Hero when I can.
I requested a couple devices from our Unix guys for the DBCC Database. I did an sp_plan_dbccdb and determined that I needed 5 gigs of space for data and only about 20 MB for log but I figured I would do 1 Gig for log. I got the devices and I created the DBCCDB and I ran the installdbcc script and boom I have DBCCDB and I am ready to go. I did also set up a named cache and bound it to DBCCDB just to make the DBCC commands run a bit faster. I also made sure that my number of worker process was sufficient for the task.
After getting everything set up I did it, I ran dbcc checkstorage on my DB and guess what. 0 Errors were found. Nothing at all to worry about and not even one spurious error that my teammates said I would have tons of. I am pretty sure that they have not run dbcc checkstorage for a couple releases and in 12.5.4 they added some error checking into the dbcc that makes it report very few if any spurious errors. Since this is the case I am going to regularly schedule this and perhaps one day it will really save us.
The dbcc does take about an hour to run on my DBA server that we load from Production once a week. I will continue to run he dbcc on this server so that I will not hinder the production server at all. Sybase does say that dbcc checkstorage is very un-intrusive but I would rather not give the application team any reason to blame the Database or my team for the application running slow. Based on this I would recommend that you run this as often ass possible as it may save you from getting in big trouble at some point. I hope to never see this report any errors but if it does I will be happy to catch it early. If you have any questions or comments please let me know here or go to www.sysadminsmith.com and send me an email.
Dec 26 2008 5:59PM GMT
Posted by: Colin Smith
Database,
Sybase
Sorry that I have not done this entry yet. I have been busy with the holidays and my wife just graduated from ASU. I am back now and should be getting back to a more regular schedule. So I have touched on the first 4 days of training and it was a five day class. The last day of class was very short but we did cover some good stuff. One thing in the book that we did not cover was Sybase Central. We did not cover this because it is a GUI and the instructor, as well as myself and the other student, figured that we can learn to click on our own. It does look like the book has some good information about Sybase Central so I will go over it myself and write a post about it.
The main topic on the last day was about monitoring the system. We talked about some third party applications that are around that will do this by monitoring some “Fake” tables that Sybase keeps only in memory just for monitoring purposes, and we also discussed monitoring the old fashioned way. My company does this the old fashioned way by monitoring the alert log and backup logs for any errors or events that we may want to know about. We also have functionality built in to monitor other files if we choose to. Essentially this works buy parsing the alert log and the backup log files at some interval and looking for key words. When a keyword is found the system then sends us an alert that something is wrong and we need to look into it. I am still pretty new to the Unix world so I will not be sharing the scripts with you as I do not know everything about them. I can tell you how to do this in the Windows world using Windows Powershell. I have a series of blog posts here on how to monitor SQL server using Powershell and I am essentially doing the same thing that we do in the Unix environment. Some things are a bit different but for the most part it is the same.
The big thing about this is that you need to have some sort of automated system set up to alert you when something is not correct in the database. If you do not do this then you will not know when things are going wrong until a user calls and tells you, and that may be too late, or you will spend your entire day watching the alert log and the backup log looking for errors. That is not a task that I would wish on my worst enemy. Be a good DBA and make your life easier, set up some monitoring and get some important things done during your day.
No matter what my title or what my job is, I will always work harder on making my job easier then I actually work on doing my job. In the Windows world I would script as much as I could. Sometimes the script writing would take me days to complete even though the task itself only took 30 minutes to do manually. I tell you what though, the next time I needed to do that task and I was done in 30 seconds it was well worth it. So please set up some monitoring and some auto responces to certain errors if you can. That way you do not even get a page. Life is good if you make your job as simple as possible. Good luck with this and if you need any help just let me know by leaving a comment or head over to www.sysadminsmith.com and send me an email. I am always happy to help when I can.
Dec 10 2008 10:26PM GMT
Posted by: Colin Smith
Database,
Sybase
Day 4 of training was really all about backup and restore. This was a good class and I learned a lot about why my company does some of the things that they do. I knew how to do a Database dump and a Database load, but I did learn a lot and found a couple things to be very interesting.
First, I did learn that it is easy to backup all of the DDL for everything in the database and that it is a good idea to do so. This way if disaster strikes you can quickly get everything back up and running. Even if you have backups of the databases you might not have the ability to quickly get all of you disks re inited and done in the correct order. If you have to rebuild master then you have to get all the devices bult with the original vdevno #. I do not know about most of you but I do not specify the Vdevno # when I do a disk init. Sybase automatically finds the lowest available vdevno # and assigns it to the device. If you do not have the original DDL for this then you may have a hard time getting this correct. So you can use Sybase Central or other third party tools to extract the ddl of objects, but a cooler faster way is to use ddlgen on the server host and output this to a file or set of files that you will keep someplace safe just in case.
Second, I learned that compression is pretty awesome. You can specify the level of compression that you want to use when backing up a database. The levels aare 0-9 and 9 is the most compressed. We found though, that you do not really gain much after the first level of compression. The first level will dramatically reduce the backup file size. Anything after that may not be worth the extra work that has to be done by the server. The difference between the first level of compression and the last is not that much.
Third is that backups are very light on the actual database server. Since backups use the backup server to do the backup you can do the backup on-line and users will most likely not even know that you are doing a backup.
Fourth is that you should do a dbcc check of the database to make sure that you do not have any sort of problem before doing the backup. Do this as much as you can anyway. This will help prevent a problem from going unnoticed for a long time, if this happens then you might not have a backup without the error and that can get you into a world of hurt.
And of course we talked about how important it is to do regular backups of your databases. If you want to be able to recover to the most recent point in time then you will want to do transaction log dumps as well. Just do them often and try to check your dbs using dbcc’s as often as possible as well.
Once again if you have any questions or need anything just let me know.
Dec 10 2008 10:12PM GMT
Posted by: Colin Smith
Database,
Sybase
My company is in the process of testing Sybase 15 with one of our homegrown apps. Since I am the new guy on the DBA team this project got dumped on me. This is OK by me since I think it is a great opportunity to learn some new things and I have. Anyway, I went through and found out everything that I needed to know in order to get the DB server installed and get all the disk devices setup and created the DBS for load. Once I did that I loaded the Databases and watched as ASE upgraded the Databases from 12.5 to 15. Everything seemed to go great. The only problem that I really ran into here was that I had to drop all encryption and set that up again. Other then that everything went great. I handed the server over to the developers and said it is all yours start testing. So they did just that. A couple weeks into the testing, with no major issue, I get an email saying that they have a stored proc that runs in a few minutes on 12.5 and never completes in 15.0. I and my team looked into this and the developers did as well. We could not really figure out what was going on. We say that the optimizer was doing things differently in 15 then in 12.5 but were not sure why. We opened a case with Sybase and after a couple weeks of communications and sending them data the level one support person agreed that we had a problem, he could replicate the issue, and was flying this up to engineering. A week later we finally hear back and they say that engineering can not duplicate the issue and that we do not have a issue. Let me assure you that we still had an issue. By this time I had gone to my training class and I had mentioned this to the instructor. He kinda laughed about it but I am sure that he would have helped out more if I asked. Instructor at class was very good and very knowledgeable. Anyway a day or two later I get an email from one of our contract developers saying that he has a solution. WOW this is great news and the solution is actually pretty simple. This should work for anyone having a similar issue. I am not sure that this is always the best solution but all he did was force the 12.5 plan on the 15.0 optimizer. I mentioned this to the instructor and he agreed that in this case this was a good solution. So anyone having a similar issue this should get you running until Sybase can help you find another solution if in fact another solution exists.
Thanks and please let me know if you have any questions.
Dec 10 2008 12:16AM GMT
Posted by: Colin Smith
Database,
Sybase
In day 3 we covered another 5 sections so once again I am just going to go over what I found to be the most interesting.
I would say that I have two things in day 3 that I found to be very cool. First is permissions and how they work in Sybase. So first you have to have a login to the server. Once that is created you should be able to log on to the server but that does not mean that you can do anything yet. Next you have to have a user set up in the database or databases that you would like to work in. For instance if you wanted to log in and work in the Pubs2 Database you have to have a login to the server but also a user in the pubs2 database. Now that you have that you can say ‘use pubs2′ and get into the database. This still does not mean that you can do anything in the pubs2 database. now we get to object level security and your user has to have permisions to objects in the database in order to view and or manipulate thos objects. I love this layered approach to security and think that it is a great way do to things. This gives the DBA so many ways to accomplish what is needed.
The other thing on day 3 that I found to be just great was the bulk copy utility. This tool gives the DBA the ability to copy data into or out of a table. This means that I can copy all the contents of a table into a file and then using that same file I can copy that data into a different table if I need to. The one draw back to this is that BCP is a minimally logged event so you will need to enable the db option and then after you are done with the bcp operation you will want to run a full database dump because this operation will invalidate your transaction logs since Sybase does not fully log this type of event.
That is a very broad overview of day 3. If you have any questions about anything more specific please let me know.
Dec 5 2008 10:03PM GMT
Posted by: Colin Smith
Sybase
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.
