When the reporting team needs an assist...

Question

When the reporting team needs an assist...

Robert_sqlapprenticeDOTnet

SSC-Addicted

Points: 433
More actions
October 20, 2013 at 11:22 am

#281592

Comments posted to this topic are about the item When the reporting team needs an assist...

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

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

vijayreports Grasshopper Points: 21 More actions · Answer 1

If centralized/linked servers is not an option, a report using Expression-based connection strings http://msdn.microsoft.com/en-us/library/ms156450.aspx and data driven subscription http://technet.microsoft.com/en-us/library/ms159150.aspx could also be considered. It would involve additional processing on the subscription output.

Ol'SureHand SSCrazy Points: 2744 More actions · Answer 2

Curious about the decision to use linked servers for the purpose of extracting a bit of info perhaps once a day.

Since these are all servers under your control, would it not be easier and lighter on resources to add a command to the nightly maintenance plan on each server to collect the info into a table per server, then have one go at collecting it into the "Central Repository" from your reporting application by connecting to each server ?

I'm just a bit uneasy about linked servers, is all... would like to hear other opinions.

Congratulations on a well documented article.

Robert_sqlapprenticeDOTnet SSC-Addicted Points: 433 More actions · Answer 3

Ol'SureHand (10/21/2013)
Curious about the decision to use linked servers for the purpose of extracting a bit of info perhaps once a day.
Since these are all servers under your control, would it not be easier and lighter on resources to add a command to the nightly maintenance plan on each server to collect the info into a table per server, then have one go at collecting it into the "Central Repository" from your reporting application by connecting to each server ?
I'm just a bit uneasy about linked servers, is all... would like to hear other opinions.
Congratulations on a well documented article.

Thanks for your comment.

Yes, the approach you mentioned had crossed my mind as well.

There were two main reasons that I chose to use a 'CentralSQLServer' /linked servers/:

1. Our customer environments and reporting environment are separated, no direct connection between them.

2. I wanted this solution to be as manageable and movable as possible. For example in case of the reporting team would inform me that they need some modification on the data that need to be collected. In this case I only need to modify the query on the 'CentralSQLServer'.

chudman SSCrazy Points: 2453 More actions · Answer 4

Robert

Great article, and you are obviously very helpful.

I understand the concept of teamwork, and the comment about 'you never know when...' however, in my curmudgeonly middle age, I would just like to say that you did the entire job for the reporting team. The next time they need something, they are not going to look for it. They are gonna hit your phone number on speed dial.

And I can almost categorically state that there will never be a time when the report team helps you in your time of need. But I understand teamwork. It just seems like certain parts of the team do all the work and the other parts of the team get all the Gatorade. I am seeing this type of split a lot recently (in the past five years). Before that, either employers were hiring a higher caliber candidate, or the pool of candidates had better problem-solving skills. Different teams had sense of ownership and were proud of something done well. Now I see people showing a sense of accomplishment when you they get somebody else to do their work, but they are proud of it and will gladly own up to having to grind away at that phone to find somebody who could do it. So I guess the fault lies with my most recent employers and their willingness to compromise on quality in the hiring process..

OK, back to your article. This was very well put together. Thanks for writing it up. It will help a lot of folks willing to put forth a little effort.

Respectfully

Jeff Bennett

Member of STLSSUG, Missouri

Robert_sqlapprenticeDOTnet SSC-Addicted Points: 433 More actions · Answer 5

chudman (10/21/2013)
Robert
Great article, and you are obviously very helpful.
I understand the concept of teamwork, and the comment about 'you never know when...' however, in my curmudgeonly middle age, I would just like to say that you did the entire job for the reporting team. The next time they need something, they are not going to look for it. They are gonna hit your phone number on speed dial.
And I can almost categorically state that there will never be a time when the report team helps you in your time of need. But I understand teamwork. It just seems like certain parts of the team do all the work and the other parts of the team get all the Gatorade. I am seeing this type of split a lot recently (in the past five years). Before that, either employers were hiring a higher caliber candidate, or the pool of candidates had better problem-solving skills. Different teams had sense of ownership and were proud of something done well. Now I see people showing a sense of accomplishment when you they get somebody else to do their work, but they are proud of it and will gladly own up to having to grind away at that phone to find somebody who could do it. So I guess the fault lies with my most recent employers and their willingness to compromise on quality in the hiring process..
OK, back to your article. This was very well put together. Thanks for writing it up. It will help a lot of folks willing to put forth a little effort.
Respectfully
Jeff Bennett
Member of STLSSUG, Missouri

Hi Jeff,

Thanks for your comment, I am glad that you have liked the article and have found it helpful.

Regarding the 'teamwork' part, I would like to reassure you that fortunately this is not that kind of situation. Our reporting team is responsible for many reports for many customers where different kind of (let just say) platform are involved (e.g.: oracle)..., so they don't have much to do with the SQL Server part.

On the other hand I totally agree with you. Unfortunately this kind of 'split of work' attitude is a very common problem.

That's why I try to set a good example 🙂

Regards, Robert Virag

Leonard Rutkowski SSCrazy Points: 2668 More actions · Answer 6

Nice article. I have done something similiar, however, I use Powershell, and run a SQL job as needed. My table is very similiar to yours. When I add a new instance, I just add the server and instance name to the table, run the Powershell script, and let it update all of the columns in the table. I use the instance table to loop through all my instances to check for failed jobs, missing backups, integrity reporting, etc, scheduled through a central SQL Server that we use for monitoring and testing.

Leonard

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 7

Leonard Rutkowski (10/21/2013)
Nice article. I have done something similiar, however, I use Powershell, and run a SQL job as needed. My table is very similiar to yours. When I add a new instance, I just add the server and instance name to the table, run the Powershell script, and let it update all of the columns in the table. I use the instance table to loop through all my instances to check for failed jobs, missing backups, integrity reporting, etc, scheduled through a central SQL Server that we use for monitoring and testing.
Leonard

I'd love to see that script. Any chance of you attaching it to one of your posts?

--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)

Leonard Rutkowski SSCrazy Points: 2668 More actions · Answer 8

Here are some of the PS code I use. To use the instance table, create the table, using the SQLServerInfoTableCreate. Rename it if you like. The LoadSQLServerInfo.ps1 script will update the table. You will need to update where the table is located and the instance or instances you want to connect to, to retrieve the information. I normally insert a row into the table, through SSMS with the server and instance name, but with a few changes, you could actually do an insert, instead of update in the PS code.

The DB Reporting Jobs has the code that does the reporting. Just create a job and add the code. I created a proxy, to use, that can connect to all of the servers in my list, so the script can connect with Windows authorization. You will need to update the smtp information in these scripts, as well as the location of the SQL Server that contains the instance list.

I also added a script, GetDatabaseInfo that will create an Excel spreadsheet. This one I use to inventory my servers.

None of these make any changes to the SQL Servers, but, as has been said before, test first.

Any questions or issues, please feel free to contact me.

Leonard