Hi Guys,
Ran into some problems the other day while running database replication on our baan database. The type of replication used was Transactional Replication and the problem encountered was that there were no primary keys found on any of the baan tables and hence replication fails at the publication step. For Transactional Replication, it says a table needs to have atleast one primary key. Tried with Snapshot Replication and it worked but our requirement is for Transactional Replication. Has anyone over here encountered this issue or come up with a work around for this?

With our existing setup, we have baan and our reporting app on the same server. This causes a lot of problem in performance and so we decided to have it on different servers by replicating the baan database.

can anyone help me out on this?

sk

Come on guys, hasn't anyone done replication on baan database?

sk

I do use DTS to replicate baan data for reporting purposes however. Maybe this is an option?

The question on everybody's mind must be: "Do you really not have any indexes on your Baan table?"
Because if that's the case then your performance issues aren't just coming from the reporting tool.

Aaah reply at last!!!
Well Francesco, there are indexes on Baan Tables..The problem i faced while using Transactional Replication was that it didnt find any primary keys in the table. When you go into the design of any table in Baan, you wouldnt find any primary keys defined. Transactional Replication doesnt work without primary keys...

Coming to DTS... are you using it on the same server or different. Would like to know more about this if you could share some pointers...i.e if your reporting environment is on a different server....

sk

you'd have to specify index1 as a primary key.

and you'd lose that after any reconfig..

It's the only way.

Dave

Thanks Dave for your input. As you said, any reconfig would take the primary keys off flushing the tedious task of assigning them in the first place. Apart from that, i am apprehensive about doing any changes to any of the baan tables for fear of starting a chain reaction of errors elsewhere.

Quite surprisingly, i didnt find any documents on replication from Baan. There was one about Multisites but i failed to see any sort of database replication in it. Is it that Baan doesnt support transactional based replication?

Leaving all that aside...Is there any other way to do this splitup of Baan and the Reporting Environment? How are 24/7 organizations working on multisites? Third party software?

sk

I believe most sites use clusters for that. I believe that there are docs for setting that up..

dave

So, bottomline...without cluster i wouldnt be able to do daabase replication? I know you used the word "most sites" but what setup does the other sites employ to work it out?

sk

I usually run multiple iterations of the BaaN database on various servers for Production, Test, Development and Scratchpad purposes. I have split the BaaN application from the BaaN database successfully a few times.

I use BaaN 5c on Win2000 with Oracle 8i databases.

Create your additional instance using either a Hot backup or Cold backup of the database.

If you need additional info, just respond and I will be happy to help you. If you have other requirements that are not evident in your request, sorry for the extraneous info,

Tuple9i

:o

hi Tuple,
Yes i would indeed like to get additional info on this...

We are running Baan4c4 on SQL 2000/Win2003...Therefore my first question would be, with this existing environment of ours, is it possible to do what you suggested?
Secondly, How do i keep the second database online with the Live one? How much time does it take to backup and restore using the "Hot & Cold" backup method?
Last but not least, Have you succesfully done Transactional Replication on Baan database? If yes, how did you handle the lack of primary keys?

Looking forward to your reply..
sk

Hi Sukesh. I just noticed that you are using SQL Server database. My solution is with Oracle, but databases are databases.

First, I am not familiar with a "Transactional" replication. Possibly confusion with terminology.

In my Oracle environment, I shutdown the Application, stop the database and then I copy the BaaN environment and Oracle datafiles to the new servers - one for the Application and one for the Database.

I am using the Win2000 operating system for both (unfortunately, but there is nothing I can do about that ;-) , so I have already set the servers to the same environment that I have throughout my BaaN Application sphere, including the database binary structures.

Now it is simply a matter of:
1) copying the BaaN application data, overlaying the existing files that are there
2)copy the datafiles to the proper locations as documented by the Production controlfile
3) bring up the database and check for Oracle errors
4) startup the BaaN application
5) check for errors in the BaaN application itself.

I do this about every 60 days to keep a Test region within a couple months of Production. I do use the Test region to test solutions and changes to BaaN or the Database. After the testing, I move the changes to Production. I am sure you do the same.

Background information -

I have a medium database - about 180 Gbytes, running Oracle 8.1.7.4
BaaN Porting set is 7.1.02: BaaN 5c, but highly customized
I run BaaN on 4 server spheres - 1 Production, 1 Test, 1 Development and 1 Scratchpad

Additional - it takes about 1 1/2 hours to do the overlay from Production to any of the other three spheres. As far as Primary Keys, I never run into that problem. Guess that is a SQL Server thing.

To create an environment that would run live with Production, you could rename the database and make the appropriate changes in the BaaN settings. I would suggest that you use different servers for each of the spheres - one for Production and one for Test.


Hope I answered your questions. If not, you know where to find me ;-)

