How to collect replication information

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to collect replication information

Author

Topic

Deepak03@rediffmail.com
Starting Member

11 Posts

Posted - 2010-07-13 : 09:28:39

Hi,

I need to create a script that i can execute on my servers to identify if they are a part of replication or not.

Please can some one help me if someone already have this type of script?

Thanks,
DG

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-13 : 16:31:28

run this at the distributor

use distribution;
go

select	distinct publisher_db from  dbo.MSpublications;
go

Deepak03@rediffmail.com
Starting Member

11 Posts

Posted - 2010-07-14 : 04:50:24

Hi,

Thanks for your reply.

My only requirement is if i run a script on my servers it would return by saying "replication configured" or "replication not configured"(if its not configured).

Thanks,

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-07-14 : 05:03:26

quote:
Originally posted by Deepak03@rediffmail.com

Hi,

Thanks for your reply.

My only requirement is if i run a script on my servers it would return by saying "replication configured" or "replication not configured"(if its not configured).

Thanks,

if exists (select 'x' from sys.databases where name='distribution')
Select 'replication configured' as result
else
select 'replication not configured' as result

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Deepak03@rediffmail.com
Starting Member

11 Posts

Posted - 2010-07-14 : 06:43:48

Thanks Senthil,

But is Distribution database is configured on third server, like A is a publisher, B is subscriber and C is for distribution.

In this case only C would be highlighter as "replication configured". 'replication not configured' will be the resilt from "A" and "B". This would be great if it is more specific like on "A" - "replication configured and its working as Publication" etc.

thanks,

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-14 : 09:34:51

[code]SELECT Case WHEN
sum(category & 1) +
sum(category & 2) +
sum(category & 4) +
sum(category & 8) +
sum(category & 16)
> 0
THEN 'is part of replication'
Else 'not part of replication'
End
from sysdatabases[/code]

Deepak03@rediffmail.com
Starting Member

11 Posts

Posted - 2010-07-14 : 10:41:29

It’s a excellent script russell.

Thank you so much for your help. Please can you explain it a bit because i am not too good in scripting and i am not able to understand it.

Also can we get the information for Integration services/Analysis services and reporting services by the same script.

Many Thanks again for your support.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-14 : 11:36:11

the category column in sysdatabases is a bitmap to identify if the database is used in replication. for explanation of the values see here: http://msdn.microsoft.com/en-us/library/ms179900.aspx

since u want it at the server level instead of db level, i just checked all of the dbs by summing the bits. if it's greater than 0 then we know that at least one db in the server participates somehow in replication.

in fact we could simplify it like so

SELECT	Case WHEN
	sum(category)
	> 0
	THEN 'is part of replication'
	Else 'not part of replication'
	End
from	sysdatabases

for installed services, better to look at the services applet

Deepak03@rediffmail.com
Starting Member

11 Posts

Posted - 2010-07-15 : 03:39:18

You are right that for installd services, its better to look at serverices applet but if you need to check for n no of server in a one go its good if you have script is place.

Can we check for intigration services/reporting and analysis services with the same way?

Thanks,

Deepak03@rediffmail.com
Starting Member

11 Posts

Posted - 2010-07-15 : 06:39:25

I have cteated a script to identify reporting services installed or not.

Just the requirement left i need to identify analysis and intergation services installed or not by a script.

Thanks,

Deepak03@rediffmail.com
Starting Member

11 Posts

Posted - 2010-07-16 : 07:33:02

Can anyone please help identifying integration services and analysis services at script level?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-16 : 15:23:37

This vbScript will do it


msgbox IsProcessRunning("ServerNameHere", "MsDtsServer")
msgbox IsProcessRunning("ServerNameHere", "MSSQLServerOLAPService")

Function IsProcessRunning( strServer, strProcess )
    Dim Process, strObject
    IsProcessRunning = False
    strObject   = "winmgmts://" & strServer
    For Each Process in GetObject( strObject ).InstancesOf( "Win32_Service" )
	If UCase( Process.name ) = UCase( strProcess ) Then
	    If UCASE(Process.State) = "RUNNING" Then
	            IsProcessRunning = True
	            Exit Function
	    End If
        End If
    Next
End Function

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-16 : 15:41:12

or this


Dim arServers
Dim strEmail
Dim i

CheckServicesAndBuildEmailString
email 

Sub CheckServicesAndBuildEmailString()
	arServers = Array("List", "Your", "ServerNames", "Here")
	strEmail = "<html><table><tr><td>Server</td><td>SSIS</td><td>OLAP</td></tr>" & vbCrLf

	For i = 0 to ubound(ar)
		strEmail = strEmail & "<tr><td>" & arServers(i) & "</td>"

		strEmail = strEmail & "<td>" & If IsProcessRunning(arServers(i), "MsDtsServer") & "</td>"
		strEmail = strEmail & "<td>" & If IsProcessRunning(arServers(i), "MSSQLServerOLAPService") & "</td>"
		strEmail = strEmail & "</tr>" & vbCrLf
	Next

	strEmail = strEmail & "</table></html>" & vbCrLf
End Sub


Function IsProcessRunning( strServer, strProcess )
    Dim Process, strObject
    IsProcessRunning = False
    strObject   = "winmgmts://" & strServer
    For Each Process in GetObject( strObject ).InstancesOf( "Win32_Service" )
	If UCase( Process.name ) = UCase( strProcess ) Then
	    If UCASE(Process.State) = "RUNNING" Then
	            IsProcessRunning = True
	            Exit Function
	    End If
        End If
    Next
End Function

Sub email()
    Dim m 'As CDO.Message
    Set m = CreateObject("CDO.Message")
    
    With m
        .Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value = "2"
        .Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value = "IP Address Of Your SMTP Server Here"
        .Configuration.Fields.Update
        
        .To = "you@yourEmailAddressHere"
        .From = "you@yourEmailAddressHere"
        .Subject = "Service Check"
        .HTMLBody = strEmail
        .TextBody = strEmail
        .Send
    End With
    
    Set m = Nothing
End Sub

Note that the function identifies whether the service is running, not whether or not it is installed

Deepak03@rediffmail.com
Starting Member

11 Posts

Posted - 2010-07-17 : 05:29:08

Thanks russell,

Does it means information for analysis and intergation services not got stored at database level like replication and reporting services ?

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources