SQL Server Magazine UPDATE—brought to you by SQL Server Magazine and SQL Server Magazine Connections
THIS ISSUE SPONSORED BY
SQL Server Magazine Connections: 4 for 1 Offer
(below COMMENTARY)
Kimball & Devlin at TDWI Boston Conference
(below NEWS AND VIEWS)
SPONSOR: WINDOWS SERVER 2003
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
(contributed by Brian Moran, news editor, [email protected])
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
(contributed by Paul Thurrott, [email protected])
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.
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
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
3. ANNOUNCEMENTS
(brought to you by SQL Server Magazine and its partners)
SQL Server Magazine and CSA Research invite you to try out the Database Performance Portal. IT professionals can conduct scalability studies, perform ad hoc systems health analysis, identify infrastructure bottlenecks, conduct off-site diagnostics, and qualify new hardware purchases.
http://lists.sqlmag.com/cgi-bin3/DM/y/eReu0FgQMn0BRZ07hf0A6
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
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
Hung wants to know if he can find out who's logging in to a specific SQL Server 2000 database and when they're logging in. See what other DBAs have said, and offer your advice, on SQL Server Magazine's Administration forum at the following URL:
http://www.sqlmag.com/forums/messageview.cfm?catid=3&threadid=16877
(contributed by Brian Moran, [email protected])
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.
Send your technical questions to [email protected]
5. HOT RELEASE (ADVERTISEMENT)
You still have time! Register now for Microsoft Certified Training Course 2073: Programming a Microsoft SQL Server 2000 Database, which runs July 14-31. For the course outline and schedule or to register online, see:
http://lists.sqlmag.com/cgi-bin3/DM/y/eReu0FgQMn0BRZ0BBFf0Ao
6. NEW AND IMPROVED
(contributed by Carolyn Mader, [email protected])
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 [email protected].
http://www.mylittletools.net
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 [email protected].
http://www.xosoft.com
7. CONTACT US
Here's how to reach us with your comments and questions:
- ABOUT THE COMMENTARY — [email protected]
- ABOUT THE NEWSLETTER IN GENERAL — [email protected]
(please mention the newsletter name in the subject line)
- TECHNICAL QUESTIONS — http://www.sqlmag.com/forums
- PRODUCT NEWS — [email protected]
- QUESTIONS ABOUT YOUR SQL SERVER MAGAZINE UPDATE SUBSCRIPTION?
Customer Support — [email protected]
- WANT TO SPONSOR SQL SERVER MAGAZINE UPDATE?
More than 102,000 people read SQL Server Magazine UPDATE every week. Shouldn't they read your marketing message, too? To advertise in SQL Server Magazine UPDATE, contact Beatrice Stonebanks at [email protected] or 800-719-8718.
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
