Microsoft SQL Server 2014

1.

Microsoft SQL Server 2014 Express
& EPLAN platform
EPLAN-Product-IT-Support / FOS / 2015
1

2.

Microsoft SQL Server & EPLAN platform
Overview
Download - Microsoft SQL Server 2014 Express with
Advanced Services
Installation - Microsoft SQL Server 2014 Express
Basic configuration - Microsoft SQL Server 2014
Express
SQL Management Studio – Database control, user
logins / roles, SQL- full-text index
EPLAN SQL-Datenbank Update - preconditions
Tips & tricks
EPLAN-Product-IT-Support / FOS / 2015
2

3.

Microsoft Download
EPLAN-Product-IT-Support / FOS / 2015
3

4.

Microsoft Download
Microsoft SQL Server 2014 Express with Advanced Services
Microsoft provides different download packages for the installation. Which one is the
right one?
Express with advanced services
You can finde the download area of Microsoft under
http://www.microsoft.com/en-us/download/details.aspx?id=42299
Select the same language as the language for your operating system.
Please refer to the installation instructions.
EPLAN-Product-IT-Support / FOS / 2015
4

5.

SQL Server Installation
EPLAN-Product-IT-Support / FOS / 2015
5

6.

SQL Server Installation
New SQL-Server stand-alone Installation
We carry out a new SQL Server stand-alone installation.
Customers who have already installed an SQL Server 2014 can also choose to add
for example a stand-alone instance for EPLAN.
EPLAN-Product-IT-Support / FOS / 2015
6

7.

SQL Server installation
License Terms
EPLAN-Product-IT-Support / FOS / 2015
7

8.

SQL Server Installation
Updates
All necessary updates will be displayed as long as an internet connection is
available and will be included directly during the installation.
EPLAN-Product-IT-Support / FOS / 2015
8

9.

SQL Server installation
Preparation for the setup
EPLAN-Product-IT-Support / FOS / 2015
9

10.

SQL Server installation
Feature selection (necessary functions for the EPLAN platform)
Here you can
select where you
are going to create
the database.
EPLAN-Product-IT-Support / FOS / 2015
10

11.

SQL Server Installation
Description to the function selection
Starting with the Version EEP8 2.3, the Full-text search for parts management is
supported for SQL.
The documentation components constitute the help system of the SQL server and are not
relevant to the EPLAN platform.
The management tools are necessary for the administration of the server
EPLAN-Product-IT-Support / FOS / 2015
11

12.

SQL Server installation
Default instance
EPLAN-Product-IT-Support / FOS / 2015
12

13.

SQL Server installation
Named instance EPLAN
A clear assignment of the SQL databases to the system.
EPLAN-Product-IT-Support / FOS / 2015
13

14.

SQL Server Installation
Service configuration
The SQL Browser is necessary for the communication with client. This is not
included in the default installation of SQL Server Express. Therefore the service
must be changed to „automatic”.
EPLAN-Product-IT-Support / FOS / 2015
14

15.

SQL Server Installation
Server configuration
The „mixed mode“ provides you with all the possibilities of authentification.
EPLAN-Product-IT-Support / FOS / 2015
15

16.

SQL Server installation
Data directories
The data directories can be adjusted here according to your system environment.
EPLAN-Product-IT-Support / FOS / 2015
16

17.

SQL Server installation
Installation progress
The installation is in progress, if error messages appear, you will find the
suggested solution in „Details“ when the installation is completed.
EPLAN-Product-IT-Support / FOS / 2015
17

18.

SQL Server installation
Complete installation
EPLAN-Product-IT-Support / FOS / 2015
18

19.

The basic configuration of
MS-SQL Server 2014
Express
EPLAN-Product-IT-Support / FOS / 2015
19

20.

The basic configuration of MS-SQL Server 2014 Express
SQL Server Configuration Manager
It is set in the basic configuration of SQL-Express that connections to other
clients cannot be accepted.
To change this setting, you have to start the SQL Server configuration
manager:
The first required condition has been already configured in the setup. The
SQL Server Browser must be started mandatorily. The related services
should have the status „runnung“.
EPLAN-Product-IT-Support / FOS / 2015
20

