Wm6 ISCoreAuditProcAudit Xref Single vs Multiple databases

[jplesmid]

IS601 is able to make use of many sets of database tables for a variety of purposes. EG.ISCoreAudit, ProcessAudit, Xref, Trading Networks, RepoV2, RepoV3.
My question is w/regard to the ISCoreAudit, ProcessAudit and Xref tables. In a SQLServer2000 environment, is it better to create a database for each of these tablesets, or simply create a single database for all of them? Currently, I have all of the table sets defined within a single database. My concern is performance. If these tables are heavily used, it seems like it might be a good idea to move the table sets to separate SQLServer boxes. For example, ISCoreAudit one one server and ProcessAudit on a second. If the tablesets are created in separate databases it would seem like it would be easier to move them to separate boxes.

Has anyone done this? Are these tables heavily used? IF these tablesets are in different databases, does that cause problems with runtime queries? That is, are there joins that reference tables between table sets?

I would greatly appreciate any thoughts or experiences on this subject.
Thanks

[wleishman]

Hi John, we struggled with this question too and decided to create the following databases in MS SQL 2000 (initial sizes are best guesses for our development environment. See note below):

1. webMethods Audit database (db name: wmaudit):
Userid: wmadtuser
This database will contains tables for IS Core Audit, IS Process Audit, Key Cross Referencing and Modeler Repository.

IS Core Audit Log tables: contains auditing information about service execution (audit, error, session, and guaranteed delivery) on the Integration Server.

Process Audit Log tables: contains auditing information about processes. These include processes generated by Business Process Modeler. The webMethods Monitor

Initial size: 50MB

2. webMethods Cross Reference database (wmxref):
Userid: wmxrefuser

Key Cross Referencing tables: contain information required to synchronize updates among various applications and the databases they reference. For more information about this database and its contents, see Building Integration Solutions Using Publication.

Initial size: 5MB

3. webMethods Modeler database (wmmodeler):
Userid: wmmdluser

Modeler Repository tables: contains saved Business Process Model Information

Initial size: 5MB

4. webMethods Trading Networks database (wmtn):
Userid: wmtnuser

This database is used by webMethods Trading Networks to store all information about the trading network partner information, the types of documents to process, how to process business documents, information about business documents that pass through the network, log information, etc.

Initial size: 15MB

5. webMethods Workflow Database (wmworkflow):
Userid: wmwfuser

The user needs permission to create, alter, and drop tables in the Workflow Server table space.

Initial size: 15MB

6. webMethods Repository Database (wmrepository):
(FUTURE - we haven't set this up yet ) We are still trying to decide if we will use the shared raid for the repository or a database.
The repository tables store the conversational state of all servers in the cluster, allowing transactions in a conversation to be continued on another server in the cluster when the original server fails. The server uses it to keep track of scheduled jobs, session context, and jobs tracked by guaranteed delivery.

Note: the above database split seems to make sense. It may seem like overkill to have separate databases however we feel it makes sense, especially for db management and performance reasons. We didn't read this in any webMethods doc. If anyone knows of a webMethods doc that recommends something similar or different please let me know.

Regards,

Wayne

[jplesmid]

Thanks very much Wayne. Your post was very helpful.

I agree with your note. It does seem like spitting up the different table sets makes complete sense w/regard to performance and management reasons.

Since I am already going to be creating all of these databases. I think I will try and split up the ISCoreAudit and ProcessAudit table sets. It seems like both of these are going to get hit pretty hard. I have not done this yet and I guess I still don't know if splitting these table set into two database will cause runtime query problems. Iguess I will find out.

Also, w/regard the the repository2 decision. My two cents would go with using a database. We originally used the file system based WmRepository2 and had nothing but headaches. The worst of which was the "Too many files open" problem. We also had corruption problems when we mis-configured our initial IS cluster. We now use a database repo2 and are quite happy with it. Another issue with the file based repo2 came up when we attempted to migrate from the local WmRepostory2 to a dual reposerver accessed repo2 (when we clustered). This simply did not work well.

Thanks again!

[wleishman]

John, thanks for the feedback on the repository. We have decided to use a database instead of files for the repository.
Do you have any recommendations on size/configurations for the repository database ? This information doesn't seem to be in the webMethods docs.

Wayne

[jplesmid]

I don't really have any exact suggestion w/regard to the initial sizes of any of the databases. W/regard to the Repo2 database, I know that a filesystem based repo2 can use up 100-200 megs over time and it does not seem like webMethods is real good at getting rid of "dead" data.
Since having a database run out of space is very bad, I would suggest over allocating space (disk is cheap - having your pager go off at 2AM is bad). I would then just have the DBA periodically monitor real disk usage (pay attention to the database log!) and make adjustments as needed.

[jbraunstein]

Sizes will of course depend on transaction volume, size, whether your logging the pipeline, etc.

Database or schema seperation will largely be due to performance. Can the DBA's ensure I/O intensive processes are seperated in different tablespaces/disk spaces and each Db has enough memory