SQL Server Database Administrator’s Roadmap

In this article, I will describe the tasks and responsibilities of “SQL Server Database Administrator“.  I will link to articles about how each item can be performed. In this way, I want this article to be a guide for new database administrators.

1)  Operating System Logs and SQL Server logs must be checked daily and intervened if there is an unusual error. You can find details in the article “How to Check SQL Server Logs and Operationg System Logs“.

2)  It should be checked if the Scheduled jobs are working correctly. You should configure the jobs to send e-mail to database administrators when jobs failed. You may want to read the article “How to Send Email After Failed Jobs“.

3)  You should check whether databases are successfully backed up and send it as an email to the dba team if there is a failed backup. To send email from SQL Server you must configure Database Mail. You may want to read the article named “How To Configure Database Mail On SQL Server“

4)  The databases must always be backed up to a different location. Because if the server becomes inaccessible, you should be able to access your backups from a different source and restore to a new environment.

5)  Once a month, you need to test that your backups can be restored. So, you can sure that your backups is healty. For more information about Backup and Restore, read the article “Backup Restore On SQL Server“.

6)  You should check disk free space regulary so that the database can grow up in a healthy way. I recommend to create a SQL Server job to check disk free space regularly. This job may send a mail to the DBAs. You can also use a third party application for this.

7)  You should monitor the system and check database performances throughout the day.(locks, deadlocks, long running queries, disk response times etc) You can use thirdparty dashboard tools for monitoring. There is many free monitoring dashboard tools on github.

• You can read the article “Data Collector, Management Data Warehouse, Perfmon” to monitor your system and create a baseline.
• You can also read the article “How To See Current Queries On SQL Server” in order to follow the current running queries.
• You can also read articles “How To Identify Long Running Queries Using Extended Events” or “How To Identify Long Running Queries Using SQL Server Profiler” to monitor long running queries in the database.
• You can read “How To Monitor SQL Server With Utility Control Point” to monitor CPU and Disk Usage of SQL Server Databases.

8)  Alerts should be created for potential problems and the system should be configured to send mail to database administrators. In order to create the necessary alerts, you can run the script that I specified in my article “SQL Server Best Practices and Configurations that need to be made After Installation“. In addition, if you write alerts in the Search section of our web site, you can also access other articles about alerts.

9)  Database should be created in accordance with the needs. Creating a database is not a right-click. I recommend reading “How To Create a Database On SQL Server” for the correct database design.

10) Before upgrading the production environment, you must test upgrade operation in the test environment. After the upgrade, you should make sure that the applications are working correctly. In addition you should use Database Migration Assistant to check whether the database is suitable for upgrade.

I usually upgrade by installing a new instance. I transfer the databases one by one to the new instance I have installed and perform their tests first. In this way, if a problem occurs in tests, I quickly redirect applications to the database on the old instance. So I minimize the risks. Not everyone has that chance. If you don’t have such a chance, you should create a scenario for yourself  and test it. For detailed information about the new installation, please refer to the article “How To Install SQL Server“.

11) Database servers must be located in a physically secure environment. Some system engineer friends might be angry at what I said. But I had serious problems in virtual environments. Nowadays, technologies such as dockers, kubernetes are famous. But I still think that the database should be located in physical environments. :))

12) Be sure to use HA (High Availaiblity) solutions in your database systems. In this way, you can prevent unexpected server errors, disk errors. In addition, thanks to HA solutions, you can perform upgrade or maintainance operations without interruption. I prefer SQL Server Always On Failover Cluster as HA and SQL Server Always On Availability Groups as DR. For detailed information about HA (High Availaiblity), you can search related articles in our web site by typing High Availability in Search section.

Especially, I recommend reading the article “Difference Between Always On Failover Cluster, Database Mirroring, Always On Availability Group, Replication and Log Shipping“.

13) Database servers and application servers should not be in the same vlan! If they are in the same Vlan, this means that you’ll be bypassing firewall. So it won’t be safe. There must be firewall between application servers and database servers and application servers must be able to access the database servers only from certain ports.

14) The number of sysadmin should be minimized.  You can access the script that returns detailed results about authorizations from the article “How To Check User Privileges in SQL Server“.

15) Users should be authorized only as much as they need. If the application only needs read, write, and execute privileges, the db_owner privilege should not be granted. This is because someone who is granted the db_owner privilege can also back up your database. The user who backs up can crash your system because he does not know how much space is available on which disk. You can read the article named “How To Deny Backup to Database Owners in SQL Server“.

16) For all database users, there must be strong password usage criteria. Strong passwords must be guaranteed with policies on Windows. You can find detailed information in my articles named “Security Policy for SQL Server(secpol.msc)“, “SQL Server Password Policy” and “SQL Server Account Lockout Policy“.

17) Users who have not logged in for a long time must be deleted in contact with the owner. Using Logon Trigger, you can create a job that will transfer users connecting to the sql server to a table. I explained in detail how to do this in the article “SQL Server Trigger Types“.

18) The database server must not be open to the Internet! This is the most basic safety principle.

19) A licensed virus program must be installed on the database server. An exception must be defined in the virus program for paths that contain database files. If you do not define an exception, the performance of your database will be slow and risky.

20) Maintenance should be done periodically. You can read the article “SQL Server Maintenance“. You can also access Maintenance-related articles by writing Maintenance Plan in the search section of our web site.

21) At regular intervals, queries that use the CPU and disk much should be identified and necessary improvements should be performed. You can access related queries from my article named “How To Find Most Expensive Queries in SQL Server“

