Every operation in the database server requires logging in to SQL Server. To make it easier to work with multiple databases, a number of database server instances may be registered. I.e. the administration module "remembers" which database servers it is set up to work on.

This is something that only needs to be done once after installation of a new web home (framework instance) - and of course maintained if new database servers are introduced.

The list of registered server is stored in the XML file App_Data/DatabaseServers.xml. The framework comes with an example file App_Data/DatabaseServers-example.xml, that may be copied to App_Data/DatabaseServers.xml to establish a starting points point. Contents of App_Data/DatabaseServers-example.xml:

<?xml version="1.0" encoding="utf-8" ?>
<DatabaseServers>
  <DatabaseServer Name=".\SQLEXPRESS"  Description="Local Express server" AdminLoginPassword="adm" AppLoginPassword="app" WindowsAuthentification="true" SQLServerLogin="true"/>
  <DatabaseServer Name="RemoteServer" Description="Remote / shared server for development and test" WindowsAuthentification="false" SQLServerLogin="true" />
</DatabaseServers>

Note
The attribute WindowsAuthentification should have ben WindowsAuthentication (spelling error) - will be changed in a later version! Attributes WindowsAuthentification and SQLServerLogin are only used in version 4.29 and newer!

This example registers 2 SQL Server instances (one local and one remote / shared). Note that on the local server, default values for the login passwords are specified - this saves the user from entering these values on each database creation.

How to edit the list of registered database servers:

• Version 4.28 and newer:

  • Version 4.28 and newer contain a web user interface for registering database servers. In the "configuration list" form, click the button "Server registration".

    Because server registration is performed through a user interface, the user does not need to know / understand the format of the XML file.

• Version 4.25 - 4.27:

  • Manually edit the XML file App_Data/DatabaseServers.xml using any plain text or XML editor application.

