Amigos, we’ve come a long way. In Part 1 we pimped the Azure databases to return health data XML, in Part 2 we made a control database that knew where these databases were at, in Part 3 we hoovered out the XML data we wanted from them via PowerShell, and in Part 4 we shredded this ready for analysis. Tidy. So how do you go about analysing it? Man, this part’s up to you.

Me? First thing I want to know is when the databases are going to go boom. There aint no auto-grow in Azure, so if you’ve gone for 10gb, when it gets there it’ll stop. Dead. Silent. Bar the barely audible whistle of your boss’ boot sailing towards your ass.

So we need a view to identify the potential butt killers and fast. This will do the job:

-- db space alert view: > 70% full
CREATE VIEW dbhealth.vCheckSpaceUsed
AS
SELECT	azdbs.azDatabaseID, 
		azdbs.dbMaxSize, 
		azdbs.dbPctUsed,
		'DB_SIZE' as alertType,
		'DB ' +  azd.azDatabaseName + ' on server ' + azs.azServerName + ' is ' + convert(varchar(14), convert(decimal(8,2), azdbs.dbPctUsed)) + '% full' as alertMsg
FROM dbhealth.azureDBSize azdbs
INNER JOIN dbhealth.azureResultSet azrs
	on azdbs.azResultSetID = azrs.azResultSetID
INNER JOIN dbhealth.azureDatabase azd
	ON azdbs.azDatabaseID = azd.azDatabaseID
INNER JOIN dbhealth.azureServer azs
	ON azd.azServerID = azs.azServerID
WHERE azrs.azResultSetDate > DATEADD(hour, -6, getdate()) -- results in the last six hours
	and azdbs.dbPctUsed > convert(decimal(8,6), 70);
GO

I’ve added a third step to my sql agent job: now the first step polls and retrieves the data, the second one shreds it, and the third runs critical alert checks. Skills.

(Dig that the view restricts results to ones in the last six hours. This is because my polling is every six hours, so I’m just checking the latest result set – i.e. the results obtained by job step one of my agent job. Don’t want to be reporting old records that have since been sorted hey. You’ll need to adjust to suit your polling period.)

My job step three executes the following stored procedure (utilising the above view and database mail):

CREATE PROCEDURE [dbhealth].[processCriticalAlerts]
AS
BEGIN TRY

	DECLARE @alertstogo nvarchar(max) = ''

	SELECT @alertstogo = @alertstogo + alertMsg + CHAR(13) + CHAR(10) -- '<br>' for HTML
	FROM dbhealth.vCheckSpaceUsed

	IF (@alertstogo <> '')
	BEGIN
		EXEC msdb.dbo.sp_send_dbmail
			@profile_name = 'Your DB Mail Profile name',
			@recipients = 'a@b.com;c@b.com',
			@copy_recipients = '',
			@body = @alertstogo,
			--@body_format = 'HTML', -- if you want to use this
			@subject = 'ALERT: SQL Azure Database Size',
			@importance = 'High'
	END
	
END TRY

BEGIN CATCH

		declare @ErrorMessage NVARCHAR(4000);
		declare @ErrorSeverity INT;
		declare @ErrorState INT;

		select	@ErrorMessage = ERROR_MESSAGE(), 
				@ErrorSeverity = ERROR_SEVERITY(), 
				@ErrorState = ERROR_STATE();

		raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);

END CATCH
GO

GRANT EXECUTE ON dbhealth.processCriticalAlerts to HealthCheck;
GO

Job done. On each run of the job an email is sent out for all databases that are more than 70% full. Add more views and more alerts to the procedure as needed but keep them critical only – don’t be an info freako here, save this for SSRS. Job step alerts = need attention right now, SSRS reports = longer-term trend analysis.

The next logical step is then to add custom SSRS reports to Management Studio. I will be doing this in the next few weeks and laying it down for you soon, as there’s good stuff to be had here. But hey, don’t wait for me. Add your critical alerts and get SSRS fired up. Automated alerts, shiny graphs and charts – these are review time gold baby. So go get analysing. You know it makes sense.

As soon as my SSRS Management Studio reports are grooving you’ll be hearing from me. Till then peace people.

Word.

It’s time to get down to business people; in Part 1 we pimped the Azure databases to return health data XML, in Part 2 we made a control database that knew where these databases were at, and in Part 3 we hoovered out the XML data we wanted from them via PowerShell. Sweet. But data aint no good sitting there doing nothing, we gotta make it sing.

So let’s add some tables to our local health database.

-- 01 Table to hold result sets (one per run of the PowerShell Script)
CREATE TABLE [dbhealth].[azureResultSet](
	[azResultSetID] [int] IDENTITY(1,1) NOT NULL,
	[azResultSetDate] [datetime] NOT NULL,
 CONSTRAINT [PK_sazResultSet] PRIMARY KEY CLUSTERED ([azResultSetID] ASC)
)
GO

-- 02 Table to hold a list of the Azure servers that data is being returned from
CREATE TABLE [dbhealth].[azureServer](
	[azServerID] [smallint] IDENTITY(1,1) NOT NULL,
	[azServerName] [varchar](100) NOT NULL,
 CONSTRAINT [PK_sazServer] PRIMARY KEY CLUSTERED ([azServerID] ASC)
)
GO

-- 03 Table to hold a list of the databases that data is being returned from
CREATE TABLE [dbhealth].[azureDatabase](
	[azDatabaseID] [smallint] IDENTITY(1,1) NOT NULL,
	[azServerID] [smallint] NOT NULL,
	[azDatabaseName] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_sazDatabase] PRIMARY KEY CLUSTERED ([azDatabaseID] ASC)
)
GO

