Skip to main content
Delphix

How to reconnect a SQL Server dSource following a Mirroring failover

 

Applicable Delphix Versions

 

Major Release

All Sub Releases

5.1

5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1

5.0

5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1 ,5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

4.3

4.3.1.0, 4.3.2.0, 4.3.2.1, 4.3.3.0, 4.3.4.0, 4.3.4.1, 4.3.5.0

4.2

4.2.0.0, 4.2.0.3, 4.2.1.0, 4.2.1.1, 4.2.2.0, 4.2.2.1, 4.2.3.0, 4.2.4.0 , 4.2.5.0, 4.2.5.1

4.1

4.1.0.0, 4.1.2.0, 4.1.3.0, 4.1.3.1, 4.1.3.2, 4.1.4.0, 4.1.5.0, 4.1.6.0

4.0

4.0.0.0, 4.0.0.1, 4.0.1.0, 4.0.2.0, 4.0.3.0, 4.0.4.0, 4.0.5.0, 4.0.6.0, 4.0.6.1

3.2

3.2.0.0, 3.2.1.0, 3.2.2.0, 3.2.2.1, 3.2.3.0, 3.2.4.0, 3.2.4.1, 3.2.4.2, 3.2.5.0, 3.2.5.1, 3.2.6.0, 3.2.7.0, 3.2.7.1

3.1

3.1.0.1, 3.1.1.0, 3.1.2.0,  3.1.2.1, 3.1.3.0 , 3.1.3.1, 3.1.3.2, 3.1.4.0, 3.1.5.0, 3.1.6.0

3.0

3.0.0.3, 3.0.0.4, 3.0.1.0, 3.0.1.1, 3.0.1.2, 3.0.1.3, 3.0.2.0, 3.0.2.1, 3.0.3.0, 3.0.3.1, 3.0.4.0, 3.0.4.1, 3.0.5.0, 3.0.6.0, 3.0.6.1

Issue

This document describes how to re-attach a dSource to a SQL Server Source Database following the failover of a SQL Server Mirrored Database.

If a SQL Server database in a Database Mirroring configuration is failed over to the mirror server, the Delphix Engine will report that it is no longer able to contact the Source database, with the message "dSource - Cannot contact source":

2017-11-01-16-50-06.png

This is because the Source Database that the Delphix Engine is connected to is no longer the active database, but the mirror database. The mirror database is in NORECOVERY mode and can no longer be queried by the Delphix Engine.

Using the steps provided in this document, a dSource can be reconnected to the active database if one the following has occurred:

  • The mirroring configuration is High-Safety Mode with Automatic Failover, and an automatic failover occurs; or
  • The mirroring role is swapped gracefully, using the command ALTER DATABASE database_name SET PARTNER FAILOVER

A forced failover, where the FORCE_SERVICE_ALLOW_DATA_LOSS option is used, will generate a recovery fork, as described below.

Recovery Forks

The Delphix Engine automatically supports failover of several High Availability configurations, but requires manual intervention for SQL Server Mirroring configurations:

2017-11-03-16-48-57.png

Database failover methods marked "No" in the above table trigger a recovery fork in SQL Server. This affects source continuity in the Delphix Engine, and prevents new snapshots from being taken as part of the same Timeflow.

This issue, and resolution steps, are discussed further in our Knowledge Base article How to solve issues with MSSQL backups when recovery fork GUID changes on the source database.

Resolution

To re-connect to the Source Database following a failover, the dSource must be unlinked from the existing Source Instance, and then re-attached to the SQL Server instance which now hosts the Primary copy of the database.

This involves the following steps:

  • Add the new environment to the Delphix Engine
  • Verify that the Recovery Fork ID has not changed
  • Record the current dSource Configuration
  • Detach the dSource
  • Attach the dSource to the new environment

Add the new environment to the Delphix Engine

The host will need to be added as an Environment to the Delphix Engine, if this has not already been done. This can be performed from the Manage -> Environments screen, using the Add Environment button.

2017-11-01-18-04-02.png

The new environment will need to meet the same requirements as any other environment, as described in Overview of Requirements for SQL Server Environments. This includes:

If the SQL Server username is the same between both environments, it should have the same SID (Service ID) on both SQL Server Instances. If the Service ID differs, the database-level users will become orphaned after mirroring failover. The document How to correct orphaned SQL Server database users was written for a slightly different scenario, but describes this issue and offers resolution steps.

