January 29, 2017 at 6:47 am
Comments posted to this topic are about the item The New DBA is a Developer
January 29, 2017 at 10:12 am
Great editorial, Steve. Personally, this is not a new concept for me at all. I also started out programming trying to understand how games worked. I was 9 years old. I progressed and eventually got into development. In my role as a DBA, I've said for years that "I approach DBA tasks with the heart of a developer." By this, I mean I try to automate everything.
There was an editorial a while back (sorry, but I don't have the link to it) about the payoff of automating a task versus running a process manually over and over again. Granted, there are one-time tasks that just aren't worth automating, but maybe it's worth automating a check for the situation that caused me to have to write the one-time cleanup script in the first place. It depends on the situation. I find that many things are worth automating.
For some of the "one-time" tasks, I find myself having to do it over again after explaining (again) why something is a bad idea. I figure the reason behind this happens is that either I didn't explain it properly the first time or the individual just didn't care enough to do it right next time.
January 29, 2017 at 4:42 pm
Heh... "The New DBA is a Developer". No, Sir... that's incorrect. If folks are just now coming around to the idea that DBAs need to be "Developers" of the right sort, that explains a whole lot of performance issues, corrupt disk problems, backup/restore problems, and a whole lot of other problems. As you pointed out, any DBA worth their salt has a library of things they bring with them from job to job and, as we both know, they're not just some "simple DML queries".
The trouble is that a lot of people think that a DBA needs to know PowerShell, C# script, etc., etc., in order to do the job correctly or efficiently. Good DBAs have been writing incredibly effective scripts and stored procedures long before either .Net or PowerShell were even a thought. Yes, things like PoSh have made it easier to do certain things but I'm not so sure that's actually a good thing because there are a whole lot of people out there that know how to run a PoSh script that they cribbed from the internet but have no clue what the scripts actually do. That doesn't make them DBAs (although they think they are and claim to be).... that makes them dangerous especially when they do stupid things like centralizing backups for all their servers as a single point of failure using one of the "too cool for school" PoSh scripts that all the "smart guys" rushed to write to impress everyone with their new knowledge of the next shinny object.
Someone should write an article called "The New DBA actually needs to learn what a DBA does and how to do it correctly". 😉 Most of the new ones that I've interviewed in the last decade can't even get the current date and time using T-SQL
Can you tell you hit a point of aggravation for me? .😛
--Jeff Moden
Change is inevitable... Change for the better is not.
January 29, 2017 at 5:03 pm
The need for a modern DBA to know more has nothing to do with quality or work or skill. Those things are important, and I hope that most employees are learning to get better at their jobs.
However, there is a need to learn more and be more of a developer as a DBA. The understanding of version control, of scripted deployments, or managing at scale, these are all valuable skills that a new DBA should have. Those should be useful and effective skills, but they are skills needed.
January 29, 2017 at 6:06 pm
Steve Jones - SSC Editor - Sunday, January 29, 2017 5:03 PMThe need for a modern DBA to know more has nothing to do with quality or work or skill. Those things are important, and I hope that most employees are learning to get better at their jobs.
I'll have to take exception to that, as well, ol' friend. What is the purpose of knowing more if it's not for "quality or work or skill"? And how will you know if what you've learned is useful or dangerous if not for an understanding of "quality or work or skill"?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 30, 2017 at 8:17 am
I started my career as a developer way back in the mid 90's, so it's difficult for me to think of it any other way. Of course at college they mainly had 3 different types of computer degrees, the engineering which was more hardware, the computer science degree for software (programming), and a business computer degree that was much lighter on the technical aspects, so database first wasn't really an option back then, they just had a couple of classes on the subject. I didn't switch to being purely a database person for the money, it was something that just made "more sense" to me and the way I think
I've still utilized my programmer experience through the years to mainly build helper applications for automation or monitoring, and of course scripting in everything from bourne shell, Windows batch / CMD scripts, and now PowerShell, but they've been for me rather than someone else, which I think is a different style of programming. If a real programmer looked at my source code for the DB monitoring program I wrote, they'd probably laugh as it's more just a bunch of data grids with a little VB.Net code who's only purpose is to take queries from a resource file and run them against whichever instance I click on so I can dig down and investigate why a certain performance metric is taking a hit.
January 30, 2017 at 8:25 am
Steve Jones - SSC Editor - Sunday, January 29, 2017 6:47 AMComments posted to this topic are about the item The New DBA is a Developer
This is an interesting article, Steve. As a developer who had to learn DBA tasks when the DBA at my previous job left, I have always thought it as the developer learning DBA skills. This article shows me that some, you and Mike Fal, now see DBA's as moving into the some programming skills. The developer world and the operations world have for a long time been separate. I'm sure its been more of a developer writing something then "throw it over the wall" to operations where they try to deploy and maintain. Now both sides are moving towards each other. I'm sure that middle will only grow in side.
Kindest Regards, Rod Connect with me on LinkedIn.
January 30, 2017 at 8:51 am
Jeff Moden - Sunday, January 29, 2017 6:06 PMSteve Jones - SSC Editor - Sunday, January 29, 2017 5:03 PMThe need for a modern DBA to know more has nothing to do with quality or work or skill. Those things are important, and I hope that most employees are learning to get better at their jobs.I'll have to take exception to that, as well, ol' friend. What is the purpose of knowing more if it's not for "quality or work or skill"? And how will you know if what you've learned is useful or dangerous if not for an understanding of "quality or work or skill"?
You are conflating two orthogonal things. Quality and skill are needed, but they take time. Adding knowledge about development practices, or how to script something is important, but when someone adds this skill, they'll not be skilled or an expert. However, they can gain that skill over time, with work and practice.
I know you bemoan the lack of skills from many candidates, but that is a separate issue from learning more and adding skills to your ability. I know you don't think everyone is completely unskilled, so why wouldn't this be something you'd hope for? Asking someone to get better at their job is fine. That is separate from asking someone that does do a good job to add skills.
January 30, 2017 at 10:21 am
Steve Jones - SSC Editor - Monday, January 30, 2017 8:51 AMJeff Moden - Sunday, January 29, 2017 6:06 PMSteve Jones - SSC Editor - Sunday, January 29, 2017 5:03 PMThe need for a modern DBA to know more has nothing to do with quality or work or skill. Those things are important, and I hope that most employees are learning to get better at their jobs.I'll have to take exception to that, as well, ol' friend. What is the purpose of knowing more if it's not for "quality or work or skill"? And how will you know if what you've learned is useful or dangerous if not for an understanding of "quality or work or skill"?
You are conflating two orthogonal things. Quality and skill are needed, but they take time. Adding knowledge about development practices, or how to script something is important, but when someone adds this skill, they'll not be skilled or an expert. However, they can gain that skill over time, with work and practice.
I know you bemoan the lack of skills from many candidates, but that is a separate issue from learning more and adding skills to your ability. I know you don't think everyone is completely unskilled, so why wouldn't this be something you'd hope for? Asking someone to get better at their job is fine. That is separate from asking someone that does do a good job to add skills.
Hi Steve, very interesting article. We had a DBA once at our organization but since his retirement my employer hasn't shown any interest at getting a replacement. Instead we are directed/encouraged to pickup some DBA knowledge and maintain our own application data. Needless to say we're scrambling around like headless chickens, so you're point is very valid with regards to adding new skills one's existing job. There's no such thing as wasteful knowledge. You mentioned in the article that you have scripts to move data around and perform restore. Do you mind sharing? I'm a novice at best when it comes SQL queries. We have a job setup to take daily backup of the databases and we keep 3 days worth of backup files on the production server. What I'm trying to do is automate and refresh the LATEST production backup to a test environment on a different server. I found the below script online but it kept failing. Any advice on what's missing from the script or do you have any better suggestion on how to automate the restore process from a different server? Thank you in advance for your input.
SET NOCOUNT ON
DECLARE @LinkedServer Varchar(255)
SET@LinkedServer = 'vmsqltest12'
DECLARE @DataFileLocation Varchar(255)
SET @DataFileLocation = 'e:\data'
DECLARE @LogFileLocation Varchar(255)
SET @LogFileLocation = 'D:\Tlogs'
DECLARE @ExcludeDbs Varchar(255)
SET @ExcludeDbs = '(''master'' , ''model'' , ''msdb'', ''tempdb'', ''arch2005'',''audit_c3d'',''RMA_Views'',''SysPsi'')'
DECLARE @RestoreDbs Varchar(255)
SET @RestoreDbs = ''
DECLARE @dbname Varchar(100) ,
@physicalpath Varchar(500) ,
@BackupDate Datetime ,
@cmd nvarchar(max) ,
@logicalname Varchar(255) ,
@PhysicalFileName Varchar(max) ,
@type Varchar(5)
--Checks linked server exists, if not then linked server is added, requires ALTER ANY LINKED SERVER permission.
IF NOT EXISTS (SELECT * FROM SYS.servers Where name = @LinkedServer)
EXEC sp_addlinkedserver @LinkedServer
-- If data file location is not specified then data files will be restored to default data file location.
IF @DataFileLocation IS NULL
SELECT @DataFileLocation = SUBSTRING(physical_name, 1,CHARINDEX(N'master.mdf',LOWER(physical_name)) - 2) FROM master.sys.master_files WHERE database_id = 1 AND FILE_ID = 1
-- If log file location is not specified then log files will be restored to default log file location.
IF @LogFileLocation IS NULL
SELECT @LogFileLocation = SUBSTRING(physical_name, 1,CHARINDEX(N'mastlog.ldf',LOWER(physical_name)) - 2) FROM master.sys.master_files WHERE database_id = 1 AND FILE_ID = 2
CREATE TABLE #filelist (
LogicalName VARCHAR(255),
PhysicalName VARCHAR(500),
[Type] VARCHAR(1),
FileGroupName VARCHAR(64),
Size DECIMAL(20, 0),
MaxSize DECIMAL(25,0),
FileID bigint,
CreateLSN DECIMAL(25,0),
DropLSN DECIMAL(25,0),
UniqueID UNIQUEIDENTIFIER,
ReadOnlyLSN DECIMAL(25,0),
ReadWriteLSN DECIMAL(25,0),
BackupSizeInBytes DECIMAL(25,0),
SourceBlockSize INT,
filegroupid INT,
loggroupguid UNIQUEIDENTIFIER,
differentialbaseLSN DECIMAL(25,0),
differentialbaseGUID UNIQUEIDENTIFIER,
isreadonly BIT,
ispresent BIT ,
TDEThumbprint Varchar(255))
--Queries backupset and backupmediafamily tables on remote msdb database to get latest full backup.
SET @cmd = 'DECLARE restore_db Cursor For SELECT a.database_name , BackupDate , physical_device_name FROM ['+ @LinkedServer+'].msdb.dbo.backupset A ' +
' INNER JOIN (SELECT database_name , BackupDate = MAX(backup_finish_date) ' +
' FROM ['+@LinkedServer+'].msdb.dbo.backupset ' +
' WHERE type = ''D'' '
IF @RestoreDbs IS NULL
SET @cmd = @cmd + ' AND database_name NOT IN '+ @ExcludeDbs +' And backup_finish_date >= DATEADD(MONTH , -1 , GETDATE()) '
ELSE
SET @cmd = @cmd + ' AND database_name IN '+ @RestoreDbs
SET @cmd = @cmd + ' GROUP BY database_name ) as b ' +
' ON A.database_name = b.database_name and a.backup_finish_date = BackupDate ' +
' INNER JOIN ['+ @LinkedServer +'].msdb.dbo.backupmediafamily c ON c.media_set_id = a.media_set_id ORDER BY database_name '
exec sp_executesql @cmd
DECLARE restore_db cursor for
SELECT @dbname , @BackupDate, @physicalpath
OPEN restore_db
FETCH NEXT FROM restore_db INTO @dbname , @BackupDate , @physicalpath
WHILE @@FETCH_STATUS = 0
BEGIN
--Check database to be restored is already there in this server, if yes then just restore with replace.
IF EXISTS (SELECT * FROM sys.databases WHERE name = @dbname)
BEGIN
--Get rid of any existing connections, so that our restore process go smoothly.
DECLARE @kill varchar(8000)
set @kill = 'killing';
SELECT @kill=@kill+'kill '+convert(varchar(5),spid)+';'
FROm master.dbo.sysprocesses
WHERE dbid=db_id(''+ @dbname + '');
IF len(@kill) <> 0
exec sp_executesql @kill;
SET @cmd = 'RESTORE DATABASE [' + @dbname +'] FROM DISK = '''+ @physicalpath +''' WITH STATS = 1 , REPLACE '
Exec sp_executesql @cmd;
END
ELSE
BEGIN
-- If database is not already there then go through the filelist and move to appropriate locations.
SET @cmd = 'RESTORE FILELISTONLY FROM DISK= '''+ @physicalpath +''''
INSERT INTO #filelist
EXEC (@cmd)
SET @cmd = 'RESTORE DATABASE ['+ @dbname +'] FROM DISK = '''+ @physicalpath +''' WITH STATS = 1 , '
DECLARE file_list cursor for
SELECT LogicalName, PhysicalName, Type FROM #filelist ORDER BY type
OPEN file_list
FETCH NEXT FROM file_list into @LogicalName, @PhysicalFileName, @type
WHILE @@fetch_status = 0
BEGIN
-- If it is data file move to data file location.
IF @type = 'D'
SET @cmd = @cmd + ' MOVE ''' + @LogicalName + '''' + ' TO ''' + @DataFileLocation +'\'+ Substring(@PhysicalFileName , LEN(@PhysicalFileName)-CHARINDEX('\' , REVERSE(@PhysicalFileName))+2 , CHARINDEX('\' , REVERSE(@PhysicalFileName))) + ''','
ELSE
-- Log files move to log file location.
SET @cmd = @cmd + ' MOVE ''' + @LogicalName + '''' + ' TO ''' + @LogFileLocation + '\'+ Substring(@PhysicalFileName , LEN(@PhysicalFileName)-CHARINDEX('\' , REVERSE(@PhysicalFileName))+2 , CHARINDEX('\' , REVERSE(@PhysicalFileName))) + ''''
FETCH NEXT FROM file_list into @LogicalName, @PhysicalFileName, @type
END
CLOSE file_list
DEALLOCATE file_list
truncate table #filelist
Exec sp_executesql @cmd
END
FETCH NEXT FROM restore_db INTO @dbname , @BackupDate , @physicalpath
END
CLOSE restore_db
DEALLOCATE restore_db
drop table #filelist
January 30, 2017 at 10:46 am
jhuynh - Monday, January 30, 2017 10:21 AMAny advice on what's missing from the script or do you have any better suggestion on how to automate the restore process from a different server? Thank you in advance for your input.
Please post this in a new thread for your version
January 30, 2017 at 10:46 am
I'd rather work in an organization where the DBA is expected to be a part time developer than I would work with developers who are part time database administrators.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 30, 2017 at 10:59 am
Jeff Moden - Sunday, January 29, 2017 4:42 PM...there are a whole lot of people out there that know how to run a PoSh script that they cribbed from the internet but have no clue what the scripts actually do...
That is a great point. Not limited to DBAs or PoSH though. :crying:
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
January 30, 2017 at 2:11 pm
Jeff Moden - Sunday, January 29, 2017 4:42 PMHeh... "The New DBA is a Developer". No, Sir... that's incorrect. If folks are just now coming around to the idea that DBAs need to be "Developers" of the right sort, that explains a whole lot of performance issues, corrupt disk problems, backup/restore problems, and a whole lot of other problems. As you pointed out, any DBA worth their salt has a library of things they bring with them from job to job and, as we both know, they're not just some "simple DML queries".
The trouble is that a lot of people think that a DBA needs to know PowerShell, C# script, etc., etc., in order to do the job correctly or efficiently. Good DBAs have been writing incredibly effective scripts and stored procedures long before either .Net or PowerShell were even a thought. Yes, things like PoSh have made it easier to do certain things but I'm not so sure that's actually a good thing because there are a whole lot of people out there that know how to run a PoSh script that they cribbed from the internet but have no clue what the scripts actually do. That doesn't make them DBAs (although they think they are and claim to be).... that makes them dangerous especially when they do stupid things like centralizing backups for all their servers as a single point of failure using one of the "too cool for school" PoSh scripts that all the "smart guys" rushed to write to impress everyone with their new knowledge of the next shinny object.Someone should write an article called "The New DBA actually needs to learn what a DBA does and how to do it correctly". 😉 Most of the new ones that I've interviewed in the last decade can't even get the current date and time using T-SQL
Can you tell you hit a point of aggravation for me? .😛
I think Jeff, that you're getting the wrong take-away from both the editorial and the source article. Neither (to me) seem to be preaching that DBAs need to be Developers, but instead that DBAs should take advantage of some of the tools that developers use, such as source control.
The idea (at least, what I think both were going for,) being that by using such tools, the DBA will be able to keep track of why they changed things in a script, be able to quickly go back to a previous version, etc.
Not so much stating that DBAs need (or should) jump to the latest and greatest (or not-so-greatest) "shineys" (PoSh, etc.)
Such tools won't help a bad DBA become a good DBA, that's a whole different kettle of fish.
Frankly, I wouldn't mind source-controlling my various work queries I've ginned up, but where I work, such tools are forbidden on the network...
January 31, 2017 at 5:34 am
jasona.work - Monday, January 30, 2017 2:11 PM[/b]
Am I reading this right? Source control tools are forbidden?
January 31, 2017 at 6:51 am
Maybe it's just me, but I have yet to meet a DBA in person who did not develop something too. There always seems to be areas that these DBA's don't do, for example, I typically find DBA's who will not develop queries for reporting, but they will develop code for ETL systems.
As with most careers, businesses want us to take on more and more so they don't have to hire both a DBA and a SQL Developer. They want it all in one. Unfortunately, this leads to the idea of businesses trying to get a DBA who also can develop in .NET.
Either way, more skills the better.
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply