Tip of the Day: SQL Server Tips

  • 1
  • Announcement
  • Updated 4 years ago
1. When provisioning a VDB you can select a SQL Server instance that has a higher version than the source database, if the source database version is higher than SQL Server 2005. The VDB will be automatically upgraded.

2. Linking a dSource 
  • If the staging environment uses the Windows 2003 operating system, the largest size of a database that can be linked to the Delphix Engine is 2TB. This is also the largest size that a virtual database (VDB) can grow to.
  • For all other Windows versions, the maximum size for databases and VDBs is 32TB
In both cases, the maximum size of the database and resulting VDBs is determined by the operating system on the staging target host.

3. LogSync is disabled by default for SQL Server data sources. For more information about how LogSync functions with SQL Server data sources, see Managing Data Sources: An Overview.



Photo of Delphix FAQs

Delphix FAQs, Official Rep

  • 4,000 Points 4k badge 2x thumb

Posted 4 years ago

  • 1
Photo of vineet khanna

vineet khanna

  • 120 Points 100 badge 2x thumb
This is what I noticed while restoring database from SQL Server 2008 (source database)  to SQL server 2014.
Delphix automatically upgrades the VDB and the compatibility level seems to reflect 120 (which is sql 2014) if verified via SSMS gui or via views .

But when the compatibility level  is verified with DBCC DBINFO command -> This value does not reflect the same information.It still shows Sql Server 2008 compatibility level.

DBCC DBINFO only updates this information when the following command is run on the database  

'ALTER DATABASE SET COMPATIBILITY_LEVEL'

 Now what I wanted to know is if anyone else has encountered something similar and if possible can Delphix support confirm the commands which are being executed as part of this VDB upgrade?  

Photo of Annirudh Prasad

Annirudh Prasad, Employee

  • 70 Points

When a VDB is created we either online the database or recover it, and SQL Server itself handles the upgrade. It appears that the 'dbi_cmptlevel' field in the DBINFO structure is not updated as a part of this upgrade, so that value you see there is stale.

The compatibility level listed in the SSMS GUI or sys.databases appears to be the correct value. To prove this, we ran the following experiment. Running 'SELECT TRY_CONVERT(INT, 1)'  will fail for compatibility levels < 110. For a VDB with a compatibility level of 110 but with a 'dbi_cmptlevel' of 100 in DBINFO, the query runs successfully. After setting the compatibility level to 100, rerunning the query fails.

We will contact Microsoft about this issue or consider possible workarounds.
Photo of vineet khanna

vineet khanna

  • 120 Points 100 badge 2x thumb
Thanks for coming back but just to let you know when I tried to do the same with the native restore and followed it with change in compatibility with alter database statement then DBINFO was reported perfectly fine. This issue was only noticed with Delphix restore. Would it be possible to share what steps are actually executed as part of delphix restore ? 
Photo of Annirudh Prasad

Annirudh Prasad, Employee

  • 70 Points
As you note in your first post, the DBCC DBINFO structure only updates when an 'ALTER DATABASE SET COMPATIBILITY_LEVEL' command runs. Unfortunately, currently this command does not run when Delphix provisions an upgraded VDB.

Regarding your questions about what steps are executed as part of the restore, when Delphix provisions a VDB from a snapshot it creates a new SQL Server database using a copy of the snapshot's filesystem. Delphix stands up this VDB using either an 'ONLINE' or a 'RESTORE' operation, taking advantage of the fact that Microsoft automatically upgrades the files to the appropriate SQL Server version.