Tag Archives | Developers

Will the Cloud Eat My DBA Job? (Dear SQL DBA Episode 31)

Will cloud services render DBAs obsolete? Does the cloud mean that developers will use less of SQL Server? In this post I talk about the future of database administration and give tips on strategizing your career.

Watch this week’s 28 minute episode, or scroll down to read a written version of the answer. Us YouTubers love it when you subscribe to our YouTube channels. You can also get this by podcast (and I would love it if you left a review on iTunes).

Dear SQL DBA…

I moved into the role of DBA about a year ago and still have a lot to learn, however I’m worried…

  • Cloud adoption may move more and more companies away from SQL Server (due to cost, for instance)
  • As the transition to the cloud becomes more commonplace the DBA job will become a service included in that offering

I responded with a few questions of my own:

  1. What made you decide to be a SQL Server DBA, specifically?
  2. What do you like about being a DBA? What parts of your job do you wish you could do all the time?
  3. Are you curious about cloud technologies at all? Or do they seem uninteresting to you?

We’re talking about career strategy here. It’s important to reflect a little on how you got to the point you’re at now, and what you enjoy doing and what you’re interested in in this context.

We’ll get back to how those questions were answered soon.

Lots of things have been reported to kill the DBA over the years

SQL Server 2005 was said to be “self-tuning”! Who needs a DBA when the instance tunes itself? (Apparently everyone.)

Outsourcing: All the DBA jobs are going to X location, then Y location, then Z location. Then back to X. DBA jobs have become more global, but “outsourcing” hasn’t gotten rid of DBA jobs in the United States. It has been part of the trend to make working remotely more normal and easy, which is generally good for DBAs.

DevOps! All the developers will manage everything. And somehow know to do so.  I love Dev Ops, and I have seen it wipe out some QA departments, but I haven’t seen it wipe out DBAs. I think it’s fun to be a DBA working with a Dev Ops team.

One job is going away… slowly

“Old School Databass Administrator”

Duties:

  • Installs SQL Server
  • Sets up and monitors jobs for backups, CHECKDB, and index maintenance
  • Manages user permissions
  • Stays in cube, silently

I don’t think the cloud specifically is killing off this role. IT teams are being demanded to communicate better, pay more attention to their customers, be more dynamic and specialized.

Who’s a DBA’s #1 customer?

Developers wanna develop

The cloud enables developers to use a lot of technologies.

But that doesn’t necessarily mean less SQL Server will be used.

The cloud is also bringing new ways for SQL Server to be cost effective – like elastic pools.

Developers have a massive challenge: there’s an incredible amount for them to learn.

Microsoft has been changing

It’s not just cloud — it’s things like SQL Server on Linux in vNext of SQL Server.

Yeah, you can spin up a Linux VM in the cloud and run SQL Server on it. But you can also do it on prem. Or in a container. You can do it anywhere. That’s really cool.

Microsoft is also embracing lots of technology in their cloud. You can bring data from multiple data sources together– including SQL Server.

Don’t get threatened by the fact that it’s not all SQL Server. If customers can do more and build more, and if there’s many ways to include SQL Server in that, it’s going to get used. It has tons of cool features for performance, and it’s a proven relational database with a really solid foundation.

Developers are still gonna need help

  • “This looks weird, what’s happening in the database?”
  • “Why is this slow?”
  • “What can we do to make queries faster without changing the code?”
  • “Is there a way to do this more cheaply?”

And also…

  • “Can we roll our own solution?”

The complexity developers face is only expanding. For those teams who choose SQL Server, there’s plenty of need for specialists.

Business owners will still need help

They have their own questions:

  • “What are the developers not seeing?”
  • “Are we meeting our SLAs, RPOs, RTOs and other acronyms?”
  • “Is our data secure?”

And also…

  • “Should we be in the cloud, or running our own?”

Remember how once upon a time, virtualization was going to completely take over everything? It definitely did become very common, but there were still plenty of companies who kept their SQL Servers using physical hardware for performance reasons. There are plenty of others who virtualized, then reverted to physical as well. And now there’s a trend toward the new hotness of containers.

As the options increase, the need for a SQL Server DBA who’s aware of the pros and cons of each just goes up.

DBA Specializations

Sysadmin / Platform

  • Scripting and managing large private implementations
  • High Availability and Disaster Recovery
  • Storage specializations

Performance tuning / architecture

  • Performance tuning critical workloads
  • Specialized code design patterns for high transaction workloads
  • Index tuning and new technologies

Cloud is a part of both of these. Many companies will use the cloud for failover. Many will come to use it primarily. Even then, which cloud do you use? Which way do you deploy to best suit your cost requirements and service level agreements? How do you manage it as it evolves?

Operations can be its own specialization

I used to work with two men who came from the “punch-card” days. When I met them they ran operations for a set of proprietary Hadoop-like clusters for a dot com. They were doing NOSQL before I ever heard it called NOSQL.

They were really good at operations. They adapted to new things to manage, and they did it very well.

