Powershell for SQL Database Administrators

Here is list of links that can get you started with Power Shell for SQL Database Administrators (DBA)


http://www.databasejournal.com/features/mssql/article.php/3681061/Microsoft-Windows-PowerShell-and-SQL-Server-2005-SMO--Part-I.htm
This is a 3 part article that start from installation and how to interact with Power Shell (PS)

http://www.mssqltips.com/tip.asp?tip=1680 - again this one introduces how to use PS and makes you comfortable with couple of commands

http://www.simple-talk.com/sql/database-administration/why-this-sql-server-dba-is-learning-powershell/
This one directly delves into how to us PS to automate some of the SQL Server admin task, includes good practical samples.

http://itknowledgeexchange.techtarget.com/dba/powershell-sql-server-health-check-script/ - Here is the really working sample that dumps the database information into an excel sheet.

Using the above links you should be able to build a script which can scan all your SQL Servers (listed in a given txt file) and dump the required information into a nicely formatted excel sheet.

But in my case most of the automation that I built around SQL Server was using VB Script, HTML templates and DOS!

I think Power Shell is for new age Database Administrators.

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

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

Enumerate Disks & Disk Free Space On SQL Server

Ever cameup with a situation wherein you want to create a databaes on the fly and to decide dynamically where to place your data and log files, yes I faced it and cameup with the following solution.

You have one stored procedure and one function that can help, first xp_fixeddrives and second one is a system function fn_servershareddrives. xp_fixeddrives procedure outputs the list of drives with free space available on each of the drives, but does not tell you which is shared(SAN drive) and which is local. This is important because you cannot create a database on a local drive when SQL Server is running in clustered environment. And function fn_servershareddrives() does not list the free space available and also does not report local drives so combine the results of both and list every thing and pick what you want and create the database, based on isClusterd() server property output. Here is what I did.




SET NOCOUNT ON

CREATE TABLE #DriveSpace (
        Drive char(1),
        MBFree int
)

-- get availabe drives and free space on each of them
INSERT INTO #DriveSpace EXEC xp_fixeddrives

SELECT * INTO #DriveUnion FROM
(
        SELECT  A.DriveName COLLATE Latin1_General_CI_AS 
        [Drives Available], #DriveSpace.MBFree, 'X'[Shared] 
        FROM    ::fn_servershareddrives() A
                LEFT JOIN #DriveSpace on
                A.DriveName COLLATE Latin1_General_CI_AS = #DriveSpace.drive
        UNION

        SELECT  Drive COLLATE Latin1_General_CI_AS [Drives Available], 
                MBFree, '-'[Shared] 
        FROM    #DriveSpace 
        WHERE   Drive NOT IN (SELECT DriveName COLLATE Latin1_General_CI_AS 
                        FROM ::fn_servershareddrives())
) A

/**
 -- if clustered environment eliminate local drives from the list.
 IF SERVERPROPERTY('isClustered')=1
        DELETE FROM #DriveUnion where Shared='-'
SELECT * FROM #DriveUnion
**/

DROP TABLE #DriveSpace
DROP TABLE #DriveUnion

 
Sample Output

'X' indicates shared drive, where as '-' indicates local drive. 
Drives Available MBFree      Shared
---------------- ----------- ------
C                50469       -
D                36053       -
E                267942      X
F                39384       X
G                3657        X
H                179584      X