SQL Server Connections
By default, SpeedBase connects to its built-in, file based database and does not require a server machine or installation of a server side software.
Starting from version 4.0 and above, SpeedBase supports connection to a remote SQL server so you may keep your database on an SQL server.
This option allows you to remotely connect to your SQL server from anywhere around the world via internet.
Before evaluating this option, make sure that you have reviewed all other available connection options given on this page
as this option requires more technical skills to setup and maintain.
Requirements & Remarks
- This options is available in SpeedBase version 4.0 and later versions.
- This option is available only if your license covers "SQL Server Connectivity" component, otherwise you may need to purchase it separately.
- SpeedBase is capable of migrating your local database to your SQL server automatically. Beware that however, migration is one way -from local database to SQL server- only.
If at a later time, you decide to return to your local database, there is no migration function to move your up-to-date database on SQL server back to local database.
- You, or an IT professional in your company must have the necessary skills and technical knowledge to install, configure, manage and backup your SQL server.
You must have a running SQL server installation on a server machine which was configured to accept remote connections.
You may however avoid most of this technical burden by subscribing to a hosted SQL server solution at the expense of the privacy.
- Backup features of SpeedBase are not available while connected to an SQL server.
- File attachments cannot be migrated to SQL server.
- Microsoft SQL server 2005 and above editions are supported. The free version (Express edition) is also perfectly sufficient for most small business applications.
Caution!
SpeedBase does NOT install or manage SQL server software for you. It also does NOT backup your database on SQL server.
You must have the required skills and technical knowledge to install, manage your SQL server and deploy an appropriate backup solution.
Technical support is only provided for the issues that are related to SpeedBase product and does not cover sql server specific issues.
New Users: How to Start Using SpeedBase with a SQL Server Database?
If you are an existing customer and would like to migrate your already established SpeedBase database to SQL Server, please skip to the next section.
Beware that SpeedBase does not recognize or manage pre-existing data on your SQL server created by any other software or means. SpeedBase
does manage data only created by itself. So if you have pre-existing data and would like to manage that data now with SpeedBase, you must first
design a new database with SpeedBase from scratch and then import your pre-existing data to this new database.
After installation of SpeedBase, an empty, built-in database was created. You may either immediately migrate it to SQL server and start designing it there, or,
design it first and migrate later. You may also continue developing it further after migration.
The steps required are given below:
- Migrate your current database to SQL Server as described in the next section,
- Export your pre-existing data to either CSV or XLS format,
- Make sure that you have created the necessary fields and other design elements on your database so it can accept your pre-existing data,
- Import your data back to your SpeedBase database created on SQL server via SpeedBase import window.
How to Migrate Data on Your SpeedBase Database to a SQL Server Database?
Follow the steps given below to configure SpeedBase to use your SQL server to manage your database:
- Make sure that your SQL server is configured properly to accept remote connections.
- Open firewall settings of your server machine and make sure that the port numbers used by your SQL server are enabled.
- Open the management software of your SQL server and create a new database, also assign a user with full modification
rights to this database. If necessary, consult documentation of your management software for these tasks.
Do not attempt to migrate to a pre-existing database with pre-existing data on SQL Server as this can have unpredictable consequences.
- Start SpeedBase, open "connection" window, select "SQL Server" from "database type" selection box.
- Enter connection information and then click "Test Connection" to verify that SpeedBase is able to connect to your SQL server.
If connection fails, review the previous steps given above.
- Click "migrate" button to start the transferring of all your data to the SQL server. This may take some time depending on the
size of your data. Once finished, the database on your SQL server will be an identical copy of your local database.
- Save changes. You are now connected to your SQL Server database. All changes you make from now on will be done on your SQL server.
- If you hold a multiuser license, go to each of other client computers and just adjust the connection settings so they all connect to your SQL
server. Do NOT initiate the migration again.
- Make sure that you have read and followed advises in the next section about managing file attachments.
Caution! File attachments are NOT migrated to SQL Server. (read next section)
If for some reason you decide to redo the migration of your database, you must first delete the database created on your SQL server
and then create a new database.
If for some reason you decide to return to your built-in database, you may do so by adjusting the connection settings. Beware that
you may only return to the latest state of your local database just before the migration. It is NOT possible to migrate your current data in the
opposite direction i.e. from SQL server database to the built-in database.
How to Manage File Attachments After Migrating to a SQL Server Database?
Note that, even after migrating to SQL server, existing file attachments remain in their current folder location and configuration of fields of file/image
type does not change. The necessary steps to follow depends on the preferred configuration of file fields:
Save Method: Save As Link:
If you have one or more file fields configured to this option, file attachments will still be accessed from their original folder location after migration. Make sure that you have
a backup solution for those files as backing up your SQL server database does not backup them.
Save Method: Copy To Local Data Folder:
If you have one or more file fields configured to this option, file attachments will still remain in and accessed from the data folder location after migration.
So make sure that:
- Your data folder is located on a shared network drive,
- Connection settings on each of the user's machine was set to the same data folder location on a shared folder.
- You have a backup solution to periodically backup your data folder.
This help page describes how to move your data folder to a new location.
Save Method: Copy To Remote Database:
This option is only enabled when you are connected to a SQL server database.
If you have one or more file fields configured to this option, file attachments are saved to your SQL server database, so backing up your SQL server database will also backup them as well.
Advanced Users: How to Share Your Existing Data on Your SQL Server With 3rd Party Software or Server-Side Scripts?
It's technically possible for 3rd party software to read and modify -data on user tables only- in a SpeedBase database on SQL Server.
However extreme caution should be exercised to avoid modification of system tables and database structure.
Caution!
The database design of the tables and fields created by SpeedBase should never ever be modified by any 3rd party software or script.
If you need to modify a user table, add/remove fields, change field properties etc. you must do it using Speedbase application interface only.
The system tables of SpeedBase (tables which are used for configuration) should never be modified by other software, neither at design level nor data level.
Doing so may easily result in unpredictable issues, loss of data or corrupted database. No technical support will be provided in such cases.
If required, access and modification of data on USER tables by 3rd party software should be done by executing simple SELECT/CREATE/UPDATE/DELETE queries only.
A table is a user table if it was created as a catalog via SpeedBase user interface.