Overview

This article will explain the process of using the Bulk Schema Upgrader tool and verifying the versions of the schema in use

Diagnosis

Customers may upgrade their Archiver server version and receive a failure on upgrading the attached databases schema, when migrating older Archiver databases to a new server, or may be bringing databases from cold storage. The Archiver Bulk Schema Upgrader tool can be used to quickly upgrade the database schema for a number of Archiver databases to the latest schema with one click of a button. 

You can check the current schema version of a database with the following steps and table.

1. Open the Microsoft SQL Server Management Studio
2. Expand Databases > GFI Archiver database > Expand Tables
3. Right click dbo.gfi_marc_dle_db_version > Select Top 1000 Rows

This will show you the schema version for the selected database. 

Solution

To upgrade or verify the schema for a number of GFI Archiver databases, perform the following:

1. Open the GFI Archiver Bulk Schema Upgrader found at the install location in the BulkSchemaUpgrader directory. (Default is ..\Program Files\GFI\Archiver\BulkSchemaUpgrader
   
2. Select which databases you wish to upgrade by selecting the checkbox next to the appropriate databases
   
3. Click on the Upgrade button to upgrade and verify the database schemas
4. You will be prompted to enter the credentials which are to be used for the schema upgrade. The credentials supplied need to be for the user assigned the dbo role for the GFI Archiver database. We strongly suggest using the 'sa' credentials for this process. If the 'sa' credentials are not available, the credentials supplied need to be for the user assigned the db_owner role on the GFI Archiver database

You can create a user with the db_owner role by performing the following:

1. Open the Microsoft SQL Server Management Studio
2. Expand Databases > GFI Archiver database > Security > Users
3. Right Click on Users and select New User
4. Enter a new User name and enter the same user name as login name
5. Select the db_owner as default schema
6. Under Schemas owned by this user select all GFI Archiver databases and db_owner under Database role Memberships and click OK
7. Enter these credentials in the GFI Archiver Bulk Schema Upgrader

You can confirm the user with the dbo role by performing the following:

1. Open the Microsoft SQL Server Management Studio
2. Expand Databases > GFI Archiver database > Security > Users
3. Right Click on dbo and select Properties
4. Use the same Login name displayed under the dbo properties
5. Enter these credentials in the GFI Archiver Bulk Schema Upgrader

Notes:

• The GFI Archiver Bulk Schema Upgrader is launched automatically as soon as the GFI Archiver installation is complete
• GFI Archiver does not store the credentials supplied in the Bulk Schema Upgrader for security purposes

Testing

The schema version can be verified by checking the following table in the GFI Archiver database using SQL Server Management Studio.

gfi_marc_dle_db_version

NOTE: This is an internal GFI Archiver table and should not be modified.

The purpose of this table is to keep track of the schema version and the database unique identification – GUID column. Every time the schema is updated, the version column is updated to reflect the schema version. This GUID value is stored in other places like MArcSettings.xml, and GFI Archiver Indexes. In case of embedded archive stores, the GUID is stored in the dbGuid.txt file.

1. Open the Microsoft SQL Server Management Studio
2. Expand Databases > GFI Archiver database > Expand Tables
3. Right click dbo.gfi_marc_dle_db_version > Select Top 1000 Rows

This will show you the schema version for the selected database. 

Compare the version column to the table above in the Diagnosis section to confirm the expected Schema is applied.