Download as PDF

System porting via database backup

version 0.1 by Anja Beuth

Requirements

In order to follow the instructions in this tutorial, the developer needs administration rights to the SQL Server Management Studio of the source and target system.

Description

If the hosting system is not yet available for later website running at the beginning of development, development must be begun on a temporary, possibly local system. The current development status should be migrated onto the target system later on with as few losses and complications as possible.

Since almost all information is content in onion.net and is therefore to be found in the database, porting can be carried out most simply via a database backup.

The necessary steps for backup on the source system as well as restoring and start-up on the target system are described in this tutorial.

Prerequisites on the target system

Before the database is ported, there should ideally already be a standard onion.net installation on the target system that functions. This applies both for the editor and for a Render Engine.

The reason for this is that more than just a correct database connection is necessary for an onion.net system to function. If everything is working before the porting takes place, configuration errors or false authorizations for example can be ruled out as error sources.

Backing up the database

As a first step, the database to be ported must be backed up. To do this, connect to the database server first of all. There you start the SQL Server Management Studio.

In the context menu of the database you will find the point “Tasks” and the option “Back up” under that.

The following illustration makes the click flow clearer.

Backing up the database on the source system

In the dialogue which then appears you can usually assume the standard settings.

Dialogue “Back up database”

Back up the file on the hard drive. To do this, you must select a memory location by clicking on the button “Add”. It is important here for the name of the backup file to also be indicated in the dialogue that appears. Make sure you indicate the correct file extension “.bak”!

Indicate the file extension “.bak”

Porting database backup onto target system

Depending on how accessible the target system is, or how the connection works, porting may not be an insignificant problem. The backup file however must be located in a local memory location of the target system so that the file can be selected when restoring.

Remote desktop connection

If an RDP connection is made via the Microsoft Terminal Service Client (mstsc), a local hard disk can be included in the options of the connection. In this way the backup file can then be copied to a local memory location of the target system.

Dialogue “Remotedesktopconnection” (mstsc)

Options > tab “Local resources”, click on “More”

Select the partition on which the database backup is located

Restoring database on target system

Start the SQL Server Management Studio on the target system and connect to the appropriate database module.

If there is already a database with the same name, you should either delete the existing database before loading the ported one to be on the safe side, or rename the existing one. In the latter case, adapt the database query in the web.config of the Information Server first of all and test whether the Editor and Render Engine are still working.

In the context menu of the point “Databases” you will find the item “Restore database …”

Enter the name of the database into the displayed dialogue. Select “From medium” as a source and add the backup file. It will then appear in the overview at the bottom. In order to restore it, you need to put a tick in front.

So that the database works on the target system, a few clean-ups are to be carried out there.

Correcting system information

The table containing system information about the computer must be cleared up, particularly if the database of a computer has been migrated onto another one.

To do this, navigate to the table “dbo.SystemInformation”, open the context menu and select the item “Edit top 200 lines”. Delete the last line, which contains the value “system.current” in the column “slot”. You can do this by right-clicking in front of the line.

Authorise user for newly created table

The database user with which the Information Server to the database connects has also been included in the database backup. Since, as stipulated by the prerequisites, there is already a user in the database module with which the database connection works successfully, this user should also be used for the imported database.

To do this, navigate into the folder “Security” > “User” underneath the restored database. There is usually a user there bearing the same name as the database. Delete this user and create a new one for the database. Enter the login names of the existing database user there.

If you do not know the login name, you can check it in the web.config of the Information Server. The user name is contained in the database query there.

Enter “dbo” as the default schema. It is important for the user to be given the database role “db_owner”.

Deleting search index

The SQL Server creates a search index for databases so that the full text search in the database works for example and access takes place quicker. The index of the source database also was transferred at the time of importing. It cannot be used by the new SQL Server instance however. We therefore delete it so that a new one can be created.

Navigate to the folder “Memory” > “Full text catalogues”. There should be an object “ObjectFullText” underneath. Delete the object.

External accounts

At the time of database backup, all user accounts that were in the onion.net system were also ported. There could be problems with external user accounts here, e.g. if you connect an active directory for the login.

Normally, the passwords of the accesses are stored in encoded form in the database table “dbo. Users”. If an external source is connected for the login however, authorisation also takes place in this external access administration.

If, after porting from the new system, access to the external access administration is no longer possible, the onion.net accesses concerned have the value “EXTERNAL” in the database. For these accesses, a new password must be set manually over the onion.net Editor with administrator authorisation.

Restarting Information Server

So that both the onion.net Render Engine(s) and the onion.net Editor(s) can draw their data from the updated database immediately, the onion.net Information Server (Windows service) must be restarted.