Database Administration

The DBA is dead; long live the DBA!

Been reading some interesting arguments over the future of DBA jobs lately, and as usual, I find a lot of truth somewhere in the middle. Let me try to sum up the positions of the three authors that impressed me the most:

Thomas LaRock – Why I’m Learning Data Science:

Tom kind of kicked off this discussion with his post; the three main takeaways I got from it are:

The traditional role of the DBA is being automated away, right in front of our keyboards.
As computers get better about self-tuning, it will be difficult to justify the expenditure of a dedicated database administrator
Computers are only good at providing answers; humans are good at asking questions.

Brent Ozar – Twitter Posts

Brent responded via twitter to “a couple of emails” stating that “the DBA career has a time bomb”. The three key points I got out of the thread are:

The tools are getting better, but the problems are getting harder.
SQL Server still ships with some legacy baggage that require hands-on experience to adjust; if computers were smart, why hasn’t that been fixed?
Even as the technology gets better, adoption is slow.

Grant Fritchey – There Is A Magic Button, A Rant

Grant took the humorous route, telling the tale of the “Run Really Fast” button that all database administrators know about once they achieve a certain level of competence.

Tools are getting better, but they can’t fix problems with design (both software and hardware).
Automation can reduce drudgery, but design is fundamental.
DBA’s are all secret alchemists.

Where I sit…

All three of these guys are smart folks that I respect a lot, and honestly, I see a lot of truth in what they’re all saying. The tools ARE getting better, and I do believe that automation is going to significantly change a lot of different jobs in IT, including database administrators. The role of a DBA is particularly susceptible to this because of the hybrid nature of the work. There’s elements of design (system and software), development, and operations associated with that role, and the stack is getting increasingly complicated. The Microsoft Data Platform now sits over relational and non-relation data, and encompasses analytics, visualization, reporting, integration services, high availability, disaster recovery, and much more. The stack is getting too complicated for the average DBA to be a master of all of it. However, it’s hard to deny the necessity of expertise, particularly with all of the technical debt associated with a product like SQL Server.

But what if we sliced the stack differently?

The cloud paradigm talks about breaking the computing stack up into various services, each acting as a black box to the level above it; Software as a Service is built on top of a Platform as a Service, which in turn is built on top of an Infrastructure as a Service. As enterprises begin to embrace the cloud, they will reorganize resources along these lines. Why? Because it lays the foundation for consolidating resources where it counts, and allows for future portability. In other words, companies can start at the bottom of the stack, and port their Platform and Software services over to cloud providers without significant alteration of those upper level. Likewise, as technology matures, migrating the Software layer to a new Platform provider will get easier over time (we’re not there yet, but it’s coming).

I would argue that the current role of database administration straddles the line between software and platform; traditional maintenance and server configuration task are part of the Platform layer, and database design are part of the Software layer. The term DBA will mean multiple things to multiple people, depending on where that role sits along this divide. In other words, a DBA that works at the Software layer will tend to focus on questions of database design, data software performance tuning, and architectural issues associated with the ever expanding set of options for databases beyond just the relational db. DBA’s at this layer need to become full-fledged member of the development team, which may eventually lead to a fuzzier distinction between application and database developers. DBA’s at this layer will need to be well-versed in the concepts of multiple data management technologies (and possibly other development technologies). Opportunities should abound here, but diversity should be valued over full-stack mastery of a single product.

DBA’s at the Platform level will change roles as well; they’ll no longer need to be steward of data, or responsible for tuning bad code. Their job will be to make the Platform support contracted levels of performance, and identify and correct resource utilization and configuration issues. Automation will have a huge impact here; Platform DBA’s will be responsible for supporting multiple instances of SQL Server, including support for high availability and disaster recovery. Scripting skills are highly desirable, as is mastery knowledge of specific products. I would expect job opportunities to slow in this area, but experts will still be needed in the future.

In short, I don’t think the role of a DBA is going away, but I do think that it’s going to split. That’s exciting, because it means that people have options. We still need experts, and there will still be opportunities for folks who love data to find meaningful work; their expertise will just become part of a different structure than we’re accustomed to now.

Now, where’d I put that Philosopher’s Stone?

#DevOps: Embrace the Ops

If you’re at all in touch with the DevOps community, you’re probably aware of the GitLabs Incident on 1/31/2017; I won’t spend too much time rehashing it here, but GitLabs has done a great job of being transparent about the issue and their processes to recover. Mike Walsh (Straight Path Solutions) wrote a great blog post about it entitled DevOps: Don’t Forget The Ops, which covers a lot of ground from a database administration perspective. Mike ultimately ends up with three specific action items for DevOps teams:

