[SSIS] Finding your connection strings in SQL Server 2008/R2

"Bad artists imitate - great artists steal" Picasso Banksy

I initially wasn't sure if this was worth posting - if you have the same problem, you've probably come across the same links.  But since I don't see a soup-to-nuts version, here goes.

Problem: you're migrating servers, and need to see what SSIS packages might be affected, so you need the connection strings for all of your SSIS packages.  You could open up each package and check the connections - but that's... inefficient.

If you're storing the packages on disk, you could write a short (power)shell script that would scan the SSIS package files.  But if it's in MSDB, you'd need to pull the package XML out of the system tables, then parse the XML.  This is for 2008/R2 (and if you're not using SSISDB in 2012).  In 2012, SSISDB holds the files differently, and I'm awaiting an answer from one of the MS people on the SSIS team (thanks to Jamie Thomson sqlblog.com/blogs/jamie_thomson/‎ for forwarding my tweet to him)

Code cribbed from somebody, possibly Feodor Georgiev  (I found two links, below)
http://sqlconcept.com/2011/12/06/extracting-ssis-package-definition-from-msdb/
https://www.simple-talk.com/content/print.aspx?article=1408

and the XML parse was a question I asked on StackOverflow.  Thanks to Davmos for the help!
http://stackoverflow.com/questions/16824772/sql-server-query-xml-node-dtsconnectionmanager-dtsname-in-t-sql/16825308?noredirect=1#comment24269324_16825308

[Powershell] Grabbing and parsing the RSS feed for new SQL Server Releases

(update 2013/05/23 fixed the date format issue; uses smalldatetime now)

My boss asked me to scrape a web site for release/security info for SQL Server.  Okay.

First attempt required a stop at stackoverflow, where I was reminded about hashtables (Thanks again to Grainer!)  And after several hours, I had found the correct Microsoft blog for security updates and had this:

cls
$posts= @() # this basically resets the posts for multiple runs - broke my ISE and good before

[xml]$rss = Invoke-WebRequest http://technet.microsoft.com/en-us/security/rss/comprehensive

$rss.SelectNodes('//item') | % {
    $posts += New-Object psobject -Property @{
        Title = $_.Title.Trim()
        Thedate = [datetime]$_.date.Trim()
        Guid = $_.Guid.Trim()
        Content = $_.Encoded."#cdata-section".Trim()
    }
}

$posts | select-object # | where-object {$_.Title -like '*sql*' -or $_.Guid -like '*sql*'}
#and then insert into a table

Awesome!  I feel good - mission accomplished.  Worked on it at home (yes, nerd here, big shock).
Then the boss looks and says "no, I didn't want that, I wanted the patches and CUs".

Crap.  So let's go find a different blog.  Oh yeah, I have one specifically for SP/CUs!  (In google reader, naturally...RIP.  OT: go to Newsblur and CommaFeed!)

