Tuesday, December 28, 2010

Heterogeneous Service for Oracle and Microsoft Access

Charlie Weaver, The Grumpy DBA
Follow me on Twitter: http://twitter.com/GrumpyOracleDBA

Yesterday, I was on Twitter and saw a blog post from Mudafiq titled “Heterogeneous Service Configuration For Connecting Oracle With Ms. Access”. This got me thinking, your query can only be as fast as your slowest data source.This feature has been around Oracle for a while and has been relabeled as Oracle Database Gateway. The figure below – from Oracle’s documentation – shows how the gateway connects to non-Oracle ODBC data sources. Plenty of moving parts that can go wrong in my book. In this blog post, I’ll explain how to think about Microsoft Access Jet databases for your Oracle environment.

Oracle Gateway Architecture heter004

Mudafiq does a great job of explaining how to setup the service to connect to Microsoft Access through ODBC, but doesn’t mention what you should consider when creating linked data sources.

Using a Microsoft Access Jet Database as a Linked Data Source

Microsoft Jet has “plagued” the organizations that I’ve worked in and admittedly I’m a fan of Microsoft Access Jet based solutions where it makes sense. I myself have set up quick and dirty Access applications for folks outside of IT because business analysts needed a solution in a hurry and I knew it would have a limited lifetime. Microsoft Access is a cheap way to help me pry business requirements out of them and show them quickly before going to the big-iron Oracle systems where process can drive good natured folks to cry in frustration worrying about SOX, PCI, etc. 

Developing an Archive Plan for Access Jet Databases

Key to my keeping my job, was making sure that users knew what the lifetime of their databases should be and then let them know the MDB file was going to get archived once the project was over. This way, we could keep out network from getting bogged down with copy upon copy of the same database – just because no one knew the origin – and kept making copies of the MDB file – thinking it was still important.

A few years ago, a buddy of mine at a North Carolina based bank told me they had over 30,000 MDB files scattered across their network file systems! Yikes, the first thing I asked was – did you look at the date the MDB file was last accessed. Only 700 had been accessed by folks over the last year. Of those, only 30 were “production” data files used at least once per week. Once they archived the files, the network disk admin was quite happy having a lot more free space on their hands.

Should Business Critical Data Really Be In Jet?

Before you start linking up Oracle to Jet, ask yourself, should the data really be in Jet? Microsoft Access does a great job of linking to ODBC data sources like Oracle. So ask yourself a simple Dirty Harry question “Do I feel lucky…?” having important data in Jet.

“Do you feel lucky…?” having your data in a Jet database

With Mudafiq’s solution, you get the impression that once you set up the link, you can query Jet data just like it was an Oracle table. We’ll, there are plenty of things to consider:

  1. Where is that Jet MDB file sitting? Under someone’s desk – most likely!
  2. Is the Jet MDB file getting backed up? When there is an active connection to a Jet MDB file, the Jet file can often not get backed up using non-Access file system utilities.
  3. Do you have highly sensitive data in the Jet MDB? In earlier versions of Access, Jet had an encryption system that was relatively easy to hack. Having your MDB file on a drive using BitLocker can help, but BitLocker is only available for Vista or better on the client, or Windows Server 2008 or better on the server. Imagine if someone had an unprotected laptop with a Jet MDB file with your sensitive customer data and it was lost – not good.
  4. Do you expect more than 25 users to access the Jet database and anyone time? I know a few developers who can architect an Microsoft Access solution using a Jet database that can handle hundreds of concurrent users, but not everyone has that expertise. Now, with the Oracle Database Gateway, the Jet database could become a big hotspot for queries – especially if they are wrapped with a SYNONYM and your development team isn’t aware they are actually connected to a Jet database table.

Putting Your Critical Jet Databases Tables on a Real Database

In my book, if the data is that important, it’s much better to have the data in the Oracle or SQL Server database and update the Microsoft Access solution to use a Linked Table to the big iron box. You can use the Oracle Database Gateway and SELECT INTO a new table or use the Oracle Application Express to move the data. I highly recommend you only migrate the data for all but the simplest of Microsoft Access applications if you don’t want to irritate your “customers” using the Access application.

Since I work in a mixed SQL Server and Oracle shop, I’d be neglect to mention that you can also use the SQL Server Migration Assistant for Microsoft Access to move the data to SQL Server and automatically setup the link in the Access solution. The latest 4.2 version is pretty solid and includes the ability to also put the Jet tables on SQL Azure.

You can then use the Oracle Database Gateway for SQL Server to reliably connect to business critical tables that are on the Microsoft platform. With the Oracle Database Gateway for SQL Server, you have a full transaction model available with two-phased commit to your development team.

