Post reply

Managing many SQL Servers?

  • January 15, 2009 at 11:05 am

    #927558

    I've written a few articles and believe me it's a great feeling to see the feed back you get. Sometime good sometimes bad but I feel that either way you will get a feeling of accomplishment and satisfaction of giving back to the community. Also, it kinda cool to see others using your idea and to see where other take it too.

    Good luck Toby, I'll be looking forward to your article as I'm sure many others will too.

    Rudy

    Rudy

  • January 15, 2009 at 2:50 pm

    #927743

    Toby White (1/15/2009)

    I think the article is great and the methodology is well documented. However, I don't like linked servers, openrowset, opendatasource. I feel they are a security risk, bugy to manage over changes/migrations, and incur unnecessary overhead. I am not sharing this to poopoo your solution, because I understand that mine is more a matter of preference that in reality is subject to details about the environment and philosophy of a particular company.

    I have created a tool that does essentially the same thing as yours, except that it uses a browser front end. It actually stores the metadata much like yours does so that it can populate the drop down list to select which servers to submit the query to. Further, their is a database box to select which database to submit the query to on all the servers that were selected. It seems the tools accomplish roughly the same thing, except that my solution allows any sql to be submitted including DML, which is nifty if I want to submit an updated version of a stored proc on all my DBM databases. Also, mine just makes a connection VIA oledb w/ integrated security versus using linked server technology.

    I do agree that linked servers should be avoided for various reasons including those mentioned above; but I think the use is justified in this case.

    Regardless, even though there are other methods around (such as SSMS 2008 as Rudy mentioned), it would be great to see your alternative as well.

  • January 15, 2009 at 4:31 pm

    #927796

    Great job, Rudy

  • January 15, 2009 at 5:17 pm

    #927809

    Hi Rudy & Everyone,

    Nice solution Rudy. But just to throw an angle, I have resisted taking this approach for some time. I have my reasons:

    I have about 30 servers/instances. A few of them have as many as 60 jobs. The way I monitor them is that every job sends me (and other personnel as needed) an email, but only if it fails. This method has served me perfectly for almost 10 years. I haven't seen any holes in my approach, but maybe some of you can?

    Thanks!

    Uncle Ben

  • January 16, 2009 at 8:20 am

    #928183

    I spent years working with batch files, osql and server lists to retrieve info and execute code on multiple databases simultaneously.

    Now I use Red-Gates Multi script. Its the most useful tool I have and its cheap. When you manage 300 SQL servers its essential.

    I'm lookign forward to the new mutli-script feature in SQL2008 SSMS but dont think it'll be as good as Multi-Script.

    My Idera SQL Admin toolkit also has this functionality but its not as good as Red Gate.

    My 2c

    cheers

    Alan Cranfield

    thanks

    SQL_EXPAT

  • January 16, 2009 at 8:43 am

    #928223

    Rudy,

    Great article. It talked more about your monitoring framework which I feel is the hardest part for a DBA to decide on and setup. I too perform automated monitoring using linked servers. I use sqlcmd instead of isql or osql, but still the same concept. I love this framework for monitoring b/c it's easy and it's allowed my team and I to monitor everything (jobs, space, error log, blocking, automated killing of blocking, replication failures and latency, etc) across ~70 installations around the globe from a central location.

    I saw your solution for job monitoring where you join on sysjobservers to see if a job failed or not. Another join you could add is sysjobhistory. By joining on sysjobhistory you can also report/alert on the actual error message from the failed job. Here's a snippet of my logic to trap failed jobs. I run it every 5mins (for some servers it may be overkill) so hence my long date calculation/check at the end. The filtering isn't perfect, but seems to trap ~99% of the failed jobs for us. In SQL2005, some job failures do not have error codes, some will fail a job step but still mark the job successful. Other jobs will show a warning for a job step but still mark the job successful so I've learned to not trust job outcomes 100% of the time. That's why I also trap on certain key words as well. So far it's kept my team and I honest, although in the earlier days there were some painful learnings as we fine-tuned the logic =). I hope this is helpful:

    select @@servername, jobs.[name], his.sql_message_id, his.sql_severity, his.[message], his.run_status, his.retries_attempted

    ,LastRunTime = convert (varchar(100), (ltrim(str(run_date))+' '+stuff(stuff(right('000000'+ltrim(str(run_time)), 6) , 3, 0, ':'), 6, 0, ':') ) )

    from msdb..sysjobhistory his with(nolock), msdb..sysjobs jobs with(nolock), msdb..syscategories cat with(nolock)

    where his.job_id = jobs.job_id

    and jobs.category_id = cat.category_id

    and his.run_status in (0,4)

    and SUBSTRING(CONVERT(char(8), his.run_date),7,2) = DATEPART(dd, getdate())

    and SUBSTRING(CONVERT(char(8), his.run_date),5,2) = DATEPART(mm, getdate())

    and SUBSTRING(CONVERT(char(8), his.run_date),1,4) = DATEPART(yy, getdate())

    --Filter certain job categories from alerting

    and cat.[name] not in ('Test')

    and (his.sql_message_id <> 0 or his.[message] like '%fail%' or his.[message] like '%Error%')

    --the date conversion below is so we only trap jobs that failed in the last 5mins

    and (convert (varchar(100), (ltrim(str(run_date))+' '+stuff(stuff(right('000000'+ltrim(str(run_time)), 6) , 3, 0, ':'), 6, 0, ':') ) )) >= (getdate() - 0.0034722222222222222222222222222222)

    lc

  • January 16, 2009 at 8:58 am

    #928242

    Hello Leon,

    Thanks for the excellent tip/modification. I will test your code and then modify my reports with this additional information. Thanks!!

    Does this code produce the same information on SQL 2005/2008?

    Are you using SSRS to produce reports? I've made several web reports so that I can have them sent to my Blackberry. The web base reports are easy on the eyes and the develops get there own web reports regarding their test/development server.

    Love hearing how other DBA are managing many SQL servers.

    It's nice to know that your not alone in the sea of SQL servers/instance 🙂

    Rudy

    Rudy

  • January 16, 2009 at 9:19 am

    #928262

    Yes, the code works on SQL2005 and SQL2008 best I can tell. We're all SQL2005 x64 for the most part with a few SQL2008 installations sprinkled in but so far it's worked fine in both environments.

    I don't use the monitoring so much for reporting in my group. We're a small DBA group so besides the usual break/fix and new server deployments we have limited bandwidth. We use the monitoring more for alerting us so we can respond quickly to issues.

    lc

  • January 16, 2009 at 10:30 am

    #928334

    Rudy,

    When you say 'this ability' what are you referring to? I ask because your description of the original script says it works in earlier versions of sql.

    Glen

  • January 16, 2009 at 11:21 am

    #928374

    Glen,

    This process/code works on all our SQL server. We are using versions 2000, 2005 and 2008 (we also has 7.0 and it work on that too). We also have a mix of 32 bit, 64 bit and servers will multiple instances. I'm not sure if this answers your question, if not can you re state it?

    Thanks,

    Rudy

    Rudy

  • January 16, 2009 at 12:42 pm

    #928456

    Rudy,

    your message of Jan 15 stated "Just wanted to mention that this ability is available in SQL 2008 but does not work against older versions of SQL server".

    I was unclear on which ability you were referencing.

    Thanks,

    Glen

  • January 16, 2009 at 1:36 pm

    #928500

    Oh, I see. Well SQL 2008 will not allow you to remotely execute code (via SSMS) to SQL 2000 and earlier. Why I'm not sure and I have not tested it but this comes from Microsoft.

    This code does not care about version numbers as long as the code you are trying to execute is a valid for that version of SQL.

    That's it.

    Rudy

    Rudy

  • January 17, 2009 at 10:53 am

    #928709

    very nive script.

    I am using a very advanced automated management tool named EZManage SQL Pro

    and one of it's features is MultiScripting.

    you can get it from

    http://www.futureitsoft.com

    itay

Viewing 13 posts - 16 through 27 (of 27 total)

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