Cracking the Vault

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

Invalid object name 'dbo.sysmergesubscriptions'

Trying to Disable Workgroup Replication returned this error in the ADMS Console Log

17/12/2012 9:43:09 AM Connectivity.Application.VaultManager.ServiceException: Invalid object name 'dbo.sysmergesubscriptions'. ---> System.Data.SqlClient.SqlException: Invalid object name 'dbo.sysmergesubscriptions'.
   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.BusinessLogic.WorkgroupBL.Database.RemovePublisher(String sqlPublisher)
   at Connectivity.Core.BusinessLogic.WorkgroupBL.DisableReplication()
   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.DisableWorkgroupReplication(String dbUser, String dbPass)
   at Connectivity.Application.VaultManager.ReplicationUtil.DisableWorkgroupReplication_DoWork(Object sender, DoWorkEventArgs e)

Running this SQL Query indicated that even after disabling replication to subscribers, they were marked as being still replicated.

SELECT DISTINCT name, category
     FROM   master.dbo.sysdatabases
     WHERE ((category & 4) = 4)
   and databasepropertyex(name, 'Updateability') = 'READ_WRITE'
        and has_dbaccess(name) = 1

I don't know the circumstances that led to this behaviour, but to Workaround this I ran this Script against all the databases to overcome it. WARNING!!! DO THIS ONLY IF YOU ARE CERTAIN THAT THESE DATABASES SHOULD NOT BE REPLICATED TO ANY SUBSCRIBER.

Use <Vault>
sp_removedbreplication

"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 :-).

Useful Performance Counters

These counters are useful if you need to analyse performance bottlenecks in your Vault Environment.

SQL
LogicalDisk(D:)\Avg. Disk Queue Length
Memory\Available KBytes
Memory\Page Faults/sec
MSSQL$AUTODESKVAULT:Buffer Manager\Buffer cache hit ratio
MSSQL$AUTODESKVAULT:Databases(_Total)\Transactions/sec
MSSQL$AUTODESKVAULT:Databases(tempdb)\Transactions/sec
MSSQL$AUTODESKVAULT:General Statistics\User Connections
MSSQL$AUTODESKVAULT:Locks(_Total)\Average Wait Time (ms)
MSSQL$AUTODESKVAULT:Locks(_Total)\Lock Requests/sec
MSSQL$AUTODESKVAULT:Locks(_Total)\Lock Wait Time (ms)
MSSQL$AUTODESKVAULT:Locks\Lock Waits/sec
MSSQL$AUTODESKVAULT:Memory Manager\SQL Cache Memory (KB)
MSSQL$AUTODESKVAULT:Memory Manager\Target Server Memory (KB)
MSSQL$AUTODESKVAULT:Memory Manager\Total Server Memory (KB)
MSSQL$AUTODESKVAULT:SQL Statistics\Batch Requests/sec
MSSQL$AUTODESKVAULT:SQL Statistics\SQL Compilations/sec
PhysicalDisk(_Total)\Disk Reads/sec
PhysicalDisk(_Total)\Disk Writes/sec
PhysicalDisk(0 D:)\Avg. Disk Queue Length
Process(_Total)\% Processor Time
Process(sqlservr)\% Processor Time
Processor(*)\% Processor Time
System\Processor Queue Length

