Tag Archives: delphix

Automating Stored Procedures When Refreshing Virtual SQL Server Databases

So… you’re using a Delphix virtualization engine to provision SQL Server virtual databases (VDBs), but you’re finding that your SQL account passwords are being reset to those used in production after every refresh?  Likewise, all of the settings in the application supported by the database?

Provisioning a new virtual databases is easy.  Go into the Delphix administrative console, find the dSource you want, find the snapshot for the point-in-time you want on that dSource, click the Provision button, specify the database server where the new database should reside, and hey presto!  In 10 minutes or less, a new virtual database is ready for use.

Except not quite…

That new VDB still has all of the data from the source database, and if that source database is production, then that means that it still has production passwords.  And it still has confidential data.  All of that has to be fixed before the database can be used by developers or testers.

So, even though you can now clone from production in minutes instead of hours or days, you still have to do the same post-clone and post-refresh processing tasks you’ve always had to do prior to Delphix.  Now, you just do them immediately rather than later.

If you’re willing to automate those tasks, whether in T-SQL stored procedures or in Windows Powershell scripts, then Delphix can help by embedding them as part of the operations of provision or refresh.

Delphix offers hooks, which are programmatic callouts which fire before and after certain actions by the Delphix virtualization engine, such as as a refresh action…

Hooks provide the ability to execute Powershell code on the target Windows server as the Windows domain account registered with Delphix, either before or after the successful completion of an action.  Here is what the form for adding hooks within a VDB looks like…

Here are the actions for which hooks can be entered, also seen listed in the screenshot above…

  • Provision
    • Configure Clone hook fires after the action completes
  • Refresh

    • Pre-Refresh hook fires before the action begins
    • Post-Refresh hook fires after the action completes successfully
    • Configure Clone hook fires after the Post-Refresh hook completes successfully
  • Rewind

    • Pre-Rewind hook fires before the action begins
    • Post-Rewind hook fires after the action completes successfully
  • Snapshot
    • Pre-Snapshot hook fires before the action begins
    • Post-Snapshot hook fires after the action completes successfully
  • Start
    • Pre-Start hook fires before the action begins
    • Post-Start hook fires after the action completes successfully
  • Stop

    • Pre-Stop hook fires before the action begins
    • Post-Stop hook fires after the action completes successfully

So back to the problem at hand…

We want some actions to take place automatically each time we refresh our virtual database (VDB).  As it turn out, we have two Transact-SQL stored procedures already coded to do the job…

  1. stored procedure MSDB.DBO.CAPTURE_ACCTS_PASSWDS
    • Saves all of the current accounts and account passwords to a set of tables in the MSDB system database
  2. stored procedure MSDB.DBO.REAPPLY_ACCTS_PASSWDS
    • Re-applies all of the current accounts and account passwords from the information previously stored in the MSDB system database

“@DatabaseName”, which is the name of the VDB, is the only parameter for these stored procedures.

I haven’t posted the T-SQL code for these stored procedures, partly because it is always going to be very customized to its environment, but mostly because I am not proficient with T-SQL myself, and I would just be copying someone else’s code for a time.

So looking at our list of Delphix hooks, it should be clear that we need to call the CAPTURE_ACCTS_PASSWDS stored procedure during the Pre-Refresh hook, and call the REAPPLY_ACCTS_PASSWDS stored procedure during the Post-Refresh hook.  Since hooks only call Powershell code and not T-SQL, here is some Powershell code we can use…

#======================================================================
# File:      callsp.ps1
# Type:      powershell script
# Author:    Delphix Professional Services
# Date:      02-Nov 2015
#
# Copyright and license:
#
#       Licensed under the Apache License, Version 2.0 (the "License");
#       you may not use this file except in compliance with the
#       License.
#
#       You may obtain a copy of the License at
#     
#               http://www.apache.org/licenses/LICENSE-2.0
#
#       Unless required by applicable law or agreed to in writing,
#       software distributed under the License is distributed on an
#       "AS IS" basis, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
#       either express or implied.
#
#       See the License for the specific language governing permissions
#       and limitations under the License.
#     
#       Copyright (c) 2015 by Delphix.  All rights reserved.
#
# Description:
#
#    Call the appropriate stored procedure within the DBO schema in
#    the MSDB database on behalf of the VDB.  The stored procedure
#    name the name of the database as a parameter called
#    "@DatabaseName"..
#
# Command-line parameters:
#
#    $fqSpName        fully-qualified stored procedure name
#
# Environment inputs expected:
#
#    VDB_DATABASE_NAME    SQL Server database name for the VDB
#    VDB_INSTANCE_NAME    SQL Server instance name for the VDB
#    VDB_INSTANCE_PORT    SQL Server instance port number for the VDB
#    VDB_INSTANCE_HOST    SQL Server instance hostname for the VDB
#
# Note:
#
# Modifications:
#    TGorman    02nov15    first version
#======================================================================
param([string]$fqSpName = "~~~")
#
#----------------------------------------------------------------------
# Verify the "$dirPath" and "$fqSpName" command-line parameter
# values...
#----------------------------------------------------------------------
 if ( $fqSpName -eq "~~~" ) {
     throw "Command-line parameter 'fqSpName' not found"
 } 
#
#----------------------------------------------------------------------
# Clean up a log file to capture future output from this script...
#----------------------------------------------------------------------
 $dirPath = [Environment]::GetFolderPath("Desktop")
 $timeStamp = Get-Date -UFormat "%Y%m%d_%H%M%S"
 $logFile = $dirPath + "\" + $env:VDB_DATABASE_NAME + "_" + $timeStamp + "_SP.LOG"
 "logFile is " + $logFile
#
#----------------------------------------------------------------------
# Output the variable names and values to the log file...
#----------------------------------------------------------------------
 "INFO: dirPath = '" + $dirPath + "'" | Out-File $logFile
 "INFO: fqSpName = '" + $fqSpName + "'" | Out-File $logFile -Append
 "INFO: env:VDB_INSTANCE_HOST = '" + $env:VDB_INSTANCE_HOST + "'" | Out-File $logFile -Append
 "INFO: env:VDB_INSTANCE_NAME = '" + $env:VDB_INSTANCE_NAME + "'" | Out-File $logFile -Append
 "INFO: env:VDB_INSTANCE_PORT = '" + $env:VDB_INSTANCE_PORT + "'" | Out-File $logFile -Append
 "INFO: env:VDB_DATABASE_NAME = '" + $env:VDB_DATABASE_NAME + "'" | Out-File $logFile -Append
#
#----------------------------------------------------------------------
# Housekeeping: remove any existing log files older than 15 days...
#----------------------------------------------------------------------
 "INFO: removing log files older than 15 days..." | Out-File $logFile -Append
 $ageLimit = (Get-Date).AddDays(-15)
 $logFilePattern = $env:VDB_DATABASE_NAME + "_*_SP.LOG"
 "INFO: logFilePattern = '" + $logFilePattern + "'" | Out-File $logFile -Append
 Get-ChildItem -Path $dirPath -recurse -include $logFilePattern |
     Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $ageLimit } |
     Remove-Item
