Welcome to the 90^th edition of Log Buffer, the weekly review of database blogs.

First, SSQA.net’s SQL Master offers his walk-through of best practices for installing SQL Server 2005, with clustering as the destination.

If you read SQL Server blogs, you already know Adam Machanic. I’m very pleased to mention his first post for the Pythian Group blog, covering the basics of minimal logging and its enhancements in SQL Server 2008.

Also looking at Katmai was Bob Beauchemin, with his tip on accessing multiple servers with the SQL Server 2008 PowerShell provider, something right out in the open that nonetheless you might have missed.

Bob also figures out a little more about 2008’s new sparse columns and column_sets.

Joe Webb’s site mentions his appearance on Buck Woody’s Real World DBA podcast, where they tackle the question, does JOIN order matter?

On OraStory appears a very-commented post by Dominic Brooks, tantalizingly called, The dea(r)th of Oracle RDBMS and contracting?. From the piece: “I feel like the war has been lost and there are only a few pockets of resistance left now, resistance that will sooner or later be squashed. The database is under attack. . . . A newly created hierarchy have decreed that databases are indeed bad. . . . And I was speaking to a friend today at a previous employer, a major media / entertainment company. They are planning to abandon their pragmatic approach to Oracle and switch wholely [sic] to open source databases, ORM tools, and the like.”

And speaking of “Oracle versus X” (why doesn’t HTML have a <segue> tag?) — in last week’s LB#89, Shakir Sadikali criticized a post by Sean McCown’s Database Underground that compared Oracle and SQL Server to the latter’s advantage. Sean follows up the original piece, with this second item on Oracle’s community. He writes, “. . . one area I think Oracle has it over Microsoft is in its downloads. When I go to Oracle to download anything, all the downloads are clearly marked on a single page. Microsoft just isn’t like that. Sometimes even finding a service pack for SQL is like finding help for Oracle. . . . to those of you who said Oracle is easier to admin that SQL, you’re just crazy.”

(more…)

Welcome to the 89^th edition of Log Buffer, the weekly review of database blogs.

Welcome, welcome everyone.

In writing this week’s Log Buffer, I’ve had a chance to sit down and read some excellent posts on all sorts of platforms. The depth and breadth of what’s available to house and retrieve data is astonishing.

Many of you who have read my posts will know that I’m a fan of vegetables. They are something most of us don’t eat enough of. Come on DBAs! I think we need to make a collective effort to get healthy. We need you to keep all these systems alive. I say this because I have a new found appreciation for the work we do day in and day out.

Six months ago my wife and I said hello to our baby girl for the first time. I don’t say this to elicit any type of congratulations, but to illustrate something entirely different. If you have ever been to a hospital for any reason — to celebrate, to hope, or to say goodbye — you know the sheer complexity of the vast numbers of systems that need to interact. Daily, these systems save lives and help bring new ones to this world. I saw first-hand how the work I do on a everyday keeps the wheels turning.

Some of our customers run systems used by hospitals and I saw them in action. In a simple world, treating people can be done without technology, but this is an issue of scale, and our involvement directly affects the sheer masses of people whose lives are better because of our behind-the-scenes support. It’s true here, and it’s true for the most serious, most mission-critical systems, to the least critical and most trivial systems. The work done by DBAs from all platforms should be recognized for what it is.

I’m proud of what I do for a living and happy that I get to work in an industry filled with so many savvy folks. Oracle, Microsoft, MySQL, Postgres, IBM, and countless other organizations, and the people involved in them have together created an industry filled with opportunities and challenges, and above all, they have together elevated our ability to communicate and share. It’s in this spirit that Log Buffer was created, so let us proceed!

Since I’m an Oracle guy, we’ll let Oracle go first this time.

(more…)

Welcome to the 88^th edition of Log Buffer, the weekly review of database blogs.

SQL Server

