SharePoint 2007 Developer’s Guide to Business Data Catalog

SharePoint 2007 developer’s Guide to Business Data Catalog is an essential guide to anyone who wishes to become proficient in building complex SharePoint applications. This book starts off assuming that the reader is a complete beginner to BDC, and explains in detail what it is. Soon afterwards, the reader is taught what can be done with it and how.

The book is divided into 11 chapters. Chapter 1 provides information for what BDC is. The BDC is a layer of defining heterogeneous data sources so that the SharePoint is aware of them. The data sources can be any of those: Microsoft SQL Server, Oracle, ODBC (Open Database Connectivity) and Web Services. Chapter 2 discusses the Application Definition File (You can think of it as being a configuration file). The ADF is used to tell SharePoint to get the data from those different data sources. Chapters 3 through Chapter 7 examine the security which allows users to use different authentication methods within the BDC and the out-of-the-box functionality of BDC can be configured through the application definition file. I think the configuration is the responsibility of SharePoint system administrator, but the lucid writing style of the book makes it easy to understand by most developers. Chapters 8 through Chapter 11 delve deep into the customized solutions which are really the job for developers. This book provides the namespace and DLL and sample code to allow developers easily to follow.

Most SharePoint books will spend one or two chapters to describe the BDC; this is the first book I have seen that provides a detailed analysis of a specific concept of SharePoint. Users should be aware of that the BDC is a component that can be used only on Microsoft Office SharePoint Server 2007 Enterprise Edition.

Reviewed by Henry

The Art of Unit Testing

As a Database Architect, I often work with Object-Oriented Programmers, but thankfully don’t often have to code in other languages other than SQL.  However, it is useful to understand the methods and basic frameworks by which my team members develop; hence, I decided to review Roy Osherove’s The Art of Unit Testing (Manning Press, 2009).  Although many of the examples in the latter half of the book were a little over my head, I do appreciate the fact that Osherove builds to that point in a simple, easy-to-follow manner.

The first chapter was particularly useful to me, because the author lays out some very basic criteria for defining a unit test, which I’ll paraphrase below:

• Is the test repeatable after a period of time (years, months, etc?)
• Is the test portable?  Can other team members run the same unit test?
• Is the test simple to run?  Can it be run with the push of a button, and in just a few minutes?
• Is the test simple to build?

If the answer to any of the previous questions is no, then you’re probably not doing a unit test; Osherove argues that you’re probably doing some form of integration testing, which may be useful, but is not a true unit test.  His argument for the art builds from there.

