Reconnecting a SQL Server dSource Following a Mirroring Failover (KBA1390)
KBA
KBA#1390Applicable Delphix Versions
Major Release |
Sub Releases |
5.3 | 5.3.0.0 |
5.2 | 5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1 |
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.1.9.0, 5.1.10.0 |
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 |
Troubleshooting Re-attaching a dSource to a SQL Server Source Database
This article provides information on 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":
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.
Recovery Forks
The Delphix Engine automatically supports failover of several High Availability configurations, but requires manual intervention for SQL Server Mirroring configurations:
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 further discussed in Resolving Issues with MSSQL Backups When Recovery Fork GUID Changes on the Source Database (KBA1044).
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:
- Adding the new environment to the Delphix Engine
- Verifying the Recovery Fork ID has not changed
- Recording the current dSource Configuration
- Detaching the dSource
- Attach the dSource to the new environment
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 SQL Server Users Requirements)
- A login for the above Domain User on the SQL Server Instance (see SQL Server User Requirements)
- A dedicated SQL Server Login, using SQL Server Authentication, for JDBC monitoring (see SQL Server User Requirements)
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).
Related Articles
- Connecting to the CLI
- CLI Cookbook: Detaching and Attaching a SQL Server dSource
- Overview of Requirements for SQL Server Environments
- Resolving Issues with MSSQL Backups When Recovery Fork GUID Changes on the Source Database (KBA1044)
External Links:
- Manually Fail Over a Database Mirroring Session (Transact-SQL)
- Database Mirroring Operating Modes
- Force Service in a Database Mirroring Session