Using delphix for DBCC CHECKDB on SQL Server?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered
Hi all

Has anyone tried running DBCC CHECKDB using Delphix...is it viable?  We were thinking of running physical only on our prod dbs and provisioning to delphix to perform a full DBCC check.

Is there a way to automate the provisioning of DBs?  So the DB is provisioned, with a post script to run DBCC CHECK, and then delete the vdb automatically with no manual intevention?
Photo of Mark Hayter

Mark Hayter

  • 394 Points 250 badge 2x thumb

Posted 4 years ago

  • 0
  • 1
Photo of Adam Bowen

Adam Bowen, Official Rep

  • 17,418 Points 10k badge 2x thumb
Official Response
Though I have never done it, I don't see any reason wny you couldn't execute dbcc on a VDB leveraging a post script.

You could write a script that executes against the Delphix API to accomplish what you are asking in regards to the automatic teardown. I figure this would be the way to go, as you likely need conditional logic to handle a vdb where the DBCC check fails.
Photo of Darin Tully

Darin Tully, SQL DBA

  • 530 Points 500 badge 2x thumb
Official Response
Mark,

Running DBCCs against the VDBs is a great way of moving the IO demand off of the production systems. As Adam stated, if you want to create the VDB and then destroyed afterwards, you'll need to create some scripts that call the Delphix APIs.

If you don't mind the VDB being left on the system, you can run the following and modify it based on your processes or policies:

1. Create a VDB on the environment you want to run the DBCCs on
2. When configuring the VDB, enter a Post script that executes the DBCC command
3. Create a VDB Refresh policy to refresh the VDB

For my test, I entered the following for the Post script:
C:\MSSQL\PrePost\DBCC.ps1

The "DBCC.ps1" script contains the following:
function die {    "Error: $($args[0])"
    exit 1
}

function verifySuccess {
    if (!$?) {
        die "$($args[0])"
    }
}

sqlcmd -b -U sa -P password -S "tcp:WIN2012R2STD\SQL2012,49915" -q "use Delphix_Admin_Test5_vdb10; DBCC CHECKDB"

VerifySuccess "sqlcmd failed to insert values"

In my script I've hard-coded the VDB name (as of version 4.0 you can use environment variables for the VDB instead). After the VDB is created the Post script will be called and a DBCC will run against the database. And when the VDB refresh policy executes, it will also call the same script once the VDB is refreshed. Here's the sql log information confirming the DBCC executed:

Date 7/11/2014 8:30:11 PMLog SQL Server (Current - 7/8/2014 11:20:00 PM)

Source spid57

Message
DBCC CHECKDB (Delphix_Admin_Test5_vdb10) executed by sa found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 6 seconds.  Internal database snapshot has split point LSN = 0000001e:0000164e:0001 and first LSN = 0000001e:0000164c:0001.

And you can switch from calling the DBCC directly in the ps1 script and instead call a sproc, passing the VDBs name and capturing the results in a temp or table variable to process the output for any errors.