To begin, Simon Sabin, on SimonS Blog, offers the proposition: SQL Server tools suck, do you agree? He elaborates, “When I moved from Oracle 7 to SQL Server 6.5 I was amazed at the tools you got with SQL Server. They made the product so much easier to manage and pickup. Things like enterprise manager, profiler and query plans where amazing. That was almost 10 years ago, and whats changed. Well very little.” There is some improvement with Katmai, he writes, but, for for most DBAs to benefit from it, it needs to be decoupled from the engine. There’s a poll.

Not one to merely complain, Simon also posts on what’s new in SQL Server 2008 tools.

Staying with things Katmai, in the Database Underground, Sean McCown has a critical look at Katmai’s backup compression. Here’s an excerpt: “[What] does Katmai’s backup compression offer? Well, it offers you the ability to compress your backups. Period. That’s it. . . . [What] about the implementation? . . . Well, simply, it’s too limited. Even if you could put up with the missing features, backup compression in Katmai is limited to the enterprise edition. Personally, that’s unacceptable as a backup solution because I don’t want to have several backup processes to manage.”

Systems Engineering and RDBMS has what a lot of SQL Server DBAs want, a basic overview of DTS vs SSIS, the data-transformation tools associated with SQL Server 2000 and 2005, respectively.

High Availability (SSQA.net) tell their story of setting up database mirroring using a local account with 2005. Three problems encountered and solved.

Kimberly L. Tripp issues the call for abstracts the fall 2008 SQL Connections conference, taking place in Las Vegas in November. Kimberly writes: “The conference will take place shortly after the SQL Server 2008 launch (when it actually RTMs, not the ‘launch’ that happened February 27th), and will focus heavily on SQL Server 2008. Abstracts are still welcome on best practices for SQL Server 2005 and how to upgrade and migrate applications from SQL Server 200x to SQL Server 2008. However, we will consider all topics . . .” And hey, it’s a paid gig, too.

(more…)

Welcome the the 87^th of Log Buffer, the weekly review of database blogs.

First up, a couple of items responding to news about H-Store, the new database technology. Nigel Thomas of Preferisco wonders if H-Store is a new architectural era, or just a toy?

Too much information, in turn, asks, Is H-Store the future of database management systems?

MySQL

Also aware of how technological change affects day-to-day DBA business, Dave Stokes of Dave’s Stuff has a tip for MySQL certification candidates: “[They] need to know about a little known but important function . . .” — PROCEDURE ANALYSE(). He writes, “It is mainly used to suggest optimal column sizes. Forty years ago you needed to worry how to properly encode data so that it would fit on an eighty column punch card. Thirty years ago when hard disks were the size of stove or dishwasher, there was a need to conserve as much space as possible. Today people carry gigs of data in their pocket and a terabyte of disk is available at local stores next to other consumer products. So you can be less than optimal in your storage of data for the most part. There are exceptions.”

Dave also asks that, as MySQL joins Sun and introduces their data into Sun’s systems, MySQL certification candidates update their email address info.

Matt Asay of the Open Road covers IBM’s announcement that is has ended development for MySQL storage engine, SolidDB. “Some among us (myself included) once worried that IBM was joining with Oracle to besiege MySQL when it acquired SolidDB, one of MySQL’s primary storage engines. It turns out, however, that IBM didn’t have such nefarious plans.”

Guiseppe Maxia, The Data Charmer, illustrates how the easy use of DISTINCT is lazy.

On Diamond Notes, a tip on speeding up imports by sorting, coming from a 60-hour, 106 million row MyISAM import.

CrazyToon offers answers to the question, how do you set up master-master replication in MySQL?, laying out the basics of this arrangement.

Baron Schwartz was mining the same vein this week. He has a piece on Xaprb on how to sync tables in master-master MySQL replication.

so many trails … so little time has an item showing how to sync two tables in MySQL.
“Usually replication is the suggested answer, but it might be a little overkill… In this case the right tool might be a mix of the new MySQL features, federated tables, extended insert synthax, stored procedures, events, triggers … quite a fest.”

(more…)

Welcome to the 86^th edition of Log Buffer, the weekly review of database blogs. Let’s jump right in.

