Additional Database Server

Guys,

We are planning to add a new database server to our current configuration.

Below is the current configuration of the database:
DBSERVER1 -Environment DV, PY, SP, PD

Planned configuration
DBSERVER1 - DV, PD
DBSERVER2 - PY, SP

Any ideas of how to proceed with this one ?
Correct me If I am wrong.
I thought of moving the PY and SP database first from DBSERVER1 to DBSERVER2.
Once this has been accomplished then change the configuration from within JDE for the environments(PY/SP) to point to DBSERVER2 instead of DBSERVER1. I am not too sure of how this is done or whether if this can be done or not.

Our JDE configuration is:
JDE 9.0, TR 8.98.1.1
Deployment Server: Win 2k5
Enterprise Server: Win 2k5
DB Server: Win 2k5 - SQL 2005 (different from Ent Server)
Web Server: Win 2k5 - Websphere (I believe its ver 6)

Any ideas of how achieve this ?
I tried searching in the jungle but didn't find any relevant doco.

Any help is truly appreciated.

Thanks in advance.

Max

Hello Max,
Once you have done with the second database server installation, move the databases of the 2 environments to the new db server. Login to the JDEPLAN in the deployment server. and create new plan for the new db server and make sure to change the data sources of the selected environments before running the installation workbench. once you have done with the installation workbench you need to confirm the data sources in System - 900 and all enterprise servers server map data sources.
I am assuming that JDE900 still in DB server 1 which will be shared by all environments.
That's it.
You have nothing to do with the web servers
In the enterprise server you need to confirm the ODBC configuration of the new data base server
Hope that was beneficial

thanks
Anan
B73.1.1 through E900
DB2/400, Oracle, SQL Server
AS/400, Windows, Citrix, OAS/WebLogic, WebSphere, VMWare

Thanks a lot Anan.
I'll go through it and will let you know the results.

-Max

Hi Anan,
I tried following those steps but its failing.
-> I have moved the PY database (Central Objs and Ctrl/Data Tables) from the old db to the new db.
-> Added new data server
-> Mapped the PY env to the new server
-> Workbench ran successfully
-> On the web side, I am able to log in but there is no menu nor can I open any application
-> Porttest failing with the below error

26516/27508 MAIN_THREAD Tue Mar 08 20:30:43.925000 Odbc_u1.c901
ODB0000163 - wSQLNumResultCols failure. rc=-1

26516/27508 MAIN_THREAD Tue Mar 08 20:30:43.925001 Odbc_u1.c901
ODB0000164 - STMT:00 [42S02][208] [Microsoft][SQL Native Client][SQL Server]Invalid object name 'JDE_CRP.CRPDTA.F0009'.

