SQL Server setup and deploying databases are very easy nowadays as Microsoft provides wizard in every of its products to make life so easy for the end user, basically before and after SQL Server installation there are few activities that need to be done by DBA to make sure there will be no hardware bottleneck or configuration issues. base on my experience some of common SQL Server critical issues raise by one or more of below mistakes that has been done by DBA.
Never make these mistakes, there are so many new ones to make!
1- Installing SQL Server without Disk Compatibility Test: Basically, DBA needs to evaluate Disk I/O to make sure that it is compatible and fast enough to place SQL Server databases.
2- Default SQL Server Instance Configuration: DBA leaves the instance configuration by default, always remember that every environment needs its own configuration in term of Memory size, CPU affinity, Security and Network configuration. By configuring SQL Server properly it is possible to increase the performance and security.
3- Use Database Primary Filegroup: DBA needs to migrate all the user tables from primary filegroup into multiple secondary filegroups to separate the user tables from system tables. This activity has few benefits.
4- Place Transaction Log File and Data File at same Disk: DBA needs to place these files separately into different hard disk drive. This needs to be done after few configuration on disk and OS.
5- Shrink Transaction Log: This is the worst activity that a DBA can do to minimize the transaction log file by changing recovery model to Simple, Shrink Log File and change recovery model to full without taking full backup, by this activity transaction log chain is broken and database owner (Application or Business owner) might loose data in case of disaster.
6- Enable Auto_Close on Database: This setting can increase the number of read I/O of disk, and slow down the overall SQL Server performance.
7- Using Default Auto_Growth Configuration: It can cause slowness and transaction log fragmentation in SQL Server environment.
8- Backing Up Database without Verifying: taking database backup base on agreed RTO and RPO is great job that a DBA can do, but in most cases the DBA does not bother to verify and restore the backup in other server. It happened that DBA schedule the backup but the SQL Server could not restore the database backup file, which it was too late to know!
Author: Hamid Jabarpour Fard