ALTER TABLE [dbhealth].[azureDatabase]  WITH CHECK ADD  CONSTRAINT [FK_azureDatabase_azureServer] FOREIGN KEY([azServerID])
REFERENCES [dbhealth].[azureServer] ([azServerID])
GO

ALTER TABLE [dbhealth].[azureDatabase] CHECK CONSTRAINT [FK_azureDatabase_azureServer]
GO

-- 04 Table to hold a list of the databases in the given result set that had an error returning data
CREATE TABLE [dbhealth].[azureDBError](
	[azResultSetID] [int] NOT NULL,
	[azDatabaseID] [smallint] NOT NULL,
	[azDBError] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_azureDBError] PRIMARY KEY CLUSTERED ([azResultSetID] ASC, [azDatabaseID] ASC)
)
GO

ALTER TABLE [dbhealth].[azureDBError]  WITH CHECK ADD  CONSTRAINT [FK_azureDBError_azureDatabase] FOREIGN KEY([azDatabaseID])
REFERENCES [dbhealth].[azureDatabase] ([azDatabaseID])
GO

ALTER TABLE [dbhealth].[azureDBError] CHECK CONSTRAINT [FK_azureDBError_azureDatabase]
GO

ALTER TABLE [dbhealth].[azureDBError]  WITH CHECK ADD  CONSTRAINT [FK_azureDBError_azureResultSet1] FOREIGN KEY([azResultSetID])
REFERENCES [dbhealth].[azureResultSet] ([azResultSetID])
GO

ALTER TABLE [dbhealth].[azureDBError] CHECK CONSTRAINT [FK_azureDBError_azureResultSet1]
GO

ALTER TABLE [dbhealth].[azureDBError] ADD  CONSTRAINT [DF_azureDBError_azDBError]  DEFAULT ('') FOR [azDBError]
GO

-- 05 Table to hold the sizes of the databases in the given result set
CREATE TABLE [dbhealth].[azureDBSize](
	[azResultSetID] [int] NOT NULL,
	[azDatabaseID] [smallint] NOT NULL,
	[dbSize] [decimal](18, 9) NOT NULL,
	[dbMaxSize] [decimal](18, 9) NOT NULL,
	[dbPctUsed] [decimal](8, 6) NOT NULL,
 CONSTRAINT [PK_azureDBSize_1] PRIMARY KEY CLUSTERED ([azResultSetID] ASC,[azDatabaseID] ASC)
)
GO

ALTER TABLE [dbhealth].[azureDBSize]  WITH CHECK ADD  CONSTRAINT [FK_azureDBSize_azureDatabase] FOREIGN KEY([azDatabaseID])
REFERENCES [dbhealth].[azureDatabase] ([azDatabaseID])
GO

ALTER TABLE [dbhealth].[azureDBSize] CHECK CONSTRAINT [FK_azureDBSize_azureDatabase]
GO

ALTER TABLE [dbhealth].[azureDBSize]  WITH CHECK ADD  CONSTRAINT [FK_azureDBSize_azureResultSet] FOREIGN KEY([azResultSetID])
REFERENCES [dbhealth].[azureResultSet] ([azResultSetID])
GO

ALTER TABLE [dbhealth].[azureDBSize] CHECK CONSTRAINT [FK_azureDBSize_azureResultSet]
GO

-- 06 Table to hold the number of connections for the databases in the given result set
CREATE TABLE [dbhealth].[azureDBConnections](
	[azResultSetID] [int] NOT NULL,
	[azDatabaseID] [smallint] NOT NULL,
	[currentConnections] [bigint] NOT NULL,
	[lastRequestTime] [datetime] NOT NULL,
 CONSTRAINT [PK_azureDBConnections_1] PRIMARY KEY CLUSTERED ([azResultSetID] ASC,[azDatabaseID] ASC)
)
GO

ALTER TABLE [dbhealth].[azureDBConnections]  WITH CHECK ADD  CONSTRAINT [FK_azureDBConnections_azureDatabase] FOREIGN KEY([azDatabaseID])
REFERENCES [dbhealth].[azureDatabase] ([azDatabaseID])
GO

ALTER TABLE [dbhealth].[azureDBConnections] CHECK CONSTRAINT [FK_azureDBConnections_azureDatabase]
GO

ALTER TABLE [dbhealth].[azureDBConnections]  WITH CHECK ADD  CONSTRAINT [FK_azureDBConnections_azureResultSet] FOREIGN KEY([azResultSetID])
REFERENCES [dbhealth].[azureResultSet] ([azResultSetID])
GO

ALTER TABLE [dbhealth].[azureDBConnections] CHECK CONSTRAINT [FK_azureDBConnections_azureResultSet]
GO

-- 07 Table to hold the object sizes for the databases in the given result set
CREATE TABLE [dbhealth].[azureDBObjectSize](
	[azResultSetID] [int] NOT NULL,
	[azDatabaseID] [smallint] NOT NULL,
	[rowRank] [smallint] NOT NULL,
	[objectType] [varchar](80) NOT NULL,
	[objectName] [nvarchar](100) NOT NULL,
	[countRows] [bigint] NOT NULL,
	[sizeMB] [decimal](18, 9) NOT NULL,
 CONSTRAINT [PK_azureDBTableCounts] PRIMARY KEY CLUSTERED 
(
	[azResultSetID] ASC,
	[azDatabaseID] ASC,
	[rowRank] ASC
)
)
GO

ALTER TABLE [dbhealth].[azureDBObjectSize]  WITH CHECK ADD  CONSTRAINT [FK_azureDBObjectSize_azureDatabase] FOREIGN KEY([azDatabaseID])
REFERENCES [dbhealth].[azureDatabase] ([azDatabaseID])
GO

ALTER TABLE [dbhealth].[azureDBObjectSize] CHECK CONSTRAINT [FK_azureDBObjectSize_azureDatabase]
GO

