Linking a dSource from a SQL server

When linking a dSource from a SQL Server source database, Delphix offers several different methods of capturing backup information:

• Delphix Managed Backups, where the Delphix Engine schedules and initiates the backups from the source database, and captures them

• SQL Server Managed Backups, where the SQL Server source database schedules and initiates backups and the Delphix Engine captures them

  • Full backups

  • Full or differential backups

  • Transaction log backups (with LogSync disabled)

  • Transaction log backups (with LogSync enabled)

Delphix Managed Backups are conceptually a lot simpler to explain, but they can be considered less desirable because they might be viewed as intrusive. SQL Server Managed Backups are explained in two sections:

• the initial load of the dSource from the source database

• subsequently keeping the dSource synchronized with the source database.

Source databases cannot be in Read-Only mode at the time that a backup is taken. While the snapshot will succeed, attempts to provision VDBs from those snapshots may fail with an error during the provision process.

Temporarily setting the database to read-write mode, and taking a new backup and snapshot while the database is in in this state, will allow VDBs to be provisioned successfully.

Below is a brief explanation of how these three different modes of operation work.

Using Delphix as a backup solution to SQL Server

Delphix provides you the option to automatically manage backups from SQL Server source databases into the Delphix Engine. Prior to Delphix 4.2, users could not link source databases that were backed up by unsupported backup software. In Delphix 4.2, a feature known as Delphix Managed Backups was introduced that allows you to have Delphix take and manage backups from your source database directly into Delphix storage. This is the first step in Delphix becoming a full-featured backup solution for SQL Server databases. When the Delphix Engine manages the backups for a dSource, it takes regular, copy-only full backups of the source database, so activating the feature will not interfere with existing backup management solutions. You can configure the schedule of when the Delphix Engine takes these copy-only full backups by specifying a SnapSync policy for the dSource. You can change the SnapSync policy for a dSource at any time by visiting the policy screen; there, you can either select a new SnapSync policy or modify the current one.

If you use a backup solution that is not supported by Delphix, you cannot use your existing backups to keep your dSources in sync. However, enabling Delphix Managed Backups will overcome this issue by using automatic copy-only full backups to keep dSources in sync. Currently, dSources linked when this feature is enabled will not support LogSync functionality, which means that you can only provision VDBs from snapshots and not from any time between snapshots. Additionally, in the current release, the Delphix Engine cannot take differential or transaction log backups of the source database.

Backup compression feature overview

Since Delphix Engine 5.2, the Delphix Engine has allowed compression to be enabled ("forced") for SQL Server backups which use Delphix Managed Backups. Delphix Managed Backups are used to synchronize SQL Server dSources when existing SQL Server backup files cannot be made available to the Staging server, or if a third-party backup vendor is used that is not yet supported by Delphix. For more information on this functionality see Delphix as a Backup Solution to SQL Server and Linking a dSource with SQL Server.

Backup compression is preferable in the following situations when:

• The default backup compression setting for the Source SQL Server instance is Disabled (0)

• The Source database does not use Transparent Data Encryption (TDE)

• The Source server has available CPU resources to perform compression

Where the compression ratio of a compressed backup exceeds 3:1, this will reduce the amount of data (by half) that must be transferred over the network to perform a backup, resulting in much faster SnapSync operations.

How to enable backup compression

If the SQL Server instance's default backup compression setting is enabled (see Microsoft's document View or Configure the backup compression default Server Configuration Option), no specific action is required. Backups will automatically be compressed in accordance with this setting.

If backups are not automatically compressed, the Delphix Engine (5.2 and later) can be configured to force compressed backups using the following steps:

• Login to the Delphix Engine's Management interface.

• Open the Manage > Datasets screen.

• Locate and select the dSource (using the tree on the left).

• Navigate to the Configuration > Data Management pane.

• Click the Edit (pencil) icon to modify the Validated Sync settings.

• 

  Select the Force Compression checkbox, then the select the Tick icon to save the changes

  

The change will automatically take effect during the next scheduled Snapshot/SnapSync operation.

Delphix managed backup mode

