SQL Server Availability Monitoring - Part II

In this part of the article I will show you how to deploy the solution that I built in the previous article SQLServer Availability Monitoring Part I . The solution was designed to monitor SQL Server in an environment from another monitoring SQL Server. Here I have assumed that I have a dedicated box with SQL Server setup for monitoring. In my case I had a MOM database server which was used only for monitoring purposes. I created my solution specific table(s) in the same server by creating another database called DBA_Benchmark.

Below are the steps involved in the solution deployment.

Create a table to store logging info that comes from Sub.vbs,following is the script for the same. The column names are self explanatory,

3 Tbl_isAlive Table creation script


CREATE TABLE [dbo].[Tbl_isAlive2] (
[RecDateTime] [datetime] NULL ,
[RecInstName] [varchar] (50) NULL ,
[ResSeconds] [numeric](8, 6) NULL ,
[ResDesc] [varchar] (500) NULL
) ON [PRIMARY]

Next create a SQL job that triggers SQLPing.vbs. Instead of calling the vbs directly I have used a wrapper batch file. Batch file actually came into picture because earlier I had an idea of using it as a NT Scheduled job, however I changed my mind and decided to play with SQL Server only.

4 Launch.bat


:Path on my the server where VBS is located
CD "C:\isAlive\UsingVB"
SQLPing.vbs SrvList.txt

In the above DOS command the SrvList.txt is the txt file that holds my entire list of SQL Server one per line. Thecontents as are shown below.

5 SrvList.txt


Sun\SQLSrv_2K
Sun\SQLSrv_2K5
Sun\MSDE

Now, its time to create a job in SQL Server to trigger theLaunch.bat. This layer of batch file can be surely avoided ifrequired.

6 SQL Batch creation script

BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'isAliveChk') > 0
PRINT N'The job "isAliveChk" already exists so will not be replaced.'
ELSE
BEGIN

-- Add the job

EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'isAliveChk', @owner_login_name = N'sun\girish', @description = N'Job Comments.',
@category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0,
@notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2,
@delete_level= 0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job steps

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'step_01_01', @command = N'-- run for 3 times in a minute.

DECLARE @iCount INT

SET @iCount=0

WHILE (@iCount < 5)

BEGIN

EXEC master..xp_cmdshell ''D:\isAliveChk\Launch.bat'', no_output

WAITFOR DELAY ''00:00:10''

SET @iCount=@iCount+1

END

-- end

', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1



IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


-- Add the job schedules

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Sch_isAlive', @enabled = 1, @freq_type = 4, @active_start_date = 20070607, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

In the job I execute the following T-SQL Code


DECLARE @iCount INT
SET @iCount=0
WHILE (@iCount < 5)
BEGIN
EXEC master..xp_cmdshell ''D:\isAliveChk\Launch.bat'', no_output
WAITFOR DELAY ''00:00:10''
SET @iCount=@iCount+1
END


This is basically I want to check the service availabilityevery 10 seconds and SQL Agent in SQL 2000 does not allow to run any task withfrequency of less than a minute. So I use above code to overcome thatlimitation.

After implementation is over, query the table Tbl_isAliveand see if results are getting logged, at least there should some rows, becausewe query and log results every 10 seconds, unless you have changed it tosomething different.

Troubleshooting: In case if you see that its notworking correctly then disable the SQL Job first and try to launch the batchfile manually by dbl clicking on the file. If it works fine then looks like thepath what you have specified in the SQL Job may not be correct so fix the pathto point at the correct location.

In case there is any other issue, the error details willbe logged in the file called as AppErrorLog.txt. If there iserror connecting to the server that it is logging the results to then you willsee the following kind of error message in the Errorlog,

06/15/2007@ 11:52:08 AM -- Error : -2147467259 ==> [Microsoft][ODBC SQL ServerDriver][DBNETLIB]SQL Server does not exist or access denied.


Otherwise if its having an issue with connecting to theservers to be monitored then entry in log file or SQL Server table would looklike,

06/15/2007@ 11:52:08 AM INSERT INTO DBA_BenchMark.dbo.Tbl_isAlive VALUES('06/15/200711:51:44 AM', 'NonExistant', 12.1875, '[Microsoft][ODBC SQL ServerDriver][DBNETLIB]SQL Server does not exist or access denied.')

Rare 'Jenny' stamp sells for $825,000

