You're being asked to do more. You're being asked to do it with less. Microsoft (R) Windows (R) Server 2003 is designed to help you manage those opposing forces and deliver powerful software solutions with less time, money, and hassle. For more information and to get a free Evaluation Kit, go to:http://lists.sqlmag.com/cgi-bin3/DM/y/eReu0FgQMn0BRZ0BBFd0Am

July 3, 2003—In this issue:

1. COMMENTARY

Free E-Books Can Help You Deliver Better BI Solutions

2. SQL SERVER NEWS AND VIEWS

Microsoft Plans to End the Patch-Management Nightmare

Results of Previous Instant Poll: Slammed Again?

New Instant Poll: Web Seminar Training

3. ANNOUNCEMENTS

Check Out the Database Performance Portal Today!

Attention Visitors to http://www.sqlmag.com

4. RESOURCES

What's New in SQL Server Magazine: Marking Milestones

Hot Thread: Tracking Logins by Database

Tip: Calling Visual Basic .NET from a Trigger

5. HOT RELEASE (ADVERTISEMENT)

Online Registration Deadline for MOC 2073: July 7!

6. NEW AND IMPROVED

Manage All Aspects of Your Server and Database

Recover Corrupt Database Files

7. CONTACT US

See this section for a list of ways to contact us.

1. COMMENTARY

FREE E-BOOKS CAN HELP YOU DELIVER BETTER BI SOLUTIONS

(contributed by Brian Moran, news editor, brianm@sqlmag.com)

I love free stuff! I recently stumbled across two free Microsoft e-books that I think many SQL Server professionals will find valuable. Both books cover information that can help you use Microsoft technologies to deliver more effective business intelligence (BI) and data-warehousing solutions. And each e-book Web site provides helpful, downloadable code samples. If you're interested in exploring how to use BI and advanced data-analysis techniques in your business but don't know where to start, these e-books provide a great introduction as well as tips for more advanced users.

The first book, "Using Microsoft Office XP as a BI Client," is packed with practical information, including setup details, how to use PivotTable and PivotChart Reports and Data Analyzer to perform analysis, and how to use Office Web components to create Web applications. The book also shows you how to use MapPoint for geospatial analysis and how to use Smart Tag Solutions to integrate applications.

I'm particularly intrigued by this book because it focuses on using Office XP as the OLAP client. Of course, Microsoft has a vested interest in having you adopt Office, but I've always believed that OLAP will never become ubiquitous in corporate America until business analysts can access OLAP data through the spreadsheet and word-processing technologies they're familiar with. You can find powerful third-party front-end BI tools, but they're not cheap. Although Office isn't technically free, if your company has already paid for it, it's as good as free. Books such as this can help you put together a compelling proof of concept around BI that can ignite a fire under the powers that be and encourage further investment in more sophisticated BI and OLAP tools and projects. You can download this e-book.

I also recommend "Preparing and Mining Data with Microsoft SQL Server 2000 and Analysis Services," which takes you into the complex world of data mining using SQL Server 2000 Analysis Services and Microsoft Visual Basic 6.0. This e-book has information about everything from setup and fundamentals to transforming and splitting data.

Today, businesses use data mining even less frequently than traditional OLAP technologies. However, data mining will eventually become an integrated part of almost all decision-making processes. Why? OLAP helps you look through data to find interesting facts and answers. Data mining tells you what those interesting facts and trends are without you having to ask the question in the first place. Although Microsoft's data-mining tools aren't quite mature yet, the Yukon release of SQL Server promises to enhance the product's data-mining functionality. You can start preparing yourself today for tomorrow's hot technology by downloading the data-mining e-book.

SQL SERVER MAGAZINE CONNECTIONS: 4 FOR 1 OFFER

Secure your seat for SQL Server Magazine Connections, October 12-15 in Palm Springs, California. The conference will again run concurrently with Microsoft ASP.NET Connections and Visual Studio Connections. Additionally, we've added the brand-new Microsoft Office System Conference. Together, these conferences will give you more than 170 sessions to choose from. Learn from the Microsoft architects who built these technologies plus world-renowned third-party gurus who will share real-world tips and techniques learned from the field. Register now to receive the lowest possible registration fee plus access to all four conferences for one price. Call 800-899-5325 or 203-268-3204 for more information.http://lists.sqlmag.com/cgi-bin3/DM/y/eReu0FgQMn0BRZ0ggP0AW

2. SQL SERVER NEWS AND VIEWS

MICROSOFT PLANS TO END THE PATCH-MANAGEMENT NIGHTMARE

(contributed by Paul Thurrott, thurrott@winnetmag.com)

Patch management is one of the biggest problems facing Microsoft's enterprise customers today, primarily because the company's many products all have their own tools and methods for providing software updates. The bewildering number of product revisions, language versions, and other product differentiators—many of which require the company to issue multiple patches for the same vulnerabilities—exacerbate the problem. And because many of Microsoft's tools use different patch infrastructures, customers often see different results when they use products such as Microsoft Windows Update, Microsoft Baseline Security Analyzer (MBSA), Microsoft Software Update Services (SUS), and Microsoft Systems Management Server (SMS) with the SMS SUS Feature Pack. But as I reported in WinInfo Daily UPDATE earlier this week, to fix these problems, the company is overhauling its patch-management infrastructure and will unleash a new generation of patch-management tools as soon as early 2004.

