SQL Server 2008 Replication Publications and Subscriptions Discovery Provider

Microsoft.SQLServer.2008.ReplicationPublicationsSubscriptionsDiscovery (DataSourceModuleType)

Element properties:

Show References:
- Discoveries (1)
Type	DataSourceModuleType
Isolation	Any
Accessibility	Internal
RunAs	Microsoft.SQLServer.SQLDiscoveryAccount
OutputType	System.Discovery.Data
Member Modules:

ID	Module Type	TypeId	RunAs
DS	DataSource	Microsoft.Windows.TimedScript.DiscoveryProvider	Default
Overrideable Parameters:

ID	ParameterType	Selector	Display Name
IntervalSeconds	int	$Config/IntervalSeconds$	Interval (sec)
SyncTime	string	$Config/SyncTime$	Synchronization Time
TimeoutSeconds	int	$Config/TimeoutSeconds$	Timeout (sec)
Source Code:

<DataSourceModuleType ID="Microsoft.SQLServer.2008.ReplicationPublicationsSubscriptionsDiscovery" Accessibility="Internal" RunAs="SQL!Microsoft.SQLServer.SQLDiscoveryAccount">

  <Configuration>

    <xsd:element name="IntervalSeconds" type="xsd:integer"/>

    <xsd:element name="SyncTime" type="xsd:string"/>

    <xsd:element name="NetworkName" type="xsd:string"/>

    <xsd:element name="SQLConnectionString" type="xsd:string"/>

    <xsd:element name="SQLInstanceName" type="xsd:string"/>

    <xsd:element name="TimeoutSeconds" type="xsd:int"/>

  </Configuration>

  <OverrideableParameters>

    <OverrideableParameter ID="IntervalSeconds" ParameterType="int" Selector="$Config/IntervalSeconds$"/>

    <OverrideableParameter ID="SyncTime" ParameterType="string" Selector="$Config/SyncTime$"/>

    <OverrideableParameter ID="TimeoutSeconds" ParameterType="int" Selector="$Config/TimeoutSeconds$"/>

  </OverrideableParameters>

  <ModuleImplementation>

    <Composite>

      <MemberModules>

        <DataSource ID="DS" TypeID="Windows!Microsoft.Windows.TimedScript.DiscoveryProvider">

          <IntervalSeconds>$Config/IntervalSeconds$</IntervalSeconds>

          <SyncTime>$Config/SyncTime$</SyncTime>

          <ScriptName>DiscoverSQL2008ReplicationPublicationsSubscriptions.vbs</ScriptName>

          <Arguments>$MPElement$ $Target/Id$ $Config/NetworkName$  $Config/SQLConnectionString$ $Config/SQLInstanceName$</Arguments>

          <ScriptBody><Script>

' ##### ..\Scripts\Common\Initialize.vbs

Option Explicit

SetLocale("en-us")



Function Quit()

    WScript.Quit()

End Function



Function IsValidObject(ByVal oObject)

    IsValidObject = False

    

    If IsObject(oObject) Then

        If Not oObject Is Nothing Then

            IsValidObject = True

        End If

    End If

End Function



Function MomCreateObject(ByVal sProgramId)

    Dim oError

    Set oError = New Error

    

    On Error Resume Next

    Set MomCreateObject = CreateObject(sProgramId)

    oError.Save

    On Error GoTo 0

    

    If oError.Number &lt;&gt; 0 Then ThrowScriptError "Unable to create automation object '" &amp; sProgramId &amp; "'", oError

End Function

' ##### Scripts\SQL2008Constants.vbs

Const SQL_VERSION = "2008"

Const SQL_VERSION_NUMBER = "10"

Const SQL_WMI_NAMESPACE = "ComputerManagement10"



Const SQL_FULLTEXTSEARCH_SERVICENAME = "MSSQLFDLauncher"

Const SQL_FULLTEXTSEARCH_SERVICECLUSTERNAME = "SQL Full-text Filter Daemon Launcher"



Const SQL_DBENGINE_CLASS = "$MPElement[Name='Microsoft.SQLServer.2008.DBEngine']$"

Const SQL_DATABASE_CLASS = "$MPElement[Name='Microsoft.SQLServer.2008.Database']$"

Const SQL_AGENTJOB_CLASS = "$MPElement[Name='Microsoft.SQLServer.2008.AgentJob']$"

Const SQL_REPLICATION_DISTRIBUTOR_CLASS = "$MPElement[Name='Microsoft.SQLServer.2008.Distributor']$"