When the Enabled checkbox for Delphix Managed Backups is selected, the Delphix Engine will initiate a COPY_ONLY full backup of the source database for the initial load of the dSource. Thereafter, the Delphix Engine will initiate COPY_ONLY full backups of the source database using the schedule specified by the selected SnapSync Policy. If SnapSync Policy is set to None, the Delphix Engine will not automatically initiate COPY_ONLY backups, but you can initiate them manually using the snapshot (camera) icon.

When the Force Compression checkbox for Delphix Managed Backups is selected and the backups are not compressed, the Delphix Engine will take a compressed copy-only full backup of the source database and this will take effect during the next scheduled Snapshot or SnapSync operation.

COPY_ONLY backup files are written to the Delphix storage that has been mounted on the staging server. Delphix does not require space on the source or staging servers to hold the backup files.

You must also select the Staging Environment and the SQL Server instance onto which the backups will be restored.

Understanding snapSync policies

SnapSync policies provide you the ability to specify how frequently the Delphix Engine takes a copy-only full backup of a source database when Delphix Managed Backups are enabled. Selecting an initial SnapSync policy is mandatory at dSource link time. However, you can change the SnapSync policy that the Delphix Engine applies to a dSource at any time by visiting the policy management screen:

1. Click Manage.

2. Click Policies.

Check SnapSync Policy

For dSources that have Delphix-managed backups enabled, the current SnapSync policy will be displayed under the SnapSync column. The rows corresponding to dSources that do not use Delphix Managed Backups will be grayed out. Clicking the current SnapSync policy for a dSource will display a drop-down menu of existing SnapSync policies along with the option to create a new SnapSync policy. Selecting a SnapSync policy from this list will change the current SnapSync policy for the dSource. When creating a new policy, you will see the following screen:

Create New SnapSync Policy

Here, you can configure the frequency with which the Delphix Engine takes backups of your source database. You can modify these schedules at any time by clicking the Modify Policy Templates button in the upper right-hand corner of the policy management screen.

The Timeout field above specifies how long a SnapSync job is allowed to run before it is terminated. If a SnapSync job exceeds its timeout window, the Delphix Engine discards the new backup and rolls back the dSource to the most recent snapshot.

SQL server managed backup modes

Initial load of the dSource

When the Delphix Managed Backups option is left unchecked (default), the Delphix Engine will initiate a backup only when the user selects to initiate a COPY_ONLY full backup that the Delphix Engine will use to keep the dSource in sync with the source database.

For the initial load of the dSource, you can choose one of the following:

• have the Delphix Engine initiate a COPY_ONLY full backup

• use the most-recent existing full or differential backup (default)

• use a specific existing full or differential backup identified by its backup_set_uuid

Simple Recovery Model

If the source database is using a simple recovery model, using a new full COPY_ONLY backup initiated by the Delphix Engine is not supported for the initial load of a dSource.

After the initial load, you need to select the Backup Paths and tell the Delphix Engine where to look for backups of the source database.

• If Autodiscover is selected, the exact path used to take the backup from the source database will be determined by querying by the source database instance. If this option is used, the Delphix Engine should take backups to a UNC path (Windows file share) so that they are accessible to the Staging Server.

• If custom paths are specified, the Delphix Engine will query the source database instance, identify the filename of the source backup, and then recursively search the specified Backup Paths for this filename. These paths should also be UNC paths (Windows file share) which are accessible to the Staging Server.

The path used to restore backups must be readable by the Windows server hosting the staging instance, using the staging environment's configured Environment User.

The Delphix Engine supports source database backups that SQL Server creates natively, as well as backups created by Quest/Netvault LiteSpeed, Red Gate SQL Backup Pro, Veritas NetBackup, and Commvault. For more information, see the topic SQL Server Support Matrix.

Once you have decided how the dSource will be initially loaded, select the staging environment and the SQL Server instance onto which the backups will be restored. NetBackup and Commvault backups are not on local storage and therefore the Backup Paths will just be ignored. See Linking a dSource from a NetBackup SQL Server Backup and Linking a dSource from a Commvault SQL Server Backup for more information. 

