Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision Both sides next revision
tutorial:adm:mssql_database_support [2019/01/29 08:01]
kotisovam [MsSQL: Run CzechIdM with MsSQL database] 		tutorial:adm:mssql_database_support [2019/01/29 08:16]
kotisovam
Line 9: Line 9:
 ==== Create database ==== ==== Create database ====
  
-There is script for setup database for CzechIdM. Please follow these instructions. If you skip some query CzechIdM should not work correctly.+There is a script for setting up a database for CzechIdM. Please follow these instructions. If you omit any of the statements, CzechIdM might not work properly.
 <code sql> <code sql>
 -- create database -- create database
Line 17: Line 17:
 ALTER DATABASE bcv_idm_storage SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE bcv_idm_storage SET READ_COMMITTED_SNAPSHOT ON;
 GO GO
--- allow snapshot isolation - setting for lock escalation+-- allow snapshot isolation - setting up a lock escalation
 ALTER DATABASE bcv_idm_storage SET ALLOW_SNAPSHOT_ISOLATION ON; ALTER DATABASE bcv_idm_storage SET ALLOW_SNAPSHOT_ISOLATION ON;
 GO GO
Line 23: Line 23:
 USE bcv_idm_storage; USE bcv_idm_storage;
 GO GO
--- create login, login is used for connect to server, check policy is for development prupose (password idmadmin)+-- create login, login is used for connecting to the server, check policy is for development purposes (password idmadmin)
 CREATE LOGIN idmadmin WITH PASSWORD = 'idmadmin', check_policy = off; CREATE LOGIN idmadmin WITH PASSWORD = 'idmadmin', check_policy = off;
 GO GO
--- create user, user will be used for connect to database+-- create a user, this user will be used to connect to the database
 CREATE USER idmadmin FOR LOGIN idmadmin;  CREATE USER idmadmin FOR LOGIN idmadmin; 
 GO GO
--- create schema+-- create a schema
 CREATE SCHEMA bcv_idm_storage AUTHORIZATION idmadmin; CREATE SCHEMA bcv_idm_storage AUTHORIZATION idmadmin;
 GO GO
--- set default schema+-- set a default schema
 ALTER USER idmadmin WITH DEFAULT_SCHEMA = bcv_idm_storage; ALTER USER idmadmin WITH DEFAULT_SCHEMA = bcv_idm_storage;
 GO GO
--- grant permision for schema (grant all is deprecated)+-- grant permission for the schema (grant all is deprecated)
 GRANT ALTER, CONTROL, CREATE SEQUENCE, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, TAKE OWNERSHIP, UPDATE, VIEW CHANGE TRACKING, VIEW DEFINITION ON SCHEMA::bcv_idm_storage TO idmadmin; GRANT ALTER, CONTROL, CREATE SEQUENCE, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, TAKE OWNERSHIP, UPDATE, VIEW CHANGE TRACKING, VIEW DEFINITION ON SCHEMA::bcv_idm_storage TO idmadmin;
 GO GO
Line 46: Line 46:
 </code> </code>
  