ALTER TABLE [dbhealth].[azureDBObjectSize]  WITH CHECK ADD  CONSTRAINT [FK_azureDBObjectSize_azureResultSet] FOREIGN KEY([azResultSetID])
REFERENCES [dbhealth].[azureResultSet] ([azResultSetID])
GO

ALTER TABLE [dbhealth].[azureDBObjectSize] CHECK CONSTRAINT [FK_azureDBObjectSize_azureResultSet]
GO

-- 08 Table to hold the exec CPU figures for the databases in the given result set
CREATE TABLE [dbhealth].[azureDBExecCPU](
	[azResultSetID] [int] NOT NULL,
	[azDatabaseID] [smallint] NOT NULL,
	[rowRank] [smallint] NOT NULL,
	[avgCPUTime] [decimal](18, 9) NOT NULL,
	[avgReads] [bigint] NOT NULL,
	[avgWrites] [bigint] NOT NULL,
	[owningObjName] [nvarchar](128) NOT NULL,
	[sqlText] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_azureDBExecCPU] PRIMARY KEY CLUSTERED 
(
	[azResultSetID] ASC,
	[azDatabaseID] ASC,
	[rowRank] ASC
)
)

GO

ALTER TABLE [dbhealth].[azureDBExecCPU]  WITH CHECK ADD  CONSTRAINT [FK_azureDBExecCPU_azureDatabase] FOREIGN KEY([azDatabaseID])
REFERENCES [dbhealth].[azureDatabase] ([azDatabaseID])
GO

ALTER TABLE [dbhealth].[azureDBExecCPU] CHECK CONSTRAINT [FK_azureDBExecCPU_azureDatabase]
GO

ALTER TABLE [dbhealth].[azureDBExecCPU]  WITH CHECK ADD  CONSTRAINT [FK_azureDBExecCPU_azureResultSet] FOREIGN KEY([azResultSetID])
REFERENCES [dbhealth].[azureResultSet] ([azResultSetID])
GO

-- 09 Table to hold the plan CPU figures for the databases in the given result set
CREATE TABLE [dbhealth].[azureDBPlanCPU](
	[azResultSetID] [int] NOT NULL,
	[azDatabaseID] [smallint] NOT NULL,
	[rowRank] [smallint] NOT NULL,
	[totalWorkerTime] [bigint] NOT NULL,
	[executionCount] [bigint] NOT NULL,
	[encrypted] [bit] NOT NULL,
	[sqlText] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_azureDBPlanCPU] PRIMARY KEY CLUSTERED 
(
	[azResultSetID] ASC,
	[azDatabaseID] ASC,
	[rowRank] ASC
)
)
GO

ALTER TABLE [dbhealth].[azureDBPlanCPU]  WITH CHECK ADD  CONSTRAINT [FK_azureDBPlanCPU_azureDatabase] FOREIGN KEY([azDatabaseID])
REFERENCES [dbhealth].[azureDatabase] ([azDatabaseID])
GO

ALTER TABLE [dbhealth].[azureDBPlanCPU] CHECK CONSTRAINT [FK_azureDBPlanCPU_azureDatabase]
GO

ALTER TABLE [dbhealth].[azureDBPlanCPU]  WITH CHECK ADD  CONSTRAINT [FK_azureDBPlanCPU_azureResultSet] FOREIGN KEY([azResultSetID])
REFERENCES [dbhealth].[azureResultSet] ([azResultSetID])
GO

ALTER TABLE [dbhealth].[azureDBPlanCPU] CHECK CONSTRAINT [FK_azureDBPlanCPU_azureResultSet]
GO

Tidy. So what exactly we got now?

1. A table (azureResultSet) to hold result sets, one per run. If you check the PowerShell script in Part 3 you’ll dig that we instantiate a “batch Date” variable prior to the loop and all database data returned is stamped with the same date, so we know which are in the same batch
2. A table (azureServer) to hold a list of the Azure servers that data is being returned from
3. A table (azureDatabase) to hold a list of the databases that data is being returned from
4. A table (azureDBError) to hold a list of the databases in the given result set that had an error returning data
5. A table (azureDBSize) to hold the sizes of the databases in the given result set
6. A table (azureDBConnections) to hold the number of connections for the databases in the given result set
7. A table (azureDBObjectSize) to hold the object sizes for the databases in the given result set
8. A table (azureDBExecCPU) to hold the exec CPU figures for the databases in the given result set
9. A table (azureDBPlanCPU) to hold the plan CPU figures for the databases in the given result set

All tables bar the result set (azureResultSet), server (azureServer), and database (azureDatabase), have FKs to both the database and the result set. There is also an FK between the server and the database tables. Tight.

Dig also that we’ve not granted any permissions to the dbHealthCheck user and that’s because we’re only going to grant them EXECUTE permission to the stored procedure that does the shredding. They don’t need direct table access so they’re not getting it. Least privilege all the way with me baby.

So let’s get shredding. I rock one stored procedure that shreds the data for all unprocessed results (records in the incomingXML table).

CREATE PROC dbhealth.processDBDashboardStats
AS

DECLARE @azServerName VARCHAR(100)
DECLARE @azDatabaseName VARCHAR(100)
DECLARE @azServerID SMALLINT
DECLARE @azDatabaseID SMALLINT
DECLARE @azResultSetID INT

SET NOCOUNT ON;