Fortunately, this blog is much easier to parse, so I don't need the hashtable.  Well, not a massive hashtable.  The second one below is specifically to rename the "Description" field so that I don't have a field that's a semi-reserved word. The first one below is to reformat it as a datetime.  Use Chad Miller's awesome scripts to turn it into a datatable and write to disk, and voila!  (Alternatively, you should be able to use my Extensible Powershell Repository, elsewhere on this blog, and just have a 2-line PS script that is invoked via the EPR.

Now, I just have to use that with a MERGE statement to add only the new records, and email me when a new record comes in. 

# CREATE TABLE SQL_Server_Releases_from_RSS
#(
# title varchar(200),
# link varchar(500),
# pubdate smalldatetime,
# descript varchar(MAX) --I really don't want a field named "description"
#)
# Scripts from http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/01/use-powershell-to-collect-server-data-and-write-to-sql.aspx
. C:\sql_tools\out-datatable.ps1  #Chad Miller rocks!
. C:\sql_tools\write-datatable.ps1
. C:\sql_tools\add-sqltable.ps1

[xml]$rss = Invoke-WebRequest http://blogs.msdn.com/sqlreleaseservices/rss.xml

$datatable = $rss.rss.channel.item | select title, link, @{Name="pubdate";Expression={get-date ($_.pubdate) -format "yyyy/MM/dd hh:mm"}}, @{Name="descript";Expression={$_."Description"}} |out-datatable

write-datatable -ServerInstance "sql_repository" -Database myrepository -TableName SQL_Server_Releases_from_RSS -data $datatable

[Extended Events] list of predicates that you can use to filter actions

I've been using XE lately to get a list of servers that connect to a server I'm going to upgrade (see http://thebakingdba.blogspot.com/2013/04/extended-events-what-servers-are.html).  That way I know what the downstream effects are beforehand.

However, these are busy servers - even with connection pooling, we can get hundreds of connections a minute. Which means even minimal logging will get big, fast.

So there are two ways to prevent that:

1. Predicates to filter before it hits the target
2. Using a histogram to store in buckets.

However, a histogram can only store one value and a count.  I haven't figured a way to combine the fields (and according to Jonathan Kehayias you can't), so I requested Microsoft add a multi-field histogram (which got closed as Won't Fix: http://connect.microsoft.com/SQLServer/feedback/details/785063/extended-events-a-better-histogram-multiple-value-fields)

So, back to  #1, and looking at Predicates. 

Fortunately, I found this blog post which has a lot of details about it:
http://blogs.msdn.com/b/extended_events/archive/2010/06/24/today-s-subject-predicates.aspx
(yup, a 2-year-old post that's now incredibly relevant)


Specifically,

SELECT name, description,
    (SELECT name FROM sys.dm_xe_packages WHERE guid = o.package_guid) package
FROM sys.dm_xe_objects o
WHERE object_type = 'pred_compare'
ORDER BY name


Which lead me to:

sqlserver.like_i_sql_ansi_string
sqlserver.like_i_sql_unicode_string


From the blog post:
"Calling a pred_compare directly follows this syntax:

package_name.pred_compare(field/pred_source, value)

[...]ADD EVENT wait_info (WHERE package0.greater_than_max_int64(duration, 500)
 "


Okay, I can do that.

 WHERE (
              (sqlserver.like_i_sql_unicode_string(sqlserver.client_hostname,N'laptop%'))
              )

which gives me everything from our laptops.  Close.  Can I do a NOT LIKE?

 WHERE (
              (NOT sqlserver.like_i_sql_unicode_string(sqlserver.client_hostname,N'laptop%'))
              )


Which, yes, works.  
So my query to look for logins not from our laptops, and excluding certain servers, looks like...

CREATE EVENT SESSION [Logins] ON SERVER
ADD EVENT sqlserver.login(
ACTION(sqlserver.client_hostname,sqlserver.client_app_name,sqlserver.database_name)
WHERE ([sqlserver].client_hostname NOT LIKE '%-SERV-%')
              AND (NOT sqlserver.like_i_sql_unicode_string(sqlserver.client_hostname,N'laptop%'))
              )
ADD TARGET package0.ring_buffer(SET max_memory = 4096)

And, typing this in... I see I'm already excluding using a LIKE.  Why'd I go through all this effort again?  Um, knowledge, I guess?  : )

[SSIS 2012] Using SQL Sentry & custom code to send detailed email on failure

(update 2013/05/02 14:45 - added some other fields we care about, like Job Description & Job Category. Also changed to use sp_sentry_dbmail_20, which is installed when you monitor a server with SQL Sentry.  It also now comes from the same address as the other SQL Sentry emails, which is nice)
(update 2013/05/05 01:01 - my pain, your gain. Found a case where, on 2012 RTM, no email is sent.  Read below.)

This is a simple modification of http://thebakingdba.blogspot.com/2012/11/sql-server-2012-ssis-getting-useful.html, which sends an email with specific job failure info, rather than the nondescript "To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report ", which is pretty freaking useless.

We use SQL Sentry, which makes this part simple (they have their own method of logging SSIS failures, but it has several prereqs I don't like - specifically, it changes the step from SSIS to CmdExec, and executes the parameter-laden string).  My original post/SP required you to add a job step to each job - this one gets set on the server.  Just add an SQL Sentry action ("SQL Server Job Agent: Failure") and add "Execute SQL", running the following code, and it takes care of every job on the server.


exec yourdbname.dbo.ssis_job_failure_info_fromjobname @job_name = '<%ObjectName%>'

(where yourdbname is wherever you store the SP)

One note: if the job fails because of an SSIS timeout (see: http://connect.microsoft.com/SQLServer/feedback/details/725840/catalog-executions-getting-cancelled-without-logging-any-error), no email is sent.  That's because, while the job failed, technically the package doesn't know it failed... or something like that.  Read the Connect item for more details.  Supposedly fixed in SP1, but haven't had it happen up to now.

And here's the modified SP:

[WAT] bizarrreness with ISNULL

Coworker came across this, and even found an article by Aaron Bertrand about it.

http://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/

But the practical takeaway we had today, from Aaron's code - ISNULL can cause truncation:

DECLARE @c5 VARCHAR(5);
SELECT 'COALESCE', COALESCE(@c5, 'longer name')
UNION ALL
SELECT 'ISNULL',   ISNULL(@c5,   'longer name');

[Extended Events] What servers are connecting to my SQL Server 2012 box?

(code is complete, soup-to-nuts, but only runs on 2012; I'll modify it for 2008/R2 in another post.)

(update 2013/04/18 Jonathan Kehayias helpfully provided a way to do it by adding the existing_connection event - but there appears to be a bug with the histogram, and it doesn't return the right data.  Connect item if you want to vote on it: https://connect.microsoft.com/SQLServer/feedback/details/785042/extended-events-histogram-does-not-properly-save-existing-connection-results)


First of all, MANY thanks to Jonathon Kehayias for all the XE wisdom and code - I modified the snot out of his http://www.sqlskills.com/blogs/jonathan/tracking-sql-server-database-usage/ lock-tracking-using-a-histogram to build this...

Say I want to figure out what servers are connecting to my SQL Server.  This is something you'd traditionally either poll sysprocesses for periodically, or probably run a trace.  Both have issues, though, especially if they're busy servers.  So, how can we get that?  Extended Events.

The easiest way to do this would be using Extended Events - create a "bucketizer" (now called the histogram), and watch for new logins, saving the servername and incrementing a counter each time it happens.  As a bonus, we could then filter it - exclude particular applications, servers, etc (see the commented out code).  And, since it's a pretty basic XE, overhead is very low.  The one caveat is that you can only get one piece of information out - servername.  I'd LOVE to get the App Name & DB Name as well, but you can only get one piece of information at a time.  Downer.  : - (

One caveat: If you have servers that stay connected, and don't open new connections, you won't see them here.  The obvious exclusion, then, is replication, but maybe your app hangs on for a long time (we've seen that with third-party tools).  Easy answer for those is to use SP_WHOISACTIVE.

Any questions?

[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.".



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