Have a Happy New Year,
The Grumpy Oracle DBA



Thursday, December 23, 2010

Oracle TPCC Performance Benchmarks

Charlie Weaver, The Grumpy Oracle DBA
Follow me on http://twitter.com/GrumpyOracleDBA
December 23, 2010

'There are three kinds of lies: lies, damned lies, and statistics.' Mark Twain  If Mark Twain were still alive, he would add vendor benchmarks to the quote!

Oracle Announces Record TPCC Benchmark Performance

On the front page of today’s Wall Street Journal (print edition), Oracle proclaims setting the world record in performance using Oracle SPARC SuperCluster with T3-4 Servers with 30 million tpmC.

Oracle WSJ Claim TPCC198746

If you dig a little deeper, you’ll see that the system that Oracle it testing against won’t be available June 6, 2011. What’s up with that? Check out Oracle’s information at http://www.oracle.com/us/corporate/features/sun-oracle-faster-072797.html. By going to http://www.tpc.org/tpcc/results/tpcc_perf_results.asp, you’ll see IBM at number two with 10 Million tpmC. That’s well and good, since TPC.org tested using DB2 version 9.7 on an IMB Power 780 Server Model 9179-MHB system. Then Oracle as the nerve to dig HP by going to #5 on the list to compare the HP Integrity Superdome Itanium2/1.6GHz/24MB iL3 using Oracle Database 10g R2! What’s up with that? Why not run the tests again with 11g R2? Is Mark Hurd that bitter against his former employer?

A Greener Planet with HP and Microsoft SQL Server

I encourage anyone who looks at benchmarks to poke around http://www.tpc.org. For example, if you look at the Top 10 Watts/Performance Results, HP with SQL Server 2005 comes out on top.

Microsoft Greener

One can only imagine how SQL Server 2008 R2 would perform in the “Green” test.

Oracle continues to simply ignore Microsoft SQL Server, most recently at Oracle OpenWorld this year. When I first attended Oracle OpenWorld in 1997, Larry Ellison would often poke fun at Microsoft with quotes like – would you want to run your enterprise software built my a company that ships Microsoft Dogs. At OpenWorld 2010, none of the keynote addresses mentioned Microsoft. – see http://www.oracle.com/us/openworld/keynotes-143370.html

Microsoft SQL Server Does Decent in Price Per Transaction Too

Here is an example of how well Microsoft does on TPC-C Price/Performance – You’ll notice that Sun hardware is not in the picture.

Microsoft In the race for TCO with TPCC

Is TPC-C Relevant Anymore?

What drives me nuts is that Oracle continues to insist on using the TPC-C benchmark which was established 18 years ago.

Today’s workloads are more complex that TPC-C. Oracle just wants to stick to TPC-C, while other vendors realize that TPC-C can be “gamed” and have promoted other workloads like TPC-E (simulates an OLTP workload based on a brokerage firm) and TPC-H (simulates an adhoc decision support environment). One has to wonder why Oracle doesn’t participate in these newer benchmarks. To Oracle’s credit, the acknowledge the benchmarks - http://wiki.oracle.com/page/Database+Benchmarking. They just don’t want TPC.org to test against them.

Oracle Real Application Testing

In practical terms, Oracle’s Real Application Testing provides the best way to assess your businesses actual workloads when comparing hardware and software configurations. This is because, Oracle RAT provides record and playback that includes an option to “turn up the amp to 11” – from Spinal Tap.

Microsoft SQL Server Profiler and Replay with Denali Release

One of my SQL buddies who attended SQL PASS 2010 in Seattle this year mentioned that Microsoft is answering Oracle RAT with SQL Server CAT (Change Assessment Toolkit) for the Denali release of SQL Server. They mentioned that Joe Yong in his session “Future SQL Server Upgrade Planning Tools” that Replay can be configured to use multiple workstations and threads per workstation to playback SQL trace files just like Oracle RAT. The chase is on!

Don’t Believe What You Read in an Ad

In my book, benchmarks – not matter who runs them – need to be taken with a “gain of salt”. Hardware and software are always changing and it’s almost impossible for mere mortals to reproduce the results without a team of experts from the database vendor to tweak every once of performance from the system.

In my world, it’s all about getting the application up and running as fast as possible. As long as it’s fast enough, my users don’t care and certainly can’t afford some of the Oracle/Sun high end machines. I’ve watched some of my DBA buddies working with SQL Server stand up a database and get a new application up and running for their users in days. With all of the audit processes that I need to worry about for our Oracle applications, sometimes I have to sigh with envy….