Four Areas to Focus on When You Start A New DBA Role

You’ve just been hired into a DBA role at a new company, or you’ve been given the DBA keys at your current company. Maybe you’re a SysAdmin and your boss has informed you that you are now supposed to manage the SQL Servers as well as everything else on your plate. In any of these situations, you may have some confidence in your skills, but especially in the case of being a new hire, you have absolutely no true idea of what you’re walking into.

In these scenarios, where do you start? Start with these four areas.

1. Building an environment inventory
2. Backups
3. SQL Agent Job Notifications
4. Security

1. Build an environment Inventory: You can’t manage what you don’t know about.

This is your first focus are because you can’t manage what you don’t know about.

The process of gathering information about your environment tells you about the make up of versions and editions in your environment. This, in turn, leads to the answer to the question, “What SQL Server’s are out of support and likely need to be upgraded?”

“How many SQL Servers are there at company X?”

Sure, the hiring manager told you the company only has a dozen SQL Servers, but I’ve often found that hiring managers often don’t know the real answer to the question, “So, how many SQL Servers do you have at company x?” That’s a question you should always ask when interviewing by the way. When you hear the answer, I’d encourage you to add at least 50% to that answer. At one job, I was told there were about a dozen SQL Servers. Less than 6 months in I had located about 50 SQL Servers, and that was before I ran the MAP Toolkit.

If you are not familiar with the MAP Toolkit, when you run the utility it does a search of your network or Active Directory to find SQL Servers. You will want to ensure that your security team and/or System Administrators know you are going to run this utility. This is because it will likely set off security software or even be blocked, in some cases.

The tool produces an Excel format list of plenty of information for you to know and this is the simplest way to start creating a SQL Server inventory. This scan is likely to find a lot of SQL Servers that no one seems to remember existing. Managing all the SQL Servers within your purview is important. You can’t do that if you don’t know about them.

You will also probably find individual employees who have installed SQL Server Standard or even Enterprise Edition on their local PCs. That’s a licensing no-no. This situation gives you the chance to help your company avoid licensing entanglements with Microsoft by addressing these scenarios.

If you find that you aren’t allowed to use the MAP Toolkit, as you hear about other SQL Servers that exist in your environment, or as you discover them for yourself as you look through Active Directory Users and Computers, make a Central Management Server and add SQL Servers to it. Ensure you have access to those SQL Servers and then query them to find out all you can about them. Review the information available to you from SERVERPROPERTY and build your own queries to discover information about the company SQL Servers. Here is an example query from the MS Docs link above. As you discover information about the company SQL Servers, create your own spreadsheet of information, or create a database and tables to hold the information you find.

SELECT  
  SERVERPROPERTY('MachineName') AS ComputerName,
  SERVERPROPERTY('ServerName') AS InstanceName,  
  SERVERPROPERTY('Edition') AS Edition,
  SERVERPROPERTY('ProductVersion') AS ProductVersion,  
  SERVERPROPERTY('ProductLevel') AS ProductLevel;  
GO

2. SQL Server backups: You have to be able to recover the data.

My experience has been that many companies aren’t doing an adequate job of managing the backup routines for their databases. I’ve been in companies where two or even three products were running backups on the same SQL Servers. I’ve been in situations where important databases weren’t backed up frequently enough and with the right set of backup types to recover appropriately, which would result in losing more data than the management was comfortable with. I’ve also seen scenarios where there were no backups at all.

So, what do you do to tackle the issue of SQL Server backups in your environment? First, connect to your SQL Servers and run the below query. This will give you the database name and the date of the last full backup if the last full backup was over 7 days ago . If the database has never been backed up, then you’ll get that information as well. Any results from this query should be investigated immediately. This query was adapted from sp_Blitz with some column names changed to make it more clear what it does.

SELECT  d.[name] AS DatabaseName ,
COALESCE(CAST(MAX(b.backup_finish_date) AS VARCHAR(25)),'No Backup Ever Made') AS LastFullBackup
FROM master.sys.databases AS D
LEFT OUTER JOIN msdb.dbo.backupset AS B ON D.name COLLATE SQL_Latin1_General_CP1_CI_AS = B.database_name COLLATE SQL_Latin1_General_CP1_CI_AS

AND B.type = 'D' /*Full backup*/
AND B.server_name = SERVERPROPERTY('ServerName') /*Backupset ran on server you're currently connected to. */

WHERE D.[name]<> 'tempdb'  /* Eliminate TempDB. No need to back that up */
AND D.state_desc NOT IN ('RESTORING', 'OFFLINE', 'OFFLINE_SECONDARY') /* Exclude databases that are offline or involved in log shipping, for example */
AND D.is_in_standby = 0 /* Exclude databases in stand-by state as part of log shipping*/
AND D.source_database_id IS NULL /* Excludes database snapshots */

GROUP BY d.name
HAVING MAX(B.backup_finish_date) <= GETDATE()-7 /*Full backup older than 7 days ago.*/
OR MAX(B.backup_finish_date) IS NULL;										    

