SQL Server Magazine UPDATE, September 14, 2006--Be a BI Expert--or Just Look Like One

In This Issue:
A recent IDC report hails the start of a new wave of BI expansion into everyday business. Reading the report will give you insight into the coming market changes--and what you’ll need to know as a database professional.

Also:
Send your solution to this week’s new T-SQL puzzle by Itzik Ben-Gan!

New Instant Poll: Avoiding the Upgrade
"What’s the biggest reason you’ve NOT yet upgraded to SQL Server 2005?" Go to the SQL Server Magazine home page and submit your vote for:

The cost of licensing SQL Server 2005
The complexity of licensing SQL Server 2005
The difficulty of migrating applications that are written for SQL Server 2000
The difficulty of moving current SQL Server 2000 components such as DTS packages and Analysis Services cubes
We don’t have a compelling business reason to upgrade
Other

To ensure that future email messages you receive from SQL Server Magazine UPDATE aren't mistakenly blocked by antispam software, be sure to add [email protected] to your list of allowed senders and contacts.

Our Sponsors, Who Help Support the Free Delivery Of This Newsletter:

September 14, 2006

1. Perspectives

Be a BI Expert—or Just Look Like One

2. SQL Server Watch

FIPS Compliance with SQL Server 2005
Product Watch: XMLA Consulting and dataReference

3. Hot Articles

Q&A: Changing SQL Query Behavior
SELECT TOP(X): SQL Server Migration Assistant
This Month’s Focus: Disaster Recovery—Database vs. Storage Replication
Puzzled by T-SQL: Grouping Consecutive Rows with a Common Element
In a Nutshell: Online Indexing in SQL Server 2005
Hot Threads: SQL Server 2005 CLR and Reporting Services

4. Events and Resources

Linux + Unix + Windows—TechX World
Secure Your Messaging Infrastructures
The Myths of Linux
5 Points for Choosing Anti-Spyware

5. Featured White Paper

Extend Microsoft Windows Rights Management Services

6. Reader Challenge

September Reader Challenge Solution: Fixing a Faulty Lookup Query
October Reader Challenge: Enforcing Uniqueness Without the Unique Constraint

7. Announcements

Special Invitation for VIP Access
Get the SQL Server 2005 System Table Map FREE

8. Web Community

http://www.sqlmag.com

Sponsor: ProClarity
Free SQL Server 2005 BI Resources—Visit Today!
Unlock the power of SQL Server 2005 by visiting the SQL Server 2005 Business Intelligence (BI) Resource Center, sponsored by ProClarity. It’s full of expert insights and best practices resources:

Comprehensive toolkits—Provide everything you need to know about SQL Server 2005 organized by topics, including migration, data visualization, the Unified Dimensional Model (UDM), reporting and analytics, and more.
Educational white papers—Complimentary best practices white papers help you successfully migrate, deploy and maximize your Microsoft BI investment.
Informative webcasts—Live and on-demand expert webcasts cover topics from cube building, deploying successful dashboards, and effectively communicating data.

Help your decision makers unlock the power of information to make better, faster decisions. Visit the BI Resource Center today!

1. Perspectives

Be a BI Expert—or Just Look Like One
by Brian Moran, [email protected]

This summer, leading IT research firm IDC released a report that evaluates the business intelligence (BI) tools and vendor market for the period 2003 through 2005. The study has been on the Microsoft site for a while and if you are a BI person, you might have already read it. But if you don’t think of yourself in those terms, chances are you haven’t read the full report or even scanned the executive summary.

For many of you, devouring 20 pages of BI market analysis might not seem quite as exciting as reading the sports page or the latest best-selling novel. For some, such a task might be on par with scanning the nutritional contents on the back of your morning cereal box. But trust me, reading the report is worth an investment of time if you’re a database professional, even if you’re not a “BI person.” More and more, BI and related technologies are starting to seep into everyday usage within database environments. IDC suggests that trends in the BI market happen in 15-year cycles. IDC also asserts that “the next wave in BI has now begun;” in a few years, we’ll look back to see that 2005 was the beginning of a 15-year cycle that focused on “expanding the reach of BI to more users both inside and outside the organization and a move to automate more decision processes by combining QRA (query reporting and analysis) and advanced analytics functionality.”

Probably not tomorrow or next week, but sometime sooner than you realize, you’ll find it hard to be a senior database professional without having at least a modest grasp of BI technologies. The IDC report is actually a pretty easy and interesting read, and it doesn’t presume much, if any, previous knowledge of the BI market. The market summary does a nice job of categorizing the various types of tools and market segments that exist today. A synopsis of the 20 largest vendors in the BI marker space provides market share and revenue share analysis and will help you understand how each vendor’s tools fit into the BI community at large. Also, although the report is 20 full pages, most of the core report is just 10 pages, so it won’t take as much time to digest as you might think.

The report provides some interesting market-share data that will interest BI veterans and provides a nice introduction to BI terms and jargon that will help newbies better understand the competitive landscape. Reading IDC’s report won’t make you a BI expert, but doing so might let you pretend to be one for at least a few minutes at your next dinner party. You can download the full report from http://www.microsoft.com/bi/IDCvendorshare2005.mspx.

Sponsor: Double-Take Software
Enhance SQL Reporting and Increase Server Performance
Increase your SQL production server’s performance by offloading Reporting Services to a secondary server. Gain additional insight into your important business data quickly and effectively. Download the whitepaper today!

2. SQL Server Watch

FIPS Compliance with SQL Server 2005
A recent Microsoft document can help you increase your level of security compliance. The article “Instructions on using SQL Server 2005 SP1 in the FIPS 140-2 compliant mode” gives a solid overview of how to run your SQL Server 2005 environment in compliance with the Federal Information Processing Standard (FIPS). FIPS, which was developed by the governments of Canada and the United States, is either recommended or mandated for use in federal government-operated IT systems. The FIPS 140-2 statement, which explains the "Security Requirements for Cryptographic Modules," specifies which encryption algorithms and hashing algorithms can be used and how encryption keys are to be generated and managed. The Microsoft article explains the difference between being FIPS compliant and FIPS certified, how to configure SQL Server 2005 for FIPS compliance, how to operate SQL Server 2005 in FIPS-compliant mode, and the effects of running SQL Server 2005 in compliance with FIPS. To read the article and link to more information about FIPS, including how to download the standard, visit the article’s Web page.

Editor's Note:
Regional Events Cover Four Key Interoperability Topics
Are you a Windows fan, a UNIX diehard or a Linux lover? Brought to you by people who understand the world you live in never fits the textbook IT infrastructure, TechX World is an OS-agnostic event that will give you insider tips for coping with your “Windows Plus” world. TechX World is a four-track, one-day event featuring information about OS interoperability, data interoperability, directory and security integration, and virtualization. Technical experts Michael Otey, Gil Kirkpatrick, Dustin Puryear and Randy Dyess will present interoperability tips to help make disparate systems work well together.

Between October 24 and November 2, the regional event series will visit four cities: Washington, DC; Chicago; Dallas, Texas; and San Francisco. At $129 per person for four tracks and a full day of learning, it’s worth sending the entire team to make sure you cover all the sessions. For complete agenda and speaker details, go to http://www.techxworld.com.

Product Watch
by Blake Eno, [email protected]

Integrated BI Solution for SQL Server 2005 and 2000
XMLA Consulting announced ReportPortal 2.1, a business intelligence (BI) solution that includes analysis, reporting, dashboards, scorecards, and advanced data visualization features for SQL Server 2005 and 2000. ReportPortal lets you build, publish, and view a variety of reports including data mining, Crystal Reports, and SQL Server 2005 Reporting Services reports. New reports in the latest release include tree map, pie chart tree, bar chart tree, geographical map chart, and dashboard reports. For more information, contact XMLA Consulting at 813-866-3483 or visit http://www.reportportal.com.

Migrate Crystal Reports to Reporting Services
dataReference announced that Report Migrator works in conjunction with SQL Server 2005 so that you can migrate single or multiple Crystal Reports into Report Definition Language (RDL) and run them by using SQL Server 2005 Reporting Services. Report Migrator lets you continue to use Cyrstal Reports Designer to develop reports but distribute them through Reporting Services. This functionality lets you maintain your investment in report design and distribute the report across your organization without retraining your development staff. For more information, contact dataReference at 214-239-0820 or 866-489-4512, or visit http://www.datareference.com.

3. Hot Articles

Q&A: Changing SQL Query Behavior
by Brian Moran, [email protected]

Q: I know that SQL Server 2005 lets me choose forced parameterization and use plan guides to change individual query behavior. How do I decide when to use these features?

Read the answer to this question today

SELECT TOP(X): SQL Server Migration Assistant
Converting Oracle databases to SQL Server has always been a manual process fraught with pitfalls—but not anymore, thanks to SQL Server Migration Assistant. Mike Otey tells you about are four features of this tool that will pave the way to a successful conversion. Read this article today and post your comments.

This Month’s Focus: Disaster Recovery—Database vs Storage Replication
by Mel Shum

Are you unsure about whether to use database or storage replication to replicate your data? Here are three factors to consider before deciding which is best for you.

Puzzled By T-SQL: Grouping Consecutive Rows with a Common Element
In this week’s blog, Itzik Ben-Gen posts a puzzle that’s sure to cause a little head scratching. Your task is to write a set-based query (no cursors) that groups consecutive rows with the same attendance status for each student in an Attendance table that Itzik has provided.

Remember, this puzzle is live! Send your solutions to Itzik, and we will give away two prizes: One winner will be chosen randomly out of those who provided correct solutions; another winner will be chosen based on the fastest correct solution. See the full details of the puzzle and submit your solution today.

