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

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.

All High-Availability Solutions Are Not Created Equal--How Does Yours Measure Up?

Sort Through Sarbanes-Oxley, HIPAA, and More Legislation Quicker and Easier!

4. Featured White Paper

Do You Know If Your Network Is At Risk Of A Trojan Attack?

5. Peer to Peer

Hot Tip: Beware Compile-Time Degradation When You Use Wide Views

Hot Article: High Availability Options

Reader-to-Reader: Simulating Oracle Sequences in T-SQL Code

In a Nutshell: Startup Procedure Tip

Hot Threads

6. Announcements

Register Now For The 2005 PASS Community Summit

Get Access to Every SQL Server Magazine Article on CD

7. New and Improved

Alert DBAs to Database Changes

Find and Fix Database Entities

Deliver Web-Browser-Based Reports

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

Database Mirror, Database Mirror, On the Wall

by Brian Moran, brian@sqlmag.com

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.

2. News & Views

Bill Baker to Kick Off Second SQL Server 2005 Roadshow Series in Portland and Seattle

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 .

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

Results of Previous Instant Poll: SQL Server Contests

"Do you participate in SQL Server contests?" Here are the results from the 28 votes:

3. Events and Resources

Are You On The 64-bit Bandwagon Yet?

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

All High-Availability Solutions Are Not Created Equal—How Does Yours Measure Up?

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

Sort Through Sarbanes-Oxley, HIPAA, and More Legislation Quicker and Easier!

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

4. Featured White Paper

Do You Know If Your Network Is At Risk Of A Trojan Attack?

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

Hot Tip: Beware Compile-Time Degradation When You Use Wide Views

by Brian Moran, savvy@sqlmag.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.

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

Reader-to-Reader: Simulating Oracle Sequences in T-SQL Code

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

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

6. Announcements

Register Now For The 2005 PASS Community Summit

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

7. New & Improved

by Dawn Cyr, products@sqlmag.com

Share Your Product Success Story and Get a T-Shirt!

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 handson@sqlmag.com.

Alert DBAs to Database Changes

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

Find and Fix Database Entities

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

Deliver Web-Browser-Based Reports

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

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 Account You are subscribed as #EmailAddr#. To unsubscribe from this email newsletter, click here /#Mailing:UnsubEmail

From the Blogs

Many organizations today cannot use public cloud solutions because of security concerns, administrative challenges and functional limitations. However, they still need a centralized platform where end users can conduct self-service analytics in an IT-enabled environment....More

It is crucial to move away from data and analytics stored on individual desktop computers. Today’s solutions must promote holistic, collective intelligence. The strong, continued alliance between Microsoft and Pyramid Analytics helps make all this possible....More

To become a truly data-driven enterprise, many business leaders recognize that they must extend the capabilities of self-service business intelligence (BI) and analytics to more of their business users. Many BI tools tackle part of this need, but they don’t offer a complete enterprise solution....More