26516/27508 MAIN_THREAD Tue Mar 08 20:30:43.925002 Odbc_u1.c901
ODB0000164 - STMT:01 [42000][8180] [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prepared.


Porttest fails after F986110 table.
In the Enterprise Server's JDE.ini
[DB SYSTEM SETTINGS]
Database=<<old Server>>

I tried changing it to <<new server>> but as expected it failed for even F986110 as was not able to find JDE900.

Stuck !!!!

-Max

Any other way to do this apart from the way I am trying ?

I tried searching for a doc on KG for this but was not able to find one. I am able to add the data-server but I believe its the mappings where I am failing.

Also, in the Database Datasource application, I am not able to see the new server. Its just the old db server. I believe I am doing something wrong.

-Max

Just wondering what method you used to move the databases over to the new server. Those errors look very much like the system alias user can't see any of the tables.

From the JDE side, the data source definition could be wrong. Don't forget to check the settings on both the System side and the Server Map side. Have the ODBC entries on the Enterprise Server been changed to point to the right server?

From the database server side, all the needed user IDs have been created? Have all the user ID numbers been fixed to match up properly with the names? Do permissions need to be regranted?

For example, if you log into Management Studio as CRPDTA, can you run a query on the tables in JDE_CRP?

Hi Max,

Your errors sound like you have an ODBC issue. Do you have the database driver loaded on the new server?

Thanks a lot guys.

Method: I created the databases on the server2, took a backup from server1 and restored them on the server2. For the users, I created the users and have attached the users to the databases making it look exactly same as on server1. I have logged onto server2 as CRPDTA and have fired select queries on the data as well as ctl tables.

A quick one: I just need to move PY environemnt so the db which I'll require on server2 would be JDE_PY900 and JDE_CRP. Is this correct ?

On the Enterprise Server, I don't have any entries in ODBC. All the entries are on Deployment Server.

I am more worried about the data source definition.
Even after running the Installation Workbench, I dont see the DBServer2 in the Database Datasources Machine select form.
The machine select form in database datasources in JDEPLAN shows:
Machine Name Data Source
---------------------------------
local Planner-900
DBServer1 System-900
DBServer1 ENTSERVER - 900 Server Map

There is no DBServer2.
So, after going into DBServer1, if I modify the entries for CRPDTA and point it to DBServer2 instead of DBServer1, will it work ?

Note: We've got database and enterprise as separate servers.

I've got the same version of MS SQL loaded on both the servers with same SP.

Thanks again.

-Max

[ QUOTE ]
... A quick one: I just need to move PY environemnt so the db which I'll require on server2 would be JDE_PY900 and JDE_CRP. Is this correct ?

[/ QUOTE ]

Yes, this is correct.

[ QUOTE ]
Machine Name Data Source
---------------------------------
local Planner-900
DBServer1 System-900
DBServer1 ENTSERVER - 900 Server Map

[/ QUOTE ]

The entries would only be for any servers that hold a System or Server Map database. Since DBServer2 does not have either of these, then it won't show up in the list. That doesn't mean you can't use it though ...

[ QUOTE ]
So, after going into DBServer1, if I modify the entries for CRPDTA and point it to DBServer2 instead of DBServer1, will it work ?

[/ QUOTE ]

And this is exactly where you do it. You should do this change in the data source entries in both System and Server Map.

As for the lack of ODBC entries on the Enterprise Server, do you have just a single entry with the Machine's name?

[ QUOTE ]

As for the lack of ODBC entries on the Enterprise Server, do you have just a single entry with the Machine's name?

[/ QUOTE ]

Yes. There was one entry on the Enterprise Server pointing to DBServer1. I added another entry pointing to DBServer2.

I have logged onto Fat Client PY environment. Its asking for user id and pwd for the DBServer2 which even If I provide correctly throws the below error in jde.log. It logs in successfully though.

588/2728 MAIN_THREAD Thu Mar 10 12:50:55.865000 Jdbodbc.c1340
ODB0000163 - SQLDriverConnect failure. rc=-1

1588/2728 MAIN_THREAD Thu Mar 10 12:50:55.865001 Jdbodbc.c1340
ODB0000164 - DBC:00 [28000][18456] [Microsoft][SQL Native Client][SQL Server]Login failed for user 'PY900'.

1588/2728 MAIN_THREAD Thu Mar 10 12:50:55.865002 Jdbodbc.c1340
ODB0000164 - DBC:01 [28000][18456] [Microsoft][SQL Native Client][SQL Server]Login failed for user 'PY900'.

1588/2728 MAIN_THREAD Thu Mar 10 12:50:55.865003 Jdbodbc.c1350
ODB0000010 - SQLDriverConnect failed. ODBC DSN: DBSERVER2.


I tried firing R0010P report. It keeps on processing.
The logs shows the same error:


4648/2448 WRK:Starting jdeCallObject Thu Mar 10 13:17:03.591001 Jdbodbc.c1340
ODB0000164 - DBC:00 [28000][18456] [Microsoft][SQL Native Client][SQL Server]Login failed for user 'PY900'.

4648/2448 WRK:Starting jdeCallObject Thu Mar 10 13:17:03.591002 Jdbodbc.c1340
ODB0000164 - DBC:01 [28000][18456] [Microsoft][SQL Native Client][SQL Server]Login failed for user 'PY900'.

4648/2448 WRK:Starting jdeCallObject Thu Mar 10 13:17:03.591003 Jdbodbc.c1350
ODB0000010 - SQLDriverConnect failed. ODBC DSN: DBSERVER2.

It definately looks like ODBC issue but am not able to figure out where the change needs to take place.

I have logged onto DBSERVER2 and have connected successfully to the databases using JDE/CRPDTA/CRPCTL/PY900 user id's.

Thanks Heaps.

Max

Hi,

You need to create ODBC connections to the new server on Logic & Batch servers and any fat client that has PY environment.

I've already done that. Another interesting thing is that the PORTTEST for PY is successful. I have restarted the services just in case.

Login to PY is successful on Fat Client without any errors.
As soon as I fire a report, it goes into "P" state to never finish off and the below errors are reported in the log files.

ODB0000163 - SQLDriverConnect failure. rc=-1

4828/2532 MAIN_THREAD Thu Mar 10 18:45:46.698001 Jdbodbc.c1340
ODB0000164 - DBC:00 [28000][18456] [Microsoft][SQL Native Client][SQL Server]Login failed for user 'PY900'.

4828/2532 MAIN_THREAD Thu Mar 10 18:45:46.698002 Jdbodbc.c1340
ODB0000164 - DBC:01 [28000][18456] [Microsoft][SQL Native Client][SQL Server]Login failed for user 'PY900'.

4828/2532 MAIN_THREAD Thu Mar 10 18:45:46.698003 Jdbodbc.c1350
ODB0000010 - SQLDriverConnect failed. ODBC DSN: DBSERVER2.

4828/2532 MAIN_THREAD Thu Mar 10 18:45:46.698004 Jdb_drvm.c909
JDB9900164 - Failed to connect to DBSERVER2

4828/2532 MAIN_THREAD Thu Mar 10 18:45:46.698005 Jtp_cm.c284
JDB9909003 - Could not init connect.

4828/2532 MAIN_THREAD Thu Mar 10 18:45:46.698006 Jtp_tm.c1134
JDB9909100 - Get connect info failed: Transaction ID =

4828/2532 MAIN_THREAD Thu Mar 10 18:45:46.698007 Jdb_rq1.c2392
JDB3100013 - Failed to get connectinfo

4828/2532 MAIN_THREAD Thu Mar 10 18:45:46.698008 SpecOpen.c2615
jdeSpecOpenLocalOpt completed in error - JDESPECRESULT_JDBFAILED.


It definately looks like ODBC issue but am not able to get to the root of the cause.

-Max

Also, noted below errors in JDE.log on Fat Client

4004/3480 FOREIGN_THREAD Thu Mar 10 18:46:03.367000 Jdb_omp1.c598
JDB9900245 - Failed to find F98611 OneWorld Local in cache

4004/3480 FOREIGN_THREAD Thu Mar 10 18:46:03.367001 Jdb_rq1.c1959
JDB3100011 - Failed to get location of table F989999 for environment PY900

4004/3480 FOREIGN_THREAD Thu Mar 10 18:46:03.367002 Jdb_utl1.c11838
JDB9900603 - Failed to open table F989999

-Max

Please try something for me ... run this statement against JDE_CRP:

sp_change_users_login 'Update_One', 'CRPDTA','CRPDTA'

and let us know what the result says.

If it updated something, then your user names are not matching up with their system IDs within SQL Server, and the statement needs to be run for each user name against each database.

If it did not update anything, then it may be a permissions problem, though I can't think of why at the moment.

Ran the query:
O/P
Command Completed Successfully.

Below is an extract from report's debug log.

Mar 10 19:03:32.324025 - 3368/3408 WRK:Starting jdeCallObject Entering JDB_GetDatabaseTypeFromDS (DS Central Objects - PY900)
Mar 10 19:03:32.324026 - 3368/3408 WRK:Starting jdeCallObject Exiting JDB_GetDatabaseTypeFromDS with Success (DS Central Objects - PY900) (Type S)
Mar 10 19:03:32.324027 - 3368/3408 WRK:Starting jdeCallObject Entering JDB_OpenTableWithSQLName(Table = F98740 and SQLTable = F98740PY900FB)
Mar 10 19:03:32.339000 - 3368/3408 WRK:Starting jdeCallObject ODB0000163 - SQLDriverConnect failure. rc=-1
Mar 10 19:03:32.339002 - 3368/3408 WRK:Starting jdeCallObject ODB0000164 - DBC:00 [28000][18456] [Microsoft][SQL Native Client][SQL Server]Login failed for user 'PY900'.
Mar 10 19:03:32.339004 - 3368/3408 WRK:Starting jdeCallObject ODB0000164 - DBC:01 [28000][18456] [Microsoft][SQL Native Client][SQL Server]Login failed for user 'PY900'.
Mar 10 19:03:32.339006 - 3368/3408 WRK:Starting jdeCallObject ODB0000010 - SQLDriverConnect failed. ODBC DSN: DBSERVER2.
Mar 10 19:03:32.339008 - 3368/3408 WRK:Starting jdeCallObject JDB9900164 - Failed to connect to DBSERVER2
Mar 10 19:03:32.339010 - 3368/3408 WRK:Starting jdeCallObject JDB9909003 - Could not init connect.
Mar 10 19:03:32.339012 - 3368/3408 WRK:Starting jdeCallObject JDB9909100 - Get connect info failed: Transaction ID =
Mar 10 19:03:32.339014 - 3368/3408 WRK:Starting jdeCallObject JDB3100013 - Failed to get connectinfo
Mar 10 19:03:32.339016 - 3368/3408 WRK:Starting jdeCallObject Exiting JDB_OpenTableWithSQLName(Table = F98740) with Failure
Mar 10 19:03:32.339017 - 3368/3408 WRK:Starting jdeCallObject SpecEncapsulation : Close completed.
Mar 10 19:03:32.339018 - 3368/3408 WRK:Starting jdeCallObject SpecEncapsulation : jdeSpecOpenLocalOpt completed in error - JDESPECRESULT_JDBFAILED.
Mar 10 19:03:32.339019 - 3368/3408 WRK:Starting jdeCallObject jdeSpecOpenLocalOpt completed in error - JDESPECRESULT_JDBFAILED.
Mar 10 19:03:32.339021 - 3368/3408 WRK:Starting jdeCallObject Fetched the record
Mar 10 19:03:32.355000 - 3368/3408 WRK:Starting jdeCallObject Entering JDB_UpdateTable (Table F983051)
Mar 10 19:03:32.355001 - 3368/3408 WRK:Starting jdeCallObject ODBC:S DBInitRequest(new) req=06CC8918 con=0564A258 env=056E14E8 dbc=056E5028 spid=58 DBSERVER2 A (JDE@DBSERVER2)


-Max

Also, ran the report locally in PY environment Fat Client. The report ran fine and it did extract proper data from PY environment.

-Max

Found the below in the SQL Log files (frm MGMT Studio):

Date 10/03/2011 7:42:10 PM
Log SQL Server (Current - 10/03/2011 7:42:00 PM)
Source Logon
Message
Error: 18456, Severity: 14, State: 8.


Date 10/03/2011 7:42:10 PM
Log SQL Server (Current - 10/03/2011 7:42:00 PM)
Source Logon
Message
Login failed for user 'PY900'. [CLIENT: xxx.xx.xxx.xx]


Googled it and found that its related to 'password mismatch' but I am 100% sure that the password for PY900 is PY900. In fact I can connect to MGMT Studio with that user id and pwd.

-Max

Hi,

Further to what Ken said, can you run the below query and paste the output. What this will do is provide a list of orphan user ids in the database

Use <database_name> /* example JDE_CRP & JDE_PY90 */

exec sp_change_users_login 'REPORT'
go

Below is the o/p to the query

use JDE_PY900
exec sp_change_users_login 'REPORT'
go


APPLEAD 0x29C0C2A0634A6B48BE1157AE5422D4E9
DEVUSER 0xC9630C17CA4DC747999F511DC775EB41
PRODUSER 0x20603CD5DBD12143BA404E646A4EF1B0
PY810 0x559BC31AD7B4B342B873CE251E21FCD7
RRTI 0x6B2CD4CD2C48D040B277CB8CD0E3836F
RRTIV9 0xB8991D8FD09AE441B103EF971DDCE257
SPCTL 0x8AD33F68BFDC104DACED301B7A7E64A8
SPDTA 0xFBC4E3024C9B524CA43166FC771F3B8D

Another thing which I found quite interesting on Fat Client:

In the ODBC data source, there is an option:
How should SQL Server verify the authenticity of login:
-> With integrated Windows Authentication
-> With SQL Server Authentication

If I use the windows authentication, then the login to Fat Client is smooth...no errors

If I use SQL Server authentication, I get the database password entry window asking for user id and password. If I type in the user id jde and its pwd, it allows me to log in. As soon as the popup appears below is scripted in the log files:

2888/3208 MAIN_THREAD Thu Mar 10 21:11:22.338000 Jdbodbc.c1340
ODB0000163 - SQLDriverConnect failure. rc=-1

2888/3208 MAIN_THREAD Thu Mar 10 21:11:22.354000 Jdbodbc.c1340
ODB0000164 - DBC:00 [28000][18456] [Microsoft][SQL Native Client][SQL Server]Login failed for user 'PY900'.

2888/3208 MAIN_THREAD Thu Mar 10 21:11:22.354001 Jdbodbc.c1340
ODB0000164 - DBC:01 [28000][18456] [Microsoft][SQL Native Client][SQL Server]Login failed for user 'PY900'.

2888/3208 MAIN_THREAD Thu Mar 10 21:11:22.354002 Jdbodbc.c1350
ODB0000010 - SQLDriverConnect failed. ODBC DSN: DBSERVER1.

-------SAME ERROR-------

-Max