Ensuring that database 'bulk inserts' do not fail

during a server update

Ensuring that database bulk inserts will not fail during the Server Update:

 Following integration with new format FDB files in July 2005, the Server Update process was modified to apply new FDB data through SQL Server’s Bulk Insert function.  Bulk Inserts allow Server Updates to complete within 20 minutes or less, but do require additional authorizations.  Those authorizations are usually in place when:

• Ascend uses the 'sa' user account to access SQL Server
• The \Update folder is located on the same server as SQL Server
• Updates are run from a client installed on the database server

Here’s a review of the authorizations required to run an update:  

The Ascend client starts the Server Update process by unzipping the Update.exe and moving some of the unzipped files around.  For this step to succeed, the Windows or Network Logon ID executing the client must have full read/write/update authority over the local client installation folder, usually C:\Program Files\Ascend, and over the Application Server where the \Update folder is located.  

An Ascend client authenticates itself to SQL Server using either Windows authentication or SQL Server authentication.  Regardless of the mode of authentication, within SQL Server the ID used must be defined as a database owner.  The illustration shows the 'sa' account used by most installations.

 When the Server Update process is ready to load an FDB data file, it constructs a Bulk Insert statement from the path to the \Update folder, the name of the file to be loaded and the name of the table in which the data will be stored.  Basically, a Bulk Insert statement says, “Take the file named X from the location Y and load its contents into table Z.”  The Bulk Insert statement is passed to SQL Server for actual execution.   

When constructing a Bulk Insert statement, the path to the \Update folder is retrieved from the .ini file of the client that kicked off the Server Update.  The path must be defined in a way that can be interpreted successfully by SQL Server.  This means that a client that kicks off a Server Update may not use a mapped network drive in its .ini file’s Server Path parameter.  

In order for SQL Server to accept a Bulk Insert statement, the ID used to authenticate to SQL Server must be granted the role of Bulk Insert Administrator.  Please refer to the illustration below.  The 'sa' User is generally defined with the role System Administrator, which automatically includes the Bulk Insert Administrator role.

 After SQL Server accepts a Bulk Insert statement, the SQL Server program itself actually retrieves the data file from the \Update folder and loads the database table.  In order to access the \Update folder successfully, the Windows or Network Logon ID under which SQL Server is executing must have full read/write/update authority over the Application Server folders.  This can be an issue if the Application Server is located somewhere other than the database server.   Authentication can be ensured by granting Everyone full access to the Application Server or by specifically granting the database server’s Network Logon ID full access to the Application Server.  The illustration shows Everyone sharing an Application Server named  'Ascend-IPShare'.  

 If all these permissions are in place, the Server Update should complete successfully.