I’m currently in the process of installing R4 (Coming from XIR2 at this company) and I’m trying to figure out whether I should just leave the default SQL2008 express as the CMS’s repository, or if I should point it to one of many full MS SQL Servers we have.
Our XIR2 was set up to use a standard MS SQL Box as it’s repository.
Can anyone point me toward any benefits/cons or considerations I should think about as to whether or not to keep the repository pointed to it’s local SQL Express vs repointing it to a full SQL?
Since it’s a new installation, there is no data to worry about just yet.
My gut is telling me to use a full SQL (The voices in my head are saying it’s bigger, better, faster), but part of me is thinking SAP wouldn’t have packaged it directly if it wasn’t up to the job.
I thought I remember seeing a pro/con document from XIR3 years ago but I cannot seem to locate anything like that today.
I started looking into the Monitoring system today and found out it has yet it’s own Database, which is stored in java tables by default.
You can now , as of SP04 I believe, switch it to use the standard Audit database.
That led me here, to my original question…
Does anyone have an opinion on the SQL express vs full SQL? We have SQL 2008 now on the same box running some other processes so it’d be an easy move… I’m just wondering if we’d get better/worse performance vs the default…
And if I do switch at this point… anyone have a good migration document
SQL Server Express only utilises one CPU at a time. It can be installed on a server with multiple CPUs, but it will use only one CPU at a time.
SQL Server Express uses a maximum of 1 GB memory for it’s data buffer. So, if your server has several GB memory, SQL Server Express cannot take advantage of it.
Aha! I knew there had to be better performance but didn’t know WHY I thought that
Now the fun task of trying to figure out how to migrate my my existing data to a full server… if I recall correctly and if it works like XIR3 there was some sort of option when changing databases to move data with it… will have to explore that a bit once I can verify a backup has taken place hehe.
I’m also assuming that once I’ve migrated the Audit and CMC databases (and new monitoring I suppose) that I can shut down the SQL Express server without any ill effects… That should free up a few resources I’d guess as well.
If you had the DB install on your BO server, you should notice a significant performance gain, when moving it to a dedicated machine, providing your network is good and the boxes are physically close together.
Hah I don’t know whether to laugh or cry… They are physically close… in fact they are the SAME machine… (I did not choose this my boss thinks it won’t be a problem… )
Our BOBJ Server (including Apache) is the same physical box as the MS SQL2008 box which now holds the CMS database. All I am trying to do at the moment is reduce the resources by being able to shut down SQL Express (which is also on the same box).
But it gets better…
The DATAbase (I hesitate to call it a Data Warehouse as it does not conform to any standard and doesn’t even have fact/dim tables nor keys… ) Is on the SAME box as well!!
Did I mention we don’t have a Dev environment either? (Go ahead, you can laugh… I always do)
The only good thing about the scenario is this box is a BEAST…
It’s Running 2 Xeon CPU E5649s at 2.5GHz… (2 dual core cpus with hyperthreading… shows as 16 cores to the OS) .
Running Server 2008 R2.
It only has 16 gigs of memory at the moment, but I believe we have another 32 on the way…
Finally as soon as I can get some sort of ‘downtime’ on this box, we’ll be installing a 500gig SSD PCI card to be used for all the SQL transaction log files, which should improve the speed of all SQL queries substantially.
I still don’t like the idea of data being on the same box as the Tomcat/CMS , but until I can prove it won’t work… I’m stuck with it.
FYI: Migrating was a snap… created an ODBC for the box… told it the new source and destination and selected to “copy” my data… everything looks intact.
EDIT: Update for anyone else who ends up moving their audit database… the BOBJ documentation is missing a step.
Changing the Auditing connection was inside the CMC under “Auditing”, but it’s a text field and you must type in your ODBC connection name exactly in order to re-assign it to another connection.
After you re-assign the audit database to your new connection, the docs say to restart the CentralManagementServer (It shows up as Stale in the sever list as well, indicating a restart is needed).
However the ACTUAL step requires you to restart your entire SIA process.
I only stumbled up on this after about 30 min of banging my fist trying to make sure it wasn’t a password issue, or security or something, as it kept telling me it couldn’t connect. A seperate issue required me to restart the SIA real fast, and as soon as I did that… all my audit issues went away.
Once you move the two databases (CMS and Audit) you can safely shut down the SQL Express service(s) via windows. One is the Server Agent and the other is the SQL Service… Be sure to change your SIA’s service dependency to your new server and remove the dependency to the old SQL Express service. (It’ll warn you if you forget).
Hopefully this helps anyone else trying the same thing.
JPetlev, I do hope you are still monitoring this thread because I need your assistance…
I am basically in the same boat as you are: CMS 4.0 is on SQL Express and we want to move it to SQL Server Enterprise. Both Express and Enterprise are on the same box.
I am in SIA Properties > Configuration > Specify > Copy from another data source
How do I make CMS see the SQL Enterprise instance (which is called BOE140_SQLENT) ?
So my question specifically lies in the “created and ODBC for the box” part
FYI: Migrating was a snap… created an ODBC for the box… told it the new source and destination and selected to “copy” my data… everything looks intact.
You have to create a system DSN under Control Panel>Administrative Tool>Data Sources ODBC.
You have to make sure the user account is set up correctly at the SQL side with read / write access to the SQL database, prior to doing this.
(BOB member since 2005-01-06)
(BOB member since 2005-01-06)
)