BEGIN TRY

	-- see if we have a batch or batches to process and if not just exit
	IF (SELECT COUNT(1) FROM dbhealth.incomingXML) = 0 RETURN
	
	BEGIN TRAN
	
	-- add any new servers we haven't processed before
	-- (I don't care for DISTINCT but needed here as xml methods are 
	-- not allowed in GROUP BY clauses)
	-- done as a MERGE as I may want to include update logic down the line
	MERGE dbhealth.azureServer AS TARGET
    USING 
    (
    SELECT DISTINCT N.C.value('@serverName', 'nvarchar(100)')
	FROM dbhealth.incomingXML
	CROSS APPLY ixXML.nodes('//dbchecks') N(C)
    ) AS SOURCE (azServerName)
    ON (TARGET.azServerName = SOURCE.azServerName)
	WHEN NOT MATCHED THEN	
	    INSERT (azServerName)
	    VALUES (source.azServerName);

	-- add any new databases we haven't processed before
	-- again, done as a MERGE as I may want to include update logic down the line
	MERGE dbhealth.azureDatabase AS TARGET
    USING 
    (
    SELECT	N.C.value('@databaseName', 'nvarchar(100)') as azDatabaseName,
			azServ.azServerID
	FROM dbhealth.incomingXML iXml
	CROSS APPLY ixXML.nodes('//dbchecks') N(C) 
	INNER JOIN dbhealth.azureServer azServ
		ON N.C.value('@serverName', 'nvarchar(100)') = azServ.azServerName
    ) AS SOURCE (azDatabaseName, azServerID)
    ON (TARGET.azDatabaseName = SOURCE.azDatabaseName
		AND TARGET.azServerID = SOURCE.azServerID)
	WHEN NOT MATCHED THEN	
	    INSERT (azServerID, azDatabaseName)
	    VALUES (SOURCE.azServerID, SOURCE.azDatabaseName);
	
	-- add the result set batches
	INSERT INTO dbhealth.azureResultSet(azResultSetDate)
	SELECT  azResultSetDate
	FROM dbhealth.incomingXML
	GROUP BY azResultSetDate
	
	-- add any error records (db failed to return data)
	INSERT INTO dbhealth.azureDBError(azResultSetID, azDatabaseID, azDBError)
	SELECT	azRSet.azResultSetID,
			azDB.azDatabaseID,
			N.C.value('errordesc[1]', 'nvarchar(max)') AS sqlText
	FROM dbhealth.incomingXML iXml
	CROSS APPLY ixXML.nodes('//dbchecks') N(C) 
	INNER JOIN dbhealth.azureServer azServ
		ON N.C.value('@serverName', 'nvarchar(100)') = azServ.azServerName
	INNER JOIN dbhealth.azureDatabase azDB
		ON N.C.value('@databaseName', 'nvarchar(100)') = azDB.azDatabaseName
		AND azServ.azServerID = azDB.azServerID
	INNER JOIN dbhealth.azureResultSet azRSet
		on iXML.azResultSetDate = azRSet.azResultSetDate
	WHERE iXml.ixXMLType = 'DB CHECK: ERROR'
	
	-- add the db size records
	INSERT dbhealth.azureDBSize(azResultSetID, azDatabaseID, dbSize, dbMaxSize, dbPctUsed)
	SELECT	azRSet.azResultSetID,
			azDB.azDatabaseID,
			N.C.value('dbcheck_size[1]/@dbSizeMB', 'decimal(18,9)') AS dbSize,
			N.C.value('dbcheck_size[1]/@dbSizeMB_Max', 'decimal(18,9)') AS dbMaxSize,
			N.C.value('dbcheck_size[1]/@dbSize_PctUsed', 'decimal(8,6)') AS dbPctUsed
	FROM dbhealth.incomingXML iXml
	CROSS APPLY ixXML.nodes('//dbchecks') N(C) 
	INNER JOIN dbhealth.azureServer azServ
		ON N.C.value('@serverName', 'nvarchar(100)') = azServ.azServerName
	INNER JOIN dbhealth.azureDatabase azDB
		ON N.C.value('@databaseName', 'nvarchar(100)') = azDB.azDatabaseName
		AND azServ.azServerID = azDB.azServerID
	INNER JOIN dbhealth.azureResultSet azRSet
		on iXML.azResultSetDate = azRSet.azResultSetDate
	WHERE iXml.ixXMLType = 'DB CHECK'
		
	-- get the connections
	INSERT dbhealth.azureDBConnections(azResultSetID, azDatabaseID, currentConnections, lastRequestTime)
	SELECT	azRSet.azResultSetID,
			azDB.azDatabaseID,
			N.C.value('dbcheck_connections[1]/@connections', 'bigint') AS currentConnections,
			N.C.value('dbcheck_connections[1]/@lastrequestendtime', 'datetime') AS lastRequestTime
	FROM dbhealth.incomingXML iXml
	CROSS APPLY ixXML.nodes('//dbchecks') N(C) 
	INNER JOIN dbhealth.azureServer azServ
		ON N.C.value('@serverName', 'nvarchar(100)') = azServ.azServerName
	INNER JOIN dbhealth.azureDatabase azDB
		ON N.C.value('@databaseName', 'nvarchar(100)') = azDB.azDatabaseName
		AND azServ.azServerID = azDB.azServerID
	INNER JOIN dbhealth.azureResultSet azRSet
		on iXML.azResultSetDate = azRSet.azResultSetDate
	WHERE iXml.ixXMLType = 'DB CHECK'
	
	-- get the object size data
	INSERT dbhealth.azureDBObjectSize(azResultSetID, azDatabaseID, rowRank, objectType, objectName, countRows, sizeMB)
	SELECT	azRSet.azResultSetID,
			azDB.azDatabaseID,
			N2.C2.value('@rowRank[1]', 'smallint') AS rowRank,
			N2.C2.value('@objectType[1]', 'varchar(80)') AS objectType,
			N2.C2.value('@objectName[1]', 'nvarchar(100)') AS objectName,
			N2.C2.value('@countRows[1]', 'bigint') AS countRows,
			N2.C2.value('@sizeMB[1]', 'decimal(18,9)') AS sizeMB
	FROM dbhealth.incomingXML iXml
	CROSS APPLY ixXML.nodes('//dbchecks') N(C)
	CROSS APPLY N.C.nodes('//dbcheck_objsizes//dbcheck_objsize') N2(C2)  
	INNER JOIN dbhealth.azureServer azServ
		ON N.C.value('@serverName[1]', 'nvarchar(100)') = azServ.azServerName
	INNER JOIN dbhealth.azureDatabase azDB
		ON N.C.value('@databaseName[1]', 'nvarchar(100)') = azDB.azDatabaseName
		AND azServ.azServerID = azDB.azServerID
	INNER JOIN dbhealth.azureResultSet azRSet
		on iXML.azResultSetDate = azRSet.azResultSetDate
	WHERE iXml.ixXMLType = 'DB CHECK'
	
	-- get the exec data (CPU)
	INSERT dbhealth.azureDBExecCPU(azResultSetID, azDatabaseID, rowRank, avgCPUTime, avgReads, avgWrites, owningObjName, sqlText)
	SELECT	azRSet.azResultSetID,
			azDB.azDatabaseID,
			N2.C2.value('@rowRank[1]', 'smallint') AS rowRank,
			N2.C2.value('@avgCPUTime[1]', 'decimal(18,9)') AS avgCPUTime,
			N2.C2.value('@avgReads[1]', 'bigint') AS avgReads,
			N2.C2.value('@avgWrites[1]', 'bigint') AS avgWrites,
			N2.C2.value('@owningObjName[1]', 'nvarchar(128)') AS owningObjName,
			N2.C2.value('sqlText[1]', 'nvarchar(max)') AS sqlText
	FROM dbhealth.incomingXML iXml
	CROSS APPLY ixXML.nodes('//dbchecks') N(C)
	CROSS APPLY N.C.nodes('//dbcheck_execs_cpu//dbcheck_exec_cpu') N2(C2)  
	INNER JOIN dbhealth.azureServer azServ
		ON N.C.value('@serverName[1]', 'nvarchar(100)') = azServ.azServerName
	INNER JOIN dbhealth.azureDatabase azDB
		ON N.C.value('@databaseName[1]', 'nvarchar(100)') = azDB.azDatabaseName
		AND azServ.azServerID = azDB.azServerID
	INNER JOIN dbhealth.azureResultSet azRSet
		on iXML.azResultSetDate = azRSet.azResultSetDate
	WHERE iXml.ixXMLType = 'DB CHECK'
	
	-- get the query plan data
	INSERT dbhealth.azureDBPlanCPU(azResultSetID, azDatabaseID, rowRank, totalWorkerTime, executionCount, encrypted, sqlText)
	SELECT	azRSet.azResultSetID,
			azDB.azDatabaseID,
			N2.C2.value('@rowRank[1]', 'smallint') AS rowRank,
			N2.C2.value('@totalWorkerTime[1]', 'bigint') AS totalWorkerTime,
			N2.C2.value('@executionCount', 'bigint') AS executionCount,
			N2.C2.value('@encrypted[1]', 'bit') AS encrypted,
			N2.C2.value('sqlText[1]', 'nvarchar(max)') AS sqlText
	FROM dbhealth.incomingXML iXml
	CROSS APPLY ixXML.nodes('//dbchecks') N(C)
	CROSS APPLY N.C.nodes('//dbcheck_plans_cpu//dbcheck_plan_cpu') N2(C2)  
	INNER JOIN dbhealth.azureServer azServ
		ON N.C.value('@serverName[1]', 'nvarchar(100)') = azServ.azServerName
	INNER JOIN dbhealth.azureDatabase azDB
		ON N.C.value('@databaseName[1]', 'nvarchar(100)') = azDB.azDatabaseName
		AND azServ.azServerID = azDB.azServerID
	INNER JOIN dbhealth.azureResultSet azRSet
		on iXML.azResultSetDate = azRSet.azResultSetDate
	WHERE iXml.ixXMLType = 'DB CHECK'
		
	-- now delete the incoming xml records that we have processed
	delete imxl
	from dbhealth.incomingXML imxl
	inner join dbhealth.azureResultSet azr	
		on imxl.azResultSetDate = azr.azResultSetDate
	
	COMMIT TRAN
	
