Today I would like to share cautions every DBA/developer should follow as I follows…for sql server 2000 .
1. Take special care of selection your disk system, disk IO is the typical bottleneck found in the db system.
2. Deciding on correct character set is important, the character set cannot be changed easily, changing character set requires exporting all the data out of your databases rebuilding the databases and then reloading all your databases, also sort order.
3. Always make sure that you ha a valid backup of the WINNT system registry before starting any upgrade.
4. Lookout of endless recursion, sql server ha a limited the minimum number of recursion level to 32.
5. If you don’t backup transaction log frequently. You should set truncate log on checkpoint=TRUE. Failure to-do so will result in transaction log file that will continue to grow until it consumes all available disk space or until it reaches to the max. Specified log space.
6. Do not recommend -setting auto close=TRUE for database that are constantly in use. It will create additional overhead associated with opening and closing the database file.
7. Auto shrink=TRUE may degrade performance, the locking associated with reduce the size of the database can load to blocking and consume server resources.
8. When renaming the database watch out the sql statements that explicitly refer to the database name.
9. You cannot detach the database if any of this is TRUE:
– The database is being restore
– The database is in use
– The database contains objects that are published for replication.
10 Be careful when you increase the size of MODEL db it becomes the minimum size of all new databases. Therefore new databases cannot be smaller then model database.
11 Non logged commands are -truncate table/BCP
12 By default SA account is not password protected. After installing sql server it is recommended to immediately change password.
13. Logins are stored in master..syslogin table.
14. Any individual with a valid sql server login regardless of security mode. Can access the databases as a guest user (if guest user exists)