SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


When the reporting team needs an assist...


When the reporting team needs an assist...

Author
Message
Robert_sqlapprenticeDOTnet
Robert_sqlapprenticeDOTnet
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 341
Comments posted to this topic are about the item When the reporting team needs an assist...
vijayreports
vijayreports
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 25
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
Ol'SureHand
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 720
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
Robert_sqlapprenticeDOTnet
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 341
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
chudman
SSC-Addicted
SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)

Group: General Forum Members
Points: 423 Visits: 405
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
Robert_sqlapprenticeDOTnet
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 341
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 Smile

Regards, Robert Virag
Leonard Rutkowski
Leonard Rutkowski
Old Hand
Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)

Group: General Forum Members
Points: 326 Visits: 1213
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
Jeff Moden
SSC Guru
SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

Group: General Forum Members
Points: 114116 Visits: 41361
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Leonard Rutkowski
Leonard Rutkowski
Old Hand
Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)

Group: General Forum Members
Points: 326 Visits: 1213
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
Attachments
PowerShell.zip (7 views, 15.00 KB)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search