Automatic DBA

The Gator dropped by today and suggested I have a little discuss on the Database Development Life Cycle. “Don’t you mean the Software Development Life Cycle?” Nope… databases are a lot different than software, although software does run and interact with the database. The database is a living, breathing thing that must be fed and nurtured to get the best performance out of it and the software that uses it. An executable or web service can be rebuilt from scratch every time, but once a database is being used in a production capacity you are left with modification in situ.

At Humana, I describe the Database Development Lifecycle thus:

• Data Refresh
  • Metadata Exchange
  • Security Sync
• Code Sync
  • Verification
• Code Turn
  • Verification
• Database Use
  • QA – UAT
  • Test – System/Integration Testing
  • Dev – Development

As I breakdown these items please keep in mind that not all elements are going to exist in every database environment.

Database Refresh – Data in lower environments (not production) grows stale over time. This stagnation allows the data, though use and testing, to get further from the real world of production. To combat this data stagnation the lower environments should occasionally be refreshed from the production source. More than likely a straight data replacement will not work: in most modern systems a certain amount of environmentally sensitive data exists and must be retained in the target database. To dig deeper into managing metadata, read about SQLDataCompare. Additionally, you will probably need to correct/sync security so that the target database has the correct security on it for the environment you are refreshing rather than the environment that the data came from (production).

Code Sync – When you refresh the data in the target environment, more than likely you will copy over the existing database, and then start modifying it to get it where it is supposed to be. Modern DBMS’s usually have a mix of data and code objects intermixed in the same storage system (It is possible to create boundaries between the two, but it is way beyond this conversation). So once you’ve done the ‘restore’ of production data on the target database, the code objects will need to be “sync’d” so that you can get the target environment back to where it was before the data refresh.

Example: Production is on release 11/08.12, QA is on 11.09.12, and Test/Dev is on 11.10. So if you refresh Test, its code base would be that of production, or 11.08, but it was on 11.10 so QA would need to be resync the database code (e.g., rerun the turn scripts for 11.09 and 11.10).

Once you think you’re sync’d then you need to perform verification to ensure that you know where your starting point is.

Code Promotion– Refreshes are usually at the start a development cycle, which means that you will probably run (test, prove, etc.) the next releases code base (scripts). If you are dealing with QA, the Turn Center will actually do the turn via a TCW. Code turns should ALWAYS be verified, regardless of who does them.

Database Use – This is why the database exists… utilization by an end-use process. QA and Test have the same end-use processes; just the audience is different. In Dev, the end-use process is the actual development and database script creation.

That is it in a nut shell. So….What do you consider the DDLC?

Previously I discussed how to automate a SQL Build. One of the things I mentioned was the inability to get a clean list of source code file changes from FinalBuilder (FB) without all the shelf set gibberish. I mean, who really wants to parse a huge set of text trying to find the nuggets of information you need if you don’t have to? Looks like I need to break out Visual Studio!

FinalBuilder comes with a set of API’s to customize ANYTHING so that it can be used with FinalBuilder. This is a two-step process (which I will over simplify):

1. Write a .NET compliant DLL that:
   1. References the necessary VSoft API.
   2. Inherits from one of the core abstract classes.
2. Create an Action Package in FinalBuilder Action Studio so that:
   1. FinalBuilder can call the custom DLL.
   2. The custom DLL can get parameters passed to it.

The first thing you need to know is that VSoft’s documentation on their API is less than stellar…way less…OK, it’s non-existent. It took me a couple of weeks, and several emails to the VSoft blokes Down-Under, to figure out how to get an action compiled, set it up in action studio, and deployed in a FB project. The API allows three basic actions, which come with matching abstract classes:

• ExecuteProgramAction – Used to launch external processes from FB project.
• StandardAction – Used to interact with a custom .NET DLL.
• IteratorAction – Used to build a list to iterate through (similar to a For-Each loop) from .NET.

As far as my DLL is concerned, I needed an iterator that interfaced directly. This is how it’s done:

1. Create a DLL project in Visual Studio.
2. Add necessary references to the VSoft API.
3. Create a new class by inheriting from the abstract IteratorAction class.
4. Add necessary TFS API references.
5. Inherit the abstract class and implement the required methods. In this case the constructor, InitializeIterator(), Validate(), and GetCurrentValue.
   1. I used Validate() to insert code necessary to validate the properties being sent to my DLL.
   2. InitializeIterator() housed the main logic used to gather the necessary source code files (TFS Items). It looked something like this (condensed for brevity)…

// Define TFS variables.

private TeamFoundationServer _tfs;

private VersionControlServer _vcs;

// Create a list to contain the source code file references.

List<Item> _itemSet = new List<Item>();

// Create objects for TFS and the client services.

_tfs = new TeamFoundationServer("tfs.humana.com");

_vcs = (VersionControlServer)_tfs.GetService(typeof(VersionControlServer));

// Pull required properties from FB.

_projectPath = ExpandProperty("ProjectPath", true);

bool bValid = DateTime.TryParse(ExpandProperty("SetFromDate", true), out _lastBuildDT);

// Given a path within TFS, find all source code files within that path.

localSet = _vcs.GetItems(_projectPath, RecursionType.Full);

// Loop through all the source code files

foreach (Item i in localSet.Items)

{

// finding all the source code files that have been altered after a given date

if ((i.ItemType != ItemType.Folder) && (i.CheckinDate.CompareTo(_lastBuildDT) > 0))

{

// and add them to the list container.

_itemSet.Add(i);

}

}

