SQL Server: Why do use SMO?

Question

I have been working with SQL Server for a couple of years. I have heard about SMO but I don't know anything about it. What are the benefits of using it? Should I learn and start using SMO in my SQL Server projects (mainly data warehouse development)? Why?

Mincing

Answer 1

It depends on what you're trying to do. SMO is SQL Server Management Objects. It is a set of libraries for managing SQL Server programmatically. For example if you're trying to build a clone of SQL Maangement Studio then SMO is something you probably want to look into. OR if you're trying to manpulate the structure of your database programmatically then that's the place to look.

Otherwise, I wouldn't bother.

Monaco

There are a lot more use cases than that, e.g., programmatically extracting the DDL for your database objects. – Puff

Answer 2

From Microsoft:

Overview (SMO)

SQL Server Management Objects (SMO) are objects designed for programmatic management of Microsoft SQL Server. You can use SMO to build customized SQL Server management applications. Although SQL Server Management Studio is a powerful and extensive application for managing SQL Server, there might be times when you would be better served by an SMO application.

For example, the user applications that control the SQL Server management tasks might have to be simplified to meet the needs of new users and to reduce training costs. You might have to create customized SQL Server databases, or create an application for creating and monitoring the efficiency of indexes. An SMO application might also be used to include third-party hardware or software seamlessly into the database management application.

The SMO object model extends and supersedes the Distributed Management Objects (SQL-DMO) object model. Compared to SQL-DMO, SMO increases performance, control, and ease of use. Most SQL-DMO functionality is included in SMO, and there are various new classes that support new features in SQL Server. The object model is intuitive and uses SQL-DMO terminology, where it is possible, to help transfer your skills.

You can download SMO here:

Microsoft® SQL Server® 2008 R2 Feature Pack

And for getting started programming:

Creating SMO Programs

Ellanellard

Thanks, what do I need to start using SMO? Do I need full Visual Studio 2008 or is SMO included in SQL Server 2008 EE? – Mincing

Answer 3

I have used SMO to automatically script out object code and user permissions and add to version control.

By doing this I can save privileges or object DDL as of a point in time for my auditing team or my own research or for cloning a server.

I also use it so I can quickly compare object code from specific dates without needing a snapshot / backup.

Recently I used SMO in a Disaster Recovery Project to script out all Server Permissions and System Database Object Permissions and run the script on the replacement server.

Panhellenic

I do this as well. SqlPubWiz was the only option before this. – Hoahoactzin

Answer 4

I've recently created an POS/Work Order Management application with a SQL database backend and the SMO library. The SMO gave my a application a lot of flexibility to control the database in terms of work order records, user's records an even my own set of user roles. Helping me to differentiate SQL users when managing a specific database. So, my take is that it all depends on the extend of your use of SQL and how much you may need to automate and control certain aspects of your SQL database.

Disclosure