EVault InfoStage™
MS SQL Server Plug-In 4.20
Installation & Configuration Guide
June 2003
1 MS SQL Server Plug-In ...4
1.1 Overview...4
1.2 Features ...5
1.3 About this Guide...6
2 Installing the MS SQL Server Plug-In... 10
2.1 Licensing... 10
3 Backups... 11
3.1 Table for User Backup Information ... 11
3.2 Performing Backups... 12
3.3 Backup Wizard... 14
3.4 Task Properties... 15
3.5 Error Messages ... 16
Revision: This manual is updated for Version 4.20 (June, 2003). Software Version: 4.20
Copyright 2000-2003 EVault, Inc. All rights reserved.
EVault, Inc. makes no representations or warranties with respect to the contents hereof and specifically disclaims any implied warranties of merchantability or fitness for any particular purpose. Furthermore, EVault reserves the right to revise this publication and to make changes from time to time in the content hereof without obligation of EVault to notify any person of such revision of changes. All companies, names and data used in examples herein are fictitious unless otherwise noted. No part of this document may be reproduced, transmitted, transcribed, stored in a retrieval system or translated into any language including computer language, in any form or by any means electronic, mechanic, magnetic, optical, chemical or otherwise without prior written permission of:
EVault, Inc.
c/o Corporation Trust Center 1209 Orange Street
Wilmington, New Castle Delaware 19801 www.EVault.com
EVault, EVault InfoStage, EVault Protect, and EVault DeltaPro, are registered trademarks of EVault, Inc. All other products or company names mentioned in this document are trademarks or registered trademarks of their respective owners. Acknowledgements: Two encryption methods, DES and TripleDES, include cryptographic software written by Eric Young. The Windows versions of these algorithms also include software written by Tim Hudson. Bruce Schneier designed Blowfish encryption.
The EVault InfoStage Agent, EVault InfoStage CentralControl, and EVault InfoStage Director applications (version 4.x only) now have the added encryption option of 128bit AES (Advanced Encryption Standard). Advanced Encryption Standard algorithm (named Rijndael, pronounced “Rain Doll”) was developed by cryptographers Dr. Joan Daemen and Dr. Vincent Rijmen. This algorithm was chosen by the National Institute of Standards and Technology (NIST) of the U.S. Department of Commerce to be the new Federal Information Processing Standard (FIPS). AES is not available when connecting to a Vault lower than version 4.1. See:http://csrc.nist.gov/encryption/aes/round2/r2report.pdf for details.
1 MS SQL Server Plug-In
1.1 Overview
The EVault InfoStage™ MS SQL Server Plug-In has been designed to allow a user to perform a database backup (and subsequent restore, if necessary) on an MS SQL Server. The Plug-In is installed on top of the EVault InfoStage™ Agent on the database host, to perform the backups, either on demand, or scheduled. The EVault InfoStage™ CentralControl application has been modified in this version to accommodate the functions and inputs necessary for this plug-in.
This diagram illustrates the basic product implementation. A user, typically a DBA, will configure the backup via the CentralControl application. CentralControl configures the agent, which may reside on a different host than CentralControl. However, the Agent and Plug-In must reside together on the MS SQL Server system. The User may schedule a backup of the Database, at which time the agent, with the aid of the DB plug-in, sends the database information to the Director.
1.2 Features
• The Plug-In application has its own installation program, but is operated and configured within the CentralControl (CC) program GUI
• MS SQL Server 7 or 2000, running on Windows 2000 Server, or Advanced Server (SP3) • The database(s) to be backed up must run on a single host.
• The Windows Agent and the MS SQL Server Plug-In must be together on the same system running MS SQL Server. The Windows Agent and the MS SQL Server Plug-In must always have the same version number.
• If a database host is completely lost, the database software may be loaded and the database completely restored, after a full system restore
• Hot backup - occurs without taking the database down
• Restore may be to same or alternate database location, or to a flat file • Provides full and incremental backup
• MS SQL Server 7.0 supports one (default) instance (i.e.: "computer_name"). MS SQL Server 2000 supports a default instance, and up to 15 Named instances (i.e.: "computer_name\instance_name") • The Plug-In will:
· Dynamically determine what SQLServer client is installed.
· If it is SQLServer 7 client, the Plug-In will support access to the single instance of SQLServer 7 installed (if it exists).
· If it is SQLServer 2000 client, the Plug-In will support access to all instances of the server installed (regardless of what version they are).
1.3 About this Guide
This section describes the product line for EVault software, and the associated manuals. A brief content of each manual is provided, to assist the user in understanding what is covered where, and how the products are referenced.
These notes are also contained in each separate manual, to provide overall consistency across the product line, and to allow users to see where each piece of their system fits in with the others.
EVault Product Family Guides
Vault Side Agent Side
CentralControl User Guide
WIndows Agent User Guide
Unix Agent User Guide
NetWare Agent User Guide
Exchange Plug-In User Guide
MS SQL Server Plug-In Installation & Configuration
Guide **
Oracle Plug-In User Guide
Director User Guide
ExoRecovery User Guide
Web Reporting Branding Guide
Ultra Recovery Guide
Quick Start User Guide
Note: ** means this Guide.
1. Quick Start User Guide – This Guide is intended as an overall “Quick Start” for new users of the EVault electronic vaulting system, for doing backups and restores.
2. CentralControl User Guide – This manual starts with a brief overview of how the EVault products work. The chapters in the manual cover the following topics:
• Installing the main CentralControl software (GUI).
• Using the CentralControl GUI – Workspace, Agents, Global Settings, Tasks, Safesets, Catalogs and Log files.
• Performing backups – Types, Seeding, Mapped drives and databases, Options, Tape, Retentions, Notification, Expiration, Scheduling and Ad-hoc.
• Report Logs – Creating and Managing Log files. • Data Security – User Authentication and Encryption. • Open File Backup – Shared files, OTM, and OFM. • Troubleshooting and Command Line Interface.
3. Windows Agent User Guide – the Agent (Service) for Windows users. • Agent for Windows Install
• Using the Agent
• Windows Systems Recovery
4. Unix Agent User Guide – the Agent (daemon) for Unix users. • Agent for Linux Install
• Agent for AIX Install • Agent for Solaris Install • Agent for HP-UX install • Using the Agent • Unix Systems Recovery
5. Netware Agent User Guide – the Agent (NLM) for Netware users. • Agent for Netware Install
• Using the Agent
• Netware Systems Recovery 6. Exchange Plug-In User Guide – • Overview of the Exchange Plug-In • DR vs. MAPI backups
• Installing and Licensing • Backups
• Restores
• DR Optimization • MAPI Optimization
7. SQL Server Plug-In Installation and Configuration Guide • Overview and features
• Backups • Restores
8. Oracle Plug-In User Guide – • Overview and features • Installing and Licensing • Backups
• Restores
Vault Side – these manuals are used by the operators/users responsible for installing, configuring and maintaining the vault side of the system.
9. Director User Guide – This manual starts with a brief overview of how the EVault products work. • Installation of the Director products - the management console and the vault component. • Operation of the Director GUI.
• Running of different Wizards. These correspond to the pull-down menu selections in the Director GUI.
10. ExoRecovery User Guide – Allows multiple backups to be stored to the same offline media. • Initializing tapes
• Archiving to tape • Recalling • Reports
11. Web Reporting Branding Guide – Allows customers to view information about their previous backups and restores.
• Installation • Usage
12. Ultra Recovery User Guide – Provides a recovery service in the event of data loss on a vault. • Installation
Release notes
Release Notes, which are shipped as a “ReadMe” text file with the product, contain “up to the minute” information on the released product, and contain an overview of new features, any known defect (bug) fixes incorporated since the last release, a description of any known issues, and a section on product support.
On Line Helps
Director Help: The Director application (GUI) has an online help, which contains information similar to the user manual. The help is accessed from the main pull-down menu, or by using the F1 function key. There is also context sensitive “WhatsThis?” help on each GUI screen.
2 Installing the MS SQL Server Plug-In
The MS SQL Server Plug-In integrates into the existing InfoStage architecture and provides the user with the ability to backup/restore MS SQL Server database to/from the Director.
Installing the EVault InfoStage™ MS SQL Server Plug-In version 4.20 requires that you have previously installed the EVault InfoStage™ Windows Agent application, version 4.20. If your EVault CentralControl application version is lower than 4.2, you must update it to version 4.20 before installing the MS SQL Server Plug-in. Also, your Director must be running EVault InfoStage™ Director version 4.20 to use the MS SQL Server Plug-in.
To install the MS SQL Server Plug-In double click on the MS SQL Server-Plug-In file (“MS_SQL_Plug-in.exe) or use the Windows “Start”, “Run” sequence locating the path to the MS SQL Server Plug-In application. This will launch the “install wizard”. Follow the detailed steps outlined in the installation Wizard.
If the SQL Server being backed up by the Plug-In was not installed as an original Microsoft product (that is, it has been resold/repackaged by a third party), you may get errors on trying to create an MS SQL task, such as “Failed to connect to the server.” Agent log may show “UNKNOWN ERROR in termRestore()” on every attempt to connect to the SQL Server. Get Status on the Agent may not show the SQL Server Plug-in, even though the license validates.
The problem is that the installation is missing the NTWDBLIB.DLL file, which should be in the EVault installation directory. Contact support to obtain a copy of this file.
Note: For functions such as “Creating a new agent”, “Creating a backup task”, “Scheduling Backups”, and Disaster
Recovery information, refer to the EVault Installation and User Guides for more information on installing and using the EVault software.
2.1 Licensing
Agent License info:
After installing the MS SQL Server Plug-In, you must supply license information before you can use the Plug-In.
1. Obtain a license from EVault, or your service provider.
2. On the CentralControl program, under My Agent, -> Global Settings -> License (tab) enter the license information.
3. Press Test, to ensure the license is correct/valid. If not, renter the data. 4. Press Set to save the information.
3 Backups
3.1 Table for User Backup Information
Before performing the MS SQL Server database Backup process, be sure you have all the information such as names, locations, passwords, etc. that the Wizard will prompt for. The table below is provided for reference.
System Requirement Customer/User supplied value Comments
New task name Name = Task used to talk to the agent with
MS SQL Server Plug-In
Vault profile Profile = Profile of an existing (already created)
Vault. Chosen from a pull-down menu.
Backup Source type Type = Select “MS SQL Server” from the
pull-down menu. MS SQL Server Connection Information Computer = Instance = Authentication = NT or SQL User Name = Password =
Dependent on user’s configuration and DBA setup.
Validates the fields, and allows access to the database.
MS SQL Server database selection
Database = Pull-down list of all available
databases.
Backup types Choose “Full” or “Incremental”
Network Account Information User Name = Password = Domain =
Supplies NT credentials to the server. Enables backup process to run across the network.
Quick file scanning Set on or off
Encryption type Type =
Password =
If you select a type, you must supply a password
Log options Options =
Detail level = Copies =
User defined
Start the backup Immediate = Schedule =
3.2 Performing Backups
To back up your MS SQL Server you will first need to add the Agent with the MS SQL Server on it to your workspace, and then create a new task in that agent, using “MS SQL Server” type. It is not recommended to do multiple tasks, at the same time, on the same database.
The Backup Wizard will direct the user through the process to create a task, but briefly, the steps to perform this include:
1. From the CentralControl GUI program (which communicates with the agent and the plug-in), create a new task.
2. Enter a Vault profile where the task will be targeted.
3. Select the source of the data that will be backed up by this task.
4. Supply the MS SQL Server connection information. Enter the MS SQL Server computer name. Then choose an instance from the “Select Server Instance” list. The list will have the first entry pre-selected as <Default>, designating the default instance. Other entries will be present only if they can be retrieved. Next, choose the NT or SQL authorization option. (See the MS SQL Server Help section on NT/SQL Authorization for more information.)
- If you choose the NT authorization button, access is by the NT logon supplied user name and password. User Name and Password on this screen are not used.
- If you choose the SQL authorization button, access is determined by the User Name and Password on this screen.
5. Select a specific database for backup. Only one database at a time is backed up with each task. But there can be more than one task doing backups, on different databases.
6. Enter network account information to enable the backup process to run across the network – User name, password, and domain. This allows the Agent to connect to a local or remote database. 7. Select or de-select the “Quick file scanning” option. Note that the “deferred” option is not
available here; the backup must be completed in one step.
8. If desired, select an encryption type and supply an encryption password. 9. Select logging options and level of detail, copies, and retention.
10. Start the backup (immediately), schedule it for later, or exit the Wizard.
Log files are created on the CentralControl machine, under the EVault directory, in directories with the task name. They are normally viewed from the CentralControl application screen.
Backup Notes:
1. MS SQL Server 7.0 does not allow incremental backups for databases with the ‘trunc.log on chkpt’ options. Likewise, MS SQL Server 2000 doesn’t allow incremental backups for databases if the model of database is set to “Simple”.
2. MS SQL Server 7.0 does not allow incremental backups for databases after switching the ‘trunc.log on chkpt’ options off (or truncating log) until at least one successful full backup. Likewise, MS SQL Server 2000 does not allow incremental backups for databases if the recovery model of database is set to “Simple”.
3. MS SQL Server Plug-In has standard backup/restore permission requirements for the logged user: 4. Backup Permissions: BACKUP DATABASE and BACKUP LOG permissions default to members
3.3 Backup Wizard
This section describes the new screens in the Backup Wizard that do the SQL Server connection: Computer selection, Instance selection, and Authentication.
Computer Selection:
Task Wizard - MS SQLServer
Enter Computer name
< Back Next > Cancel Computer:
This allows the user to enter a computer name. The “Next” button is disabled until something is entered. There is no connection/verification here. That comes after the Authentication screen.
Instance Selection
Task Wizard - MS SQLServer
Select Server instance
< Back Next > Cancel Available instances:
<Default> Instance1 Another Instance
Authentication
Task Wizard - MS SQLServer
Enter Authentication information
< Back Next > Cancel User Name: NT Authentication SQL Server Authentication Password: Confirm Password: Instance: Computer:
The screen will allow the user to select the authentication type (NT or SQL) and enter a user name and password to connect to the selected instance. After entering valid credentials, the user will proceed to the database selection dialog, which is the same as before. Note here that Computer and Instance names are presented in read-only format, for information purposes.
The “SQL Server Authentication” option can only be chosen if “SQL Server and Windows” authentication is enabled on the SQL Server.
3.4 Task Properties
This section describes a new tab in the screen for displaying/changing task properties, for all versions of the plug-in.
Task properties SQL Server Source Instance: Computer: Authentication User Name: Database: Change... OK Cancel
In the Task Properties, the “SQL Server Source” tab will display: • Computer name or address
• Instance name or <Default>
• Authentication type (NT or SQL Server) • User name
The “Change” button will bring up a Wizard (similar to Backup) consisting of: • Computer Selection screen
• Instance Selection Screen (this will be skipped for previous version of the plug-in) • Authentication Screen
• Database Selection Screen • Finish screen
3.5 Error Messages
When an SQL database is backed up using the incremental (transaction log) method, the logs are sent to the Vault, and are applied against the last full backup, when doing a restore. However if another user or task, outside of the EVault applications, causes the database to produce a transaction log file, it is recorded by the SQL server, in msdb, but EVault does not know about this. In this case the transaction log files are out of synch, for EVault, and thus the system must create a full backup to ensure integrity of the data, as one or more of the transaction log files is “missing” from the EVault backup.
The backup process detects that the logs are out of synch and automatically switches from an incremental backup to a full back. No action is required on the part of the user/DBA. An informational message is written to the backup log (history) file. (Note that the backup process may take longer than anticipated, as the process is now a full backup, as opposed to an incremental one.)
“An external backup was detected. Switching from Incremental to Full backup.”
This means that the backup history of the database in the system SQL database msdb is out of synch with the list of successful task backups on EVault server.
List of possible reasons:
- The database backup was made outside the selected task after last successful task backup - Some files were lost on EVault server
- Backup history was cleaned up - msdb was restored
As well, on a restore, the system may discover that the transaction log files are out of synch, and again will switch to a full backup. Again, no action is required on the part of the user/DBA. An informational message is written to the backup log (history) file.
“A compromising restore was detected. Switching from Incremental to Full backup.”
This means that the restore history of the database in the system SQL database msdb indicates that the database was restored after the last successful full backup in this task.
List of possible reasons:
- The database was restored after last incremental backup in this task - Backup/restore history was cleaned up
4 Restores
For Restore, the user will specify a backup (a particular safeset) up to which he would like to restore. Restores can be to the original location, an alternate location, or a file. Restores may be necessary in three scenarios:
• Restoring the full database, with any incremental backups, overwriting the existing database. • With no system backup, restoring the system from the ground up (“bare metal”) – installing the
OS, applications and then the full database, and any incremental backups, onto a new system. • If there is an MS SQL Server backup, and a full system backup, install the OS and then restore
the system, and MS SQL Server.
The Restore Wizard will direct the user through the process, but briefly, the steps to perform this include: 1. From the CentralControl, select the restore function (Wizard).
2. Supply the restore type location and type – Vault and safeset
3. Confirm account information for MS SQL Server authentication – computer name, instance selection, user name, and password. Choose the NT or SQL authorization option.
4. Select the destination where the MS SQL Server database should be restored – original database, other database, or Directory.
5. Enter network account information that will enable the restore process to run – User name, password and domain.
6. Select logging options and level of detail. 7. Start the restore process.
Restore Notes:
1. Restore Permissions: If the database being restored does not exist, the user must have CREATE DATABASE permissions. If the database does exist, RESTORE permissions default to members of the sysadmin fixed server role and the db_owner fixed database role.
2. REPLACE option specifies that MS SQL Server should create the specified database and its related files even if another database already exists with the same name. In such a case, the existing database is deleted. When the REPLACE option is not specified, a safety check occurs (which prevents overwriting a different database by accident). The safety check ensures that the RESTORE operation will not restore the database to the current server if:
• The database named in the RESTORE statement already exists on the current server, or • Either the database name is different from the database name recorded in the backup set, or • The set of files in the database is different from the set of database files contained in the
