Framework:Database on SQL Server: Difference between revisions

From CoPlanner 10
Jump to navigationJump to search
← Older edit
VisualWikitext
No edit summary
No edit summary
 
(9 intermediate revisions by the same user not shown)
Line 3: Line 3:
You need to distinguish between system tables and data tables.  
You need to distinguish between system tables and data tables.  


Data tables are created via the object management. They have a COPSYS_ID as their name.
Data tables are created via the [[Framework:Object_administration|object administration]]. They have a COPSYS_ID as their name.


System tables are tables pre-defined by the CoPlanner and start with “tb_”. These are:
System tables are tables pre-defined by the CoPlanner and start with “tb_”. These are:
Line 42: Line 42:
It is possible to create the CoPlanner data tables as a view which then is given the corresponding name as in the CoPlanner object management.  
It is possible to create the CoPlanner data tables as a view which then is given the corresponding name as in the CoPlanner object management.  


For this purpose, there is a specific menu item in the CoPlanner under Administration – Create views. Existing views are not deleted thereby, but if there exist already views with a name that is to be created such views are overwritten.  
For this purpose, you find a macro "CreateViews" in the object management. Existing views are not deleted thereby, but if there exist already views with a name that is to be created such views are overwritten.  


If required, you can delete all views (except for the “time” view) and have all CoPlanner data tables newly created as views.  
If required, you can delete all views (except for the “time” view) and have all CoPlanner data tables newly created as views.  
Line 55: Line 55:
From within the developer menu you can backup and restore the entire database as well as all definitions of the server directories.  
From within the developer menu you can backup and restore the entire database as well as all definitions of the server directories.  
The following data is saved:
The following data is saved:
:*The entire SQL database which is set in the connection string in SvrConfig.xml.
:*The entire SQL database which is set in the connection string in [[Technics:CoPlanner-Server#SvrConfig.xml|SvrConfig.xml]].
:*The “Agg Masks”, “Reports” “Report” and “Transfer” server directories as well as their sub-directories.  
:*The “Agg Masks”, “Reports” “Report” and “Transfer” server directories as well as their sub-directories.  
Please note:
Please note:
:*Any existing content in the saved directories will be overwritten during the restoring process.  
:*Any existing content in the saved directories will be overwritten during the restoring process.  
:*Configuration data (SvrConfig.xml, CopServer.exe.config, ClientConfig.xml and coplanner.exe.config) are not saved.  
:*Configuration data ([[Technics:CoPlanner-Server#SvrConfig.xml|SvrConfig.xml]], [[Technics:CoPlanner-Server#CopServer.exe.config|CopServer.exe.config]], [[Technics:CoPlanner_Win-Client#ClientConfig.xml|ClientConfig.xml]] and [[Technics:CoPlanner_Win-Client#coplanner.exe.config_File|coplanner.exe.config]]) are not saved.  
:*The database contained in the backup will be restored with the name which is defined in the SvrConfig.xml in the connection string – irrespective of the name of the originally saved database.  
:*The database contained in the backup will be restored with the name which is defined in the [[Technics:CoPlanner-Server#SvrConfig.xml|SvrConfig.xml]] in the connection string – irrespective of the name of the originally saved database.  
:*OLAP data is not saved.
:*OLAP data is not saved.
:*In case of doubt, carry out a backup before the restoring process.  
:*In case of doubt, carry out a backup before the restoring process.  
Line 67: Line 67:


===Backup database===
===Backup database===
{| {{Bausteindesign2}}
|-
|<span style="color: darkblue">'''Entry'''</span> &nbsp;Click the [[image:Icon_Dateiauswahl.jpg‎]] button and choose a directory on your local client computer.
|}
The following dialog box will appear:


[[image:Backup database.jpg‎|thumb|center|Framework, Backup database]]
[[image:Backup database.jpg‎|thumb|center|Framework, Backup database]]


#Drücken Sie auf den Button [[image:Icon_Dateiauswahl.jpg‎]] und wählen sie ein Verzeichnis auf Ihrem lokalen Client-Rechner.
{| {{Bausteindesign2}}
#Drücken Sie "Start". Es wird jetzt am Coplanner-Server ein Backup der Datenbank im definierten Verzeichnis erstellt. Zu beachten ist, dass in der Datei svrConfig.xml, welche sich im Server-Verzeichnis befindet, die Zeile <Backuppath> richtig definiert ist. Es muss ein Pfad angegeben werden, auf welchen der SQL-Server Schreibrechte hat. Das Backup wird nach einiger Zeit (bitte geduldig sein – die Datei wird am Server komprimiert und anschließend auf den Client übertragen) im angegebenen Verzeichnis gespeichert.
|-
|<span style="color: darkblue">'''Entry'''</span> &nbsp;Click the “Start” button. Following this, a backup of the database is created in the defined directory on the CoPlanner server and transferred to your client computer.
|}
 
 
{| {{Bausteindesign1}}
|-
| <span style="color: darkblue;">'''Hint'''</span> &nbsp;Please ensure that in the [[Technics:CoPlanner-Server#SvrConfig.xml|SvrConfig.xml]], which is located in the server directory, the <Backuppath> line is correctly defined. You must enter a path for which the SQL server has writing rights. After some time (please be patient – the file is compressed on the server and then transferred to the client) the backup is saved in the directory indicated.
Please also note that files which exceed the “BackupRestoreLimitinMB” (default volume = 50 MB) as set in the “copservice.exe.config” server configuration file, are not transferred to the folder indicated but are left in the folder given under “Backuppath” in the “[[Technics:CoPlanner-Server#SvrConfig.xml|SvrConfig.xml]]” configuration file which is used for temporary storage.
|}
 
 
===Restore database===
 
{| {{Bausteindesign2}}
|-
|<span style="color: darkblue">'''Entry'''</span> &nbsp;Please select the “Developer mode/backup” command from the CoPlanner menu.
|}
 
The following dialog box will appear:
 
[[image:Restore database.jpg‎|thumb|center|Framework, Restore database]]
 
 
{| {{Bausteindesign1}}
|-
| <span style="color: darkblue;">'''Hint'''</span> &nbsp;Verify if the database which you wish to overwrite by means of the restoring process has been defined in the connection string in [[Technics:CoPlanner-Server#SvrConfig.xml|SvrConfig.xml]].
Again, you need to ensure that the <Backuppath> is configured correctly. Furthermore, it is important to make sure that the pooling=false part exists in the connection string. Besides, no other client, query analyzer, enterprise manager or the like must be connected on the SQL server.  
If this is not the case, please change the parameters accordingly and restart the server and the client.
|}
 


{| {{Bausteindesign2}}
|-
|<span style="color: darkblue">'''Entry'''</span> &nbsp;Click the [[image:Icon_Dateiauswahl.jpg‎]] button and select a backup file and then click the “start” button.
|}


===Datenbank wiederherstellen===
After some time there will be a message saying that the backup has been integrated. The server will then be restarted and you can continue to work with the new data.


[[image:Datenbank_wiederherstellen.jpg‎|thumb|center|Framework, Datenbank wiederherstellen]]


#Überprüfen sie, ob in SvrConfig.xml im connectionstring die Datenbank definiert ist, welche Sie mit dem Wiederherstellen überschreiben möchten.
{| {{Bausteindesign1}}
Zu beachten ist hierbei wieder die richtige Konfiguration von <Backuppath>. Weiters wichtig ist, dass im Connectionstring der Teil pooling=false; vorhanden ist. Weiters darf auf den SQL-Server kein Query-Analyzer, Enterprise Manager o.ä. verbunden sein. Weiters sollte kein weiterer Client mit dem CoPlanner Server verbunden sein.
|-
Wenn nicht, ändern Sie bitte die Parameter entsprechend und starten Sie Server und Client neu.
| <span style="color: darkblue;">'''Hint'''</span> &nbsp;Close the existing masks before any restoring procedure since the underlying data no longer needs to be available.  
#Drücken sie den Button [[image:Icon_Dateiauswahl.jpg‎]] und wählen sie eine Backup-Datei.
|}
#Drücken Sie Start. Nach einiger Zeit erscheint eine Meldung, dass das Backup eingespielt wurde. Der Server wird anschließend neu gestartet und man kann mit den neuen Daten weiterarbeiten. (Es empfiehlt sich, bestehende Masken vor einem Restore zu schließen, da die zugrunde liegenden Daten nicht mehr vorhanden sein müssen.)




Line 88: Line 128:
__NOEDITSECTION__  
__NOEDITSECTION__  


[[Category:Framework|Datenbank am SQL-Server]]
[[Category:Framework|Database on SQL Server]]
[[de:Framework:Datenbank am SQL-Server]]

Latest revision as of 10:38, 29 March 2017

Tables

You need to distinguish between system tables and data tables.

Data tables are created via the object administration. They have a COPSYS_ID as their name.

System tables are tables pre-defined by the CoPlanner and start with “tb_”. These are:

  • tb_AggMskConfig
  • tb_Formeln
  • tb_FrmlFelder
  • tb_FrmlParameters
  • tb_FrmlVerb
  • tb_ImpFeldzuordnung
  • tb_ImpObject
  • tb_ImpTabzuordnung
  • tb_InpMskCnfg
  • tb_MakroAction
  • tb_MakroActionDetails
  • tb_MakroActionParam
  • tb_MakroActionParamDetails
  • tb_ObDetails
  • tb_ObjektRechte
  • tb_ObTypes
  • tb_OlapStructure
  • tb_PlanBenutzer
  • tb_Planverwaltung
  • tb_TabDetails
  • tb_tabdim
  • tb_VersionInfo
  • tb_zeit


Views

The only CoPlanner view is the “time” view. This results from:

Create View [time] As SELECT dbo.[tb_time].* FROM dbo.[tb_time]

This is used in the OLAP for the time dimension.

It is possible to create the CoPlanner data tables as a view which then is given the corresponding name as in the CoPlanner object management.

For this purpose, you find a macro "CreateViews" in the object management. Existing views are not deleted thereby, but if there exist already views with a name that is to be created such views are overwritten.

If required, you can delete all views (except for the “time” view) and have all CoPlanner data tables newly created as views.


Stored Procedures

The CoPlanner does not use any stored procedures.

Backup / Restore

From within the developer menu you can backup and restore the entire database as well as all definitions of the server directories. The following data is saved:

  • The entire SQL database which is set in the connection string in SvrConfig.xml.
  • The “Agg Masks”, “Reports” “Report” and “Transfer” server directories as well as their sub-directories.

Please note:

  • Any existing content in the saved directories will be overwritten during the restoring process.
  • Configuration data (SvrConfig.xml, CopServer.exe.config, ClientConfig.xml and coplanner.exe.config) are not saved.
  • The database contained in the backup will be restored with the name which is defined in the SvrConfig.xml in the connection string – irrespective of the name of the originally saved database.
  • OLAP data is not saved.
  • In case of doubt, carry out a backup before the restoring process.

In order to backup a database, proceed as follows:


Backup database

Entry  Click the button and choose a directory on your local client computer.

The following dialog box will appear:

Error creating thumbnail: File missing
Framework, Backup database
Entry  Click the “Start” button. Following this, a backup of the database is created in the defined directory on the CoPlanner server and transferred to your client computer.


Hint  Please ensure that in the SvrConfig.xml, which is located in the server directory, the <Backuppath> line is correctly defined. You must enter a path for which the SQL server has writing rights. After some time (please be patient – the file is compressed on the server and then transferred to the client) the backup is saved in the directory indicated.

Please also note that files which exceed the “BackupRestoreLimitinMB” (default volume = 50 MB) as set in the “copservice.exe.config” server configuration file, are not transferred to the folder indicated but are left in the folder given under “Backuppath” in the “SvrConfig.xml” configuration file which is used for temporary storage.


Restore database

Entry  Please select the “Developer mode/backup” command from the CoPlanner menu.

The following dialog box will appear:

Error creating thumbnail: File missing
Framework, Restore database


Hint  Verify if the database which you wish to overwrite by means of the restoring process has been defined in the connection string in SvrConfig.xml.

Again, you need to ensure that the <Backuppath> is configured correctly. Furthermore, it is important to make sure that the pooling=false part exists in the connection string. Besides, no other client, query analyzer, enterprise manager or the like must be connected on the SQL server. If this is not the case, please change the parameters accordingly and restart the server and the client.


Entry  Click the button and select a backup file and then click the “start” button.

After some time there will be a message saying that the backup has been integrated. The server will then be restarted and you can continue to work with the new data.


Hint  Close the existing masks before any restoring procedure since the underlying data no longer needs to be available.
Retrieved from "https://copsupport.coplanner.com/help10_en/index.php?title=Framework:Database_on_SQL_Server&oldid=15920"