Plan to Fail (so you don’t)
Verify Backups (focus on restores, not backups)
Secure your environment (from yourself).

I agree with all of these ideas; I think Mike is spot on about the need to Remember the Ops in DevOps. However, I want to go a step further, and encourage DevOps adoptees to Embrace the Ops.

What do I mean by that? Let me start with this; Brent Ozar posted this on Facebook yesterday (the image will take you to the job description):

Now, it’s obvious that GitLabs had a backup strategy (they detailed it in their notes), so I don’t mean to imply that they didn’t expect administrative tasks from their database people, but I do think we can infer that administrative tasks were not prioritized as much as other tasks (high availability, performance tuning, etc.). Again, we know that GitLabs had strategy for backups, so it appears that this is a cultural issue (at least based on this flimsy evidence and the outage). And to some degree, that’s understandable; one of the longest running challenges on the operations side is being labeled as a cost center as opposed to development being viewed as a revenue generator. This perception is pervasive in traditional IT shops, so it’s probable that even Unicorn shops share some of this mentality. Development (new features) makes money; Operations cost money.

However, in a true DevOps model, the focus is on delivering quality services to customer, faster. New features may bring new clients, but reliable service retains clients; both are revenue generating. So while it may add some cost to deliver quality service to customers, cutting corners in operations risks impacting the bottom line. From this perspective, I’m arguing that DevOps shops should not only remember the ops, they should embrace it. The entire value stream of a business service includes people, procedures, and technology split into teams; the fewer the teams per service, the fewer silos. So how do we embrace the ops?

If Ops is part of the Value Stream, then apply consistent Development principles to it. I’ve written before that “we are all developers“, and I believe that; administrators are creative folk, just like application developers. Operations includes backup, monitoring, and validation. We should apply development principles to these operations, like creating reusable scripts, finding opportunities for automating validation, and logging (and investigating) errors with that pipeline. We should use source control for these tools, and treat the operations pipeline like any other continuous integration project (automate your backup, automate your restores, and log inconsistencies).
Include operational improvements as part of the development pipeline. I’m borrowing a lot from Google’s SRE model; SRE is what you get when you treat operations as if it’s a software problem (see point 1 above). However, the SRE model is usually a self-contained bubble within operations; they have their own pipelines for toil reduction. I think if DevOps wants to truly embrace operations, developers need to include toil reduction in the service delivery pipeline. If operations folks have to flip 30 switches to bring an app online, development should make it a priority to reduce that (if possible). It goes back to the fundamental rule for DevOps: communicate. Help each other resolve pain points, and commit to improving everything in the value stream.
Finally, balance risk and experimentation with safety. Gene Kim’s The Phoenix Project provides the Three Ways, and the Third Way is all about creating a culture that rewards risk and experimentation. This is great for developers; try something new, and if it breaks, you can deliver a fix within hours. However, as the GitLabs incident shows, some fixes can’t be delivered, and risk needs to be mitigated by secure data handling processes and procedures. While I’m a big fan of controlled failures (e.g., shutting a server down hard in order to see what the impact is), you don’t do that unless you can test it in a lab first and make sure you have good mitigating option (how do you recover? What error messages do you expect to see? Are you sure your backup systems are working?). Don’t forsake basic safety nets while promoting risk; you want competitive advantages, but you also want to stay in business.

2017-02-03 by Stuart Ainsworth DevOps SQLServerPedia Syndication 2

Geek Sync: #DevOps, the Cloud Paradigm, and the Microsoft Data Platform

I’m pleased to announce that I’ll be presenting a Geek Sync webinar (hosted by Idera) to talk about what I see as the evolution of the DBA in light of movements like DevOps and the Cloud Paradigm. Registration’s free, so please feel free to join on January 25, 2017 at 12:00 EST.

The Future of the DBA: DevOps, the Cloud Paradigm, and the Microsoft Data Platform

We’re on the cusp of exciting times for database development and administration; data storage is set to explode in volume over the next 5 years by as much as 500%. Companies are struggling to manage traditional relational databases and several forms of Big Data, including dark, binary, and streaming data. New theories of development, administration, and data management have matured, but what impact do they have on DBA’s? What are the concepts and skills needed for future career growth? In the (paraphrased) words of Dr. Seuss:

“Oh, the places you’ll go!
You have brains in your head
And SQL Skills to boot
You’ll soar to great heights
On the Data Platform, too”

