Minion CheckDB provides remote integrity checks, where a database may be restored to another instance for DBCC CheckDB operations.

Note: Remote operations only apply to DBCC CheckDB operations. Minion CheckDB does not support remote CheckTable. For more information, see “About: Feature Compatibility”.

Requirements

IMPORTANT: Remote CheckDB has a few requirements:

  • The source server’s SQL Agent service account must have rights on the remote server, including permissions to create jobs. And of course, the two servers must be able to “see” each other.
  • You must either be using Minion Backup 1.3 or above on the source server; or there must be an external process that restores the database(s) in question to the remote server for CheckDB operations (RemoteRestoreMode=NONE). This also means that the remote server must be a compatible version of SQL Server, that the database can restore to.
  • The remote server must have Minion CheckDB installed in the same database as the local (“source”) server. So, if MC is installed in master on the source server, the remote server must have MC installed in master, too. (Officially speaking, for Connected mode, you only need the Minion.CheckDBResult table.)
  • Remote CheckDB currently only supports Windows authentication.

For full instructions on configuring remote CheckDB, see “How to: Set up CheckDB on a Remote Server”.

Remote CheckDB modes: Connected vs Disconnected

Remote CheckDB has the option of Connected mode or Disconnected mode:

Connected mode is equivalent to running DBCC CheckDB commands from SQL Server Management Studio on Svr1, against Svr2. Connected mode maintains the connection throughout the operation(s). It does not require a full Minion CheckDB installation on the remote server. Connected mode is good for when you don’t have permissions from the remote server back to the primary server. Connected mode has fewer moving parts internally than Disconnected mode.

Disconnected mode requires a full Minion CheckDB installation on the remote server. Disconnected mode requires the most permissions, but is also the more robust option; it has higher tolerance for things like network fluctuations.

Remote Restore Modes

As you will see in the “How to: Set up CheckDB on a Remote Server” section, remote CheckDB is configured in the Minion.CheckDBSettingsDB table. The RemoteRestoreMode field has three options:

NONE – MC performs no database restore to the remote server. If you already have a process in place for restoring databases to a remote server – whether it’s a third party backup and restore proess, home grown, detatch/attach, or anything else – “NONE” allows MC to fold into the existing scenario easily. Note that with the benefit of Inline Tokens, the remote database could be named the same as the source database, or the name could be generated in some rolling process with (for example) the date or a number, like DB1.20170101. In that case, we can set PreferredDBName = ‘%DBName%.%Date%’, or the less specific ‘%DBName%.%’. When in doubt, Minion CheckDB will select the most recently created database that fits the naming scheme.

LastMinionBackup – Restores the last backup from Minion Backup to the remote server, then runs CheckDB against it.

NewMinionBackup – Takes a new backup using Minion Backup, restores it to the remote server, then runs CheckDB against it.

Dynamic Remote CheckDB

You can also define thresholds for remote CheckDB, so the operation will run remotely only if it is above that threshold.

To turn on this feature, the Minion.CheckDBSettingsDB column IsRemote must be set to 0. While this may seem counterintuitive, IsRemote = 1 turns on remote CheckDB for all databases (that the given row applies to). If you wish to handle remote operations dynamically, based on database size, set IsRemote = 0 – meaning, “I want operations to be local unless a database crosses the threshold”.

For full instructions on configuring remote CheckDB, see “How to: Set up CheckDB on a Remote Server”.