IIS\ADMS
W3SVC_W3WP(#####_DefaultAppPool)\Requests / Sec
W3SVC_W3WP(#####_DefaultAppPool)\Active Requests
W3SVC_W3WP(#####_DefaultAppPool)\Total Threads
W3SVC_W3WP(#####_DefaultAppPool)\Active Threads Count
W3SVC_W3WP(#####_DefaultAppPool)\Maximum Threads Count
W3SVC_W3WP(#####_DefaultAppPool)\Current File Cache Memory Usage
W3SVC_W3WP(#####_DefaultAppPool)\Maximum File Cache Memory Usage
W3SVC_W3WP(#####_DefaultAppPool)\Output Cache Current Memory Usage
W3SVC_W3WP(#####_DefaultAppPool)\Current Files Cached
W3SVC_W3WP(#####_DefaultAppPool)\Current Metadata Cached
W3SVC_W3WP(#####_DefaultAppPool)\Total Metadata Cached

CPU
Processor(_Total)\% Interrupt Time
Processor(_Total)\% Privileged Time
Processor(_Total)\% Processor Time
Processor(_Total)\% User Time
System\Context Switches/sec
System\System Calls/sec

Hard Disk
PhysicalDisk(_Total)\% Disk Time
PhysicalDisk(_Total)\% Disk Read Time
PhysicalDisk(_Total)\% Disk Write Time
PhysicalDisk(_Total)\Current Disk Queue Length
PhysicalDisk(_Total)\Disk Reads/sec
PhysicalDisk(_Total)\Disk Writes/sec
PhysicalDisk(_Total)\Avg. Disk Bytes/Read
PhysicalDisk(_Total)\Avg. Disk Bytes/Write

Memory
Memory\Available Bytes
Memory\Cache Faults/sec
Memory\Demand Zero Faults/sec
Memory\Pages/sec
Memory\Transition Faults/sec
Process(inetinfo)\% Processor Time
Process(w3wp)\% Processor Time
Process(w3wp)\Handle Count
Process(w3wp)\ID Process
Process(w3wp)\Private Bytes
Process(w3wp)\Thread Count
Process(w3wp)\Virtual Bytes
Process(w3wp)\Working Set

Network
Network Interface(Name of Network Interface card)\Bytes Total/sec
Network Interface(Name of Network Interface card)\Current Bandwidth

Unable to clean up replication database

Trying to disable replication from the Publisher ADMS Console on a Standard Content Center Library, showed this error in the ADMSConsole log file.

11/13/2012 2:22:52 PM Unable to clean up replication database 'AI2013_Inventor ISO' on subscribing database server 'RR-W2K8R2-SUB\AUTODESKVAULT'.
11/13/2012 2:22:52 PM Connectivity.Application.VaultManager.ServiceException: SubscriberCleanupError [4415] (RR-W2K8R2-SUB\AUTODESKVAULT, AI2013_Inventor ISO) ---> Connectivity.Core.Exceptions.EncodableException: SubscriberCleanupError [4415] (RR-W2K8R2-SUB\AUTODESKVAULT, AI2013_Inventor ISO) ---> System.Data.SqlClient.SqlException: Changes to the state or options of database 'AI2013_Inventor ISO' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
ALTER DATABASE statement failed.
   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.SetDatabaseOptions(String databaseName, String[] options)
   at Connectivity.Core.DataAccess.KnowledgeMasters.DropDatabase(String databaseName)
   at Connectivity.Core.BusinessLogic.WorkgroupBL.Database.CleanupRemoteSubscription(Workgroup publisher, String publicationName, String databaseName)
   at Connectivity.Core.BusinessLogic.WorkgroupBL.Database.Unsubscribe(Workgroup currentwg, Workgroup targetwg, String databaseName, Boolean ignoreSubscriberErrors)
   --- End of inner exception stack trace ---

Server stack trace:
   at Connectivity.Core.BusinessLogic.WorkgroupBL.Database.Unsubscribe(Workgroup currentwg, Workgroup targetwg, String databaseName, Boolean ignoreSubscriberErrors)
   at Connectivity.Core.BusinessLogic.WorkgroupBL.KnowledgeDB.DeleteReplica(String sqlServer, KnowledgeDatabase database, Boolean ignoreSubscriberErrors)
   at Connectivity.Core.Services.KnowledgeLibraryService.DeleteLibraryReplica(String sqlServer, Guid libraryGuid, String partionID, Boolean ignoreSubscriberErrors)
   at System.Runtime.Remoting.Messaging.Message.Dispatch(Object target, Boolean fExecuteInContext)
   at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)

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.Core.Services.KnowledgeLibraryService.DeleteLibraryReplica(String sqlServer, Guid libraryGuid, String partionID, Boolean ignoreSubscriberErrors)
   at Connectivity.Application.VaultManager.KnowledgeLibraryService.DeleteLibraryReplica(String sqlServer, Guid libraryGuid, String partitionID, Boolean ignoreSubscriberErrors)
   --- End of inner exception stack trace ---
   at Connectivity.Application.VaultManager.KnowledgeLibraryService.DeleteLibraryReplica(String sqlServer, Guid libraryGuid, String partitionID, Boolean ignoreSubscriberErrors)
   at Connectivity.Application.VaultManager.ReplicationUtil.DeleteLibraryReplica(String remoteServerName, String workgroupDisplayName, Guid libraryGuid, String libraryPartition, Boolean ignoreSubscriberErrors, String dbUser, String dbPassword, ICommonProgressReporter progressReporter)
   at Connectivity.Application.VaultManager.KnowledgeLibraryObject.DeleteReplica(String remoteServerName, String workgroupDisplayName, ICommonProgressReporter progressReporter)
   at Connectivity.Application.VaultManager.ManageReplicationCommand.ApplyChange(ICommonProgressReporter progressReporter, Form threadSafeForm, ReplicationUpdate currentUpdate)
   at Connectivity.Application.VaultManager.ManageReplicationCommand.WorkgroupReplication_DoWork(Object sender, DoWorkEventArgs e)

 

I am not able to reproduce how this happened in the first place.

Logging into SQL Server Management Studio (SSMS) on the subscriber showed that the database had been put into Single User mode. To workaround this, I detached the database from the subscriber SSMS restarted the following services

Net stop "Autodesk Data Management Job Dispatch"
Net stop "SQLAgent$AUTODESKVAULT"
Net stop "MSSQL$AUTODESKVAULT"
Net stop "W3SVC"
Net start "Autodesk Data Management Job Dispatch"
Net start "MSSQL$AUTODESKVAULT"
Net start "SQLAgent$AUTODESKVAULT"
Net start "W3SVC"

...and then went back to the Publisher ADMS Console and tried again. The second time it was successful.

Replication - Restore To The Same Computer Names

No doubt, all readers of this blog will be aware of the Backup and Restore Autodesk Vault document, that should be used to back up a replicated environment. Of particular interest is the line...

“Restore Process

A restore should be perform on a computer with the same name as the original computer.”

 

This has caveat has raised some interest from customers wishing to validate their backups in a test system. However, this restriction is enforced by Microsoft SQL Server and can be referenced to this statement.

"Replication supports restoring replicated databases to the same server and database from which the backup was created. If you restore a backup of a replicated database to another server or database, replication settings cannot be preserved. In this case, you must recreate all publications and subscriptions after backups are restored." Source: MS Article 151152

Connected Workgroups - Data Non-Covergence

Referring to TS20200238

"File and File Properties are missing or differ between Publisher and one or more Subscriber Workgroups."

Problem

In a Connected Workgroup environment using SQL Replication, you notice that there are differences in files and file properties between a Publishing and one or more Subscribing Workgroups.

Solution

To avoid this from occurring, Autodesk strongly recommends that you follow the guidance offered in Microsoft Article ID 2591692 “Non-convergence or data loss if a query times out during merge replication in SQL Server 2008 or in SQL Server 2008 R2”. All customers using Connected Workgroup SQL Replication should consider installing this Microsoft update.

 

WARNING. Before any major update in a production replicated environment ensure that you have backed up your publisher and any subscribing databases. Follow the instructions from the “Backup_and_Restore_Autodesk_Vault.pdf”. Refer to the sections “Backing up a Full Replication Publisher”, “Backup Process for the Publisher” and “Backup Process for the Subscriber”

 

How Do I Open the Firewall Ports For SQL Server on Windows Server 2008

I found MS Knowledgebase Article ID 968872 useful in automating opening up the SQL Ports when configuring Connected Workgroups. It saved me a lot of mouse clicks! :-)

 

@echo =========  SQL Server Ports  ===================
@echo Enabling SQLServer default instance port 1433
netsh firewall set portopening TCP 1433 "SQLServer" 
@echo Enabling Dedicated Admin Connection port 1434
netsh firewall set portopening TCP 1434 "SQL Admin Connection" 
@echo Enabling conventional SQL Server Service Broker port 4022  
netsh firewall set portopening TCP 4022 "SQL Service Broker" 
@echo Enabling Transact-SQL Debugger/RPC port 135 
netsh firewall set portopening TCP 135 "SQL Debugger/RPC" 
@echo =========  Analysis Services Ports  ==============
@echo Enabling SSAS Default Instance port 2383
netsh firewall set portopening TCP 2383 "Analysis Services" 
@echo Enabling SQL Server Browser Service port 2382
netsh firewall set portopening TCP 2382 "SQL Browser" 
@echo =========  Misc Applications  ==============
@echo Enabling HTTP port 80 
netsh firewall set portopening TCP 80 "HTTP" 
@echo Enabling SSL port 443
netsh firewall set portopening TCP 443 "SSL" 
@echo Enabling port for SQL Server Browser Service's 'Browse' Button
netsh firewall set portopening UDP 1434 "SQL Browser" 
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE