You are handed a SQL Server that someone else set up and has, or more likely has not, maintained over time. You are now responsible for this server, but have limited time to check it out because of your busy schedule. What are your first 3 SQL Server settings to check?

1. Backups

This should always without a doubt be the first thing you check. If backups are failing or even are non existent, you need to know about it and make it your first priority to fix. Now that this server is your responsibility, the business is going to look at you when the server crashes and they need a restore. Not being able to recover any data is a great way to get fired!

Here is a script you can use to check the backups on your server. When you run this and don’t see any dates, or the dates are from 3 years ago you have a problem. Current dates are a good sign that your backups are succeeding. Even if the dates are current you should still investigate further to understand the backup solution that is in place and how it is meeting your RPO and RTO goals.

SELECT SDB.[name] AS 'DatabaseName', 
    COALESCE(CONVERT(char(10), MAX(BUS.backup_finish_date), 101),'!Never!') AS 'LastBackUpTime'
FROM sys.sysdatabases AS SDB
    LEFT OUTER JOIN msdb.dbo.backupset AS BUS
        ON BUS.[database_name] = SDB.[name]
WHERE SDB.[name] <> 'tempdb'
GROUP BY SDB.[name]
ORDER BY SDB.[name]

If you find the server doesn’t have any backups it’s time to put some in place. So where do you start? Redgate has a great article on backups if you aren’t familiar with the basics. For a backup solution Ola Hallengren has got you covered with his ever popular maintenance solution that is free!

2. Corruption Checks

9 times out of 10 when I check out a new server, corruption checks are not being done. Last known good corruption check is never. If there is corruption on this new server you want to find out about it right away so you can do 2 things. First, you can make everyone aware of the issue, so nobody can blame you for it. If you let it sit for 6 months and then find out the database is corrupted, everyone will have forgotten about that last guy that maintained this server and blame you. Second, you start working on the issue before it gets any worse.

Use the DBCC command below to see when the last corruption check was on your database. Look for the record with value dbi_dbccLastKnownGood in the field column. It will have the date of the last corruption check. If the date is 1900/01/01 then your database has never been checked for corruption.

DBCC DBINFO('YourDatabaseName') WITH TABLERESULTS

My favorite resources for learning about corruption are Steve Stedman’s site and SQLskills. If you don’t know anything about corruption, they have some great resources to get you started. To start checking for corruption regularly  Ola Hallengren has our back again with integrity checks being a part of his free maintenance solution.

3. Security

Knowing the landscape for security on a server is huge. Time and time again, I see servers where everyone has sys admin privileges. If other people have full control over the server you are responsible for, you need to know about it and make others aware of it. Remember you have no way to prevent these people from doing something stupid! With this you also run the risk of their accounts being compromised because they surf the web and open phishing emails with these accounts everyday. It’s only a matter of time before something bad happens! When you put your money in the bank you expect them to tightly control who has access to it right? Your employer expects no less of you with their data! Protecting your users from themselves can be an uphill battle but it is worth it in the end.

Check out this article on SQL Server access control to get some basic information on SQL Server security. You can use this script to identify the accounts and/or groups that have sys admin privledges on your SQL Server.

exec sp_helpsrvrolemember 'sysadmin'