DLM Automation–Making a Database Connection

One of the things I’ve been wanting to do is dig more into the command line automation cmdlets from the Redgate DLM Automation suite. While there are plugins for many build and release systems, I should be able to customize, or fall back, to the command line if necessary.

Plus, this allows me to get a better feel for what’s happening at each step.

This post looks at the basics of making a connection to a database. Note that this is documented on the DLM Automation 2 Cmdlet Reference page.

New-Database Connection

When I started working with the connections, I used the New-DatabaseConnection cmdlet. However, when I run this now, I get this:

2016-10-14 15_32_04-DLM Automation–Making a Database Connection - Open Live Writer

That’s fine, as my call to the new cmdlet works.

2016-10-14 15_32_34-cmd - powershell

Do I know what’s happening here? I should investigate further.

First, if I’ve run this, what does it do? Not much, apparently. If I run an Extended Events session looking for a connection, I don’t get one. Let’s try a new command, this time using a name and password.

2016-10-14 15_37_53-cmd - powershell

That’s an invalid password for my instance (as it should be on every instance in the world). However, I don’t get an error. No connection was made. In this case, the cmdlet is storing off the connection credentials that will be used. If I don’t provide the user/password, then a trusted (Windows auth), connection is assumed.

Can I now test this? Sure, I’ll use the Test-DLMDatabaseConnection to make a connection.

2016-10-14 15_39_37-cmd - powershell

This fails. That’s good to know. Now I can use this to actually determine if I target database is alive before I continue processing. My preferred method here would be to wrap this up in some error handling. For example, I can use a try..catch structure in PoSh. I’ll switch to the ISE to make this cleaner. Here’s the code

try
{
Test-DlmDatabaseConnection -$test -ErrorAction Stop
} 
catch
{
Write-Host "error"
}

In the ISE:

2016-10-14 16_22_45-Windows PowerShell ISE

I can also get the error details from the current object ($_). Note, I’m not a PoSh expert, so if there are better ways, let me know.

2016-10-14 16_36_48-Windows PowerShell ISE

I can also pass in some common parameters, like –ErrorAction and –ErrorVariable. Then I can use these to decide whether or not to continue. In my case, I’ll just write a message. Here’s my code:

$testconn = New-DlmDatabaseConnection -ServerInstance ".\SQL2014" -Database "st_integration" -U "sa" -Password "test"
try
{
Test-DlmDatabaseConnection $testconn -ErrorAction SilentlyContinue -ErrorVariable ErrorHolder;
} 
catch
{
Write-Host $_.Exception.Message
};

if ($ErrorHolder)
{
Write-Host($ErrorHolder)
}
else
{
Write-Host("Everything works");
}

That will give me this:

2016-10-14 16_41_08-Windows PowerShell ISE

If I remove the user and password parameters, I get this:

2016-10-14 16_41_29-Windows PowerShell ISE

You didn’t think I’d publish the real sa password, did you? Of course not. If I change the code to the right password, things connect.

Checking the Object

What if I just want to check the object? Well, there are properties associated with the object. For example, if I use a simple variable to assign to the object, I can see some properties. If I just type the variable name, $testconn, I get these properties:

  • ServerInstance
  • Database
  • SQLServerCredential
  • ConnectionString
  • Description

You can see these returned in a test session.

2016-11-14 16_58_29-cmd - powershell

Other Options

I have other parameters I can use with my connection, or rather, in place of my connection object. I can use a connection string, with all of the parameters that are valid there. For example:

2016-10-14 16_53_17-Windows PowerShell ISE

If I enter the correct password, I get this:

2016-10-14 16_54_09-Windows PowerShell ISE

My connection string can contain all of the items I might normally use: ApplicationIntent, Encrypt, NetworkLibrary, etc. A full list of parameters is at: https://www.connectionstrings.com/all-sql-server-connection-string-keywords/

The New-DlmDatabaseConnection object outputs a RedGate.DLMAutomation.Compare.SchemaSources.DatabaseConnection object, which is the input to quite a few other of the cmdlets. While not complex, it is worth understanding how to use this cmdlet, which is the basis for determining from which source or two which target you will be moving database changes.

In another post, I’ll start to bind this cmdlet with others and actually perform some database deployment automation work.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , , . Bookmark the permalink.

One Response to DLM Automation–Making a Database Connection

  1. Pingback: Validating a Set of Database Scripts using DLM Automation | Voice of the DBA

Comments are closed.