Another developer gift

Just found this one, looks like it was dropped in right around the time I started at the company.

ALTER procedure [dbo].[usp_Get_RowCount] 
@pStructureRowCount	nvarchar(500),
@pRowCount int output


set nocount on
@SQLStatement varchar(500)

set @SQLStatement = 'Select count(*) From ' + @pStructureRowCount

create table #tempRowCount (----Temporary table created for storing related to current Database
StrucRowCount bigint,

insert into #tempRowCount exec(@SQLStatement)

select @pRowCount = StrucRowcount from #tempRowCount

Windows Updates affected SQL Server?

I had read about the issues with the SQL Agent “Is Alive” checks spamming event logs on clustered SQL Server 2012 and SQL Server 2008 R2 SP instances… and since I have three sets of 2 Node clusters running multiple instances including 2012, 2008R2 and 2008, after reading about that a few months back, I checked my servers. None of them were spamming the logs. They all have (change that to HAD now that I’ve upgraded) SQL 2012 RTM, SQL 2008 R2 SP2, and SQL 2008 SP3. Not a one of them ever had the “dreaded” LooksAlive or CheckServiceAlive entries with Event ID 53 from [sqagtres]. Here’s one connect article that shows some more details and which versions are fixed with what CU’s.

Anyway, so, none of the clusters in my environment have any history of Event ID 53 for SQL Agent. Last night, the System Administrator applied some Windows Updates (I don’t have a “test” SQL Cluster, but all these updates were applied on our Test SQL Servers prior to last night). After the Windows updates were applied, my event logs on ONE server started being spammed with the Agent Alive checks. WTF? I thought this was a SQL Agent bug?

Here’s the list of Updates applied last night. KB2799494, KB2789645, KB2251487, KB2790655, KB2538243, KB890830, KB2790113, KB2789642, KB2797052, KB2687441, KB2596672, KB2778344, KB2645410, KB2792100, KB2538242.


On Monday (since I’ve worked enough this damned weekend already), I’m going to go through the other two clusters and compare Updates that were installed, and base levels of SQL Server. Off hand, I think that both of the other Clusters were running SQL 2012 SP1 already.

Oh, another odd data point for me to consider… I was getting these LooksAlive entries in the event logs for the SQL 2008 instance!

Finally, a parting thought. Installing CU3 for SQL Server 2008 R2 at 2AM (or was it 3AM, damned DST!) on a Sunday morning, I really liked that I had to implement a work around to get the POS CU3 installed. Yeah, the old “Rename the C:\Windows\System32\perf-*-sqlagtctr.dll” file to allow the installer to upgrade your SQL Server bug. Thanks for that MS.

Extended Properties as a Run Book?

Perhaps Run Book isn’t the right terminology, but it’s close. So, in my environment we are a manufacturer of large equipment. We do have a centralized IT organization, but it seems to be more focused on keeping the lights on and trying to catch up on what the business purchases off the shelf or even what some groups have developed on their own.

One thing I’ve struggled with in the past two years, since this company has no defined processes (no ITIL or anything similar) and little to no documentation, has been in capturing what databases are where, who is the “Business Owner”, what the application is, and such. I’ve also started implementing DNS aliases for connection strings so that we can be more portable when moving applications, especially COTS systems where we might not have the understanding to change a set of connection strings. As you may know, I have a number of Powershell scripts that capture the specifics of the servers, instances, database properties, file locations, etc. One thing that these cannot grab are the applications and business representatives. Sure, with my two years of knowledge, I can look at a database server, look at an instance name, look at the name of the database and make a good guess at what it does, where it runs, who I should contact if there are problems or changes are planned… but when onboarding someone new, it would take an incredible amount of time for that new person to build up the same intimacy with the environment.

Previously, the approach was to take the Excel document output from the Database Properties script and add a column with “Owner” which sometimes had a name I could place with it.

Today I received a request for a new blank database via email from a quasi-developer in another location. This person is not in IT but has developed a number of supporting type applications for the local users at his manufacturing site. I thought to myself, I have all the details in this email from the requestor, maybe we should just embed this email to the excel document. But I’m lazy, and this is a very loose requirement, I could be busy, I could be in a procrastination mood and put off the follow up documentation for… well,,, ever. Next, I thought, we have a web based in-house developed tool for requesting services from IT. So I thought, hey, I could make it a requirement that a ticket be created for any new Database needed, and then just copy the ticket number into the spreadsheet. Blah, same thing, when I’m off creating a DB in SSMS, I don’t really want to have to open a spreadsheet on our SharePoint site to update that… still too manual and loose.

So, I thought, why not make it part of the “Create database” process to copy the details from the request ticket into Extended Properties inside the database. What I came up with was setting up four Key, Value pairs to start with. A Description which contains some details of the what the database is used for. An AppOwner key with the requestor name or whomever is identified as the owner. A Ticket key with the number from the request. The final required one is the ConnectionString which contains the DNS alias name created for the application to use. I kind of like it, it’s tidy and neat, and it stays with the database if I restore to a new server. The only maintenance might be to change the AppOwner if they change roles or leave the company.

I’m going to modify my above referenced powershell script to include grabbing these keys and values from every database when I run it. I’ve also been noodling a type of “Self Service portal” where power users like the requestor above can hit a web page form that would then kick off an approval work flow and I could then choose the server to deploy to and the database create and all the above documentation would be created on the fly. That would be really neat, the only flaw would be organizational changes.

In short, I’m going to make it a requirement than any new database request be filled out with our services request system, and require a description of the application just for paperwork reasons… it’s much easier to run a web based report than search a DBA’s PST file for auditing purposes. I’m going to have the DBA team start using the Extended Properties for the database to hold that. I’m going to “Extend” my powershell script to grab those properties for our documentation purposes. I may or may not work on a Provisioning Portal… I might be getting too far ahead of the rest of the IT organization here.

My Generic Event Notifications for SQL Servers

While reading a blog post regarding using server DDL triggers to capture a “CREATE DATABASE” and fire an email to the DBA, I thought that coupling a trigger to sp_send_dbmail and an external executable wasn’t a great idea. I decided that there must be a better way to capture that important event. For me, this would really only occur in a development server where the developers often have enhanced rights to the dev servers. Production is pretty tight. However, my heart is always broken when a developer runs up and asks me to restore an accidentally dropped database on the Dev server, only for me to find out that the same developer created that database without telling me. My backup packages don’t dynamically grab all the online databases when it runs, so if they add a database and don’t tell me, it don’t get backed up.

My first thought was Extended Events. I don’t know much about them, I’ve heard the abstracts and read the rumors, but I haven’t had call to work with them. If I remember correctly, it seemed there was a way to capture the CREATE Database event, but then I was stuck with a data record of the event, and not a handy way to email it to me. Searching for Extended Events Send Email took me quickly to a Johnathan Kehayias awesome answer on the MSDN forums explaining that, no, there’s no real plumbing between Extended Events and the Service Broker. His second answer in the same discussion linked to his blog which is fountain of knowledge and an article explaining Event Notifications vs Extended Events. From there, I saw a link to another article in his blog. Sounded pretty darn close to what I wanted.

That lead to my current version of a generic Event Notification system which I am currently testing on a number of servers. It’s actually a combination of procedures from Jonathan’s articles and Sergey Maskalik’s article. Sergey’s error handling and timeout on the Waitfor along with cleanup of the Conversation Handles coupled with Jonathan’s shredding of the XML message body seems to be a work of art to me. I added in some of my own magic to ensure XACT_ABORT was on, some COALESCE’s to ensure a null value wasn’t concatenated over valid values, and setting this up in a “utility” database and setting “TRUSTWORTHY ON” to allow the execution of sp_send_dbmail in MSDB.

We’ll start with the guts needed to set up and wire up the Service Broker and Queues, Services, and Routes. It’s pretty boiler plate, with the added commands to turn on the Service Broker and set TRUSTWORTHY.

NOTE: Jonathan has visited and brought up a very valid and real security risk. In my approach, I take a utility database and set TRUSTWORTHY=ON to it. Please visit and read Raul Garcia’s article on the risks of the TRUSTWORTHY bit. At the time of my writing this, the database that I deploy this solution to is already restricted to the DBA team and anyone with Sysadmin privileges. In this case, all those principals already have enough access to do what this setting allows without any other work, so I feel the risk in my environment is low. For a more secure solution, I strongly recommend a careful review of your situation, and indeed using certificates to sign the procedures to allow cross-database execution.

