SQL installs guide
ShareScan 5.0has two SQL setup types: Complete:
Installs and connects the local MS SQL Server 2008 Express R2 to the ShareScan Manager. The default usernames and passwords are:
Username: sa Username: ecopy
Password: e+C0py2007_@x Password: e+C0py2007_@x
Custom:
Allows disabling the local install of MS SQL Server 2008 Express R2, and connects to an existing local or remote database.
You have two Custom options Option One:
Option two:
If you would like to connect to an existing ShareScan database installed on an existing SQL server, disable"ShareScan Database".
This option should be selected in the case when you’ll have multiple Managers connecting to a single, centralized database. When installing the 2nd, 3rd, etc. Managers without a database – you’ll need to specify the existing database at the first startup of the Administrator Console.
IMPORTANT FACT: ShareScan only supports Mixed Mode Authentication. ShareScan does not support
Windows Integrated authentication).
error message when clicking"Next".
Solution: Verify that all parameters are correct and try again. Click “NEXT”.
SQL Script commands How to use database scripts:
The database backup and restore features are also offered via the Administration Console UI (Advanced tab of the ribbon, Database button, Database Backup or Restore menu item) – for instructions and help to use those, please consult with the ShareScan Administration Console Help.
This document provides detailson how the command line scripts can be used for database backup and restore. These scripts provide basic support for backing up and restoring an existing ShareScan
database. It is also possible to drop (delete) a corrupted database and create a new, empty one. The backup and restore scripts can also be used as blueprints for a database administrator to set up an automatic database backup.
ShareScan setup automatically installs four database scripts into the "C:\Program
Files\Nuance\ShareScan5\Server\Tools\Database" folder: db_inst.cmd, db_uninst.cmd, db_backup.cmd and db_restore.cmd
The user must provide proper parameters for the command line scripts. (Previous versions of these scripts delivered with ShareScan have been using values pre-configured to use the local SQL Server database, but these pre-configured values are now removed for security reasons).
SQL Scripts location:
C:\Program Files (x86)\Nuance\ShareScan5\Server\Tools\Database Db_backup.cmd Runs a Backup on the DB
5 SQL Query files:
eCopyShareScan.sql main SQL file
eCopyShareScan-Backup.sql Run when db_Backup.cmd is used eCopyShareScan-Drop.sql Run when db_uninst.cmd is used eCopyShareScan-Restore.sql Run when db_restore.cmd is used ShareScanServer.sql Run when db_inst.cmd is used
Backing up a database
It is highly recommended to back up the ShareScan database when the initial configuration of the system is complete. In order to backup an existing local database:
1. Make sure that Admin Console is not running
2. Launch a command line window and go to the"C:\Program Files\Nuance\ShareScan5\Server\Tools\Database\" folder. 3. Enter and execute the following command:
db_backup.cmd [SQLSERVER] [backupUser] [backupPassword] [BackupFilePath]
The purposes of these parameters are:
[SQLSERVER] is the name (or IP address) of the SQL Server where the database to backup is hosted. This should contain the instance name too, if the database is hosted in a named instance (versus to the default SQL Server instance). Examples for valid SQLSERVER parameters:
BN-DBSERV1 The name of the machine where the SQL Server in use is installed CORPDBSERVER\ECOPYDB A named instance of the SQL Server is created to host the ShareScan
database on the server ‘CORPDBSERVER’
10.140.112.12 The IP of the PC running the SQL Server is specified .
(a period character)
The local SQL Server is used, with the default instance name. (This is the case for the default installation of the SQL Server, created by the ShareScan installer)
Please note that with the scripts you can perform backup or restore operation on remote SQL Servers (i.e. SQL Servers running on another machine that you’re using to run the script) too.
[saUser] is the user name having the ‘sysadmin’ rights. This can be ‘sa’ (without quotes). If the ‘sa’ account is used/known only by the database administrator, then this can be another valid user (created by the database administrator) that has the ‘db_backupoperator’ role assigned for the eCopyShareScan database. NOTE: the ‘eCopy’ user (used by the ShareScan system for normal operations) is NOT appropriate for this purpose, as it does not have the rights to perform a database backup. [saPassword] password of the user described in the previous paragraph. If the database server is installed by the ShareScan installer, then the ‘sa’ password will be e+C0py2007_@x
[BackupFilePath] the file name or the full file path you want to use as a database backup file. If only a filename is specified, the backup file will be created in the backup folder of the SQL Server – this path is set during the installation of the SQL Server, and will look like this:
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup
(Depending on your SQL Server installation, the above example is from a default ShareScan installation on a Windows7 OS. The easiest way of getting to know this path is to open the Database Backup or Restore dialog in the ShareScan Administration Console.)
Backup-eCopyShareScan-MMDDYYYY-HHmmAA.bak
(MM = month, DD = day, YYYY=year, HH= hour, mm = minute, AA= AM/PM)
If you’re using the scripts discussed in this document, the file name specified by the last parameter can be anything, but it is recommended to use the BAK extension. (The Restore dialog in the Administration Console will show only the files having the BAK extension, that’s why we recommend this.)
If you specify a full file path, then the backup file will be created at the specified path on the server the SQL Server is running (i.e. NOT the PC where you launched the db_backup.cmd script). Please make sure the specified path does exist on the PC running the SQL Server you specify as the target of the backup command. (1stparameter)
NOTE: Make sure you use double quotes to enclose this last parameter, if it contains a path (this is required because of the backslash characters contained in the path).
The script stops the Manager and Agent services and creates a backup copy of the actual state of the ShareScan database to the specified folder, into the file named as specified by the last parameter of the command line. After completing the backup the script restarts the Agent and Manager services.
NOTE: Starting and stopping of the services performed by the script is meaningful only in the case if you perform a database backup on the local machine. If you perform a database backup on a remote database, or if there are multiple Managers connected to the database you want to backup, you need to ensure that all the ShareScan Manager and ShareScan Agent services are stopped before starting the backup operation, and restarted after completing the backup operation successfully.
The result of the operation is logged into the file eCopyShareScan-Backup.log in the same folder from where the command line script is started. It is a good practice to check the content of this log file to see if there were no errors.
Example:
Db_backup.cmd BP-SHARESCAN1 sa e+C0py2007_@x db_backup_20110312.bak
Restoring a database
In order to restore a previous backup of the database: 1. Make sure that Admin Console is not running.
2. Launch a command line window and go to the"C:\Program Files\Nuance\ShareScan5\Server\Tools\Database\" folder.
3. If you specify only the backup file name, then make sure that the database backup file (created either by using the Backup – Restore dialog of the ShareScan Administration Console or by using the db_backup.cmd script describe in the previous section) exists in the Backup folder that can be found at C:\Program Files\Microsoft SQL Server\*\Backup – this folder certainly depends on the installation path of the SQL folder and also can be different, if another path was specified during the SQL Server installation. If you want to perform a restore operation from a backup file that was created on another database server, then you need to copy the backup file into this folder manually. (Or, you need to ask the database administrator to perform this copy
operation, if you do not have access to the physical server where the SQL Server is running.). Or, if you want to perform the backup from another folder, specify the full path as the 4thparameter
of the script.
4. Enter and execute the following command:
db_restore.cmd [SQLSERVER] [backupUser] [backupUsrPassword] [BackupFilePath] [eCopyUserPassword]
The meaning of the five obligatory parameters of the script is the same as for the Backup operation – see the above section. The last parameter is an extra one, compared to the backup operation, and it should contain the password that will be used as the password of the ‘eCopy’ database user used for normal ShareScan operation.
NOTE: Please note that the password will be set to the value specified in the command line parameter if there is no such user in the SQL Server. This means that if the ShareScan database was installed in the same SQL Server previously, and the database users ‘eCopy’ and ‘eCopyAddressbookAdmin’ are not deleted manually by the database administrator, then the passwords will not be overwritten with the new value.
The script stops the ShareScan Manager and ShareScan Agent services and restores the ShareScan database from the file specified. After completing the restore the script restarts the Agent and Manager services.
services are stopped before starting the restore operation, and restarted after completing the restore operation successfully.
The result of the operation is logged into eCopyShareScan-Restore.log in the folder from where the script is started. It is a good practice to check the content of this log file to see if there were no errors. Creating a new, empty database
It might be necessary to recreate the entire database due to system failure and/or missing backup. Please note that whenever you recreate the database you will lose all existing configuration settings and you have to reconfigure the system. Creating a new empty database is a two phase process. First, the existing database has to be dropped and then the new empty database has to be created.
In order to drop (delete) the existing database: 1. Make sure that Admin Console is not running.
2. Launch a command line window and go to the"C:\Program Files\Nuance\ShareScan5\Server\Tools\Database\" folder. 3. Enter and execute the following command:
db_uninst.cmd
4. Verify that the following files no longer exist: a. c:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\eCopyShareScan.mdf b. c:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\eCopyShareScan_log.LDF The script stops the ShareScan Manager and ShareScan Agent services and deletes the ShareScan database. The result of the operation is logged into the file eCopyShareScan-Drop.log.
In order to create a new database:
1. Make sure that Admin Console is not running.
2. Launch a command line window and go to the"C:\Program Files\Nuance\ShareScan5\Server\Tools\Database\" folder. 3. Enter and execute the following command:
db_inst.cmd
4. Verify that the following files are created: a. c:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\eCopyShareScan.mdf b. c:\Program Files\Microsoft SQL
The script creates a new, empty database and restarts the Agent and Manager services. The result of the operation is logged into eCopyShareScan.log.
Here are some suggested links to install SQL Server 1. Download and install Windows Installer 4.5:
http://go.microsoft.com/fwlink/?LinkId=159623 2. Download and install .NET Framework 3.5 SP1:
http://go.microsoft.com/fwlink/?LinkId=159615 3. Download and install Windows PowerShell 1.0:
http://www.microsoft.com/windowsserver2003/technologies/management/powershell/downl oad.mspx
4. Download SQL Server Express 2008 R2 from here:
IMPORTANT FACT: If you choose the “Named instance” option here, (for example, because you
'sa' account. Remember the password, you will need it later.
Verifying SQL Server installation:
1. Launch SQL Server Management Studio
3. Click New Query on the toolbar
5. Expand the Databases node in the Object Explorer and make sure that there is no eCopyShareScan database listed
Verifying ShareScan database installation:
1. Launch Database Management Studio and login with the sa account
3. Expand the Security node in the Object Explorer and verify that both eCopy and eCopyAddressBookAdmin logins exist
4 Click New Query on the toolbar
5 In the new query window enter the following commands: DECLARE @HWFP nvarchar(100)
6 Click the Execute button. In case of a successful command execution, you should see a 12-character long identifier (containing hexadecimal 12-characters) in the output window.
Troubleshooting SQL connection issues
The following message appears when opening the ShareScan Administration Console.
Changed database context to 'master'. Creating eCopyShareScan...
DBCC execution completed. If DBCC printed error messages, contact your system administrator. Changed database context to 'eCopyShareScan'.
Creating eCopyAddressbookAdmin... Creating <unnamed>... Creating ShareScan... Creating eCopy... Creating <unnamed>... Creating symKeyLicenses... Creating ShareScanCLRHelpers...
Msg 5847, Level 16, State 88, Server BP-VM-server\SQLEXPRESS, Line 1
Execution of .NET Framework code is disabled. Set "clr enabled" configuration option and restart the server.
In order to successfully deploy the Tables, we need to set the CLR option enabled, running the following query in SLQ Server Management Studio:
GO
Once done, please delete the partially created eCopyShareScan Database
You can delete the eCopy Database table by using the SQL Server Management Studio, or use can use the db_uninst.cmd script (for example, if no MGMT Studio is installed) – they are
equivalent. If you delete the database with either method, it is NOT required to delete the MDF / LDF files, because it will be deleted by the database delete operation.
Go to Services: restart the SQL Server service for this instance
Navigate to C:\Program Files (x86)\Nuance\ShareScan5\Server\Tools\Database\
Open a command line and run the Database Installer tool with the following parameters: db_inst [SQL Server name] [SA account] [SA password]
Example: C:\Program Files (x86)\Nuance\ShareScan5\Server\Tools\Database>db_inst BP-VM-W2K8X64R2\SQLEXPRESS sa P@ssword
You should see the following: Stopping services...
The ShareScan Manager v: 5.0 service is stopping.
The ShareScan Manager v: 5.0 service was stopped successfully. The ShareScan Agent v: 5.0 service is stopping.
The ShareScan Agent v: 5.0 service was stopped successfully. Installing ShareScan 5.0 Database...
Server
eCopyshareScan Done
Starting services...
The ShareScan Agent v: 5.0 service is starting.
The ShareScan Agent v: 5.0 service was started successfully. The ShareScan Manager v: 5.0 service is starting.
The ShareScan Manager v: 5.0 service was started successfully.
Errors can be ignored, as these will not have any effect for the outcome of the operations performed by the scripts.
The Management Studio should now show the created Tables:
Tracing:
The following trace files get created where the SQL scripts are run. eCopyShareScan.log
