General advice on running a query against several servers

Question

Post reply

General advice on running a query against several servers

thehammer-178982

SSC Eights!

Points: 907
More actions
August 15, 2005 at 9:36 am

#363379

I have a requirement to run the same query against up to 100 instances. What would be the best way to achieve this? Linked servers?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lee Dise SSCarpal Tunnel Points: 4384 More actions · Answer 1

> up to 100 instances

Yikes!

I would start with making a list of all the server names I had to process against (in a table, a temp table, or a cursor), and then loop through them.

If all you need to do is to initate the queries without any need to stick around and play catch with results sets, then inside the loop I would use the server name as a -S argument to a dynamic isql/osql call. If you don't have an all-purpose "trusted" login, you'd need to find some way of storing logins/passwords for each server that didn't compromise security. You're better off with the trusted connections.

Managing linked server connections could get a little gnarly, but I can't see any reason why they wouldn't work. I've only ever used linked servers on a onesy-twosy basis.

Do you need to collect the results sets from all servers? The linked server connection would be more convenient for collecting results sets.

Any other solution would be more complicated, or so it seems to me. DTS might be able to accomodate you, but instead of making a list and writing code, you'll be moving little icons around.

If the solution can be scheduled instead of executed "real time", why not put a scheduled task on all the servers? You'd only have to write it once, and then script it out. You could then open the script and run it on the other servers -- the script just defines the job, it doesn't run it.

Gimli Say Hey Kid Points: 687 More actions · Answer 2

I'm using the following script to check backup's (execute sql task in a dts). Maybe you can use it as an example:

Declare serverdbcursor cursor read_only for

select server_name, database_name

from TSDB

open serverdbcursor

fetch next from serverdbcursor

into @ServerName, @DatabaseName

while @@fetch_status = 0

begin

SET @SQLString = 'SELECT @Counter = (SELECT count(*)

FROM [' + @ServerName + '].msdb.dbo.sysdbmaintplan_history

WHERE database_name = ''' + @DatabaseName + '''

AND activity = ''Backup database''

AND start_time > GETDATE()- 1 ')'

EXEC sp_executesql @SQLString, @ParmDefinition, @Counter = @BackupCount output

IF @BackupCount = 0

INSERT TLFT VALUES (@ServerName, 'warning', GETDATE(), 0, 'Backup database',

@DatabaseName, 'No backup', '')

fetch next from serverdbcursor

into @ServerName, @DatabaseName

end

close serverdbcursor

deallocate serverdbcursor

All my servers are linked servers, but you can create them in the task with sp_addlinkedserver and sp_addlinkedsrvlogin (and drop them again).

Kindest Regards,

Gimli

If you are a spineless creature, they can't break you

peterhe SSChampion Points: 11469 More actions · Answer 3

It's better to create a SP for the query on EACH of your 100 instance(you can do this by osql or similar tool). Then using the same tool login to each server to call the SP. The perfromance should be better than linked server.

thehammer-178982 SSC Eights! Points: 907 More actions · Answer 4

Thank you all for your responses so far. They have given me something to think about. I should have added that I would prefer to have one scheduled job that is called from one instance and accesses all other instances. I assume this will have some bearing on previous responses.

Michelle Cook SSCrazy Points: 2119 More actions · Answer 5

We use a VB script to do something simiar to this and run it from a centralized server that has SQL Server client installed on it and can connect to all our SQL Server instances. The script runs as a scheduled task every morning to report any errors from our maintenance plans from the previous day. Here is an example of the code:

Dim objSendMail

Dim strTo, strFrom

Dim strSubject, strBody

Dim dataflag

'create an array of all the instances you want to run the query on

Dim strArray()

Redim Preserve strArray(0)

strArray(0) = "Provider=SQLOLEDB.1; Integrated Security=SSPI; Initial Catalog=master; Data Source=servername\inst1;"

Redim Preserve strArray(1)

strArray(1) = "Provider=SQLOLEDB.1; Integrated Security=SSPI; Initial Catalog=master; Data Source=servername\inst2;"

Redim Preserve strArray(2)

strArray(2) = "Provider=SQLOLEDB.1; Integrated Security=SSPI; Initial Catalog=master; Data Source=servername\inst3;"

.

,

Redim Preserve strArray(24)

strArray(24) = "Provider=SQLOLEDB.1; Integrated Security=SSPI; Initial Catalog=master; Data Source=servername\inst24;"

' mail constants

Const CdoBodyFormatType = 0 ' Body property is HTML

Const CdoMailFormatType = 0 ' NewMail object is in MIME format

Const CdoNormal = 1 ' Normal importance (default)

strFrom = "mailaddress@company.com" ' System administrator or DBA mail account

strTo ="mailaddress@company.com" ' Recipient mail account - i.e. Sales Manager

strSubject = "Daily Maintenance Plan Error Report" ' Mail subject

' Call function to build the HTML mail body