END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000)
	DECLARE @ErrorSeverity INT
	DECLARE @ErrorState INT

	SELECT	@ErrorMessage = ERROR_MESSAGE(), 
			@ErrorSeverity = ERROR_SEVERITY(), 
			@ErrorState = ERROR_STATE();
			
	ROLLBACK TRAN;

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
	
END CATCH
GO

GRANT EXECUTE ON dbhealth.processDBDashboardStats to HealthCheck;
GO

Run this and you’ll be laughing. Smooth. I run this procedure straight after the PowerShell script, so I have fresh data coming in every six hours via my agent job: PowerShell script Job Step 1, stored procedure Job Step 2. Sorted. The schedule of your collection will depend on the volatility of your databases and you may want to start at an hour and then lengthen from there depending on what you’re seeing coming back.

In the next instalments I’ll check out ways to analyse the data, starting with Part 5 showing how to generate critical alert emails. So till then you best get hoovering and shredding so you’ve got something to work with.

Word.

Yo, yo, yo. Now we get to the fun stuff, collecting our health data via PowerShell. Tidy. If you aint got down with Part 1 or Part 2 then you best go hit them first, else this aint gonna make much sense. You knows it.

So what we got at this point?

1. All our Auzure databases have a UDF that returns us sweet XML health data
2. We have a control database that holds the details of these Azure databases and the servers they on

