[Servers] Extensible (Powershell) Repository - just add scripts!

(Version 1.1. I so should've posted this earlier - looks like everybody has been releasing their version of this idea over the past week).


Over the past few months, we've been working on knowing more about our servers.  Besides sp_blitz, there's a lot of data we want: DMVs, WMI info, etc. So a coworker & I had a challenge going - which would be a faster way to query our 80+ production servers, SSIS or PowerShell?  Well, he had it running faster, but then I asked him to up the number of simultaneous threads and it was a chore for him to change.  For me, alter a line of code in a text file.  And it seemed easier for me to add more scripts.

So I decided to make mine, while not (necessarily) the fastest, the easiest to use.  It uses Powershell and some PS scripts originally written by Chad Miller, Arnoud Jansveld, and several other people smarter than me.  I just put it all together.

TL;DR: Drop a query in a folder, and it runs several threads in parallel against all your servers, saving the details out to a table, overwriting the old data if you want.

Pros:

• Trivial install - 6 scripts in a folder, a table with a list of servers, 1 or 2 empty subfolders, and 1 job.
• Low overhead - a sample (simple) script ran on 80+ servers in under 6 seconds.
• Easy to add a new collection - just drop a SQL/PS script in the folder with the name for the table.
• Need more servers done faster?  Up the threads.
• Skips servers it can't connect to.
• No powershell extensions needed - just those 6 scripts.  PS3 below, modify the one line for PS2.

Cons:

• Does not deploy code; just runs scripts. (so sp_blitz, for instance, needs to be installed separately)
• No failure info. That's on my to-do list, but is not nearly as easy as I'd like
• Datatypes for new tables need to be tweaked if the script creates the table - text fields default to varchar(1000)

To install:

• Create a table with a list of servers to monitor.
• Create 3 folders: c:\sql_tools (or wherever; change the actual script's foldername), and underneath it one for scripts where you want to keep the data, and one where you don't.
• Grab add-sqltable.ps1, write-datatable.ps1, and invoke-sqlcmd2.ps1 from Hey Scripting Guy or poshcode. Put in c:\sql_tools.
• http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/01/use-powershell-to-collect-server-data-and-write-to-sql.aspx
• http://poshcode.org/3679 (Split-Job)
• Save the below script to a file called "repository_extensible.ps1", in c:\sql_tools.
• Create a job with 2 job steps, both as Type: "Operating System (CmdExec)":
• powershell "& c:\sql_tools\repository_extensible.ps1 c:\sql_tools\repository_scripts_keep 0"
• powershell "& c:\sql_tools\repository_extensible.ps1 c:\sql_tools\repository_scripts_delete 1"
• The first script runs scripts where each time, records will be added to the table.  The second will delete records from each server, replacing it with the new records. 
• Toss a couple sample scripts in the folders 
• For SQL, I recommend Paul Randall's excellent Wait & Latches scripts
• http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
• http://www.sqlskills.com/blogs/paul/survey-most-prevalent-latch-waits-code-to-run/
• For Powershell, you will need to tell the script what computer to run against.  This is because we're not using something like SQLCMD which has a "server" parameter for the entire script.
• Note the " -computername $args[0]" - that allows us to tell it which server to query.
• Get-WmiObject Win32_LogicalDisk -computername $args[0] 
• Name each scripts tablename_you_want.sql - the base name becomes the table name
• Run the job once.
• Go into your repository database and modify the table; by default strings automatically become varchar(1000).  Yes, it's not great, but will work for now.

[Replication Monitor] YA monitor - check delay times with last_distsync and sp_replmonitorhelpsubscription

Got hosed because I accidentally added a a clause in our "how backed up are we" from http://thebakingdba.blogspot.com/2010/10/replication-better-alternative-to.html , and set it up wrong, so I filtered stuff I didn't mean to.

Lessons learned, and got me thinking ... One thing I haven't had on my replication monitor was a reliable way of detecting if any of my subscriptions are expired or about to expire.  The dread 72 hours.  So, several hours later, a profile trace and a question on StackOverflow, and I got a way to do it.

Basically, you're running the system SP sp_replmonitorhelpsubscription, once for each server that the distributor handles.  You then filter that out (looking for warnings and ignoring recent records) and send an email if there's anything left.

One downside: because we're trying to avoid the NESTED EXEC issue (can't have an INSERT INTO EXEC where the code you're running has an INSERT INTO EXEC), the most reliable way to avoid it is by using OPENROWSET (thanks to Paul Ibison for that).  However, that requires that Ad Hoc Distributed Queries be enabled via sp_configure.  Yes, it can be a security hole.

An alternative way to do it, according to replication expert Hilary Cotter, is to run the SP twice, and only INSERT INTO on the second Exec.  In my testing it's not as reliable (fails occasionally, and doesn't lend itself to automation quite as well, but it may be a better option for you.

Hope this helps (and hopefully the blogspot sourcecode formatter I'm using is good)!

[tips] using OPENROWSET to avoid "An INSERT EXEC statement cannot be nested."

Found this, looks like from Paul Ibison (replicationanswers.com).  In this case I _did_ need it for replication, but this could be useful whenever you are run into the error "An INSERT EXEC statement cannot be nested.".

One note - depending on how the original piece of code works, you may or may not need the SET FMTONLY OFF;.  (In this case I do).  If you do, be aware that the code is run TWICE.  For this, it works fine, but keep it in mind.

USE eif_workspace
GO
create table dbo.repmonitor (
[status] int null,
warning int null ,
subscriber sysname null ,
subscriber_db sysname null ,
publisher_db sysname null ,
publication sysname null ,
publication_type int null ,
subtype int null ,
latency int null ,
latencythreshold int null ,
agentnotrunning int null ,
agentnotrunningthreshold  int null ,
timetoexpiration  int null ,
expirationthreshold  int null ,
last_distsync  datetime null ,
distribution_agentname  sysname null ,
mergeagentname  sysname null ,
mergesubscriptionfriendlyname  sysname null ,
mergeagentlocation  sysname null ,
mergeconnectiontype  int null ,
mergePerformance  int null ,
mergerunspeed float null ,
mergerunduration int null ,
monitorranking  int null ,
distributionagentjobid  binary(30) null ,
mergeagentjobid binary(30) null ,
distributionagentid  int null ,
distributionagentprofileid int null ,
mergeagentid int null ,
mergeagentprofileid int null ,
logreaderagentname sysname null
)
go

Insert Into aud.dbo.repmonitor

SELECT *

FROM OPENROWSET('SQLNCLI', 'Server=yourservername;Trusted_Connection=yes',  'set fmtonly off;  exec distribution..sp_replmonitorhelpsubscription @Publisher=@@servername,@publication_type=0')

[Extended Events] Finding new connections and saving to a asynchronous bucketizer/histogram

We're trying to get rid of our .Net SQLClient Data Provider apps.  Well, trying to get rid of the useless name.  How do we do that?  By figuring out which servers they're coming from and which databases they're hitting, and giving that to our systems folk so they can find the connection strings and add Application Name.

My first thought was EN...  ha!  No, it won't work for that, I don't think.
My second thought were traces.  Better, but we'd need to just get audit_login, then we'd have to parse it out, etc.
So my third thought was to use XE.  Lo and behold, it works!  Get the client host names, and save them to a bucket.  Then, as they connect, either add to or increment the number for that bucket.

-- If the Event Session Exists, drop it first
IF EXISTS (SELECT 1
FROM sys.server_event_sessions
WHERE name = 'UnknownAppHosts')
DROP EVENT SESSION UnknownAppHosts
ON SERVER;

-- Create the Event Session
CREATE EVENT SESSION UnknownAppHosts
ON SERVER
ADD EVENT sqlserver.login(
    ACTION(sqlserver.client_hostname)
WHERE ([sqlserver].[client_app_name] LIKE 'Microsoft SQL Server Management%')
)
ADD TARGET package0.histogram
( SET slots = 50, -- Adjust based on number of databases in instance
 filtering_event_name='sqlserver.login',
 source_type=1,
 source='sqlserver.client_hostname'
)
WITH(MAX_DISPATCH_LATENCY =1SECONDS);
GO

-- Start the Event Session
ALTER EVENT SESSION UnknownAppHosts
ON SERVER
STATE = start ;
GO

-- Parse the session data to determine the databases being used.
SELECT  slot.value('./@count', 'int') AS [Count] ,
        slot.query('./value').value('.', 'varchar(20)')
FROM
(
SELECT CAST(target_data AS XML) AS target_data
FROM sys.dm_xe_session_targets AS t
    INNER JOIN sys.dm_xe_sessions AS s
ON t.event_session_address = s.address
WHERE   s.name = 'UnknownAppHosts'
 AND t.target_name = 'Histogram') AS tgt(target_data)
CROSS APPLY target_data.nodes('/HistogramTarget/Slot') AS bucket(slot)
ORDER BY slot.value('./@count', 'int') DESC

GO


-- Start the Event Session
ALTER EVENT SESSION UnknownAppHosts
ON SERVER
STATE = STOP ;
GO

[EN] My exclusion list for events

During my Event Notification presentation, I said I'd provide a list of my filters.  Hope these help.


exclusion_set exclusion_type excluded_value
a ApplicationName Quest Diagnostic Server (Monitoring)
b DatabaseName tempdb
c EventType update_statistics
d ObjectName _WA_Sys%
f EventType OBJECT_CREATED
f EventSubClass 0
g EventType OBJECT_ALTERED
g EventSubClass 0
h EventType OBJECT_DELETED
h EventSubClass 0
j PropertyName show advanced options
k EventType OBJECT_CREATED
k ObjectType 21587

ac ApplicationName Red Gate Software Ltd SQL Prompt%
ac EventType ERRORLOG


a: Quest Spotlight, which tends to create a large number of objects.
b: tempdb, so I don't get temp tables.
c: update_statistics, only needed if you're using DDL_TABLE_VIEW_EVENTS but don't want stats. Type out the extra characters in your deploy, it'll be easier on your systems
d: system-created statistics, which would show up in OBJECT_CREATED
f/g/h: For OBJECT events, it filters EventSubClass 0 , which occurs when the OBJECT change first runs.  (there is also a EventSubClass 1 or 2 returned, success/failure)
j: We have a couple apps which flip it incessantly.
k: this should be a dupe of D - ObjectType 21587 is statistics (as per: http://msdn.microsoft.com/en-us/library/ms180953.aspx)
ac: because SQL Prompt runs a trace flag (3604) that shows up in your database.

Here's how I looked at the errorlog messages:

SELECT message_body_xml.value('/EVENT_INSTANCE[1]/TextData[1]', 'varchar(100)'), *
FROM EventNotificationRec..ENAudit_Events
WHERE EventType = 'errorlog' AND
message_body_xml.exist('/EVENT_INSTANCE[1]/ApplicationName[contains(.,"Red Gate Software Ltd SQL Prompt")]') = 1

That gives a good example on querying the XML.  The "exist" (MUST be lower case!) basically sees if the App Name contains that phrase, and also returns the TextData field, up to the first 100 characters.

[EN] Errorlog emails based off Event Notification

NOTE! THIS DOES NOT WORK.  In writing this, we found there's a bug in EN where ERRORLOG doesn't get sent if the Source is "Server".  Which shutdown qualifies as.  You can search for "shut down", but you'll only get the message when the service restarts - the message gets sent close enough to shutdown that it doesn't make it to the centralized service until after restart.



All of our servers that have Event Notifications get the ERRORLOG event type.  What can we do with that?  Well, one thing we needed was a notice when a machine announced it was shutting down.  We've seen that a few times over the years, where the server decides it's better off restarting, and does it on its own.  This isn't a hard bounce - the error logs actually state that it's a deliberate choice by the server.  Offhand I know we've seen it due to DBCC CHECKDB, but I seem to recall other instances.

On looking at the code, I realize I could change the CTE to use half the IO.  However, I want to make sure I catch ANY instances, and the exist/contains XML query is CASE SENSITIVE.  So, I'm staying with mine for now.

But here it is in case....
 and EventType = 'errorlog'

 AND message_body_xml.exist('/EVENT_INSTANCE[1]/TextData[contains(.,"shutdown")]') = 1

And here's the full email.

set quoted_identifier on

declare @now datetime

select @now = convert(smalldatetime,getdate())

;with cte as 

(

SELECT message_body_xml.value('/EVENT_INSTANCE[1]/TextData[1]', 'varchar(150)') as textdata, * 

FROM ENAudit_Events with (NOLOCK) WHERE insert_datetime >= @now

 and EventType = 'errorlog'

 )

select * into ##listofshutdown from cte where textdata like '%shutdown%'

if (select count(*) from ##listofshutdown) > 0

begin

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'youremailprofile',

    @recipients = 'youremail@dev.null.com',

    @query = 'select * from ##listofshutdown' ,

    @subject = 'Attention - a machine has announced it is shutting down via EN ERRORLOG',

    @query_attachment_filename = 'shutdowninfo.txt',

    @query_result_separator =  ' ',

    @query_result_width = 750,

    @attach_query_result_as_file = 1 ;

end

drop table ##listofshutdown

[PASS] everything from my presentation today on Event Notifications

Thanks to all who attended.  Here's my Share with all the code samples and slide deck from today.  I'll be cleaning things up in the next couple of hours, replacing updated code and the like. 
Everything's current now - the "script 1-5" are the scripts shown during the demonstration.  The SSMS report and SSRS report have been added, and my automation script is the current version.  The Parse_EN_Messages was updated in November and hasn't changed since.

At the very least, find yourself a testbox and run Script 1 - that shows how it all works, and is a great Proof Of Concept.  You could also change the trace group to DDL_SERVER_LEVEL_EVENTS and see all the various things tracked.


Thanks again!
TBD


https://docs.google.com/folder/d/0B3a287PS_UJIcnY3Q1pvX3p1eEE/edit?usp=sharing

And my original blog post on it, which I keep updating:
http://thebakingdba.blogspot.com/2012/10/master-of-all-i-survey-using-event.html

--
and the video (Thanks to Mike for handling the video duties!)
mms://passmedia.sqlpass.org/share/dba/MasterofAllISurvey_03272013.wmv

[PASS] I'm presenting March 27, 2013!

Howdy, all!  I am pleased to be presenting on Event Notifications this week.  It's a soup-to-nuts on tracking code changes and more, across your environment.  I honestly think most people reading this would find it handy.  We've found it essential in our environment.

More information at dba.sqlpass.org

Date: March 27, 2013 

Noon Eastern time – www.timeanddate.com/worldclock/fixedtime.html

Topic: Master of All I Survey - Tracking Code Changes Across Dozens of Servers Seamlessly and Automatically – sponsored by Quest Software

Presenter: Michael Bourgon

Abstract:

Everyone's been bitten by it - rogue changes made on servers, be it SPs, index rebuilds, or even configuration changes. What if you knew? What if you could look historically across servers and track down what changes were made when, by who, and from where? It exists, it's already built into MSSQL, it's easy to implement and it's been there waiting for you for 7 years. Come learn what Event Notification is and why you'll want it in your environment.

Bio:
Michael Bourgon is the Senior Production DBA for Emdeon Pharmacy Services.  He started work with Sybase on RS/6000 over 15 years ago, and has been meddling with some form of the SQL Server codebase ever since.  He's always looking for a better way to be a Lazy DBA - spending far too many hours automating processes in order to accomplish that.  In his spare time he listens to weird music, reads Science Fiction, and tries to ensure his daughter grows up a nerd, too. 

 Door prize: There will be a raffle for a $50 Amazon gift certificate.  You do not need to register for the meeting, but if you want to enter the raffle, you must register at www.livemeeting.com/lrs/8000181573/Registration.aspx no later than 5:00 PM EDT on March 26th.

Live Meeting Link: www.livemeeting.com/cc/8000181573/join

[SSMS] Fun fact - Emacs!

Was going through my freshly formatted install, and put 2012 SSMS on it.  Then went digging to change the comment/uncomment shortcuts, and (of course) change Ctrl+O to it's old Query Analyzer function: New Database Engine Query (new window, choosing database connection at that time).  I honestly don't know how people DON'T have this mapped.  Do you seriously click each time, or go File->New->Database Engine Query?

Lo and behold...
Edit.EmacsWordCapitalize
Edit.EmacsSetMark
Edit.EmacsScrollLineTop

and so on, and so forth.  Forty-three commands in total.  Impressive, if only because Emacs is the quintessential best/worst about Linux.

I'm both amused and annoyed by this:
1) You went to all the effort to emulate Emacs, but couldn't bother setting up a base shortcut for Database Engine Query, which you use all the frickin' time?
2) All the other glaring issues with SSMS, but you had time to emulate Emacs?
and of course, the most Egregious:
3) No VI mode!

[RDP] Finding and logging people off from command line

Thanks to Tony for this!

Obviously, you can use the Terminal Services Manager (now the Remote Desktop Services Manager) to find people and log them off.  This is a command line way to do the same thing.  Easier, possibly, and doesn't (to my knowledge) require the Manager pack be downloaded & installed.

To list users:
query session /server:yourservernamehere

To kick off users:
RESET SESSION 1 /SERVER:yourservernamehere
or
rwinsta /SERVER:yourservernamehere 1

(replace 1 with whatever the ID is in the results from the QUERY)