MySQL

This was the week in which Sun Microsystems’ acquisition of MySQL went through. Wow, that didn’t take long at all! Zack Urlocker reports on the celebration at MySQL AB when the deal was completed, and looks ahead at the immediate future, which includes the “Meetup Mashup” tour. There’s a nice pic of MySQL CEO Marten Mickos and some of the gang looking well pleased, part of our ongoing reportage of DBAs with bottles. Zack has some more thoughts on the Open Sources blog, on integrating MySQL with Sun.

In his Technical Notes and Articles of Interest, Ronald Bradford observed the occasion and added some commentary — on the official Sun-MySQL website and the marketing material presented therein; the same on the mysql.com site.

The two organizations’ CEOs have been getting around this week. Robert Scoble conducted a (kind of watery) video interview with Sun’s Jonathan Schwartz. And guess who shows up — it’s Marten again!

Monty says some things about the first “normal” build of the Maria storage engine, and also some regarding the new arrangement: “I hope that within Sun we will get resources to change our current polices, priorities and in some ways the whole engineering organization to make the development model much friendlier to outside participants. It should be as easy for an outsider to get a patch into the MySQL server as someone working for MySQL.”

On MySQL Dump, Julian Swanhart discusses incrementally refreshing materialized views with MySQL 5. He writes: “One of my favorite features of the Oracle database is support for Materialized Views . . . I’ve often lamented that MySQL lacks this feature, but everybody I talked to seemed to feel that the feature was just “too big”, “too difficult” or “frankly impossible” to implement. Well, frankly, nothing motivates me more than telling me that something is impossible.”

Jeff Stoner, inhabitant of Stoner’s World, has an item about his use of monitoring MySQL and Red Hat Cluster. “In a perfect world, the only reason MySQL would pass between nodes is for server maintenance, under the control of a human. In reality, crap happens. Redhat Cluster manages all this, but I still want to know that a failover happened (is hardware going bad? did a software bug cause a failure? etc.)”

The DBA Dojo has an exposition on MySQL Multi Master-Master on EC2. “It is about improving the availability of your databases on EC2 and allowing easy backups without affecting either master instance. . . . I am going to test using mysql-proxy to perform that role of load-balancing a multi master replication cluster next. Then we are going to hammer the hell out of the configuration using mysqlslap, then sysbench and then the granddaddy of OLTP benchmarks DBT.”

(more…)

Welcome the the 85^th edition of Log Buffer, the weekly review of database blogs. Here we go!

Oracle

We start with the obscure. Eddie Awad has started the Obfuscated SQL Code Contest on his Oracle Community site, thanks to an idea by Chen Shapira. If you’re familiar with this contest’s antecedents, like the obfuscated C or Perl contests, you’ll know how entertaining it is to see people turn their creativity to code that should never exist in the real world. Like Rob van Wijk’s interrogative submission, for example.

Chen herself (just a simple DBA on a complex production system) posts about the right way to handle Oracle on NFS and TCP throttling, reaching into relatively dark corners of Linux, like net.core.rmem and net.core.wmem.

Even the ordinary business of DBA can seem obscure. “Are any of you that run RAC in your production environments backing up your archive logs to an FRA that resides in an ASM disk group (and of course backing up the archive logs to tape from the FRA)?” That’s what Eric S. Emrick asks in his post, RMAN, RAC, ASM, FRA and Archive Logs, FYI.

On Halis way, Hampus Linden shows how to delete an object with a special character in Oracle. He writes, “There are some things in Oracle that are possible but shouldn’t be possible. One thing I love to hate is the fact that you can create tables with almost any name, just as long as you double quote it. . . . Horrible! And what’s even more horrible is that people actually do this.” Hampus attacks the horror with some PL/SQL.

On the Oracle Scratchpad, Jonathan Lewis links to a well regarded article on using Statspack, writing, “It’s been a few years since I last read this article from Connie Dialeris Green of Oracle about how to use Statspack - and I’d forgotten how good it was. . . . If you want to get the best out of Statspack . . . you need to create and validate a sensible hypothesis based on all the information available. This paper instructs you in the method.”