So what do we need now? Well people, we need a local health database to keep the data in, that’s what. We’ll dig on processing the data in Part 4 and for now just concentrate on getting it. So let’s create the health database. Mine’s on a local SQL 2008 R2 box, as monitoring cloud databases from the cloud probably aint the smartest idea – as if the data centre in question is having issues then you aint got no monitoring! So keep it local people, keep it local. You can always use SQL Express if your boss is a tight-ass mofo.

Let’s get the health database up and running:

USE master
GO

CREATE LOGIN dbHealthCheck
WITH PASSWORD = 'An0th3rC0mpl3x0n3'
GO

-- if doing this in Azure, create a master login for the user as well
/*
CREATE USER dbHealthCheck
FOR LOGIN dbHealthCheck
WITH DEFAULT_SCHEMA = dbo
GO
*/

-- now create a database named AzureDBHealth
-- you'll need to alter this line and set the defaults to match your own standards
CREATE DATABASE AzureDBHealth;
GO

USE AzureDBHealth
GO

CREATE SCHEMA [dbhealth] AUTHORIZATION [dbo]
GO

CREATE USER dbHealthCheck
FOR LOGIN dbHealthCheck
WITH DEFAULT_SCHEMA = dbhealth
GO

CREATE ROLE HealthCheck
GO

-- add a table to receive the incoming XML
CREATE TABLE [dbhealth].[incomingXML](
	[ixID] [int] IDENTITY(1,1) NOT NULL,
	[ixXML] [xml] NOT NULL,
	[ixXMLType] [varchar](20) NOT NULL,
	[azResultSetDate] [datetime] NOT NULL,
	CONSTRAINT [PK_incomingXML] PRIMARY KEY CLUSTERED ([ixID] ASC)
)
GO

-- add a stored procedure to insert the data
CREATE PROCEDURE [dbhealth].[setIncomingXML]
	(
	@ixXML as xml,
	@ixXMLType as varchar(20),
	@azResultSetDate datetime
	)
AS

BEGIN TRY

	BEGIN TRANSACTION

	insert into dbhealth.incomingXML(ixXML, ixXMLType, azResultSetDate)
	values (@ixXML, @ixXMLType, @azResultSetDate)

	COMMIT TRANSACTION

END TRY

BEGIN CATCH

	declare @ErrorMessage NVARCHAR(4000);
	declare @ErrorSeverity INT;
	declare @ErrorState INT;

	select	@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

	ROLLBACK TRANSACTION

	raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);

END CATCH
GO

-- grant our role permission to the stored procedure
GRANT EXECUTE ON dbhealth.setIncomingXML to HealthCheck
GO

-- add our user to the role
EXEC sp_addrolemember N'HealthCheck', N'dbHealthCheck'
GO

Smooth, now we’re really motoring, so let’s hook it all up. Enter the beast that is PowerShell!

(Note: You’ll want to save the below script in a file, mine’s saved as AzureDBHealthChecker.PS1 but you go with whatever turns you on.)

param
(
  [string]$controldbname, 
  [string]$controldbserver,
  [string]$controluserid ,
  [string]$controlpwd, 
  [string]$healthdbname, 
  [string]$healthdbserver,
  [string]$healthdbuserid,
  [string]$healthdbpwd, 
  [int]$givenServerOnly
)

