Monthly Archives: April 2016

How SQLRX Can Help You When Tools Do Not

 

For those of you who visit our humble blog from time to time and have never actually checked out our website (www.sqlrx.com) to get acquainted with what we do, please allow me to check that box for you.

 

SQLRX is part of a Dallas, Texas based consulting company called Integrated Services Inc (ISI). The SQLRX division is tasked with providing consulting services for everything SQL Server. We do it all from installation to migrations to performance tuning and RemoteDBA services. Since we have been in business for a while, we have worked on systems from small 1 server shops to fortune 100 high transactional enterprise level systems.

 

One of the things we commonly see is that many shops have SQL monitoring software installed that they regularly use to monitor the systems that we are asked to work on. Interestingly, we always seem to find root causes of performance issues that the monitoring software may have been either missing or generating data on but not providing a solution. I ran across a blog post from Jonathan Kehayias who warns about severe performance issues that can be directly attributed to a very well-known monitoring software company who has yet to fix it. https://www.sqlskills.com/blogs/jonathan/a-warning-about-diagnostic-managers-query-monitor-feature-in-v9/   I sent this link to a client of mine that uses this software and he said that the next version was also buggy and that the vendor had tried to patch but had not fixed.

 

So, why use expensive tools to monitor for SQL performance if they don’t fix them? I realize that some places only have one person acting as the DBA and they use the software to help them keep an eye on things. However, when you look at how most of the SQL monitoring software companies promote themselves, they make it sound like they are the answer to all your issues. I know that is not true for anyone or anything.

 RemoteDBA

We offer a DBA subscription service that is VERY reasonably priced (about 1/3rd the cost of a FTE) that can be used either as the sole DBA for up to 4 SQL Servers or as backup to a staff DBA who needs some help. SQLRX does not install any executable to any server so you don’t have to worry about applying updates or planning an outage for software maintenance. We strictly use SQL Server functionality and have developed maintenance and proactive monitoring routines complete with reporting that is designed to watch for only the things that really need to be monitored, and alert only when there really is an issue. And, we don’t just monitor we also fix things. You can’t get that from software.

 

Here are a few examples of the things we monitor and alert on.

Blog_20160428_1Blog_20160428_2

Blog_20160428_3

Blog_20160428_4

Performance Tuning

Another service that we offer that is SUPER valuable is our performance tuning assessments. While these are fixed fee reports, they are a complete assessment of a SQL Server that looks at both SQL and Windows hardware performance. We call them holistic because we look at the entire system, not just one part. We don’t just tell you what is wrong, we also provide a list of recommended fixes in a list that gives the order that we would fix things. There are numerous companies with systems that were performing very badly that used our assessments to fix everything from queries to finding hardware that was incorrectly configured by a major hardware vendor. We even had one company that asked for our assistance in completely re-engineering all indexes in their main database, which we did quite successfully.

Blog_20160428_5

Blog_20160428_6

If you are looking for access to a SQL DBA or have software that is monitoring your SQL Servers but not providing the answers you are looking for, please contact us. We would be happy to provide a free of charge proof of concept and would love to help you out. Just send us an e-mail to get started: SQLRX@sqlrx.com

 

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

 

Load Windows Event Log Errors into a SQL Table

–By Lori Brown @SQLSupahStah

Part of being a proactive DBA is to get information on what is going on at the server level as well as at the SQL level. We know that the Windows Event logs are around but I find that not many DBA’s check them regularly. You can make this easier by using a vbscript to load errors and warnings from the Event Logs into a SQL table.

Review or scrape the Event Logs at a minimum to find out if any Windows or hardware related errors or warnings are being written. Most hardware vendors write warnings to the Event Logs when they anticipate an error is going to occur, so this gives you the opportunity to be proactive and correct the problem during a scheduled down time, rather than having a mid-day emergency.

Build a SQL table to hold Event Log info:

— table for the Windows Event Log

CREATE TABLE [dbo].[WinEventLog](

[ID] INT IDENTITY(1,1) NOT NULL,

[ComputerName] VARCHAR(128) NULL,

[EventCode] INT NULL,

[RecordNumber] INT NULL,

[SourceName] VARCHAR(128) NULL,

[EventType] VARCHAR(50) NULL,

[WrittenDate] DATETIME NULL,

[UserName] VARCHAR(128) NULL,

[Message] VARCHAR(MAX) NULL

) ON [PRIMARY]

