Upsizing a Database to SQL Server

You can upsize (convert) an iBase Microsoft™ Access database to SQL Server format. You must have a backup of the original database if you want continued access to the Microsoft Access version of the database.

Before you begin

Before you can upsize a Microsoft Access database, you need:

An iBase logon for the original database with at least the Database Administrator role.
Exclusive access to the database that you are upsizing.
A backup of the iBase database that you are upsizing, or sufficient space to make a disk copy if you want the upsize process to make a copy for you.
A printout of the database statistics for the Access database— you might want to compare these with the statistics of the upsized database.
The identity (network name) of the server on which Microsoft SQL Server is running.
The login name and password of an SQL Server user that belongs to the dbcreator server role. See the Administration Center document Managing Access Control, for details of SQL Server logins.
Sufficient disk space and time to complete the operation.

Note: The upgraded databases use twice the disk space of the original iBase database.

About this task

The upsize process creates an SQL Server database and an .idb file, which contains the connection details.

Make sure that you have a backup of the database that you intend to upsize. If this is an operational database, it is a good idea to restore the backup and make sure that you can read the restored version before you complete the upsize.

You can complete the upsize from any iBase client machine. For large databases, however if possible, run this iBase Designer session on the server machine to reduce network traffic.

Note: If you are upsizing any database that is likely to exist already on the server, such as the supplied example database User Guide.idb, you may need to rename the original database (.idb) file to a name expected to be unique on the server. For example, you might rename the database file User Guide.idb to User GuideAB.idb. After the upsize is completed, rename the database connection file created by the upsize back to its original name to make sure that any report templates work. For example, you would rename the connection file User GuideAB.idb back to User Guide.idb.

Procedure

Start iBase Designer.

Note: Do not open any database.
Select File > Logon.
In the Security File browser dialog, navigate to the folder and select the security file used to secure the database you are upsizing.
Click Open.
When you successfully log on, click Cancel in the i2® iBase dialog. You cannot have the database open when upsizing.

Note: You might want to open the database briefly, to confirm that you have used the correct security file and, perhaps, from the File menu to select Database Statistics and view or print the information so that you can compare it with statistics for the database after upsizing. Close the database before you continue.

You are now ready to upsize the database:
From the Tools menu in iBase Designer, select Database Setup > Upsize > Database to SQL Server.
Select a database from the list. If necessary, select the entry More Files and click Next to display a file browser where you can locate the database.
Name the backup file or, if you do not want a disk backup file, delete the suggested name to leave an empty field. Click Next to continue. A backup is created if required.
Enter the name of the server or select it from the Server list.

Note: Do not use the aliases (local) or '.' because they refer to the client machine when the connection file is opened remotely.
Enter the logon details for the SQL Server instance on the server. Use SQL Server authentication for the upsizing, not Windows™ authentication.
See Authenticating Connections to SQL Server for details.
Click Next to continue.
Your choices are checked and any problems are reported. For example, if the database exists on the server, you must choose another server, or exit and change the name of the original database, before you restart the process. Provided there are no problems, the settings for the new database are displayed.
Check that these settings are what you want and click Finish
The upsizing process starts and progress is displayed by listing each stage with a time and success or failure.
Click Close.
When the upsize process is complete, the iBase database file is overwritten with a file of the same name and extension. For example, User Guide.idb is now a connection file to an SQL Server database, and it is likely to be significantly smaller than before. The new SQL Server database is opened automatically.
Optional: Close the database and change the name of the connection file back to the original database name.
Before you use the database, check the database properties to see that the settings in the Configuration and Advanced pages of the Properties dialog are what you expected.
If success is reported for all stages of the upsize process, there is no reason to expect problems. It is still wise to check the upsized database as described fully in Checking a Database and summarized here:
1. Select Tools > Database Administration > Schema Integrity Check. Select the new database and complete each page of the wizard.
  When you finish the wizard, the database is reopened. Close the database.
2. Select Tools > Database Administration > Link Integrity Check. Select the new database and complete each page of the wizard.
  When you finish the wizard, the database is reopened.

What to do next

If you want to use Word Search with the upsized database, you must to rebuild the index.

Note: The original index (.idx) file is no longer be used by the upsized database. However, it might be required if you plan to allow continued access to the Microsoft Access version of the database.