SQL Administration Tools

  • Wesley Brown

    SSChampion

    Points: 12045

    I've been an admin for a few years now. I recently went to work for a managed services company. I've always used EM or custom stored procs to handle all my db maintinace and monitoring. Now though every sql server is behind not one but two firewalls that don't allow rpc in most cases or named pipes ether. I was thinking of working with DMO and building a web admin tool but it looks like at some level DMO uses named pipes and/or wins/netbios lookups on names. I need a tool that will basicly duplicate EM but only talk on tcp 1433 to the servers I need. I know this is tall order. I haven't looked at any third party tools yet. I thought I'd pose the question here first.

    Thanks

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 717951

    EM will run onn 1433, but also uses 435 or 433 or something like that. I caught this on my personal firewall at home.

    The only problem with a web admin is that some things take more time than a web server will allow.

    I'd ask for either firewall access to/from specific IPs if possible. If you have Windows 2000, then terminal services (admin mode) is another possibility.

    Are you looking for complete EM functionality? Or can you live with T-SQL functionality and then script your changes?

    Steve Jones

    steve@dkranch.net

  • Wesley Brown

    SSChampion

    Points: 12045

    I am shooting for full EM functionallity because thats what I'm use to 🙂 I've got my network people sniffing out the traffic to and from the firewalls to see what ports we will need to open up other than 1433 and if this is acceptable to do so with our current security model. I got a gut feeling I'm gonna be using a ton of sprocs to get things done. One of the things I have noticed is even though I give it the IP it still wants to do netbios name resolution and establish rpc calls. I know rpc isn't gonna fly with the network guys. I can't connect with QA ether so I am assuming that QA uses netbios to do some of its work or RPC. Oh well. My question still stands if you know of any good software other than EM to manage multiple db servers. I've used EM to manage as many as 60 servers in the past.

    Thanks for the forums!

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 717951

    Be sure your client is only running sockets. Use Client Network Utility.

    I setup Tiny Software's personal firewall and it will default to allow nothing, but pops a box for each packet and you can see what ports are being requested.

    I know EM uses one other port, but I have used it across the Internet , so it will deinfitely run using just TCP/IP.

    The best solution, IMHO, is some type of remote control program. Using a web interface works, but be sure it is secure, otherwise.... (cut your resume).

    Terminal Services is nice, pcAnywhere is also a good solution. Keep in mind that in this way you can connect to the server and run EM there and let it connect to all other servers.

    Steve

    Steve Jones

    steve@dkranch.net

  • K. Brian Kelley

    SSC Guru

    Points: 114485

    Check your Network Client Utility settings. Make sure TCP/IP is the preferred network library. You can even disable Named Pipes altogether. One other thing you might want to do is use the Alias tab and create aliases for your servers. In that manner you can use a regular string (such as SQL1) to identify the server, but still connect to the IP address. It makes it easier than looking at a bunch of IPs.

    K. Brian Kelley

    bkelley@agfirst.com

    K. Brian Kelley
    @kbriankelley

  • Wesley Brown

    SSChampion

    Points: 12045

    This is what the big problem is. I've got a 65,000sq foot datacenter full of servers all of them are seperated by firewalls. So, My only real solution is to manage via 1433 one layer in but still outside customer firewalls. I will try setting the network library under the genral tab network setup to tcp/ip only

    -Check your Network Client Utility settings. Make sure TCP/IP is the preferred network library.- I'm not sure if this is the same thing you are talking about.

    Thanks,

    Wes

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 717951

    Please let us know what you find out. This is an interesting problem that I am sure others have run into.

    steve

    Steve Jones

    steve@dkranch.net

  • Andy Warren

    SSC Guru

    Points: 119676

    Duplicating ALL the functionality of EM might take some time! Doesnt BMC sell a web based admin product? I use PCAnywhere from home to office workstation, if needed I can open Terminal Service connection to the server from there. Works pretty well.

  • Wesley Brown

    SSChampion

    Points: 12045

    Ok this is what I got by playing with the network libs. First I removed named pipes from the network libs available. Second, I added an lmhost entry onto my management server(this is the server that is one hop out from all the customers) Three I only used SQL auth(SA) to connect to the server. I can push and pull data and see everything. Only 1433 is open ip to ip. Side effects are Netbios is still bouncing around bad but doesn't seem to effect anything since I have lmhost entries on my box for those ip's to resolve to. There is alot of 1434 udp packets getting denied as well. I know this is to monitor sql status on the box. Also, you can't use server services manager to hit the sql server because rpc isn't open. Because RPC isn't open I can't start or stop services remotely. But, we are running HP openview and vpo. vpo is a client that sits on the server and only uses one port but will allow me to start and stop services.

    I think this will get me through for now 🙂

    Wes

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

  • K. Brian Kelley

    SSC Guru

    Points: 114485

    Ok, port 1434 for UDP is what the client is using to try and determine what ports SQL Server is listening on, what instances are available (if SQL2K), whether it's clustered, etc. That's why you are seeing the UDP 1434 traffic bouncing around. The way around this is once again with your Client Network Utility. By setting aliases there , you can also specify to only use port 1433, rather than have the client try and dynamically determine what port to use. Also, by setting the aliases there, I would think EM would then automatically resort to TCP/IP, thereby reducing the NetBIOS calls. Here's an article I found on the Technet site which details these things in a little more detail:

    http://support.microsoft.com/support/kb/articles/Q287/9/32.ASP

    The article talks about the minimum required to connect to SQL Server across a firewall.

    Andy spoke about using PCAnywhere. Other remote control products include NetMeeting (with Remote Desktop Sharing) and VNC, both of which are free. However, I'm not sure of the required ports on either of those.

    Hope that helps.

    K. Brian Kelley

    bkelley@agfirst.com

    K. Brian Kelley
    @kbriankelley

  • Wesley Brown

    SSChampion

    Points: 12045

    I connect to the internal network via vpn and hit a terminal session to start EM on the management server, but after that the mangement server still has to go through the customer facing firewalls. Thats what I needed to see. I forgot about the network client configurator all together. I've locked down the ports and we will run some more test tomorrow. No rpc still means no stopping and starting services but atleast I should get the information I need.

    Thanks again everyone!

    Wes

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 717951

    Brian,

    Thanks for the Q article.

    I think you will be stuck with RPCs due to the firewalls, but probably do not want to have those open anyways. I'd stick with your openview and VPO for services.

    do you need to start and stop services very often?

    Thanks again for the postings.

    Steve Jones

    steve@dkranch.net

  • Wesley Brown

    SSChampion

    Points: 12045

    No I don't, but with as many diffrent customer servers we have I'm just trying to cover all my bases. The things that worry me the most are we don't manage custom apps written by other companies not on our product catalog. I've been in situation where misbehaved apps have caused much grief for me 🙂 with vpo on the servers I should be able to fill all the gaps without having to invest in another third party tool. between all my dbcc, backup and restore scripts I should be covered now.

    Thanks,

    Wes

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

Viewing 13 posts - 1 through 13 (of 13 total)

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