1. Once initialized, FB will used the GetCurrentValue method to pull item based on position. The latest version of FB uses a 1 based (vs. 0 based) starting position of the iterator, so code the method appropriately:

   // Return the string (TFS path) of the TFS Server Item.

   return _itemSet[iteration-1].ServerItem.ToString();

    

2. The only other thing we need is to get the parameters from FB when called, and that is accomplished through the protected property ExpandProperty from the base class BaseAction. This is probably the biggest gotcha and is not explain very well. In a nutshell, the properties you define in Action Studio are called via ExpandProperty to get a value from the FB interface to your DLL. ExpandProperty can be called anytime in the life of your object, even in the constructor. There are also ways to: SetProperty, SendMessage, GetFileSet, GetString,…..All of which are available from the base class BaseAction.
3. Once I compiled the DLL, I had to move the following files to \\[machine/drive]\Program Files\FinalBuilder 7\ActionDefs\Assemblies\2.0:
   1. FBIntegration.DLL (My new DLL.)
   2. Supporting DLL’s (DLL’s that are being referenced by my DLL…In this case the TFS DLL’s.)

OK…So the DLL is built, now we have to use the DLL to create an action that a FB project can use.

1. Open FB Action Studio and create a new action package.
2. While in the new package, create a new action.
3. Add all the properties that will be passed to the DLL via ExpandProperty, in this case I required:
   1. ProjectPath – TFS path to the set of files I want to check.
   2. SinceDate – A date that represents a cutoff point for changes to be included in the list.
4. Create a property page (interface form). This will be the representation of your action while interacting with the FB project. I added three controls to the page:
   1. Last Date Time – Corresponds to the SinceDate property being sent to the DLL.
   2. TFS Path – Corresponds to the ProjectPath property being sent to the DLL.
   3. Iterator Variable – Variable that will be stuffed with the GetCurrentValue method’s return value.
5. On the Action Details section, add two entries (found on the bottom of the page). These tell the action which DLL to use and which class is associated with the action.
   1. Namespace.Classname – In my case this was somthing like Company.Unit.FBIntegration.ChangedItemIterator.
   2. Assembly – Which is the name of the DLL or FBIntegration.dll
6. Save the package, using an appropriate name for both the package and action.

Done! Now if I open FB can see a new category with a new action item in it (TFS Change Iterator). The best part is that this can be done with anything that can be coded in .NET, so the possibilities are pretty much endless. In fact, this might be a good candidate for a VSoft packaged component. If you do happen to go down this road, I would be happy to guide you and help out.

Data refreshes are a vital part of preparing an environment for testing. During any rigorous testing phase, two things occur to the data in the test environment: 1) the data gets modified (or even mangled) when testing the extremes of the possible data ranges; and, 2) the data gets stale (further from the current production state). This also occurs in the development environment, but to a lower extent. Normally all you have to do is grab a production backup, push it down to the target environment and overlay the immediate build. But what if you have intermixed environmentally sensitive data with the regular data?

“What the heck is ‘environmentally sensitive data’?” I can here you say. OK…here’s the scenario: You have tables in the application’s database that contain URL’s to the environment’s web service host. You restore the production back onto the testing server, but now you have production URL pointers in the test environment. If testing proceeded without changing the URL’s to the test web service host there could be major issues including, but not limited to:

• Invalid testing results.
• Misreported defects.
• Updating production system during testing.
• Inappropriate outbound communications. (Yes this does happen!)

What other types of data could cause similar issues: Email addresses, redirecting URL’s, server names, drive URI’s, account numbers, etc. In fact, up to the time I left Spending Accounts, new types of metadata were being integrated into the databases every release. Each new piece of metadata increased the possibility of an issue turning into a full blown production support incident or a PR problem.

Needless to say, we would want to minimize these types of occurrences, so ensuring the correct metadata is in place is vital to both release testing and system integrity. So with this in mind I set out a strategy that would be generic enough to work regardless of the actual platform or number of environments.

The construct’s rules are as follows:

1. Data is only pushed down to a lower environment (Production -> UAT -> Testing -> Development).
2. Code is only pushed up to a higher environment (Development -> Testing -> UAT -> Production).
3. Metadata is maintained only in the environment that it is targeted for.

Sound easy enough? In general, it is pretty easy to grab a backup from a production database and restore it to a lower environment (taking into consideration any size limitations). And it is also pretty easy to overlay a backup with whatever builds (source code) that has not yet been applied.

But the metadata….That is a little tougher. It must be updated ‘in-place’ as it were: You must save it off prior to restore of the production data, and then overwrite whatever was brought over from production. And to make it a little tougher, the records are not always a one to one match. You might also have some issues with surrogate key consistencies that must be taken into account. So when I first started to look at the possible solutions I could:

• Write DML scripts to completely update the data: A little tedious, even if you write code to create the DML. I would have to generate the scripts before every refresh for every environment. Also there is the problem of interspersed metadata in primarily non-metadata tables…bother!
• Keep a separate copy of all metadata containing tables, by environment, segregated from the other environments. Then overlay the restored database with the metadata. Sounds much simpler….But is it?

After a little research, I turned to an old friend…the SQLToolbelt from Redgate Software. Specifically, I used the SQL Data Compare tool. This allowed me to:

• Create projects that allowed the polling and storage of data from a target environment to a common database.
• Create projects that allowed the syncing of data from the common database to the target environment.
• Prepare for command-line execution for open-system automation.
• Create reports to a file (for both later auditing/accountability and concatenate into overall system health report).

I had SQLToolbelt certified here at Humana and we purchased a couple of licenses. We used this tool to automate the data cleansing and refresh process. Later on I’ll give a detailed outline of the actual data refresh process for Spending Accounts.