Don’t use Cursors in SQL Server

Using cursors in TSQL is like using a paddle to row your car.  It will get you there, but every other way to get there is better.

I was a programmer for a decade before I became a DBA.  I understand the desire to make Turing machines out of everything you can get your hands or keyboard attached to.

Techies who do not understand SQL tend to try to turn it into a procedural programming language.  The desire to create familiar programming structures can be overwhelming, but you must resist it when it comes to cursors and TSQL.

Next time you think you need to make a cursor to plod through a set of data please spend a few moment making a temp table and putting your data there for sorting and adjusting.  Then update the existing data from your temp table.

Database engines are built to manipulate data in relational databases.  Let them do what they do using TSQL.  Put away the paddle and try using the steering wheel instead.

How many hats do you wear at work?

I have a hat rack in my office, I am a People Manager, Technical Manager, Project Manger, Business Analyst, Programmer, Database Administrator, Data Modeler, System Administrator, System’s Architect, and occasional furniture mover.  I am sure I have forgotten a hat or two, they are probably stuck under another hat.

How do I wear all of those hats?  One at a time and very carefully.  I am very lucky to be in a job that allows me to be a “hybrid” IT professional.  I have had jobs that were only one hat, Programmer, or Database Administrator.  I found those to be restrictive.  I am not sure that I could go back to just having one job to do each day.  I am never bored, and I have a to do list that my successors will still be working to clear, regardless of when I retire.

How do you handle the hat juggle?

SQL Server Integration Monitoring Made easy

Companies spend millions creating monitoring solutions to solve a problem that a mildly competent server admin can do with a handful of scripts.  I am a big fan of cool Graphical interfaces, pie charts and monitor applications on my iPhone, but if you have no budget for that.  Here is a simple trick I use to monitor database integrations.

This one runs on SQL Server 2012.  Setting up email to work is beyond the scope of what I am talking about her and covered much better by MSDN articles.    This will get you to 90% maybe 98% coverage, which is better than 0%.  If you want 99.999999% get ready to open the wallet and bring in the consultants.

Using SQL Server to Monitor Integration operation:

If you have an integration that is supposed to keep two databases in sync, and you want to be sure that integration is working I use something like this:

< Begin SQL Monitor script>

if 0 < (

— Some query that returns a non-zero result on a problem condition.
Select count(ID) from database1.dbo.table1
where ID not in (Select id from database2.dbo.table2)

begin
print ‘we have a problem’
exec msdb.dbo.sp_send_dbmail
@profile_name = ‘Mail’,
@recipients = YourName@Domain.com’,
@body = ‘The Integration appears to be lagging behind.

If you see this message repeatedly something is wrong on the Integration Server.

Take appropriate action immediately.’,
@subject = ‘Integration Out of Sync Warning!!!’ ;
end
else
begin
print ‘all good’
end

<End SQL Monitoring Script> 

 

This script checks the two databases to make sure they are in sync, if they get out of sync beyond a certain time range an email is sent.

Service Monitoring for Windows 2008 Servers Made Easy – Thanks PowerShell

For cheap and easy monitoring of servers a few assumptions are made.  The biggest assumption is that all of the servers do not go down at once.  If they do, it is probably a data center issue and that should get noticed by someone.  If your entire data center can go down without being noticed this Power Shell script can’t help.  You have other issues to solve.

I run this script in a task checking regularly on a heavily monitored production server.  That server is in a major data center and monitored by other monitoring systems.  In a sort of a buddy check system key servers monitor each other from different data centers.

This was originally developed to make sure a critical service for a Document Management System was running.  Over the years it has been used for other services, but the idea remains the same.  You can check to see if anything is running on another server if the user doing the checking has permission to do so.  In this case the Service account running the server applications has interactive permissions on both servers.


 

<Begin Power Shell Source>

# This is a service monitor script for:
# Server: ImportantServer
# Service: Important Service

function sendMail{

Write-Host “Sending Email”

#SMTP server name
$smtpServer = “mail.domain.com”

#Creating a Mail object
$msg = new-object Net.Mail.MailMessage

#Creating SMTP server object
$smtp = new-object Net.Mail.SmtpClient($smtpServer)

#Email structure
$msg.From = “YourName@Domain.com”
$msg.ReplyTo = “YourName@Domain.com”
$msg.To.Add(“YourName@domain.com”)
$msg.subject = “Important Service has stopped”
$msg.body = “The Important  Service on Important Server has stopped.”

#Sending email
$smtp.Send($msg)

}
if (Get-Service -ComputerName ImportantServer -Name ‘Important Service’| Where-Object{$_.status -eq “stopped”} ){Write-Host”Stopped”; sendmail}
#elseif (Get-Service -ComputerName ImportantServer -Name ‘Important Service’| Where-Object{$_.status -eq “Running”}) {Write-Host “Not Stopped”;}

<End PowerShell Source>