That made me realize that as long as I’m good at solving a problem for a business with technology, I’m going to be able to find a job. I just need to keep adapting as the technology evolves.

Business intelligence is another example

The rise of self-serve data sounded like it was going to put BI folks out of business at the beginning.

After all, PowerPivot used “The Engine of the Devil!”

Who needs to build cubes anymore if people can just do everything they need in an Excel spreadsheet that references the cloud?

This customer wants their BI team more than ever

BI consultants now

I don’t work in Business Intelligence, but people seem to be really productive, happy, and active in that field.

More options on how to get things done resulted in more work, and more demand for interesting data to work with from business users. People want to figure out how to make their data meaningful in new ways.

What if you’re just starting out?

If you are…

  • Fresh in the industry, no experience
  • Someone who loves working with data

Should you focus on just Microsoft SQL Server?

Well, no, but that has nothing to do with SQL Server, or even the cloud specifically. Don’t settle down too fast. Why marry one technology before even getting to know others?

If you’re in this position, it’s an amazing time to experiment with all the technologies out there. This definitely includes cloud technology, but give yourself freedom to explore and try as many new things as you can get your hands on.

Our questioner isn’t in that position

Here’s how they answered my questions…

1.What made you decide to be a SQL Server DBA?

  • Accidental DBA, support background

2.What do you like about being a DBA?

  • Inner workings of SQL Server, flagship technologies. (NOT writing reports.)

3.Are you curious about cloud technologies?

  • Yes

The cloud is part of your job security

Get to know different options to deploy SQL Server to the cloud.

  • Strengths
  • Weaknesses / limitations

Ask questions around these areas…

  • Monitoring
  • HA/DR
  • How to support it / what maintenance is required

Cloud is a great reason to get to go to conferences! You need to provide the curiosity and justification for attending the training. Ask, and keep asking. When you do get to go to training, take the initiative to schedule time and share what you learned with your team. (It really helps when it comes time to ask to go again.)

Will the robots take my job?

Robots may take EVERYONE’s job. I can’t change that.

However, I think as database people, we’re probably near the end of the list of jobs that will be replaced by Skynet. The robots need us to serve them until they are fully self-sustaining, and no longer need humans at all.

But for the foreseeable future, there’s tons of work for data professionals to answer the question…

“How do we get the most out of all this stuff?”

My #1 advice: Look for problems you want to solve at work, and go after them

Don’t get stuck on a job title. I did this at one point in my career, and it was a huge mistake. The job title “DBA” may disappear, or you might find you like a job with a different title.

That’s fine. It’s not like “administrator” is a sexy term.

You get one life. You may as well get to solve problems that you enjoy while you work. That’s way more important than how the HR department tries to classify you.

Got a question for Dear SQL DBA?

Ask away at LittleKendra.com/dearsqldba!

How to Script Out Indexes from SQL Server

snapshot-indexes-littlekendra.com

Sometimes you need to script out all the indexes in a database.

Maybe you’re concerned something has changed since they were last checked in.

Or maybe the indexes aren’t checked into source control, and you’re working on fixing that. (Important!)

Either way, sometimes you need to do it, and it’s not fun through the GUI. I needed to write some fresh demo code for this recently, and I needed it to give the details for partitioned tables using data compression, and I thought I’d share.

Enjoy! If you’ve got ideas about ways to improve this, I’d love to hear them in the comments.

TSQL For Scripting Out All Indexes in a Database

This will script out:

  • Clustered and nonclustered indexes (including filtered indexes)
  • The filegroup the index is created on (if not partitioned)
  • The partition scheme the index is created on (if partitioned)
  • Compression settings if the index is compressed– and it’s per partition if the table is partitioned
  • For tables that don’t have a clustered index, they will still get a row in the results with a comment indicating it is a heap

The code is available in a gist (for easy copying and reference), and is also below.

