|
More on SQL Distributed Management Objects
Tech Forum - Dr. Nitin Paranjpe
 The
SQL DMO method of administering SQL Server seems to have caught
on as a concept. After the last article was published, quite a few
administrators as well as developers gave me feedback that a lot
of their tasks could become simpler by using SQL DMO instead of
some other semi-automated or manual methods they are currently using.
So let’s delve deeper into SQL DMO and explore some very useful
things you can do with it.
Making the right choice
One thing I have realised is
that in IT, people don’t think beyond their comfort zone. If you
are very comfortable writing SQL scripts, you will try to manage
everything there. If you have convinced yourself that you are an
administrator, you will do everything through Enterprise Manager.
This approach limits one’s capabilities.
|
It is very important
to know ALL the options available for performing the desired
task, before choosing the most appropriate one.
|
In this context, let us understand
when DMO should be used in comparison with other alternatives.
Alerts
As you know SQL Server anyway
defines alerts using Enterprise Manager or SQL commands. But if
you see how easy it is to add alerts using DMO, you will never want
to go to the UI. Here is how you add an alert:
Dim srv as new sqldmo.sqlserver
Dim myalert as new sqldmo.alert
x.Connect "nitin1","sa"
myalert.Name = ‘sample’
myalert.MessageID= 9002
myalert.DatabaseName=’pubs’
x.JobServer.Alerts.Add myalert
This alert is generated when tempdb
is full. Remember, for an alert to be fully usable, you should also
add a notification to it. But the syntax is simple
x.jobserver.alerts(10).addnotification
<operatorname>, <alert type>
Internally adding an alert invokes
a stored procedure :sp_add_alert
Managing queries
Although DMO should not be
typically used for managing data handling, it can run queries and
get results for further processing.
To execute a query you need
to use the ExecuteWithResults method. To capture the results
you need to create an object of type QueryResults. Here is
how:
DMO with MSDE:
MSDE is the stripped down version
of SQL Server, to be used for desktop applications. MSDE does not
have an equivalent of enterprise manager. Therefore, all the administration
needs to be done programmatically. Fortunately DMO is available
with MSDE. That leads us to a great use of DMO.
|
DMO should be used
to create administrative UI within your desktop applications
that use MSDE
|
Dim srv As New SQLDMO.SQLServer
Dim db As SQLDMO.Database
Dim qr As SQLDMO.QueryResults
srv.Connect "nitin1", "sa"
Set db = srv.Databases("pubs")
set qr = db.ExecuteWithResults("select * from
authors")
debug.print qr.rows ‘returns no of rows
debug.print qr.columns ‘returns no of columns
debug.print qr.getcolumnstring(2, 2) ‘returns contents of 2nd row
and 2nd column
Scripting
In Enterprise Manager we have all
used a very nice feature called Generate Script. This functionality
is available for all objects like databases, tables, stored procedures
and so on. The script generates a text files with the Data Definition
Language command for the object. For example, for a database, it
will create the Create Database command along with the DB options
currently set. Now, if you want to do this for many objects, it
becomes very cumbersome to do it using Enterprise Manager. But there
is good news.
| DMO is the most
convenient way of scripting objects on demand, especially if
you have to generate it across multiple objects |
The concept is simple. Find the required
object in the hierarchy and use the Script method. The Script method
takes three parameters.
object.Script(
[ ScriptType ] [, ScriptFilePath ] [, Script2Type ] ) as
String
- ScriptType
Optional. A long integer that
overrides default scripting behaviour as described in Settings.
- ScriptFilePath
Optional. A string that specifies an operating system file
as an additional target for the generated Transact-SQL script.
- Script2Type
Optional. A long integer that overrides default scripting
behaviour as described in Settings.
So if you use this method for
generating a database script, for say Pubs using the command:
Srv.databases("pubs").script
, "c:\ sample.sql"
… here is what you get.
CREATE DATABASE [pubs] ON (NAME =
N’pubs’,
FILENAME = N’C:\ Program Files\ Microsoft SQL
Server\ MSSQL\ data\ pubs.mdf’ , SIZE = 2,
FILEGROWTH = 10%) LOG ON (NAME =
N’pubs_log’, FILENAME = N’C:\ Program Files\
Microsoft SQL Server\ MSSQL\data\ pubs_log.ldf’ ,
FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
exec sp_dboption N’pubs’, N’autoclose’, N’false’
GO
exec sp_dboption N’pubs’, N’bulkcopy’, N’false’
GO
exec sp_dboption N’pubs’, N’trunc. log’, N’true’
GO
Due to space constraints I
have omitted the remaining script. But this feature works with any
object that is scriptable. A great convenience for administrators,
provided they want to learn a little bit of programming!
DMO Multistrings
This is a nice enhancement
to manage object-oriented programming in a special context. On many
occasions within DMO programming, you need to pass multiple values
within a single parameter. For this purpose, multistring parameters
are supported. Multistring is a single string containing multiple
strings delimited by various characters. Typically these are [ ]
characters as delimiters.
Here is an example of a multistring:
The backup object has a property called Databasefiles. This is the
list of files to be backed up. Here is how you can pass it:
<Backupobject>.DatabaseFiles
= [file1] [file1] [file3]
This is a nice twist, which is not
seen often. Otherwise, we would have required multiple optional
parameters. But how many optional parameters will you build. There
could potentially be many files to backup. So it is simpler to pass
all file names as a delimited string. The string is passed by DMO
internally and used appropriately. Great Stuff. That gives me an
idea.
|
While designing objects,
think of scenarios where YOU could implement multi-string
type of parameters. This could reduce unnecessary amount of
optional parameters.
|
SQL Namespaces
Lastly, let me present another nice
feature of SQL server programmability, which is called SQL Namespaces.
It adds to the programmability given by SQL DMO. Now, DMO allowed
you to do tasks that were possible through Enterprise Manager. So
technically speaking, you could create your own Enterprise Manager
application using DMO. But what if you require to show some part
of Enterprise Manager User Interface within your application? You
would have to recreate that part using DMO and/or SQL commands,
right? WRONG! Microsoft has thought about this need also. It provides
you a nice programmatic way of invoking any UI within Enterprise
Manager, without the user having to open it manually! Is this not
great?
|
Using SQL Namespaces
objects, you can programmatically invoke any UI element that
is available in Enterprise Manager
|
When should you use it? Let
us say you have your own application installed at a customer site.
Within the application you want the user to change database properties.
What happens today?
The customer’s administrators
have to start Enterprise Manager, navigate to your database and
change settings. Why not give it within your application menus itself?
Here is how you do it:
Dim objSQLNS
As SQLNamespace
Dim hArray(10)
As Long
Sub samplens()
Set objSQLNS = New SQLNamespace
objSQLNS.Initialize "SQL Name Space Tester",
_ SQLNSRootType_Server,
"Server=.;UID=sa;pwd=;", HWND
‘Refer to the root of the hierarchy. Remember: the
‘hierarchy on your server may be different
hArray(0) = objSQLNS.GetRootItem
hArray(1) = objSQLNS.GetFirstChildItem(hArray(0),
SQLNSOBJECTTYPE_DATABASES)
‘ get second level server->databases->database(‘quiz’)
hArray(2) = objSQLNS.GetFirstChildItem(hArray(1),
_ SQLNSOBJECTTYPE_DATABASE, "quiz")
‘get a SQLNamespaceObject to execute commands
‘ against on the wanted level
Dim objSQLNSObj As SQLNamespaceObject
Set objSQLNSObj = objSQLNS.GetSQLNamespaceObject(hArray(2))
‘ execute the command; the properties dialog
‘ should appear objSQLNSObj.Commands("Properties").Execute
End Sub
Now the properties dialog of
the quiz database appears within your application. The advantages
are obvious:
- You have just called the dialog.
You have not had to write any code to create the dialog itself.
- The users can only change properties
of the quiz database in this case. No other items in SQL server
are visible.
- This works with remote machines
also. As you get only one dialog related info, it reduces the
number of data transfer and SQL commands generated in the background.
Therefore, the response time is faster and lower bandwidth is
utilised.
Here is the dialog that would
appear:
Now, if you see the SQL profiler
trace for these SQL-NS commands, you will be surprised. There were
more than 70 SQL commands which were fired in the background for
this to work! There is much more possible in SQL namespaces. But
I leave it to you to explore it further.
Summary
Effective use of SQL DMO can
lead to more effective task automation and better integration of
SQL Server within your application.
The secret
Everything I have shown in
past two articles is NOT from some secret source of information
on SQL Server. It is from SQL Server Books Online! More secrets?
This functionality has been available since SQL Server 6.5—that
is, for more than five years now.
| Key learning
point : Please read online help and apply it in practice! |
|
Scenario |
What to use |
Why |
|
Create one database |
Enterprise Manager |
It is a single ad hoc task. EM is the simplest method available. |
|
Create multiple databases as a part of deployment of a packaged
product |
SQL Script |
This needs to be done in an automated manner. Apart from database
creation, there would be other DDL tasks involved. Therefore,
it is best to save all these as SQL Script, which can easily
be bundled with setup script and executed. |
|
Create a database from a third party application using a COM
enabled front-end tool |
DMO |
Integration with front-end code is simpler than creating a DSN,
loading database drivers and so on. Programmers will also find
the object-oriented syntax easy to use. Moreover, they don't
have to learn the intricacies of SQL syntax. |
|
Add 200 users based upon data in some other file (say Excel
/ ASCII) |
DMO |
Enterprise Manager is very inefficient for repetitive tasks.
The SQL code would be much lengthier for this purpose. To Import
nonSQL Server data using native SQL commands is also complicated.
DMO is the natural choice here. |
|
Create a performance counter based alert or a SQL error based
alert at runtime based upon the dynamic environment |
DMO |
It is very easy. Takes very little coding and manages all the
complex SQL commands in the background. |
|
Create a performance counter based alert or a SQL error based
alert at runtime while running a batch process which is implemented
as a stored procedure |
T SQL |
As stored procedure is already running, calling DMO from a stored
procedure would be highly inefficient. SQL is native to stored
procedure. Therefore, in this case you should use SQL directly. |
|
Creating a backup schedule automatically, in a user configurable
packaged product |
DMO |
Users may create databases dynamically. The frequency and method
of backup may change based. |
|
Create a backup schedule for an already running application
|
Enterprise Manager |
The base application is not providing any facility here. Therefore,
UI based configuration is best. |
|
Triggering backup based upon certain conditions like current
transaction load, number of users connected, type of data being
entered |
DMO |
As the invocation of the backup is based upon application specific
parameters, it is better that the application manages the show
dynamically using DMO. |
|
Adding a row in an audit table whenever a row in another table
is deleted |
Triggers |
There is no substitute for triggers here. Whenever there is
a direct data handling related task, DMO has no role to play.
It has to be native SQL from the database. |
|
Adding a row in audit table when user chooses a particular menu
option / URL in the front end |
SQL stored procedure |
Data handling is not done with DMO. It is done with SQL only.
It could have been done by having a dynamic recordset of the
audit table also. But that would be very inefficient. |
 |
About the Author:Dr
Nitin Paranjape is the Chairman and MD of Maestros (Mediline).
He is a consultant with many organisations, covering appropriate
technology utilisation, business application of relevant technology,
application architecture and audit as well as knowledge transfer.
He has authored more than 650 articles on various technology-related
subjects. He can be contacted at nitin@mediline.co.in |
|
