7.2 Update and Connecting to External MySql DB

[Kedgar]

Hello,

I worked this out today with the help of Eric at JAMF Support. We have our
mysql database located on a rhel5 mysql server rather than locally on the
JSS. With the 7.2.1 update, the layout of the Tomcat webapp directory
changed quite a bit, and the name of one of the config files I needed to
modify also changed.

For anyone this may help, here are the files you have to modify in order to
connect to an external DB:
/Library/Tomcat/webapps/ROOT/WEB-INF/xml/DataBase.xml
and
/Library/Tomcat/webapps/ROOT/META-INF/context.xml

I hope this helps someone out there

-Ken

[ernstcs]

Thanks for the information, Ken.

I am wondering if there are those out there willing to share some insight as to why they prefer to run their MySQL databases on a different box from their JSS? What advantages or disadvantages it might have? I am not saying it’s wrong to do so here, I’m merely asking the question and will appreciate anyone’s input.

We have a ‘dedicated’ MySQL server, but I haven’t been given a compelling reason to use it. To me it just seems like more work (I like to be efficient as possible, some may call it lazy) and another point of failure. Are there performance, security, or stability benefits?

Thanks!

Craig E

[Kedgar]

The main reason we are using it is first we have a dedicated mysql server as
you do; however we also do not yet have a good/standard backup solution for
the Mac servers. We are easily able to back up the database using
phpmyadmin and netbackup on the rhel server. Sad answer I know... Backup
will be a big project of mine this year I'm sure.

Thanks,
Ken

[tlarkin]

You can just use msql to dump the database and automate it with
launchd, probably pretty easy actually. Then you can rsync the back up
folder to another server or device for redundancy.

I was going to ask if you had a msyql admin as well that was in charge
of all msyql databases?

-Tom

[ernstcs]

This is good to know. I wasn’t sure if I ever mentioned how our backup here works for our JAMF resources in the event of our main system failing. This method is probably not recommended I’m sure, but it works. It does require you to have two Mac Servers available (or whatever you would consider a JSS backup if your main failed, like a Mini server perhaps).

The default main share location for Casper is CasperShare in /Shared Items/CasperShare/

Within /Shared Items/CasperShare/ I have a subfolder called Backups.

I have my scheduled JSS MySQL backups directed to this folder.

I have a secondary distribution point setup on my second server in the same location, /Shared Items/CasperShare

I have scheduled distribution point sync every evening, and it takes the entire CasperShare folder, so it also takes the Backups folder and it’s contents. I have this shares permissions and the CasperAdmin and CasperInstall users setup identical to my main server.

The beauty of this for me is that it just works with little effort using the tools provided by JAMF

If my main server fails:

Run the JSS Setup Utility against the second server to install the current version I’m on Populate the database with a restore of the most recent nightly backup of the JSS from the sync * Adjust my DNS pointer to the new server

Voila, all my packages and scripts are already there from the sync.

The bad:

From the time of the last sync to the failure that data and any new packages/scripts are missing/lost Copies of your SQL backups are exposed during Casper Imaging having the CasperShare mounted, unless you restrict the CasperInstall account properly for the Backups folder There is no method that I’ve implemented to alert me if a nightly sync failed or even ran so you have to manually verify as often as you feel to make sure it is running properly I’m sure there are more CONS that I’m missing here and there are perhaps better ways to deal with failing over if people want to share those as well. I know that in the past people have expressed interest in having fault tolerance in the JSS, but I don’t know where that stands with JAMF.

So perhaps that was a long and not necessarily on topic reply to yours, but I couldn’t stop myself.

Craig E

[donmontalvo]

Marko,

I spoke to Steve over the phone regarding our need to leverage existing MySQL servers in client environments.

Ditto on your request.

Don

[tlarkin]

I have 6 fully synced xserves for my Casper distribution points and
JSS. It would be really cool if I could sync mysql to all servers and
if one failed, it could auto promote a back up. That would be a great
feature request. That is technically how Open Directory works. All
your replicas can be authenticated to, if the master goes down. As long
as home folders can be mapped that is.

--missing content--

packages/scripts are missing/lost

Copies of your SQL backups are exposed during Casper Imaging having the
CasperShare mounted, unless you restrict the CasperInstall account
properly for the Backups folder

There is no method that I’ve implemented to alert me if a nightly sync
failed or even ran so you have to manually verify as often as you feel
to make sure it is running properly

I’m sure there are more CONS that I’m missing here and there are
perhaps better ways to deal with failing over if people want to share
those as well. I know that in the past people have expressed interest in
having fault tolerance in the JSS, but I don’t know where that stands
with JAMF.

So perhaps that was a long and not necessarily on topic reply to yours,
but I couldn’t stop myself.

Craig E

[donmontalvo]

One of our firm's mandates is to protect critical services so uptime meets our standard SLA. Spinning off MySQL to an existing, MySQL database server increases resilience of the infrastructure. It's important for our clients.

Don

[donmontalvo]

