Cracking the Vault

How Much Memory Should I Allocate to the SQL AUTODESKVAULT Instance?

This question occassionally crops up from partners and customers.

The amount of memory needed is often directly linked to the amount of data needed in the cache to achieve high cache hit ratios.

Its not possible to have too much memory for SQL Server, but there must be some left over for the Operating System. So as a general rule of thumb is to have 90% of memory for SQL and leave 10% for the OS to handle general processing.

(The above suggestion assumes that SQL is running on a dedicated server with no other applications such as IIS or another SQL instance is running.)

 

Useful SQL Performance Counters (Source)

• SQL Server: Buffer Manager: Free List Stalls/sec
• SQL Server: Buffer manager: Lazy Writes/Sec
• SQL Server Buffer Manager: Checkpoint Pages/sec
• SQL Server Buffer Manager: Page Life Expectancy
• SQL Server Buffer Manager: Page Lookups/Sec
• SQL Server Buffer Manager: Page Reads/sec
• SQL Server Buffer Manager: Page Writes/ Sec
• SQL Server Buffer Manager: Readahead/sec
• Memory: Free System Page Table Entries

 

Questionable SQL Perforamnce Counters

SQL Server: Buffer Manager: Buffer Cache Hit Ratio

DatabaseError [109]

You can sometimes see this error in the Vault Server vlog. (Note DatabaseError [109] is a generic error. The Exception(Inner) message is more indicative.)

Error: Soap Exception ( mesg-id = xxxxxxxxxxxxxxxxxxxx )
Exception: DatabaseError [109] 
Stacktrace: 
Server stack trace: 