#
#----------------------------------------------------------------------
# Run the stored procedure...
#----------------------------------------------------------------------
 "INFO: Running stored procedure '" + $fqSpName + "' within database '" +
     $env:VDB_DATABASE_NAME + "'..." | Out-File $logFile -Append
 try {
     "INFO: open SQL Server connection..." | Out-File $logFile -Append
     $sqlServer = $env:VDB_INSTANCE_HOST + "\" + $env:VDB_INSTANCE_NAME + ", " + $env:VDB_INSTANCE_PORT
     "INFO: sqlServer = '" + $sqlServer + "'" | Out-File $logFile -Append
#     [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
     $conn = New-Object System.Data.SqlClient.SqlConnection
     $conn.ConnectionString = "Server=$sqlServer; Database=MSDB; Integrated Security=SSPI;"
     "INFO: conn.ConnectionString = '" + $conn.ConnectionString + "'" | Out-File $logFile -Append
     $conn.Open()
     $cmd1 = New-Object System.Data.SqlClient.SqlCommand($fqSpName, $conn)
     $cmd1.CommandType = [System.Data.CommandType]::StoredProcedure
     $cmd1.Parameters.Add('@DatabaseName', $env:VDB_DATABASE_NAME) | Out-null
     "INFO: calling " + $fqSpName + ", @DatabaseName = " + $env:VDB_DATABASE_NAME | Out-File $logFile -Append
     $exec1 = $cmd1.ExecuteReader()
     $exec1.Close()
     $conn.Close()
 } catch { Throw $Error[0].Exception.Message | Out-File $logFile -Append }
 #
 "INFO: completed stored procedure '" + $fqSpName + "' within database '" +
     $env:VDB_DATABASE_NAME + "' successfully" | Out-File $logFile -Append
#
#----------------------------------------------------------------------
# Exit with success status...
#----------------------------------------------------------------------
exit 0

The formatting of the code is a little constrained by the narrow viewing area, so if you want the actual code correctly formatted, it can be downloaded from HERE.

Of course, this code does more than just calling the stored procedure with the database name as the sole parameter;  it is also qualifying command-line parameters, creating and updating a log file, and handling possible error conditions.

Once this script is saved on the target Windows host server where the VDB resides, then we can call it from the Pre-Refresh and Post-Refresh hooks.

Here we see the Pre-Refresh hook being called with a call to the CALLSP.PS1 powershell script located within the C:\DELPHIX\SCRIPTS directory on the target Windows server, to call the CAPTURE_ACCTS_PASSWDS stored procedure…

Likewise, we see here how the Post-Refresh hook is constructed to call the REAPPLY_ACCTS_PASSWDS stored procedure…

And finally, with both hooks created, we can see them in the Configuration panel together like this…

So now, whenever the refresh operation is performed on the VDB named VV11, these hooks will execute the Powershell script, which will in turn execute the specified stored procedures, and so when the refresh operation is complete, the account and password settings which were present prior to the refresh operation will still be present.

I hope this is useful?


This post can also be found on the Delphix website HERE


If you have any questions about this topic, or any questions about data virtualization or data masking using Delphix, please feel free to contact me at “tim.gorman@delphix.com”.

Presenting “Accelerating DevOps Using Data Virtualization” at #C16LV

Borrowing from Wikipedia, the term DevOps is defined as…

DevOps (a clipped compound of "development" and "operations") is a culture, movement or practice that emphasizes the collaboration and communication of both software developers and other information-technology (IT) professionals while automating the process of software delivery and infrastructure changes. It aims at establishing a culture and environment where building, testing, and releasing software, can happen rapidly, frequently, and more reliably.

Now, I hate buzzwords as much as the next BTOM (a.k.a. bitter twisted old man)…eastwood

…but the idea behind DevOps, of building, testing, and releasing software more rapidly and reliably is simply amazing and utterly necessary.

As system complexity has increased, as application functionality has ballooned, and as the cost of production downtime has skyrocketed, writing and testing code leaves one a long way from the promised land of published and deployed production code.

As explained by DevOps visionaries like Gene Kim, the biggest barrier to that promised land is data, in the form of databases cloned from production for development and testing, in the form of application stacks cloned from production systems for development and testing.

The amount of time wasted waiting for data on which to develop or test dwarfs the amount of time spent developing or testing.  Consequently, IT has learned to be satisfied with only occasional refreshes of dev/test systems from production, resulting in humorously inadequate dev/test systems, and that has been the norm.

There is a new norm in town.