USE [master];

--  We could also do this when creating the DatabaseBackup database
--  as part of the initial package run, or update.
--  Trustworthy allows a stored proc in the current database
--   execute SP_SEND_DBMAIL in msdb


USE DatabaseBackup

-- Drop the notification if it exists
            FROM    sys.server_event_notifications
            WHERE   name = N'CaptureDBAEvents' ) 

-- Drop the route if it exists
            FROM    sys.routes
            WHERE   name = N'DBAEventRoute' ) 
        DROP ROUTE [DBAEventRoute];

-- Drop the service if it exists
            WHERE   name = N'DBAEventService' ) 
        DROP SERVICE [DBAEventService];

-- Drop the queue if it exists
            FROM    sys.service_queues
            WHERE   name = N'DBAEventQueue' ) 
        DROP QUEUE [DBAEventQueue];

			FROM MASTER.sys.event_notifications
			WHERE name = N'CaptureDBAEvents' )

--  Create a service broker queue to hold the events

--  Create a service broker service receive the events
ON QUEUE [DBAEventQueue] ([]);

-- Create a service broker route to the service

-- Create the event notification to capture the events
TO SERVICE 'DBAEventService', 'current database';

Right above, while creating the EVENT NOTIFICATION, you can see the event types I have. I decided that while it’s great to have AutoGrowth events sent, that in our current environment, this might be more noise than there is value for, so we have left that out for now. Sure there’s a lot more audit events that I could hit up too, but I felt that the ROLL MEMEBERSHIPS, USER and LOGIN work and the DATABASE create and drop were a great start. Also the DEADLOCK_GRAPH was just a nice freebie.

Next, the guts of this, an Stored Procedure that is generic enough to handle different Event Types and shred as much of the XML as possible into a friendly email message. Right, who doesn’t like a mailbox full of raw XML in the morning? 🙂 Note, the final ELSE in the shredding and Email Body building, so if we decided to add a Event Type, we will always just email off the XML until things are fixed.

USE DatabaseBackup

-- Drop the procedure if it exists
			FROM sys.procedures
            WHERE   name = N'ProcessEvents' ) 
        DROP PROCEDURE [ProcessEvents];

