DBA Repository – Load DNS Aliases

In my environment the DBA team is part of Application Development and separate from the Networking team.  The DBA team encourages the use of DNS aliases on database servers for individual applications.  Having a DNS alias for a database server for each application provides a significant amount of flexibility for maintenance tasks.

If an application begins to consume too many resources on a given server due to an upgrade and resulting change in architecture or to expanded utilization across the enterprise it can be quickly and easily moved to a new location simply by moving the database files and updating the DNS alias.

Also, if a server’s hardware is due to be upgraded the cut-over is simplified because the database files can be migrated at individual department’s time frames and DNS aliases are reconfigured at the appropriate time to complete the change.

In any of these cases configuration of connections strings don’t change so Windows apps, Web apps, ad-hoc reports, DSN’s, linked servers or any connection method to the data source is unaffected by the move.  An important aspect to using many DNS aliases is ensuring they’re used appropriately and removed when unused.  In order to do this in my environment where the DBA team does not have access to the DNS configuration directly we created a simple process to allow the Network team to export the DNS CNAME list to an Excel spreadsheet which the DBA team can import the DBARepository.

Create the dbo.ServerAliases Table

We’ll need a table to store all the aliases we find in DNS for each database server in the DBARepository, here’s the table:

CREATE TABLE [dbo].[ServerAliases] (
      [ServerName] [nvarchar](128) NOT NULL
    , [Alias] [nvarchar](256) NOT NULL
    , [FileLoaded] [datetime] NOT NULL,
    )
GO

Load DNS Aliases SSIS Package

The package that loads the alias data is straight-forward, it performs the following logical steps:

  • Look for a new Excel file; if the file is outdated, email a reminder to create a new one
  • Delete the old alias data from the DBARepository
  • Transfer the new alias data to the DBARepository

Here’s what the package looks like:

Get DNS File Attributes Script Task

This task is a small C# script that checks the creation time of the file.  It takes a read only variable for the path to look for the Excel spreadsheet and returns a the create time of the file:

public void Main()
{
    string fileName = Dts.Variables["DnsFilePath"].Value.ToString();
 
    FileInfo dnsFile = new FileInfo(fileName);
    if (dnsFile.Exists)
    {
        Dts.Variables["DnsFileCreateDate"].Value = dnsFile.CreationTime;
        Dts.TaskResult = (int)ScriptResults.Success;
    }
    else
    {
        Dts.Events.FireError(0, @"Dts.Variables[""TaskName""]", String.Format("The file {0} could not be found", fileName), String.Empty, 0);
    }
}

Get Previous DNS File Load Date Execute SQL Task

The next task queries dbo.ServerAliases to find the last time the data was loaded and stored the date in a user variable so it can be compared to the file creation date from the previous step.

Precedence Constraint Logic

The two success precedence constraints leading from the Get Previous DNS File Load Date task check for:

  1. Our Network team agreed to provide a new file every two weeks, the 1st constraint checks the age of the Excel file.  If it is 15 or more days stale the package will execute the Notify DBAs DNS Data is not new Send Mail task.  That is a simple reminder email sent to the DBA and Network teams.
  2. The 2nd constraint checks if the file is newer than the last loaded date, if so control flows to the Load DNS Aliases Sequence Container

Remove Old DNS Aliases Execute SQL Task

When we have a new file this task will delete dbo.ServerAliases

Gather DNS Aliases Control Flow

This control flow performs a couple simple but important transforms to get the data we’re interested in, here’s what the flow looks like:

Conditional Split Transform

This transform splits out only those records in the Excel file that are CNAME entries so we pick up only the aliases

Clean Server Names Derived Column Transform

The Excel file has the Fully Qualified Domain Name for the the servers and our DBARepository only uses the host name.  This transform will extract the host name from the FQDN so we can compare apples to apples in the next step.

Find DBA Servers Lookup Transform

This lookup queries the server.Servers view to find all the database servers configured in the DBARepository.  The transform only outputs those aliases in the data stream that match our DBARepository which are then input to the dbo.ServerAliases table in the OLE DB Destination at the end of the data flow.

Summary

This SSIS package takes the input form an Excel spreadsheet provided by out Network team.  It performs a few checks to make sure the data it’s loading is fresh and sends a reminder to the appropriate people if it is stale.  All the email addresses are in a config file so we can easily update it if contacts change.

Once the package determines the file is new it kicks out any data that isn’t an alias, cleans up the FQDNs to host names compares the servers in the file to those in the DBA Repository and loads only aliases for those servers in the DBARepository.

This process helps the DBA team ensure applications are using the correct DNS aliases to access their data and ensures all aliases are reconfigured properly when a hardware change occurs.

Enjoy!

Leave a Reply

Your email address will not be published. Required fields are marked *