Disk Space - DTS
-
Hi All
Requirement:
My requirement is to have a centralised database to monitor the diskspace of multiple SQL Servers.
We need to define a job to fulfill this requirement.
Contraints:
We cannot hard code any connection informations of SQL Servers.
I am using a centralised DB, MonitorDB which resides in MonitorServer.
Following are the steps I have to taken to find a solution for the above mentioned requirement.
1. Created a DTS package,DTSConnection, which contains connection objects of all the SQL Servers.
2. Created another DTS package, DTSDiskspace.
In this DTS package, using ActiveX script I am trying to get the all connection objects - Server Name, Password.
which are defined in the first package, DTSConnection by creating an instance of it.
3. Create Linked Server using the connection information
4. Exec GetFreeSpace, this stored procedure uses xp_fixeddrives and update disk space details in tables.
5. Drop all the Linked Servers.
ActiveX script of DTSDiskspace:
Set objConn=CreateObject("ADODB.Connection")
Set objDTS=CreateObject("DTS.Package")
' Load DTSConnection Package
objDTS.LoadFromSQLServer "DBDEV01", "jkp","jagan123", , , "", "", "DTDKBOSS1"
'Creating Linked Servers
objConn.Open "provider=sqloledb;data source=MonitorServer; initial catalog=MonitorDB;user id=sa; password=password"
For i = 1 To objDTS.Connections.Count
strServer=objDTS.Connections.Item(i).DataSource
strPassword = objDTS.Connections.Item(i).Password
objConn.Execute " EXEC sp_addlinkedserver " & strConn
objConn.Execute "EXEC sp_addlinkedsrvlogin @rmtsrvname = '" & strServer & "' , @useself = 'false', @locallogin = 'jkp', @rmtuser = 'sa', @rmtpassword = '" & strPassword & "'"
objConn.Execute "Exec GetFreeSpace " & strServer
objConn.Execute "EXEC sp_dropserver " & strServer & ",'droplogins'"
Next
Set objConn=Nothing
Set objDTS=Nothing
Problems facing:
1.objDTS.Connections.Item(i).Password always returns NULL.
Any solution to retrieve the password?
2.Any better solutions?
Thanks,
JP
-
1. You cannot retrieve the password from a DTS connection. This is by design as a security measure.
2. We use SQLH2 for this.
--------------------
Colt 45 - the original point and click interface
-
I collect the disk space info on each of my SQL Server here at my job reporting this info from a centralized SQL Server. To get the info to the central server, I set up DTS packages on each of the satelite servers to push the info to the central server at a certian time. No need to link the servers this way. Another variation, would be to pull the data in from the central server with one DTS package that includes the data feeds to each satelite server. Nothing against linked servers, just I try to stay away from them if I have a choice.
Dave
-
I run a stored proc on my monitoring server that uses the linked server approach to call sprocs on the remote servers. It requires me to install in the remote servers 3-4 sprocs plus a low priviledge account for the link. All the server names are in a table I created on the monitoring server.
Logically it is a bit convoluted but all persistant data is on my monitoring server and if I have to set up a new SQL Server for this, I just have one extra script to install in MSDB of that new server.
I'm thinking of changing all this to run the logic from perl scripts on my monitoring machine with just direct connects to the other servers.
I hope that monitoring multiple servers gets easier in MSSQL 2005.
Jeff
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply