Managing many SQL Servers?

  • Comments posted to this topic are about the item Managing many SQL Servers?

    Rudy

  • Hello Everyone,

    Hope you like the article and I am hoping to write more showing how to use the remote management to help do other tasks.

    If you have any ideas as to what can be done, I would love to hear about them.

    Thanks,

    Rudy

    Rudy

  • Very nice article. It sound as if this util grew and grew and became what it is over time. Those are the best kind of tools. And whats more, you sharing it. Good job!

    -Mike

  • Thank you very much for all the kind words. I feel that I've been a DBA long enough (8 years) and that it's time to share with all the people on this great site!

    Rudy

    Rudy

  • Hi Rudy,

    I scanned the article, looks intriguing. I have about 80 instances, with more to add, that I have to monitor, and have had thoughts along the same lines. I would want to be monitoring not only for success/failure of routines but performance measurement and database health as well.

    Keep up the good work!

    Glen

  • Rudy,

    Nice work! The time you took to document this and share it are what makes this site what it is - A Valuable Resource!

    Thank you!

    Kevin

    KU

  • Hello Glen,

    Sounds like that I was going through when I first started this code. For example, I only wanted to know when a job failed so that I could review and resolve the problem. This setup will do that for you. It really will cut down the time need to review failed jobs.

    I think my next article will be the SQL error log analysis report. Using the code from this article and some additional steps, I can quickly review any SQL server's error log without have to connect to the server/instance and without have to view all the successes in that log file. It helps to review failed jobs.

    Rudy

    Rudy

  • Rudy,

    If I am not mistaken, this connection would be a linked server connection to the other server.

    If so, can you create objects (i.e. Tables, SP's, Views, Jobs, Functions) remotely via a linked

    server connection?

    Can you modify Schema of objects via a linked server connection?

    If not, do you know how to "Create Objects" and "Modify Schema" via a linked server

    connection?

    Thanks,

    PMJ

  • Good question. Not sure if this can be done but I believe (not tested this) that you could. Best to try it on a test server first. Up to now I have only used this technique to pull information. Link server connection documentation is very limited so that best way to know for sure is to test.

    Sorry that I couldn't help more and let us know what you find.

    Thanks,

    Rudy

    Rudy

  • Hello Everyone,

    Just wanted to mention that this ability is available in SQL 2008 but does not work against older versions of SQL server. Also you have to create groups in order to execute code remotely. Other DBAs find that this is a preferred way of executing code remotely as most business have a mix of SQL server versions.

    I'm am currently making new remote execute code and reports for our Oracle servers. Yes, we have Oracle servers too and the pilot so far is working well. Soon our Oracle DBA can get web based reports on the Jobs statuses of the Oracle servers. Oracle's reporting is not as easy to use/create/modify as Microsoft's Reporrting Sercvices.

    Just thought you might wanted to know.

    Rudy

    Rudy

  • 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.

  • Hello Toby,

    Thanks for your reply. Would you be interested in sharing this code/application with others on this site? Maybe you could write and article? I for one would be interested to see it working. Can your tool be scheduled to, for example, to collect data every hour? My link connections are pulling data throught the day and night with no overhead to the SQL server or its users. I understand that there are many way to do the same thing, my code is just one of them. I'm not proclaiming that's the best but so far it works for my needs.

    This is the kind of feed back I enjoy. To see what other are doing to make there job easier. We DBAs have lots on our plates including review and learning new versions of SQL server. I just hope that others DBAs will find ways to make there day a bit easier and share them with us here. Making SQL server work for the business is number 1 on the list but making it work for DBA should be number 2.

    Thanks,

    Rudy

    Rudy

  • I see the ability to schedule jobs that run on all the servers an advantage to your method because mine is essentially a dumb terminal that sends commands and displays results. It is actually implemented via HTML/DOM/VBS/COM/TSQL. It might be worth creating a post for, but it would take me a lot of time to create a post as concise as yours. Anyway, the idea is create the objects in SQL and then connect to that DB in the web page. The web page starts out with:

    set oFile=CreateObject("Scripting.FileSystemObject")

    set oCon = CreateObject("adodb.connection")

    oCon.ConnectionTimeout=3

    oCon.commandtimeout=180

    sCon = "Driver={SQL Server};Server=dc00976;Database=DBA;Trusted_Connection=yes;"

    servercombo = " "

    resultsTable = ""

    set rs = CreateObject("ADODB.Recordset")

    rs.ActiveConnection = oCon

    Many people might say that it is old technology (ie not .net) but who cares - it gets the job done and the legacy COM objects are still available for legacy style ASP. Anyway, you have obviosly put a lot of thought into your methodology and the documentation therof. I thank your for sharing your method in your article.

  • Toby,

    I think many people here would love to see and use your tool. I would. Sometime you need just need a solution like yours. So I'm hoping to encourage you to write an article here. I know it takes time but it's really worth the efforts.

    Thanks,

    Rudy

    Rudy

  • I suppose it's only fair since I have used this site for so many years now. Thanks for the encouragement.

Viewing 15 posts - 1 through 15 (of 27 total)

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