22) Missing indexes in the system should be checked periodically and created if necessary. See the article “How To Find Missing Indexes in SQL Server“.

23) Unused indexes and tables should be removed in contact with the application developer. You can read the articles “How To Find Unused Indexes in SQL Server” and “How To Find Unused Tables in SQL Server“.

24) Creating Index is not always good. For example, there are 4 columns in the table and SQL Server recommends to create an index for 3 columns. This is already the same thing as recreating the table. Such an index should not be created. In addition, if too many indexes are created, inserts, updates and deletes may slow down. Before creating an index, I recommend reading “Index Concept and Performance Effect On SQL Server” and “Statistic Concept and Performance Effect On SQL Server“.

25) A primary key should be defined in each table and this primary key should be on the most commonly used unique column. If possible it should be an integer colum. Tables without a Primary Key should be identified and reported to the application developer. You can benefit from the articles “What is Primary Key and Foreign Key” and “Differences Between Primary Key and Unique Constraint“.

26) For queries with a high number of execution count, all columns in the Select, Where  or Join can be defined on the index. In this way, the query will reach all the data from the index without accessing the actual data in the table.  This reduce I / O and improves performance. This type of index is called covering index. You should also look at the columns in ORDER BY and GROUP BY. I recommend reading the following articles for details.

“Index Concept and Performance Effect On SQL Server“

“Create Index on the Order By Column(ASC,DESC)“

“Create Index On the GROUP BY Column“

“Create Index On Join Columns“

27) You should be careful while determining the size of the columns in the tables. If you create columns with unnecessary sizes, this will cause unnecessary I / O. Also, appropriate data types will prevent unnecessary growth of the table. See the article “SQL Server Data Types“.

28) Before you can use Query Hint, you need to make sure that the operation you are performing gives the result you want. Because, you force SQL Server to change its normal behavior by using query hint. The query hint can improve performance but also reduce it. Therefore, query hint should not be used without fully testing the results.

Microsoft gives a warning for query hints:

“Because SQL Server typically chooses the best execution plan, use query hints only as a last resort.”

You can find examples of query hints in the following article.

“Query Hints in SQL Server“

29) It is more useful to use Stored Procedure instead of adhoc query.

You can read the article “Sp(Stored Procedure) On SQL Server“

In some cases,  if you are using stored procedures, parameter sniffing occurs. You can read the article “What is Parameter Sniffing” for the solution.

30) When writing SQL code, comments about the content of the code should be written in the code. Thus, everyone can understand what this code do.

31) When using the UNION statement, you should look for duplicate records. Because UNION reduces duplicated lines to a single line. If there are duplicated records in the result set and the application does not need duplicated records, UNION can be used, otherwise UNION ALL will be required. You may want to read the article “UNION and UNION ALL Operators in SQL Server(TSQL)” for details and the use of UNION and UNION ALL.

32) When using the select statement, only the required columns should be selected. Some application developers select all columns(select * from) and this causes to unnecessary I / O and performance loss.

33) Where condition must be added to the Select statement as needed. When the filter is added, only the needed result set will be returned. This prevent unnecessary I / O. You can also improve the performance of the query by adding indexes on the columns in the where condition.

34) Negative expressions such as “<>”, “!=”, “!>”, “!<“, “NOT IN”, “NOT LIKE” should not be used if possible in your queries when using Where condition. Because a query written with these statements can cause table scan instead of using index. For example, you can use LEFT JOIN instead of NOT IN.

35) You must configure SQL Server after installation. You may want to read the article “SQL Server Best Practices and Configurations that need to be made After Installation“.

36) You can define application servers as login so that no password is specified within the application(webconfig). Thus, you can monitor the users other than the application user with audit. You can find the details in the article “Create Server Login(Do not type a password in web config)“.

37) You should definitely monitor the users other than the application user with the audit. You can find the details in the article “How To Create SQL Server Audit“.

38) Each database administrator must know how to connect to the database with a DAC when they cannot connect to SSMS or to the database server. You can find details in the article”DAC(Dedicated Administrator Connection) On SQL Server“.

39) Each database administrator must know how to connect to SQL Server from the cmd command line when necessary. You may be interested in the article”How To Connect To SQL Server From cmd command line“.

40) If you are using Always On Availability Group, you must have an alert system. You may read the article “Always On Availability Group Alert System” to create an alert system for Always On Availability Group.

41) If you do not specify a path when creating a database, your database is created in the default paths that you specify in the installation. You may be interested in the article “How To Change Default Paths of the Data and Log Files“.

42) The Recovery Model of the database plays a critical role in database management. You may want to read “What is Database Recovery Model” and “How To Change the Database Recovery Model“

43) You must find the tables that need partition and create partitions if necessary. You may want to read below articles.

“How To Create Partition On SQL Server“,

“Sliding Window-Switch Partition-Split Range-Merge Range“,

“How To Find Partitioned Tables In The Database On SQL Server“

44) a good database administrator should also be a good sql developer. You can find many articles about TSQL in the link “https://dbtut.com/index.php/category/mssql/tsql/“

Actually, these items are not enough to be a database administrator. But I thought it would be a good road map to start with. After reading the articles here, I recommend you to follow the articles on our web site in order. Almost all articles on our website consist of real scenarios.

If you want to know about SQL Server History and comparison with Other Relational Database Management Systems you may want to read the below article.

“About SQL Server and Comparison with Other Relational Database Management Systems“