SQL Server DBA

Powershell Get-EventLog and Event Log messages

2012-02-25T00:37:25-08:00

This post explains how to list Event Log Messages with Powershell Get-EventLog , on multiple servers and output the results to a HTML file.

It is only one extra step to send an email attachment.

This example iterates through a list of servers and returns Error messages from the System Event Log generated in the last 24 hrs

$isodate=Get-Date -format s 
$isodate=$isodate -replace(":","")
$basepath=(Get-Location -PSProvider FileSystem).ProviderPath


$serverpath=$basepath + "\config\servers.txt"
$outputfile="\logs\sql_server_health_eventviewer_" + $isodate + ".html"
$outputfilefull = $basepath + $outputfile


#invoke stylesheet
. .\modules\stylesheet.ps1

#intro smtp function 
. .\modules\smtp.ps1


$dt = new-object "System.Data.DataTable"
foreach ($server in get-content $serverpath)
{

	Clear-Host
	$ServerCurrent = "$server"
	$dt = Get-Eventlog -computername $server -logname system -EntryType Error -After $(Get-Date).AddHours(-24) | sort eventid |`

}
$dt | select * -ExcludeProperty Data,Category,Index,Category,CategoryNumber,ReplacementStrings,CategoryNumber,Site,SiteContainer,TimeWritten,Container,InstanceId,UserName | ConvertTo-Html -head $reportstyle -body "Server System Errors on sqlserver-dba.com Servers" | Set-Content $outputfilefull

SQL Server - DBA Team FAQ

2012-02-24T03:36:49-08:00

Developers constantly ask about placing the latest code\feature\process\third party applications onto Production Database Servers.

I’m building an FAQ to supply Developers – as this could save me having very similar conversations .

Some other reasons for retaining a process is :

a) Maintain database standards such as ISO-11179 Naming Conventions and SQL DDL and

b) Server standards such as Should libraries be installed on database servers ?

c) Maximsing SQL Server system redundancy and high availability

1) I want to set up a new database server Instance or a database within an existing Instance .

Create a helpdesk request , outlining requirements. A member of the DBA Team will supply you with documentation. The documentation will request information regarding: Management requirements,Capacity Planning , Database Engine,Security, SSIS,SSAS,SSRS and Master Data Services.

A meeting will be organized to complete the requirements documentation

2) I want to make a change on existing code?

Create a change request . Add a member of the DBA team as an approver, who’ll review the code and advise on impact

3) Can I place objects , such as Stored Procedures , with .Net code on a Production Database Server?

Yes, but certain conditions must be met. As the DBA team we’d like :

a document specifying the functionality
Predicted execution frequency and duration of the code
Justify on a technical level why integrating into the database server is the best approach
Some details on alternatives – and the impact on business usage and maintenance overhead.
What is the CPU and memory overhead of .Net code usage
Who supports the code ?
A brief conversation discussing the above

4) Can we integrate other SQL Server features such as Reporting Services, Analysis Services or SSIS?

Yes. There are already implementations of these features on Production Servers. Some of the key questions to discuss are : a) is this a third party vendor requirement – if so , is there internal support if troubleshooting is required.

b) If you’re planning on implementing SSIS for migrating data , outline the workflow and impact on other resources

Request a meeting with a DBA team member to discuss options

5) Can I create a SQL server Agent Job to run regular jobs?

Yes. Keep in mind , generally database servers only host SQL servers. There would have to be a strong reason to process jobs on the database server. Justification is required.

Are Logs set up?
Is documentation available to describe the workflow?
In case of Disaster Recovery , do these job need to keep operating?

SQL Server – Powershell Active Directory search

2012-02-20T08:13:30-08:00

Working with Powershell and Active Directory simplifies some complex tasks for the DBA.

Active Directory is LDAP compliant. This means the RFC 1779 and RFC 2247 standards are met.

This example lists all employees on an LDAP path. This method requires knowledge of the LDAP path .

Common Name (cn)

Organizational Unit (ou)

Domain Component (dc)

$group = [ADSI] "LDAP://cn=MYORGAllEmployees,ou=MYORG,dc=south,dc=syborg,dc=net" 
foreach ($member in $group.member) 
{ 
$member 
}

SQL Server – read only database status

2012-02-17T02:36:10-08:00

This post describes how to return the database read-only status via T-SQL .

The system view – sys.databases returns a column called is_read_only.

The column returns either 1 or 0

1 = read only

0 = read write

   SELECT [name] ,is_read_only  
   FROM sys.databases  
   ORDER BY [name]

SQL Server – DBA productivity and less is more

2012-02-15T07:29:42-08:00

A typical DBA day can include a mixture of Operational, Engineering and Architectural tasks. Whilst maintaining 100% database server availability and acceptable performance levels.

I’m always looking for method to focus my efforts. Automation, documentation , reporting, monitoring are all ways of simplifying the mountains of information. S

Inspired by the minimalist guide to database administration

My current Zen List of DBA productivity

1) Check work email 3 times a day. With the exception of critical systems monitoring emails – which need immediate attention. Remember: if the issue is important enough , people will find you e.g IM & mobile phone

2) When checking email – take an action on every email : archive, delete , add to task list, delegate

3) If I estimate the action to take less than 1 minute – commit the action

4) Commit to one task at a time.

5) Focus completely on that task. Leads to less mistakes and attention to the details.

6) Aim to do less tasks – with greater concentration . Focus on solutions – documenting – reusing

7) Morning ritual : check failed backups, jobs, critical errors in logs. Don’t let problems stack up.

8) Focus on necessary tasks. Think about what is most important to fulfil the job. As a Production DBA , it could be : availability.

SQL Server DBA

Powershell Get-EventLog and Event Log messages

See Also

SQL Server - DBA Team FAQ

1) I want to set up a new database server Instance or a database within an existing Instance .

2) I want to make a change on existing code?

3) Can I place objects , such as Stored Procedures , with .Net code on a Production Database Server?

4) Can we integrate other SQL Server features such as Reporting Services, Analysis Services or SSIS?

5) Can I create a SQL server Agent Job to run regular jobs?

See also

SQL Server – Powershell Active Directory search

See Also

SQL Server – read only database status

See Also

SQL Server – DBA productivity and less is more

See Also