21.

The basic configuration of MS-SQL Server 2014 Express
SQL Server Configuration Manager
In the next step the SQL Server has to be adjusted in the network configuration.
Here you will find the protocols for the instance which was named as „EPLAN“
The communication via protocol „TCP/IP“ has to be changed to „enabled“ here.
Then you have to restart the SQL Service of the instance so that the settings
can take effect.
EPLAN-Product-IT-Support / FOS / 2015
21

22.

Create the EPLAN standard databases
Create new databases in the EPLAN platform
To create the databases for the EPLAN platform, it is suggested to create them out
of EPLAN with SQL administration rights.
It is not supposed to provide the EPLAN users with administation rights in the most
companies. If there is an exception, a definition of an „EPLAN_ADMIN“ from the IT
map can be used so that the administration can be carried out.
The basis of the naming conventions can be taken from the consultiing standard
from the IT maps.
Starting from the version 2.4 (32 Bit) you can, and from 2.4 (64 Bit) you have to
manage your part-, translation- and project management databases with the SQL
Server.
EPLAN-Product-IT-Support / FOS / 2015
22

23.

Create the EPLAN standard databases
Create an SQL part database – SQL Administrator
The IT Map defines the standard for database convetions
Consulting standard database name „ EPLAN_PARTS“ and
„EPLAN_PARTS_TMP“
EPLAN-Product-IT-Support / FOS / 2015
23

24.

Create the EPLAN standard databases
Create an SQL translation database – SQL Administrator
The IT Map defines the standard for database convetions
Consulting standard database name „ EPLAN_TRANSLATE“ and
„EPLAN_TRANSLATE_TMP“
EPLAN-Product-IT-Support / FOS / 2015
24

25.

Create the EPLAN standard databases
Create an SQL project management database – SQL Administrator
The IT Map defines the standard for database convetions
Consulting standard database name „ EPLAN_PROJECTS“ and
„EPLAN_PROJECTS_TMP“
EPLAN-Product-IT-Support / FOS / 2015
25

26.

SQL Management Studio
Database control,
User logins / Roles
&
SQL Full-text index
EPLAN-Product-IT-Support / FOS / 2015
26

27.

SQL Management studio
Start the SQL Management studio
The SQL administrator has created the databases out of the ELAN platform in the
last step. The next step is to configure the user rights of the database users.
This is to be done with the SQL Management studio:
The registration here has been set during the installation. All the administrators
who are taken into account here will have the access to the management console
via Windows registration or their local sa(Server Admin).
EPLAN-Product-IT-Support / FOS / 2015
27

28.

SQL Management studio
Control the databases / clarification of permissions
All required databases are listed below:
Now it comes to the definition of access to the databases. It is possible to define
local registrations, to use the users or user groups from the AD domain of the
company.
Here is also supported by the IT map to use the local user registrations which are
defined in consulting as standard.
This user mapping must be defined in accordance with the administrator of the
company.
EPLAN-Product-IT-Support / FOS / 2015
28

29.

SQL Management studio
SQL EPLAN user registration
In the object explorer under security logins you can define a „new login“ under
the context menu.
Then we will use the registration from the consulting standards and define the
necessary roles.
EPLAN-Product-IT-Support / FOS / 2015
29

30.

SQL Managementstudio
SQL Eplan Parts editor
EPLAN-Product-IT-Support / FOS / 2015
30

31.

SQL Management studio
SQL Eplan Parts editor
EPLAN-Product-IT-Support / FOS / 2015
31

32.

SQL Management studio
SQL Eplan parts reader (nonadministrativ)
EPLAN-Product-IT-Support / FOS / 2015
32

33.

SQL Management studio
SQL Eplan parts reader (nonadministrativ)
EPLAN-Product-IT-Support / FOS / 2015
33

34.

SQL Management studio
SQL Eplan translation editor
EPLAN-Product-IT-Support / FOS / 2015
34

35.

SQL Management studio
SQL Eplan translation editor
EPLAN-Product-IT-Support / FOS / 2015
35