Data virtualization, like server virtualization, breaks through the constraint.  Over the past 10 years, IT has learned to wallow in the freedom of server virtualization, using tools like VMware and OpenStack to provision virtual machines for any purpose.

Unfortunately, data and storage did not benefit from virtuaMatrix1lization as well.  This has resulted in a white-hot nova in the storage industry, and while that is good news for the storage industry, it still means that IT has cloned from production to non-production the same way it has done the past 40 years, in other words slowly, expensively, and painfully.

Matrix2And we have continued to do it the old way, slowly, expensively, and painfully, because we didn’t know any better.

The IT industry couldn’t see any better way to do clone from production to non-production.  Slow and painful was the norm.

But once one realizes the nature of making copies, and how modern file-system technology can share at the block-level, compress, anMatrix3d de-duplicate, suddenly making copies of databases and file-system directories becomes easier and inexpensive.

Here is a thought-provoking question:  why doesn’t every individual developer and tester have their own private full systems stack?  Why can’t they have several of them, one or more for each task on which they’re working?

I can literally hear all of the other BTOM’s scoffing at that question:  “Nobody has that much infrastructure, you idiot!

And that is the point.  You certainly do.

You just don’t have the right infrastructure.

This was presented at the Collaborate 2016 conference in Las Vegas on Monday 11-April 2016.

You can download the slidedeck here.

Data Virtualization and Greener Data Centers

On the Saturday before the Oracle OpenWorld 2014 conference started, I had the added bonus of finding out that the Data Center Journal had published my article on how data virtualization leads to greener data centers.  Hooray!

Unfortunately, I recently learned that Data Center Journal has gone defunct, so my posted article no longer exists.  As a result, here we go…

I recall that the January 2000 issue of National Geographic magazine had a “Letters From The Editor” column article that speculated, in jest, that the rate at which humans were saving back-issues of National Geographic magazine, would by the year 2100 result in the total accumulation of yellow magazines outweighing planet Earth.

Note:  The public archives of Nat’l Geographic magazine appear to only go back to 2005, so I can’t verify the exact issue in which this comment appeared.

Anyway, that statement resonated with me, because although I change residences every few years, it has only been recently that I hadn’t packed and carried my decades of accumulated National Geographic magazines with me.  Now that I’m free of them, I have no idea why I schlepped them with me for so long.  Worse, it cost real money to do so;  movers charge by weight. One mover commented that he was certain that two-thirds of the weight of all my possessions were books and National Geographic magazines, as he handed me an $8,000 bill for the move.

I now collect books on Kindle.  And I dropped off my boxes of yellow Nat’l Geographic magazines at the Goodwill store, in the middle of a dark and shameful night, almost a decade ago.  I don’t know if it was a particularly “green” decision, but I know that my recent moves have been the easiest since I was an undergraduate.

Likewise in data centers.  If we keep doing business in data centers as we have for the past 30 years, quite soon the planet would tilt off it’s axis due to the sheer weight of data storage hardware.

The advent of virtual machines has had a profound impact on provisioning environments.  Instead of unpacking, racking, wiring, powering, and cooling physical servers, data centers can now create virtual machines by the hundreds by pointing and clicking.  All of these new virtual machines share the previously under-utilized CPU and RAM resources of physical servers, making the ROI on CPU and RAM resources sky high.

So, virtual machine technology has allowed data centers to provision several million virtual servers without having to power and cool several millions of physical servers.  They use the existing physical servers far more efficiently.  That is “green”.

Not so with disk storage.

Each virtual machine still requires a full image of storage.  So, as several million virtual servers have been spun up, each has required a full complement of disk storage, thus driving the already overheated computer storage industry into supernova.

I’ve said it before and say it again:  if you have money to invest, do so in either energy or data storage.  We’re never going to use less of either.

So how does Delphix and data virtualization fit in?

Delphix virtualizes data, just as VMware and their competitors virtualize servers.  Delphix data virtualization makes more efficient use of existing storage, and slows the rate of growth of storage in data centers.