Join IDERA and Stuart R. Ainsworth as we explore how DevOps and the Cloud Paradigm have developed to address modern software delivery challenges. We’ll also examine how the Microsoft Data Platform provides a framework for career enhancement for SQL Server professionals.

Stuart Ainsworth (MA, MEd) is an IT manager working in financial information security. Over the past 20 years, he’s worked as a research analyst, a report writer, a DBA, a programmer, and a public speaking professor. He’s a chapter leader for AtlantaMDF, the SQL Server user group in Atlanta, as well as a speaker at SQLSaturdays, PASS Summit, code camps, and user groups.

2017-01-10 by Stuart Ainsworth SQLServerPedia Syndication The Social Web 0

#SQLServer – Where does my index live?

Today, I got asked by one of my DBA’s about a recently deployed database that seemed to have a lot of filegroups with only a few tables. He wanted to verify that one of the tables was correctly partition-aligned, as well as learn where all of the indexes for these tables were stored. After a quick search of the Internets, I was able to fashion the following script to help. The script below will find every index on every user table in a database, and then determine if it’s partitioned or not. If it’s partitioned, the scheme name is returned; if not, the filegroup name. The final column provides an XML list of filegroups (because schemes can span multiple filegroups) and file locations (because filegroups can span multiple files).

 WITH C AS ( SELECT ps.data_space_id
, f.name
, d.physical_name
FROM sys.filegroups f
JOIN sys.database_files d ON d.data_space_id = f.data_space_id
JOIN sys.destination_data_spaces dds ON dds.data_space_id = f.data_space_id
JOIN sys.partition_schemes ps ON ps.data_space_id = dds.partition_scheme_id
UNION
SELECT f.data_space_id
, f.name
, d.physical_name
FROM sys.filegroups f
JOIN sys.database_files d ON d.data_space_id = f.data_space_id
)
SELECT [ObjectName] = OBJECT_NAME(i.[object_id])
, [IndexID] = i.[index_id]
, [IndexName] = i.[name]
, [IndexType] = i.[type_desc]
, [Partitioned] = CASE WHEN ps.data_space_id IS NULL THEN 'No'
ELSE 'Yes'
END
, [StorageName] = ISNULL(ps.name, f.name)
, [FileGroupPaths] = CAST(( SELECT name AS "FileGroup"
, physical_name AS "DatabaseFile"
FROM C
WHERE i.data_space_id = c.data_space_id
FOR
XML PATH('')
) AS XML)
FROM [sys].[indexes] i
LEFT JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
LEFT JOIN sys.filegroups f ON f.data_space_id = i.data_space_id
WHERE OBJECTPROPERTY(i.[object_id], 'IsUserTable') = 1
ORDER BY [ObjectName], [IndexName]

2014-08-14 by Stuart Ainsworth Code SQL SQL Server SQLServerPedia Syndication XML 0

Managing a Technical Team: Act Like a Good Developer

This is one of my favorite pieces of advice from my Managing a Technical Team presentation that I’ve been doing at several SQLSaturdays and other conferences: act like a good developer, with a different focus. Most new managers, especially if they’ve been promoted from within (the Best Operator) model don’t know how to improve their management skills. However, if you were to ask managers what makes a good developer, you’ll probably get a series of answers that are similar to the following broad categories:

Good Developers have:

a desire to learn,
a desire to collaborate, and
a desire for efficiency.

I could probably say that this is true for all good employees, but as a former developer, I know that the culture in software development places a lot of focus on these traits; system administrators usually have different focus points. However, all technical managers SHOULD emulate these three traits in order to be effective. Let me explain.

Desire to Learn

Let’s imagine Stacy, a C# developer in your company; by most accounts, she’s successful at her job. She always seems to be up on the latest technology, has great ideas, and always seems to have a new tool in her toolkit. If you ask her how she got started programming, she’d tell you that she picked it up as hobby while in college, and then figured out how to make a career out of it. She’s an active member of her user group, and frequently spends her weekends reading and polishing her craft; while not a workaholic, she does spend a great deal of her personal time improving her skills. She’s on a fast track to managing a team, in part because of her desire to learn.

One day, she gets promoted, and is now managing the development team; she struggles with the corporate culture, the paperwork, laying out a vision, and can’t seem to figure out how to motivate her team to the same level of success that she was achieving as a developer. The problem is that her desire to learn no longer syncs up with her career objectives; Stacy needs to invest her educational energies into learning about management.

