Reconnecting a SQL Server dSource Following a Mirroring Failover (KBA1390)

Adding 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.

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:

• A Domain User who is part of the OS-level Backup Operators group (see Windows Users and Permissions on Database Servers)
• A login for the above Domain User on the SQL Server Instance (see Database User Requirements for SQL Server)
• A dedicated SQL Server Login, using SQL Server Authentication, for JDBC monitoring (see Database User Requirements for SQL Server)

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.

Verifying 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.

Recording 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.

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

Detaching the dSource 

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

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

Attaching 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).