Responsibilities of a database developer

Standard

As a SQL Server DBA I tend to keep my servers as fast as possible and try to find performance issues when they comeup. A had a situation where the responsibilities of a database developer were clear for all parties..

responsibilityA couple of weeks ago I got into a discussion with a software developer stating that the developer didn’t keep it’s end of the bargain.

In last few years we’ve been having performance issues with Microsoft Dynamics 2009 with the calculation of the invoices.
Because the developer couldn’t give a clear solution to the problem, I started my own investigation.

I came to the conclusion that we were missing some indexes due to some standard functionality of Dynamics AX 2009 which includes an extra column in an index called the DATAAREAID. SQL Server doesn’t want that for the query so the query creates a lot of table scans.

There were three queries that caused a lot of I/O which could easily be fixed adding a few indexes. Due to the new indexes created the process went from 8 hours to 1!

This raised a lot of questions from our side because in my opinion creating indexes is a developer’s responsibility.

In my opinion the responisbilities of a database developer are the following:

  • Develop ER diagrams
  • Create tables, views, indexes, stored procedures, triggers etc
  • Create custom functionality
  • Make sure code doesn’t create blocking and deadlocking
  • Develop reports
  • Develop SSIS packages

I’m sure there are a lot more responsibilities for a developer but this is my shortlist.

As it came out, the developer showed no intention to help and take responsibility for the development of the database. The reason for this was because they could only create indexes through the application.

I can understand that companies want to hold on to a certain way of developing but there is a time and place when you should look back and see if you’re still on the right road.
In my opinion a software developer is responsible for the their product from A to Z which includes development on the database level. This is besides their responsibility also their prerogative from which customers are not allowed to make changes to the software without the software developer knowing about it.
In many cases you’re not even allowed to make a change in a database because support or warrenty will void and you’ll be in a pickle when things go wrong.

I’ve worked with a lot of software developers and unfortunately there are companies that don’t take responsibility for the database development and only delivered the code.

In the end we decided to make the neccesary changes to the database and contacted the software developer that we made the changes.
The people from the business were happy and the developer had not made a response.

Get Server Warnings and Errors With Powershell

Standard

I’m a great fan of Powershell especially when it makes my life easier for all day jobs.

So one day I had to check all our servers if an error or a warning occured. I had to supply the server name and followed by the server name a “V” for checked and good, a “X” for checked errors and an “!” if something went wrong.

This had to be placed in an Excel file.

I  created scripts before that had to ouput data to a CSV file so  this was not new for me.

The script can be used as follows:

Get-Eventlog2.ps1 -ServerList 'C:\Temp\Servers.txt' -Output 'C:\temp' -Rows 500

As you can see the script uses three variables:

  1. Serverlist: File with the list of Servers
  2. Output: Output directory where the results can be placed
  3. Rows: The amount of rows from the eventlog that need to be searched

The first parameter is a mandatory parameter pointing to the CSV file with the servers. The format how the servers need to be placed in the CSV file is as follows:

Server, Domain
Server01,domain01.local
Server02,domain01.local

The second parameter is not mandatory and is the output directory. If this parameter is not used the script will output the data to the screen.

The third parameters is the amount of rows that need to be searched through. Eventlogs tend to get pretty big which causes the script to become slow. This is why the amount of rows is given. The script will select the newest records untill the amount of rows is reached.

The script will need to have the ping function script that is also included at the bottom of the article.

The output of the script looks as follows:

Server01,Domain01.local,V
Server02,Domain01.local,!,Could not connect
Server03,Domain01.local,X
# PowerShell script to return errors and events status of a host
#
# Author: Sander Stad
# Version: 1.1 Januari 2011 tested on Powershell v2.0
#
# Changes:
# Version 1.0: 
# 	Initial version worked as it should
# Version 1.1:
# 	Implementation of parameters
# 	Implementation of constants
# 	Error check on parameters
# 	Partially loaded classes
#
# Usage:
#	Get-Eventlog2.ps1 -ServerList 'C:\Temp\Servers.txt' -Output 'C:\temp' -Rows 500

Param(
	[Parameter(position=1,Mandatory = $true )][string] $ServerList,				# List of servers that needs to be checked. Has to a text file!
	[Parameter(position=2,Mandatory = $false )][string] $Output,				# Output directory for the Excell file. If no value is given an Excell sheet will open.
	[Parameter(position=3,Mandatory = $false )][int] $Rows						# Row count to check for
)

