I’m surprised by the lack of comment on this subject. Caring for your SQL server can be important for almost any installation. Even when you are purchasing platform as a service features such as Azure SQL, you still need to have care in making sure you don’t have an outage.
Let’s start with the basic first area you must implement when caring for your SQL Server. A disaster recovery plan is essential. Even in Azure SQL with two levels deep real time backups of your database, you can still lose your SQL Server instance and its contents. Yes, it can and has happened. It is not common, but the possibility exists. In short, if your SQL Server Service becomes lost, and you need to move it somewhere else, do you have a plan in place to do so?
The rest of the care topics are related to self-hosted SQL services hosted on your own hardware or Platform as a Service.
Log management is important. When things go wrong, or important events occur on you SQL Server instance, they are written to a log file on disk. You need to review this log frequently to assure nothing bad is happening. One time I reviewed a log and found someone was attempting to hack the SA password on an SQL Server instance.
Scheduled task executions also need to be reviewed. When a scheduled task executes it maintains some history regarding the results of the execution. You should not assume your scheduled tasks always succeed. You need to review the results daily.
You need to establish daily maintenance tasks, and keep them up to date, as the databases hosted on your SQL Server Instance change. Often the maintenance tasks perform against the currently attached databases regardless of name, but you need to be sure yours are covered. A daily CheckDB can work wonders, if run off hours. The same is true with index defragmenting through rebuilding or reorganizing an index. These should be part of your daily maintenance process. Also, include rebuilding of statistics, especially for tables with a high volume of Insert, Update and Delete activity throughout the day.
It is also valuable to have a baseline of performance statistics on your server, and to compare them to the statistics for the last 24 hours. Comparing this daily can show you if your server is beginning to feel stress as utilization increases.
These are some basic things that take little time when caring for a SQL Server Instance. Doing them daily can save you a lot of grief by catching things before they go bad.
Cheers,
Ben