Cracking the Vault

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 

Improving SQL Merge Replication Performance in Connected Workgroup Environments.

In a SQL Replicated environment (Connected Workgroups) it is necessary to run regular SQL Maintenance plans on each site to keep the database healthy and optimized for performance. This should be done from one Vault Server in each workgroup. Autodesk has observed significant performance benefits specifically in the merge replication by using the ADMS Console defragmenting tool immediately prior to running a SQL maintenance plan.

The ADMS Console defragment command can be run via

A) the User Interface

B) a windows CMD line by running a batch command as follows:-

Connectivity.ADMSConsole.exe -Odefragmentvault –NVaultDB -VUAdministrator -VPadmin -S

Connectivity.ADMSConsole.exe -Odefragmentlibrary –NCustom_Library -VUAdministrator -VPadmin –S

(If you run a custom SQL Maintenance Plan you can the above lines before or after your existing defragmenting batch scripts.)

If the command is run via the User Interface or without the –S switch, the following message will appear requiring user input

“This operation cannot be performed if the vault is unlocked and available to users. Would you like to lock this vault now? Selecting ‘No’ will abort the operation.”

This message will remain on the screen until Yes or No is clicked. Similarly if you run the command whilst the ADMSConsole is open you will see this message.

Run the ADMS Console defragmentation to see how long it takes. It will vary from one time to another dependent on user activity since the previous ADMS Console defragmentation. One example may be… if it takes 1 hour to process an ADMS Console defragmentation on all databases, double it, then use that time to schedule it before running the SQL Maintenance plan.

An example could be, the following.

The Administrator responsible for running SQL Maintenance on each database, has configured a SQL Maintenance plan to run at mid-day on each Saturday. Testing has revealed that defragmenting all the Vault and Library databases takes 5 hours. Allowing for fluctuations in user activity, since the previous defragmentation, (in the near future) they choose to run the ADMSConsole defragmentation 10 hours prior to the scheduled SQL Maintenance plan.

	Defrag	SQL Maintenance
1 Vault Site Server in Workgroup1	Run Weekly on Saturday @ 0200	Run Weekly on Saturday @ midday
1 Vault Site Server in Workgroup2	Run Weekly on Saturday @ 0200	Run Weekly on Saturday @ midday
1 Vault Site Server in Workgroup3	Run Weekly on Saturday @ 0200	Run Weekly on Saturday @ midday

Note:

                The ADMS defragmentation only needs to be run from 1 Vault Site server in each Connected Workgroup.

Vault Upgrade Documentation is Now Available

Autodesk has recently released detailed documentation on how to Upgrade Autodesk Vault on the Vault Wiki.

There is also a downloadable version posted at the bottom of the above mentioned page.

#23 Do... Setup Database Mail In A Connected Workgroup Environment

Autodesk have published a Best Practice in the Vault Wiki called "Monitoring SQL Replication"

"It is very important to monitor the replication environment.  SQL Server provides replication alerts and threshold settings that can be monitored and emailed to system administrators if an alert occurs or a threshold has been passed."

Setting Up SQL Clustering With Vault Server

I recently saw a question asking how to set up SQL Clustering for the Vault Server using SQL Express.

The first point to make is that Clustering is not supported for SQL Express

(Source http://www.microsoft.com/sqlserver/en/us/product-info/compare.aspx)

Once the Cluster is configured (Using an edition of SQL that supports clustering) and the SQL AUTODESKVAULT instance has been pre-installed and configured as per the Advanced Configuration Guide, then you would install Vault Server using the Remote SQL option and point it to the clustered SQL instance.

Ultimately Vault does not know or care if the SQL Instance is clustered or not, it cannot tell the difference.

(Note, remote SQL is only supported in Vault Collaboration and Vault Professional)

Error 1920. Service 'MSSQL$AUTODESKVAULT'

When installing Vault Server, one of our partners reported this issue.

Error 1920. Service 'MSSQL$AUTODESKVAULT' (MSSQL$AUTODESKVAULT) failed to start. Verify that you have sufficient privileges to start system services.

Now, time was short for this issue as this occured at a conference, so we did not have time to fully investigate. Nor did we search the web for more information. We made sure that the User Account Control was disabled as per the ACG.We checked that the Anti Virus was disabled and that the logged in user was an administrator.

We overcame this by manually installing SQL 2008 Express R2 as per the Advanced Configuration Guide. Then installed Vault Server. Because SQL was already installed, up and running, the Vault Server install was then successful.

 

 

SQL 2008 32 bit on 64 Bit OS

Problem
You have installed Vault Server 201x on a 64 bit OS and let it automatically install SQL 2008 Express SP2 (32 bit) on a 64 bit machine.

Your end-state goal is
64 bit OS
AUTODESKVAULT running in a 64 bit SQL 2008 Standard\Express\Enterprise (R2 or not R2) SQL instance.

Microsoft does not support cross platform upgrades from 32 to 64 bit. (See "Version and Edition Upgrades”This means that if you then manually install 64 bit SQL, it will not upgrade the exisiting 32 bit AUTODESKVAULT instance to a 64 bit instance.

Solution
You will need to
1) Uninstall SQL Server 2008 (32 bit). (No need to uninstall Vault Server as well)

2) Manually install SQL Server 2008 (64 bit) (Standard, Express (R2 or not R2) as per the Advanced Configuration Guide.

3) Create and configure the VaultSys account as per the Advanced Configuration Guide