That is “green”.

For many, the time has arrived where server virtualization has completely taken over, even for those situations where sharing CPU and RAM resources are not desired.  For high-impact production environments, it is very common to have virtual machines one-for-one with physical machines.  Having production application encapsulated in a virtual machine makes it easier and simpler to migrate to other physical servers, whether to address resource shortfalls or to deal with physical server failure.

In these situations, data virtualization does not yield benefit, green or otherwise.

But in the scenario where a couple, or dozens, or even hundreds or thousands of virtual machines are provisioned to a cluster of physical servers, we have an environmentally unsustainable model, in every sense of the phrase.

An analogy for server virtualization without data virtualization in this latter scenario is an advance in technology to enable us to build automobiles entirely from cheap renewable resources, such as cellulose.  Hey terrific, instead of building cars from expensively mined resources such as metals and exhaustible resources such as plastic, let’s imagine a leap in technology where we could employ cellulose waste from food production, mainly biomass left over from farming.

Perhaps we would have found a way to get rid of all those old back issues of National Geographic?

We could then produce these cars more cheaply and with less environmental impact, using what is essentially mulch, for a fraction of the cost of currently manufactured automobiles.

It would be the golden age of personal transportation.  Everyone on the planet could afford one.

But what if these new automobiles still used internal combustion engines, consuming fossil fuels, at the same level of efficiency as today, about 20-40 miles per gallon?  Even if they were more efficient, upwards of 100 miles per gallon;  would that yield a net benefit to the environment?

Of course not.  The proliferation of these inexpensive, environmentally friendly automobiles would be an utter disaster environmentally, as the consumption of fossil fuels skyrocketed.

The oil companies would be quite happy, wouldn’t they?

That is server virtualization without data virtualization.

Except that it is the storage companies in the place of the oil companies in our analogy.

Server virtualization is a huge advance, but data virtualization is needed to fully deliver on the promise of the solution.

Lovin’ la vida Oracle

As we prepare for the week of Oracle OpenWorld 2014, I look back on the 25 years I have spent within the orbit of Oracle Corporation.

I joined Oracle Consulting Services (OCS) as an employee on 15-January 1990 and worked my way to Technical Manager when I resigned to start my own consultancy on 31-July 1998.  I worked as an independent Oracle consultant from then (with a side trip into company-building with friends) until 30-April this year.  On 01-May 2014, I joined startup Delphix.

Throughout this quarter-century of La Vida Oracle, I’ve made a great living, but it has also been a great way of life.  I started presenting at the Rocky Mountain Oracle Users Group in 1993, and joined the board of directors in 1995.  I’ve since worked with many other Oracle users groups as a volunteer and I’ve found the experiences to be incredibly educational, in so many ways.  I’ve also met a lot of amazing people through volunteering at Oracle users groups.  I met the junta of the Oak Table Network, and joined that group in 2002.  I was elected as an Oracle ACE in 2007, before I even knew the program existed, then I was made an ACE Director in 2012, which is an elevation I appreciate but still never sought.

But over it all, all throughout, is Oracle.  The Big Red O.  Some people have had bad experiences at Oracle Corporation, some have had REALLY bad experiences, just as people have good and bad experiences at any huge corporation.  In the spirit of a comment made famous by Winston Churchill, “Democracy is the absolute worst form of government.  Except for all the others.”  Oracle is populated by, and led by, some very human … beings.  I love them all, some more than others.

So for 25 years now, out of the 37 years Oracle has been in existence, I have had a really great life.  La vida Oracle.  I am so GLAD I met ya!  And I love this life!

And so it continues today.  For the first time in a quarter century, I’m out of the direct orbit of Oracle, now that I’m working at Delphix.  I’m still heavily involved with Oracle as an Oracle ACE Director and adviser to the boards of three local Oracle users groups (RMOUG, NoCOUG, and NEOOUG) and a board member at ODTUG.