Today, Microsoft's patch-management solutions are, well, patchy at best. The company admits that it often provides incomplete and inaccurate patch information; uses inadequate assessment and deployment tools, which produce an inconsistent patch experience because of the range of installer types the company uses; and produces poor-quality patches. The latter point is a tough spot for Microsoft, which must walk the line between delivering high-quality patches and delivering patches quickly. Changes to the underlying infrastructure should help the company do both.

To address these patch-management problems, Microsoft is creating a new, centralized patch-management architecture that it will use for all its products. Then, the company will build new versions of Windows Update, MBSA, SUS, and the SMS SUS Feature Pack around this infrastructure. Microsoft has been discussing centralized patch management for years, but it always seemed like pie-in-the-sky functionality. Now, though, the company plans to provide these tools to customers in early 2004—much earlier than I had previously expected. (At an early June reviewer's workshop for Microsoft Exchange Server 2003, the company noted that Kodiak, the next major Exchange version, will integrate with Windows Update, which led me to believe that the centralized architecture was still some years away because Kodiak isn't due until 2006 to 2007.)

From a scheduling standpoint, Microsoft has many patch-management milestones in the months ahead. Later this month, the company will standardize its Knowledge Base articles, making them easier to read, and will release a new version of the Microsoft.com Search tool that will be geared toward searching for security patches, which the company says is the number-one reason customers visit the site. Also in July, Microsoft will release updated best-practices guides for patch management. In first quarter 2004, Microsoft will deliver its common-patch architecture, update its patch installers, and release a new version of Windows Update that's geared toward all Microsoft products. In second quarter 2004, Microsoft will upgrade MBSA, SUS, and SMS 2003 to work with this new architecture. In late 2004, Microsoft will convert from eight patch-installer types to just two (Windows Installer—MSI—3.0 and Update.exe), and in early 2005 the company will move to a common-patch distribution infrastructure with the release of SMS 2005, Microsoft System Center, and a new SUS version.

RESULTS OF PREVIOUS INSTANT POLL: SLAMMED AGAIN?

The voting has closed in SQL Server Magazine's Instant Poll for the question, "If attackers release a new Slammer-like worm, are your SQL Server and MSDE instances safe?" Here are the results (+/- 1 percent) from the 194 votes:

59% Yes

14% No

27% I'm not sure

NEW INSTANT POLL: WEB SEMINAR TRAINING

The next Instant Poll question is "Have you ever attended a Web seminar for SQL Server training?" Go to the SQL Server Magazine Web site and vote for 1) Yes, and I found it valuable, 2) Yes, but I didn't find it valuable, 3) No, but I'd like to, or 4) No, and I don't plan to. http://www.sqlmag.com

SPONSOR: KIMBALL & DEVLIN AT TDWI BOSTON CONFERENCE

Join keynote speakers Ralph Kimball and Barry Devlin and other industry experts at the premier event for BI and DW education—-TDWI World Conference, August 17-22. Take advantage of over 50 full-day, half-day, and evening classes. Hot topics include: Data Warehousing Architectures, Real-Time Data Warehousing, Meta Data Management, Advanced Data Modeling Techniques, and more! Other features include a one-day Business Intelligence Strategies program for executives, peer networking, one-on-one consulting, and a hassle-free exhibit hall!http://lists.sqlmag.com/cgi-bin3/DM/y/eReu0FgQMn0BRZ0BBFe0An

If you've been putting off subscribing to SQL Server Magazine, now's the time to act. The past 24 issues of SQL Server Magazine online are now locked down and available only to subscribers. For a limited time, subscribe at the best rates ever offered online! http://lists.sqlmag.com/cgi-bin3/DM/y/eReu0FgQMn0BRZ0BA1w0Aj

4. RESOURCES

WHAT'S NEW IN SQL SERVER MAGAZINE: MARKING MILESTONES

SQL Server reached significant milestones in performance and overall market share this past year, posting record-breaking TPC-C scores for nonclustered systems and the fastest revenue growth rate in the database market. But Microsoft still faces the challenge of keeping SQL Server affordable and easy to use while adding advanced features aimed at the enterprise. In "Marking Milestones," Michael Otey analyzes these accomplishments and looks to the future for SQL Server. Read this July SQL Server Magazine article at http://www.sqlmag.com/articles/index.cfm?articleid=39146

Q. I need to call a Visual Basic .NET object when a specific event happens in SQL Server. I can capture this event through an INSERT trigger on a table, but how do I call the Visual Basic .NET object from the trigger?

A. You can solve your problem in several ways. Let's walk through the pros and cons of some different approaches.

Option 1. Use xp_cmdshell to call out to the OS and run almost anything.

