Transcription
eCopy ShareScan 5.0SQL installs guideCreated by Rob O’Leary, 3/28/2011
5.0 SQL installs guide 2ShareScan 5.0 has 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: saPassword: e C0py2007 @xUsername: ecopyPassword: e C0py2007 @xCustom:Allows disabling the local install of MS SQL Server 2008 Express R2, and connects to an existinglocal or remote database.IMPORTANT FACT: The sa password will need to be supplied by the Customer’s IT Department.
5.0 SQL installs guide 3You have two Custom optionsOption One:If the you would like to have the ShareScan database installed on an existing SQL server, disable the"MS SQL Server 2008 Express R2" option.
5.0 SQL installs guide 4Option 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 tospecify the existing database at the first startup of the Administrator Console.Click “NEXT”.
5.0 SQL installs guide 5You will then be prompted to connect to the Database Server:IMPORTANT FACT: ShareScan only supports Mixed Mode Authentication. ShareScan does not supportWindows Integrated authentication).You will need to enter in the SA username and password or an account with SYSADMIN permissions.
5.0 SQL installs guide 61. If any of the provided connection parameters are incorrect, then you may receive the followingerror message when clicking "Next".Solution: Verify that all parameters are correct and try again.Click “NEXT”.The install will build out the SQL tables and stored procedures.
5.0 SQL installs guide 7SQL Script commandsHow to use database scripts:The database backup and restore features are also offered via the Administration Console UI (Advancedtab of the ribbon, Database button, Database Backup or Restore menu item) – for instructions and helpto use those, please consult with the ShareScan Administration Console Help.This document provides details on how the command line scripts can be used for database backup andrestore. These scripts provide basic support for backing up and restoring an existing ShareScandatabase. 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 anautomatic database backup.ShareScan setup automatically installs four database scripts into the tabase" folder: db inst.cmd, db uninst.cmd, db backup.cmdand db restore.cmdThe user must provide proper parameters for the command line scripts. (Previous versions of thesescripts delivered with ShareScan have been using values pre-configured to use the local SQL Serverdatabase, but these pre-configured values are now removed for security reasons).SQL Scripts location:C:\Program Files (x86)\Nuance\ShareScan5\Server\Tools\DatabaseDb backup.cmdRuns a Backup on the DBDb inst.cmdRuns an Install on the DBDb restore.cmdRuns a Restore on the SQL DBDb uninst.cmdRuns an un-install on the SQL DB
5.0 SQL installs guide 85 SQL Query files:eCopyShareScan.sqlmain SQL fileeCopyShareScan-Backup.sqlRun when db Backup.cmd is usedeCopyShareScan-Drop.sqlRun when db uninst.cmd is usedeCopyShareScan-Restore.sqlRun when db restore.cmd is usedShareScanServer.sqlRun when db inst.cmd is usedBacking up a databaseIt is highly recommended to back up the ShareScan database when the initial configuration of thesystem is complete. In order to backup an existing local database:1. Make sure that Admin Console is not running2. Launch a command line window and go to the tabase\" folder.3. Enter and execute the following command:db backup.cmd [SQLSERVER] [backupUser] [backupPassword] [BackupFilePath](Please note that the names of the parameters shown by the script if you start it without parameters arenot precisely as shown above, as they are called ‘DatabaseUser’, ‘DatabasePassword’, and this namingcan be confusing – the “Usage” help provided by the command line scripts will be updated in the nextservice pack. Please read the below information carefully to make sure you understand the purpose ofthese parameters.)
5.0 SQL installs guide 9The purposes of these parameters are:[SQLSERVER] is the name (or IP address) of the SQL Server where the database to backup is hosted. Thisshould contain the instance name too, if the database is hosted in a named instance (versus to thedefault SQL Server instance). Examples for valid SQLSERVER parameters:BN-DBSERV1The name of the machine where the SQL Server in use is installedCORPDBSERVER\ECOPYDBA named instance of the SQL Server is created to host the ShareScandatabase on the server ‘CORPDBSERVER’10.140.112.12The IP of the PC running the SQL Server is specified.The local SQL Server is used, with the default instance name. (This isthe case for the default installation of the SQL Server, created by theShareScan installer)(a period character)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 (createdby the database administrator) that has the ‘db backupoperator’ role assigned for the eCopyShareScandatabase. NOTE: the ‘eCopy’ user (used by the ShareScan system for normal operations) is NOTappropriate 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 isinstalled 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 afilename is specified, the backup file will be created in the backup folder of the SQL Server – this path isset 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 installationon a Windows7 OS. The easiest way of getting to know this path is to open the Database Backup orRestore dialog in the ShareScan Administration Console.)If you’d use the Administration Console to create a backup then the file name is created based on a filenaming rule
5.0 SQL installs guide 10Backup-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 canbe anything, but it is recommended to use the BAK extension. (The Restore dialog in the AdministrationConsole 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 theSQL Server is running (i.e. NOT the PC where you launched the db backup.cmd script). Please make surethe specified path does exist on the PC running the SQL Server you specify as the target of the backupcommand. (1st parameter)NOTE: Make sure you use double quotes to enclose this last parameter, if it contains a path (this isrequired 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 theShareScan database to the specified folder, into the file named as specified by the last parameter of thecommand 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 thecase if you perform a database backup on the local machine. If you perform a database backupon a remote database, or if there are multiple Managers connected to the database you want tobackup, you need to ensure that all the ShareScan Manager and ShareScan Agent services arestopped before starting the backup operation, and restarted after completing the backupoperation successfully.The result of the operation is logged into the file eCopyShareScan-Backup.log in the same folder fromwhere the command line script is started. It is a good practice to check the content of this log file to seeif there were no errors.Example:Db backup.cmd BP-SHARESCAN1 sa e C0py2007 @x db backup 20110312.bakThis command will create a backup of the eCopyShareScan database hosted on the server named ‘BPSHARESCAN1’, and the backup file will be named as ‘db backup 20110312.bak’ on that server, in thefolder described above. (And not on the PC from where we started the script – except if it was exactlythe PC named ‘BP-SHARESCAN1’)
5.0 SQL installs guide 11Restoring a databaseIn 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 tabase\" folder.3. If you specify only the backup file name, then make sure that the database backup file (createdeither by using the Backup – Restore dialog of the ShareScan Administration Console or by usingthe db backup.cmd script describe in the previous section) exists in the Backup folder that canbe found at C:\Program Files\Microsoft SQL Server\*\Backup – this folder certainly depends onthe installation path of the SQL folder and also can be different, if another path was specifiedduring the SQL Server installation. If you want to perform a restore operation from a backup filethat was created on another database server, then you need to copy the backup file into thisfolder manually. (Or, you need to ask the database administrator to perform this copyoperation, 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 4th parameterof 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 itshould contain the password that will be used as the password of the ‘eCopy’ database user used fornormal ShareScan operation.NOTE: Please note that the password will be set to the value specified in the command line parameterif there is no such user in the SQL Server. This means that if the ShareScan database was installed inthe same SQL Server previously, and the database users ‘eCopy’ and ‘eCopyAddressbookAdmin’ arenot deleted manually by the database administrator, then the passwords will not be overwritten withthe new value.The script stops the ShareScan Manager and ShareScan Agent services and restores the ShareScandatabase from the file specified. After completing the restore the script restarts the Agent and Managerservices.NOTE: Starting and stopping of the services performed by the script is meaningful only in thecase if you perform a database restore on the local machine. If you perform a database restoreon a remote database, or if there are multiple Managers are connected to the database youwant to restore, you need to ensure that all the ShareScan Manager and ShareScan Agent
5.1 SQL installs guide 12services are stopped before starting the restore operation, and restarted after completing therestore operation successfully.The result of the operation is logged into eCopyShareScan-Restore.log in the folder from where thescript 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 databaseIt 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 settingsand 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 tabase\" folder.3. Enter and execute the following command:db uninst.cmd4. Verify that the following files no longer exist:a. c:\Program Files\Microsoft SQLServer\MSSQL10 50.MSSQLSERVER\MSSQL\DATA\eCopyShareScan.mdfb. c:\Program Files\Microsoft SQLServer\MSSQL10 50.MSSQLSERVER\MSSQL\DATA\eCopyShareScan log.LDFThe script stops the ShareScan Manager and ShareScan Agent services and deletes the ShareScandatabase. 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 tabase\" folder.3. Enter and execute the following command:db inst.cmd4. Verify that the following files are created:a. c:\Program Files\Microsoft SQLServer\MSSQL10 50.MSSQLSERVER\MSSQL\DATA\eCopyShareScan.mdfb. c:\Program Files\Microsoft SQLServer\MSSQL10 50.MSSQLSERVER\MSSQL\DATA\eCopyShareScan log.LDF
5SQL installs guide 13The script creates a new, empty database and restarts the Agent and Manager services. The result of theoperation is logged into eCopyShareScan.log.Here are some suggested links to install SQL Server1. Download and install Windows Installer 4.5:http://go.microsoft.com/fwlink/?LinkId 1596232. Download and install .NET Framework 3.5 SP1:http://go.microsoft.com/fwlink/?LinkId 1596153. Download and install Windows PowerShell hnologies/management/powershell/download.mspx4. Download SQL Server Express 2008 R2 from tallOptions.aspx
5.0 SQL installs guide 145. Select New installation or add features to an existing installation6. On the License Terms page select I accept the license terms and click Next
5.0 SQL installs guide 157. On the Setup Support Rules page make sure that all tests pass and click Next:
5.0 SQL installs guide 168. On the Feature Selection page select Database Engine Services, Management Tools - Basic andclick Next.
5.0 SQL installs guide 179. On the Instance Configuration Page choose Default Instance, and click Next.IMPORTANT FACT: If you choose the “Named instance” option here, (for example, because youalready have a default installation of the SQL Server) then you’ll need to use that instance namelater on when specifying the database server for ShareScan. (Instead of the pure server name,you’ll need to use Servername\Instancename).
5.0 SQL installs guide 1810. On the Server Configuration page choose NT AUTHORITY\NETWORK SERVICE for the SQL ServerDatabase Engine account and select Automatic for the SQL Server Browser Startup Type andclick Next.
5.0 SQL installs guide 1911. On the Database Engine Configuration page select Mixed Mode and provide a password for the'sa' account. Remember the password, you will need it later.IMPORTANT FACT: Mixed needs to be specified for the authentication mode.
5.0 SQL installs guide 2012. On the Error Reporting Page click Next
5.0 SQL installs guide 2113. Complete page make sure that the installation was successful and click Close
5.0 SQL installs guide 22Verifying SQL Server installation:1. Launch SQL Server Management Studio2. On the Connect to Server dialog change the Authentication to SQL Server Authentication, entersa in the Login field and provide the password for the sa account you have chosen during theinstallation (in step #11) and click Connect.
5.0 SQL installs guide 233. Click New Query on the toolbar4. In the new query window enter select @@version and click the Execute button. You shouldreceive the version of the SQL Server as a result
5SQL installs guide 245. Expand the Databases node in the Object Explorer and make sure that there is noeCopyShareScan database listed6. Exit Database Management Studio
5.0 SQL installs guide 25Verifying ShareScan database installation:1. Launch Database Management Studio and login with the sa account2. Expand the Databases node in the Object Explorer and verify that the eCopyShareScan databaseexists and the database contains tables
5.0 SQL installs guide 263. Expand the Security node in the Object Explorer and verify that both eCopy andeCopyAddressBookAdmin logins exist4Click New Query on the toolbar5In the new query window enter the following commands:DECLARE @HWFP nvarchar(100)EXECUTE [eCopyShareScan].[ShareScan].[GetHWFP] @HWFP OUTPUTPRINT @HWFP
5.0 SQL installs guide 276Click the Execute button. In case of a successful command execution, you should see a 12character long identifier (containing hexadecimal characters) in the output window.78Exit Database Management StudioLaunch Admin Console
8.0 SQL installs guide 28Troubleshooting SQL connection issuesThe following message appears when opening the ShareScan Administration Console.Due to this Database error, all options in Manager are disabled:
5.0 SQL installs guide 29Open the Database in SQL Server Management Studio, it shows that the Tables for eCopyShareScanDatabase did not get created:
5.0 SQL installs guide 30Browse to "C:\Program Files\Nuance\ShareScan5\Server\Tools\Database"Open the eCopyShareScan.log file
5.0 SQL installs guide 31Changed 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 1Execution of .NET Framework code is disabled. Set "clr enabled" configuration option and restart theserver.In order to successfully deploy the Tables, we need to set the CLR option enabled, running thefollowing query in SLQ Server Management Studio:GOsp configure 'show advanced options', 1;GORECONFIGURE;GOsp configure 'clr enabled', 1;GORECONFIGURE;GO
5.0 SQL installs guide 32You should see the following after running these commands:
5.0 SQL installs guide 33Once done, please delete the partially created eCopyShareScan DatabaseYou can delete the eCopy Database table by using the SQL Server Management Studio, or use canuse the db uninst.cmd script (for example, if no MGMT Studio is installed) – they areequivalent. 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.IMPORTANT FACT: if the DB is not deleted, the next attempt to deploy it will fail it is NOTrecommended to delete the MDF / LDF files ONLY, if the DB has not been deletedpreviously with any of the above methods, as it will result in an error in SQL Server.However, if you’d have some corrupt state when the database would not exist in the SQLManagement Studio at all, but the MDF / LDF files would be there, then you should deletethe MDF / LDF files manually, but only in this special case.
5.0 SQL installs guide 34
5.0 SQL installs guide 35 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 Scan5\Server\Tools\Database db instW2K8X64R2\SQLEXPRESS sa P@sswordBP-VM-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.ServereCopyshareScanDoneStarting 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.Note: if any or both of the services (Agent or Manager) are stopped when running these scripts,there may be error messages saying that the stopping of the service was unsuccessful – this
5.0 SQL installs guide 36Errors can be ignored, as these will not have any effect for the outcome of the operationsperformed by the scripts.The Management Studio should now show the created Tables:The ShareScan Manager will be able to launch without issue.
5.0 SQL installs guide 37Tracing:The following trace files get created where the SQL scripts are run.eCopyShareScan.logShareScanServer.log
ShareScan 5.0 has 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 .
