The Data Lobby

For those of you using Delphix with Oracle you’ve probably already had some exposure to Hooks; when you create a VDB via the GUI you always hit a hook screen during the provision process. I don’t intend to explain in detail what hooks are in this post but I will say they can be extremely useful to implement specific requirements where Delphix does not support them natively. A good example for Oracle is database account passwords. Lets take a quick look at what I mean.

Your non-prod VDB is most likely a copy from production and the passwords for all accounts will be identical to those in production. These passwords, especially for privileged accounts, will be held in a password repository where you can retrieve them when required (some production incident or planned change) and, in the case of password repositories that can automatically control accounts, will be changed to some random password at the end of a predefined time frame, thus ensuring no one can gain privileged access to the database unless authorised.

So the scenario is we now have a VDB, lets assume it is a Jet Stream container VDB to be consumed by a developer, with account passwords unknown. The obvious way around this is for the Dev to contact the DBA and ask them to change the password for all the accounts the Dev needs to do their job. Well that sounds like manual labour to me! Cue Delphix Hooks…

Just like in the physical world, if we were automating the refresh of dev databases, we can write a short shell script that can update these accounts changing the password to something known. Hooks have several trigger points (Pre-Refresh, Post-Refresh, Pre-Start, Post-Start to name a few) that gives us the ability for this use case to extract the existing passwords (hashed obviously) from the VDB prior to refresh and then reset the accounts to this password after the refresh.

Well this all sounds very useful and you can probably think of many great uses for hooks but if you are working with SQL Server you’re out of luck… well actually, not quite.

Up until Delphix version 4.3 (I think) Hooks were not available and in the current version 5.1 they are “not supported by default” so your only option was to use Pre- and Post-Scripts but their implementation made them very limiting in what you could do. For instance you couldn’t capture anything from the VDB before a refresh because the Pre- script fires to late (after the VDB has been shutdown).

Well, don’t despair. I said in version 5.1 hooks are “not supported by default” which actually means that if you raise a ticket with Delphix Support they will happily switch them on and then you’ll get all the hook goodness that our Oracle friends enjoy. I’ve been assured by people in the know that Delphix version 5.2 will have SQL hooks enabled by default.

I’m going to give a rough example of a SQL hook here written in Powershell, which will be familiar to the attendees of the training courses I teach (and in fact I’m putting it here so they can easily copy and paste the code during the classes!). This is a slightly different use case to above and could be quickly developed further.

The scenario is that we have a dev instance with an existing login used by a developer but the prod version of the database does not contain the database user for the login to map to therefore the dev can’t get access to the database until the database user is recreated and mapped to the login.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')

$sqlserver = "."
$dbname = $env:VDB_DATABASE_NAME
$name = "appuser"

$Server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlserver

if (-Not $Server.Logins.Contains($name))
{
$Login = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login -ArgumentList $Server, $name
$Login.LoginType = 'SqlLogin'
$Login.Create('delphix')
}

$database = $server.Databases["$dbname"]

$user = new-object ('Microsoft.SqlServer.Management.Smo.User') $database, $name
$user.Login = $name
$user.Create()

As you can see it’s pretty straight forward but I will mention another great thing about hooks here. Look at the $dbname variable assignment on line 4. Delphix provides a number of environment variables, which gives us the opportunity to create generic non-database specific scripts. You could add it to a Hook Template and use it across multiple VDBs.

dSource Environment Variables

VDB Environment Variables

 

So there you are. If you work with SQL Server on Delphix 4.3+ then you too can make use of this great functionality and it will only take a quick support ticket to enable.