SQL Server Magazine UPDATE—brought to you by SQL Server Magazine and SQL Server Magazine Connections
THIS ISSUE SPONSORED BY
Free White Paper on Data Recovery Best Practices
Need Access to Helpful SQL Server Experts?
(Below COMMENTARY)
June 3, 2004—In this issue:
1. SQL Server Perspectives
|
2. News and Views
|
3. Announcements
|
4. Resources
|
5. Events Central
|
6. New and Improved
|
Sponsor: Microsoft Security E-Learning Clinic
Our free Microsoft(R) Security E-Learning Clinics follow the same content outline as our Security Webcasts, but deliver that information via a learner-centered format that offers unique user benefits. With an E-Learning Clinic, you can access the security topic you want, when you want it, and learn at your own pace. Each lesson can be paused, and all security topics are indexed for fast and easy repeat use. So sign on today for an E-Learning Clinic, and get free information that can help you better protect your organization against security issues.
http://lists.sqlmag.com/cgi-bin3/DM/y/egCG0FgQMn0BRZ0BItA0A6
1. SQL Server Perspectives
Windows XP SP2 Defaults Break SQL Server Connections
by Brian Moran, [email protected]
If you're running Windows XP Service Pack 2 (SP2) and SQL Server, you've probably figured out that the XP SP2 default installation prevents most network access to SQL Server—including to Microsoft SQL Server Desktop Engine (MSDE). Fortunately, making SQL Server and XP SP2 coexist isn't difficult, and I'm here to provide resources that will make the task even easier for you.
The best place to start is with "FAQ: How Windows XP Service Pack 2 (SP2) Affects SQL Server and MSDE." This FAQ answers questions about how XP SP2 affects SQL Server and MSDE, how you can tell if your installations of SQL Server or MSDE are blocked, what to do if your application needs SQL Server to listen to the network, what you should do to update your SQL Server system, and where to find more technical information about getting XP SP2 to work with SQL Server and MSDE. You also need to review "How to enable SQL Server connectivity on Windows XP Service Pack 2," to learn how to set SQL Server as an exception in Windows Firewall. Finally, you should watch Richard Waymire's archived MSDN Webcast, "SQL Server and Windows XP SP2—Level 300." Bring your popcorn and settle in for 90 minutes. The Webcast presents a wealth of important SQL Server security information such as how to enable listening on the various network protocols and when you don't need to make any changes. This Webcast is a must-see for any SQL Server professional.
XP SP2 has many valuable new features, including a set of Microsoft-developed security technologies to help reduce the risk of malicious attacks against your computers. I'm particularly fond of Windows Firewall—an enhanced version of the component previously called Internet Connection Firewall. However, the Windows Firewall default installation is what blocks Internet access to SQL Server. Years of malicious worms and other Internet nasties have proven that our SQL Server boxes need to be locked by default. I don't have the space to describe the steps necessary to maximize the security capabilities of Windows Firewall while enabling users to access SQL Server across the Internet. The Microsoft article I mentioned earlier, "How to enable SQL Server connectivity on Windows XP Service Pack 2," gives all the details. But we all need to realize that the days of leaving Internet "front doors" open in case a neighbor stops by are over.
Microsoft SQL Server Reporting Services LoadFest
Don't miss the Microsoft(R) SQL Server(TM) 2000 Reporting Services LoadFest Event in your area. Join with your peers to load a 120-day evaluation copy of SQL Server Reporting Services on your own server. SQL Server specialists will provide you with instructor-led training on installing and configuring your server, plus hands-on training covering the product's main features. SQL Server 2000 Reporting Services enables organizations to transform valuable enterprise data into shared information for insightful, timely decisions at a lower total cost of ownership. This is an excellent opportunity to load and configure SQL Server Reporting Services code on your own server and start early evaluation. Space is limited, so register today for the limited-time, deeply discounted price of $50--a $400 value. Click here:
http://lists.sqlmag.com/cgi-bin3/DM/y/egCG0FgQMn0BRZ0BIkl0Af
2. News and Views
by Paul Thurrott, [email protected]
At TechEd 2004, Microsoft revealed that SQL Server 2005 will include native support for encryption, decryption, and key management. The idea is to provide a strong level of security directly in the product so that customers won't have to turn to third parties or rival database vendors. With this feature, even someone who successfully hacks into a SQL Server 2005 database won't be able to get data out of the database, Microsoft said. SQL Server 2005 will also undergo the National Information Assurance Partnership (NIAP) Common Criteria Evaluation and Validation Scheme (CCEVS) certification, which will ensure that the system is fit for use in the most secure government installations.
Yesterday, Microsoft announced the availability of two new Microsoft Office 2003 Business Intelligence (BI) Accelerators: the Microsoft Office Business Scorecards Accelerator and the Microsoft Office Excel Add-in for SQL Server Analysis Services. The Business Scorecards Accelerator is a Web-based application that enables a company to simplify the measurement and management of key performance metrics to more easily communicate, drive, and measure business strategy. The Excel Add-in for Analysis Services is a flexible reporting and analysis tool that lets you access and analyze data directly in Excel. Both accelerators are now available for download at no additional cost to users who have valid licenses for the underlying Microsoft products.
The Office Business Scorecards Accelerator is available at
http://www.microsoft.com/office/solutions/accelerators/scorecards/default.mspx
The Office Excel Add-in for SQL Server Analysis Services is available at
http://www.microsoft.com/office/solutions/accelerators/exceladdin/default.mspx
Microsoft has released a hotfix for a looping problem with Microsoft BizTalk Server 2004. If a message fails validation or routing in BizTalk Server 2004, the BizTalk Adapter for SQL Server rolls back the transaction and leaves the failed message in the source SQL database. The SQL adapter will pick it up again and try to process it, but when the processing fails again, the transaction rolls back again. This problem creates a continuous loop. To learn more about the problem and the supported hotfix, read the Microsoft article "FIX: The BizTalk Adapter for SQL Server may repeatedly try to process a bad message" at http://support.microsoft.com/?kbid=841618.
The voting has closed in SQL Server Magazine's Instant Poll for the question, "What is your primary application programming language?" Here are the results (+/- 1 percent) from the 335 votes:
- 50% Visual Basic/Visual Basic .NET
- 6% C++
- 23% C#
- 6% Java
- 15% Other
The next Instant Poll question is "What's the main type of SQL Server system you have deployed?" Go to the SQL Server Magazine Web site and vote for 1) 8-way, 2) 4-way, 3) dual-processor, or 4) single-processor.
http://www.sqlmag.com
3. Announcements
If you're a SQL Server developer or administrator, SQL Server Magazine is a must-read. Subscribe and gain access to a library of hot-topic discussions related to SQL Server 2005, Reporting Services, and much more. Learn from a treasury of articles, experts, savvy tips, and code listings that will give you the answers you're looking for. Order now:
http://lists.sqlmag.com/cgi-bin3/DM/y/egCG0FgQMn0BRZ0BItB0A7
Visit the SQL Server Magazine Web site and experience an easy way to find the SQL Server solutions, news, guidance, and how-to information you're looking for. Reference lists of active forums, hot-topic discussions, keyword searches, free Web seminars, FAQs, and much more. The site also features a Web-exclusive column by Itzik Ben-Gan. Click here:
http://lists.sqlmag.com/cgi-bin3/DM/y/egCG0FgQMn0BRZ0BIUg0AE
4. Resources
T-SQL programmers have wanted a way to calculate row numbers and other ranking values in SQL Server for a long time. SQL Server 2005's new analytical functions let you write short and efficient code, compared to SQL Server 2000 alternatives, which are long, complex, and slow. In his June T-SQL 2005 column, "Ranking Functions," Itzik Ben-Gan describes three new analytical ranking functions in SQL Server 2005--RANK(), DENSE_RANK(), and NTILE(). Read this article today at http://www.sqlmag.com/articles/index.cfm?articleid=42646
Bryanmurtha used xp_cmdshell to stop a Blaster worm attack. But now, SQL Server can't find xp_cmdshell when Bryanmurtha tries to define a new distributor for replication. Bryanmurtha wants to know if xp_cmdshell is safe. Offer your advice and see what other people have said on SQL Server Magazine's Security forum at http://www.winnetmag.com/sqlserver/forums/messageview.cfm?catid=1667&threadid=121804
by Brian Moran, [email protected]
Q. My company wants to charge application owners usage fees based on consumption of database resources, such as memory, CPU, I/O, the number of queries executed, and so on. Several third-party tools have this capability, but their data is based on sampling. We have many applications that run in less than 100ms, and a sampling approach misses much of this activity. Is there a comprehensive usage-based costing tool for SQL Server?
A. Questions about usage-based costing are becoming more frequent as SQL Server penetrates deeper into the enterprise environment. Robust usage-based costing tools are common in the mainframe world, but SQL Server 2000 doesn't have a comparative feature. Linchi Shea, a SQL Server MVP who has real-world experience dealing with usage-based costing in a high-end SQL Server environment, has some valuable insights I'd like to share with you.
SQL Server doesn't have a native accounting system that can provide basic, accurate, and persistent information you can use to calculate the charge-back amount, but there are many workarounds. Some techniques are cruder than others, but they all fall into one of two categories: those based on SQL Server Profiler and those based on sampling of master..sysprocesses. The trade-off is between using Profiler to get accurate, granular information but adversely affecting performance or getting less accurate data from sampling master..sysprocesses with minimal performance effects.
For implementing an internal charge-back system, sampling master..sysprocesses is a better solution than using Profiler, especially if you're more concerned with charging proportionally for the SQL Server resources an application uses rather than charging for the application's absolute usage of SQL Server's resources. When building a charge-back system, you need to address many details-some business oriented and some technology focused. For instance, you need to answer the following questions:
- What usage data do you use? Do you charge only for CPU utilization or a combination of CPU, memory, and I/O?
- How do you collect and aggregate the usage data (e.g., by login, by database)?
- How do you charge back usage to application owners--what is the formula you use to translate the raw usage data into the dollar amount you charge?
- How do you ensure your charge-back infrastructure is reliable? If the usage-data collection component fails for an extended period of time, how do you deal with the gaps in your usage data?
- Can your charge-back system provide an adequate answer if a customer questions a charge-back dollar amount? In other words, what kind of audit trail can you provide? Customers demand cost transparency.
A good charge-back system is also a valuable performance-tuning tool. In distributed computing, developers commonly do whatever it takes to meet their release deadlines. Developers don't often worry about inefficient queries as long as the application works and no one complains too much about performance--even if they know that they could take steps to improve query performance. However, developers often improve their system's performance if they receive a huge monthly bill for resource usage, so a charge-back system can provide useful performance feedback into the development team.
Send your technical questions to Brian Moran at [email protected].
5. Events Central
For a complete guide to Web and live events, see http://www.winnetmag.com/events
Discover the endless benefits of performance management and business intelligence within your organization. Learn how to access valuable information to understand business performance, leverage existing infrastructures to lower total costs of ownership, gain visibility in results, and much more. Sign up today for this free, interactive Web seminar sponsored by Panorama. Click here: http://lists.sqlmag.com/cgi-bin3/DM/y/egCG0FgQMn0BRZ0BItC0A8
Join the Professional Association for SQL Server ( http://www.sqlpass.org ) for a Webcast on the new .NET Common Language Runtime (CLR) integration with SQL Server 2005 presented by Michael Otey. In addition, we'll also explore how to use Visual Studio to create stored procedures and triggers. Registration is required for this Webcast, so sign up today: http://lists.sqlmag.com/cgi-bin3/DM/y/egCG0FgQMn0BRZ0BItD0AA
6. New and Improved
(contributed by Dawn Cyr, [email protected])
Simplify ADO.NET Application Development Red Brook Software announced WiredNav 2.0, a self-wiring .NET control for filling, navigating, searching, updating, and reporting from ADO.NET data tables. Developers can use the control in database applications without making initial property settings or writing code. And the control requires no DataAdapter, DataSet, DataSource, or DataTable properties. At runtime, the navigator automatically wires itself to every data object on the Windows form you add it to. This feature means that you need only one navigator per form instead of one per data table. WiredNav includes features that improve data binding, currency management, and disconnected data handling. You can use WiredNav to print and view reports you create in SQL Server 2000 Reporting Services or Crystal Reports for Visual Studio .NET. You can use the control with SQL, Oracle, OLE DB, and ODBC data adapters. WiredNav supports SQL Server 2000 and costs $199. Contact Red Brook Software at 518-248-3450 or [email protected].
http://lists.sqlmag.com/cgi-bin3/DM/y/egCG0FgQMn0BRZ0BItE0AB
Embarcadero Technologies announced the latest releases of three products--ER/Studio 6.5, ER/Studio Repository 3.0, and DT/Studio 2.2--to help you better manage, analyze, and integrate your data. ER/Studio 6.5 and ER/Studio Repository provide data architects, DBAs, and developers with a multilevel data-modeling application for logical enterprise data analysis and physical database design and construction. The latest releases of these products contain more than 50 new features, including comprehensive business intelligence (BI) and dimensional modeling support, simplified enterprise metadata analysis and management support, and model-driven database-tuning assistance. DT/Studio, a Java-based data-integration tool, lets you transform, migrate, and integrate large quantities of data from disparate sources. Enhancements to the latest release include J2EE Connector Architecture (JCA) support; impact analysis, which lets users track and manage data interdependencies; task-version management to facilitate multiuser development; and a new security model. The products support SQL Server 2000, 7.0, and 6.5. For pricing and other information, contact Embarcadero Technologies at 415-834-3131 extension 3 or [email protected].
http://lists.sqlmag.com/cgi-bin3/DM/y/egCG0FgQMn0BRZ0BItF0AC
JNetDirect announced JSQLMapper, a bidirectional mapping tool that eliminates the need to construct custom code to move relational data into XML documents. The product lets you quickly link business applications by creating relational data entries in a standards-based XML format and mapping those entries into a relational data source. In addition, the XML mappings perform create, read, update, and delete operations, so not only can the tool put relational data into XML documents, it can also use an XML document to populate a relational database. JSQLMapper is a Java product and is fully compliant with Sun's J2EE enterprise platform. JSQLMapper supports multiple database platforms including SQL Server, Oracle, and IBM DB2. Pricing starts at $225 for a single license, and a free trial download is available. For complete pricing and other information, contact JNetDirect at 800-995-8534 or [email protected].
http://lists.sqlmag.com/cgi-bin3/DM/y/egCG0FgQMn0BRZ0BF5M0AD
Application Security announced AppRadar for SQL Server, a database intrusion-protection system that lets large enterprises thwart database-level attacks and malicious behavior. The product is database-specific and host-based. AppRadar detects attacks and database misuse, detects and prevents complex attacks by valid users, and facilitates information security lockdown policies. AppRadar supports SQL Server 2000 and Microsoft SQL Server Desktop Engine (MSDE) 2000. For pricing and other information, contact Application Security at 866-927-7732 or [email protected].
http://lists.sqlmag.com/cgi-bin3/DM/y/egCG0FgQMn0BRZ0BGG20A4
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/sub.cfm?code=ssei211x1y
CONTACT US
Here's how to reach us with your comments and questions:
- About SQL Server Perspectives — [email protected]
- About the newsletter — [email protected]
(please mention the newsletter name in the subject line) - About technical Questions — http://www.sqlmag.com/forums
- About Product News — [email protected]
- About your subscription — [email protected]
- About sponsoring SQL SERVER MAGAZINE UPDATE? — Kate Silvertooth ([email protected]
Manage Your Account
You are subscribed as #EmailAddr#
To unsubscribe from this email newsletter, send an email message to mailto: #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://www.winnetmag.com/email
Copyright 2004, Penton Media, Inc.
