In this article, we will discuss the SQL Server administration best practices. The database administrators must know the security basics, log, and data file management, TempDB management, SQL server documentation, changing the auto-growth default settings, the importance of regular backups, and how they can be done, etc. Let’s explore one by one.
Database security
What you need to have fundamentally is a trusted DB. You can give the permissions to manage the SQL services only to trusted people. This is critical as you do not want to have all the people around snooping into accessing crucial corporate data. It is also suggested to encrypt all the data backups and allow only authorized users to access them. Ransomware is so popular nowadays that you have to keep your data backups in a secured and hidden location where everyone cannot access the data. You can also keep it on a shared drive instead of fixed drives and access only the trusted DBAs.
Considering authentication while connecting to the SQL Server
You should use windows authentication instead of the default system admin account. Adding to it, you may also try not to use the SA account in order to connect to the web applications on the SQL servers. Also, do not share your system admin user account information with anyone else. System administration access should be restricted only for fellow DBAs. If someone insists on getting the DBA permissions, inform them that they can take over the server administration and be in charge of backing up the corporate data. They will back out on their own. Also, make sure to change the passwords every now and then audit the file logins regularly to identify any suspicious activities. It is also important to change the passwords often, which, even if a whole lot of work, is fully worth doing so from time to time.
Data management and log management
Ensure that you have a dedicated drive for the installation of SQL Server. It is ideal to have MDF or LDF log files. However, you cannot store them on different partitions, as each one of these needs a dedicated physical drive. This is one important piece of advice you should follow. If you tend to put them on the same physical disk, then it may run only slower than expected. This is because the writes on the disk may occur at the same time as other writes and reads. This may cause writing log files to the data files, and the process may randomly go back and forth.
If you use an SSD drive instead, then this may not be a big issue. However, most people still tend to use physical drives which run on plates. This is primarily because the SQL Databases may be quite large, and the accessory SSD drives can be expensive. If the databases are not active, then they can be on the same drive. In fact, all this will depend on the number of databases you have and their activity levels.
Shrinking the database files
While shrinking the DB files, experts’ best practice is to avoid shrinking the database files at all. However, you must be able to differentiate between when shrinking can be done at best and when it should be avoided. For example, it is recommended not to shrink data files on the active database as once the shrinkage begins, all the other transactions in the device may be stopped. So, you have to be really careful about what you want to shrink and why you want to do it.
If you plan to shrink files just in order to save some space, then just don’t do it. This is a very bad idea to follow. Instead, you should have a plan for the database to grow. Shrinking may damage the index heavily and may also reverse the order of the clustered index by taking it from fragmented to defragment. This will also hurt the performance of the database. You have to shrink files if you really need to do it. One can also shrink the databases which are not very important, like the non-production DBs.
You may even be trying them if you have to free up some disk space. For example, when you are dealing with a database that is not very important. And you need to have some very simple transaction logs instead of the folder, then it is ideal for shrinking it.Contact RemoteDBA.com for more info.
Temp DB management
In the case of standard databases, the default temp DB size may be set to 8MB with 1MB of the transaction log file. This may be quickly used up, so you have to deal with additional growth soon. So, you can try to set it to grow in larger increments for staff; you have to test and see how much the temp DB is growing. Then you can plan the size accordingly for your staff when it comes to temp DB sizing operations. If it is small, then it may generate a lot of storage. So, we have to be careful with what you are doing.
Documentation of SQL server
You may say that creating and gathering SQL server documentation is very difficult, time-consuming, and boring, but you should remember that this is very important. The major thing every expert database administrator may tell you is that SQL server documentation should be out there. Proper documentation should contain all the details about the SQL servers, list all databases, their sizes, and information about database files, SQL configuration settings, important data about server admins, user privileges, etc. You should also have information about the database set comparisons and specifications of your settings in light of industry best practices.
The database administrators should be mindful of all these aspects if you want to ensure your SQL Server DB’s optimum performance in enterprise database management. There are many standard tools and practices out there to follow these properly. Which you should explore and further learn to bring into practice.
