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 		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/05/06 11:53]
kopro [Develop CzechIdM with MsSQL and a docker]
Line 1: Line 1:
 ====== MsSQL: Run CzechIdM with MsSQL database ====== ====== MsSQL: Run CzechIdM with MsSQL database ======
 +{{tag> sqlserver sql mssql install database supported drivers}}
  
 The tutorial describes how to run CzechIdM with a Microsoft SQL Server. When following the tutorials [[tutorial:adm:server_preparation|Server preparation]] and [[tutorial:adm:czechidm_installation|CzechIdM installation]] please do skip all the settings and setup related to database. The tutorial describes how to run CzechIdM with a Microsoft SQL Server. When following the tutorials [[tutorial:adm:server_preparation|Server preparation]] and [[tutorial:adm:czechidm_installation|CzechIdM installation]] please do skip all the settings and setup related to database.
Line 5: Line 6:
 <note important>The tutorial doesn't describe how to install an MsSQL database. For development you can install MsSQL in a docker, see the section MsSQL in a docker.</note> <note important>The tutorial doesn't describe how to install an MsSQL database. For development you can install MsSQL in a docker, see the section MsSQL in a docker.</note>
  
 +<note important>**Don't forget** to run these queries:
 +
 +-----
 +ALTER DATABASE bcv\_idm\_storage SET READ\_COMMITTED\_SNAPSHOT ON;
 +
 +ALTER DATABASE bcv\_idm\_storage SET ALLOW\_SNAPSHOT\_ISOLATION ON;
 +-----
 +
 +Otherwise you risk deadlock on database.</note>
 =====  Setup MsSQL with CzechIdM =====  =====  Setup MsSQL with CzechIdM ===== 
  
 ==== 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 27:
 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 33:
 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 56:
 </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 76:
 </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 97:
   * *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 ===== 
 +Beware this is recommended only for develop.
  
-Run lasted MsSQL database (change SA-PASSWORD):+Run lasted MsSQL database (change <SA-PASSWORD> with your 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. Just copy and create file with defined sql queries, 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