In this undated photo provided by Heritage Auction Galleries of Dallas, a rare 24-cent stamp depicting an upside-down airplane is shown. The 1918 stamp depicts a Curtiss JN-4 'Jenny,' a World War I training aircraft that became an airmail plane. A collector from New York has purchased the rare 24-cent stamp depicting the upside-down airplane for $825,000, according to Heritage Auction Galleries.
(AP Photo/Heritage Auction Galleries)

The rare 1918 24-cent stamp, depicting an upside-down Curtis JN-4 biplane known as "Jenny," was sold privately this week to a Wall Street executive who did not want to be identified.

Heritage Auction Galleries president Greg Rohan, who brokered the sale, said the buyer is the same collector who lost an auction last month in which another "Inverted Jenny" sold for $977,500. Rohan said his client, whom he described as not being a rare stamp collector, was glad to get another chance at the prized misprint.

"I suspect he's going to enjoy owning it and showing it to a few close friends," Rohan said.

The mint condition red, white and blue stamp is one of the finest known surviving stamps from the original sheet of 100 misprints. The original 100 were bought at a Washington, D.C., post office in 1918.

Randy Shoemaker, founder of Professional Stamp Experts, said there are probably fewer than 1,000 collectors in the world with the money and obsession to seriously pursue such a rare item.

"This is the Rolls-Royce," Shoemaker said. "It's the most expensive airmail stamp in the world."

Rohan said Heritage acquired its stamp from Sonny Hagendorf, a dealer from Scarsdale, N.Y. Rohan said the copy sold by Heritage is one of fewer than five to have never been hinged, meaning affixed with adhesive to be placed in a stamp album.

I am saving this image and article just who knows one day this also might fetch some money :)

SQL Server Availability Monitoring - Part I

The most typical issue that any production support DBA faces is SQL Server "SERVICE" availability. Many times it happens that the 24/7 support team calls up and says XYZ application is down and it shows the message as “Database Error”. I log in remotely and check the service, and service looks like is up and running, and when I check back the application, it's running fine now. So what went wrong in the mean time ?

Usually the SQL Server might be busy and the application might have timed out, either lock or connection timeout issue either way from application's point of view the database service was not available. So the trouble here is

1 - Exactly when the application/database was was experiencing the issue?
2 - Was application connection timeout setting was too low?
3 - Do you have statistics of SQL Server service availability?

Lot of questions will be raised when the issue is escalated and both app and database management team sit for discussions.

To address all such situation I came up with a utility/script "SQLPing". Yes, you got it right I will ping the SQL Server not to kill but tocheck if the service is available or not, so that I have answers to all abovequestions. I have implemented this as a VB Script because of its obvious advantage and I can edit as and when I want to change some parameter. And theother advantage is I don’t have to document it to make other understand what is it all about, have a look at the code and you will get what it is doing.

Let me quickly explain the files used in providing this solution,

1 - SQLPing.VBS

' Script description
'----------------------
' This procedure only reads the SQL Server name from the given(paramer) file
' and launches the sub.vbs per SQL Server found in the file.
' This design was choosed to avoide the delay caused in pinging subsequent server while
' ping scirpt is waiting for current one. Also, all servers can be pinged almost (difference of fraction seconds)
' at the same time.

'CODE STARTS

Set WshShell = WScript.CreateObject("WScript.Shell")
IF Wscript.Arguments.Count = 0 THEN
MsgBox "Server List Missing", vbCritical, "Provide the list of servers."
Wscript.Quit
END IF

PingSQLServer


'*** Procedure to query database and create the HTMLlized content ***
Sub PingSQLServer

Dim conn, rs, Qry_Txt, Qry_01, Qry_02, Qry_03, sTarget, RptFileName
const RptFldr = "C:Girish_PatilisAliveUsingVB"
SET fso = CreateObject("Scripting.FileSystemObject")
SET oFileTargets = fso.OpenTextFile(Wscript.Arguments(0),1)
DO UNTIL oFileTargets.AtEndOfStream
WshShell.Run "sub.vbs " & oFileTargets.ReadLine
LOOP
End Sub

Save the contents of about table to a file and name it as SQLPing.vbs. This file basically when executed, reads the server name from the file (passedas input parameter) and in-turn executes the Sub.vbs




2 - Sub.VBS

