Microsoft.SqlServer.Management.Smo in Automation Account

Category: azure automation

Question

Joachim Keyser on Wed, 24 Jul 2019 07:18:13


Hi, 

I'm trying to utilize the scripter class (namespace Microsoft.SqlServer.Management.Smo) in a powershell script as a runbook in our automation account. The idea is to script out certain objects of a development database. 

I tried several things along the lines: 

  1. $server = New-Object Microsoft.SqlServer.Management.Smo.Server(<InstanceName>);
  2. $scripter = new-object Microsoft.SqlServer.Management.Smo.Scripter($server);
  3. $database = $server.Databases[<DatabaseName>];

Where instance name is the location of the Azure Sql Database server

I get the error message "ERROR: Unable to find type [Microsoft.SqlServer.Management.Server"] (and for anything in the namespace, really.)

I have tried to load assemblies as 

Import-Module -Assembly ([System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo"))

but this doesn't seem to make any difference.

First question: is it not possible to use the management objects in the automation account environment in Azure?
I would rather not get into azure functions for this, but I might have to?

I understand that the SMO is dependent on SQL Server, but it would be great to be able to automate this scripting in Azure..





Replies

SwathiDhanwada-MSFT on Wed, 24 Jul 2019 11:01:19


AFAIK, its not possible to use management objects in automation account. One way to use management objects is through Hybrid Runbook worker.When working with a Hybrid Runbook Workeryou can control the underlying OS so loading assemblies and dealing with the local file system and working with it is easy. When working with runbooks that run in Azure, not everything may be available in the sandbox that we run automation runbooks on so things may not always work.


Stanislav Zhelyazkov on Wed, 24 Jul 2019 13:01:50


Hi,

Actually I think this is possible even on Azure workers. You will have to import Sqlserver module from PowerShell Gallery in Azure Automation.

Than you can have something as this:

$ErrorActionPreference="Stop"
import-module SqlServer
$assemblylist =   
"Microsoft.SqlServer.Management.Common",  
"Microsoft.SqlServer.Smo",  
"Microsoft.SqlServer.Dmf ",  
"Microsoft.SqlServer.Instapi ",  
"Microsoft.SqlServer.SqlWmiManagement ",  
"Microsoft.SqlServer.ConnectionInfo ",  
"Microsoft.SqlServer.SmoExtended ",  
"Microsoft.SqlServer.SqlTDiagM ",  
"Microsoft.SqlServer.SString ",  
"Microsoft.SqlServer.Management.RegisteredServers ",  
"Microsoft.SqlServer.Management.Sdk.Sfc ",  
"Microsoft.SqlServer.SqlEnum ",  
"Microsoft.SqlServer.RegSvrEnum ",  
"Microsoft.SqlServer.WmiEnum ",  
"Microsoft.SqlServer.ServiceBrokerEnum ",  
"Microsoft.SqlServer.ConnectionInfoExtended ",  
"Microsoft.SqlServer.Management.Collector ",  
"Microsoft.SqlServer.Management.CollectorEnum",  
"Microsoft.SqlServer.Management.Dac",  
"Microsoft.SqlServer.Management.DacEnum",  
"Microsoft.SqlServer.Management.Utility"  
  
foreach ($asm in $assemblylist)  
{  
    $asm = [Reflection.Assembly]::LoadWithPartialName($asm)  
}  
$server = New-Object Microsoft.SqlServer.Management.Smo.Server("server");

Mark this reply as answer if it has helped you resolve your issue.

Joachim Keyser on Mon, 29 Jul 2019 09:24:24


This works well, thanks

I had a bit more issues with authentication/ getting server context in Azure before grabbing the database objects, but after this: 

$server = new-object Microsoft.SqlServer.Management.Smo.Server($sqlInstanceName)
$conContext = $server.ConnectionContext
$conContext.LoginSecure = $false
$conContext.Login = $login
$conContext.Password = $pwd
$database = $server.Databases.Item($dbName);

I am able to create a scripter object and use it like I wish, e.g:

$scripter = New-Object Microsoft.SqlServer.Management.Smo.Scripter($server);

$scripter.Options.ScriptDrops = $false  
$scripter.Options.WithDependencies = $true  
$scripter.Options.IncludeIfNotExists = $true 

.

.

.

$table = $database.Tables[$tableName];

$SavedCreateScript = $scripter.Script([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$table)