Archive

Archive for January, 2012

Query Multiple Servers – SQL Server 2008

In this thread I would like to share my experience with multi server queries, a few days ago I received a requirement to query both the development and production servers at a single instance in SQL Server 2008. In brief, the requirement is for querying the stage and fact tables for load count – if matches then success else failure – for both production and development servers. Then I googled a lot about multi server queries and came up with the following query/procedure to validate the load in single instance.

I want you all to take a look at the query below and if you want to change/modify anything please do post a comment which will be a great enhancement for this query

Version: SQL Server 2008

Production Server’s SQL Instance Name: Production

Development Server’s SQL Instance Name: Development

Before querying multiple servers we need to add those servers as linked servers, this can be done either by stored procedures or on Linked Servers (Right click and add new) as shown in screenshot below,

linked servers

Procedure:

ALTER PROCEDURE [dbo].[Factloadtiebackreport]
AS

EXEC Sp_addlinkedserver
'Production'

EXEC Sp_addlinkedserver
'Development'

DECLARE @Stgfactcount INT,
@Factcount INT,
@LoadSuccessful BIT

-- - - Validation in Development Server - - -

SELECT @Stgfactcount = Count(*)
FROM [Development].[Training].dbo.stg_salesa

SELECT @Factcount = Count(*)
FROM [Development].[Training].dbo.factsales

IF @Factcount = @Stgfactcount
BEGIN
PRINT 'The fact load is successful'
PRINT 'Row Count –'
+ Cast(@FactCount AS NVARCHAR(5))
EXEC msdb.dbo.Sp_send_dbmail
@profile_name = 'Gmail',
@recipients = 'yourid.com',
@body = 'The stored procedure finished successfully.',
@subject = 'Automated Status Message – Development'
SET @LoadSuccessful = 1
END
ELSE
BEGIN
PRINT 'Load Failure - Data mismatch'
EXEC msdb.dbo.Sp_send_dbmail
@profile_name = 'Gmail',
@recipients = 'yourid.com',
@body = 'There is a data mismatch between STG and Fact',
@subject = 'Automated Status Message – Development'
END
-- - -  Validation in Production Server - - -

IF @LoadSuccessful = 1
BEGIN
SELECT @Stgfactcount = Count(*)
FROM [Production].[Training].dbo.stg_salesa
SELECT @Factcount = Count(*)
FROM [Production].[Training].dbo.factsales
IF @Factcount = @Stgfactcount
BEGIN
PRINT ‘The fact load is successful'
PRINT 'Row Count –'
+ Cast(@Factcount AS NVARCHAR(5))
EXEC msdb.dbo.Sp_send_dbmail
@profile_name = 'Gmail',
@recipients = 'yourid.com',
@body = 'The stored procedure finished successfully.',
@subject = 'Automated Status Message – Production'
END
ELSE
BEGIN
PRINT 'Load Failure - Data mismatch'
EXEC msdb.dbo.Sp_send_dbmail
@profile_name = 'Gmail',
@recipients = 'yourid.com',
@body = 'There is a data mismatch between STG and Fact',
@subject = 'Automated Status Message – Production'
END
END

EXEC Sp_dropserver
'Production'
EXEC Sp_dropserver
'Development'

Please refer my other blog for Database Mail that is used in this procedure,

https://rdineshkumar.wordpress.com/2011/06/20/database-mail-sql-server-2008/

 

Thanks

Advertisements
%d bloggers like this: