SQL Server Magazine UPDATE—brought to you by SQL Server Magazine and SQL Server Magazine Connections
THIS ISSUE SPONSORED BY
Experience the Benefits of Real Time Monitoring
Special Offer from SQL Server Magazine
(below COMMENTARY)
FREE White Paper on Continuous Data Auditing
(below NEWS AND VIEWS)
SPONSOR: EXPERIENCE THE BENEFITS OF REAL TIME MONITORING
Poring over event records after the fact? Undetected failed services causing havoc? Which system resource will be your next bottleneck? TNT Software's ELM Enterprise Manager is the affordable solution that monitors the health and status of your systems and alerts you by page, email, or instant message in time to take prompt corrective action. Download your FREE 30-day evaluation software of ELM Enterprise Manager 3.1 NOW and start experiencing the benefits of real time monitoring.
http://lists.sqlmag.com/cgi-bin3/DM/y/eA0DrXpe0A0BASG0AP
August 28, 2003—In this issue:
1. COMMENTARY
- Top Causes of SQL Server Downtime
2. SQL SERVER NEWS AND VIEWS
- Diagnostic Fix for Unreported I/O Problems
- Results of Previous Instant Poll: MCDBA Certification
- New Instant Poll: Access Migration
3. ANNOUNCEMENTS
- Register Now! Performance Tuning Mini-Series
- Check Out the Database Performance Portal Today
4. RESOURCES
- What's New in SQL Server Magazine: Disaster Prevention: Preparing for the Worst
- What's New at sqlmag.com: The Best Place for Bulk_Logged
- Hot Thread: Windows Authentication Through a VPN
- Tip: Using a Stored Procedure in a CASE Expression
5. HOT RELEASE (ADVERTISEMENT)
- $200 Early Bird Discount Expires Aug. 29
6. NEW AND IMPROVED
- Check for SQL Server Vulnerabilities
- Verify Your Database Code
- Store Mainframe Metadata in SQL Server
7. CONTACT US
See this section for a list of ways to contact us.
1. COMMENTARY
(contributed by Brian Moran, news editor, [email protected])
Several dozen readers responded to my call for feedback about the primary causes of planned and unplanned SQL Server outages in their environments. I tallied the results, and the following list presents the reasons in order of how many readers reported them as their primary cause of downtime—not in order of most serious problems. After all, your server is either available or it isn't. Users and employers don't care why they can't get to the database—they simply want the database up and running when they need it. Here's what's causing your SQL Server downtime:
1. Applying Service Packs and Other Patches (28 votes)
Not surprisingly, applying service packs and other patches was the leading cause of downtime (I include OS- and database-level patches and service packs in this group). In light of the many security-related critical updates that Microsoft has recently released, the company needs to improve patch-management functionality so that customers can apply service packs and patches without having to reboot their servers.
2. Problems with SQL Mail (11 votes)
I was surprised that problems associated with SQL Mail were so prominent. Technically, most problems with SQL Mail are related to Messaging API (MAPI)-level issues rather than with SQL Mail code, but SQL Mail remains responsible for many downtime incidents among readers. Again, this problem falls squarely in Microsoft's lap.
3. Random Bugs and Unknown Problems (8 votes)
What are random bugs? Most readers described them as memory-leak problems. Everyone wants to blame Microsoft for these kinds of problems, but I've seen many cases where client-developed code or third-party software was responsible for the outages.
4. Errors in Administration and Maintenance Procedures (5 votes)
Few readers said, "It was my fault." But one reader summed up this common IT problem by saying, "The reasons we experience downtime are usually caused by human error or network problems. Our main problems come from technicians who either reconfigure something without notifying anyone or test something without notification or from equipment that breaks down. My main frustration comes from technicians who forget to communicate." This reason falls into the "oops, I forgot" class of problems and is a reminder that high availability is equal parts technology and human policies and procedures.
5. Lack of Knowledge and Training (3 votes)
This reason is directly related to the errors-in-administration problem. The difference is that, in the previous problem, DBAs know what to do and simply either don't do it or do it incorrectly. However, sometimes DBAs make mistakes because they don't know better. You'll never have a highly available database environment without investing in the human component of high availability through policies, procedures, and training.
The following responses garnered one vote each:
- Adding indexes to very large tables, which causes blocking
- Virus attacks
- Complex environment interdependencies
This reader comment describes how complex environment interdependencies can lead to downtime: "What really makes my SQL Servers reboot too often is their interdependency with the heterogeneous and complex environment they live in. There are bunches of DNS servers, domain controllers, backup servers, firewalls, proxies, routers, switches, repeaters, thousands of network cables, power supply, redundant power supply, disks shared on a SAN, kilometers of fiber channel linking you to your backup site, and last but not least, other database servers. All these elements require maintenance, upgrades, reboots, and the like. The problem is that when one of these elements starts to malfunction, it may have consequences on other elements, and eventually, Windows or your SQL Server will hang and require a reboot."
Are systems getting so complex that one person, or even a group of people, can't possibly keep them running reliably? That's a depressing thought, but I suspect this phenomenon is at work more often than we realize. Microsoft has made great strides in helping customers deploy highly available SQL Servers over the past few years, but clearly both Microsoft and the SQL Server community still have work to do. I hope this commentary serves as a reminder that technology alone isn't enough to keep your systems running. Planning, careful adherence to well-thought-out procedures, and an investment in training your team are core components of high availability.
SPECIAL OFFER FROM SQL SERVER MAGAZINE
SQL Server Magazine presents the SQL Server Technical Education Package, including a 1-year print subscription to SQL Server Magazine, full SQL Server Magazine Web site access, and a 1-year subscription to the SQL Server Magazine Master CD (2 CDs), for only $39.95 (US)! Click here for this incredible limited-time offer!
http://lists.sqlmag.com/cgi-bin3/DM/y/eA0DrXpe0A0BBno0AX
2. SQL SERVER NEWS AND VIEWS
In a fix for all editions of SQL Server 2000 Service Pack 3 (SP3), Microsoft has added SQL Server diagnostics to detect some unreported I/0 problems. According to the Microsoft article "FIX: Additional SQL Server Diagnostics Added to Detect Unreported I/O Problems," if OS, driver, or hardware problems cause lost write conditions or stale read conditions, you might see data-integrity error messages such as Error 605, 823, or 3448. The supported fix is only for the problem described in this article.
http://support.microsoft.com/default.aspx?scid=kb;en-us;826433
The voting has closed in SQL Server Magazine's Instant Poll for the question, "Do you have your MCDBA certification?" Here are the results (+/- 1 percent) from the 681 votes (deviations from 100 percent are due to a rounding error):
- 16% Yes, and I find it valuable
- 8% Yes, and I haven't found it valuable
- 31% No, but I'm planning to get it
- 23% No, I don't have the time or money to pursue it
- 23% No, I don't see the value of it
The next Instant Poll question is "How many Access databases are you planning to migrate to SQL Server?" Go to the SQL Server Magazine Web site and vote for 1) Fewer than 10, 2) 10-25, 3) More than 25, 4) None, we're keeping all our Access databases, or 5) None, we don't have any Access databases.
http://www.sqlmag.com
SPONSOR: FREE WHITE PAPER ON CONTINUOUS DATA AUDITING
Proactively monitor and audit your data without the use of triggers and application modification with Lumigent(R) Entegra(TM)—the single most important, non-invasive solution for verifying, investigating, and reporting data activity. FREE white paper at:
http://lists.sqlmag.com/cgi-bin3/DM/y/eA0DrXpe0A0BBgz0Ab
3. ANNOUNCEMENT
(brought to you by SQL Server Magazine and its partners)
The Performance Tuning Mini-Series advanced-level online training course for SQL Server professionals will be presented September 3, 10, 17, and 26 from 1:00 to 2:00 p.m. Eastern time by Kimberly L. Tripp. Don't miss your chance to participate in this top-quality training!
http://lists.sqlmag.com/cgi-bin3/DM/y/eA0DrXpe0A0BBgy0Aa
SQL Server Magazine and CSA Research bring you the Database Performance Portal. IT professionals can use the portal to 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/eA0DrXpe0A07hf0Az
4. RESOURCES
Any event that causes data loss or system downtime can be a disaster for your company. But with careful planning, you can minimize a disaster's effects. In her article "Disaster Prevention: Preparing for the Worst," Kalen Delaney offers disaster-prevention best practices that will help your system survive. Read this September SQL Server Magazine article at
http://www.sqlmag.com/articles/index.cfm?articleid=39647
SQL Server 2000's Bulk_Logged database recovery model lets some operations occur quickly and with minimal logging, but it also limits your recovery options. In her Web-exclusive article "The Best Place for Bulk_Logged," Kimberly L. Tripp recommends that if your environment can't afford data loss, your safest bet is to use the Full recovery model as your primary recovery model and control changes through Bulk_Logged. Read this new Web-only article at
http://www.sqlmag.com/articles/index.cfm?articleid=39782
A customer wants to access DBADave's database server. The database server accounts require Windows authentication, but the customer's domain isn't and can't be trusted with DBADave's company's domain. The customer wants to communicate with DBADave's network through a VPN. But Windows authentication passes the customer's network credentials to SQL Server, and SQL Server denies access and provides a NULL connection-error message. Can DBADave create a network account for the customer in his domain and have the customer's VPN connection force a domain login into his network? DBADave is trying to avoid using SQL Server security if possible. See what other DBAs have said, and offer your opinion, on SQL Server Magazine's Security forum.
http://www.sqlmag.com/forums/messageview.cfm?catid=6&threadid=18572
(contributed by Microsoft's SQL Server Development Team)
Q. I'm trying to create a query that executes a stored procedure as one part of a CASE expression that has a mix of handlers (i.e., the CASE expression needs to handle multiple inputs). The stored procedure works on its own, but when I put it into the CASE expression, it fails. What's happening?
A. You can't execute a stored procedure from a CASE expression. A CASE expression evaluates a list of conditions and returns the result that meets the criteria. However, you can use a CASE expression to evaluate the handlers' values and return a character string that points to the stored procedure you want to execute, as the following code shows:
DECLARE @state char(2)
,@sp SYSNAME
-- Examine the value of @state and set @sp to the
-- appropriate stored procedure name.
SELECT @sp = CASE @state
WHEN 'WA' THEN 'first_sp'
WHEN 'KS' THEN 'second_sp'
WHEN 'TN' THEN 'third_sp'
END
EXECUTE(@sp) -- dynamic execution of the stored procedure
Then, you can pass the character string to an EXECUTE command that dynamically executes the stored procedure.
In the example code, the objective is to examine the value of @state at runtime. The code then executes the appropriate stored procedure, which is related to the particular state value.
5. HOT RELEASE (ADVERTISEMENT)
SQL Server Magazine Connections runs concurrently with Microsoft ASP.NET Connections, Visual Studio Connections, and Microsoft Office System Connections. Register before the discount expires and receive 4 conferences for the price of 1. Also, check out the Harley giveaway!
http://lists.sqlmag.com/cgi-bin3/DM/y/eA0DrXpe0A0ggP0AQ
6. NEW AND IMPROVED
(contributed by Carolyn Mader, [email protected])
Application Security announced AppDetective 3.1.8, vulnerability-assessment software that can check for three new SQL Server vulnerabilities: named pipe hijacking, named pipe Denial of Service (DoS), and buffer overflow in Local Procedure Calls (LPC). You can also check SQL Server to see whether database ownership chaining has been disabled. In addition, AppDetective can verify whether you've installed a patch that prevents revealing an account name that SQL Server runs under. For pricing, contact Application Security at 212-420-9270 or 866-927-7732.
http://www.appsecinc.com
Innovartis announced DB Ghost, software that can verify whether your database code complies with and doesn't break existing code. You can verify changes made to your database schema and static data. You can also compare two existing databases and make them match. DB Ghost lets you compare an existing database against a set of base schema creation scripts (e.g., for stored procedures, tables, views, indexes, and foreign keys). DB Ghost also can build new databases from scratch if you point the software to a set of object-creation scripts. You can version your database to roll back or roll forward changes. DB Ghost, which supports SQL Server, costs $300 for a single-user license, $730 for a 5-user license, $1160 for a 10-user license, and $3000 for a site license. Contact Innovartis at [email protected].
http://www.innovartis.co.uk
Xbridge Systems announced that Xbridge Host Data Connect now supports SQL Server and can store mainframe metadata in SQL Server. Host Data Connect enables Windows, Web, and mobile applications to securely access all OS/390 and z/OS mainframe data (i.e., Virtual Storage Access Method—VSAM, Queued Sequential Access Method—QSAM, DB2, and Information Management System/Database—IMS DB data). You can see the data in realtime in the format of the requesting application (e.g., Microsoft Excel). The Host Data Connect Results Cache add-on module lets you download mainframe data and determine the frequency and time of download. Host Data Connect starts at $20,000 for a single seat. Contact Xbridge Systems at [email protected].
http://www.xbridge.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?
Contact Richard Resnick at [email protected] or 800-949-4007.
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
