Similar presentations:
Microsoft SQL Server 2014
1.
Microsoft SQL Server 2014 Express& EPLAN platform
EPLAN-Product-IT-Support / FOS / 2015
1
2.
Microsoft SQL Server & EPLAN platformOverview
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 DownloadEPLAN-Product-IT-Support / FOS / 2015
3
4.
Microsoft DownloadMicrosoft 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 InstallationEPLAN-Product-IT-Support / FOS / 2015
5
6.
SQL Server InstallationNew 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 installationLicense Terms
EPLAN-Product-IT-Support / FOS / 2015
7
8.
SQL Server InstallationUpdates
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 installationPreparation for the setup
EPLAN-Product-IT-Support / FOS / 2015
9
10.
SQL Server installationFeature 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 InstallationDescription 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 installationDefault instance
EPLAN-Product-IT-Support / FOS / 2015
12
13.
SQL Server installationNamed instance EPLAN
A clear assignment of the SQL databases to the system.
EPLAN-Product-IT-Support / FOS / 2015
13
14.
SQL Server InstallationService 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 InstallationServer configuration
The „mixed mode“ provides you with all the possibilities of authentification.
EPLAN-Product-IT-Support / FOS / 2015
15
16.
SQL Server installationData directories
The data directories can be adjusted here according to your system environment.
EPLAN-Product-IT-Support / FOS / 2015
16
17.
SQL Server installationInstallation 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 installationComplete installation
EPLAN-Product-IT-Support / FOS / 2015
18
19.
The basic configuration ofMS-SQL Server 2014
Express
EPLAN-Product-IT-Support / FOS / 2015
19
20.
The basic configuration of MS-SQL Server 2014 ExpressSQL 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 ExpressSQL 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 databasesCreate 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 databasesCreate 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 databasesCreate 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 databasesCreate 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 StudioDatabase control,
User logins / Roles
&
SQL Full-text index
EPLAN-Product-IT-Support / FOS / 2015
26
27.
SQL Management studioStart 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 studioControl 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 studioSQL 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 ManagementstudioSQL Eplan Parts editor
EPLAN-Product-IT-Support / FOS / 2015
30
31.
SQL Management studioSQL Eplan Parts editor
EPLAN-Product-IT-Support / FOS / 2015
31
32.
SQL Management studioSQL Eplan parts reader (nonadministrativ)
EPLAN-Product-IT-Support / FOS / 2015
32
33.
SQL Management studioSQL Eplan parts reader (nonadministrativ)
EPLAN-Product-IT-Support / FOS / 2015
33
34.
SQL Management studioSQL Eplan translation editor
EPLAN-Product-IT-Support / FOS / 2015
34
35.
SQL Management studioSQL Eplan translation editor
EPLAN-Product-IT-Support / FOS / 2015
35
36.
SQL Management studioSQL Eplan translation reader (nonadministrativ)
EPLAN-Product-IT-Support / FOS / 2015
36
37.
SQL Management studioSQL Eplan translation reader (nonadministrativ)
EPLAN-Product-IT-Support / FOS / 2015
37
38.
SQL Management studioSQL Eplan project management user
EPLAN-Product-IT-Support / FOS / 2015
38
39.
SQL Management studioSQL Eplan project management user
EPLAN-Product-IT-Support / FOS / 2015
39
40.
SQL Management studioSQL 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 studioSQL 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 studioSQL 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 studioSQL 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-DatabaseUpdate
Preconditions
EPLAN-Product-IT-Support / FOS / 2015
44
45.
EPLAN Database UpdateThe 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 UpdateExample of database roles for EPLAN_ADMIN
EPLAN-Product-IT-Support / FOS / 2015
46
47.
EPLAN Tips & TricksDeactivate 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 & TricksDeactivate 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 & TricksDeactivate the „Auto Close“ function of databases
Set the „Auto close“ to „false“ in the options of the database.
EPLAN Consulting / Schreibmüller / 2014
49