Jonathan also has an item on pushing predicates. “Some time ago I wrote a note . . . about the push_pred() and no_push_pred() hints. I’ve recently discovered a bug in the 9.2 optimizer that means you may find that Oracle will not use ‘join predicate pushing’ when it is obviously a good idea.”

Tim Hall has an article about 11g bits and bobs, “ . . . covering the Miscellaneous New Features section of the OCP upgrade exam.”

From Igor’s Oracle Lab comes a ecumenical piece by Gary Myers, who asserts that, while databases differ, problem-solving approaches don’t. He writes, “This entry is more SQL Server than Oracle, but it is generic in some ways, and its also got the closest I’ve found to v$sql in SQL Server 2005.”

SQL Server

In the SQL Server world, SSQA.net’s blog brings the news that the cumulative update package 6 for SQL Server 2005 Service Pack 2 is now available.

(more…)

It’s the 84^th blog-tacular edition of Log Buffer, the weekly review of database blogs!

We begin with some Oracle security news. A tutorial of Oracle’s on defending against SQL injection attacks gets a good review on Pete Finnigan’s Oracle security weblog. Pete writes, “This is a superb tutoral, well written and positioned just right. . . . [It] starts by explaining what SQL Injection is with some good flash examples and how to avoid SQL Injection. It includes first order and second order attacks and also discusses reducing the attack surface, removing API’s, use of invoker rights, reducing arbitary inputs and more. . . . This is a good document and one of the best security documents I have seen from Oracle.”

Systems Engineering and RDBMS reports on Sequence Enhancement in Oracle 11g: “Starting with Oracle 11g, we can use sequences with straight variable assignment. Before 11g, we always have to use SELECT INTO clause to get sequence value in the variable.” Examples provided.

Christian Bilien demonstrates that the “log file sync” wait event is not always spent waiting for an I/O, it may be trying to get some CPU attention.

On her Oracle blog, Girlgeek is trying to lose a datafile — on a Windows system in particular, but some the post also has some comparisons with this experiment on other OSs.

Jonathan Lewis shines a light on a little bug in index rebuilds in Oracle 10g.

Cary Millsap has the first part of the tale of how OFA (Oracle Flexible Architecture) began. It comes from him, and it started like this: “So I created myself a standard.” Marco Gralike responds with his own story about OFA and other standards, such as SAME.

While I’m talking about oaky people and their stories, here’s Jason Arneil offering his book review of Tales of the Oak Table, not a new book but arguably a classic.

The Ardent Performance Computing blog posts this howto on Oracle Clusterware on RHEL5/OEL5 with udev and multipath.

On Preferisco, Nigel Thomas rounds up some recent posts on schema version control and offers his own perspective. “Code control is a walk in the park compared to ’schema control’. . . And ‘data control’ adds even more challenges.”

(more…)

Welcome to the 83^rd edition of Log Buffer, the weekly review of database blogs.

Little things can make big differences. Archimedes (no blogger, but a very smart guy) said, “Give me a lever long enough and a fulcrum on which to place it, and I shall move the world,” and With CLUE as (Select * from Random_Thought ORDER BY Common_Sense DESC) proves him right with a story of leverage: “This story is how changing one character in a 300 line stored procedure removed 90% of the impact of the worst single query on the entire server.”

On Third Abnormal Form, Paul McMillan shows that in SQL Server 2005, 1 and 0 may not quite equal True and False.

Mladen Pradić of I want some Moore writes, “Probably everyone is familiar with the Count(*) function in SQL Server. But there seems to be a great deal of confusion amongst youngsters (SQL wise) about how all its possible options work.” In Back to Basics: Count, Count, Count, Sum or how to Count, he gives a remedial lesson.

Roland Bouman has another little thing, the most misunderstood character in MySQL’s SQL-Dialect, the humble semi-colon.

On FruitCase’s Blog, Richard Case offers a little Task to run PowerShell scripts from within a SSIS package.