Ask a new IT manager what books they’re reading, and typically the response will be either none at all, or a book on the latest technology. We tend to cling to that which is familiar, and if you’ve got a technical background, it’s easy and interesting to try and keep focusing on that background. However, if you’re serious about being a manager, you need to commit to applying the same desire to learn that you had as an employee to learning more about management. Sure, pick up a book on Big Data, but balance it out with a book on Relationship Development. Podcasts? There’s management ones out there that are just as fun as the development ones. Webinars? Boom.

Desire to Collaborate

Bob’s a data architect. Everybody loves Bob, because he really listens to your concerns, and tries to design solutions that meet those concerns; if he’s wrong about something, he’s quick to own up to the mistake, and moves on. He works well with others, acknowledging their contributions and adapting to them. In short, Bob is NOT a jerk; nobody wants to work with a jerk.

Bob gets promoted to a management position, and he too struggles; he’s still hanging out with his former teammates, and is still going to the same conferences. Everybody still likes Bob, but he’s having trouble guiding his team in an effective manner. He hasn’t really built relationships with his new peers (other managers that report to his director), and hasn’t found ways to manage more effectively. He’s collaborating, but with the wrong people.

As a new manager, you should continue to maintain relationships with your directs, but you need to build a relationship with your new team of peers. Understand their visions, and find ways to make your team valuable resources to them. Reach out to other managers at user groups and conferences; build a buddy system of people based on your management path, not just your technical one.

Desire for Efficiency

If you sat down and had a conversation with any development team that was effective and producing results and asked them about their methodology, it wouldn’t be long before they started talking about frameworks. Efficiency in development is derived from reusable patterns and approaches to problems; they’re tough to implement at first, but the long term gain is enormous.

As you’ve probably guessed, there’s management frameworks that can be very effective in a technical environment; investing time in implementing them can yield great efficiencies when faced with making decisions. In my current environment, I use three:

MARS – my own self-rolled approach to system operations; it’s not perfect, but it helps focus efforts.
Kanban – allows me to see what our WIP (Work In Progress) is, and helps queue up items for work
ITIL – we’re just starting to adopt this, but we’re working on isolating Incident Management from root cause analysis, as well as implementing robust change control processes.

The challenge with management frameworks is similar to that of development frameworks: bloat. It’s too easy to get bound up in process and procedures when lighter touches can be used, but in most cases, the efficiency gained by having a repeatable approach to decisions allows you to respond quickly to a changing environment.

Summary

Management is tough, but it’s especially tough if you continue to focus on your technical chops as opposed to your leadership abilities. Act like a good developer, and apply those same basic principles to your team.

2014-08-04 by Stuart Ainsworth Professional Development SQLServerPedia Syndication 0

Determining the Primary Key of a table without knowing it’s name

So, I’ve been trying to find more technical fodder for blog posts lately in order to get in the habit of blogging on a regular basis, so I thought I would explore a few of my higher-ranked answers on StackOverflow and provide a little more detail than that site provides. This is my highest-rated answer (sad, I know, compared to some posters on the site):

Determine a table’s primary key using TSQL

I’d like to determine the primary key of a table using TSQL (stored procedure or system table is fine). Is there such a mechanism in SQL Server (2005 or 2008)?

My answer:

This should get you started:

SELECT *
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
        JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name
    WHERE tc.CONSTRAINT_TYPE = 'Primary Key'

Besides the obvious faux pas of using SELECT * in a query, you’ll note that I used the INFORMATION_SCHEMA views; I try to use these views wherever possible because of the portability factor. In theory, I should be able to apply this exact same query to a MySQL or Oracle database, and get similar results from the system schema.

The added benefit of this query is that it allows you to discover other things about your PRIMARY KEYs, like looking for system named keys:

SELECT  tc.TABLE_CATALOG, tc.TABLE_SCHEMA, tc.TABLE_NAME, ccu.CONSTRAINT_NAME, ccu.COLUMN_NAME
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
        JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name
WHERE   tc.CONSTRAINT_TYPE = 'Primary Key'
    AND ccu.CONSTRAINT_NAME LIKE 'PK%/_/_%' ESCAPE '/'

or finding PRIMARY KEYs which have more than one column defined:

SELECT  tc.TABLE_CATALOG, tc.TABLE_SCHEMA, tc.TABLE_NAME, ccu.CONSTRAINT_NAME
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
        JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name
WHERE   tc.CONSTRAINT_TYPE = 'Primary Key'
GROUP BY tc.TABLE_CATALOG, tc.TABLE_SCHEMA, tc.TABLE_NAME, ccu.CONSTRAINT_NAME
HAVING COUNT(*) > 1

