Database Server Setup for SQLServer on Windows¶
- Database Server Setup for SQLServer on Windows
- Prerequisites
- Prepare Database Instance for FWD Authentication
- Create the custom_db Database
- FWD and SQL Server collations
- Create the Login and FWD User for SQL Authentication
- Compile an Install CLR Support for FWD's Server-Side Functions and Operators (pl/java Equivalent)
- Installing custom_db Schema to Freshly Created Database
- Run the Import
- Runtime Configuration
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 copyingsqljdbc4.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 builtp2j.jar
for creating the assemblies that will handle server-side functions and operators (pl/java equivalent). If the name of the database iscustom_db
then, in the conversion folder you should find theddl/schema_table_custom_db_sqlserver2012.sql
andschema_index_custom_db_sqlserver2012.sql
. Make sure that inp2j.cfg.xml
theddl-dialects
parameter undercfg/schema/namespace/parameter
is configured at least withsqlserver2012
.
Prepare Database Instance for FWD Authentication¶
Authenticate inMicrosoft 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 chooseNew Database...
item- enter the name of the database (
custom-db
); - set the
Recovery model
toSimple
(inOption
page); - set the
Auto Shrink
flag toTrue
(also inOption
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.
- enter the name of the database (
- 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 chooseNew 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".
- enter
- 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 forcustom_db
database; - check
db_datareader
,db_datawriter
anddb_ddladmin
(public
should already be checked).
Be sure thedb_denydatareader
anddb_denydatawriter
are NOT checked.
- check the mark in front of
- 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 injdbc-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.