SELECT
DB_NAME() AS database_name,
sc.name + N'.' + t.name AS table_name,
(SELECT MAX(user_reads)
FROM (VALUES (last_user_seek), (last_user_scan), (last_user_lookup)) AS value(user_reads)) AS last_user_read,
last_user_update,
CASE si.index_id WHEN 0 THEN N'/* No create statement (Heap) */'
ELSE
CASE is_primary_key WHEN 1 THEN
N'ALTER TABLE ' + QUOTENAME(sc.name) + N'.' + QUOTENAME(t.name) + N' ADD CONSTRAINT ' + QUOTENAME(si.name) + N' PRIMARY KEY ' +
CASE WHEN si.index_id > 1 THEN N'NON' ELSE N'' END + N'CLUSTERED '
ELSE N'CREATE ' +
CASE WHEN si.is_unique = 1 then N'UNIQUE ' ELSE N'' END +
CASE WHEN si.index_id > 1 THEN N'NON' ELSE N'' END + N'CLUSTERED ' +
N'INDEX ' + QUOTENAME(si.name) + N' ON ' + QUOTENAME(sc.name) + N'.' + QUOTENAME(t.name) + N' '
END +
/* key def */ N'(' + key_definition + N')' +
/* includes */ CASE WHEN include_definition IS NOT NULL THEN
N' INCLUDE (' + include_definition + N')'
ELSE N''
END +
/* filters */ CASE WHEN filter_definition IS NOT NULL THEN
N' WHERE ' + filter_definition ELSE N''
END +
/* with clause - compression goes here */
CASE WHEN row_compression_partition_list IS NOT NULL OR page_compression_partition_list IS NOT NULL
THEN N' WITH (' +
CASE WHEN row_compression_partition_list IS NOT NULL THEN
N'DATA_COMPRESSION = ROW ' + CASE WHEN psc.name IS NULL THEN N'' ELSE + N' ON PARTITIONS (' + row_compression_partition_list + N')' END
ELSE N'' END +
CASE WHEN row_compression_partition_list IS NOT NULL AND page_compression_partition_list IS NOT NULL THEN N', ' ELSE N'' END +
CASE WHEN page_compression_partition_list IS NOT NULL THEN
N'DATA_COMPRESSION = PAGE ' + CASE WHEN psc.name IS NULL THEN N'' ELSE + N' ON PARTITIONS (' + page_compression_partition_list + N')' END
ELSE N'' END
+ N')'
ELSE N''
END +
/* ON where? filegroup? partition scheme? */
' ON ' + CASE WHEN psc.name is null
THEN ISNULL(QUOTENAME(fg.name),N'')
ELSE psc.name + N' (' + partitioning_column.column_name + N')'
END
+ N';'
END AS index_create_statement,
si.index_id,
si.name AS index_name,
partition_sums.reserved_in_row_GB,
partition_sums.reserved_LOB_GB,
partition_sums.row_count,
stat.user_seeks,
stat.user_scans,
stat.user_lookups,
user_updates AS queries_that_modified,
partition_sums.partition_count,
si.allow_page_locks,
si.allow_row_locks,
si.is_hypothetical,
si.has_filter,
si.fill_factor,
si.is_unique,
ISNULL(pf.name, '/* Not partitioned */') AS partition_function,
ISNULL(psc.name, fg.name) AS partition_scheme_or_filegroup,
t.create_date AS table_created_date,
t.modify_date AS table_modify_date
FROM sys.indexes AS si
JOIN sys.tables AS t ON si.object_id=t.object_id
JOIN sys.schemas AS sc ON t.schema_id=sc.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS stat ON
stat.database_id = DB_ID()
and si.object_id=stat.object_id
and si.index_id=stat.index_id
LEFT JOIN sys.partition_schemes AS psc ON si.data_space_id=psc.data_space_id
LEFT JOIN sys.partition_functions AS pf ON psc.function_id=pf.function_id
LEFT JOIN sys.filegroups AS fg ON si.data_space_id=fg.data_space_id
/* Key list */ OUTER APPLY ( SELECT STUFF (
(SELECT N', ' + QUOTENAME(c.name) +
CASE ic.is_descending_key WHEN 1 then N' DESC' ELSE N'' END
FROM sys.index_columns AS ic
JOIN sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.key_ordinal > 0
ORDER BY ic.key_ordinal FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS keys ( key_definition )
/* Partitioning Ordinal */ OUTER APPLY (
SELECT MAX(QUOTENAME(c.name)) AS column_name
FROM sys.index_columns AS ic
JOIN sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.partition_ordinal = 1) AS partitioning_column
/* Include list */ OUTER APPLY ( SELECT STUFF (
(SELECT N', ' + QUOTENAME(c.name)
FROM sys.index_columns AS ic
JOIN sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.is_included_column = 1
ORDER BY c.name FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS includes ( include_definition )
/* Partitions */ OUTER APPLY (
SELECT
COUNT(*) AS partition_count,
CAST(SUM(ps.in_row_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_in_row_GB,
CAST(SUM(ps.lob_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_LOB_GB,
SUM(ps.row_count) AS row_count
FROM sys.partitions AS p
JOIN sys.dm_db_partition_stats AS ps ON
p.partition_id=ps.partition_id
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
) AS partition_sums
/* row compression list by partition */ OUTER APPLY ( SELECT STUFF (
(SELECT N', ' + CAST(p.partition_number AS VARCHAR(32))
FROM sys.partitions AS p
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
and p.data_compression = 1
ORDER BY p.partition_number FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS row_compression_clause ( row_compression_partition_list )
/* data compression list by partition */ OUTER APPLY ( SELECT STUFF (
(SELECT N', ' + CAST(p.partition_number AS VARCHAR(32))
FROM sys.partitions AS p
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
and p.data_compression = 2
ORDER BY p.partition_number FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS page_compression_clause ( page_compression_partition_list )
WHERE
si.type IN (0,1,2) /* heap, clustered, nonclustered */
ORDER BY table_name, si.index_id
OPTION (RECOMPILE);
GO