Exception rethrown at [0]: 
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
   at Connectivity.Document.Services.FolderService.AddFile(Int64 folderId, String filename, String comment, DateTime lastWrite, FileAssociationParam[] associations, BOM bom, Stream file, FileClassification fileClass, Boolean hidden)
   at Connectivity.Web.Services.v17.DocumentService.AddFile(Int64 folderId, String fileName, String comment, DateTime lastWrite, FileAssociationParam[] associations, BOM bom, FileClassification fileClassification, Boolean hidden, ByteArray`1 fileContents)

Exception(Inner): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Stacktrace(Inner):    at Connectivity.Core.Database.TransactionContext.OnSqlException(SqlException e)
   at Connectivity.Core.Database.SqlAccess.ExecuteReaderInternal(SqlCommand cmd)
   at Connectivity.Core.Database.SqlAccess.ExecuteReader(CommandType commandType, String commandText, SqlParameter[] commandParameters, Int32 commandTimeout)
   at Connectivity.Core.DataAccess.OwnershipAndAccessControlCheck.ProcessReadOnly()
   at Connectivity.Core.DataAccess.DirectorExtentions.PostBuild(ArrayList builders)
   at Connectivity.Document.DataAccess.Folders.AddFile(Int64 folderId, String name, String comment, Int32 checksum, Int64 fileSize, Int64 createUserId, Nullable`1 createDate, DateTime lastWriteTime, FileAssociationParam[] associations, BOM bom, FileClassification fileClass, Boolean hidden, Boolean unmanaged)
   at Connectivity.Document.BusinessLogic.FolderBL.AddFile(Int64 folderId, String filename, String comment, Int64 createUserId, Nullable`1 createDate, DateTime lastWrite, FileAssociationParam[] associations, BOM bom, Stream file, FileClassification fileClass, Boolean hidden, Boolean unmanagedFile)
   at Connectivity.Document.BusinessLogic.FolderBL.AddFile(Int64 folderId, String filename, String comment, DateTime lastWrite, FileAssociationParam[] associations, BOM bom, Stream file, FileClassification fileClass, Boolean hidden, Boolean unmanagedFile)
   at Connectivity.Document.Services.FolderService.AddFile(Int64 folderId, String filename, String comment, DateTime lastWrite, FileAssociationParam[] associations, BOM bom, Stream file, FileClassification fileClass, Boolean hidden)
   at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
   at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)

It will also normally be accompanied by a Client Side User Interface error dialogue message.

This normally indicates a performance bottleneck issue that needs investigating.

It can be alleviated by increasing the SQL timeouts in the web.config file. Please refer the Advanced Configuration Guide for the version of Vault that you are using for more information.

Illegal pathname or filename during Vault Backup

I have created a Vault environment with a remote SQL (AUTODESKVAULT instance on another machine) which finally went fine and I could work in Vault very well.

However creating a Vault Backup failed with an error message pointing to the SQL Share folder:
The logs gave me following details:
Connectivity.Application.VaultManager.ServiceException: IllegalPath [139] (\\KEVINVMWARE25\SQLShare) ---> Connectivity.Core.Exceptions.CoreException: IllegalPath [139] (\\KEVINVMWARE25\SQLShare)

To solve this I needed to create the AutodeskVault windows user ( with the default password Au1oDSK@26200 ) on the remote SQL machine and give full permissions on the SQL Share folder in the sahring and security tab.
I also needed to make sure that the networkservice has full access on the share as well.

After this the backup finished successfully.

Vault Server 2014 Components installed

Operating System Windows Server 2012 Standard (x64)

• Microsoft SQL Server 2008 (64-bit) (If no version of SQL is pre-installed, SQL 2008 Server (SP3) build 10.0.5500 Express 64-bit, is installed)
• Microsoft SQL Server 2008 Browser
• Microsoft SQL Server 2008 Native Client
• Microsoft SQL Server 2008 Setup Support Files
• Microsoft SQL Server VSS Writer
• Microsoft Visual c++ 2010 x64 redistributable - 10.0.40219
• Microsoft Visual c++ 2010 x86 redistributable - 10.0.40219
• Microsoft WSE 3.0 Runtime
• Autodesk Vault Professional 2014 (Server)
• IIS 8.0 Components

WebServer (IIS)

Web Server

Common HTTP Feature

Default Document

        Directory Browsing

        HTTP Errors

        Static Errors

Health and Diagnostics

HTTP Logging

        Performance

Static Content Compression

Security

Request Filtering

Windows Authentication

        Application Development

.NET Extensibility 4.5

ASP.NET 4.5

ISAPI Extensions

ISAPI Filters

Management Tools

IIS Management Console

IIS 6 Management Compatibility

        IIS 6 Metabase Compatibility

If you need to uninstall the above and reinstall, do so in this order

1. Autodesk Vault Professional 2014 (Server)
2. Microsoft SQL Server 2008 (64-bit)
   1. Database Engine Services
   2. SQL server Replication
   3. SQL Client Connectivity SDK
3. Microsoft SQL Server 2008 Native Client
4. Microsoft Visual c++ 2010 x64 redistributable - 10.0.40219
5. Microsoft Visual c++ 2010 x86 redistributable - 10.0.40219
6. Microsoft WSE 3.0 Runtime
7. Web Server (IIS)
   1. .NET Framework 3.5 Features
   2. .NET Framework 4.5 Features
8. Restart\recycle the machine

The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON.

When trying to "Add Workgroup", after entering the Replication Credentials, appeared this message in the User Interface.

The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON.

 

In the ADMS Console running from the Publising Workgroup, was this entry.

2/02/2013 2:50:22 AM Connectivity.Application.VaultManager.ServiceException: The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON. ---> System.Data.SqlClient.SqlException: The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON.
   at Connectivity.Core.Database.TransactionContext.OnSqlException(SqlException e)
   at Connectivity.Core.Database.SqlAccess.ExecuteNonQueryInternal(SqlCommand cmd)
   at Connectivity.Core.Database.SqlAccess.ExecuteNonQuery(CommandType commandType, String commandText, Int32 commandTimeout, SqlParameter[] commandParameters)
   at Connectivity.Core.DataAccess.DatabaseSecurity.CreateDatabaseLogin(DatabaseUser databaseuser)
   at Connectivity.Core.BusinessLogic.WorkgroupBL.Create(String sqlServer, String description, String sauser, String saPassword, String agentUser, String agentPassword, String Label)
   at System.Runtime.Remoting.Messaging.Message.Dispatch(Object target, Boolean fExecuteInContext)
   at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)
   --- End of inner exception stack trace ---
   at Connectivity.Application.VaultManager.AddWorkgroupCommand.AddWorkgroup(String remoteServerName, String remoteWorkgroupName, String remoteSQLServerUser, String remoteSQLServerPassword, String remoteWindowsUser, String remoteWindowsPassword, Boolean useDefaultPassword, String workgroupLabel)
   at Connectivity.Application.VaultManager.AddWorkgroupCommand.AddWorkgroup(AddWorkgroupForm form)

 

This happened because the VaultSys account on the Subscriber  was not correctly configured and suffered from a Microsoft UI bug.

On the Subscriber it was necessary to run this Query, afterwhich the problem went away.

ALTER LOGIN VaultSys WITH PASSWORD='%DefaultVaultSyspwd%'

Cannot drop the database 'databasename' because it is being used for replication

Problem.

 

Trying to delete a database from the ADMS Console resulted in this error.

 12/01/2013 3:22:58 PM Connectivity.Application.VaultManager.ServiceException: Cannot drop the database 'TEST' because it is being used for replication. ---> System.Data.SqlClient.SqlException: Cannot drop the database 'TEST' because it is being used for replication.
   at Connectivity.Core.Database.TransactionContext.OnSqlException(SqlException e)
   at Connectivity.Core.Database.SqlAccess.ExecuteNonQueryInternal(SqlCommand cmd)
   at Connectivity.Core.Database.SqlAccess.ExecuteNonQuery(CommandType commandType, String commandText, Int32 commandTimeout, SqlParameter[] commandParameters)
   at Connectivity.Core.DataAccess.KnowledgeMasters.DropDatabase(String databaseName)
   at Connectivity.Core.Operations.DeleteBase.Execute()
   at Connectivity.Core.Operations.DeleteVault.Execute()
   at Connectivity.Core.Services.KnowledgeVaultService.DeleteKnowledgeVault(Int64 vaultId, OnProgressDelegate onProgress)
   at System.Runtime.Remoting.Messaging.Message.Dispatch(Object target, Boolean fExecuteInContext)
   at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)
   --- End of inner exception stack trace ---
   at Connectivity.Application.VaultManager.ProgressCommand.Execute()
   at Connectivity.Application.VaultManager.DeleteVaultCommand.OnExecute(ExplorerCommandContext context)

 

Solution 

USE [database]

EXEC sp_removedbreplication @dbname=[database]

GO

Vault Server SP1 installation error.

I just had a rather odd Installation error whilst trying to apply "Autodesk Vault 2013 Service Pack 1 (Server).msp" on my laptop.

I neglected to take a screenshot of the error (my bad), however a MSI log file in the %TEMP% folder had the error I got on the screen. It contained simply....

IIS is not running as the default web server
=== Logging stopped: 13/12/2012  21:28:37 ===

The message sent me off in the completely wrong direction trying to check settings in the registry, searching the web for similar issues, none of which helped.

No VP Client could connect.

I started the ADMS Console and saw this error.

 

13/12/2012 21:32:45 *******************************************************************
Error: The connection to the database has been broken. Reattempting operation.
Exception: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)
Stacktrace:    at Connectivity.Core.Database.TransactionContext.OnSqlException(SqlException e)
   at Connectivity.Core.Database.TransactionContext..ctor(TransactionDatabase database, DatabaseUser databaseUser, Boolean transact)
   at Connectivity.Core.Database.TransactionBase.SetupTransactionContext(TransactionReqs transactionReqs, TransactionDatabase database, Nullable`1 databaseUser)
   at Connectivity.Core.DataAccess.LegacySystems.DetermineKVMName()
   at Connectivity.Core.DataAccess.LegacySystems.LegacyTransaction..ctor(TransactionReqs transactionReqs)
   at Connectivity.Core.DataAccess.SiteCompatibility.GetSiteCompatibilityWithKnowledgeMaster()
   at System.Runtime.Remoting.Messaging.Message.Dispatch(Object target, Boolean fExecuteInContext)
   at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)

