Compatiblity of SQLDMO and SQLSMO with respect to scripting via COM

Hi all,

I need your help:

Context:
I have developed since long an installation with InstallShield's InstallScript language. This installation "talks" to SQLServer2000 in order to install, upgrade, backup and restore the application's database. All this is done in the InstallScript code using SQLDMO currently, using the COM interface as if using VB script.

Problem:
Now as we are preparing to support installation on SQL2005, I am investigating the usage of SQLSMO, as SQLDMO cannot be excepted on all target systems (SQLExpress must be supported, too).

I looked in the SMO namespace and recognized, that the contained classes are registered as COM objects, too. Due to this I have no problem creating a Microsoft.SqlServer.Management.Smo.Server object from my scripting code.
But - opposed to SQLDMO the SMO server object does not have a Connect() method.

Looking further I found the Microsoft.SqlServer.Management.Common.ServerConnection object which seems to have the functionality I need - but this object is not registered as a COM object and as such is not scriptable - I am stuck :-(

Generally it seems, that SQLSMO is by far not a replacement or enhancement to SQLDMO. So - how to solve tasks which where using SQLDMO up to now?

Please - give advice!

Thanks,
Jo

Sorry, Jo, you're correct with respect to COM support for SMO - it's not there. It's not hard to build a VB console application to do what you're attempting, though, and then you can connect to the server either through the Connect() method or by setting arguments in the ServerConnection object. I've used both successfully.

There's a lot of argument in this forum whether or not SMO is an enhancement to DMO, and while some have had some performance issues with SMO, I feel that the object model is much more straightforward and easy to use than its predecessor. We just have to approach things differently.

Compatiblity level in SQL 2005

What does are the rammifications in SQL 2005 of setting the compatibility level for a database to a lower level, for example SQL 2000 (80)?

Does this affect the underlying datastructure, the stored files on the server, or the indexes?

What effect does changing the compatibility level have? Does changing it cause the server to have to do any work to the indexs or table?

TIA!

refer this link,

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-48ebca266659.htm

performance dashboard cannot be used if the compatability is not 90.......

|||

Compatibility level allows you to keep databases in SQL Server 2005 that remain compatible with prior versions of SQL Server. This also means that you cannot use Transact-SQL extensions introduced in SQL Server 2005 with a SQL Server 2000-compatible database. AFAIK there is not much change in storage architecture of sql server 2005 from 2000. Still they are stored in Pages/extents.

Madhu

Compatibility and Performance

Is there any performance hit when using a lower compatiblity mode? IE 70 instead of 90?

Compatibility level 70 means you are trying to SQL Server 7.0 mode? Is that what you really want to do? You obviously will not be able to use any SQL 2005 features.

Look for sp_dbcmptlevel in BOL and check out all the info.

|||

Yes we want the database to be in 7.0 mode. I understand the options. What I want to know is... is there a performance hit by using different compatiblity modes? Since SQL 2005 'performs' better than SQL 7 or SQL 2000, will we see any of that performance related to queries etc, or will the added overhead of the compatibility negate the performance improvements.

Also, what performance related issues are there to consider when using different modes, if any?

Thanks,

Raymond Laubert

MCDBA, MCITP:Administrator, MCT