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.
Database settings are configured under Settings - Services - Database server in the site configuration.
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 take care to disable all activity logging in the site configuration.
You must specify the server and database connection information for the SQL Server you want to use. The various options are explained below.
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 where a bundled version of Microsoft SQL Express was included, it will be called MACHINENAME\FOTOWARE.
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)
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.
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 account and password. When using a trusted connection, FotoWeb will automatically grant access to the FotoWare process account to the database and configure it to use Windows authentication. This is a more secure way of connecting to SQL Server, and the method endorsed by FotoWare.
When you change the connection information for the database, you should always 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.
Important information about the FotoWare process account:
If you specify a domain account as the server's process account in the Operations Center 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. That's because the SQL database stores the windows user with only the short form of the domain name, omitting the three-letter suffix (.net, .com, etc.) so that the SQL server will not be able to find the user.
For an in-depth explanation of database settings, user accounts, and authentication methods, please refer to the Database settings in detail reference article.
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:
For FotoWeb versions prior to 8.0 Feature Release 3 the password is GgCP6127 (without the # at the end)
Important: 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, then we strongly recommend changing the password of the sa user, or using Windows authentication.
Test Connection / Create and verify database
The Test Connection button will verify that the connection to the main database is working. If you want to create the database also, you must click on the Create and verify database button. This will require you to fill in the account info 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 will also make sure that the database schema is correct and up to date. 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 will need to modify the scripts slightly to define the user accounts for sa and the database user. Note that this should only be done by someone proficient in SQL server scripting.
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 will remove all outdated log entries in the search and activity logs. It will also run other consistency checks on the database to verify that the information is correct and up to date.
The database will also be shrunk during this procedure. This ensures that the database files do not occupy more disk space than necessary. FotoWeb will allocate 10% more disk space than actually used by the data.
As an optional step, you can instruct FotoWeb to make a backup of the database. This will back up the Master database and the database for the site to the site's 'Backup' folder. The file will be 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 you have a full version of Microsoft SQL Server 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 will be available for requests while the operation runs.
Days to keep log entries: This setting defines how old log entries and report data can be before they're flushed (deleted) permanently. 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 that is used as the basis for reports is purged, already created reports and the data within them will 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.
Important: 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 will not be affected), follow the procedure outlined above.
After the flush, the FotoWeb data pump will retrieve log events from the tables that FotoWeb logs to and generate 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:
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 that have been deleted from the FotoWeb system will show up in reports as Unknown user id 1234. This way the data is anonymized and in compliance with the GDPR.