This one will show you any databases in the Full recovery model that haven’t had a transaction log backup in over 24 hours. This query was adapted from sp_Blitz with some column names changed to make it more clear what it does.

SELECT  d.[name] AS DatabaseName ,
COALESCE(CAST(MAX(b.backup_finish_date) AS VARCHAR(25)),'No Backup Ever Made') AS LastLogBackup
FROM master.sys.databases AS D
LEFT OUTER JOIN msdb.dbo.backupset AS B ON D.name COLLATE SQL_Latin1_General_CP1_CI_AS = B.database_name COLLATE SQL_Latin1_General_CP1_CI_AS

AND B.type = 'L' /*Log backup*/
AND B.server_name = SERVERPROPERTY('ServerName') /*Backupset ran on server you're currnetly connected to. */

WHERE D.[name]<> 'tempdb'  /* Eliminate TempDB. No need to back that up */
AND D.state_desc NOT IN ('RESTORING', 'OFFLINE', 'OFFLINE_SECONDARY') /* Exclude databases that are offline or involved in log shipping, for example */
AND D.is_in_standby = 0 /* Database is read-only to restore a log backup as in Log Shipping. */
AND D.source_database_id IS NULL /* Excludes database snapshots */
AND D.recovery_model_desc = 'Full'
GROUP BY d.name
HAVING MAX(B.backup_finish_date) <= GETDATE()-1 /*Log backup older than 1 day ago.*/
OR MAX(B.backup_finish_date) IS NULL;										    

Review the SQL Server to see if there are any native backup methods in place, such as a Maintenance Plan or Ola Hallengren scripts.  Each of these backup methods will produce one or more SQL Server Agent jobs. Check to see if the jobs associated with those options are enabled.

If you don’t see either of those options set up, your company may be using a 3rd party backup software. These are typically, but not always, deployed by System Administrators. Check with them to see what 3rd party software is in place for backups and let them know your findings for the server in question. This could be a situation where this SQL Server was just not enrolled in the 3rd party software and so is not being backed up.

If none of these things turn up a backup solution, then get one in place ASAP. See this post for options on how to create backups.

3. SQL Agent Job Notifications: If a job is worth creating, it’s worth knowing if it failed.

This has always been an easy win in every job I’ve taken. In every new role I’ve had, I have discovered many SQL Agent jobs with no notifications or notifications set up to wrong people. If a job is worth creating, it’s worth knowing if it failed.

As you work through finding failed jobs that never notified anyone, you may find that you’re fixing important agent jobs that should have been working. This process fixes business and data related processes and also helps you find stake-holders for data processes. When jobs fail and you investigate, you often need to know who is impacted by the job failure. This further leads to identifying who should be notified if it fails. This process then, actually helps connect you to key players in the organization very early. Those people on the business side almost always appreciate your efforts to make sure that data processes that affect them are working properly. This gives you allies as you move forward in your role.

So how do you proceed if you find jobs that have no notification set up if they should fail?

• First, locate jobs that are failing. I have a post that will show you two ways to do that. You can either look at the SQL Agent Job Activity Monitor on a SQL instance or query the msdb database.
• Second, locate enabled jobs that have no email operators assigned. These will be jobs that no one will know whether they are working or not. This post will tell you about how to find these jobs.
• Third, work through a process to make sure jobs with no notification set up will have an email operator assigned to them. This will ensure the right “someone” will know if the job fails.

4. Security: Who has access to SQL Server and what type of access do they have?

I generally focus on this one after the others because security is often a political subject within an organization. You don’t want to become embroiled in power struggles right away by trying to take away someone’s access, because you will lose. Additionally, if you’ve done the previous work mentioned, you have likely built some good business relationships and a few allies to help you have the discussion about SQL Server access and security.

As a DBA, you and the business need to know who has SysAdmin level access. The access that they possess allows their credentials to make any change they want to. In truth, it’s often not so much about trusting the person who has the access as it is realizing that every person who has SA permission is a person whom a hacker could exploit to gain access to the company’s data. You will want to make the business aware of how many credentials have this level of permission and whose credentials they are.

How do you find who has SysAdmin level access on your SQL Server? Here is a query for that from MS Docs. I have added a WHERE clause to exclude the commonly found SQL Server accounts that start with NT SERVICE.

SELECT	roles.principal_id AS RolePrincipalID,	
roles.name AS RolePrincipalName,	server_role_members.member_principal_id	AS MemberPrincipalID,	
members.name AS MemberPrincipalName

FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.server_principals AS members 
    ON server_role_members.member_principal_id = members.principal_id
WHERE members.name NOT LIKE 'NT SERVICE%';

This query shows you what credentials have Server level SA access. You also will want to know what accounts have the db_owner role at the database level.

I would strongly encourage you to review the permissions scripts written and maintained by Kenneth Fisher. These will provide a wealth of information about your server level logins and your database level users.

Next Steps To Take

1. Check out the First Responder Kit on Github.
2. If you have a comment or question about this post specifically, leave a comment and I’ll get back to you.
3. If you would like help with something else related to SQL Server, reach out to me on Twitter, and I’ll be glad to offer assistance.