4) Create and configure the ADMS-<Computername> account.

• Open the Microsoft SQL Server Management Studio.
• Expand the Security -> Logins folder.
• Right-click on the Logins folder and select New Login.
• Type ADMS-* for the login name.(Where * indicates the NT Computer name) Example, if the computer name is MAINSERVER then the login name would = ADMS-MAINSERVER
• Select SQL Server Authentication.
• Enter the correct password. The correct password can be found web.config file here "C:\Program Files\Autodesk\ADMS Professional 2013\Server\Web\Services" on the "<siteSecurity generateUser" line.

Note: I am not allowed to publish passwords on this blog for security reasons, although I am allowed to write where to find them.

Knowledge is key!

Autodesk regularly publishes White Papers regarding frequently asked-for topics such as how something works, best practices and optimising performance. There is a wealth of topics covered on the Autodesk Vault White Paper website.

The latest addition to the Vault White Papers website covers tests carried out on Vault speed performance used in conjunction with Riverbed WAN Optimisation technologies. See "Speed collaboration using Autodesk Vault and Riverbed WAN Optimization".

"Geographically dispersed teams using 3D for clear design communication may struggle to collaborate effectively because sharing data using common Microsoft Windows file directories can take too long. Autodesk® Vault helps solve this problem by improving access to the latest designs even across global, wide area networks (WANs). When WANs exhibit high latency, the combination of Autodesk Vault and Riverbed® WAN optimization technology utilizing Steelhead®, a powerful data store and deduplication appliance, provides an effective solution to help maintain fast and efficient design data access. Tests performed by Autodesk illustrate the value of this combination—the addition of WAN optimization may dramatically reduce file transfer by up to 99% without further requirements to upgrade or replace existing software or hardware"

"This server must be the same version as the servers in the replication environment and must have the same applications."

If you install a Vault Server Update on one Server, but not another, when you start the ADMS Console on a machine that does not have it installed, you will get this sort of message.

--------------------
Autodesk Data Management Server Console 2012
--------------------
This server must be the same version as the servers in the replication environment and must have the same applications.
You must add or upgrade to the following applications:

  Vault Professional Server 16.1.58.0  (Service Pack 1)
  Vault Server 16.1.58.0  (Service Pack 1)
  Vault Collaboration Server 16.1.58.0  (Service Pack 1)
  Vault Workgroup Server 16.1.58.0  (Service Pack 1)

Service packs are available from:
  www.autodesk.com.
Applications are available from your reseller.

Ctrl+C copies this message to the clipboard
   
--------------------
OK
--------------------

Before running the ADMS Console in a Connected Workgroup, ensure that you have applied the same level of Update as all other Vault Servers. If there are any special instructions in the readme file, such as a -b2bmigrate, that command must be executed on 1 Vault Server in the Publisher Workgroup first.

 

Windows NT user or group '"SUBSCRIBER"\"SUBSCRIBER_USER-ID"' not found. Check the name again.

I encountered a problem trying to "Add Workgroup" when trying to setup SQL Replication from the publisher to the Subscriber that I thought was worth sharing. Neither machine were added to a domain, both were using LOCAL user accounts on Windows Server 2008 R2 SP1. It took me a while to solve it and the solution was not obvious, so I thought it worth sharing.

The Computer names were as follows:

• Publisher = RRSQLPUB
• Subscriber = RANKINR-SUBSCRIBER

On both machines, I had created a user account called "VAULTREPL", with the same password.

When I clicked "Add Workgroup" I entered all the details correctly, but I kept getting this message.

"Windows NT user or group 'computer\sharedfolder' not found. Check the name again"

First, I realised that in the "Remote Replication Credentials", you must enter the user account of the Subscriber, not the Publisher. So, in the above error message 'RRSQLPUB\VAULTREPL" was replaced with "RANKINR-SUBSCRIBER". Also, both the impersonating user (AutodeskVault by default) and the "SQL Server (AUTODESKVAULT)" service user (often "Network Service") need full control on this folder.

But I kept getting the same message, different computer name.

I found that renaming the server to contain less than 15 Characters resolved the issue! This meant:-

1. Uninstalling SQL (2008 R2 RTM) on the Subscriber,
2. rename the machine,
3. restart
4. reinstall SQL (2008 R2 RTM)

As I write this, after I had resolved it, I found this article from Microsoft....http://msdn.microsoft.com/en-us/library/aa952588.aspx...go figure. As a best practice, I'd recommend avoid using computer names exceeding 15 characters.

Comment on this blog if you experience a similar issue, but found a different workaround...