How to Deploy a Relational Database |
  
|
How to Deploy a Relational Database |
|
You can directly connect to SQL and Oracle database.
Deploying a relational database includes connection and publication.
Connection
To add a connection to a relational database:
1.In the Paths tab, select a free directory number or a number you want to change the assignment of
2.Click Server and select SQL Server or Oracle; this opens a dialog box
3.Fill out the server name (full path)
4.Fill out the database name
5.Check the kind of authentication; in the case of SQL Server Authentication, sign out
6.With Windows Authentication, click the Test button
7.To create an article, click Create Article; the directory has now the server name for full path and has a specific pictogram (globe)
8.To publish the database, click Create Indexes.
By default the qdv database inherits the collation (configuration setting that determines how the database engine should treat character data) from the SQL database; to change this, select Select database collation.
To connect through web services, click Web Services. For several databases/servers at once and server credentials insulated from user credentials, download and run http://www.iaes-software.com/downloads/WebServiceForQDVDatabases.exe.
A database can be published to a local server in various ways. To be published in QDV7, the databases must already contain at least one article for each manufacturer to be published.
To insert the columns to the table to be published, check the box Add included columns.
To show the manufacturer views, check the box Publish databases by all manufacturer. It is optional to check the box Delete all current published databases.
The table of published databases shows in Management Studio:
The xTendedFieldData table contains one row for each free field of each article. For details, ask for How to create view in SQL article databases.docx.
To speed up the access, select Create Indexes. In the dialog box, select a key among the five basic fields; this will delete and recreate the indexes.
For reference on indexes, visit http://use-the-index-luke.com
Performance Issues
To improve the performance of sorting/searching/filtering in the article database, include ALL the columns you use regularly in the articles viewer.
To enhance the indexes, add therein columns in addition to the key column ; as a result the indexes address more queries.
An index that includes all the invoked columns, whether a key or not, significantly improves the query performance; the physical table is not read, which results in fewer accesses to the disks.
Avoid adding unnecessary columns into indexes as adding too many columns, whether a key or not, may result in the following:
▪More disk space is required to store the index
▪Maintaining the index may increase the time it takes to perform edits such as insertion, deletion, updating
In summary, assess whether the benefits in the queries execution exceed the edition performance loss and the need for more disk space.
NOTE With more complex conditions, the Article database still works; however large Article databases work slowly and the indexes must be created manually in each case.
The SQLite databases are transportable, but not used by trades.
To use an SQLite database, run <installation folder>MigrateQDVDatabasesToSQLServer.exe. Refer to Migrating from SQLite databases to SQL Server.
Connect SQL Server to the target. Then click Migrate Article databases.
DESCRIPTION and INDEX SIZE OVERFLOW
By default, MigrateQDVDatabasesToSQLServer.exe truncates the description when it exceeds 800 characters, a limit imposed by most SQL Server versions. The console shows an overflow message.
To avoid truncation, open MigrateQDVDatabasesToSQLServer.exe.config and enter a higher value at the end (DescriptionMaxLenght setting).
The resulting database can be published without recreating indexes. In case you select an option Create View on Manufacturer/UserDefinedField/Family, the ReCreateArticleIndexes setting must be set to True. The console may show an overflow message.