strBody = MailBody()

' The following section creates the E-mail object and sends the mail

Set objSendMail = CreateObject("CDONTS.NewMail")

objSendMail.From = strFrom

objSendMail.To = strTo

objSendMail.Subject = strSubject

objSendMail.Body = strBody

objSendMail.BodyFormat = CdoBodyFormatType

objSendMail.MailFormat = CdoMailFormatType

objSendMail.Importance = CdoNormal

objSendMail.Send

Set objSendMail = Nothing

' **********************************************************************************

Function MailBody()

Dim oConn

Dim oCmd

Dim oRs

Dim dStartPos, dname, uStartPos, db, sLength

Dim tmpBody

tmpBody = "<H2>MAINTENANCE PLAN ERROR REPORT</H2>"

tmpBody = tmpBody & "As of " & Date() & " "

tmpBody = tmpBody & "<TABLE BORDER=1><TR BGCOLOR=Skyblue ALIGN=Middlefont >"

tmpBody = tmpBody & "<TH>SERVER_NAME</TH>"

tmpBody = tmpBody & "<TH>PLAN_NAME</TH>"

tmpBody = tmpBody & "<TH>DB_NAME</TH>"

tmpBody = tmpBody & "<TH>ACTIVITY</TH>"

tmpBody = tmpBody & "<TH>START_TIME</TH>"

tmpBody = tmpBody & "<TH>MESSAGE</TH>"

dataflag = false

'Loop through the array to run the query on each instance

For intCount = LBound(strArray) To UBound(strArray)

On Error Resume Next

'set oConn = CreateObject("ADODB.Connection")

'oConn.Open("strArray(intcount)")

set oCmd = CreateObject("ADODB.Command")

oCmd.ActiveConnection = strArray(intcount)

oCmd.CommandText = "select server_name, plan_name, database_name, activity, convert(char(16),start_time,120) start_time, message from msdb..sysdbmaintplan_history where succeeded = 0 and convert(float,datediff(minute,start_time, getdate())) <= 1440"

oCmd.CommandType = 1

oCmd.Prepared = True

set oRs = oCmd.Execute

If Err.Number <> 0 Then

dbinfo = strArray(intCount)

tmpBody = tmpBody & "<TR BGCOLOR=Cornsilk><TD>" & dbinfo & "</TD>"

tmpBody = tmpBody & "<TD>" & "ERROR QUERYING" & "</TD>"

Else

while not oRs.EOF

dataflag = true

tmpBody = tmpBody & "<TR BGCOLOR=Cornsilk><TD>" & oRs.Fields("server_name") & "</TD>"

tmpBody = tmpBody & "<TD>" & oRs.Fields("plan_name") & "</TD>"

tmpBody = tmpBody & "<TD>" & oRs.Fields("database_name") & "</TD>"

tmpBody = tmpBody & "<TD>" & oRs.Fields("activity") & "</TD>"

tmpBody = tmpBody & "<TD>" & oRs.Fields("start_time") & "</TD>"

tmpBody = tmpBody & "<TD>" & oRs.Fields("message") & "</TD>"

oRs.moveNext

wend

END IF

tmpBody = tmpBody & "<TR BGCOLOR=Cornsilk><TD>" & "NO MAINTENANCE PLAN ERRORS FOUND" & "</TD>"

End IF

tmpBody = tmpBody & "</TR></TABLE>"

MailBody = tmpBody

set oRs = nothing

set oCmd = nothing

set oConn = nothing

End Function

HTH

Gimli Say Hey Kid Points: 687 More actions · Answer 6

This has no bearing on my answer. ALL objects are created on only 1 server. The DTS package is scheduled and runs about 30 seconds (connecting to about 25 servers). I didn't want to create SP's on other servers like peterhe suggested. The performance is no issue because the job is running in quiet times. The program Michelle suggested look better to me (more flexible and faster) but I don't know any VB script.

Kindest Regards,

Gimli

If you are a spineless creature, they can't break you

noeld SSC Guru Points: 96590 More actions · Answer 7

You should look at implementing Multiserver administration with SQLServerAgent

Have a look at that topic in BOL.

* Noel

Paul Williams SSCommitted Points: 1930 More actions · Answer 8

Multiserver administration is useful only in certain circumstances and can't cover all options. It particularly deals with monitoring multiple jobs and alerts.

I administer over 150 SQL Server instances and use currently SQL-DMO via vbscript to run queries against multiple instances, which is similar to one of the suggestions above. Using LDAP it is possible to query AD directly to obtain connections to the instances rather than manually setup an array.

DTS is also possible, and again you can use vbscript within the ActiveX tasks.

I would tend to avoid using linked servers since they require extra administration once setup and open another security connection that requires care.

So all in all, the answer is it depends; depends upon what you are doing and what you are most comfortable with - sql, dts or vbscript or combinations of these.

Paul R Williams.