Tuple9i

First thing first...
Transactional Replication is one of the three choices you have in Replicating Databases(In MS Sql), the other two being Snapshot and Merge. Transactional Replication creates a snapshot or a copy of the original databases and as and when the tables(in the source db) are being updated, it updates the second copy with whats updated. This seemed to be perfect fit for our requirement untill we ran into the primary key issue..

I dont know what similar choice you have in Oracle(last time i worked on Oracle was around 1997-98).

Coming back to your solution: It is suitable for a test server environment and not for a time critical reporting environment. Most of our reports are critical of data to the last few minutes if not seconds...

The end result we like to see is

Two servers
Server A (Baan and Baandb/MS Sql)
Server B (Reports and Baandb/MS Sql)
Server B.Baandb should be updated by Server A.Baandb to the last minute. All the reports users would be using Server B while Server A could be free of reporting overload...

Any idea how to achieve this? Transactional Replication was promising untill the P.key issue..

sk

Hi Sukesh, I remember the limitations with SQL Server from my last contract. The poor database Administrator ran into the same problem. It seems Developers for SQL databases don't like to use primary keys - an offshoot of the Access database development I guess. I don't know why, seems that they would be important to assure correct data, but just my theory. Oracle does not have that particular issue, but it has it's own quirks ;-)

You may try to cluster your database. I have never seen a Microsoft cluster work well, so I have limited knowledge on how to effectively help you with that - sorry.

In the Oracle realm, I would use a Logical Standby database as my Reports environment. That refreshes by applying a commited query to the standby from the Primary at almost real time.

I know that probably doesn't help, but I hope you find your solution. Let me know the results when you finally resolve your issue.

Good Luck,

Tuple9i

Hi,
In my ongoing search for a solution i came across something called as "Log Shipping". The idea behind this is simple that you would want to see it work before you beleive it. For those who are not familiar with this technique, it goes something like this
Live Server Job 1: Main Database is backed up and then copied to the standby server where a stored procedure is fired to restore it on to that server
Live Server Job 2: The Transaction Log is then backed up and copied in similar fashion to the standby server where another stored procedure is fired to restore the log to that server..

Both these are two different jobs where in the first job is executed once in a day while the second one is executed every 15 minutes. The stored procedures are restore database and restore log commands created in the master database of the standby server.

It all went well till the restore log part when it gave an error: "The log in this backup set cannot be applied because it is on a recovery path inconsistent with the database."

Has anyone done this before?

sk

Please ignore the earlier post as further investigation reveals that ..
"Log-shipping can't usually allow the secondary server to be used for reporting as it requires an exclusive lock on the database to restore the log i.e. during the restore users will be forcibly removed, or the log-shipping job will fail."
So there goes the effort in the drain...

Still looking for a way....

sk

in log shipping you have two options to put the logged shipped database into either No recovery mode or Standby mode if it is in standby mode you can run queries on the database except when the logs are being written to the database

Could you ellaborate on the Restore Log part.
First time along with the database restore, it(restore log) goes all ok. But then when i tried the restore log after 10 minutes, it gave out an error which read
"The preceeding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step"

This is the command i am running
///****
RESTORE LOG database_name
FROM DISK = 'path\dbname_log.bak'
WITH
DBO_ONLY,
STANDBY = 'path\undo_dbname.ldf'
****////

I am all at sea on this...What am i doing wrong?

sk

I setup log shipping for another INFOR product syteline 7 but on sql 2000 enterprise you get a nice wizard to set this stuff up for you under covers for you. but your problem could be that when you did the full database restore did you have norecovery selected as if you did you cannot restore any further logs.

Can we keep this going. I also have had these same issues trying to do exactly the same thing. Any tips on how to make this work greatfully accepted

Thats what i thought when i saw the phrase "preceeding restore operation" but then i was confused about which restore it was talking about Restore Database or Restore Log(first time)..Technically the first Restore Log qualifies as the preceeding restore operation...
Anyways.. i checked my Restore Database command and found out that there was no mention of NORECOVERY..

I am including the command below..
///*****
Restore Database DbName
From Disk = 'Path\Filename'
WITH DBO_ONLY,
REPLACE,
StandBy = 'Path\Undo_DbName.ldf',
MOVE = 'Data Logical Name' TO 'Path\DBName.mdf',
MOVE = 'Log Logical Name' TO 'Path\DBName_Log. ldf'
*****///

So which of those settings up there do you think is responsible for the restore log error?

Appreciate all the help..

sk

P.S: True that Log Shipping is available in the enterprise edition as a neat wizard but that shouldnt deter users of standard edition to use it with the help sql scripts and jobs. Not only that the underlying technique(behind log shipping) is just the same as what we are doing.... with the "minor" exception that this one isnt working yet...