Welcome to my SMO (SQL Management Objects) Blog

I’m Ray Wampler and I have decided to blog about SMO: SQL Management Objects, which is yet another Microsoft Data Access library, geared toward providing a complete administrative API for Microsoft SQL Server that you can use with any .Net language such as C# or VB.net.

I have been programming for almost 30 years and started database development back in 1984 with dBase II. (Trivia Note: there never was a dBase I.  Ashton-Tate thought it would be better marketing to start at version 2.)  When I worked at EDS (Electronic Data Systems, founded by Ross Perot) I started with Microsoft Access version 1.0 and SQL Server 4.21.  I then went to work for Pacer/CATS, a company that made ticketing and concessions software for theaters and amusement parks.  There I became the database administrator and architect, working with Access, and SQL Server.  Since then I worked with every version of SQL Server, plus Oracle and MySQL.  I still prefer Microsoft SQL Server because it offers the best performance and ease-of-use at a reasonable cost.

I began programming with Microsoft .net since version 1.0 in 2002, much of it involving databases accessed through ADO and ADO.net.  I’ve also been keeping up with Microsoft’s evolving data access technologies: LINQ and the ADO Entity Framework, as well as using various data access layer and libraries like the Microsoft Enterprise Practices Data Access Library, Nhibernate, and .Net Tiers.

Where does SMO fit into all this?  Unlike ADO and it’s predecessor ODBC, which offer generic access to any number of data sources, SMO is for SQL Server only.  Although this sounds limiting to anyone experienced with n-tier programming, by specializing in SQL Server SMO lets you easily do many things that would be difficult or impossible with a database-agnostic data access technology.

Some of the things you can do with SMO include:

·         Modify and view SQL Server settings, information, and user options.

·         View and modify SQL Server settings and configuration options.

·         Back up and restore databases.

·         Create a new database or drop and existing database.

·         Register the instance of SQL Server in the Active Directory directory service.

·         Subscribe to and handle SQL Server events.

·         Reference databases, endpoints, credentials, logins, linked servers, system messages, DDL triggers, system data types, and user-defined messages.

·         Detach and attach databases.

·         Stop processes or databases.

·         Grant, deny, or revoke permissions on the database.

·         Enumerate information about the server.

·         Read the error log.

·         View and modify SQL Server engine objects.

·         Perform DDL (data definition language) tasks on an instance of SQL Server.

·         Script databases dependencies.

·         Transfer database schemas and data.

·         Reference database objects in collections, such as tables, users, and triggers.

·         Set up database mirroring.

·         Check data, allocations, catalogs, and tables.

·         Issue a checkpoint.

·         Grant, revoke, and deny permissions to users on the database.

·         Run Transact-SQL statements.

·         Enumerate database information, such as locks or object permissions.

·         Monitor the number of transactions.

·         Set the database offline or online.

·         Change the owner of the database.

·         Update statistics.

·         Shrink the database.

·         Truncate the log.

 Hopefully the large list above answers the question: how can you have an entire blog dedicated to an obscure, specialized and little-known Microsoft technology?  As I’ve been learning SMO I’ve been Googling for help on all the tasks that I’ve needed to do, and have not found a good reference or set of tutorials.  So my each issue of my SMO blog will be dedicated to demonstrating how to do each of the above tasks and many more.