VSys One: Volunteer Management Software

Previous Topic

Next Topic

Book Contents

Book Index

SQL Server

Edition compatibility

SQL Server Express, Standard and Enterprise; 2012 and 2014, 2016 and 2017; virtualized or not; VSys One will work with all of these variants. VSys does not require its own SQL Server installation or even its own instance and will work correctly with other applications on a common server and/or instance.

In general the database working set will be under 100MB of data, and the full database under 5GB. High availability is generally not required.

VSys will happily run on a virtualized copy of SQL Server, and the VSys client can also be used in a Citrix or Terminal Services environment.

Hardware Requirements

Permissions

In order to use VSys One, users must have rights to the underlying SQL Server database. This can be done in one of three ways:

Every user needs the same basic sets of permissions to the database: VSys does not use SQL Server's permissions to limit what actions individuals can perform. Instead VSys does that authorization internally due to the complex nature of table and data interactions.

All VSys One users must have at least SELECT, INSERT, DELETE and UPDATE rights on all tables in the database. CREATE and ALTER rights may be needed occasionally when upgrading versions, as VSys occasionally makes changes to its database schema. DROP rights are only ever needed for data restores when tables already exist or for special schema updates in which one or more tables need to be built from scratch and then repopulated.

Best practice: give users db_ddladmin plus db_datareader and db_datawriter rights on their database.

When using VSys Live, VSys Live Kiosk and VSys Anywhere, database permissions are given to the VOXI service that underlies these tools. Individual users then access VSys via a web browser and do not themselves get permissions to the SQL Server database.

Setup

When creating a SQL Server database to be used by VSys, you have several options.

Option

Preferred value

Additional information

Collation

Latin1_General_BIN

VSys will set this itself on individual indices as they're created.

Recovery model

Simple

Other models are valid but will use unnecessary amounts of disk space and are not helpful to VSys.

Compatibility level

Any value is valid here.

 

Autogrowth

Enable Autogrowth and Unrestricted File Growth should be selected.

 

Advanced notes

For VSys One to work correctly, SQL Server requires that the VSys One tables be owned by "dbo", not the person who created them. You can see table ownership within SQL Server Enterprise Manager – just open up the database where your VSys One data is stored, then open up Tables. The VSys One installer and VSys One itself will automatically make all new tables owned by "dbo".

VSys One requires that certain columns use case-sensitive collation orders. When VSys One creates tables, it will specify the collation order for these columns as Latin1_General_BIN. Don't change this: VSys One will fail miserably but silently on many lookups if you do.

SQL Server 2008

When connecting to a SQL Server 2008 database, it's important that the Server address value is the server's canonical name, e.g. "SS2008A" rather than just its IP address. When there are multiple instances of SQL Server running, Server address must be the server's canonical name + backslash + the instance name, e.g. "SS2008A\VSysInstance".

Index collation issues

Under certain upgrade circumstances, VSys will complain at startup about collation mismatches. If that happens, from the database selection screen, click on Tools then on Drop all SQL Server indices. This will force VSys One to drop all indices on all of the VSys tables in its database (it won't attempt to touch any other database nor any tables it doesn't recognize within its own database). Once that's done, click on Connect to have VSys connect to the database normally. From there it will prompt you about re-creating the indices that VSys needs.

Services

If the Standalone E-mail Robot or VOXI are installed as services, and you're using domain authentication to connect to SQL Server, you may find that these tools run just fine when started as desktop applications but not when started as services. If that happens, the account under which they run as a service probably does not have the appropriate rights to the VSys One database on SQL Server. (When you run an application from the desktop it's run using your login credentials; when it's run as a service it's run as a special Windows user.)

See Also

Databases

Connecting VSys One to a Database

Upgrading VSys One