GO

Use the vbscipt code below by saving it into a notepad document with the extension .vbs. Make sure to modify connection strings to connect to your instance and the correct database. And, set the number of days you want to check as well. I have it defaulted to 2 days but you can set it to whatever makes sense for you.

************************

‘VBScript

‘Purpose of script to query Application log for errors

dim strConnect, strComputer, strMessage, RoleStr

dim Category, Computer_Name, Event_Code, Message, Record_Number, Source_Name, Time_Written, Event_Type, User

dim dtmStartDate, dtmEndDate, DateToCheck

dim dtTimeWritten

 

‘Connection string for SQL Server database.

strConnect = “DRIVER=SQL Server;” _

& “Trusted_Connection=Yes;” _

& “DATABASE=<<Database Name>>;” _

& “SERVER=<<SQL Instance Name>>”

 

‘Use this string if SQL Server driver does not work

‘strConnect = “Provider=SQLOLEDB;” _

‘& “Data Source=<<SQL Instance Name>>;” _

‘& “Initial Catalog=<<Database Name>>;” _

‘& “Integrated Security=SSPI;”

 

‘ Connect to database.

Set adoConnection = CreateObject(“ADODB.Connection”)

adoConnection.ConnectionString = strConnect

adoConnection.Open

 

Set dtmStartDate = CreateObject(“WbemScripting.SWbemDateTime”)

Set dtmEndDate = CreateObject(“WbemScripting.SWbemDateTime”)

‘ Set number of days to scrape here

DateToCheck = Date – 2

dtmEndDate.SetVarDate Date, True

dtmStartDate.SetVarDate DateToCheck, True

 

strComputer = “.”

Set objWMIService = GetObject(“winmgmts:” _

& “{impersonationLevel=impersonate}!\\” & strComputer & “\root\cimv2”)

Set colLoggedEvents = objWMIService.ExecQuery _

(“Select * from Win32_NTLogEvent Where Logfile = ‘Application’ and (” & _

“TimeWritten >= ‘” & dtmStartDate & _

“‘ and TimeWritten < ‘” & dtmEndDate & _

“‘) and (EventType = ‘1’ or EventType = ‘2’)”)

 

For Each objEvent in colLoggedEvents

Category = objEvent.Category

Computer_Name = objEvent.ComputerName

Event_Code = objEvent.EventCode

Message = objEvent.Message

Record_Number = objEvent.RecordNumber

Source_Name = objEvent.SourceName

Time_Written = objEvent.TimeWritten

Event_Type = objEvent.type

User = objEvent.User

 

‘Fix single quotes in the message string

strSQ = Chr(39)

strDQ = Chr(34)

if len(Message) > 0 then

strMessage = Replace(Message, strSQ, strDQ)

else

strMessage = ” “

end if

 

dtTimeWritten = WMIDateStringToDate(Time_Written)

 

RoleStr = “SET NOCOUNT ON INSERT INTO WinEventLog (ComputerName, EventCode, RecordNumber,” _

& “SourceName, EventType, WrittenDate, UserName, Message) VALUES” _

& “(‘” & Computer_Name & “‘, ‘” & CLng(Event_Code) & “‘, ‘” & CLng(Record_Number) _

& “‘, ‘” & Source_Name & “‘, ‘” & Event_Type & “‘, ‘” & dtTimeWritten _

& “‘, ‘” & User & “‘, ‘” & strMessage & “‘)”

adoConnection.Execute RoleStr

Next

 

adoConnection.Close

 

Function WMIDateStringToDate(Time_Written)

WMIDateStringToDate = CDate(Mid(Time_Written, 5, 2) & “/” & _

Mid(Time_Written, 7, 2) & “/” & Left(Time_Written, 4) _

& ” ” & Mid (Time_Written, 9, 2) & “:” & _

Mid(Time_Written, 11, 2) & “:” & Mid(Time_Written, _

13, 2))

End Function

************************

To run the vbscript, you can double click it to run it manually or you can set up a SQL job that uses CMDEXEC to call the script.

@subsystem = N’CmdExec’,

@command = N’cscript E:\SQLRX\ScrapeWindowsEventLog.vbs’

Hopefully this will help make you more proactive and knowledgeable about your servers in general.

Enjoy!

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!