SQl Server Food For Thought
Two things occur to me yesterday that will change my administrative skills in maintaining our server. It's all about power interuption. Here's what happen:
At around 8:10am there was a power interuption. I was confident with our sql server in handling bakup transaction even though there was one module who keeps on updating customers arrears(Utang, unpaid bills). Due to the massive amount of power consumption by our server and network switch the UPS last for just a minute. One thing I did not realize was SQL server memory was set to full power for querrying and login purposes. The result was a major drawback since sql server did not have enough capabilities to backup things well. As soon as the power was up my database morph into suspect mode. I read a lot articles of how to restore it but sadly I never did. The Good part was no one started to encode something on it and I had backups all i did was restore the latest backup and it was up and running. I have learned my lesson and this time the memory was shared 50/50 1GB for sql querry and 1GB for other transaction.
The next one was at around 3:00pm. It was quite lonely as I was bumming around I realize that at the right side of my table was the golden book I bought last 2003 which is: Microsoft SQL Server 2000: Administrative Companion. here's the picture:
The price back then was P2,995.00 the next day it was P3,200.00
I decided to check out some problems regarding how to disect a suspect mode database. As I read it, it was just the same procedure with the sql server built in help like executing the sp_resetstatus and using the ALTER DATABASE syntax. I decided to dig deep scanning all those pages and searching for some important chapters what I found is something more interesting and important. One most important was the filegroups. Sql Server could actually store database and tables on different drives. With that kind of capabilities I have a good change
of boosting some speed. Ill test this soon.
Another cool feature I found was the User Defined Data Type. This feature plays a very important part in standardizing your tables. User Defined Data Type or alias data types, are system data types that have been customized. The simpliest meaning is a customize data type that can be used in assigning records. To give you a better understanding here's some screen shots.
One of the most important aspect of this structure is to standardize. Say you have a field called name and its type is nvarchar(30)(a character with a maximum value of 30) then you have 30 tables where there are 30 names on it and you want to standardize them in order to save space and avoid confusion. Its because sometimes if you have a group of developers they tend not to follow the exact amout of types most especially during rush time or out of the mood situation.
However, the disadvantage of this structure out weighs the advantage in terms of maintenance. Its because as soon as you assign this data type to a table and at the middle of your development changes occur say the field name called name was change from 30 to 60 due to some user's suggestion you have to remove everything then delete the data type and create a new one since the data type doesn't have the ability to
be edited. And you can't assign the customize data type on your stored procedure. Still you have to use the standard values.
Maybe when the sql server 2005 aka yukon have improve features on this.
At around 8:10am there was a power interuption. I was confident with our sql server in handling bakup transaction even though there was one module who keeps on updating customers arrears(Utang, unpaid bills). Due to the massive amount of power consumption by our server and network switch the UPS last for just a minute. One thing I did not realize was SQL server memory was set to full power for querrying and login purposes. The result was a major drawback since sql server did not have enough capabilities to backup things well. As soon as the power was up my database morph into suspect mode. I read a lot articles of how to restore it but sadly I never did. The Good part was no one started to encode something on it and I had backups all i did was restore the latest backup and it was up and running. I have learned my lesson and this time the memory was shared 50/50 1GB for sql querry and 1GB for other transaction.
The next one was at around 3:00pm. It was quite lonely as I was bumming around I realize that at the right side of my table was the golden book I bought last 2003 which is: Microsoft SQL Server 2000: Administrative Companion. here's the picture:
The price back then was P2,995.00 the next day it was P3,200.00
I decided to check out some problems regarding how to disect a suspect mode database. As I read it, it was just the same procedure with the sql server built in help like executing the sp_resetstatus and using the ALTER DATABASE syntax. I decided to dig deep scanning all those pages and searching for some important chapters what I found is something more interesting and important. One most important was the filegroups. Sql Server could actually store database and tables on different drives. With that kind of capabilities I have a good change
of boosting some speed. Ill test this soon.
Another cool feature I found was the User Defined Data Type. This feature plays a very important part in standardizing your tables. User Defined Data Type or alias data types, are system data types that have been customized. The simpliest meaning is a customize data type that can be used in assigning records. To give you a better understanding here's some screen shots.
One of the most important aspect of this structure is to standardize. Say you have a field called name and its type is nvarchar(30)(a character with a maximum value of 30) then you have 30 tables where there are 30 names on it and you want to standardize them in order to save space and avoid confusion. Its because sometimes if you have a group of developers they tend not to follow the exact amout of types most especially during rush time or out of the mood situation.
However, the disadvantage of this structure out weighs the advantage in terms of maintenance. Its because as soon as you assign this data type to a table and at the middle of your development changes occur say the field name called name was change from 30 to 60 due to some user's suggestion you have to remove everything then delete the data type and create a new one since the data type doesn't have the ability to
be edited. And you can't assign the customize data type on your stored procedure. Still you have to use the standard values.
Maybe when the sql server 2005 aka yukon have improve features on this.
Comments