SQL Server Magazine UPDATE, July 21, 2005--Database Mirroring

Subscribe to SQL Server Magazine:
http://lists.sqlmag.com/t?ctl=F1D0:7B3DB

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
    http://lists.sqlmag.com/t?ctl=F1D5:7B3DB

FREE SQL Server 2000 Training CD!
    http://lists.sqlmag.com/t?ctl=F1D2:7B3DB

July 21, 2005

1. Perspectives

2. News and Views

3. Events and Resources

4. Featured White Paper

5. Peer to Peer

6. Announcements

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:
    http://lists.sqlmag.com/t?ctl=F1D5:7B3DB

1. Perspectives

    by Brian Moran, [email protected]

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.
    http://lists.sqlmag.com/t?ctl=F1D2:7B3DB

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:
http://lists.sqlmag.com/t?ctl=F1CB:7B3DB

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:
http://lists.sqlmag.com/t?ctl=F1C9:7B3DB

"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.
    http://lists.sqlmag.com/t?ctl=F1C5:7B3DB

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.
    http://lists.sqlmag.com/t?ctl=F1D6:7B3DB

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.
    http://lists.sqlmag.com/t?ctl=F1C1:7B3DB

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!
    http://lists.sqlmag.com/t?ctl=F1C0:7B3DB

See the complete Windows IT Pro Network guide to Web and live events.
    http://lists.sqlmag.com/t?ctl=F1D4:7B3DB

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!
http://lists.sqlmag.com/t?ctl=F1C6:7B3DB

5. Peer to Peer

by Brian Moran, [email protected]

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
    http://lists.sqlmag.com/t?ctl=F1C4:7B3DB

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
http://lists.sqlmag.com/t?ctl=F1C2:7B3DB

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
http://lists.sqlmag.com/t?ctl=F1C3:7B3DB

* 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 [email protected]. 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
http://lists.sqlmag.com/t?ctl=F1C7:7B3DB

Hot Threads: Check out the following hot threads, and see other discussions in our 30 SQL Server forums.
http://lists.sqlmag.com/t?ctl=F1CD:7B3DB

Administration: General Network Error After SP4 Install
http://lists.sqlmag.com/t?ctl=F1BF:7B3DB
T-SQL: Update Trigger Ignores NULL Value Updates in Source Column
http://lists.sqlmag.com/t?ctl=F1BE:7B3DB
Performance: Will More CPUs and Memory Increase Performance?
http://lists.sqlmag.com/t?ctl=F1BB:7B3DB
Development: Passing Values to Stored Procedure Using ASP Parameters
http://lists.sqlmag.com/t?ctl=4232:7B3DB
Reporting Services: Need Configuration Help
http://lists.sqlmag.com/t?ctl=422D:7B3DB
Security: Using Windows Security to Remotely Access SQL Server
http://lists.sqlmag.com/t?ctl=F1BA:7B3DB
Reporting Services: Performance Concerns When Running Reporting Services on OLTP Databases
http://lists.sqlmag.com/t?ctl=F1BD:7B3DB
OLAP/Data Warehousing: Can't Connect to Analysis Manager
http://lists.sqlmag.com/t?ctl=F1BC:7B3DB
>

6. Announcements

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:
    http://lists.sqlmag.com/t?ctl=F1D1:7B3DB

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:
     http://lists.sqlmag.com/t?ctl=F1CE:7B3DB

7. New & Improved

    by Dawn Cyr, [email protected]

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 [email protected].

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.
http://lists.sqlmag.com/t?ctl=F1DB:7B3DB

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 protected].
http://lists.sqlmag.com/t?ctl=F1D8:7B3DB

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 [email protected].
http://lists.sqlmag.com/t?ctl=F1D9:7B3DB

Contact Us

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.
http://www.sqlmag.com/rd.cfm?code=00ep214xeb

Manage Your AccountYou 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.
http://email.winnetmag.com/winnetmag/winnetmag_prefctr.asp

View the SQL Server Magazine Privacy Policy.
http://www.winnetmag.com/aboutus/index.cfm?action=privacy

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