My technical skills
My favorites
My picture album
Shortcut keys
My code library
VB resources
SQLServer resources
SQLServer books
Replication FAQ
Scripting resources
ASP resources
Search my site
Sign my guestbook
Contact information
SQL Server Articles New
|
Click here to find out the top 15 SQL Server books purchased by this site's visitors! NEW |
| Want to keep in touch with the latest in SQL Server world? Email vyaskn@hotmail.com with 'subscribe' in the subject line |
| Title: | Real-World SQL-DMO for SQL Server |
| Authors: | Allan Mitchell, Mark Allison |
| Publisher: | APress |
| ISBN: | 1590590406 |
| Pages: | 400 |
Click here for more information or to buy this book from: Amazon.com or Amazon.co.uk or Amazon.ca
SQL DMO?!? What is it? Unfortunately, that's the kind of response I would expect from many developers. Here's a description of SQL-DMO from SQL Server 2000 Books Online:
"SQL Distributed Management Objects (SQL-DMO) encapsulate the objects found in Microsoft® SQL Server 2000 databases. SQL-DMO allows applications written in languages that support Automation or COM to administer all parts of a SQL Server installation. SQL-DMO is the application programming interface (API) used by SQL Server Enterprise Manager in SQL Server 2000; therefore, applications using SQL-DMO can perform all functions performed by SQL Server Enterprise Manager.
SQL-DMO is intended for any Automation or COM application that must incorporate SQL Server administration"
Things are changing, and slowly DBAs and developers are realising the power and potential of SQL-DMO. But, where can one go to learn more about SQL-DMO? Of course, SQL Server Books Online and MSDN have documentation and examples. But they just introduce you to basics, and don't really show you how to implement SQL-DMO based real world solutions. Sadly, there hasn't been a good SQL-DMO book in the market for a while. To my delight, I came to know that my friends Allan and Mark are working on an SQL DMO book. I really looked forward to the book, and my wait was worth it. I received a copy of this book from them, and had a chance to go through it.
This is the ONLY book available, that covers SQL-DMO for SQL Server 2000 (and SQL Server 7.0 as well). I happen to know the authors Mark and Allan, through SWYNK newsgroups, and I know for a fact that they've been working with SQL DMO for a long time. Their experience really speaks in this book. What I like about this book is, it is filled with code samples, examples, sample applications. Code samples are written in Visual Basic (VB6) and VBScript. I am not overly exaggerating, when I say, there isn't a page in this book, that goes without a code snippet. Best of all, these code samples cover real world scenarios, as the title suggests. Authors take real world scenarios from their own experiences and show you how to accomplish those specific tasks using SQL-DMO, in a very informal manner.
This is a very useful book and a good reference for anyone interested in learning SQL DMO. With all those well written examples, you will quickly get comfortable with coding in SQL DMO, and developing your own administrative routines based on SQL DMO. This book is for SQL Server developers, Database Administrators (DBA), let them be beginners or advanced. It doesn't ship with a CD, but you can download all the source code from this book's website.
Gert Drapers wrote a very insightful foreword describing how SQL DMO development started, and what the development team's goals were. Gert is currently working as an architect in Transaction Services team at Microsoft and he is a former product unit manager of SQL Server Management Tools team. Another foreword was written by a well-respected SQL Server MVP, Sharon Dooley. Now, let me quickly walk you through the chapters:
Chapter 1: Getting Started with SQL-DMO
As the title indicates, this chapter introduces you to SQL-DMO and starts off with examples, showing you how to connect to an SQL Server instance with different authentication modes, create a database using SQL-DMO. Then it shows you how to create database objects like tables, views, triggers, stored procedures and how to setup relationships between tables, using foreign key constraints, how to set various database options. Provides you with a sample DMO application, that can be used to create database objects.
Chapter 2: Backup and Restore: A DBA's Bread and Butter
This is a very good chapter. It starts off by explaining, what database backups are, what are the different types of backups supported by SQL Server, and then the authors discuss some sample backup/restore scenarios and methods, SQL Server 2000 database recovery modes (simple, full, bulk-logged). After a good introduction to backups, authors briefly discuss the BACKUP command and go on to write an application that backs up databases and transaction logs using SQL-DMO. This application shows you how to loop through available servers, databases, devices using various DMO collections. Code for another application is also provided, that shows you how to restore databases and transaction logs.
Chapter 3: Managing Users of Your Database and Server
Yet another good chapter. Starts off by introducing you to terminology like Login, User, Fixed Server Roles, Database Roles, Permissions etc. Then complete code (along with fantastic explanation) is provided for creating logins, users, database roles, adding removing users from database roles, granting, revoking and denying object level and statement level permissions to/from users and database roles.
Chapter 4: Using SQL-DMO with OLE Automation
This chapter shows you how to use SQL-DMO object model, directly from T-SQL, using a bunch of OLE Automation stored procedures like sp_OACreate, sp_OAMethod, sp_OAGetProperty, sp_OASetProperty, sp_OAStop, sp_OAGetErrorInfo, sp_OADestroy. Starts off with example that shows you how to invoke DMO from T-SQL for creating Logins and users. This is a bit cumbersome, as adding logins and users is much easier, when done directly from T-SQL. But hey, there's more. The next example shows you how to move or transfer a databases between servers, right from T-SQL. Cool...isn't it? Hold on, there's more! Ever wondered, how to generate scripts for your database objects from within T-SQL? That's exactly what the next example is about. This chapter not only shows you how to use DMO, but more importantly, it teaches you how to perform OLE Automation using T-SQL. This opens doors to endless possibilities for T-SQL programmers.
Chapter 5: Jobs, Alerts, and Operators
Efficient management of jobs, alerts and operators is very vital for the smooth operation of any environment. Job management is one of the core strengths of SQL-DMO, and this chapter equips you with everything you need to know about managing jobs using SQL-DMO. Starts off with introduction to jobs, alerts, operators and briefly discusses how to add jobs using T-SQL. Then complete code is provided to add a new operator and to check if an operator already exists. Then a full-blown example is provided covering all aspects of jobs, like, creating jobs, adding job steps, creating job schedules, querying information about jobs. The chapter ends with a DMO example, that creates an Alert.
Chapter 6: Viewing and Setting Server-wide Options
When you right click on the server name in Enterprise Manager and select 'Properties' from the popup menu, you will get a tabbed dialog box (with tabs like General, Memory, Processor, Security, Connections, Server Settings, Database Settings, Replication) with various server level settings. This chapter shows you how to view, set, alter all those settings using SQL-DMO code. Shows you how Enterprise Manager works under the hood, and provides samples that use the Registry and Registry2 objects.
Chapter 7: Replication and SQL-DMO
Introduces you to various types of replication topologies like snapshot, transactional and merge. Provides you with DMO code, to create a distributor, enable publishers and subscribers. Shows you how to create publications, add articles to those publications. Shows you how to create subscribers, and add subscriptions. Chapter concludes with an example on generating replication scripts, using DMO.
Chapter 8: QALite
This chapter is about a sample application called QALite. QALite is a scaled down version of Query Analyzer, that can accept commands, connect to an SQL Server, execute those queries and display the results. This is a great exercise for testing all that you've learned about DMO, in the previous chapters. QALite is also very helpful tool for Microsoft Data Engine (MSDE) users, as MSDE doesn't ship any client tools. Complete code for QALite is provided with explanations.
Chapter 9: Using SQL-DMO to Script an Entire Server
This chapter walks you through a real world application called "Scripter", developed by the authors, in VB 6.0. They use this application in their production environments, on a day-to-day basis. Scripter is a command line utility and can script logins, alerts, operators, replication, DTS packages, jobs, backup devices, databases, database roles, database users, defaults, tables, triggers etc. As you can see, it is a very useful and powerful application. Complete source code can be downloaded from the Apress website.
Appendix A: SQL-DMO Object Library Quick Reference
Walks you through all the objects and collections provided by SQL-DMO object model. Very useful!
Appendix B: Knowledge-Base Articles and Other SQL-DMO Resources
Lists all the important KB articles from Microsoft site, that discuss DMO related bugs and problems and fixes. A list of How-To articles is also provided, along with a list of useful sites from around the web!
Overall, it is a good book. Authors often show you how to do stuff using T-SQL, which is very useful. There are plenty of tips, tricks, warnings etc., that come directly from the authors' real world experiences. I highly recommend this book for anyone interested in delving deep into SQL-DMO!
Click here for more information or to buy this book from: Amazon.com or Amazon.co.uk or Amazon.ca