Powershell Day 21

Day 21 SMO programming Part 2 

Continue with my earlier blog where I discussed about the information about SMO. Today we will talk about programming sql server using SMO. 

SMO is a very big subject to discuss and each method /property can be discussed detail and I can have a complete Book on it, so I will try to focus major stuff here and rest you can explore by your own.

For All Classes:

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.aspx 

SERVER Class: Microsoft.SqlServer.Management.Smo.Server:

Server class has selever properties and methods used to work on sql server properties. Its not easy to explain all here, but will try to make a special blog for some special methods like performance monitor.

 Server class is used to manage server configuration, we can get and set the parameter/values to the sql server using server class.

 Can go to database and get the database properties/methods.

Get OS/Sql version

Get service related values

Work on  sql processes.

Lock

Permission

Error logs

 

All Server related task can be performed.

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

Complete list and description of server class – property and methods are here

http://msdn.microsoft.com/enus/library/microsoft.sqlserver.management.smo.server.aspx

 When you create an object for “Server”.

 PS C:\> $smo1 |gm | ? {$_.Name -like “*version*” -and $_.MemberType -eq “Property”} | select name

Name

—-

BuildClrVersion              :CLR version

BuildClrVersionString      : CLR version String -FULL

OSVersion                     :OS Version…

ResourceVersion            : Resource Database Version

ResourceVersionString   : Resource Database Version String -FULL

Version                         :  Sql Version -Full

VersionMajor                : Major –Version 9/10/10.5

VersionMinor                : Minor – Service PAck

VersionString                 : Version FULL

BuildNumber                   : # eg 1600

 #just like sys.databases

PS C:\> $server1.databases |select name,status,recoverymodel,createdate

 #want to know the specific database related property/method, create another variable with $_name with that database

PS C:\> $DB_srv1=$server1.databases | ? {$_.name –eq “DBNAME”}

 Enumerates

PS C:\> $smo1 |gm | ? { $_.MemberType -eq “method” -and $_.name -like “enum*”} |select name

 Name

—-

EnumActiveCurrentSessionTraceFlags    :

EnumActiveGlobalTraceFlags                      : Traceflags

EnumAvailableMedia                                     : media

EnumCollations                                                 : Collation

EnumDatabaseMirrorWitnessRoles         : Mirror 

EnumDetachedDatabaseFiles                     : Detached

EnumDetachedLogFiles                                 : Detached log

EnumDirectories                             

EnumErrorLogs                                                 : Errorlog ()

EnumLocks                                                         : Current Locks

EnumMembers                               

EnumObjectPermissions                             

EnumPerformanceCounters                       : Performance counter

EnumProcesses                                                : Current Sys processes

…

…

 Database Class: Microsoft.SqlServer.Management.Smo.database:

 http://msdn.microsoft.com/enus/library/microsoft.sqlserver.management.smo.database.aspx

As “Server” class handles server related task, “Database” Class is on database related and can manuputed the database included

>>Create database

>>Execute Any  query on database

>> Alter database

>>remove database

Database object is depends on server object, so before database object you should have server object created,

$Database1=new-object (‘Microsoft.SqlServer.Management.Smo.Database’) ($Server1,”databaseNm”)

 Here we will work on server $server1.

 #To create a database. –this will create a database with default configuration –model database.

$databases.Create()

 

To configure the database with file management you have to create several objects related to it. Like

                $FileGroup1 = New-Object (’Microsoft.SqlServer.Management.Smo.FileGroup) ($Database1,”FilegroupNm”)

                DataFile’= New-Object (’Microsoft.SqlServer.Management.Smo.DataFile’)($Databases1,”DataFilenm”)

                LogFile’= New-Object (’Microsoft.SqlServer.Management.Smo.LogFile’)($Databases1,”Logfilenm”

 And provide the appropriate parameter values to it. And at the and call function

$databases.Create()

This will create database base with above configuration values.

 Also we can set the recovery model As:

PS C:\> $database1.set_recoverymodel(1)

1 =FULL, 2 = Bulked log and 3 =Simple

 Finally, if you want to run any query on the database.

                PS C:\> $q1= $database.executequerywithresult(“Sp_help”)

 PS C:\> $q1.tables |ft 

#this will give query “Sp_help” output

As usual to get complete list of properties and methods use get-member and explore the database class

 There is so much to do that will try to try cover in next blog.