This book is very well laid out, with lots of examples (written in C#).  It’s a short read (under 300 pages, including indexes), and has a straightforward style that lends itself well to both the beginning programmer (or programmers from other traditions, like myself) all the way to experienced programmers who want to refresh their memory on basic concepts.

SQL Server 2008 Administration in Action

Rod Colledge’s book SQL Server 2008 Administration in Action is a great asset for most SQL Server DBA’s; it covers a variety of issues in a simple-to-understand format. Let me preface this post by saying that while I am a DBA, I’ve been doing mostly development work for the last few years. However, armed with this book, I felt like I could easily dive back into administrative duties.

The book is broken up into three sections, each with several supporting chapters: Planning and Installation, Configuration, and Operations. Each chapter does a relatively deep dive into issues by starting with basic definitions, moving quickly through options and concepts, and then finally wrapping up with a bulleted list of best-practice considerations. If I had but one suggestion to make for the publishers, it would be that they consider consolidating all of the checklists into one document. What is probably the most amazing fact to me is that the book is very short (compared to some other administrative guides); it’s only 440 pages, including the index. It’s just well-written, straight to the point, and focuses on just the important stuff.

I also like the Appendices at the end, especially the Top 25 WORST practices, and the basic schedule in Appendix B. Although it’s probable that most DBA’s will have much more complicated schedules than described herein, it’s a useful template (especially for those “accidental DBA’s”). Overall, I really liked this book; I think it will be very helpful for me in the future (especially as I begin to study for the 2008 certification exams).

Here's an interview with the author:

Also, you can download a few sample chapters:

Sample chapter 4 Installing and upgrading SQL Server 2008
Sample chapter 10 Backup and recovery

Stu

UltraEdit Text Editor 15

Most SQL Server DBA’s are used to dealing with data that comes in via some other method than a database connection; flat files, csv’s, and XML files are all too common.  However, as databases get larger, and XML becomes more prevalent, I don’t think I’m alone in suggesting that Notepad doesn’t really cut it as a text editor anymore.

Quick case in point; last week I was charged with importing a 32 Meg XML file into a database so that we could use some of the values to update one of our older security scanning tools. Unfortunately, while the XML file was valid (e.g., all tags were closed), SQL Server was screaming about certain characters not being valid XML characters.  Rendering the file in IE 7 killed IE.  Wordpad would open the file, but 32 megs of text yields something along the lines of 300,000 characters; not exactly easy to scan and edit.

Enter UltraEdit.  I downloaded the 45-day free trial, and went to work.  SQL Server identified the line and character position of the invalid characters in the xml column in my scratch table; I opened the file in UltraEdit, used the Goto Line command (including the column number) and discovered the first invalid character: the trademark symbol, or ™.  I edited it out, used the find and replace feature to find the rest of them and do the same, and uploaded the file to SQL Server again.  Using XQuery on the new imported contents gave me a new character position, so I repeated the process.  After about 10 repeats (less than an hour of time), I had a clean XML file, and was off to the races.

UltraEdit was also very useful when querying the file; I could use the XML manager to explore and identify the nodes far better than the XML parser included in SQL Server Management Studio (which choked when trying to open the whole XML value).  This was a great help when attempting to write valid XQuery statements to select particular nodes, attributes, and values from the database.

I realize that UltraEdit is more than an XML editor, and that there are probably better XML editors out there, but I was very pleased with how easy it was to use it for this particular project.  I’m hoping that I’ll have time to more fully explore it’s capabilities for authoring scripts, etc, in the future.  The cost for a license is only $49.95, and it was well worth it to solve this particular problem.

How to become an Exceptional DBA (Second Edition)

I downloaded this free ebook from RedGate and quickly perused it in a couple of hours; Brad McGehee lays out an interesting set of observations about what is necessary to transform from an average DBA to an exceptional one.  To be honest, there weren’t any hidden secrets here; most of the book deals with personal work habits (which you probably already have or you wouldn’t be interested in being an exceptional DBA).

There were a few gems here and there, like Brad’s suggestions on how to become an MVP, as well as how to manage your career; however, even these were not necessarily new ideas, just general concepts that were slimmed down and located in a single easy-to-use guide.  I don’t regret reading this book, but at the same time, I didn’t really walk away with any new insights either.

If you’re a new DBA or are planning to transition to a DBA role, you may find his overview of the career path useful.  Also, if you’re just now beginning to expand your skillset, there is some basic information on how to do that.  However, if you’re already working long hours solving problems and you enjoy your job, this ebook may not be the best use of your time.  You’ve probably already encountered these same ideas along the way; spend your time building a network of database associates instead.

Pros:

• FREE!  Can’t beat the price!
• Concise explanation of what makes a DBA “exceptional”
• Overview of community-building

Cons

• Nothing really new under the sun

SQL in a Nutshell- 3rd Edition

Kevin Kline’s SQL in a Nutshell (like many of the technical books from O’Reilly) is one of those essential desktop reference books that every programmer should have access to; it covers several different flavors of SQL, including MySQL, Oracle, PostgreSQL, and SQL Server. The nice thing about this book is that it uses the 2003 ANSI SQL as the foundation, and then attempts to tie in the various flavors back to that source.  While this gives you a great overall picture of how the various database platforms interact with another, it does make it a bit difficult to translate from one dialect to another.  In other words, if you know how CHARINDEX works in SQL Server, it’s difficult using the book to figure out a comparable function in MySQL.

Despite this limitation, this book does provide a very useful codex for the major dialects of SQL.  IT’s worth having a copy if you are skilled on one platform and need to interact with another flavor of SQL.

3 of 5 stars.

Dynex Optical Mouse and Multimedia Keyboard Combo

My wireless mouse and keyboard died last night. I replaced the batteries, and got nowhere with it; since I needed the keyboard and mouse for work today and this was the THIRD wireless mouse to die in the last two years for me, I made a quick trip to my local Best Buy to buy a wired keyboard and mouse combo. I sit near my computer, so it made sense (why keep wasting batteries?).

Wired keyboards and mice are hard combo to find; there was really only one option at the store, and it was the Dynex Optical Mouse and Multimedia Keyboard. I’ve never been a big fan of Dynex products (you’ll find out why in a minute), but since I really had no choice, I decided to pick one up. It was cheap (you get what you pay for), and the display model looked OK. I bought it, and left it on my desk for installation this morning.

Install went fine (mostly); I plugged in the keyboard and the mouse into the USB connections on my KVM switch, and I immediately had a basic keyboard and mouse functioning. However, this is a MULTIMEDIA keyboard and mouse combo; there’s extra buttons on both, and I wanted to make them work. Since there was no install disk, I headed over to the Dynex web site to find drivers. I’ve done this before for other Dynex products (hence my distaste for the brand), so I thought it was going to be relatively easy.

Well, the combo wasn’t listed as an option for download, so I looked for drivers for the individual components; the mouse was easy: DX-WMSE, and there’s the driver right on the page. Download, unzip, install. Done. The keyboard? DX-WKBD. No driver on the page. Hmmm. Click the Drivers link on the bottom left of the page, and then the Input Devices drop down. No listing for my keyboard; also, no listing for the mouse driver either. At this point, I begin swearing softly.

They have a support number, which I call, and I speak to a very nice representative named Mike, who proceeds to tell me that the keyboard doesn’t require a driver. I tell him that the basic functions are working, but none of the multimedia keys work; he asks me to uninstall the keyboard and reinstall, and then finally utters that panacea of support technicians everywhere – REBOOT. I tried all of those, and got nowhere. I thanked him for his time, and hung up. Since I don’t need the multimedia functions, I can make do until I need a replacement. When I do need a replacement, it probably won’t be a Dynex.

PROS: Cheap keyboard and mouse combo. Basic functions work well.

CONS: No drivers available for the multimedia functionality. Other reviews seem to indicate that it does work out of the box, but I couldn’t get it to work for me.

EDIT: I’ve revised my rating, because I determined that the USB switch on my KVM switch was interfering with the device discovery; when I plugged the keyboard directly into a PC, the functionality worked. I still think that there should be some sort of drivers associated with the keyboard, but it’s unfair to give this a low rating because of that.

2 of 5 stars 4 of 5 stars

SQL and Relational Theory: How to Write Accurate SQL Code

When I first got this book I was very excited; the author, C.J. Date is one of those names that you hear tossed around with Codd. I was hoping that this book would be a good refresher on basic SQL theorems and relational design, and while I was not disappointed, I was a bit overwhelmed by the third chapter. Having recently seen the latest incarnation of Star Trek, I thought a science-fiction metaphor might be appropriate.

Reading this book made me realize that I’m an engineer; I am Scotty. I know how my particular environment works, I know when “I’m giving her all she’s got”, and I know how to tweak the system to keep it afloat when pressed with stressful situations. SQL Server is a wonderful platform, but like all database systems, it’s got its own peculiar quirks. Like all good engineers, you can learn to make the best of those quirks, but it doesn’t mean you understand how the engine is supposed to work.

Date is definitely Spock; beyond the logical comparisons (pardon the pun), Date’s focus is on the theory of how the relational model is supposed to work, not necessarily how SQL is implemented in the various database platforms. Very early on, he distinguishes between the relational model and SQL, and it became increasingly clear as I read this book that SQL <> relational model. Date tries his best to explain a very complex subject in a very thorough method, but I’m afraid that I’m not one of his best students.

The math involved in the relational model is very complex, and at times, I found myself frustrated with a passage because I knew how to do something in SQL, but had no clue as to what Date was saying in a relational language. That’s both sad and liberating in a way; I walked away from this book knowing first that I’m comfortable being an engineer, and second that I need to re-read this material occasionally. Date is like broccoli; he’ll never be my favorite author, but I know that what he’s writing is good for me.

4 out of 5 stars.

Joe Celko's SQL Programming Style

Just finished this book and it's really worth the read. You sometimes know that you are supposed to do things a certain way or avoid other ways. This gives you insight into why and brings up things you didn't think of. Even if you don't agree with everything, it's still worthwhile to get a different perspective.