SQL Server Magazine Web Exclusive
August 5, 2004
SQL Server Magazine UPDATE, August 5, 2004—SA Rights
InstantDoc ID 43524
by Various Authors
To ensure that future email messages you receive from SQL Server Magazine UPDATE aren't mistakenly blocked by antispam software, be sure to add [email protected] to your list of allowed senders and contacts.
THIS ISSUE SPONSORED BY
August 5 , 2004—In this issue:
1. SQL Server Perspectives
- Granting SA Rights to Developers
2. News and Views
- Learn How to Send and Receive Email with SQL Mail
- Results of Previous Instant Poll: T-SQL Debugger
- New Instant Poll: SA Rights
- Try a Sample Issue of Windows Scripting Solutions
- Get 5 Years of SQL Server Tools, Tips & Content
- What's New in SQL Server Magazine: Is VB Dead?
- Hot Thread: SQL Authentication
- Tip: Fighting OS-Level Fragmentation
5. Events Central
- Upcoming SQL Server Web Seminar: High Availability
- SQL Server Magazine Connections Conference
6. New and Improved
- Guard Database Security
- Administer Databases Across Platforms
Sponsor: Cluster-Class Availability for Your SQL Server
Has your business suffered a loss of business-critical data because your SQL Server was down? Neverfail for SQL Server proactively monitors the health of your entire SQL environment to provide true application availability. Neverfail for SQL Server is affordable and is easy to install and maintain. To learn how Neverfail's cluster-class high availability solution for SQL Server can help your business save IT dollars and resources, access a free white paper and/or datasheet:
1. SQL Server Perspectives
(contributed by Brian Moran, news editor, [email protected])
Last month, in "SQL Server Profiler: For Developers, Too" (http://www.winnetmag.com/article/articleid/43268/43268.html ), I encouraged you to integrate SQL Server Profiler into your unit-testing process. IT professionals typically think of Profiler as a DBA tool, but in my article, I outlined many benefits to training developers how to use Profiler effectively. I got a lot of positive feedback about that commentary, but I also received some messages such as the following:
"I enjoyed your article about granting developers access to run Profiler during development, but I'm a DBA who holds back sa authority, not only in the production environment, but also in development and quality assurance. I understand that sa permissions are required to run Profiler, but that requirement is what keeps Profiler out of our development process. Do you think Microsoft's SQL Server developers can, or will, do anything about requiring sa permissions for Profiler? Could they create another database or server role that runs only Profiler? I will pass your article along to my supervisor, but his view is, "If developers feel the need to run Profiler, then the DBAs should run it for them." I think you'll understand what the consequences would be if every developer suddenly had the need to trace something."
I agree that Microsoft made a mistake in requiring sa rights to run Profiler. Fortunately, the company will address the problem in SQL Server 2005 by making it possible for users who don't have sa rights to run the tool. However, Microsoft has no plans to address the problem for SQL Server 2000 users, which doesn't help us much today. SQL Server 2005 is still the better part of a year from release, and many of you will probably continue to run SQL Server 2000 for years after SQL Server 2005's release.
So, let's revisit the earlier reader comment. Development shops have many reasons to withhold sa access from everyone but a few, select DBAs. Assume that we all agree about the benefits of letting developers run Profiler, but you're unwilling to grant your developers sa access for the sole purpose of running Profiler. What are your options if you want developers to integrate Profiler into their unit testing and development? Grant developers sa permissions for SQL Server instances installed on their local machines. A lot of unit testing can easily be done on local instances of SQL Server. It's also reasonable to install "safe for developer" instances of SQL Server on development and testing machines. Applying backups (perhaps with log shipping) to the developer-safe machines wouldn't be difficult, and you could grant developers full sa rights to these boxes. These machines would never be the system of record for any live code, so there's minimal risk involved in granting developers sa rights on these instances.
This technique involves some administrative support, and the extra instances can cost additional license fees. But Profiler is too important to arbitrarily deny developers the rights to use it in a safe and reasonable manner. Our reader says that management requires developers to ask a DBA to run a trace for them, but realistically, that model doesn't scale. I firmly believe that the costs and risks involved in not using Profiler far outweigh the risks of granting developers sa rights in a controlled environment.
Free Trial of Lumigent Log Explorer
Lumigent(R) Log Explorer(R) is the leading transaction-analysis and online data-recovery solution for SQL Server. It provides access to the database transaction log, giving you the ability to understand and resolve elusive database problems. With Log Explorer, you can:
- Recover data while your database is online—no backups are needed
- Selectively recover modified, deleted, dropped, or truncated data
- Identify any data, schema, or permissions change
- Repair data at various levels of granularity
- Export transaction information for analysis and reporting
- Receive alerts when key database events occur
Download today: http://lists.sqlmag.com/cgi-bin3/DM/y/egyq0FgQMn0BRZ0BKMz0A2
2. News and Views
Microsoft has released an article that tells you how to send and receive email with SQL Server 2000's SQL Mail feature. You can configure SQL Mail 2000 to send mail to a Microsoft Exchange Server or through a Microsoft Outlook 2000 Service Pack 3 (SP3) client—Microsoft doesn't recommend configuring SQL Mail to send mail through a Post Office Protocol 3 (POP3) or Simple Mail Transfer Protocol (SMTP) account on a computer that has Outlook 2002 installed. To learn more, read the Microsoft article "How to send and receive e-mail with the SQL Mail feature in SQL Server 2000" at
The voting has closed in SQL Server Magazine's Instant Poll for the question, "Do you use SQL Server 2000's Query Analyzer T-SQL debugger?" Here are the results (+/- 1 percent) from the 215 votes:
- 28% Yes, regularly
- 15% Yes, sometimes
- 14% Yes, but infrequently
- 20% No, but I plan to
- 23% No, and I don't plan to
The next Instant Poll question is "Do you grant sa rights to your developers?" Go to the SQL Server Magazine Web site and vote for 1) yes, 2) yes, but only under controlled circumstances, 3) no, but I'll consider it, or 4) no, and I don't plan to.
Sponsor: Get Two Free Sample Issues of SQL Server Magazine!
If you're a SQL Server user, SQL Server Magazine is a must-read. Each issue is loaded with information covering Reporting Services, performance tuning, high availability, security, and much more. By subscribing today, you'll gain access to a treasury of online article archives, savvy tips, endless code listings, and expert advice that will give you the answers you're looking for. Try two, no-risk sample issues today. Order now:
Windows Scripting Solutions is the monthly newsletter from Windows & .NET Magazine that shows you how to automate time-consuming, administrative tasks by using our simple downloadable code and scripting techniques. Sign up for a sample issue right now, and find out how you can save both time and money. Click here!
Master the SQL Server universe with the SQL Server Magazine Master CD. This portable, high-speed resource will give you access to 5 years' worth of articles, code, tips, and expertise published in SQL Server Magazine and T-SQL Solutions. Let this helpful resource save you some time anywhere you are. Subscribe today and get 25% off!
The adoption of .NET technologies, released more than 2 years ago, continues to lag behind Microsoft's expectations. The set of .NET-based development tools that Microsoft launched in February 2002 included an IDE, the ASP.NET Web-development platform, and the new C# and Visual Basic .NET (VB.NET) languages. Many Web developers have embraced these tools, but the biggest base of Microsoft developers consists of 8 million Visual Basic (VB) 6.0 developers who haven't migrated to Visual Studio .NET in the numbers Microsoft hoped for. In his August editorial, "Is VB Dead?", Michael Otey discusses the advantages and disadvantages of VB 6.0 and VB.NET. Read this article today at:
Rparsons is running SQL Server 2000 Reporting Services on Windows XP. So far, his published reports look great, but no one else can view them. Anyone else who tries to generate a report receives the error message: "An error has occurred during report processing. (rsErrorOpeningAborted) Get Online Help. Cannot create a connection to data source 'ARSystem'. (rsErrorOpeningConnection) Get Online Help. Login failed for user 'NT AUTHORITY/ANONYMOUS LOGON'." Rparsons' SQL Server is on another machine, and the data set is using integrated security. Offer your advice and see what other people have said on SQL Server Magazine's Reporting Services forum at:
by Brian Moran, [email protected]
Q. I've read about how fragmentation at the SQL Server level can hurt my queries' performance. I understand how to look for fragmentation within my SQL Server tables by using the DBCC SHOWCONTIG command. However, should I also worry about fragmentation at the OS level?
A. Fragmentation exists at both the SQL Server level and the file level within the OS. It sounds like you already use DBCC SHOWCONTIG to combat SQL Server-level fragmentation (for a DBCC SHOWCONTIG primer, see SQL Server Books Online—BOL). So, let's look at how and when OS-level defragmentation can speed up your SQL Server.
Remember that SQL Server can report 0 percent fragmentation even when the on-disk files are horribly fragmented. SQL Server doesn't know or need to know how the OS physically lays out the bits on disk; it's the OS's job to manage physical bits on disk. However, because SQL Server doesn't know how the bits are laid out on disk, SQL Server has no direct way to report about file fragmentation. Imagine you're performing a table scan and SQL Server reports 0 percent fragmentation but the file that contains the table is scattered all over your disk. In this case, performing an OS-level defragmentation could help performance by making the files more contiguous on disk. However, defragmenting at the OS level doesn't always have the effect that you might expect. SQL Server preallocates space when you create a file. For example, if you create a 100MB file on a disk that's been defragmented recently, SQL Server creates the file in contiguous space. And SQL Server can read and write to that file forever, and the file won't fragment at an OS level (unless you stop SQL Server and perform a file-copy operation that puts the file into noncontiguous space). But if you create that 100MB file on a disk that's heavily fragmented, the file will also be fragmented.
What happens if SQL Server creates the 100MB file in contiguous space but auto-grow operations increase the size of the file to 200MB? In this case, the new space added to the SQL Server file might fragment as the file grows. An OS-level defragmentation will improve performance if the files become fragmented and you're performing table-scan operations within SQL Server that look at ranges of data. So, I suggest scheduling a regular OS-level defragmentation. The only downside is that you'll need to stop SQL Server during the defragmentation operation because defraggers can't work on open files. Stopping SQL Server might not be a problem for small shops, but larger enterprise shops trying to maintain 4 or 5 nines (99.99% to 99.999%) of availability will be hard-pressed to find enough time for regular OS-level defragmentation. The best practice for highly available SQL Servers is to create your files on contiguous space that's already been defragmented, planning ahead of time for data growth and building that growth into the size of the initial files.
Send your technical questions to Brian Moran at [email protected].
5. Events Central
For a complete guide to Web and live events, see
Discover solutions that minimize the likelihood of downtime in your SQL Server implementation and help to ensure continuous SQL Server application availability. SQL Server Magazine invites you to attend a free, interactive Web seminar designed specifically for SQL Server professionals. This live, online event will be presented August 19. Register today—it's free!
The SQL Server Magazine Connections conference is coming to Las Vegas, Nevada, November 7-10. Registration includes an exclusive day of presentations on SQL Server 2005 by the Microsoft SQL Server 2005 product team! All attendees receive the latest SQL Server 2005 beta and Visual Studio 2005. Call 203-268-3204 or 800-438-6720.
6. New and Improved
(contributed by Dawn Cyr, [email protected])
Guardium announced the SQL Guard Security Suite, a set of integrated applications for protecting relational databases. The suite, which requires Guardium's SQL Guard platform, includes three application modules: SQL HealthGuard, SQL PolicyGuard, and SQL AuditGuard. SQL HealthGuard lets you monitor, measure, and report on overall database security health. The module also tracks client/server interactions and draws a connectivity map of those interactions so that you can perform effective analyses of your system's security health. The SQL PolicyGuard module automates the process of developing database access rules and enabling database access controls. The SQL AuditGuard module monitors all database activities and automates the process of database auditing and regulatory compliance reporting. Because these applications work through the SQL Guard platform, which is non-intrusive, the applications don't affect database performance. The SQL Guard Security Suite is priced at $2995 per module. The SQL Guard database security platform starts at $12,995. For more information, contact Guardium at 877-487-9400 or [email protected]
AquaFold announced Aqua Data Studio 3.7, a database administration and query tool that works with all databases on all OSs. Through one interface, DBAs and developers can use the Java-based product to work on tasks in SQL Server, Oracle, IBM DB2, and other relational databases running on Windows, Mac OS X, Solaris, or Linux platforms. The new release lets you visually create and alter database schema objects, including storage objects for databases. Schema extraction gives you the ability to extract the definition of any database object including security and storage objects. And a new import tool provides a simple interface to import data into any database from various data sources. A commercial license for Aqua Data Studio costs $89 per user, and licenses for personal and educational uses are free with the purchase of a commercial license. For more licensing information, contact AquaFold through the company's distributor, Regsoft, at 800-780-8137 or [email protected]
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.
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.
Copyright 2004, Penton Media, Inc.