Run query across 100\'s of servers in 1 go to set up a process

Question

Run query across 100\'s of servers in 1 go to set up a process

mtz676

SSCrazy Eights

Points: 9180
More actions
November 2, 2019 at 11:58 pm

#3694108
I am able to run an extended query on 1 server and explore the data from the .xel file.
But I need it to run this extended events query across 100's of servers against a particular database which exists on all servers.
Start the session on all servers in one go(part of the query I believe)
And then I need to collect the result from the .xel files and create a report out of it.
How do I achieve this ?
Responses are appreciated.
- This topic was modified 4 years, 5 months ago by mtz676.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

Let's do the math here... you say "100's of servers". If each server only had 4 core, and "100's of servers" meant at least 200, that would be 800 core. Multiple that by the standard edition price of $7K per core and we end with the number 5.6 million dollars just in SQL Server license fees. That doesn't include hardware, Windows Server License fees, electricity for the servers or their cooling, nor all the people necessary to support such a farm.

Now, consider the question you're asking and then tell us why you don't know the answer to this nor why the company hasn't provided the necessary training to such a key member on such a project.

I'm thinking this is an interview question. Either that or you really do need to ask for some training.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Linked servers, CMS, OPENROWSET, Policies, SSRS, SSIS - each has its advantages and its limitations and could form the whole solution or part of it. You'll need to figure out what works best for your own environment.

John

mtz676 SSCrazy Eights Points: 9180 More actions · Answer 3

Awesome Jeff Moden...but not enough ....I did not know I could post here and also expect an answer and then give it back to the interviewer all at the same time !

Anyways...appreciate your time ! Good day !

Thanks John Mitchell.Appreciate your feedback.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

mtz676 wrote:

Awesome Jeff Moden...but not enough ....I did not know I could post here and also expect an answer and then give it back to the interviewer all at the same time !
Anyways...appreciate your time ! Good day !
Thanks John Mitchell.Appreciate your feedback.

So do like I suggested... ask the company you're working for for some training.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frederico_fonseca SSCoach Points: 16014 More actions · Answer 5

what you can do is to implement it using powershell.

See this http://byobi.com/2015/10/powershell-scripts-for-collecting-ssas-related-perfmon-and-xevent-trace-data/ for example from Aaron Bertrand

and then this one to load them onto a sql server table for analysis https://www.sqlservercentral.com/articles/load-extended-events-via-powershell-1

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

Jeff Moden wrote:

mtz676 wrote:
Awesome Jeff Moden...but not enough ....I did not know I could post here and also expect an answer and then give it back to the interviewer all at the same time !
Anyways...appreciate your time ! Good day !
Thanks John Mitchell.Appreciate your feedback.
So do like I suggested... ask the company you're working for for some training.

I'm also curious and I try to ask this question of anyone making the claim... do you really have 100's of servers that you need to monitor? I ask because most of the people making the claim in the fashion that you did don't actually have such a situation. Usually, it's a question someone asks when trying to prep for an interview with a large company or trying to answer a question from an interview.

If its for an interview, no answer on a forum post can actually prep you for such a thing. People will blow holes through your answers and you might actually be the one unwittingly blowing the holes. I'm also here to tell you that the interviewers will perceive such holes as BS on your part and that BS will be perceived as you lying and that's nearly instant disqualification during an interview. It's ok to say that you've studied a couple of methods (and name the methods and be prepared to answer questions about the methods) but haven't actually done such a thing. For DBAs and Developers, absolute honesty is an absolute must absolutely all the time.

If you DO actually have 100's of servers you need to do this to, then you actually do need some help because if you do actually land or build a centralized routine that can do it for you, then your entire domain(s) is/are at great risk if a bad guy ever gets in with elevated privs (and you should absolutely plan on that eventually happening).

So my original post, although greased with a little of the "yeah, right/sure you do" attitude, wasn't as totally sarcastic as you might have thought. If you actually have 100's of servers that you suddenly need to access in such a fashion, then you need some training because of the underlying security issues I spoke of. If it's for an interview in any way, shape, or form, any answer other than the truth (you've not done it before) will cost you the interview.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

x SSC-Insane Points: 23660 More actions · Answer 7

If you DO actually have 100's of servers you need to do this to, then you actually do need some help because if you do actually land or build a centralized routine that can do it for you, then your entire domain(s) is/are at great risk if a bad guy ever gets in with elevated privs (and you should absolutely plan on that eventually happening).

I do this with sqlcmd. Not hundreds but dozens, so it pays to script this up so that I can execute t-sql against a list of servers. We pay 0 dollars per server for SQL licensing.

Whats my SQL edition? Whats my security ramifications?

edit: We run dozens of express editions so really the added cost is zip. They're still able to be remotely logged into via ordinary remote server connections, and I certainly batch them up and when we need to do these connections, the batch method is fairly popular here. Obviously there are security ramifications if someone were to steal my credentials, but isn't this still pretty much the same thing as me running SSMS and connecting interactively each time to each express instance?

This reply was modified 4 years, 5 months ago by x.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

x wrote:

If you DO actually have 100's of servers you need to do this to, then you actually do need some help because if you do actually land or build a centralized routine that can do it for you, then your entire domain(s) is/are at great risk if a bad guy ever gets in with elevated privs (and you should absolutely plan on that eventually happening).
I do this with sqlcmd. Not hundreds but dozens, so it pays to script this up so that I can execute t-sql against a list of servers. We pay 0 dollars per server for SQL licensing.
Whats my SQL edition? Whats my security ramifications?
edit: We run dozens of express editions so really the added cost is zip. They're still able to be remotely logged into via ordinary remote server connections, and I certainly batch them up and when we need to do these connections, the batch method is fairly popular here. Obviously there are security ramifications if someone were to steal my credentials, but isn't this still pretty much the same thing as me running SSMS and connecting interactively each time to each express instance?

Understood. But is the cost really zip"? Probably not... they have to live on a box somewhere and it would be my guess that they don't all live on the same box. VM or physical, each box would require a Window license.

Still and like you said, that's not 100's of servers like many people claim.

While "It Depends" still rings true, you might have a bit of a different problem for "security". Are you the only person with access to all of the Express instances? Knowing you, though, the answer would be "No" and, of course, that would be a good thing. And, yes, I totally agree that if someone gets in as you, that would cause a problem. We've started to limit how many servers an AD login can actually get to for SQL Server. I'm not sure what else they're doing on the AD side of the house but I'm personally becoming less of a fan .

I'm still curious about the OP's situation... do they really have 100's of servers? If not, what is the reason for the question?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mtz676 SSCrazy Eights Points: 9180 More actions · Answer 9

This was certainly not an interview question.

1.I have accumulated relevant information from across servers(110+ servers) into a central server using SSIS. But that was more of a SQL monitoring solution and we still use it and it works well. Would it wise(not sure) to use SSIS to capture information into a central server.I have noticed when querying .xel files generated out of extended events in SSMS it takes quite some time to display the output.

2.My second option was to query against the central management server but

I do not know how to redirect the results from CMS into to centralized database as in automate it?

How do I schedule a query against CMS and dump the extract into a centralized database.

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 10

(1) SSMS is just one way you can pull data into a central server. Try some of the other ways I suggested above and see which works best for you.

(2) This may help you get started.

John