Delphix builds data virtualization software for Oracle, PostgreSQL, SQL Server, and Sybase ASE, as well as file-system directories on Unix/Linux and Windows.  Virtualizing Oracle databases is a big part of Delphix’s business, but it is not the only part, and the non-Oracle parts are growing rapidly.  It’s refreshing to work with other database technologies.  But I still love working with Oracle Database, and I’m continually impressed by Oracle’s technology prowess, with the In-Memory option of Database12c a brilliant example.

Some say that Delphix competes with Oracle.  Be serious – please name a technology company that doesn’t compete with Oracle in one way or another, as the breadth of Oracle products and services is so expansive.

As an independent contractor at EvDBT for 16 years, I myself competed with Oracle Consulting in my own very small way.  But, at the same time I cooperated with Oracle by optimizing the implementation of Oracle technology.  I sure as heck understand who hold the tent up.

The same is true with Delphix.  As a company, Delphix products can be said to compete with Oracle Enterprise Manager 12c Cloud Control, in the niche area known as Database-As-A-Service (DBaaS) in the specific SnapClone functionality.  The Delphix software appliance is very similar to this SnapClone piece, but this part of the Oracle product is just a small part of the scope the vast EM12c Cloud Control product suite.

In the same way, I as an independent consultant could have been said to have competed with the EM12c diagnostics pack and performance tuning pack, because the techniques I used and taught tended to make people independent of those tools.

That’s not to say I steered people away from EM12c; it’s just that I myself didn’t use it for performance tuning, though gradually I learned to appreciate many of its features, not least through paying attention to my wife Kellyn Pot’vin.

In fact, the Oracle Enterprise Manager 12c Cloud Control, using the Cloud API, can fully administer virtual databases created by Delphix.  After all, Delphix is just an alternate mechanism to implement data virtualization.  Instead of using the mechanism of Oracle DBaaS SnapClone, customers can also use Delphix.  So Delphix can become a part of EM12c.

So there is no competition between Delphix and Oracle.  Delphix is an alternative to the SnapClone mechanism underlying DBaaS, but Delphix virtual databases can still be orchestrated through the EM12c console.  It need not be an either-or choice.

Of course, I still have to write that extension through the EM12c cloud API, and I’m getting right on that.  Unless someone else gets to it first.

Keep your eye on the Oracle EM12c Extension Exchange webpage for more progress on integrating Delphix within EM12c…

#CloneAttack at Oracle OpenWorld 2014

Delphix and Dbvisit will be at the OTN Lounge in the lobby of Moscone South from 3:30 – 5:00pm on Monday 29-Sept.  Come join us to hear about #CloneAttack and #RepAttack, two great hands-on learning opportunities.

What:

#CloneAttack is your chance to install a complete Delphix lab environment on your Windows or Mac laptop for you to play with and experiment at any time.  Experts Kyle Hailey, Steve Karam, Adam Bowen, Ben Prusinski, and I will be sharing USB “thumb” drives with the virtual machine OVA files for the lab environment, and we will be working one-on-one with you to help you get everything up and running, then to show you basic use-cases for cloning with Delphix.

Bring your laptop, bring your VMware, and get some data virtualization into your virtual life!

At the same time, #CloneAttack will be joined by #RepAttack by Dbvisit, where Arjen Visser, Jan Karremans, and the team will be helping you replicate Oracle to Oracle for zero downtime upgrades.

This just in!  #MonitorAttack from Confio SolarWinds will also be joining the party at the CCM on Tuesday to show you how to quickly and easily install Confio Ignite and enjoy the great features there.

Where:

Children’s Creativity Museum, 221 4th St, San Francisco

When:

Tuesday, Sept 30 from 10am – 5pm PDT

Before you arrive:

Hardware requirements (either Mac or Windows):

  • at least 8 GB RAM
  • at least 50 GB free disk space, but preferably 100 GB free
  • at least 2 Ghz CPU, preferably dual-core or better