For example, you could call an executable program that then calls whatever .NET program you need. However, you can't use this method to run a program that has a UI because a UI would try to write to the display on the SQL Server and wouldn't be able to, thus causing SQL Server to hang. Keep in mind that xp_cmdshell operates in a synchronous manner and doesn't return control to the SQL Server connection that called xp_cmdshell until the program that xp_cmdshell called completes. This could create problems within the trigger in several different ways. Most importantly, the synchronous nature of xp_cmdshell means the trigger might take much longer to finish, and the trigger will hold locks while it's running. These locks could block other connections, leading to serious contention problems on the server.

Option 2. Use sp_OA* functions to directly instantiate and interact with a COM object.

Sp_OA* procedures have pretty high performance overhead, so I never like to use them for systems that handle heavy transaction loads. In fact, I tend to avoid them even on lightly loaded servers because transaction loads on servers often increase to volumes much greater than originally anticipated. Before going with this solution, research this topic further in SQL Server Books Online (BOL) and other resources to learn more about the possible performance effects.

Option 3. Use sp_start_job to start a SQL Server Agent job directly from the trigger.

The SQL Server Agent job could include an ActiveX script task or a task that calls an OS-level command. The job could also call a Data Transformation Services (DTS) package, which in turn could manipulate your .NET program in any number of ways. Remember that SQL Server Agent jobs are single entry—only one person can run the job at a time. If an INSERT, UPDATE, or DELETE operation invokes a trigger to run a particular job while the job is already running, SQL Server generates an error message such as the following:

Server: Msg 22022, Level 16, State 1, Line 0

SQLServerAgent Error: Request to run job wait for me (from User Nittany\BMoran) refused because the job is already running from a request by User Nittany\BMoran.

So, starting a SQL Server Agent job directly from the trigger isn't a workable option in a multiuser environment.

You can use any of these techniques to call a .NET program (or any program without a UI) from a T-SQL trigger. However, I'm not fond of any of them because of the performance effects and limitations I've already mentioned.

Often when you need to call an external program, a few seconds' delay between when the triggering event happens and when the external program is called is OK. If that's the case, I recommend using a trigger that writes some status information to a table that an external polling program monitors. The polling program could query the status table every few seconds (with no noticeable performance impact) and could call the .NET program when it identifies an event of interest. This approach offers stability and better performance than the first three options.

6. NEW AND IMPROVED

(contributed by Carolyn Mader, products@sqlmag.com)

MANAGE ALL ASPECTS OF YOUR SERVER AND DATABASE

MyLittleTools.net announced myLittleAdmin, a Web-based SQL Server 2000 and 7.0 and MSDE database-administration tool. The new version features a skin interface, user levels, and multidatabase support for SQL script generation. You can use the tool to manage all aspects of your server and database including tables, indexes, foreign keys, triggers, constraints, views, stored procedures, users, roles, rules, defaults, and logins. Web-hosting companies can incorporate myLittleAdmin as part of their hosting product offering. You can buy a one-site, one-server license for myLittleAdmin for $490. An unlimited-use license costs $1999. A free "lite" version is available so that you can test the product in your own environment. Contact myLittleTools.net at webmaster@mylittletools.net. http://www.mylittletools.net

RECOVER CORRUPT DATABASE FILES

XOsoft announced Data Rewinder and WANsync 3.5, product suites that can recovery corrupted files and databases. The suites can undo corruption that application and database failures might cause and rewind data resources to the state they were in before corruption. You can use the suites to recover data on servers of any type, including SQL Server and Microsoft Exchange Server. You don't have to perform a system reboot or interrupt database operations to install the products. For pricing, contact XOsoft at 212-798-1490, 866-926-7962, or info@xosoft.com. http://www.xosoft.com

7. CONTACT US

Here's how to reach us with your comments and questions:

ABOUT THE COMMENTARY — brianm@sqlmag.com

ABOUT THE NEWSLETTER IN GENERAL — kathy@sqlmag.com(please mention the newsletter name in the subject line)

SQL Server Magazine UPDATE is brought to you by SQL Server Magazine, the only magazine completely devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.http://www.sqlmag.com/sub.cfm?code=ssei211x1y

Receive the latest information about the Windows and .NET topics of your choice. Subscribe to our other FREE email newsletters.http://www.winnetmag.net/email

From the Blogs

Don’t let bad data sneak up on you when and where you least expect it. Ferret out bad data with Melissa Data’s newest Profiling Component for SSIS. Learn how to take control of your data using knowledge-base-driven metadata. The truth shall set you free!...More

Now that we’ve outlined the process to let servers in a SQL Server AlwaysOn Availability Group "talk to each other" by means of setting up linked servers, it’s possible to set up some additional or improved checks on Availability Group Health....More

In my previous post, I provided a high-level outline of the core logic (and rationale behind that logic) that would be needed to set up regular synchronization checks on SQL Server Agent Jobs for servers where AlwaysOn Availability Groups have been deployed. In this post, I’ll walk through the steps--and the code--needed to setup those checks....More