Subscribe to SQL Server Magazine:
This Issue Sponsored By
This email newsletter comes to you free and is supported by the following advertisers, who offer products and services that might interest you. Please take a moment to visit these advertisers' Web sites and show your support for SQL Server Magazine UPDATE.
Free Disaster Recovery Toolkit for the SQL DBA
FREE SQL Server 2000 Training CD!
July 21, 2005
2. News and Views
3. Events and Resources
4. Featured White Paper
5. Peer to Peer
7. New and Improved
Sponsor: Free Disaster Recovery Toolkit for the SQL DBA
Written by SQL Server expert Brian Knight, this handy, "how-to" toolkit contains comprehensive first-hand advice and scripts for SQL Server DBAs that need to build and implement a successful disaster recovery plan. With his tips and quips, Brian walks the DBA through real-world scenarios using an easy, step-by-step approach. And as part of the download, you'll receive four scripts, which will greatly speed your recovery time! Download it today, compliments of Lumigent:
by Brian Moran, firstname.lastname@example.org
Mirror, mirror, on the wall, who's the fairest database of all? I don't know about fairest, but database mirroring, a new feature in SQL Server 2005, certainly makes SQL Server more attractive in terms of high availability and standby database needs. Database mirroring is transaction-log based and sends a continuous steam of log records from a primary server to a mirror server to provide high availability, fault tolerance, and standby solutions. Previous SQL Server Standard Edition releases lack valuable high-availability solutions. SQL Server 2005 Standard Edition includes basic database mirroring to provide additional data protection from certain types of system failures. However, Microsoft chose to save some more advanced database mirroring features for the Enterprise Edition to raise the value.
Database mirroring runs in one of three modes: High Availability, High Protection, or High Performance. The High Performance Mode works only when the Transaction Safety setting is off and is available only in SQL Server 2005's Enterprise and Developer Editions. It might sound counterintuitive to have a high-end feature—saved for the Enterprise Edition—associated with "unsafe" transactions. High Performance mode requires these "unsafe" transactions to ensure that mirroring won't adversely affect performance in high-volume environments, especially when the primary and mirror servers are separated over a WAN. A Full Safety setting requires that transactions are committed on both the primary and mirror servers before the primary server will consider the transaction fully committed. Think of this as synchronous communication. However, turning the High Performance Safety mode off lets the primary server consider a block of transactions as fully committed before receiving notification from the mirror that its transactions are also committed; think of this as asynchronous. Eliminating the need to receive positive confirmation from the mirror before committing a block of transactions removes a tremendous serialization bottleneck that might otherwise occur. Both the High Availability and High Protection modes require synchronous, or safe, transactions.
I've shared a few key database-mirroring concepts with you here, but I also want to encourage you to read Ron Talmage's Microsoft white paper "Database Mirroring in SQL Server 2005," which provides a wonderful introduction to this new feature. I think the most valuable parts of the 39-page white paper are the in-depth analyses of real-world implementation models that help you understand the pros and cons of using database mirroring in certain scenarios.
One of my favorite sections of the white paper compares database mirroring with other SQL Server high-availability and standy technologies. SQL Server 2005 supports four distinct high-availability options—database mirroring, clustering, log shipping, and transactional replication—that you can also use for standby purposes. Of course the white paper doesn't include the growing number of high-availability add-on solutions from third-party vendors. And Talmage doesn't discuss how you can use database mirroring with the other three high-availability technologies that come in the SQL Server box. Choices are many, which means you must be more diligent when picking the most appropriate combination of technologies to ensure you roll out the best solution for your needs. I hope you enjoy the white paper; I know you'll appreciate the extra flexibility that database mirroring provides.
FREE SQL SERVER 2000 TRAINING CD!
See why AppDev was voted THE BEST by readers of SQL Server Magazine...get a FREE SQL Server 2000 Training CD-ROM—a $95 value! Click the link below for your free SQL Server training CD.
2. News & Views
Bill Baker, Microsoft General Manager for SQL Server Business Intelligence, will kick off the second round of the Get Ready for SQL Server 2005 Roadshow series next Tuesday, July 26, in Portland at the Hilton Portland & Executive Tower. The Seattle event is scheduled for Thursday, July 28, at the Sheraton Seattle Hotel & Towers.
"We've been working hard on SQL Server 2005," said Baker, who will deliver keynote sessions about SQL Server 2005 innovations in Portland and Seattle. "We're looking forward to traveling the country to meet with our users, and showing them some of the amazing things they can do in their businesses with the technology they've been working on."
The second round of the roadshow, which will visit 15 cities between July 26 and September 22, follows a highly successful spring series in April and May that attracted an average of about 300 attendees per city. Attendees were drawn to the event because of its technical, independent content focused on business intelligence, administration, and development. The training companies contributing content for this series are Hitachi Consulting, Scability Experts, and Solid Quality Learning. Database administrators, developers, and business intelligence (BI) professionals can learn first-hand how to upgrade to SQL Server 2005 in this one-day intensive course, produced by SQL Server Magazine and the Professional Association for SQL Server (PASS) and sponsored by Microsoft. The fee for the event is $99 for pre-registration online and $150 for walk-in registration. The fee includes a full day of technical content, breakfast, lunch, a one-year subscription to SQL Server Magazine, a one-year membership to PASS, an attendee bag, roadshow T-shirt, and the opportunity to win prizes from various sponsors. Complete information and secure online registration is available at http://lists.sqlmag.com/t?ctl=F1CC:7B3DB .
The roadshow dates and cities are:
Portland, Oregon, July 26, Hilton Portland & Executive Tower
Seattle, July 28, Sheraton Seattle Hotel & Towers
Kansas City, August 9, Sheraton Overland Park Hotel (at the Convention Center)
St. Louis, August 11, Sheraton Westport Chalet Hotel St. Louis
Minneapolis, August 16, Hyatt Regency Minneapolis
Washington, DC, August 18, Wardman Park Marriott Hotel
Phoenix, August 23, Arizona Biltmore Resort & Spa
Denver, August 25, Denver Marriott Tech Center
Atlanta, August 30, Cobb Galleria
Tampa, September 1, Grand Hyatt Tampa Bay
Los Angeles, September 8, Los Angeles Marriott Downtown
Detroit, September 13, Hyatt Regency Dearborn
Philadelphia, September 15, Hilton Philadelphia City Center
Cincinnati, September 20, NKU/METS Center for Corporate Learning
Columbus, September 22, Greater Columbus Convention Center
For more information, including the complete agenda and registration information, visit the following URL:
If so, you qualify to enter this awards program. SQL Server Magazine is organizing its 4th annual Innovator Awards Program. An awards committee, designated by SQL Server Magazine, will review all entries and select one grand-prize winner, 3 runners up, and honorable mentions if appropriate. The grand-prize winner will win a free trip to the 2005 SQL Server Magazine Connections Conference in Las Vegas, Nevada, November 7-10, a special trophy, and a write-up in the January 2006 issue. Contest ends August 1, so fill out an entry form today to get the recognition you deserve for your innovative solution. Click here:
"Do you participate in SQL Server contests?" Here are the results from the 28 votes:
"What is your favorite part of the SQL Server Magazine Web site?" Go to the SQL Server Magazine home page and submit your vote for
3. Events and Resources
Find out all you need to know about 64-bit computing and why your next computer will be 64-bit! Join industry expert Mike Otey as he compares 32-bit and 64-bit technology and reveals the best platform for high performance. You'll also learn how to successfully migrate and manage the two. Register now to learn the most important factors and best uses of 64-bit technology.
Microsoft will give every attendee a copy of SQL Server 2005 and Visual Studio 2005. SQL Server Magazine Connections is co-located with Microsoft ASP.NET Connections and Visual Studio Connections.
In this free Web seminar, you'll get the tools you need to ensure your systems aren't going down. You'll discover the various categories of high-availability and disaster-recovery solutions available and the pros and cons of each. You'll learn what solutions help you take preemptive, corrective action without resorting to a full system failover, or in extreme cases, that perform a non-disruptive, automatic switchover to a secondary server.
In this free Web seminar, get the tips you've been looking for to save time and money in achieving IT security and regulatory compliance. Find out how you can simplify these manually intensive, compliance-related tasks that reduce IT efficiency. Turn these mandates into automated and cost-effective solutions. Register now!
See the complete Windows IT Pro Network guide to Web and live events.
4. Featured White Paper
Discover the various methods available for controlled Internet access and how to use them to increase security and decrease legal exposure. Download your free white paper now!
5. Peer to Peer
by Brian Moran, email@example.com
Recently, I worked with a customer who had complex views referencing tables that had more than 200 columns. Some of the most expensive queries were accessing only 10 to 15 columns even though the view definitions on the wide tables used SELECT *. Most people know to avoid SELECT * because it can chew up I/O, memory, and network bandwidth. Network traffic is affected when you're reading more columns than you need; I/O and memory pressure are also affected when you retrieve 100 columns and use only 10. Less obvious is that complex views, especially ones that include SELECT * references to a wide table, might create significant compilation-time problems for you. I was shocked when I measured the performance cost in a few real-world situations. In some cases, I reduced compile times for complex queries by 4 to 6 seconds simply by creating new views that referenced only the necessary columns.
Read the rest of this tip today at
High availability is probably a DBA's highest priority. Nothing gets a DBA involved faster than the database server going down. SQL Server provides several features that you can use to create highly available server environment. In his July SELECT TOP(X) column "High Availability Options," Michael Otey discusses SQL Server's high-availability options and shows you what types of failures each solution handles best. Read this article today and post your comments at
Sequences are Oracle special database objects designed to produce values in sequence, starting with an initial value and incrementing by a given value. The defaults are to initialize the sequence at 1 and increment by 1. Although SQL Server uses the Identity column to obtain a similar functionality, Oracle sequences have two main advantages over the Identity column. One, SQL Server's Identity column is linked to a specific table column, whereas an Oracle sequence is an independent entity. Two, with SQL Server, you can use only one Identity column per table. With Oracle sequence objects, you can use any sequence to insert any value into any appropriate column in your table. Thus, you can use as many sequence columns as you need in a table. In this month's Reader-to-Reader tip "Simulating Oracle Sequences in T-SQL Code," Eli Leiba offers T-SQL code snippets that simulate the behavior of specific Oracle functions. Read this article today at
* Share your SQL Server discoveries, comments, problems, solutions, and experiences with products and reach out to other SQL Server Magazine readers. Email your contributions (400 words or less) to firstname.lastname@example.org. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you'll get $50!
Have you ever tried to launch a SQL Server startup procedure only to find that it doesn't return a row when it should? Then, when you run the same startup procedure in Query Analyzer using the same connection credential, it returns the results as expected? You might be hitting a little known aspect of startup procedures that involves the number of connections they use. If you read about the CREATE PROCEDURE statement in SQL Server Books Online (BOL), you'll find: "There is no limit to the number of startup procedures you can have, but be aware that each consumes one connection while executing. If you must execute multiple procedures at startup but do not need to execute them in parallel, make one procedure the startup procedure and have that procedure call the other procedures. This uses only one connection." Check out the BOL entry and let Kevin know if you've ever encountered similar behavior today at
Hot Threads: Check out the following hot threads, and see other discussions in our 30 SQL Server forums.
Administration: General Network Error After SP4 Install
T-SQL: Update Trigger Ignores NULL Value Updates in Source Column
Performance: Will More CPUs and Memory Increase Performance?
Development: Passing Values to Stored Procedure Using ASP Parameters
Reporting Services: Need Configuration Help
Security: Using Windows Security to Remotely Access SQL Server
Reporting Services: Performance Concerns When Running Reporting Services on OLTP Databases
OLAP/Data Warehousing: Can't Connect to Analysis Manager
The 2005 PASS Community Summit, September 27-30 in Grapevine (Dallas), Texas, is your final chance to obtain in-depth information about Microsoft SQL Server 2005 prior to its upcoming official launch. Register by August 31 for just $1495 for the LARGEST user-driven SQL Server educational event! Click here:
The SQL Server Magazine Master CD goes a step further by offering portable access to the entire SQL Server Magazine article database—more than 2300 articles! The newest issue includes bonus SQL Server 2005 content, and if you sign up now, you'll save 25%. Offer ends 7/31/05, so subscribe now:
7. New & Improved
by Dawn Cyr, email@example.com
Have you used a product that saved you time or made your job easier? Tell us your product-success story, and if we print your submission in the Hands On department, we'll send you a SQL Server Magazine t-shirt. Send your product suggestion with information about how the product helped you to firstname.lastname@example.org.
Quest Software announced SQL Watch for SQL Server 2005, 2000, and 7.0, software that sends DBAs automatic email and SNMP alerts about changes to the structure of SQL Server databases. SQL Watch uses a consolidated view of the enterprise to compare previous views and new views of database objects. The software informs DBAs of structural changes to the database and includes object details such as the object's name, its original contents, any added objects, and objects' current contents. The software retains a change history so that DBAs can produce comparison reports for different time periods. And the product provides the ability to track metadata changes (e.g., changes to DTS packages, replication rules, and database restore activity) at the database and SQL Server instance levels. For pricing and other information, contact Quest Software at 949-754-8000.
Nob Hill Software announced the latest release of Diana, a database entity browser, finder, and information provider. The software lets developers and DBAs find specific entities, search and replace database code, and fix broken entities across multiple databases in multiple servers. Implemented as a Windows-based application, Diana lets you find entities by any attribute, including name, creation date, type, subtext, metadata properties, dependencies, and runtime-execution attributes. You can use the software to find entities that don't compile or won't run properly, find entities that would run faster with indexes, and find unused indexes that consume unnecessary disk space. Features include a powerful result-set grid that simplifies finding entities by resorting, filtering, grouping, and exporting them to an external file. The software supports all editions of SQL Server 2000 and 7.0. Pricing starts at 49 for a single license for the silver version and $89 for the gold version. A free full version is available for students. For more information, contact Nob Hill Software at 646-831-6344 or email@example.com.
LogiXML announced LGX Report Liberator Edition, a free, zero-footprint business intelligence (BI) reporting product that lets you deliver Web-browser-based reports. The software rests on fully managed .NET code that's easier to deploy, maintain, and integrate than client/server reporting products. LGX Report Liberator Edition is based on an open, portable XML standard that doesn't lock users into saving reports in proprietary binary files. The product is the base component of LogiXML's complete Business Intelligence Report Development Platform, which lets developers connect with Web services and all major databases, including SQL Server, Oracle, IBM DB2, Sybase, and MySQL. You can easily upgrade LGX Report Liberator Edition to the company's Enterprise Reporting, Dashboarding, and Interactive Data Analysis products. For more information, contact LogiXML at 888-564-4965, 703-748-0020, or firstname.lastname@example.org.
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.
Manage Your Account You are subscribed as #EmailAddr#. To unsubscribe from this email newsletter, click here /#Mailing:UnsubEmail
To make other changes to your email account such as changing your email address, updating your profile, and subscribing or unsubscribing to any of our email newsletters, simply log on to our Email Preference Center.
SQL Server Magazine is a division of Penton Media, Inc., 221 East 29th Street, Loveland, CO 80538, Attention: Customer Service Department
Copyright 2005, Penton Media