-==== Download JDBC driver ==== +==== Download a JDBC driver ==== 
-Please download JDBC driver for MsSQL. For example: [[http://clojars.org/repo/com/microsoft/sqlserver/sqljdbc4/4.0/sqljdbc4-4.0.jar|http://clojars.org/repo/com/microsoft/sqlserver/sqljdbc4/4.0/sqljdbc4-4.0.jar]], or see section another supported drivers. +Please download a JDBC driver for MsSQL. For example: [[http://clojars.org/repo/com/microsoft/sqlserver/sqljdbc4/4.0/sqljdbc4-4.0.jar|http://clojars.org/repo/com/microsoft/sqlserver/sqljdbc4/4.0/sqljdbc4-4.0.jar]], or see the section other supported drivers. 
  
-The driver you must be placed for example into */opt/tomcat/current/lib/* tomcat external classpath, or libraries of tomcat.+The driver must be placed for example into */opt/tomcat/current/lib/* tomcat external classpath, or libraries of tomcat.
  
 ==== Add connection to property file (application.properties) ==== ==== Add connection to property file (application.properties) ====
  
-Is required add correct datasource url ans password. For exemple visit github page with [[https://github.com/bcvsolutions/CzechIdMng/blob/develop/Realization/backend/app/src/main/resources/application-dev-mssql.properties|developer profile for mssql]].+The correct data source url and password are required. To see an example, visit the github page with [[https://github.com/bcvsolutions/CzechIdMng/blob/develop/Realization/backend/app/src/main/resources/application-dev-mssql.properties|developer profile for mssql]].
  
 Example settings: Example settings:
Line 66: Line 66:
 </code> </code>
  
-Example use JDBC driver [[http://clojars.org/repo/com/microsoft/sqlserver/sqljdbc4/4.0/sqljdbc4-4.0.jar|Sqljdbc4 4.0]], if you want use another driver please setup correct url.+An example of using a JDBC driver [[http://clojars.org/repo/com/microsoft/sqlserver/sqljdbc4/4.0/sqljdbc4-4.0.jar|Sqljdbc4 4.0]], if you want to use another driver please setup the correct url.
  
-==== Setup scheduler (quartz.properties) ==== +==== Scheduler setup (quartz.properties) ==== 
-For full example please visit github page with [[https://github.com/bcvsolutions/CzechIdMng/blob/develop/Realization/backend/app/src/main/resources/quartz-dev-mssql.properties|developer profile]].+For full example please visit the github page with [[https://github.com/bcvsolutions/CzechIdMng/blob/develop/Realization/backend/app/src/main/resources/quartz-dev-mssql.properties|developer profile]].
  
-There is two propeties that is different:+There are two propeties that are different:
 <code properties> <code properties>
 org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.MSSQLDelegate org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.MSSQLDelegate
Line 87: Line 87:
   * *Not fully tested* [[https://sourceforge.net/projects/jtds/files/jtds/1.3.1/jtds-1.3.1-dist.zip/download|jTDS - SQL Server and Sybase JDBC driver]].   * *Not fully tested* [[https://sourceforge.net/projects/jtds/files/jtds/1.3.1/jtds-1.3.1-dist.zip/download|jTDS - SQL Server and Sybase JDBC driver]].
  
-===== Not Supported drivers =====+===== Not supported drivers =====
   * [[https://mvnrepository.com/artifact/com.microsoft.sqlserver/sqljdbc4/4.0.0|Sqljdbc4 4.0.0]] (this is version 4.0.0 not 4.0)   * [[https://mvnrepository.com/artifact/com.microsoft.sqlserver/sqljdbc4/4.0.0|Sqljdbc4 4.0.0]] (this is version 4.0.0 not 4.0)
  
  
-===== Develop CzechIdM with MsSQL and Docker =====+===== Develop CzechIdM with MsSQL and a docker =====
  
 Run lasted MsSQL database (change SA-PASSWORD): Run lasted MsSQL database (change SA-PASSWORD):
    $ docker run --name=test-mssql -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<SA-PASSWORD>' -p 1433:1433 -d microsoft/mssql-server-linux:latest    $ docker run --name=test-mssql -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<SA-PASSWORD>' -p 1433:1433 -d microsoft/mssql-server-linux:latest
  
-Copy initial script (init script is described upper, in example has script file name import.sql) +Copy the initial script (init script is described above, in our example the name of the script file is import.sql) 
    $ docker cp import.sql test-mssql:/import.sql    $ docker cp import.sql test-mssql:/import.sql
  
-Run init script in docker (change SA-PASSWORD):+Run the init script in the docker (change SA-PASSWORD):
    $ docker exec test-mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P <SA-PASSWORD> -d master -i /import.sql    $ docker exec test-mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P <SA-PASSWORD> -d master -i /import.sql
  
  
  • by doischert