Centralized Auditing, Data Aggregation, Deployment Processing

I have been experimenting with SERVICE BROKER, XML, XPATH, & MERGE  for a way to transfer data across untrusted domains. We have  many uses for a reliable and standardized way to move data, look-up data and trigger events. One example is a nightly config checker and health evaluation job that runs on well over 100 servers. This job populates about 8 tables in our DBA DB, such as DBA_DBServerInfo, DBA_DBInfo, DBA_DBDiskInfo.... I want this data aggregated at a central HUB SQL Server for look-ups, reporting, and alerting. I also want this system to be usable for other data gathering, data auditing, DDL auditing, data pushing, and deployment processing projects. 


SSB was fairly simple to set up other than it is not very GUI centric. I was able to create certificates on the servers to authenticate with so that I can get past the Domain and sql login issues and simply open a single port between all SQL Servers to perform all DBA Processing messages. After I was sure I could get Service Broker to do what I wanted, the next step is to create code the reliable takes a table to XML and back to a table again. 


The first hurdle was to encoding the data into XML in a way that was generic, contained all of the information of where it came from and how to turn it back into a table to put back into a table. "SELECT * FROM TABLE FOR XML RAW" gave me the data as XML but I found it extremely difficult to put it back into a table. I finally did come up with a great process now that works well and I also came up with a way that works with single tables or a collection of tables in a single XML Doc.


I added XMLSCHEMA to the query and it gave me all of the schema I needed to query the data back out of the XML to Merge it into the destination table. The next trick was to figure out how to get what I wanted out of the XSD portion of the XML so I could query the data portion back into the exact formats it came from.


This was my first experiment with merge and I also found it to be fairly handy. I was able to dynamically generate a merge statement that looks at the PK of the destination table and uses those columns to determine between an insert or an update.


There really isnt much information or examples (that work) out there for xpath queries so it took quite a while to get something that works. I am sure someone out here can tell me how bad I did and how much better it could be. which is exactly what I want.


This is my basic encode - decode - merge of a single table...





SET NOCOUNT ON
IF OBJECT_ID('tempdb..#XMLSCHEMA') IS NOT NULL DROP TABLE #XMLSCHEMA
IF OBJECT_ID('tempdb..##Source') IS NOT NULL DROP TABLE ##Source


DECLARE @XML XML
,@XSL XML
,@TSQL nVarChar(4000)
,@TableName sysname

CREATE TABLE #XMLSCHEMA
([name] sysname NULL
,[type] sysname NULL
,[base] sysname NULL
,[value1] sysname NULL
,[value2] sysname NULL)
------------------------------------------------------------------------
------------------------------------------------------------------------
-- GENERATE A XML CHUNK TO PLAY WITH
------------------------------------------------------------------------
------------------------------------------------------------------------
SET @XML =  
(SELECT * From dbaadmin.dbo.DBA_DBInfo FOR XML AUTO,XMLSCHEMA,ROOT('Table'))


------------------------------------------------------------------------
------------------------------------------------------------------------
--GET ONLY THE TOP XDS PORTION OF THE XML CHUNK
------------------------------------------------------------------------
------------------------------------------------------------------------
SELECT TOP 1 
@XSL = x.query('.') 
FROM @XML.nodes('/Table/*') a(x)


SELECT @XML = CAST(REPLACE(CAST(@XML AS VarChar(max)),CAST(@XSL AS VarChar(max)),'') AS XML)


------------------------------------------------------------------------
------------------------------------------------------------------------
-- GET THE TABLE NAME
------------------------------------------------------------------------
------------------------------------------------------------------------
SELECT @TableName = a.x.value('*[2]/@name','sysname')
FROM @XSL.nodes('*') a(x)


SELECT @TableName [TableName]


------------------------------------------------------------------------
------------------------------------------------------------------------
-- POPULATE THE XMLSCHEMA TEMP TABLE
------------------------------------------------------------------------
------------------------------------------------------------------------
INSERT INTO #XMLSCHEMA
SELECT a.x.value('@name','sysname') [name]
,a.x.value('@type','sysname') [type]
,a.x.value('*[1]/*[1]/@base','sysname') [base]
,a.x.value('*[1]/*[1]/*[1]/@value','sysname') [value1]
,a.x.value('*[1]/*[1]/*[2]/@value','sysname') [value2]
FROM @XSL.nodes('/*/*/*/*') a(x)


