SQL Server Compare – sy.configurations – Using Linked Server

Background

Needed a quick code to compare SQL Instance configurations across two SQL SQL Server Instances.

Prerequisite

Please create a linked server named Secondary to the other SQL Server Instance.

Sample Code

Code

Here is a sample code for comparing SQL Server configuration’s setting set via sp_configure.

 



select
          [basedOn] 
			= cast (serverproperty('servername') as sysname)

        , [name] 
			= tblSCSrc.[name] collate Latin1_General_100_BIN2

        , [valuePrimary]
			= tblSCSrc.[value]

        , [valueSecondary]
		   = tblSCDes.[value]

        , [missing]
            = case
                    when ( tblSCDes.[value_in_use] is null) then 1
                    else 0
             end

from   [master].sys.configurations tblSCSrc
 
left outer join [SECONDARY].[master].sys.configurations tblSCDes
 
    on tblSCSrc.[name] = tblSCDes.[name] collate Latin1_General_100_BIN2
 
where  (
 
			(

				   ( tblSCSrc.[value_in_use] != tblSCDes.[value_in_use] )

                or (tblSCDes.[value_in_use] is null )

            )
 
        )
 
union
 
select
          [basedOn]
			 = [tblSCDes].[basedOn]

        , [name]
			= [tblSCDes].[name] collate Latin1_General_100_BIN2

        , [valuePrimary]
			= tblSCSrc.[value]

        , [valueSecondary]
		    = tblSCDes.[value]

        , [missing]
            = case
                    when ( tblSCDes.[value_in_use] is null) then 1
                    else 0
             end


from   [master].sys.configurations tblSCSrc
 
right outer join
		(
			select *
			from   openquery
			(
				   [SECONDARY]
				 , 'select 
							  [basedOn]
								 = cast (serverproperty(''servername'') as sysname)
 
							, tblSC.* 
					from [master].sys.configurations tblSC 
					'
			)

		)   tblSCDes
 
    on tblSCSrc.[name] = tblSCDes.[name] 
		collate database_default
 
where  (
            (
				(tblSCSrc.[value_in_use] is null )
            )
       )


Output

comparesqlserversettings_20170225_0426

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s