Project

General

Profile

Database Server Setup for SQLServer on Windows

At this time, the FWD project has some support for Microsoft SQLServer as its backing Relational Database Management System (RDBMS) but it is not yet complete. We are working on bringing this dialect on-par with PostgreSQL, which is the main RDBM System.

Prerequisites

This is a list of minimum software components that are required by FWD to be present on the machine the server will be installed:
  • SQL Server
    The minimum version FWD needs is SQL Server 2012 because FWD uses sequences and previous releases (like SQL Server 2008) do not support them. The recommended editions are Standard or Enterprise. The Express editions are not useful because of the limitations, the most important being the maximum size of the database.
    The actual process of the installation of the database engine is out of the scope of this book and is not presented here. Just make sure the dedicated instance has mixed authentication activated.
  • Java and SQL Server driver
    If the same machine runs the FWD server and the database engine, then the JDK is already installed. We need just to add the SQL Server driver. Download Microsoft JDBC Driver 4.0 for SQL Server from http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774. Make sure SQL Server driver service system wide accessible by copying sqljdbc4.jar to %JRE_HOME%\lib\ext.
    If this is a dedicated box (FWD connects remotely from other machine), skip this step.
  • FWD conversion artifacts
    We assume your project was already converted with FWD and the generated classes were compiled. For the SQL Server installation we need the dedicated DDL scripts for crating the database schema and the successfully built p2j.jar for creating the assemblies that will handle server-side functions and operators (pl/java equivalent). If the name of the database is custom_db then, in the conversion folder you should find the ddl/schema_table_custom_db_sqlserver2012.sql and schema_index_custom_db_sqlserver2012.sql. Make sure that in p2j.cfg.xml the ddl-dialects parameter under cfg/schema/namespace/parameter is configured at least with sqlserver2012.

Prepare Database Instance for FWD Authentication

Authenticate in Microsoft SQL Server Managment Studio to the SQL Server instance using a security admin user. Then:
  • make sure the Server Authentication is set to mixed mode (SQL Server and Windows Authentication mode)
  • make sure the instance accepts TCP/IP (Using SQL Server Manager, a restart of the instance is required!)

Create the custom_db Database

  • Right click the Databases node and choose New Database... item
    • enter the name of the database (custom-db);
    • set the Recovery model to Simple (in Option page);
    • set the Auto Shrink flag to True (also in Option page);
    • set the initial PRIMARY file to approximate database size. This will boost the performance a little as the SQL will not be forced to do supplementary file growth. Allow the data file to Autogrow in 10% increments for future inserting of data;
    • depending of your disk resources configure the log file.
  • Confirm. The database is created.

FWD and SQL Server collations

In order to ensure that text data sorts same as Progress, we should use a collation that fully matches the way P4GL sorts strings. The SQL Server only comes with a fixed set of (about 80) collations, they are indexed functions. Additionally, Windows collations can be used for internationalization. Unfortunately, among all of them there is no exact match for the Progress collation. However, based on comparative tables, the best of all available is SQL_Latin1_General_CP1_CI_AI. We are working in this area and hopefully will soon find a better solution.

Create the Login and FWD User for SQL Authentication

  • Open the Security node, right-click the Logins item and choose New Login...
    • enter custom_usr login name;
    • select SQL Server authentication;
    • enter and confirm the password for sql authentication;
    • set the Default language as you please;
    • optionally, select the the Default database as "custom_db".
  • Switch to User Mapping page
    • check the mark in front of custom_db database, the lower panel allows you to change the membership of the mapped user for custom_db database;
    • check db_datareader, db_datawriter and db_ddladmin (public should already be checked).
      Be sure the db_denydatareader and db_denydatawriter are NOT checked.
  • Confirm. At this moment the database security access configured.

Compile an Install CLR Support for FWD's Server-Side Functions and Operators (pl/java Equivalent)

As noted in PostgreSQL Database Server Setup, FWD needs server side functions to duplicate (known as User Defined Functions - UDFs) in order to ensure the converted application behaves identically to the original application. Since SQL Server does not support directly Java code we use for PL/Java and PostgreSQL database we are are constrained to package the implementation in Windows native assemblies that can be loaded into databases and executed by SQL Server.

Because our development systems run Ubuntu, the following steps are executed on Linux machine. When an action is needed to be executed on Windows (with SQL Server installation), it will be expressly mentioned.

To have FWD functions in SQL queries we need to create UDF functions that map to CLR. The functions are decorated to overcome the overloaded method constraint. These functions are in fact just synonyms of the 'external' functions defined in P2JWrappers assembly. The p2j2clr is composed of two C# classes that delegates the decorated SQL function to mapped overloaded method from p2jpl assembly (a datatype change is performed if needed from native CLR to IKVM datatype). p2jpl assembly is obtained using ikmvc compiler from p2jpl.jar.

0. Acquire Last Version of Software Needed: IKVM and mono

At this moment we only support Java8 code. As result the we need to use IKVM 8. For this I used with success the IKVM.NET 8.1 Release Candidate, announced on http://weblog.ikvm.net/#ab36dc873-097a-445d-b61a-3501eca38f5e and available for download at http://www.frijters.net/ikvmbin-8.1.5717.0.zip . Download the ikvmbin-8.1.5717.0.zip (~21 MB) to working directory.
Unzip it:

unzip ikvmbin-8.1.5717.0.zip

A new folder ikvmbin-8.1.5717.0 will be created.
Do the same on the Windows machine where the SQL Server is installed. We used the system drive (C:) so the path to IKVM installation is c:\ikvmbin-8.1.5717.0\

At this level create working directory structure:

mkdir src
mkdir log
mkdir out
mkdir bin

We cross-compile the assemblies on Linux using mono. This utility is available on standard Ubuntu repositories (package mono-complete).

1. Add New Assemblies to GAC

In the bin directory, create a script file install_local_ikvm_to_gac.sh with following content:

#!/bin/bash
# put the ikvm from current directory to gac
# must be run as admin

for line in $(find . -iname '*.dll'); do 
   gacutil -i "$line" 
done

Set is as executable and run it from IKVM bin directory like this:

cd ikvmbin-8.1.5717.0/bin
sudo ../../bin/install_local_ikvm_to_gac.sh
cd ../..

The new assemblies will be added. Two assemblies are known to fail the GAC registration: ikvm-native-win32-x64.dll and ikvm-native-win32-x64.dll.

This step is required only once, then the assemblies can be generated multiple time. Redo this step only if IKVM is updated to a new version.

2. Prepare the Sources from a Pre-built FWD Project

Starting with FWD revision 10961, the needed files can be found in src/com/goldencode/p2j/persist/pl.

cp <P2J_HOME>/build/lib/p2jpl.jar lib/
cp <P2J_HOME>/src/com/goldencode/p2j/persist/pl/*.cs src/

The src/ folder should contain the following five files: AssemblyInfo.cs, Common.cs, ErrorHandler.cs, Functions.cs and Operators.cs.
Now we are ready for conversion and cross-compile.

3. Convert the p2jpl Assembly Using IKVM Compiler on mono

Execute:

mono ikvm-8.1.5717.0/bin/ikvmc.exe -target:library lib/p2jpl.jar -out:out/p2jpl.dll -version:0.0.2.2 1>log/p2jpl_conv.outlog 2>log/p2jpl_conv.errlog

The log will display some warnings but hopefully no errors. The p2jpl.dll file should be created.

4. Cross-Compile the p2j2clr Wrapper Assembly

We use gmcs version of the x-compiler because we need the references for .net 2.0.

gmcs -r:cscompmgd.dll -r:System.Windows.Forms -r:./ikvm-8.1.5717.0/bin/IKVM.OpenJDK.Core.dll -r:./ikvm-8.1.5717.0/bin/IKVM.OpenJDK.Jdbc.dll -r:out/p2jpl.dll -pkg:dotnet -target:library -out:out/p2j2clr.dll src/*.cs 1>log/p2j2clr_compile.outlog 2>log/p2j2clr_compile.errlog

You should find both assemblies (p2j2clr.dll and p2jpl.dll) in the out directory. Transfer these files to the machine where Microsoft SQL instance will run.

5. Add Assemblies to custom_db SQL Instance

On Windows machine open the SQL Server Management Studio (or connect with your preferred SQL client) and run the following SQL script:

use custom_db
go

alter database custom_db set trustworthy on
go

sp_configure 'clr enabled', 1
go

reconfigure
go

create assembly [system.drawing] from 'c:\Windows\Microsoft.NET\Framework\v4.0.30319\system.drawing.dll' with permission_set = unsafe
go

create assembly [system.windows.forms] from 'c:\Windows\Microsoft.NET\Framework\v4.0.30319\system.windows.forms.dll' with permission_set = unsafe
go

create assembly [IKVM.Runtime] from 'c:\ikvmbin-8.1.5717.0\bin\IKVM.Runtime.dll' with permission_set = unsafe
go

create assembly [IKVM.OpenJDK.Jdbc] from 'c:\ikvmbin-8.1.5717.0\bin\IKVM.OpenJDK.Jdbc.dll' with permission_set = unsafe
go

create assembly [p2jpl] from '<path-to-generated>\p2jpl.dll' with permission_set = unsafe
go

create assembly [p2j2clr] from '<path-to-generated>\p2j2clr.dll' with permission_set = unsafe
go

Installing custom_db Schema to Freshly Created Database

Now that we have a dedicated user, we can authenticate to SQL instance with custom_usr user.
  • Open a connection to SQL instance: sqldev01\custom_instance;
  • Open the ddl\schema_table_custom_db_sqlserver2012.sql file generated by FWD when the project was converted;
  • Hit F5 (Execute). In a few seconds the queries should be executed and
    Command(s) completed successfully.
    should be displayed.

Unless there won't be any data imported to this database DO NOT execute the scripts from schema_index_custom_db_sqlserver2012.sql. The indexes will be automatically created by the import process. In fact, the import process will fail if indexes were added to database schema.

Note that at the beginning of the schema_table_custom_db_sqlserver2012.sql the full set of UDF required by FWD are defined. They map the functions exposed from assemblies created in a previous step.

At this moment the schema is installed into the SQL instance and is ready to import data and FWD can use its pl functions in SQL queries.

Run the Import

Create the link to database dump or copy the Progress export into data folder. Again, you need to be logged in as an Administrator

   cd data
   mklink /D dump ..\..\data\dump

In the data/dump you should have a file for each table from your database (*.d), one _seqvals.d with sequence values (if any) and one _user.d with user credentials.

Follow the instructions in the Data Migration chapter.

Depending on the database size, configuration and hardware capability the import can take up to a couple of hours. Check the data import log file. If any warnings or non-fatal errors were encountered, they will be listed in there. Non-fatal errors may be present if the imported data set break the 900 bytes index limitation of SQL Server.

Runtime Configuration

To let FWD server know at runtime how to access the new database we need to configure the following entries in the directory:
  • the SQL dialect:
    Set the /server/custom_server/database/custom_db/hibernate/dialect = "com.goldencode.p2j.persist.dialect.P2JSQLServer2012Dialect".
  • the SQL driver:
    Set the /server/custom_server/database/custom_db/hibernate/connection/driver_class = "com.microsoft.sqlserver.jdbc.SQLServerDriver". This class is contained in jdbc-sql4.jar noted in {Prerequisites}.
  • the SQL authentication:
    The credentials were added in Create the login and FWD user for SQL authentication. Now we just need to make them available for FWD by setting the /server/custom_server>/database/custom_db/hibernate/connection/username = "custom_user" and /server/custom_server>/database/custom_db/hibernate/connection/password = "custom_password".
  • the jdbc connection string:
    Set /server/custom_server/database/custom_db/hibernate/url="jdbc:sqlserver://sqldev01;instanceName=custom_instance;databaseName=custom_db"/>

The resulting directory should look like this:

  <node class="container" name="">
    <node class="container" name="server">
      <node class="container" name="custom_server">
        <node class="container" name="database">
          <node class="container" name="custom_db">
            <node class="container" name="hibernate">
              <node class="string" name="dialect">
                <node-attribute name="value" value="com.goldencode.p2j.persist.dialect.P2JSQLServer2012Dialect"/>
              </node>
              <node class="container" name="connection">
                <node class="string" name="driver_class">
                  <node-attribute name="value" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
                </node>
                <node class="string" name="url">
                  <node-attribute name="value" value="jdbc:sqlserver://sqldev01;instanceName=custom_instance;databaseName=custom_db"/>
                </node>
                <node class="string" name="username">
                  <node-attribute name="value" value="custom_user"/>
                </node>
                <node class="string" name="password">
                  <node-attribute name="value" value="custom_password"/>
                </node>
              </node>

For more details on how to configure FWD's directory.xml, please refer to Server Directory chapter.


© 2004-2017 Golden Code Development Corporation. ALL RIGHTS RESERVED.