2014-07-23 by Stuart Ainsworth SQL SQL Server SQLServerPedia Syndication 0

Error 574 Upgrading SQL Server 2012 to SP1

This blog post is way overdue (check the dates in the errror log below), but I promised our sysadmin that I would write it, so here it is. Hopefully, it’ll help some of you with this aggravating issue. During an upgrade of our SQL cluster, we ran into the following error as we attempted to upgrade one of the instances:

2014-04-15 23:50:14.45 spid14s     Error: 574, Severity: 16, State: 0.

2014-04-15 23:50:14.45 spid14s     CONFIG statement cannot be used inside a user transaction.

2014-04-15 23:50:14.45 spid14s     Error: 912, Severity: 21, State: 2.

2014-04-15 23:50:14.45 spid14s     Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_upgrade.sql’ encountered error 574, state 0, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

2014-04-15 23:50:14.45 spid14s     Error: 3417, Severity: 21, State: 3.

2014-04-15 23:50:14.45 spid14s     Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

Google wasn’t helpful; there’s apparently lots of potential fixes for this, none of which helped. The closest match we found was that we had some orphaned users in a few databases (not system databases), which we corrected; the upgrade still failed. We eventually had to contact Microsoft support, and work our way up the second level technician. Before I reveal the fix, let me give a little more background on how we got orphaned users.

You see, shortly after we upgraded to SQL 2012 (about a year ago), we did what many companies do; we phased out a service offering. That service offering that we phased out required several database components, including a SQL login associated with users in the database, and several maintenance jobs that were run by SQL Agent. When we phased out the service, those jobs were disabled, but not deleted. Our security policy tracks the last time a login was used; if a login isn’t used within 60 days, it’s disabled. 30 days after that (if no one notices), the login is deleted. Unfortunately, our implementation of this process missed two key steps:

The associated user in each database was not dropped with the login (leaving an orphan), and
any job that was owned by that login was also not dropped or transferred to a sysadmin.

The latter was the key to our particular situation; the upgrade detected an orphaned job even though that job was disabled, and blocked the upgrade from going forward. Using trace flag –T902, we were able to start the server instance and delete the disabled job. We then restarted the server without the trace flag, and the upgrade finished successfully.

Resources:

Find and fix all orphaned users for all databases.

Brent Ozar Unlimited’s sp_blitz will find jobs that are owned by users other than sa.

2014-07-15 by Stuart Ainsworth SQL SQL Server SQLServerPedia Syndication 1

Cleaning up orphaned users in SQL Server

Short blog post here; I’m working with my team to clean up some older SQL Servers, and we’re removing several logins that have been dormant or disabled for some time. Removing the logins is easy, but it leaves orphaned users in several of our databases. I created the script below to cycle through each of the databases on the server and generate a series of DROP USER commands for each database.

DECLARE @t TABLE
    (
      db VARCHAR(100)
    , u VARCHAR(1000)
    , s INT
    )

DECLARE @sql NVARCHAR(4000)
SET @sql = 'USE [?];

SELECT  DB_NAME()
      , dp.name
      , s = CASE WHEN s.Schema_id IS NOT NULL THEN 1
                 ELSE 0
            END
FROM    sys.database_principals AS dp
        LEFT JOIN sys.server_principals AS sp ON dp.sid = sp.sid
        LEFT JOIN sys.schemas s ON s.principal_id = dp.principal_id
WHERE   dp.type IN ( ''U'', ''S'' )
        AND sp.sid IS NULL
        AND dp.authentication_type IN ( 1,  3 )
        AND dp.name <> ''dbo'''

INSERT  INTO @t
        EXEC sp_msforeachdb @sql

SELECT  'USE [' + db + ']; '
        + CASE WHEN s = 1
               THEN 'ALTER AUTHORIZATION ON SCHEMA::[' + u + '] TO dbo; '
               ELSE ''
          END + ' DROP USER [' + u + ']'
FROM    @t

If the script discovers an orphaned user, the output will look something like:

USE [AdventureWorks2012]; DROP USER [olduser]

If that user owns a schema in the database, an ALTER AUTHORIZATION step is added to first transfer the schema to dbo, and then drop the user:

USE [AdventureWorks2012]; ALTER AUTHORIZATION ON SCHEMA::[olduser] TO dbo; DROP USER [olduser]

I’m sure there are other ways to accomplish the same thing, but it works well for this task.

2013-10-02 by Stuart Ainsworth SQL SQL Server SQLServerPedia Syndication 7