Managing many SQL Servers?

Question

Managing many SQL Servers?

Rudy Panigas

SSChampion

Points: 10702
More actions
January 14, 2009 at 10:49 pm

#196132

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

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

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

Rudy Panigas SSChampion Points: 10702 More actions · Answer 1

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

Mike DiRenzo SSCrazy Points: 2013 More actions · Answer 2

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

Rudy Panigas SSChampion Points: 10702 More actions · Answer 3

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

Glen Moffitt-317424 Old Hand Points: 301 More actions · Answer 4

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

SequelDBA Ten Centuries Points: 1093 More actions · Answer 5

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

Rudy Panigas SSChampion Points: 10702 More actions · Answer 6

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

paramejunk SSC Journeyman Points: 85 More actions · Answer 7

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

Rudy Panigas SSChampion Points: 10702 More actions · Answer 8

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 Panigas SSChampion Points: 10702 More actions · Answer 9

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

Toby White SSCertifiable Points: 6187 More actions · Answer 10

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.

Rudy Panigas SSChampion Points: 10702 More actions · Answer 11

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

Toby White SSCertifiable Points: 6187 More actions · Answer 12

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.

Rudy Panigas SSChampion Points: 10702 More actions · Answer 13

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

Toby White SSCertifiable Points: 6187 More actions · Answer 14

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