' Script description
IF Wscript.Arguments.Count <> 0 THEN
SQLWork Wscript.Arguments(0)
END IF


'*** Procedure to connect to SQL Server ***
Sub SQLWork(SqlSrv)
On Error Resume Next
myTimer=timer
TstTime=formatdatetime(now, 0)
set conn = CreateObject("ADODB.Connection")
conn.ConnectionTimeout = 3
RecDate = formatdatetime(now, 0) ' current time on monitoring system.
conn.open("DRIVER=SQLServer; SERVER="& SqlSrv & ";DATABASE=master;Trusted_Connection=Yes")
if err.number <> 0 Then
WriteToDB "INSERT INTO DBA_BenchMark.dbo.Tbl_isAlive VALUES('" & TstTime & "', '" & SqlSrv & "', " & timer-myTimer & ", '" & REPLACE(Err.description, "'","''") & "')"
err.clear
exit sub
End If
set rs = CreateObject("ADODB.Recordset")
set rs = conn.execute("select getdate() AS [DateTime]")
WriteToDB "INSERT INTO DBA_BenchMark.dbo.Tbl_isAlive VALUES('" & TstTime & "', '" & SqlSrv & "', " & timer-myTimer & ", '" & "Response " & RS(0) & "')"
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
End Sub


'*** Procedure to WRITE the output to a given file ***
Sub WriteToDB (TxtToWrite)
On Error Resume Next
err.clear '-- clearing the error from previous sub
set conn = CreateObject("ADODB.Connection")
conn.ConnectionTimeout = 10
conn.open("DRIVER=SQL Server;SERVER=SQL2K5;DATABASE=DBA_Benchmark;Trusted_Connection=Yes")
' Error handler / Timeouts
if err.number <> 0 Then
LogErrors ("-- Error : " & Err.Number & " ==> " & Err.description)
LogErrors TxtToWrite
Err.Clear
exit sub
End If

'run a query to log the results
conn.execute (TxtToWrite)
conn.Close
Set conn = Nothing
End Sub


'*** Procedure to WRITE the output to a given file - for error logging ***
Sub LogErrors (TxtToWrite)

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim fso, f
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile( "AppErrorLog.txt", ForAppending, True)
f.Write date & " @ " & time & VBTAB & TxtToWrite & VBCRLF
f.Close
End Sub


Sub.vbs is the one which actually connects to SQL Server and checksif it’s responding, and collects the response time. In case of connection timeout and query timeout it will log the same information.

I will explain how to deploy this solution in my next article SQL Server Availability Monitoring Part-II

Exporting SQL Maintenance Plans & Jobs

 
The best way that I have employed is to restore the MSDB database. That will get you all the jobs, maintenance plans and their histories and also backup set details.

However if you say that you don’t want anything except Maintenance plan only then here is the method. Don’t forget that there are jobs attached to maintenance plan as well. So I would import the data from following tables.

sysdbmaintplan_databases - Contains one row for each database that has an associated maintenance plan. This table is stored in the msdb database.

sysdbmaintplan_history - Contains one row for each maintenance plan action performed. This table is stored in the msdb database.

sysdbmaintplan_jobs - Contains one row for each maintenance plan job. This table is stored in the msdb database.
sysdbmaintplans - Contains one row for each database maintenance plan. This table is stored in the msdb database.

For jobs following tables need to be copied/ imported.
sysjobs - Stores the information for each scheduled job to be executed by SQL Server Agent. This table is stored in the msdb database.

sysjobschedules - Contains schedule information for jobs to be executed by SQL Server Agent. This table is stored in the msdb database.

sysjobsteps - Contains the information for each step in a job to be executed by SQL Server Agent. This table is stored in the msdb database.

NOTE: Table definitions are as given in the books online (help files)
So basically we have to move data from following queries to respective tables.
SELECT * FROM sysdbmaintplans
SELECT * FROM sysjobs WHERE job_id in (SELECT job_id FROM sysdbmaintplan_jobs)
SELECT * FROM sysjobsteps WHERE job_id in (SELECT job_id FROM sysdbmaintplan_jobs)
SELECT * FROM sysjobschedules WHERE job_id in (SELECT job_id FROM sysdbmaintplan_jobs)
Table names are self explained.
Similarly DTS packages can also be moved. Check it out here http://www.sqldts.com/204.aspx