------------------------------------------------------------------------
------------------------------------------------------------------------
-- CREATE THE DYNAMIC SQL TO CREATE THE SOURCE TEMP TABLE FROM THE XSD
------------------------------------------------------------------------
------------------------------------------------------------------------
SET @TSQL = 'SELECT '
SELECT @TSQL = @TSQL
+ 'a.x.value(''@'+[name]+''','''+REPLACE(COALESCE([type],[base]  + '('+value1+COALESCE(','+ nullif(value2,'') + ')',')')) ,'sqltypes:','')+''') ' + QUOTENAME([name])
+ CHAR(13) + CHAR(10) + ' ,' 
FROM #XMLSCHEMA ColumnData

SET @TSQL = REPLACE(@TSQL+'||',' ,||','INTO ##Source'+CHAR(13) + CHAR(10)+'FROM @XML.nodes(''/Table/*'') a(x)')


------------------------------------------------------------------------
------------------------------------------------------------------------
-- CREATE THE SOURCE TEMP TABLE FROM THE XSD
------------------------------------------------------------------------
------------------------------------------------------------------------
EXEC sp_Executesql @TSQL,N'@XML XML',@XML




------------------------------------------------------------------------
------------------------------------------------------------------------
-- SHOW THE SOURCE DATA AFTER INSERTED INTO THE TEMP TABLE
------------------------------------------------------------------------
------------------------------------------------------------------------
-- SELECT * FROM ##Source


------------------------------------------------------------------------
------------------------------------------------------------------------
-- CREATE THE DYNAMIC SQL TO GENERATE THE MERGE STATEMENT
------------------------------------------------------------------------
------------------------------------------------------------------------
SET @TSQL = 'MERGE INTO '+@TableName+' as Target' + CHAR(13) + CHAR(10) 
+ 'USING ##Source as Source' + CHAR(13) + CHAR(10)
+ 'ON' + CHAR(9)

SELECT @TSQL = @TSQL + 'Target.['+[name]+'] = Source.['+[name]+']'+CHAR(13)+CHAR(10)+'AND'+CHAR(9)
FROM syscolumns 
WHERE [id] = OBJECT_ID(@TableName)
AND colid IN (
SELECT SIK.colid 
FROM sysindexkeys SIK 
JOIN sysobjects SO 
ON SIK.[id] = SO.[id]  
WHERE SIK.indid = 1
AND SO.ID = OBJECT_ID(@TableName)
)

SET @TSQL = REPLACE(@TSQL+'||','AND'+CHAR(9)+'||',CHAR(13)+CHAR(10)+'when matched then update set'+CHAR(13)+CHAR(10)+'Target.')


SELECT @TSQL = @TSQL + '['+[name]+']=Source.['+[name]+']'+CHAR(13)+CHAR(10)+',Target.'
FROM #XMLSCHEMA ColumnData


SET @TSQL = REPLACE(@TSQL+'||',',Target.||',CHAR(13)+CHAR(10)+'when not matched then insert'+CHAR(13)+CHAR(10)+'(')


SELECT @TSQL = @TSQL + '['+[name]+']'+CHAR(13)+CHAR(10)+','
FROM #XMLSCHEMA ColumnData


SET @TSQL = REPLACE(@TSQL+'||',CHAR(13)+CHAR(10)+',||',')'+CHAR(13)+CHAR(10)+'values'+CHAR(13)+CHAR(10)+'(Source.')


SELECT @TSQL = @TSQL + '['+[name]+']'+CHAR(13)+CHAR(10)+',Source.'
FROM #XMLSCHEMA ColumnData


SET @TSQL = REPLACE(@TSQL+'||',CHAR(13)+CHAR(10)+',Source.||',');')




------------------------------------------------------------------------
------------------------------------------------------------------------
-- SHOW THE MERGE STATEMENT
------------------------------------------------------------------------
------------------------------------------------------------------------
--PRINT (@TSQL)
------------------------------------------------------------------------
------------------------------------------------------------------------
-- RUN THE MERGE
------------------------------------------------------------------------
------------------------------------------------------------------------
EXEC (@TSQL)




------------------------------------------------------------------------
------------------------------------------------------------------------
-- CLEAN UP TEMP TABLES
------------------------------------------------------------------------
------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#XMLSCHEMA') IS NOT NULL DROP TABLE #XMLSCHEMA
IF OBJECT_ID('tempdb..##Source') IS NOT NULL DROP TABLE ##Source