Const SQL_DISTRIBUTOR_CONTAINS_DATABASE_RELATIONSHIP = "$MPElement[Name='Microsoft.SQLServer.2008.DistributorContainsDatabase']$"

Const SQL_REPLICATION_PUBLISHER_CLASS = "$MPElement[Name='Microsoft.SQLServer.2008.Publisher']$"

Const SQL_REPLICATION_SUBSCRIBER_CLASS = "$MPElement[Name='Microsoft.SQLServer.2008.Subscriber']$"

Const SQL_REPLICATION_PUBLICATION_CLASS = "$MPElement[Name='Microsoft.SQLServer.2008.Publication']$"

Const SQL_REPLICATION_SUBSCRIPTION_CLASS = "$MPElement[Name='Microsoft.SQLServer.2008.Subscription']$"

' ##### ..\Scripts\Common\Error.vbs

Class Error

    Private m_lNumber

    Private m_sSource

    Private m_sDescription

    Private m_sHelpContext

    Private m_sHelpFile



    Public Sub Save()

        m_lNumber = Err.Number

        m_sSource = Err.Source

        m_sDescription = Err.Description

        m_sHelpContext = Err.HelpContext

        m_sHelpFile = Err.HelpFile

    End Sub



    Public Sub Raise()

        Err.Raise m_lNumber, m_sSource, m_sDescription, m_sHelpFile, m_sHelpContext

    End Sub



    Public Sub Clear()

        m_lNumber = 0

        m_sSource = ""

        m_sDescription = ""

        m_sHelpContext = ""

        m_sHelpFile = ""

    End Sub



    Public Default Property Get Number()

        Number = m_lNumber

    End Property

    Public Property Get Source()

        Source = m_sSource

    End Property

    Public Property Get Description()

        Description = m_sDescription

    End Property

    Public Property Get HelpContext()

        HelpContext = m_sHelpContext

    End Property

    Public Property Get HelpFile()

        HelpFile = m_sHelpFile

    End Property    

End Class



Function ThrowScriptErrorNoAbort(ByVal sMessage, ByVal oErr)

    On Error Resume Next

    Dim oAPITemp

    Set oAPITemp = MOMCreateObject("MOM.ScriptAPI")

    oAPITemp.LogScriptEvent WScript.ScriptName, 4001, 1, sMessage &amp; ". " &amp; oErr.Description

End Function



Function ThrowScriptError(Byval sMessage, ByVal oErr)

    On Error Resume Next

    ThrowScriptErrorNoAbort sMessage, oErr

    Quit()

End Function



Sub HandleError(customMessage)

    Dim localLogger

    If Not (Err.number = 0) Then

        Set localLogger = new ScriptLogger

        localLogger.LogFormattedError(customMessage)

        Wscript.Quit 0

    End If

End Sub



Function HandleErrorContinue(customMessage)

    Dim localLogger

    HandleErrorContinue = False

    If Not (Err.number = 0) Then

        Set localLogger = new ScriptLogger

        localLogger.LogFormattedError(customMessage)

        Err.Clear

        HandleErrorContinue = True

    End If

End Function





' ##### ..\Scripts\Common\Discovery\SQLReplicationPublicationsSubscriptionsDiscovery.vbs

'Copyright (c) Microsoft Corporation. All rights reserved.



' Parameters that should be passed to this script

' 0 MPElement ID ($MPElement$)

' 1 Target Id for ME this rule is running against ($Target/Id$)

' 2 Computer (FQDN) that SQL instance is hosted on

' 3 SQL Connection String for the instance that the DBs are being discovered on

' 4 SQL Instance that this rule is being run for 



Const SQL_DISCOVERY_CONNECT_FAILURE = -1

Const SQL_DISCOVERY_QUERY_FAILURE = -2

Const SQL_DISCOVERY_SUCCESS = 0



Dim oArgs

Set oArgs = WScript.Arguments

if oArgs.Count &lt;&gt; 5 Then

   Wscript.Quit -1

End If



Dim  SourceID, ManagedEntityId, TargetComputerID, InstanceName, ConnectionString



SourceID = oArgs(0)

ManagedEntityId = oArgs(1)

TargetComputerID = oArgs(2)

ConnectionString = oArgs(3)

InstanceName = oArgs(4)



Dim oAPI, oSQLDiscoveryData



Set oAPI = MOMCreateObject("MOM.ScriptAPI")

Set oSQLDiscoveryData = oAPI.CreateDiscoveryData(0, SourceID, ManagedEntityId)





If DoReplicationDiscovery(TargetComputerID, InstanceName, ConnectionString, oSQLDiscoveryData) &gt;= 0 Then Call oAPI.Return(oSQLDiscoveryData)