The staging instance opens the backup file for reading and may hold a lock on it when restoring the backup on the staging database. A new source database backup, initiated with the Append to the existing backup set option, may fail as SQL Server will not be able to open the locked backup file to append a new backup to it.

Keeping the dSource synchronized with the source database

Next, specify how the Delphix Engine will capture subsequent backups of the source database.

The selected Validated Sync mode determines how often the Delphix Engine will check for new backups, and which type of backups it will check for. You can always force synchronization with the source database by enabling Validated Sync Mode from the Data Management tab available under the Configuration tab for the selected dSource.

Validated sync and logSync

When you link a source database into the Delphix Engine, a staging database will still be required if the Validated Sync is not enabled, as described in Overview of Setting Up SQL Server Environments. In this process, the Delphix Engine continuously monitors the source database for new full and differential backups if the source database is using a simple recovery model, or transaction log backups if using a full recovery model. This will also depend on the selected backup mode. When it detects a new backup, it restores that backup to the staging database with the storage residing in Delphix. The result is a TimeFlow with consistent points from which you can provision a VDB, also known as snapshots.

Snapshots accumulate over time. To view a snapshot:

1. From the Datasets panel, click the group containing the dSource.

2. Select dSource.

3. Click the TimeFlow tab.

Each snapshot is displayed and includes some information about the captured database along with Snapshot database change number (SCN for Oracle and LSN for SQL Server). You can scroll through these cards to select the one you want, or you can enter a date and time to search for a specific snapshot.

Summary of validated sync modes

This table summarizes each mode of Validated Sync, displaying how often the Delphix Engine will poll to check for new backup files when it creates snapshots for the dSource, and whether point-in-time restores for provisioning and refreshing virtual databases (VDBs) is possible or not.

Timeflow cards

The Delphix Engine will create a Timeflow card for each backup it restores to the staging server. For example:

• A database in Full backups Validated Sync mode, and daily backup configured on the dSource, would receive one Timeflow card per day

• A database in Full or differential backups sync mode, with one daily backup and two differential backups per day, would receive three Timeflow cards per day

• A database in Transaction log backups sync mode, with a log backup every 15 minutes, would receive 96 TimeFlow cards per day

Linking data sources with SQL server

Linking a dSource will ingest data from the source and create a dSource object on the engine. The dSource is an object that the Continuous Data Engine uses to create and update virtual copies of your database. As a virtualized representation of your source data, it cannot be managed, manipulated, or examined by database tools. 

For an overview of all dSource related actions, please Managing Data Sources and Syncing Data.

When linking a dSource from a SQL Server source database, Delphix offers several different methods of capturing backup information:

• SQL Server Managed Backups, where the SQL Server source database schedules and initiates backups and the Delphix Engine captures them

  • Full backups

  • Full or differential backups

  • Transaction log backups (with LogSync disabled)

  • Transaction log backups (with LogSync enabled)

• Delphix Managed Backups, where the Delphix Engine schedules and initiates the backups from the source database, and captures them.

Source databases cannot be in Read-Only mode at the time that a backup is taken. While the snapshot will succeed, attempts to provision VDBs from those snapshots may fail with an error during the provision process.

Temporarily setting the database to read-write mode, and taking a new backup and snapshot while the database is in in this state, will allow VDBs to be provisioned successfully.

Procedure

1. Login to the Delphix Management application.

2. Navigate to Manage > Datasets.

3. Click the plus icon and select Add dSource.

4. In the Add dSource wizard, select the source database with the correct environment user-specified.

5. Select user type for source database authentication and enter the login credentials. Enter username and password for Database user or Domain (Windows) user. For Environment User, select a source environment user from the dropdown list and click Next.

6. Enter a name and select a group for your dSource.
   Adding a dSource to a dataset group lets you set Delphix Domain user permissions for that database and its objects, such as snapshots. See the topics under Users and Groups for more information.

7. Select the Data Management settings needed. For more information, Data Management Settings for SQL Server Data Sources.

8. Select the Staging environment and SQL Instance that will be used to manage the staging database used for validated sync of the dSource.

9. Select any policies for the new dSource.

10. Enter any scripts that should be run on the Hooks page.

11. Review the dSource Configuration and Data Management information, and then click Submit.