• Version 4.24 and earlier:

  • Version 4.24 and older do NOT contain the Administration Module.

    To administer configurations, use Windows Explorer to create/modify/delete configuration folders (requires access to the web server's file system).

    To administer databases, refer to Database Management using Scripts.

There is one important difference between using this user interface and running scripts from a command prompt:

• When running scripts from a command prompt, these scripts run as the logged in (interactive) user.

• When using the web user interface, the scripts run as the user that is defined on the application pool (in Internet Information Server) - e.g. "IIS APPPOOL\ASP.NET v4.0" (default user for web homes).

This has security implications:

• In a typical setup, a logged in (interactive) domain user may connect to both local and remote (shared) servers using Windows Authentication. This is commonly achieved by adding the user to the "local administrators" groups on either machine.

  A typical application pool user like "IIS APPPOOL\ASP.NET v4.0" is NOT member of the "local administrators" group on the local machine, and CANNOT be member of the "local administrators" group on the remote server (a local user cannot be used to connect to a remote machine).

  Workarounds:

• Local servers:

  • Create a login (in SQL Server Management Studio) connected to the application pool user.

    Tip
    In SQL Server Management Studio, there is a button "Search..." that is normally used to browse for Windows users. This does not work with special users like "IIS APPPOOL\ASP.NET v4.0". But it IS possible to copy/paste the full user name into the user name field.

• Remote servers:

  • If the remote database server is in the same (or trusted) domain as the local machine (= the web server), you may specify a domain user on the application pool, and create a corresponding login in SQL Server.

  • Use SQL Server login: Use an existing login (e.g. "sa"), or create a new SQL Server login for this purpose.

    SQL Server login may be used irrespective of whether the remote database server is in the same (or trusted) domain as the local machine or not.

• In any case, the login used to connect to the database server MUST have permission to create / drop databases - this may be accomplished by setting the "sysadmin" server role on the login.

• With the default setup of the web home, lauching the web home displays a menu listing all (active) configurations. In the top frame there is a link "Configurations". Clicking this link will launch the Administration module (in a new browser window or new tab, depending on browser settings).

• If the web home is set up to launch directly to the login form of one specific application, the browser address line will contain something like "localhost/TOPICA/LoginForm.aspx?config=myconfig". Go to the address line and delete everything from "/LoginForm.apx" and the rest of the line - leaving "localhost/TOPICA", and hit Return. This will display the default menu as described above.

This form displays the configurations in the current framework instance ("web home") in a grid. All configurations are listed, irrespective of whether they have been connected to a database or not. This grid contains the following information for each configuration:

• configuration name

• configuration culture

• database server

• database name

• data model version

• database culture

• connect status (OK if connection to database is working, ERROR otherwise)

• culture status (OK if configuration and dtabse culture match, ERROR otherwise)

• Link "Description", if the file Documents/Description.htm exists in the configuration. Clicking this link will open Documents/Description.htm in a new browser window.

  Note
  Column/link "Description" is available in version 4.30 and later only.

The configuration name is a hyperlink. Clicking this hyperlink opens the "Configuration properties" form (described below).

At the bottom of the configuration list form, you will find these buttons:

• "Create new configuration".

• "Create new database"

• "Build database objects"

• "Server registration"

In many cases the configuration exists beforehand. This is the case for the demo configurations distributed with the framework, or when a new environment for an existing application is to be set up.

When the configuration does not exist, the user may choose to build the configuration first and then the database - or the database first and then the configuration. Se chapter "Create configuration or database first?" above.

To set up a TOPICA application, both a configuration and a database is needed. The Administration Module does not impose any order of creation of these items - it gives the user a choice:

• First create a configuration, then create a database for that configuration.

• First create a database, then create a configuration for that database.

In many cases a configuration will already exist. This is for example the case with the demo configurations, that come with the framework - and also when a configuration has been developed in one environment, and is about to be set up in another environment.

Irrespective of the order of creation, the Administration Module uses the proper defaults (e.g. to make sure that the cultures match). The Administration Module also automatically updates the Server.config file, that holds the reference to the database (the "connection string").

This form displays basic information on the selected configuration.

If a database has been set up for this configuration, the form tests the connection to this database. If the connection is OK, the header contains a hyperlink "Log in". Click this to log in to the application.

At the bottom, the form contains the following buttons:

• "Create database": Creates a new database, and changes the current configuration to refer to this new database. Any database the configuration referred to before is left untouched.

• "Connect to existing database": Prompts the user for server and database names. Updates the configuration to refer to the selected database. Any database the configuration referred to before is left untouched.

• "Drop database": Drops the database the configuration refers to.

  Tip

  The database is per default in "multi user mode". An error occurs when trying to drop a database that is in this mode, from an application, that uses the database (as is the case with the Administration Module). Check the checkbox next to "In use" to set the database in "single user mode" - this makes it possibly delete a database that is in use. USE WITH CARE! Especially on remote / shared servers, make sure not to drop a database used by other configurations - or containing data that should be kept!

  The user may specify what will happen after database has been successfully dropped:

  • Leave reference to the database, that is now dropped.

  • Removing the reference to the database (e.g. removes the Database parameter from the file Server.config). This is the default.

  • Delete the configuration. USE WITH CARE!

• "Update database": This command updates the database to the next data model version. This command this only enabled when the current database's data model version number is less that the framework's version number. E.g. framework version is 4.27 and the current database's data model version is 4.24 - the command is enable, and running this command will update the database to data model version 4.25.

  Note
  In framework versions 4.24 to 4.26, this command is only enabled when the data model of the database is one minor version lower that the framework's version number. E.g. the framework can only update from previous to current version. Example: version 4.25 of the framework may update data model version 4.24 to 4.25 - but version 4.26 of the framework CANNOT update data model version 4.24 to 4.25.

• "Delete configuration". Deletes the configuration. Any database the configuration referred to is left untouched. After deletion, the user is redirected to the "configuration list" form (see above).

• "Cancel": Returns to the "configuration list" form (see above).

This form prompts the user for the necessary parameters for creating a new configuration (either a new, empty configuration, or a copy of an existing configuration). When the form is submitted, the parameters are validated, and a new configuration folder is created.

After successful creation of the configuration, the user is redirected to the "Configuration properties" form - see above. From this form it is possible to create a new database for the configuration, or connect the configuration to an existing database.

The "Administration Module" internally runs command line scripts, that are used to create databases from a command prompt. Therefore, the structure of a created database will be exactly the same, irrespective of whether it is created using the web user interface or by running database management scripts from a command prompt

The form allows entering the necessary parameters for creating a database. When the form is submitted, the parameters are validated, the framework runs the CreateTopicaCore.cmd script (as an external process), and the results are displayed for the user.

After successful creation of a database, the user has the option to generate a new configuration for this new database (either an empty configuration, or a copy of an existing configuration). The newly created configuration is automatically set up to refer to the newly created database.

• Database server

  • The FIRST database server listed in the file App_Data/DatabaseServers.xml is selected as the default server.

• Data model version

  • The default data model version when creating a new database is equal to the version of the TOPICA framework.

    Earlier data model versions may be selected, because new version of the framework is backward compatible with several older data model versions.

    Why would anyone ever build a new database with a data model version that is older that the framework version?

    • To test updating a database from one data model version to the next.

    • To create an environment that resembles an existing environment, where a newer framework version runs with an older data model version.

• Login passwords

  • For each TOPICA database created, two SQL Server logins are automatically created. The names are always <databasename>_AdminLogin and <databasename>_AppLogin. Each of these logins must be given a password.

    The file App_Data/DatabaseServers.xml may contain default values for these passwords (see above). Do not use this feature for production database servers.

• Cultures

  • Culture name is specified both in the configuration and in the database. Obviously these should match.

  • Configuration is created first:

    • Configurations are not localizable, e.g. a given configuration is created in one language. The corresponding culture should be defined in the configuration. This is done by the parameter Culture in the Application.config file.

    • When a database is created for a configuration, the default value for the culture of the new database is taken from the configuration.

  • Database is created first:

    • Database creation REQUIRES a culture name.

    • When a configuration is created for a database, the default for the culture name in the configuration is read from the database.