' This function should be used to escape Connection String keywords.

Function EscapeConnStringValue (ByVal strValue)

	ON ERROR RESUME NEXT

	Err.Clear



	EscapeConnStringValue  = "{" + Replace(strValue, "}", "}}") + "}"

End Function





Function DoReplicationDiscovery(ByVal sTargetComputerID, ByVal sSqlInstance, ByVal sSQLConnectionString, ByVal oSQLDiscoveryData)



	Dim e

	Dim oDistInstance

	Dim cnADOConnection

	Dim cnADOConnection2

	Dim strProv

	Dim oResults

	Dim oResults2

	Dim oResultsDistributor

	Dim oRel

	Dim oDatabase



	Set e = New Error



	Set cnADOConnection = MomCreateObject("ADODB.Connection")

	cnADOConnection.Provider = "sqloledb"

	cnADOConnection.ConnectionTimeout = 30

	strProv = "Server=" &amp; EscapeConnStringValue(sSQLConnectionString) &amp; ";Database=master;Trusted_Connection=yes"

  

	e.Clear

	On Error Resume Next

		cnADOConnection.Open strProv

		e.Save

	On Error Goto 0    

	if 0 &lt;&gt; e.Number then

		'Error event in here

		'g_oSQL.CreateConnectionFailureAlert sInstance, Err.number, Err.Description

		DoReplicationDiscovery = SQL_DISCOVERY_CONNECT_FAILURE

		Exit Function

	end if

	    

	' Create the publication and subsciption objects

	On Error Resume Next

		' query for the list of databases which are not database snapshots

		Set oResults = cnADOConnection.Execute("SELECT name, state_desc FROM sys.databases WHERE source_database_id IS NULL")

		e.Save

	On Error Goto 0

	if 0 &lt;&gt; e.Number then

		'Error event in here

		DoReplicationDiscovery = SQL_DISCOVERY_QUERY_FAILURE

		Exit Function

	end if



	Set cnADOConnection2 = MomCreateObject("ADODB.Connection")

	cnADOConnection2.Provider = "sqloledb"

	cnADOConnection2.ConnectionTimeout = 30

    

	Dim DistributorName

	Dim sDBName, bNeedCreateDB



	Do While Not oResults.EOF

        bNeedCreateDB = False



        ' Change database context

        ' TODO: Check if we can use 'USE' statement instead of reconnection

		sDBName = oResults(0)



        strProv = "Server=" &amp; EscapeConnStringValue(sSQLConnectionString) &amp; ";Database=" &amp; EscapeConnStringValue(sDBName) &amp; ";Trusted_Connection=yes"

        e.Clear

	    On Error Resume Next

		    cnADOConnection2.Open strProv

		    e.Save

	    On Error Goto 0



        If e.Number = 0 Then

            ' Discover transactional and snapshot publications

            On Error Resume Next

				Set oResults2 = cnADOConnection2.Execute("sp_helppublication")

				e.Save

			On Error Goto 0



			If e.Number = 0 And oResults2.Fields.Count &gt; 0 Then

                bNeedCreateDB = True

		

                Do While Not oResults2.EOF

                    Set oDistInstance = oSQLDiscoveryData.CreateClassInstance(SQL_REPLICATION_PUBLICATION_CLASS)

                    With oDistInstance

                        .AddProperty "$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", TargetComputerID

                        .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.ServerRole']/InstanceName$", sSqlInstance

                        .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Database']/DatabaseName$", sDBName

                        .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Publication']/PublicationID$", oResults2(0)

                        .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Publication']/Name$", oResults2(1)

                        .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Publication']/IsActive$", CBool(oResults2(3))

                        .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Publication']/Description$", oResults2(7)

                        .AddProperty "$MPElement[Name='System!System.Entity']/DisplayName$", oResults2(1)

                    End With

                    Call oSQLDiscoveryData.AddInstance(oDistInstance)

                    oResults2.MoveNext

                Loop

		    End If



            ' Discover merge publications

            On Error Resume Next

				Set oResults2 = cnADOConnection2.Execute("sp_helpmergepublication")

				e.Save

			On Error Goto 0



			If e.Number = 0 And oResults2.Fields.Count &gt; 0 Then

                bNeedCreateDB = True



                Do While Not oResults2.EOF

                    Set oDistInstance = oSQLDiscoveryData.CreateClassInstance(SQL_REPLICATION_PUBLICATION_CLASS)

                    With oDistInstance

                        .AddProperty "$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", TargetComputerID

                        .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.ServerRole']/InstanceName$", sSqlInstance

                        .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Database']/DatabaseName$", sDBName

                        .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Publication']/PublicationID$", -1 * oResults2(0)

                        .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Publication']/Name$", oResults2(1)

                        .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Publication']/IsActive$", CBool(oResults2(3))

                        .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Publication']/Description$", oResults2(7)

                        .AddProperty "$MPElement[Name='System!System.Entity']/DisplayName$", oResults2(1)

                    End With

                    Call oSQLDiscoveryData.AddInstance(oDistInstance)

                    oResults2.MoveNext

                Loop

		    End If



            ' Try get distributor name

            On Error Resume Next

		        Set oResultsDistributor = cnADOConnection2.Execute("sp_helpdistributor")

            On Error Goto 0

            If oResultsDistributor.Fields.Count &gt; 0 Then DistributorName = oResultsDistributor(0)



            ' Discover transactional and snapshot subscriptions

            On Error Resume Next

		        Set oResults2 = cnADOConnection2.Execute("sp_helpsubscription")

		        e.Save

	        On Error Goto 0



	        If e.Number = 0 And oResults2.Fields.Count &gt; 0 Then

                bNeedCreateDB = True

                Do While Not oResults2.EOF

                    Set oDistInstance = oSQLDiscoveryData.CreateClassInstance(SQL_REPLICATION_SUBSCRIPTION_CLASS)

                    With oDistInstance

                        .AddProperty "$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", TargetComputerID

                        .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.ServerRole']/InstanceName$", sSqlInstance

                        .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Database']/DatabaseName$", sDBName

                        .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Subscription']/Name$", oResults2(8)

                        .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Subscription']/Publisher$", TargetComputerID

                        .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Subscription']/PublisherDB$", sDBName

                        .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Subscription']/Publication$", oResults2(1)

                        .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Subscription']/Distributor$", DistributorName

                        .AddProperty "$MPElement[Name='System!System.Entity']/DisplayName$", oResults2(8)

                    End With

                    Call oSQLDiscoveryData.AddInstance(oDistInstance)

                    oResults2.MoveNext

                Loop

		    End If



            ' Discover merge subscriptions

            On Error Resume Next

		        Set oResults2 = cnADOConnection2.Execute("sp_helpmergesubscription")

		        e.Save

	        On Error Goto 0



	        If e.Number = 0 And oResults2.Fields.Count &gt; 0 Then

                bNeedCreateDB = True

                Do While Not oResults2.EOF

                    Set oDistInstance = oSQLDiscoveryData.CreateClassInstance(SQL_REPLICATION_SUBSCRIPTION_CLASS)

                    With oDistInstance

                        .AddProperty "$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", TargetComputerID

                        .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.ServerRole']/InstanceName$", sSqlInstance

                        .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Database']/DatabaseName$", sDBName

                        .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Subscription']/Name$", oResults2(0)

                        .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Subscription']/Publisher$", TargetComputerID

                        .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Subscription']/PublisherDB$", sDBName

                        .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Subscription']/Publication$", oResults2(1)

                        .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Subscription']/Distributor$", DistributorName

                        .AddProperty "$MPElement[Name='System!System.Entity']/DisplayName$", oResults2(0)

                    End With

                    Call oSQLDiscoveryData.AddInstance(oDistInstance)

                    oResults2.MoveNext

                Loop

		    End If



            If bNeedCreateDB Then

                ' Create the DB instance so if it does not exist we won't get an error submitting the hosted instance

                Set oDatabase = oSQLDiscoveryData.CreateClassInstance(SQL_DATABASE_CLASS)

                With oDatabase

                .AddProperty "$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", TargetComputerID

                .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.ServerRole']/InstanceName$", sSqlInstance

                .AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Database']/DatabaseName$",sDBName

                End With

                Call oSQLDiscoveryData.AddInstance(oDatabase)

            End If



            cnADOConnection2.Close()

        End If

        

		oResults.MoveNext

	Loop



	cnADOConnection.Close()

	DoReplicationDiscovery = SQL_DISCOVERY_SUCCESS



End Function



Function EscapeDatabaseName(ByVal sDatabaseName)

	On Error Resume Next

	EscapeDatabaseName  = "[" + Replace(sDatabaseName, "]", "]]") + "]"

End Function

                  </Script></ScriptBody>

          <TimeoutSeconds>$Config/TimeoutSeconds$</TimeoutSeconds>

        </DataSource>

      </MemberModules>

      <Composition>

        <Node ID="DS"/>

      </Composition>

    </Composite>

  </ModuleImplementation>

  <OutputType>System!System.Discovery.Data</OutputType>

</DataSourceModuleType>