Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Managing many SQL Servers? Expand / Collapse
Author
Message
Posted Thursday, January 15, 2009 9:13 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 9, 2012 2:48 PM
Points: 493, Visits: 636
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.
Post #637279
Posted Thursday, January 15, 2009 9:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:19 PM
Points: 318, Visits: 1,127
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



Post #637296
Posted Thursday, January 15, 2009 9:55 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 9, 2012 2:48 PM
Points: 493, Visits: 636
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.
Post #637334
Posted Thursday, January 15, 2009 10:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:19 PM
Points: 318, Visits: 1,127
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




Post #637401
Posted Thursday, January 15, 2009 11:00 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 9, 2012 2:48 PM
Points: 493, Visits: 636
I suppose it's only fair since I have used this site for so many years now. Thanks for the encouragement.
Post #637405
Posted Thursday, January 15, 2009 11:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:19 PM
Points: 318, Visits: 1,127
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



Post #637407
Posted Thursday, January 15, 2009 2:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 21, 2014 4:03 PM
Points: 17, Visits: 129
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.
Post #637618
Posted Thursday, January 15, 2009 4:31 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:38 PM
Points: 31,355, Visits: 15,819
Great job, Rudy







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #637680
Posted Thursday, January 15, 2009 5:17 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 8, 2014 4:10 PM
Points: 21, Visits: 103
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



Post #637694
Posted Friday, January 16, 2009 8:20 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 27, 2012 9:09 PM
Points: 136, Visits: 383
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
Post #638130
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse