NetXMS Support Forum

Hello there,

I was testing Netxms, and now I need to move it to production.
I´m Using the same SQL Server, and Database.
I made a fresh instalation and conected to the same database. But It didn´t work. My objects are missing ... What do I have to copy to move everything from a server to another ?

My test environment was using the latest version, so I didn´t have to make any upgrade. I´m just moving.

Any ideas ?

Hello!

New installation should just take all information from existing database. Could you please describe step by step how you connect existing database to new installation?

Best regards,
Victor

First of all, I copied my database from a SQL Server to another, and created a user for netxms and gave him a SA permissions on SQL Server 2k.

After the new instalation, it pops me up the configuration wizard. I cheched to use an existing database and checked to initialize it. I typed the database name, user and password. At the end of this wizard it gave me a "success" message.

using nxdbmgr.exe check it pass everything. no errors.
on netxmsd.conf file, everything is like the old one. it just changed the DBServer = databasename\instance

My SQL Database has around 800Mb, and I´m able to conect on Netxms console but it´s like a new instalation. I have only the local machine on the object explorer.

On SQL Activity monitor I can see a conection from the user that I configured to use the database. So seems that the service is conected and using the database.

Bellow follow a log from when I tried to reindex the database looking for errors ..

C:\NetXMS\bin>nxdbmgr reindex
NetXMS Database Manager Version 0.2.21

Configuration file OK
Reindexing table raw_dci_values by (item_id)...
SQL query failed (General SQL Server error: Check messages from the SQL Server.)
:
CREATE INDEX idx_raw_dci_values_item_id ON raw_dci_values(item_id)
Reindexing table event_log by (event_timestamp)...
SQL query failed (General SQL Server error: Check messages from the SQL Server.)
:
CREATE INDEX idx_event_log_event_timestamp ON event_log(event_timestamp)
Reindexing table thresholds by (item_id)...
SQL query failed (General SQL Server error: Check messages from the SQL Server.)
:
CREATE INDEX idx_thresholds_item_id ON thresholds(item_id)
Reindexing table thresholds by (sequence_number)...
SQL query failed (General SQL Server error: Check messages from the SQL Server.)
:
CREATE INDEX idx_thresholds_sequence ON thresholds(sequence_number)
Reindexing table alarm_change_log by (alarm_id)...
SQL query failed (General SQL Server error: Check messages from the SQL Server.)
:
CREATE INDEX idx_alarm_change_log_alarm_id ON alarm_change_log(alarm_id)
Reindexing table alarm_notes by (alarm_id)...
SQL query failed (General SQL Server error: Check messages from the SQL Server.)
:
CREATE INDEX idx_alarm_notes_alarm_id ON alarm_notes(alarm_id)
Reindexing table syslog by (msg_timestamp)...
SQL query failed (General SQL Server error: Check messages from the SQL Server.)
:
CREATE INDEX idx_syslog_msg_timestamp ON syslog(msg_timestamp)
Reindexing table snmp_trap_log by (trap_timestamp)...
Reindexing table address_lists by (list_type)...
SQL query failed (General SQL Server error: Check messages from the SQL Server.)
:
CREATE INDEX idx_address_lists_list_type ON address_lists(list_type)
Database reindexing complete.

Most likely schema name was changed on copy. For example, if you copy database as sa user, tables may have been created in dbo schema, like dbo.config. If after that you create database user netxms, and use it for connection NetXMS server to database, it expects to found tables in schema "netxms", like netxms.config.

Also, you should not check "initialize database" when connecting new NetXMS server installation to existing database.

Best regards,
Victor

Then I´ll try to run a Backup database and restore it to the production server.
Let´s see if it works ..

Thanks for your timely help !

Using a backup and restore, SQL seems weird.
It´s logging the following events on the eventviewer:

Event Type:   Error
Event Source:   NetXMSCore
Event Category:   None
Event ID:   43
Date:      8/4/2008
Time:      9:03:54 AM
User:      N/A
Computer:   SERVERNAME
Description:
SQL query failed (Query = "SELECT var_value FROM config WHERE var_name='DBFormatVersion'"): General SQL Server error: Check messages from the SQL Server.

Event Type:   Error
Event Source:   NetXMSCore
Event Category:   None
Event ID:   63
Date:      8/4/2008
Time:      9:03:54 AM
User:      N/A
Computer:   SERVERNAME
Description:
Your database has format version 0, but server is compiled for version 80

You´re right, there´s some problem with the schema.
I´m still working to find out and move the database without problems.
I´m loging everything here, in case it helps someont in the future.

hello,

- use management studio to connect to sql server on production
- browse to <server_name>\Databeses\<netxms_database\Tables

a) if tables have prefix dbo.<tables...> execute SQL in query window:
EXEC sp_changedbowner '<DBLogin from netxmsd.conf>';

b) if tables have prefix <DBLogin from netxmsd.conf>.<tables...> execute SQL in query window:
EXEC sp_change_users_login 'Update_One', '<DBLogin from netxmsd.conf>', '<DBLogin from netxmsd.conf>';

Ales

I tryed to run those commands, and they run successfully, seems that SQL is having a problem when I restore the database on the production server. it brings up the security for the database, but it´s not linked to the user from the SQL server.
Example, I cannot delete the user from the database, because it says that it´s hieriting objects. even running your command, it´s still complaining about the user still has objects linked.
When I try to create the same user, it says that the user already exists. I cannot change the roles of that user, or anything.
I tryed to create a new different user, but seems that the restored database still have some stuff linked to the old one.
Well, I´m working on the backup and restore on W2k SQL Server. I doubt that SQL 2k5 wouldn´t have those weird errors.
PS: This is a fresh instalation of SQL 2k, with Sp4 ... I don´t understand ....

Ok, here goes the solution under credits of our It Manager Marcelo Ramos :

When you install netxms, on the database creation screen, it asks you for a credential. This credential, somehow is passed as paramenter when creating the SQL tables, and if you take a look on that, it creates as "username.tablename".
EX: netxms.acl
      netxms.actions
      netxms.address_lists

And the correct would be :
      dbo.acl
      dbo.actions
      dbo.address_lists

At least it was done on my case. And we made a SQL script to correct it
First run this script and select the output as "text" (not as grid) to generate the final script.

--*************************************************************************************
SELECT 'EXEC sp_changeobjectowner ''' + TABLE_SCHEMA + '.' + TABLE_NAME + ''', dbo'
FROM INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
AND TABLE_TYPE = 'BASE TABLE'
GO
--*************************************************************************************

The output would be something like this :

EXEC sp_changeobjectowner 'netxms.acl', dbo
EXEC sp_changeobjectowner 'netxms.actions', dbo
EXEC sp_changeobjectowner 'netxms.address_lists', dbo
EXEC sp_changeobjectowner 'netxms.agent_configs', dbo
EXEC sp_changeobjectowner 'netxms.agent_pkg', dbo
EXEC sp_changeobjectowner 'netxms.alarm_change_log', dbo
EXEC sp_changeobjectowner 'netxms.alarm_grops', dbo
EXEC sp_changeobjectowner 'netxms.alarm_group_map', dbo
...
... I cuted the middle
...
EXEC sp_changeobjectowner 'netxms.user_profiles', dbo
EXEC sp_changeobjectowner 'netxms.users', dbo
EXEC sp_changeobjectowner 'netxms.vpn_connector_networks', dbo
EXEC sp_changeobjectowner 'netxms.vpn_connectors', dbo
EXEC sp_changeobjectowner 'netxms.zone_ip_addr_list', dbo
EXEC sp_changeobjectowner 'netxms.zones', dbo

All you have to do is copy all lines generated, and run them on netxms database.

After that, All my configuration, data, objects, everything is working perfectly as on the test environment.
I hope it helps someone else.

Thanks for all guys that helped me on this issue.