Saturday, December 29, 2007

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


    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

      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.'

      -- 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)


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

      WAITFOR DELAY ''00:00:10''

      SET @iCount=@iCount+1


      -- 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


      GOTO EndSave

    In the job I execute the following T-SQL Code

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

    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.')

Thursday, December 27, 2007

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 :)

Friday, December 21, 2007

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.


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


      '*** 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
      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
      TstTime=formatdatetime(now, 0)
      set conn = CreateObject("ADODB.Connection")
      conn.ConnectionTimeout = 3
      RecDate = formatdatetime(now, 0) ' current time on monitoring system."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, "'","''") & "')"
      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) & "')"
      Set rs = Nothing
      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"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
      exit sub
      End If

      'run a query to log the results
      conn.execute (TxtToWrite)
      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
      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

Thursday, December 20, 2007

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

About Me

By profession I am a Database Administrator (DBA) with total 13 yrs. of experience in the field of Information Technology, out of that 9 yrs as SQL DBA and last 3 years in IBM System i/iSeries and DB2 LUW 9. I have handled Developer, plus production support roles, and I like both the roles. I love and live information technology hence the name "Techonologyyogi" Apart from that I am a small, retail investor, with small investments in India and United States in the form of Equity holdings via common stocks. Don't ask me if I have made money, I have been loosing money in stocks.