Configuring the site database
An SQL database is only needed when using the reporting feature. FotoWeb uses a SQL database for storing activity logs. The topic deals with how to configure a working connection to the database.
Tip: It is possible to omit using a SQL server altogether if you do not wish to use activity logging. In this case, you should disable all activity logging in the site configuration.
From the Tools menu (cogwheel icon), go to Site Configuration > Server Configuration > Database Server.
Connection information
Here, you specify the server and database connection information for the SQL Server you want to use.
- Server: This is the server where the SQL Server runs. It's typically in the form MACHINENAME\INSTANCENAME. If you've upgraded from an earlier Fotoware installation with a bundled version of Microsoft SQL Express, it will be called MACHINENAME\FOTOWARE. The domain for a process account user cannot be empty even if a local user is set using a trusted connection to the database.
- Database name: This is the name of the database you want to use for this site. You must have separate databases for each site in your system. When FotoWeb creates a new database, it is placed in the default location of the database server. If you are using a full version of Microsoft SQL Server and want to control the database location, you should manually create an empty database, and then specify that database name here.
- Failover server: You can specify a failover SQL server in case the main SQL server is unavailable. You must set up database mirroring according to Microsoft guidelines. For information, see the following resources:
http://technet.microsoft.com/en-us/library/ms189047.aspx (How to prepare a mirror database for mirroring)
http://technet.microsoft.com/en-us/library/ms188712.aspx (How to configure a database mirroring session)
- Username: This is the name of the account FotoWeb should use to connect to the database.
- Password: This is the password FotoWeb should use to connect to the database. The password is stored in FotoWeb configuration files in encrypted form.
- Use Trusted Connection: You can use a trusted connection instead of specifying a database SA (System Administrator) account and password. When using a trusted connection, FotoWeb automatically grants access to the Fotoware process account to the database and configures it to use Windows authentication. This is a more secure way of connecting to SQL Server, and the method endorsed by Fotoware.
For more information about the authentication methods available with SQL Server, see Datasbase settings in detail.
When you change the connection information for the database, use the Test Connection button to verify that the connection operates correctly.
When connecting FotoWeb to an existing SQL server in your organization you may need to enable some ports in your firewall, for more information, see Before you install FotoWeb.
Important information about the Fotoware process account
If you specify a domain account as the server's process account in Operations Center Settings and fill in the Domain field as yourcompany.com, yourcompany.net, or something similar using a suffix, you will not be able to use a trusted connection when connecting to the SQL database. This is because the SQL database stores the Windows user with only the short form of the domain name, omitting the three-letter suffix (.net, .com, and so on) so that the SQL server will not be able to find the user.
If you specify a local account as the server’s process account in Operations Center Settings and leave the Domain field empty, you will not be able to use a trusted connection when connecting to the SQL database. This is because the SQL database stores the Windows user with its domain name (in this case, a computer name omitting the domain suffix) so that the SQL server will not be able to find the user. We recommend setting Domain field (in Operations Center Settings) to the computer name if the local account is the server’s process account. For example, if your full computer name is myserver.mydomain.com, the Domain field should have myserver value. In this case, the full name of the process account is myserver\localaccount
For a more detailed explanation of database settings, user accounts, and authentication methods, see Database settings in detail.
For more information about the Fotoware process account, see Setting a process account for Fotoware services.
Default account and password for the SQL Express instance (no longer included with installers)
The default username and password for the SA account set by FotoWeb on the SQL Express instance is:
Username: sa
Password: GgCP6127#
For FotoWeb versions before 8.0 Feature Release 3, the password is GgCP6127 (without the # at the end)
Note: If you are still running a version of Microsoft SQL Server Express that was bundled with an older version of FotoWeb, then we recommend updating to a newer version of Microsoft SQL Server. See system requirements for supported versions.
If you decide to keep the bundled version of Microsoft SQL Server Express or part of its configuration, we strongly recommend changing the password of the SA user or using Windows authentication.
Test Connection / Create and verify database
The Test Connection button verifies that the connection to the main database is working. If you want to create the database also, select Create and verify database. You must enter the account information for the database SA account (see above for default settings with SQL Express instance) or use a trusted connection, which will create the database and configure it to use Windows authentication in the context of the Fotoware process account. This also ensures that the database schema is correct and current. If the tables required by FotoWeb do not exist, you will be asked if you want to create them. FotoWeb will also create the database and the user accounts if they do not exist.
Scripts to create the database
Complete scripts to create the database and the necessary tables can be found in C:\ProgramData\FotoWare\FotoWeb\Server Settings\Database
Run scripts 1 through 4 to set everything up. You need to modify the scripts slightly to define the SA and database user accounts. Note that this should only be done by someone proficient in SQL server scripting.
Known issue
There is a known issue when the SQL server does not have an 'sa' SQL user account.
If you select Create and verify database it will fail with the following error message when trying to execute the 1_ChangeDbOwner.sql SQL query script:
Cannot find the principal 'sa', because it does not exist or you do not have permissions
If this occurs you can either add the 'sa' SQL user account to the SQL Server database or use Microsoft SQL Server Management Studio to manually create the database using the scripts mentioned above but leave out 1_ChangeDbOwner.sql .
Information panel
The information panel on the right shows information about your SQL server and database after selecting Test Connection.
SQL server version: This is the version of the SQL server.
Database size: This is the current size of the database in megabytes.
Database maintenance options
FotoWeb has a built-in task scheduler that performs various background administration tasks. Maintenance of the database logs is built-in and runs every night at midnight (00:00 local server time). This maintenance routine removes all outdated log entries in the search and activity logs. It also runs other consistency checks on the database to verify that the information is correct and current.
The database is also shrunk during this procedure. This ensures that the database files do not occupy more disk space than necessary. FotoWeb allocates 10% more disk space than actually used by the data.
As an optional step, you can instruct FotoWeb to make a database backup. This backs up the Master database and the database for the site to the site's Backup folder. The file is named according to the time when the backup was executed. You must manually copy these files to a permanent backup location or let a backup program handle that. You must also manually remove old backup files; otherwise, your disk will eventually be filled with the database backups. The backup procedure is only available if the SQL server runs on the same server as FotoWeb. If a full version of Microsoft SQL Server is running on a separate server, use its Enterprise Manager application to configure a backup plan.
The database backup is run every night at 00:00 local server time. The FotoWeb system is available for requests while the operation runs.
Backing up the database
For information on how to configure automatic backups of the database, see Backing up the FotoWeb databases.
Flush logs
To activate flush logs, turn on the Enable flush logs toggle.
Days to keep log entries: This setting defines how old log entries and report data can be before they are permanently flushed (deleted). Log data includes information such as which users have downloaded files, logged in, performed a search, etc. The FotoWeb reports module uses this information when generating reports.
Note that while the data used as the basis for reports is purged, already created reports and the data within them remain intact. Therefore, you can still access previously generated reports, but you will not be able to create new reports using data that has been purged.
Note: Report data is also flushed along with log data.
Manually purging the contents of reporting tables
To manually delete old reporting data (reports already created using this data are unaffected), follow the above procedure.
After the flush, the FotoWeb data pump retrieves log events from the tables that FotoWeb logs to and generates reporting data again.
1. Remote onto the server, open Windows Command Line utility, and navigate to C:\Program Files (x86)\Fotoware\FotoWeb 8.0\ConfigurationServer\bin
2. Run the following command:
DataPump.exe /ListSites
This lists all the FotoWeb sites on the server. In the next command, make sure you add the correct site from the list above:
DataPump.exe /Site INSERT-SITE-NAME /Flush
After flushing, note that users deleted from the FotoWeb system will appear in reports as Unknown user id 1234. This way, the data is anonymized and in compliance with the GDPR.
Did you know?
You can configure the Fotoware system to write all event logs to the database, too. For more information, see Writing the event log to a database.