Moving SQL Server databases or security files

For a SQL Server iBase database, moving or renaming the database also involves making changes to its connection and security files. In some cases, iBase Designer can make the changes automatically. In others, you might need to intervene.

An iBase SQL Server database consists of:

  • The security file, with extension .ids

  • The database connection file, with extension .idb

  • The Microsoft™ SQL Server database that holds the main iBase database

  • Optionally, extra Microsoft SQL Server databases that hold the iBase audit log, archived audit logs, and the Search 360 indexes

If the security file has been upsized to SQL Server, the database also consists of:

  • The security connection file, with extension .ids

  • The SQL Server database that holds the security data

It is simplest for administrators and users if you keep the security file (or security connection file) in the same folder as the database connection files that it secures. Share the folder and reference it by a UNC path.

Note: Do not copy connection files to client workstations. This might compromise the security of your system and adds to the administrative workload. Keep the .ids file in the same folder as the .idb file, in a central location.

Moving or renaming the security connection file

If you move or rename a SQL Server security connection file, you must open each related database in iBase Designer to update the stored location.

Note: If you move the security and database connection files to the same folder, you can update both locations in one operation by opening the database.

What happens when you open the database depends on the location of the .ids file relative to the .idb file. The possibilities are:

  • If there is a .ids file in the same folder as the database connection file, iBase Designer opens that file immediately, even if it is the wrong security file.

  • If there is no .ids file, a Security File browser is displayed for you to locate the moved security file and click Open.

When you open the database, log on as a user with the Security Administrator role. iBase Designer then recognizes that the security file is in a new location, and asks if you want to store that new location in the database.

An example message:

Incorrect Security File
You normally connect to this database via a different security file
(\\SERVER\databases\my_security.ids).
Are you sure you want to connect via this security file
(\\SERVER2\databases2\my_security.ids)?

If you confirm that you want to use the new file, iBase Designer stores the location. You can close the database immediately, or make any changes that you want.

If you have other database connection files and databases linked to this security file, you can open and close them for update now, without needing to log on each time.

Moving or renaming the database connection file

You can move or rename database connection files, but you should not copy them to client workstations. After you move or rename a database connection file:

  • In iBase Designer, open the database connection file in its new location. The new location is stored in the database. There is no accompanying message.

Note: Renaming a database connection file prevents any existing Analyst's Notebook charts from accessing that database.

Note: If your organization builds up maps or Analyst's Notebook charts that use data from multiple iBase databases, the name of the database connection file must be unique.

Moving the SQL Server database

To move a Microsoft SQL Server database to another server:

  1. Use Microsoft SQL Server Backup and Restore to copy the database from server to server. You must use the same name for the database on the new server as it had on the old server.

    Note: It is possible to rename the database if it is the main database containing the entity and link data. For details, see SQL Server Database Names. You cannot rename the database containing the security data.

  2. Use the Database Configuration utility to open the database connection file that connects to the database, and update the connection details for the new server. See Managing SQL Server Connection Settings.

    Note: If the security (.ids) file has been upsized, you might need to provide the Database Configuration utility with the database password in order to remake the connection with the security database. See the next section for information.

  3. If you are using Bulk Import, Alerting, or Search 360 for this database, you must set up the new server. See:

Note: If you move a full-text indexed database to another server, you must also install the Microsoft Search Service on the new server if you want to continue to use Full-Text Search.

Updating an upsized security file

An upsized security (.ids) file stores the location of the SQL Server database that contains the security data. If, after you copy the database to a new server, the .ids file no longer has access to the old environment, the Database Configuration Utility displays this message:

iBase was unable to connect to the security database. Please enter the database password for this security file to confirm that you have security administrator permissions.

To find the password and open the database on the new server:

  1. In the old environment, open the .idb connection file in iBase Designer.

  2. Go to Tools > Feature availability > Options.

  3. In the Options dialog, click the Advanced tab and make a note of the value in the Database Password field.

  4. Return to the new environment and launch the Database Configuration Utility.

  5. Open the .ids file and enter your administrator credentials. This results in the message requesting the password.

  6. Enter the password that you noted in Step 3.

  7. Click OK to open the security database in your new environment.