Process
{

	# Import the Library with the functionality
	. ./Function_Ping-Host.ps1

	# Clear the screen
	CLS

	# Set global error check
	# Value 1: High error program will stop
	# Value 2: Warning program will continue
	# Value 3: Information about execution
	$ErrorStatus = 0;

	#Set file activity
	# Value 1: Data will be written to screen (Default value)
	# Value 2: Data wil be written to given path
	$FileAction = 0;

	If($Output -eq '')
	{
		Write-Host "INFO: Path for output not given. Data will be written to screen." -ForegroundColor Red -BackgroundColor Green
		$FileAction = 1
		$ErrorStatus = 3
	}
	ElseIf(Test-Path $Output)
	{
		$Output = $Output + '\DailyCheckReport_' + (Get-Date).ToString("yyyyMMddHHmmss") + '.csv'
		Write-Host "INFO: CSV file will be written to $Output." -ForegroundColor Red -BackgroundColor Green
		$FileAction = 2
		$ErrorStatus = 3
	}
	Else
	{
		Write-Host "INFO: Path for output is set. Data will be written to $Output." -ForegroundColor Red -BackgroundColor Green
		$FileAction = 2
		$ErrorStatus = 3
	}
	# Check all the parameters
	If($ServerList -eq '')
	{
		Write-Host "ERROR: Server list not given" -ForegroundColor Red -BackgroundColor Yellow
		$ErrorStatus = 1
	}
	ElseIf(!(Test-Path $ServerList))
	{
		Write-Host "ERROR: Path to serverlist doesn't exist" -ForegroundColor Red -BackgroundColor Yellow
		$ErrorStatus = 1
	}

	If($Rows -eq '')
	{
		$Rows = 500
	}

	$Servers = Import-Csv $ServerList
	#$ExportFile = "C:\temp\sander\"

	$fromDate = (Get-Date).Adddays(-1)

	Foreach ($Server in $Servers)
	{
		# Setup the full path to the server: Servername + Domain
		$fullPath = $Server.Server + "." + $Server.Domain 
		If(Ping-Host $fullPath)
		{
			try{
				$fullPath
				# Get the warnings and errors into variables
				$Errors = Get-EventLog -ComputerName $fullPath -LogName system -Newest $Rows | where {$_.entryType -match "Error"} | where {$_.timegenerated -gt $fromDate}
				$Warnings = Get-EventLog -ComputerName $fullPath -LogName system -Newest $Rows | where {$_.entryType -match "Warning"} | where {$_.timegenerated -gt $fromDate}
				
				# Check of the data needs to be wriiten to screen of to file
				If($FileAction -eq 1)
				{
					If($Errors.Count -ge 1)
					{
						Write-Host "$fullPath`tX"
					}
					Elseif($Warning.Count -ge 1)
					{
						Write-Host "$fullPath`t!"
					}
					Else
					{
						Write-Host "$fullPath`tV"
					}		
				}
				ElseIf($FileAction -eq 2)
				{
					If($Errors.Count -ge 1)
					{
						Write-Output "$fullPath,X" | Out-File -Append -Encoding Unicode -FilePath $Output 
					}
					Elseif($Warning.Count -ge 1)
					{
						Write-Output "$fullPath,!" | Out-File -Append -Encoding Unicode -FilePath $Output 
					}
					Else
					{
						Write-Output "$fullPath,V" | Out-File -Append -Encoding Unicode -FilePath $Output 
					}
				}
			}
			Catch [InvalidOperationException]
			{
				Write-Host "$fullPath: Could not connect to host!" -ForegroundColor Red
				If($FileAction -eq 1)
				{
					Write-Host "$fullPath`t!"
				}
				ElseIf($FileAction -eq 2)
				{
					Write-Output "$fullPath,!,Could not connect" | Out-File -Append -Encoding Unicode -FilePath $Output 
				}
			}
			Catch [IO.IOException]
			{
				Write-Host "$fullPath: Could not connect to host!" -ForegroundColor Red
				If($FileAction -eq 1)
				{
					Write-Host "$fullPath`t!"
				}
				ElseIf($FileAction -eq 2)
				{
					Write-Output "$fullPath,!,Network path not found" | Out-File -Append -Encoding Unicode -FilePath $Output 
				}
			}
		}
		Else
		{
			Write-Host "$fullPath: Host could not be pinged!"
			If($FileAction -eq 1)
			{
				Write-Host "$fullPath`t!"
			}
			ElseIf($FileAction -eq 2)
			{
				Write-Output "$fullPath,!,Host could not be pinged!" | Out-File -Append -Encoding Unicode -FilePath $Output 
			}
		}
	}
}

 

If you have any comments please feel free to post them!