Verify that the Recovery Fork ID has not changed

Once the failover has been performed, and a backup taken from the new Primary host, the following query (substituting <DATABASE_NAME> as appropriate) can be used to check whether the Recovery Fork ID has changed:

SELECT TOP 100
  bs.database_name, bs.backup_start_date, bs.type, bs.last_recovery_fork_guid, bs.first_lsn, bs.last_lsn, bmf.physical_device_name
FROM
  msdb.dbo.backupset bs INNER JOIN
  msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE
  database_name IN ('<DATABASE_NAME>')
ORDER BY
  bs.backup_start_date DESC

This will produce output similar to the following:

database_name  backup_start_date        type  last_recovery_fork_guid               first_lsn           last_lsn            physical_device_name
StackOverflow  2017-11-03 09:30:00.000  L     DBF4071F-0B77-4B3E-98E2-81E3BCB30383  286000000891900001  286000000892700001  ..._20171103_093000.trn
StackOverflow  2017-11-01 12:30:00.000  L     DBF4071F-0B77-4B3E-98E2-81E3BCB30383  286000000891600001  286000000891900001  ..._20171101_123000.trn
StackOverflow  2017-11-01 12:15:00.000  L     DBF4071F-0B77-4B3E-98E2-81E3BCB30383  286000000891300001  286000000891600001  ..._20171101_121500.trn
StackOverflow  2017-11-01 12:00:00.000  L     DBF4071F-0B77-4B3E-98E2-81E3BCB30383  286000000891000001  286000000891300001  ..._20171101_120000.trn
StackOverflow  2017-11-01 11:45:00.000  L     DBF4071F-0B77-4B3E-98E2-81E3BCB30383  286000000890700001  286000000891000001  ..._20171101_114500.trn

The last_recovery_fork_guid of the most recent backups should be the same on both servers. A difference in this value indicates that a recovery fork has occurred, and that the database will need to be relinked.

Record the current dSource Configuration

From the Manage -> My Datasets screen, browse to the dSource's Configuration tab, and note the value of all editable fields, including:

  • Database User (DB User)
  • Validated Sync Mode
  • Staging Environment
  • Pre Script and Post Script (these are not commonly used for dSources)
  • Autodiscover Backup Path setting, and/or the configured Backup Path

Please note that the Password for the DB User cannot be extracted via the Management Interface. You will need to retrieve this password from your own records, for entry in a later step.

2017-11-03-17-40-22.png

In most cases, you will want to replicate these settings when re-attaching the dSource.

Detach the dSource

The dSource can be detached using the dSource's Unlink dSource button, in the Configuration tab.

2017-10-28-17-21-37.png

Once this is complete, the dSource will appear in the left-hand dataset lists as Detached.

Attach the dSource to the new environment

The following steps must be performed via SSH, using the Delphix Engine's Command Line Interface, as there is presently no mechanism for re-attaching dSources from the GUI.

For a more detailed guide to this process, please refer to the document CLI Cookbook: Detaching and Attaching a SQL Server dSource.

Use an SSH terminal such as PuTTY to connect to your Delphix Engine, using your Delphix login credentials.

As an example, a dSource can be attached to a new Source Instance using the following commands. The CLI offers tab-completion, so it is possible to show a list of Source Databases by typing set config= and pressing the Tab key.

/database
select StackOverflow
attachSource
edit attachData
set type=MSSqlAttachData
set config=NEW_SOURCE_ENVIRONMENT_NAME/MSSQLSERVER/StackOverflow
set dbUser=delphix_db
set dbCredentials.password
[enter password]
set pptRepository=STAGING_ENVIRONMENT_NAME/MSSQLSERVER
set sharedBackupLocation=\\SERVERNAME\SHAREDBACKUPFOLDER
set validatedSyncMode=FULL   <-- Can be FULL, FULL_OR_DIFFERENTIAL, TRANSACTION_LOG, NONE
back
ls
commit

With the exception of config, most of the parameters used above should reflect the dSource configuration noted in an earlier step. The output of the ls command will allow you to confirm that all appropriate variables have been set.

Once commit has been entered, the Delphix Engine will create an Attach Database job. The job progress can then be monitored via the CLI, or from the Admin App (GUI).

External Links

The following Microsoft documents provide more details on some of the failover commands described in this article.