1 / 17

Mastering SQL Server Management Objects (SMO): A Comprehensive Guide

Learn how to leverage SMO to manage SQL Server effectively. Explore database creation, user management, job scheduling, and backup strategies using SMO classes in .NET. Get insights from Arie D. Jones, a seasoned Microsoft consultant.

hailey
Télécharger la présentation

Mastering SQL Server Management Objects (SMO): A Comprehensive Guide

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DBA Toolkit: Server Management Objects(SMO) Arie D. Jones Senior Microsoft Consultant Perpetual Technologies, Inc. February 21st, 2008

  2. Speaker Background • Lead Microsoft Consultant for Perpetual Technologies • 20+ years of programming experience • 11 years of experience with SQL • Lead author ‘SQL Functions’ book • Etc.

  3. Server Management Objects • What is SMO? • Replacement of DMO • Used to program all aspects of managing a SQL Server. • SQL-DMO: COM based…Yuck! • SQL-SMO: .NET based….Yeah! • Compatible with 7.0, 2000, & 2005!

  4. SMO .NET Classes • Root • Microsoft.SqlServer.Management.Smo • Agent • Broker • Mail • RegisteredServers • WMI Your logo here

  5. So what can we do…. • Almost anything… • Create Databases • Script Objects • Create Jobs • Configure the SQL Server • Assign Permissions • Create Users Your logo here

  6. Demo Time! • Hooking into the Server Your logo here

  7. Server object • Things to remember • Default connection is Windows Authentication and Local instance • Windows authentication is based upon user or entity running the application • Server can be default or named instance • You can also search for the server • Remember to check which types these functions return! • Remember about explicitly calling Connect()! Your logo here

  8. Server Object (cont.) • Remember that you need these references • Microsoft.SqlServer.Smo • Microsoft.SqlServer.SmoEnum • Microsoft.SqlServer.SqlEnum • Microsoft.SqlServer.ConnectionInfo Your logo here

  9. Demo Time! • Creating and Manipulating Databases! Your logo here

  10. Databases • Things to remember • Changes to the database structure are not automatically applied • You cannot simply drop items when iterating through a collection • Scripting can be performed even on non-existent objects Your logo here

  11. Demo Time! • Managing Users and Security! Your logo here

  12. Users and Security • Things to remember • Permissions are based upon ServerPermissionSets • Permissions can be enumerated by using ServerPermissionInfo object and EnumServerPermissions() Your logo here

  13. Demo Time! • Creating and Managing Jobs Your logo here

  14. Jobs • Things to remember • Imports Microsoft.SqlServer.Management.Smo.Agent • Schedules are separate from Jobs so don’t waste your time looking for the AddSchedule function. • JobServer are where jobs reside • TargetServer is where you deploy the job to Your logo here

  15. Demo Time! • Backup and Restore Your logo here

  16. Backup and Restore • Things to remember • Backups and Restores are separate from the the Server object • You must pass the Server object to the Backup or Restore command to tell it where to execute from • Make sure you know where you are at(database-wise) when you execute. Your logo here

  17. Thank You! • Slides can be found on my blog • http://www.programmersedge.com • Or http://events.perptech.com • Email: arie.jones@perptech.com • Questions & Open Discussion • At least till they start wanting to hand out prizes!

More Related