Microsoft SQL Server

The following steps are necessary to set up a new Vertec database on a SQL server.

• On the SQL server, create a blank database named Vertec. You can also use a different name.
• On the SQL server, create a new login named VertecUser under Security > Logins.
• Switch to SQL Server authentication, assign a password and remember it. You will need this password when installing Vertec Server. Note: You can leave the default database on master in this dialog.
• Enter the VertecUser as owner of the new Vertec database:

• In the database Vertec > Security > Users under Schemas owned, check the db_owner.

• Open the properties dialog with the right mouse button on the Vertec database. Under Permissions, activate the point Tabellen erstellen (Create Table) for VertecUser.

• Under Optionen, activate the point Statistiken automatisch aktualisieren:

Technically this is called AUTO_UPDATE_STATISTICS and can also be set via T-SQL ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS ON;.

• Install Vertec as a server according to the article Reinstalling Vertec.
• Please note the notes on handling transaction logs on MS SQL servers.

We recommend to recalculate the index statistics regularly (e.g. over the weekend). Either with a maintenance plan directly on MS SQL Server (depending on the version used and described in the corresponding Microsoft documentation), or with the scheduled task available by default from Vertec 6.8, see Recalculation of index statistics.

Supported Drivers (Provider)

The following drivers (connection modules) are supported for data access to the MS SQL server and the interaction between Vertec and the MS SQL server:

• OLE DB Provider for SQL Server (SQLOLEDB)
• SQL Server Native Client (SQLNCLI11)
• Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL)

The provider is specified in the [Database] Section of the Vertec.ini file.

For the conversion of a Vertec database, please note the following articles:

Database Convert

When loading a SQL Server database from another SQL Server installation (i.e. backup was performed on another SQL Server installation), the Vertec database user must be synchronized with the corresponding server login in order to be able to use the database.

It is best to proceed in 2 steps:

1. Check if the Vertec database user (e.g. VertecUser or whatever is used in the imported database) already exists on the server. The server-wide users can be found in the SQL Manager under Security > Logins. If there is no login with the name of the DB user, create a new SQL Server login (Caution: case sensitive). The password must also be set.
2. If a login exists on the server, the following call to sp_change_users_login can be made on the imported database (with dbo rights) in the Query Analyzer:

   sp_change_users_login "AUTO_FIX", "VertecUser"

   Use the appropriate username instead of VertecUser. This will link the user to login.

The Query Analyzer returns a corresponding message when the login is synchronized successfully.

Deadlocks

SELECT with simultaneous UPDATE can cause a deadlock in MS SQL Server. The message reads:

Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

There is a SNAPSHOT feature on MS SQL Server that causes selects to work without locks. The feature is disabled by default because it requires more resources. It can be activated by setting the option on the SQL Server  READ_COMMITTED_SNAPSHOT Then all READ_COMMITTED transactions (this is the default) are re-executed with SNAPSHOT_ISOLATION, which avoids deadlocks, but consumes significantly more resources.  

If the database server is located in a different domain, the following message appears when Vertec logs on to the database:

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

By default, both named pipes and TCP/IP protocols are disabled in SQL Server Configuration Manager. For Vertec, however, both protocols must be enabled.

Make sure that the Named Pipes and TCP/IP protocol are Enabled.

Furthermore, the SQL Server port must be entered in the TCP/IP Properties under IP Addresses in all TCP/IP lines (default: 1433).

Then restart the SQL Service.

If you get the above error message when starting Vertec and you get the error message:

Login failed for user 'VertecUser’. (Microsoft SQL Server, Error 18456)

VertecUser cannot log in directly to the SQL server, the reason is probably that in the server properties of the corresponding server instance under security only the Windows Authentication Mode is enabled and not the SQL Server and Windows Authentication Mode. This setting is incorrect for Vertec and is the default setting when reinstalling a SQL server.

Turn on the SQL Server and Windows Authentication Mode and restart the SQL Service.

This error message appears if the SQL Server login has been configured to expire the password.

In SecurityLoginsVertecUser, Context menu Properties, remove Enforce password expiration.