Yep, master/replica is sorely lacking in JSS. Given our firm's mandates, the current single-point-of-failure design of JSS is a problem for us. Some months ago our firm held discussions with JAMF regarding resilience/redundancy (MySQL was one of the concerns). Let's just say there was more than one feature request on the table that in the end would result in a much easier to pitch solution for our enterprise clients who have existing infrastructure (AD, MySQL, etc.) and significant Macintosh sub environments. Can't go into detail, but happy to report that JAMF were quite receptive to our needs and concerns. Looking forward to future releases... :)

Don

[tlarkin]

That is really cool. I have some friends that own their own web development company. I have been working with them on the back end to help sync their dev servers to their client hosts and it is all mysql (they mainly or almost exclusively develop with drupal). There definitely has got to be a better way about doing this. Mysql does have it's limits though I have found out, or maybe it is my lack of experience with mysql.

Either way, glad to hear that this will be a future part of the software suite.

[stevewood]

Perhaps I'm missing the boat, or just lucky that I'm not having to worry
On Fri, Apr 30, 2010 at 10:07 AM, Don Montalvo <donmontalvo at gmail.com>wrote:
about this, but I don't see where a Casper server would be considered a
mission critical service/server. I know that those in the edu name space
would argue with me, and I totally get that having your JSS down for 10
minutes means 10 minutes without policies being enforced, but still does it
require as much "five 9's" as is being discussed? In my previous life, at
my previous company, we needed five 9s on everything, so I get it and
understand the importance of it, just not here.

Let's assume your JSS goes down, maybe a hard drive failure that takes out
the entire server. Assuming you have backups of the JSS data using the JSS
Setup Utility to schedule backups, and that you have a backup of your Casper
Share, how long would you be out if you had to switch to a new machine?

I'm sure most of us have an image of our server hard drives, right? So I
boot that server off of an external drive, or boot a different box off of an
external drive, re-install the JSS on that box, restore the data from backup
and I'm back online. Total downtime maybe 30 minutes, give it an hour to be
safe.

During that hour of downtime, what have I truly missed? Maybe some policies
being pushed. Again, maybe it is that I'm fortunate to not have to worry
about having to keep script kiddies out of my machines, or maybe I'm getting
lax in my administration since I have one shop to worry about.

And if I recall, and it's been a few years, do LANDesk and Altiris have
failover capabilities? I mean, these are the two big hitters on the PC side
of the house. I remember when we were looking at both at my previous
company, but do not remember if they had failover capabilities.

Enlighten, please, as to why the JSS is such a mission critical piece in the
enterprise.

Steve Wood
Director of IT
swood at integer.com

The Integer Group | 1999 Bryan St. | Ste. 1700 | Dallas, TX 75201
T 214.758.6813 | F 214.758.6901 | C 940.312.2475

[localhorst]

Hi,

On 29 Apr 2010, at 22:00, Ernst, Craig S. wrote:

we operate a high available MySQL cluster using DRBD and heartbeat. In addition to this, the two cluster nodes run on a VMware vSphere kit which automatically moves/restarts them in case of a physical host failure to another ESX host.

As this cluster is well looked after (ie. daily backups (including regular restore tests), Nagios monitoring, log scanning, regular software patching), we do not operate any additional MySQL database. For us it would cause definitively an overhead to patch, backup and tune additional database servers. In terms of performance I am tempted to say that an optimised MySQL cluster should outperform a MySQL instance running with default configuration.

Another benefit from moving the database off the JSS server is the ability to deploy the JSS servlet to another Tomcat instance ie in case of a hardware failure. As a minor security related remark, I do not like the idea of all JSS instances operating with the same database name, user and password combination. So when you have to modify the two mentioned XML files anyway, you may want to choose unique credentials.

Unfortunately, the JSS setup utility does not support external database servers. Every time you upgrade the JSS, you have to upgrade your JSS database manually and re-apply the changes to the servlet configuration to connect to the external database server.

Therefore I would like to raise the feature request to JAMF to extend the JSS Setup Utility to support external MySQL databases as well as using Tomcat for serving the CasperShare via http/https.

Best,
Marko

--

Marko Jung
NSMS - Oxford University Computing Services

[#13]

I realize this isn't within strict confines of the topic; but, I thought I'd offer it up as an example of my personal workflow.

I'm consulting with a school district that has some pretty strict regulations as to what services can be on the network. For better or worse (mostly worse), I am not allowed to run an official external database off of the production JSS server (a SLeo Xserve on the farm).

I personally use Navicat on both my workstation and laptop (mobile workstation). Navicat is scheduled to grab a backup on the workstation every hour and places it on my backup server (Leo Serv on a Mac Pro at my desk) and the laptop then syncs in the morning and then at the end of the day (I'm running MAMP and Catslapper (essentially, MAMP for Tomcat) on my MBP for multiple projects).

This was the only way I could actually get a working backup off the production box within the artificial limitations set by my client.

It's a frustrating situation; however, the upside is that I've learned quite a bit about the structure of the JSS database and have definitely found some ways of leaping hurdles when mass editing records...

Obviously, Navicat isn't necessary for this workflow (it's just a tool that I'm personally fond of and use).