13/12/2012 21:34:49 Unable to check the Autodesk data management server system state.  After closing this message box, you can press F5 to try again. 
If the error condition continues, contact your IT administrator.
__________________________

Operation failed:  System error.  Please contact your system administrator.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)
13/12/2012 21:37:30 Operation failed:  System error.  Please contact your system administrator.
Connectivity.Application.VaultManager.ServiceException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) ---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)
   at Connectivity.Core.Database.TransactionContext.OnSqlException(SqlException e)
   at Connectivity.Core.Database.TransactionContext..ctor(TransactionDatabase database, DatabaseUser databaseUser, Boolean transact)
   at Connectivity.Core.Database.TransactionBase.SetupTransactionContext(TransactionReqs transactionReqs, TransactionDatabase database, Nullable`1 databaseUser)
   at Connectivity.Core.DataAccess.LegacySystems.DetermineKVMName()
   at Connectivity.Core.DataAccess.LegacySystems.LegacyTransaction..ctor(TransactionReqs transactionReqs)
   at Connectivity.Core.DataAccess.SiteCompatibility.GetSiteCompatibilityWithKnowledgeMaster()
   at System.Runtime.Remoting.Messaging.Message.Dispatch(Object target, Boolean fExecuteInContext)
   at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)
   --- End of inner exception stack trace ---
   at Connectivity.Application.VaultManager.KnowledgeMasterService.GetSiteCompatibilityWithKnowledgeMaster()
   at Connectivity.Application.VaultManager.ConfigUtil.InitializeSystemState()

SOLUTION

For some reason the AUTODESKVAULT SQL Service had not started since bootup!

Nothing to do with IIS or the web service. After starting SQL in the Services (and losing 10 minutes of my life that I will never get back again), Vault 2013 Update 1 was applied successfully.

 

 

The database principal owns a database role and cannot be dropped

Trying to attach a Vault database to the ADMS Console returned this error.

27/11/2012 09:15:51 Connectivity.Application.VaultManager.ServiceException: The database principal owns a database role and cannot be dropped. ---> System.Data.SqlClient.SqlException: The database principal owns a database role and cannot be dropped. at Connectivity.Core.Database.TransactionContext.OnSqlException(SqlException e) at Connectivity.Core.Database.SqlAccess.ExecuteNonQueryInternal(SqlCommand cmd) at Connectivity.Core.Database.SqlAccess.ExecuteNonQuery(CommandType commandType, String commandText, Int32 commandTimeout, SqlParameter[] commandParameters) at Connectivity.Core.DataAccess.DatabaseSecurity.ClearDatabaseUsers(String dbName) at Connectivity.Core.DataAccess.KnowledgeMasters.AttachDatabase(String dbName, String datafile, String logfile) at Connectivity.Core.Operations.Attach.Execute(Boolean markForMigration) at Connectivity.Core.Services.KnowledgeVaultService.AttachKnowledgeVault(String name, String datafile, String logfile, String filestore, Boolean markForMigration) at System.Runtime.Remoting.Messaging.Message.Dispatch(Object target, Boolean fExecuteInContext) at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext) --- End of inner exception stack trace --- at Connectivity.Application.VaultManager.ProgressCommand.Execute() at Connectivity.Application.VaultManager.AttachVaultCommand.OnExecute(ExplorerCommandContext context)

This happened because the Database Roles had been edited by the DBA which was not supported by Autodesk.

The solution was to attach the mdf and ldf in SQL Server Management Studio.

In the properties of the Database, expand Security>Roles>Database Roles. Right click on ADMS-VacuumRestriction>Properties. On the General page, change the Owner to "dbo".

Detach the database from SQL Server Managment Studio then, re-attach via the ADMS Console.. (and don't do it again.....)

 

"The referenced vault does not exist"

Trying to disable replication on a database you may see this error:-

This error occurred because the same database was disabled on the publisher. It should not be possible to disable a database on the publisher if it is enabled on a subscriber. So Autodesk is intending to address this in the next release of Vault.

The workaround is to enable the database on the Publisher before disabling replication on the Subscriber. Credit goes to Johannes Bauer for working around the issue and sharing so quickly.

 

 

Replication Error

When trying to enable replication of a database to a subscriber I got this error.

"The CREATE DATABASE statement failed. The primary file must be at least xxx MB to accomodate a copy of the model database."

This happened because I had changed the values of the Model database on the subscriber (I was playing in the interest of my own education). The Model database is a template database. All databases should contain a base set of objects and when a new database is created properties are copied from the Model database to the new one.

I had set the Initial size of the Model DB to 500 MB and when the newly replicated DB was created on the subscriber, it was smaller that 500MB, hence the error.

To get past this error I just set the Model DB back to the default of 2MB and tried again and it worked. I lived and I leant and I shared :-).