In a Nutshell: Online Indexing in SQL Server 2005
Does performing online indexing on a large table degrade SQL Server performance? In this week’s blog, Kevin Kline eavesdrops on a conversation between SQL Server MVP Hilary Cotter and Microsoft uber-genius Stefano Stefani. Read about their discussion of this hot topic, and send your comments to Kevin today.

Hot Threads:

4. Events and Resources

Linux + Unix + Windows—TechX World
Pure-play IT shops are a nice idea, but the reality today is that we’re all faced with interoperability issues. TechX World 2006 gives you access to leading experts in the field and will prepare you to master interoperability issues in your environment.

Tired of using separate products on your Microsoft Exchange server for antivirus, antispam, attachment filtering, disclaimers, content auditing/filtering? This webcast will address the latest threats to messaging security and spotlight Sunbelt’s Messaging Ninja that enables system administrators to easily secure their messaging infrastructures and stop threats at the Exchange Server.

Can you distinguish between the facts and fiction of Linux? Get the straight answers about Linux UNIX, and Windows—together with head-to-head comparisons. Read articles and download free resources today! You can also test your Linux skills and enter to win a $150 MSN Music gift card!

Randy Franklin Smith outlines five evaluation points to consider when choosing your antispyware solution in this free podcast. Download it today!

See the complete Windows IT Pro Network guide to Web and live events.
http://www.winnetmag.com/events

5. Featured White Paper

Extend Microsoft Windows Rights Management Services (RMS) to support enterprise requirements for information protection, including proprietary business data. Download the free whitepaper today!

6. Reader Challenge

September Reader Challenge Solution: Fixing a Faulty Lookup Query
by Umachandar Jayachandran, [email protected]

Congratulations to Steve Kass and Lay Chew. Steve won first prize of $100 for the best solution to the September Reader Challenge, "Fixing a Faulty Lookup Query." Lay won second prize of $50. You can read a recap of the problem and the solution to the September Reader Challenge at http://www.sqlmag.com/Article/ArticleID/93486/sql_server_93486.html.

October Reader Challenge:
Now, test your SQL Server savvy in the October Reader Challenge, "Enforcing Uniqueness Without the Unique Constraint" (below). Submit your solution in an email message to [email protected] by September 21. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We'll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Here's the challenge:
Paul is a database architect in a company that provides Web-based message services. He must design a schema to store and retrieve forum messages from the database, and he plans to use SQL Server 2000 as the database server. Among the tables that contain the messages is a table that has a unique per-message identifier column. The table's schema is shown in the following code:

CREATE TABLE messages (
msg_id INT NOT NULL PRIMARY KEY,
msg_hdr VARCHAR(1024) NOT NULL
)

Paul wants to enforce uniqueness on the msg_hdr column and tries to define a unique constraint on the column by using the following script:

ALTER TABLE messages ADD CONSTRAINT uq_messages_id UNIQUE(msg_hdr)

The ALTER TABLE statement produces the following warning message:

Warning! The maximum key length is 900 bytes. The index 'uq_messages_id' has maximum length of 1024 bytes. For some combination of large values, the insert/update operation will fail.

Using generated sample data for the table, Paul performs tests that reveal that the msg_hdr value might exceed 900 bytes, so he can't use the unique constraint approach. (A unique index in SQL Server enforces a unique constraint, and, as the warning message says, index keys are restricted to a maximum of 900 bytes.)

Help Paul to efficiently enforce uniqueness on the msg_hdr column in the Messages table without the unique constraint.

Hot Spot: Neverfail
SQL Server Guide to Clustering Alternatives
Want the convenience of a server cluster without the expense? Learn about server cluster alternatives that provide high availability, preventative maintenance, and failover capabilities at pricing that fits your budget.

6. Announcements

Special Invitation for VIP Access
Become a VIP subscriber and get continuous, inside access to ALL of the content published in Windows IT Pro magazine, SQL Server Magazine, Exchange and Outlook Administrator newsletter, Windows Scripting Solutions newsletter, and Windows IT Security newsletter. Subscribe now and SAVE $100!

Get the SQL Server 2005 System Table Map FREE
SAVE up to $30 on SQL Server Magazine and get a SQL Server 2005 System Table Map poster FREE with your paid order! In addition, you’ll also get unlimited access to the entire online article archive, which houses more than 2300 helpful SQL Server articles. This is a limited-time offer, so order now!

7. Web Community

About the newsletter—[email protected]
About SQL Server Perspectives—[email protected]
About technical questions—http://www.sqlmag.com/forums
About product news—[email protected]
About your subscription—[email protected]
About sponsoring SQL Server Magazine UPDATE—Richard Resnick, [email protected]

SQL Server Magazine UPDATE is brought to you by SQL Server Magazine, the only magazine devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today!

SQL Server Magazine is a division of Penton Media, Inc.
221 East 29th Street, Loveland, CO 80538
Attention: Customer Service Department

Comments

Plain text