SQLDMO Object

[Verve]

Hi,
how to create an SQL DMO object in .net and use it to enumerate the
properties of Sql server. Can we use this object to list properties
without logging in to the server?

[Simon Hayes]

You can connect to a server like this in C#:

SQLDMO.SQLServer2 srv = new SQLDMO.SQLServer2();
srv.Name = "MyServer";
srv.LoginSecure = true;
srv.Connect(null,null,null);

Then you can access the various properties and methods - see Books
Online for the full SQLDMO object model. As I mentioned in reply to
your previous post, SQLDMO is executing TSQL behind the scenes, and it
requires an authenticated connection to the SQL Server for most
operations. An exception to this would be using something like the
Application.ListAvailableSQLServers() method, but for anything where
you're querying the properties of a specific instance, you will need to
log on to MSSQL.

Simon

[Verve]

That's what my problem is. I have to query some different machine with
different administrator. I can give a list of the properties that I
need to retrieve. For the rest I have found WMI classes:
1. Replication
With which servers
Publisher/Subscripto
2. Memory allocation (Dynamic or fixed)
How much is allocated
3. Memory allocated to SQL query
4. Maximum Concurrent User Connections
5. Startup parameters
6. Enabled Protocols (Named Pipes, TCPIP etc...)
7. Backup Service enable and details
8. DB Maintenance Plans
9. Log info (Date, Size, DB)
10. SQL Logins
11. Only need to gather the SQL specific login accounts
12. Linked Servers

[Simon Hayes]

You could get most of that information from SQLDMO (see the
Configuration, Registry, Replication and Login objects in the SQLDMO
hierarchy), but some of it isn't really clear - what do you mean by
"backup service", for example? Personally, I would seriously consider
buying a tool to do this, rather than reinvent something myself.

As for connecting to different servers, there's no way to get
information from any server without authenticating first, at the OS
level and/or the MSSQL level. I don't know anything about your
environment, but monitoring is a general systems issue, not an
MSSQL-specific one, so you might need to discuss it with your
manager/colleagues/client, to agree a general solution which allows
scripts and tools to connect to any server. You might create specific
domain accounts for monitoring purposes, for example.

Simon