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?

Answer 1

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