Powershell Day 20

Day 20 Introduction to SMO

>>SQL Server Management Objects (SMO) are objects designed for programmatic management of Microsoft SQL Server

>>The SMO object model extends and supersedes the Distributed Management Objects (SQL-DMO) object model.

>>The DMO library is a COM object model, whereas SMO is implemented as a .NET Framework assembly

 >>SMO is compatible with SQL Server 2000, SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2,

>>The SQL Server provider automatically loads the SQL Server Management Object (SMO) assemblies

The Microsoft.SqlServer.Management.Smo namespace contains classes that represent the core SQL Server Database Engine objects. These include instances, databases, tables, stored procedures, and views.

>>SMO classes include two categories:

Instance classes :databases, tables, triggers, and stored procedures

Utility classes: Transfer, Backup, Restore and scripter

Support all advance feature from sql server :

Table and Index Partitioning

EndPoints

Snapshot Isolation/Row Level Versioning

XML Schema Namespace, XML Indexes and XML datatype

Full-Text Search Enhancements

Page Verify

Snapshot Databases

Service Broker

Index Enhancements

>>SQL Server Management Objects (SMO) Supports programming in Microsoft Visual Basic .NET, Microsoft Visual C# .NET, and Microsoft Visual C++.

>>SQL Server Management Objects (SMO) uses the Microsoft System.Data.SqlClient (we discussed about this in earlier blog)

>>Using smo classes you can do the following:

  • Connect to an instance of the SQL Server Database Engine.
  • View and modify instance settings and configuration options.
  • View and modify database objects.
  • Perform DDL (data definition language) tasks on an instance of the SQL Server Database Engine.
  • Script databases dependencies.
  • Perform database maintenance tasks, such as backup and restore operations.
  • Transfer database schemas and data.

>> The list of assembly will be located at “C:\WINDOWS\assembly\” Folder

>>All the classes in the Microsoft.SqlServer.Management.Smo namespace are in following four files:

Microsoft.SqlServer.Smo.dll

Microsoft.SqlServer.SmoExtended.dll

Microsoft.SqlServer.SqlEnum.dll

Microsoft.SqlServer.SmoEnum.dll

You can get the list of all the smo classes

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo(v=SQL.100).aspx

>>If you want to know which all Assemblies are loaded

[appdomain]::currentdomain.getassemblies()

>>Lets start the simple example to write a simple script for SMO

Load the assembly

[System.Reflection.Assembly]::LoadWithPartialName(’Microsoft.SqlServer.SMO’) | Out-Null        

Create the object you want to work with eg.  Server,database,table, backup….

 For eg. Create an object for sever

$Server1=new-object (“Microsoft.SqlServer.Management.Smo.Server”) ‘server\instance’

 Now you are good to go and explore all the server related information.

 Our great help…. Get-member

 PS C:\>$server1 |gm

About these ads
This entry was posted in Powershell and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s