CREATE PROCEDURE [dbo].[ProcessEvents]
    DECLARE @eventType VARCHAR(128);
	DECLARE @messagetypename NVARCHAR(256);

    DECLARE @serverName VARCHAR(128);
    DECLARE @postTime VARCHAR(128);
    DECLARE @databaseName VARCHAR(128);
    DECLARE @duration VARCHAR(128);
    DECLARE @growthPages INT;   
	DECLARE @userName VARCHAR(128);
	DECLARE @loginInfo VARCHAR(256);

    DECLARE @messageBody XML;
	DECLARE @emailTo VARCHAR(50);
	DECLARE @subject varchar(150);

	SET @emailTo = '<DBA TEAM EMAIL HERE>;   

	WHILE (1=1) 
		BEGIN TRY                
			BEGIN TRANSACTION               
				WAITFOR (                        
					RECEIVE TOP(1)    
					@ch = conversation_handle,                                                            
					@messagetypename = message_type_name,                                
					@messagebody = CAST(message_body AS XML)                        
					FROM DBAEventQueue              
				), TIMEOUT 60000;             
				IF (@@ROWCOUNT = 0)              
					ROLLBACK TRANSACTION;                       
				IF (@messagetypename = '')                
					--  Get the common information 
					SELECT @eventType = COALESCE(@messagebody.value('(/EVENT_INSTANCE/EventType)[1]','varchar(128)'),'UNKNOWN'),
						@serverName = COALESCE(@messagebody.value('(/EVENT_INSTANCE/ServerName)[1]','varchar(128)'),'UNKNOWN'),
						@postTime = COALESCE(CAST(@messagebody.value('(/EVENT_INSTANCE/PostTime)[1]','datetime') AS VARCHAR),'UNKNOWN');
					SELECT  @emailBody = 'The following event occurred:' + CHAR(10) 
						+ CAST('Event Type: ' AS CHAR(25)) + @EventType + CHAR(10)
						+ CAST('ServerName: ' AS CHAR(25)) + @ServerName + CHAR(10) 
						+ CAST('PostTime: ' AS CHAR(25)) + @PostTime + CHAR(10);
					-- Now the custom XML fields depending on the Event Type
					IF (@EventType like '%_FILE_AUTO_GROW')
						SELECT @duration = COALESCE(@messagebody.value('(/EVENT_INSTANCE/Duration)[1]','varchar(128)'),'UNKNOWN'),
							@growthPages = COALESCE(@messagebody.value('(/EVENT_INSTANCE/IntegerData)[1]', 'int'),'UNKNOWN'),
							@databaseName = COALESCE(@messagebody.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(128)'),'UNKNOWN');
						SELECT @emailBody = @emailBody
							+ CAST('Duration: ' AS CHAR(25)) + @Duration + CHAR(10) 
							+ CAST('GrowthSize_KB: ' AS CHAR(25)) + CAST(( @GrowthPages * 8 ) AS VARCHAR(20)) + CHAR(10)
							+ CAST('DatabaseName: ' AS CHAR(25)) + @DatabaseName + CHAR(10);
					ELSE IF (@EventType like '%_DATABASE')
						SELECT @userName = COALESCE(@messageBody.value('/EVENT_INSTANCE[1]/LoginName[1]', 'varchar(128)'),'UNKNOWN'),
							@DatabaseName = COALESCE(@messagebody.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(128)'),'UNKNOWN');
						SELECT @emailBody = @emailBody 
							+ CAST('User: ' AS CHAR(25)) + @userName + CHAR(10)
							+ CAST('DatabaseName: ' AS CHAR(25)) + @DatabaseName + CHAR(10);
					ELSE IF (@EventType like '%_LOGIN')
						SELECT @userName = COALESCE(@messageBody.value('/EVENT_INSTANCE[1]/LoginName[1]', 'varchar(128)'),'UNKNOWN'),
							@loginInfo = COALESCE(@messageBody.value('/EVENT_INSTANCE[1]/ObjectName[1]', 'varchar(256)'),'UNKNOWN'),
							@SID = COALESCE(@messageBody.value('/EVENT_INSTANCE[1]/SID[1]', 'varchar(128)'),'UNKNOWN');
						SELECT @emailBody = @emailBody
							+ CAST('User: ' AS CHAR(25)) + @userName + CHAR(10)
							+ CAST('New User: ' AS CHAR(25)) + @loginInfo + CHAR(10)
							+ CAST('New SID: ' AS CHAR(25)) + @SID + CHAR(10);
					ELSE IF (@EventType like '%_ROLE_MEMBER')
						DECLARE @roleName VARCHAR(128);
						DECLARE @command VARCHAR(128);
						SELECT @userName = COALESCE(@messageBody.value('/EVENT_INSTANCE[1]/LoginName[1]', 'varchar(128)'),'UNKNOWN'),
							@loginInfo = COALESCE(@messageBody.value('/EVENT_INSTANCE[1]/ObjectName[1]', 'varchar(256)'),'UNKNOWN'),
							@roleName = COALESCE(@messageBody.value('/EVENT_INSTANCE[1]/RoleName[1]', 'varchar(256)'),'UNKNOWN'),
							@command = COALESCE(@messageBody.value('/EVENT_INSTANCE[1]/TSQLCommand[1]/CommandText[1]', 'varchar(256)'),'UNKNOWN');
						SELECT @emailBody = @emailBody
							+ CAST('User: ' AS CHAR(25)) + @userName + CHAR(10)
							+ CAST('Affected User: ' AS CHAR(25)) + @loginInfo + CHAR(10)
							+ CAST('New Role: ' AS CHAR(25)) + @roleName + CHAR(10)
							+ CAST('Command issued: ' AS CHAR(25)) + @command + CHAR(10);
					ELSE  -- TRAP ALL OTHER EVENTS AND SPIT OUT JUST THE XML - We can pretty it up later :)
						SELECT @emailBody = CAST(@messagebody AS VARCHAR(max));

					-- Send email using Database Mail
					SELECT @subject = @eventType + ' on ' + @serverName;
					EXEC msdb.dbo.sp_send_dbmail                
						@profile_name = 'DBA Email', -- your defined email profile 
						@recipients = @emailTo, -- your email
						@subject = @subject,
						@body = @emailBody;               
				IF (@messagetypename = '')            
					DECLARE @errorcode INT;                          
					DECLARE @errormessage NVARCHAR(3000) ;                 
					-- Extract the error information from the sent message                  
					SET @errorcode = (SELECT @messagebody.value(                        
						N'declare namespace brokerns="";                         
						(/brokerns:Error/brokerns:Code)[1]', 'int'));                  
					SET @errormessage = (SELECT @messagebody.value(                        
						N'declare namespace brokerns="";                        
						(/brokerns:Error/brokerns:Description)[1]', 'nvarchar(3000)'));                  
					-- Log the error 
					END CONVERSATION @ch WITH CLEANUP;                             
				IF (@messagetypename = '')                
					-- End the conversation                        
					END CONVERSATION @ch WITH CLEANUP;                
		END TRY        
		BEGIN CATCH             
			ROLLBACK TRANSACTION;                
			DECLARE @ErrorNum INT;                
			DECLARE @ErrorMsg NVARCHAR(3000);                
			SELECT @ErrorNum = ERROR_NUMBER(), @ErrorMsg = ERROR_MESSAGE();                
			-- log the error                

Finally, let’s activate the new Stored Procedure by altering the Queue. Again this is pretty boiler plate.

-- Activate the procedure with the Queue
          PROCEDURE_NAME = [ProcessEvents],
          MAX_QUEUE_READERS = 1,
          EXECUTE AS OWNER);

Thanks, I hope that helps anyone interested in Event Notifications.

Reporting Services 2008 R2 subscription error

So today we’re setting up new SQL Server 2008 R2 servers from existing SQL Server 2005 server. One of the parts is Reporting Services reports running using Data Driven Subscriptions. I inherited the design of this system, where I feel Reporting Services has been turned sideways to simply use the Enterprise Edition feature of Data Driven Subscriptions to simply allow users to schedule reports to email to end users.

In the existing SQL Server 2005 system, the service account being used to run Reporting Services is a Domain Administrator account (Yeah, I know!), and the “administrator user” who set up the schedules, reports, and subscriptions is in the Local Administrators group in the OS and in the SysAdmin role in SQL Server. Again, this was inherited.

So, when I set this up and installed 2008 R2 and SSRS on the new server, there was absolutely no way I was going to set the service to run under a domain admin, and we’re also enforcing no administrator accounts for developers on the production instances. Code deployments are going through TFS and any DDL changes that they don’t script out in source control will go through the DBA team. Set up SSRS to run with a dedicated AD service account with minimal rights on the OS and in SQL. Everything works via the Web UI. However, no emails were sent for the scheduled reports.

The reports will email the user reqeusting the report with the PDF embedded. The error the ReportServer database table was “rsConfigError” and the error in the trace file was a generic “Configuration Error”. After checking permissions on the data sources for the reports to make sure the configured user had permissions, and trying to set up an execution account yielded no improvement, I switched the service account to run under LocalHost\System account. That yielded no better results, actually there were some errors for AuthzInitializeContextFromSid and Access denied trying to look up the AD account. So then I decided to throw out a curve ball and set the service account to a domain admin account. I know! I did it simply for testing. So after this, the trace log showed new information… about not being able to authenticate the “administrative” user’s account… WTF, where was that coming from!!?? I then added that account in the Administrators group in Windows, and BLAM! Reports were emailed. W.T.F.?!!!?

I switched the service account back to my domain service account (no way I’m running this as a domain admin)… and back to the generic error. So then I started digging to the ReportServer database, and found the “Subscriptions” table with an OwnerID column and a GUID. Cross-referencing with the Users table, and blam, there’s the “administrative” user’s account as the owner. Damn you MS! The user that configures the subscriptions needs elevated permissions to send emails with attachments. And… there’s no way to change the owner of the subscription via the GUI. So I updated the OwnerID column to NT Authority\System, removed the “administrative” user’s account from the Administrators group in Windows. Everything works as expected. Unfortunately, I have no idea of what might not work properly going forward with this manual change. Also, I’ve got to do this work around if the “administrative” user ever creates new reports and schedules and subscribes them for end users.