The SSQA.net blog gives a very nice introduction showing what it takes for a newbie DBA in resolving performance issues.

Eddie Awad covers Oracle SQL Developer’s better SQL formatter.

(more…)

Welcome to the 82^nd edition of Log Buffer, the weekly review of database blogs.

Some MySQL news to start. They call the engine Maria. “They” being MySQL AB co-founder Monty Widenius, and Maria being his new storage engine for MySQL. On his new blog, Monty Says, Monty says Maria is, “. . . a crash-safe alternative to MyISAM”, and continues with a very thorough FAQ.

On his MySQL Performance Blog, Peter Zaitsev responds to both the arrival of Maria and the new blog: “I’m really excited to see Monty speaking publicly again in the free form rather than in form of sanitized interviews and press releases we’ve seen during recent years. . . . It is still unclear how Maria will be integrated with MySQL (what version, which conditions etc) . . .  Initial version has two main benefits compared to MyISAM - it has page cache for rows . . . and it will be (optionally) crash safe.” Peter also describes his early experiences with the new engine.

Kevin Burton’s NEW FeedBlog has some links to other Maria-related blogs, and has Kevin’s own thoughts on Maria and SSD: “InnoDB was generally designed for use with HDDs. . . . However, MyISAM on SSD wouldn’t suffer form the same performance hit due to lock contention. . . . So why not ditch InnoDB and just go with MyISAM? InnoDB is a bloated beast compared to MyISAM.”

(more…)

Welcome to the 81^st edition of Log Buffer, the weekly review of database blogs.

We begin this issue with some more (and probably not the last) commentary on the acquisition of MySQL AB by Sun. On rand($thoughts);, Savio Rodrigues questions the idea that MySQL are not big enough for some customers: “I’m confused that Sun, and/or MySQL believe that the major impediment to MySQL growth was that MySQL wasn’t a big enough vendor to offer enterprise support. ¶ Huh? I guess Red Hat didn’t get that memo. Could something other than vendor size be relevant to a customer’s willingness to pay for enterprise support once an OSS product gets as ubiquitous as MySQL?”

On the MySQL Performance Blog, Peter Zaitsev has this to say: “It is very interesting for me MySQL choose to be bought out by Sun rather than going IPO even though [the] majority of the steps required for IPO already were done. This could be related to current market conditions or may be 1B price tag was at higher end what was expected from IPO. . . .  How much independence will MySQL management team have? . . .  How relationship with other partners will be structured - will Sun will be able to work with Oracle (Innodb Owner) well or will Falcon be pushed hard as Innodb replacement even if it is half baked?”

George J. Trujillo Jr. of MySQL DBA - An Oracle DBA’s Journey deems MySQL a change agent for open source. “Open success succeeding is a win for every individual and startup company on the Internet. . . . MySQL employees have the opportunity to be a ‘Tipping Point’ for open source. . . . Everyone on the Internet: Internet companies such as Google, Yahoo, YouTube, MySpace, bloggers, startup companies, individuals building their first website, hosting companies should all be rooting for the Sun acquisition of MySQL to be successful.” George also has a nice pic of The MySQL Story, a document given to employees of MySQL AB at their recent annual meeting.

Ronald Bradford of Technical Notes and Articles of Interest has a lot to say about the buy-out. For example: “Overall I believe it’s a good thing, on the surface and at the moment. . . . I’d would have liked the option to buy my own MySQL shares, be part of a company that got to that point . . . . [It’s] a shame for the pinnacle victory of Open Source, to become something of worth, to see the light at the end of the tunnel, but be tempted by the fruits enroute.” Ronald also gives us a list of things he’d like to see come of the new arrangement.

Linux.com has video interviews from the MySQL AB meeting (featuring Robin Miller’s mind-boggling microphone technique) with some notable people from both Sun and MySQL AB, such as James Gosling, Monty Widenius, and Brian “Krow” Aker. To quote Monty, “Expect the unexpected.”

(more…)