Skip to main content
Delphix

Ingesting SQL Server Backups from Different Locations (KBA1281)

 

 

KBA

KBA#1281

Issue

When linking a SQL Server dSource, Delphix presents the user with the choice of finding backups via "Autodiscover" or by searching recursively through a defined "Backup Path".

  • Autodiscover works when the "BACKUP DATABASE" command specifies a SMB share that is available to the SQL Server staging instance. Delphix is then able to query some of the tables in the msdbdatabase. In particular, it can query the column physical_device_name of the backupmediafamily table for the location of the backup. For example:

    BACKUP DATABASE

    BACKUP DATABASE [tinydb] TO  DISK = N'\\sqlprod52\\b$\\Backup\tinydb.bak' WITH NOFORMAT, NOINIT,
    NAME = N'tinydb-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
    
  • Different scenarios may prevent the usage of the Autodiscover feature such as specifying a local directory in the "BACKUP DATABASE" command. In these situations, Delphix relies on the Backup Path pointing to a SMB share that can be accessed from the SQL Server staging instance. Delphix will recursively search through this directory for the database or transaction log dumps. Since Delphix recursively searches the backup location for the backup file(s), providing the right backup location is important. e.g. if the backup location is just the root of a drive on a file server which might have backups for a large number of databases ( \\<servername>\d$ ), Delphix would potentially have to search through many directories. Narrowing down the search scope by specifying a more relevant path in the filesystem reduces search time e.g. \\<servername>\d$\backups\<database name> .

In some situations, dumps may be striped across separate physical locations or transaction log dumps may go to one physical directory and the database dumps to a separate physical directory. Prior to Delphix 5.2.5.0, Delphix supported one Backup Path. Some extra steps were required to allow Delphix to find all of the files if they are written to separate directories and Autodiscover can not be used.

Troubleshooting 

This issue typically presents itself in a fault raised by the Delphix Engine when it can't find a backup file. For example:

title          | Backup file for the source database was not found in the backup location
description    | For dSource "AdventureWorks2012", backup file "AdventureWorks_20160711_200006_1.bak" was not found in the backup location "\\sqlprod52\b$\Backup".
response       | The Delphix Engine will keep checking the backup location for the file.
action         | Make sure the backup location is correct and the user "Delphix\srvsql" has the required read privileges on it.
severity       | WARNING

Resolution 

Delphix 5.2.5.0 and higher enables users to enter multiple backup locations to search for backups when adding new data sources.

Prior to this release, in order to allow Delphix to find backup files that are located in separate physical directories, some extra setup is needed so that a single Backup Path can be specified that Delphix can recursively search to find all the files:

  1. Share the individual backup locations over the network. Lets say c:\backups is shared as '\\<server name>\c-backups' and f:\backups is shared as '\\<server name>\f-backups'.
  2. Create a shared unified view with links to the separate backup locations in it. First, create a folder like:

    c:\unifiedbackups\<database name>
    
  3. Create links to the individual backup locations inside "c:\unifiedbackups\<database name>" using the MS-DOS command mklink. Use the UNC path to the backup locations instead of local paths when creating the links:

    mklink /D c:\unifiedbackups\<database name>\c \\<server name>\c-backups\<database name>
    mklink /D c:\unifiedbackups\<database name>\f \\<server name>\f-backups\<database name>
    
  4. Share c:\unifiedbackups. For example, share it as \\<server name>\unifiedbackups.
  5. On the SQL Server staging server host to be used with the dSource, enable "Remote to Remote SymlinkEvaluation" for Delphix to be able to search into the linked remote locations from the staging using the PowerShell fsutilcommand from an elevated command prompt. This can be done by running the following command:

    fsutil behavior set SymlinkEvaluation R2R:1
  6. On the staging host run the following command from a PowerShell prompt to verify the property has been set:

    fsutil behavior query SymlinkEvaluation

    If the property is set correctly, the output would show:

    "Remote to remote Symbolic links are enabled."
  7. On the staging host, run the following PowerShell command to confirm that the new share can be searched recursively:

    Get-ChildItem -literalPath "\\<server name>\unifiedbackups\<database name>" -recurse
  8. When linking the dSource, provide \\<server name>\unifiedbackups\<database name> as the backup location.