MSSQL V2P file mapping

Introduction

This article describes how to customize file path mappings when performing a Virtual to Physical (V2P) operation for MSSQL databases. During the V2P process, it could be required to create mappings between the files and directories existing on the staging host, as well as files and directories created on the target. For example, putting all of the transaction log files that exist on the staging environment into a folder on the target machine.

The Configuration section of the process has name fields that can be specified for the database and directories, as shown in the screenshot below.

• Database Name: V2PDatabase

• Target Directory: C:\temp

• Data Directory: data

• Archive Directory: archive

• Script Directory: script

• Temp Directory: temp

• External Directory: external

When working with File Mappings, the data file names affect everything that follows. These data files, including log files and File Streams folders, inherit file names from the dSources and VDBs by default (e.g., SourceDB2.mdf, SourceDB2_log.ldf, File_Stream, etc.). Users have control over the data files that are part of the dSource and VDB snapshots.

Archive log files go directly into the C:\temp\archive directory. If provided, the V2P process automatically appends the target directory and data directory to the data file names, as shown in an example list below.

• C:\temp\data\SourceDB2.mdf

• C:\temp\data\SourceDB2_log.ldf

• C:\temp\data\File_Stream

Pattern matching

Pattern Matching rules can be used to create full path names for data files and control files. These rules have take this format: source-regex-expression-KEY ? target-replacement-VALUE. Multiple rules can be used and are applied successively. In addition, multiple rules with the same source key are allowed.

File mapping options

Example 1

For this example, the ultimate goal is to perform a V2P operation that has the following data file File Mappings:

• C:\temp\SourceDB2\data\SourceDB2.mdf

• D:\temp\SourceDB2\logFiles\SourceDB2_log.ldf

• E:\temp\SourceDB2\fileStream\SourceDB2_File_Stream

The default behavior can be changed to modify the target directory, modify the data directory, or modify the individual file location 

• Modify the target directory.

• Modify the data directory.

• Modify the individual file location by using File Mapping.

To modify the location of each file, select Configure File Mapping in the Advanced tab of the Configuration page.

Click the + button to add a new File Mapping or fetch all of the available data files that can be modified by simply clicking Validate with an empty File Mapping list, as shown in the screenshot below.

When modifying the file location for the received files, the Copy to Mapping Rules option copies the current (source location) and new (target location) to File Mapping, or the values can be entered manually to Find and Replace. The steps below are to follow. 

1. Since the new (target location) of the SourceDB2.mdf would appended to C:\temp\SourceDB2\data\SourceDB2.mdf (a combination of C:\temp\SourceDB2 (target directory), data (data directory), and SourceDB2.mdf (file name)), applying File Mapping is not necessary. 

2. Copy the SourceDB2_log.ldf file using the Copy to Mapping Rules option.

3. Place the SourceDB2_log.ldf file into the D:\temp\SourceDB2\logFiles\SourceDB2_log.ldf location. 

4. Configure Replace to D:\temp\SourceDB2\logFiles\SourceDB2_log.ldf and then select Validate to see the results.

   

5. Similarly, to move File_Stream to E:\temp\SourceDB2\fileStream\SourceDB2_File_Stream, provide the mapping as shown below.

   

6. Select Validate between each new entry, in order to verify that data files are being mapped as expected.

7. The File Mappings build upon one another, so all the provided File Mapping Rules are applied sequentially to each source file in order to generate a target file location.

8. Once all the files are located as desired, select Next to continue the provision process.

9. The Summary page will show the modifications to Target Directory and Database Name directories, and will show that Customized File Mapping was defined.

10. After V2P completes, login to the target server and verify that the data files were mapped correctly.

    

Example 2

In this example, several rules are applied to the source file path for SourceDB2.mdf. Note that the rules are applied in sequential order, as shown in the screenshot below. 

1. Applying the rule Source?McLaren results in: C:\temp\data\McLarenDB2.mdf

2. Applying the rule McLaren?Ferrari results in: C:\temp\data\FerrariDB2.mdf

3. Applying the rule FerrariDB2?FerraiNew results in: C:\temp\data\FerraiNew.mdf

4. Applying the rule Source?no results in an error, because Source is no longer found in the pathname.

   

During the pattern matching process, various errors can be generated. Some of these errors are described below.

1. No match for specified mapping rules When none of the rules match a source file

2. Invalid regex pattern specified for path mapping An invalid regex rule mapping error

3. File Extension Mismatch A modified file extension or folder to file conversion error

4. Duplicate target Paths When the same target path is created for multiple source files

The java.regex.util class article (redirect to docs.oracle.com) shows the regular expression syntax and constructs recognized by the Delphix Engine pattern-matching operations.

As “replace” takes an String value to create a valid windows path, characters like ?, <, >, |, *, " are not supported.