function getAzureDBsToHealthCheck($controldbname, $controldbserver, $controluserid, $controlpwd, $healthdbname, $healthdbserver, $healthdbuserid, $healthdbpwd, $givenServerOnly)
{
 
    # connect to the local health db and fail the job if we can't connect or the insert stored procedure has issues
    try
    {
        $sqlConnectionHealthDB = new-object System.Data.SqlClient.SqlConnection;
	$sqlConnectionHealthDB.ConnectionString = "Server=" + $healthdbserver + ";Database=" + $healthdbname + ";User ID=" + $healthdbuserid + ";Password=" + $healthdbpwd + ";";
        $sqlConnectionHealthDB.Open()
        
        # create a command to use in a loop later rather then creating one each time
        $sqlCommandHealthDB = new-Object System.Data.SqlClient.SqlCommand("dbhealth.setIncomingXML", $sqlConnectionHealthDB)
        $sqlCommandHealthDB.CommandType = [System.Data.CommandType]::StoredProcedure
        $sqlCommandHealthDB.Parameters.Add("@ixXML",[system.data.SqlDbType]::Xml) | out-Null
        $sqlCommandHealthDB.Parameters.Add("@ixXMLType",[system.data.SqlDbType]::Varchar, 20) | out-Null
        $sqlCommandHealthDB.Parameters.Add("@azResultSetDate",[system.data.SqlDbType]::DateTime) | out-Null
        $batchDate = Get-Date -format f
    }
    catch
    {
    
        $errorMsg = "Cannot connect to the Health database " + $healthdbname + " on server " + $healthdbserver + ". Error: " + $_.Exception.ToString()
	throw $errorMsg
    }
    
    # same drill for the azure control db, do one if we can't connect or get a list of databases to poll
    # to account for transient Azure connection errors, we try three times in a loop
    $controlCurrentRetry = 0;
    $controlSuccess = $false;
    do
    {
        try
        {
            $sqlConnection = new-object System.Data.SqlClient.SqlConnection;
            $sqlConnection.ConnectionString = "Server=tcp:" + $controldbserver + ".database.windows.net;Database=" + $controldbname + ";User ID=" + $controluserid + "@" + $controldbserver + ";Password=" + $controlpwd + ";Trusted_Connection=False;Encrypt=True;";
            $sqlConnection.Open()
     
            # read the all databases view (for either just the given server or all servers)
            $sqlCommand = $sqlConnection.CreateCommand();
            if ($givenServerOnly -eq 1)
                {
                $sqlCommand.CommandText = "select DBServerName, DatabaseName, WSVersion from dbo.vAllMRDatabases where DBServerName = '" + $controldbserver + ".database.windows.net' and [DatabaseActive] = 1 and isInternal = 0";
                }
            else
                {
                $sqlCommand.CommandText = "select DBServerName, DatabaseName, WSVersion from dbo.vAllMRDatabases where [DatabaseActive] = 1 and isInternal = 0";
                }
            #Execute the Command
            $sqlReader = $sqlCommand.ExecuteReader();
            
            # flag the connection to the control db as ok 
            $controlSuccess = $true;
     
            while ($sqlReader.Read()) 
            {
                # Get the xml from each database
                $azureDBCurrentRetry = 0;
                $azureDBSuccess = $false;
                do
                {
                    try
                    {
                        $sqlConnectionDB = new-object System.Data.SqlClient.SqlConnection;
                        $sqlConnectionDB.ConnectionString = "Server=tcp:" + $sqlReader["DBServerName"] + ";Database=" + $sqlReader["DatabaseName"] + ";User ID=" + $controluserid + "@" + $sqlReader["DBServerName"].Replace(".database.windows.net", "") + ";Password=" + $controlpwd + ";Trusted_Connection=False;Encrypt=True;"; 
                        $sqlConnectionDB.Open()
                        $sqlCommandDB = $sqlConnectionDB.CreateCommand()
                        $dbsql = "select dbhealth.fnProduceDBDashboardStats(0)" # @sqlTextLimit = 0 (full sql)
                        $sqlCommandDB.CommandText = $dbsql
                        $hcxml = $sqlCommandDB.ExecuteScalar()
                        # Store the xml in the health database
                        $sqlCommandHealthDB.Parameters['@ixXML'].value = $hcxml.replace("'", "''")
                        $sqlCommandHealthDB.Parameters['@ixXMLType'].value = "DB CHECK"
                        $sqlCommandHealthDB.Parameters['@azResultSetDate'].value = $batchDate 
                        $Return = $sqlCommandHealthDB.ExecuteNonQuery()
                        $sqlConnectionDB.Close()
                        # flag the connection to the Azure db as ok 
                        $azureDBSuccess = $true;
                    }
                    catch
                    {
                        if ($azureDBCurrentRetry -gt 3)
                        {
                            # tried three times but with no luck
                            # flag the connection to the Azure db as ok and move on
                            # (we can account for this in the processing as the database will have no error or data row
                            # so we'll know it had issues)
                            # try to log the error here anyway to note why this db is not accessible
                            try
                            {
                                $xmlBatchDate = Get-Date -format s
                                $errorXML = "<dbchecks runDate=""" + $xmlBatchDate.ToString() + """"
                                $errorXML += " databaseName=""" + $sqlReader["DatabaseName"] + """"
                                $errorXML += " serverName=""" + $sqlReader["DBServerName"].Replace("database.windows.net", "") +""">"
                                $errorXML += "<errordesc>" + $_.Exception.ToString() + "</errordesc>"
                                $errorXML += "</dbchecks>"
                                $sqlCommandHealthDB.Parameters['@ixXML'].value = $errorXML
                                $sqlCommandHealthDB.Parameters['@ixXMLType'].value = "DB CHECK: ERROR"
                                $sqlCommandHealthDB.Parameters['@azResultSetDate'].value = $batchDate 
                                $Return = $sqlCommandHealthDB.ExecuteNonQuery()
                            }
                            catch
                            {
                            }
                        # flag as success so we can move onto the next database
                        $azureDBSuccess = $true;
                        }
                        else
                        {
                        # short pause before trying again
                        Start-Sleep -s 1;
                        }
                        $azureDBCurrentRetry = $azureDBCurrentRetry + 1;  
                        
                    }
                } 
                while (!$azureDBSuccess);
            };
         
            # Close the database connections
            $sqlConnectionHealthDB.Close();
            $sqlConnection.Close();
        }
        catch
        {
            $errorMsg = "Cannot connect to the Azure Control database " + $controldbname + " on server " + $controldbserver + ".database.windows.net. Error: " + $_.Exception.ToString()
            if ($controlCurrentRetry -gt 3)
            {
            # tried three times but with no luck
            throw $errorMsg
            }
            else
            {
            # short pause before trying again
            Start-Sleep -s 1;
            }
            $controlCurrentRetry = $controlCurrentRetry + 1;
        }
    } 
    while (!$controlSuccess);
}

getAzureDBsToHealthCheck $controldbname $controldbserver $controluserid $controlpwd $healthdbname $healthdbserver $healthdbuserid $healthdbpwd $givenServerOnly

All good stuff, what-what. You’ll notice the retry logic in there when talking to Azure databases, this is because you do get transient errors back from Azure – whilst it’s doing some magic – and failing on the first try would be foolish, so we have three goes and bail out if there is still problems (as these are probably more serious then transient errors). If you want the full low-down on transient errors go here and pray your dev guys both know about and have catered for these.

The script does the following:

1. Logs on to the Azure control database and gets back a list of databases – for all data centres or just the one the control database is on, your choice
2. Loops through these databases connecting to each one, retrieving the health data and inserting it into the local health database

It exists if it can’t connect to control or the local health database but does not fail if individual calls to the polled databases fail (attempting to record the errors of those that do).

Easy. I run this script at set intervals using a SQL Agent job, using a job step of type PowerShell. In the command window you’ll just need to rock this (the call to the PS1 file on one line to stop Powershell missing some of the parameters):

cd {Path to the directory the file is in – c:\Whatever}
.\AzureDBHealthChecker.PS1 -controldbname “AzureControl” -controldbserver “abcd1234″ -controluserid “dbHealthCheck” -controlpwd “wh8t3V3rduD3″ -healthdbname “AzureDBHealth” -healthdbserver “{Your Local Server}” -healthdbuserid “dbHealthCheck” -healthdbpwd “An0th3rC0mpl3x0n3″ -givenServerOnly 0

If you’ve been using the sample code so far then the passwords above will be good but do just adjust to fit however you’ve gone with it – maybe change to a Windows login for the local server if that’s your thing (I put it as a SQL login in case I did ever have to put the health database in the cloud). Arse-coverage is no bad thing my friends.

So there you have it; pimp your Azure databases to return health data, poll them via a control database, and periodically suck down the health data into a local health database. Bingo. Except that this isn’t really much use if you can’t analyse the data. Well fear not amigos, Part 4 that’s coming up next will do this and sort you right out. Check ya later.

Word.

‘Sup. So if you followed Part 1 (Overview and Azure Databases) then your Azure databases are primed and ready to return health monitoring data. Sweet. All you need now is somewhere to get a list of databases to poll (your little black book).

Enter the control database. Aint no need for it to be fancy and it can be either on-site or in Azure. Me, I’ve got mine in Azure; a copy on each data centre and one on-site, all synched smoothly via Azure Data Synch Services (think peer-to-peer replication – kinda). And why do that? Well, three main reasons:

• High Availability: If the server with the control database is tripping, I just connect to another so I can continue to monitor the rest of my data centres
• High Availability: Web applications can initially connect to the control database to get the name and location of the database they need, so if a data centre is offline, I can restore the databases somewhere else and just change the server links in the databases table
• Disaster Recovery: Multiple synchronised copies in existence

Tidy. You know it makes sense.

This can be pimped up anyway you like; to also hold your Azure Subscription and Web Roles data (steady on the keys and passwords), backup schedules for the databases, server-level data (T&Cs, etc.) – whatever floats your boat. For demo purposes, we’re laying it down simple: a table to hold the SQL Servers and another to hold the databases. Oh, and a view to access this data (no direct table access to the calling user on my watch baby).

Check this (for a SQL Azure DB – do your own thang if creating this on-site):

-- IN MASTER DB

-- NOTE: The login and user will already exist if you've 
-- run the scripts from Part 1
-- if not just remove the comment block before running

/*
CREATE LOGIN dbHealthCheck WITH PASSWORD=N'wh8t3V3rduD3'
GO

CREATE USER dbHealthCheck
	FOR LOGIN dbHealthCheck
	WITH DEFAULT_SCHEMA = dbo
GO
*/

CREATE DATABASE AzureControl -- a 1GB Web Edition db by default
GO

Now open a query window to the AzureControl database and create what we need in there:

CREATE SCHEMA azcontrol AUTHORIZATION dbo
GO

CREATE USER dbHealthCheck FOR LOGIN dbHealthCheck WITH DEFAULT_SCHEMA = azcontrol
GO

CREATE TABLE [azcontrol].[Azure_DBServers]
(
	[DBServerID] [int] IDENTITY(1,1) NOT NULL,
	[DBServerName] [nvarchar](25) NOT NULL, -- 'XXXXXXXXXXX'
	[DBServerFQN] [nvarchar](50) NOT NULL, -- 'XXXXXXXXXXX.database.windows.net'
	[DBServerRegion] [nvarchar](50) NOT NULL, -- 'North Central US'
	[DBServerCity] [nvarchar](50) NOT NULL, -- 	'Chicago IL'
	[DBServerIsActive] [bit] NOT NULL, -- 1
 CONSTRAINT [PK_Azure_DBServers] PRIMARY KEY CLUSTERED([DBServerID] ASC)
)
GO

CREATE TABLE [azcontrol].[Azure_Databases]
(
	[DatabaseID] [int] IDENTITY(1,1) NOT NULL,
	[DBServerID] [int] NOT NULL,
	[DatabaseName] [nvarchar](50) NOT NULL, -- 'TestDB'
	[DatabaseActive] [bit] NOT NULL, -- 1
 CONSTRAINT [PK_Azure_Databases] PRIMARY KEY CLUSTERED ([DatabaseID] ASC)
)
GO

CREATE view [azcontrol].[vAllMRDatabases]
as
select	serv.DBServerFQN as DBServerName,
		db.DatabaseName,
		db.DatabaseActive
from azcontrol.Azure_Databases db
inner join azcontrol.Azure_DBServers serv
	on db.DBServerID = serv.DBServerID
GO

ALTER TABLE [azcontrol].[Azure_Databases]  WITH CHECK ADD 
	CONSTRAINT [FK_Azure_Databases_Azure_DBServers] FOREIGN KEY([DBServerID])
	REFERENCES [azcontrol].[Azure_DBServers] ([DBServerID])
	ON UPDATE CASCADE
	ON DELETE CASCADE
GO

ALTER TABLE [azcontrol].[Azure_Databases] CHECK CONSTRAINT [FK_Azure_Databases_Azure_DBServers]
GO

CREATE ROLE HealthCheck
GO

GRANT SELECT ON azcontrol.vAllMRDatabases TO HealthCheck
GO

EXEC sp_addrolemember N'HealthCheck', N'dbHealthCheck'
GO

Now we’re cooking on gas.

Dig again that we’re going to be connecting from our automated routine as the dbHealthCheck user, who only has permission to see the view, nothing else, and the same user in the monitored Azure databases can only execute the health-data returning function, keeping it all nice and tight. (Actually they can do slightly more as they can see database table metadata – VIEW DEFINITION – and they can access DMVs – VIEW DATABASE STATE – but, since they have no permissions to anything else, it’s cool.)

So go fill up control with your servers and databases. I’ll catch you in Part 3 where we’ll rock PowerShell to hoover the data out into a local on-site Health database. Man your boss is going to be one happy bunny when you let slip that you have a monitoring system that costs almost no Benjamins (just the cost of the control database and you can use SQL Express for the on-site database if you need to). Respect. Right there.

Word.