36.

SQL Management studio
SQL Eplan translation reader (nonadministrativ)
EPLAN-Product-IT-Support / FOS / 2015
36

37.

SQL Management studio
SQL Eplan translation reader (nonadministrativ)
EPLAN-Product-IT-Support / FOS / 2015
37

38.

SQL Management studio
SQL Eplan project management user
EPLAN-Product-IT-Support / FOS / 2015
38

39.

SQL Management studio
SQL Eplan project management user
EPLAN-Product-IT-Support / FOS / 2015
39

40.

SQL Management studio
SQL full-text index of EPLAN Parts
As a general rule, not every user can have dbOwner rights. However, all the users need
the dbOwner right in the catelog „ftc“ for server-side full-text search on the SQL server.
Otherwise they cannot user the search function.
Recommended procedure :
In the database system based on EPLAN (with sa or EPLAN_ADMIN), once 1x
data set is created, 1x indexing should be initiated (so that the catalog ftc
exists)(perform for each database, for example productive & TEMP-DB)
EPLAN-Product-IT-Support / FOS / 2015
40

41.

SQL Management studio
SQL full-text index of EPLAN Parts
Now there should be an ftc catalog on the SQL-Management-Studio in the
corresponding database under „Storage“:
Before proceeding you should check in the properties whether the indexing is
completed.
EPLAN-Product-IT-Support / FOS / 2015
41

42.

SQL Management studio
SQL full-text index of EPLAN Parts
In the next step you should make sure that all the users are allowed to use the
catalog. That means dbOwner rights should be included in the catalog
In oder to be independent of the way of registration (whether Windows or SQL
authentification), it is suggested that the „public roles“ should be given the rights to
the catalog. In this way it would be an one-off action and you don‘t have to repeat it
for every user group.
Recommended procedure:
Right click on the corresponding database and create a new query:
EPLAN-Product-IT-Support / FOS / 2015
42

43.

SQL Management studio
SQL full-text index of EPLAN Parts
Set the permission of the public roles with the following command:
copy & paste
grant CONTROL
ON FULLTEXT CATALOG :: ftc
to public
After running, the following message should appear!
The process must be repeated for all existing part databases.
The translation and project SQL databases are currently not using a full-text catalog as
the part management does.
EPLAN-Product-IT-Support / FOS / 2015
43

44.

EPLAN SQL-Database
Update
Preconditions
EPLAN-Product-IT-Support / FOS / 2015
44

45.

EPLAN Database Update
The ideal procedure when updating a database
When intalling a hotfix or updating to a higher version, the update of a database in
the EPLAN Plattform might be necessary.
The user logins which were shown earlier have no permission to it.
You have to clarify to your administrator how you are going to deal with the user
rights when an update is required.
We suggest that you should define for example a login EPLAN_ADMIN. This login
has a dbowner right for all the databases in addition. This can be assigned to e.g. a
KeyUser. As an alternative it can be assigned to the EPLAN_<Datenbank>_Writer.
Important ist that only with this right can an update be performed.
EPLAN-Product-IT-Support / FOS / 2015
45

46.

EPLAN Database Update
Example of database roles for EPLAN_ADMIN
EPLAN-Product-IT-Support / FOS / 2015
46

47.

EPLAN Tips & Tricks
Deactivate the „Auto Close“ function of databases
In a SQL Express Server the protocols are created by default in the background.
The protocols open a database and look into the database.
By default settings, the database will be then automatically closed.
Which will lead to a large number of disk activities and a low performance.
Therefore this setting should be done for every database which was created by the
customer.
EPLAN-Product-IT-Support / FOS / 2015
47

48.

EPLAN Tips & Tricks
Deactivate the „Auto Close“ function of databases
Select the database in the SQL Management studio. Right click and select
“properties”.
EPLAN Consulting / Schreibmüller / 2014
48

49.

EPLAN Tips & Tricks
Deactivate the „Auto Close“ function of databases
Set the „Auto close“ to „false“ in the options of the database.
EPLAN Consulting / Schreibmüller / 2014
49