SQL Server Magazine UPDATE, July 1, 2004--The DTA Hero

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

Download Idera's Free Performance Monitor for SQL Server

Amaze Your Coworkers with Your SQL Server Smarts

July 1, 2004—In this issue:

1. SQL Server Perspectives

  • The DTA: A Jack-of-All-Trades Hero
  • 2. News and Views

  • SQL Server 2005 Express Edition to Replace MSDE
  • Microsoft Releases Visual Studio 2005 Beta 1
  • Results of Previous Instant Poll: New Microsoft BI Tools
  • New Instant Poll: DBA vs. DTA
  • 3. Announcements

  • Get 25% Off the SQL Server Magazine Master CD
  • Online Resource for SQL Server DBAs and Developers
  • 4. Resources

  • What's New at sqlmag.com: Pivot (Or Unpivot) Your Data
  • Hot Thread: Deleting Large Backup Files
  • Tip: Finding File-Creation Time
  • 5. Events Central

  • Get Smart! Evaluate Your Options in the Entry-Level Server Market
  • 6. New and Improved

  • Incorporate SQL Statements into Automated Tasks
  • Increase SQL Server's Value to Your Organization
  • Build Reports from Analysis Services Cubes

  • Sponsor: Download Idera's Free Performance Monitor for SQL Server
    Idera's SQLcheck provides real-time performance monitoring for SQL Server, plus your server's hardware and operating system. The new version 2.0 features a secure screensaver, and a user interface design that enables easy visibility of diagnostic information from a distance. Get the information you need to efficiently manage your SQL Server environment.
        Download SQLcheck now!

    1. SQL Server Perspectives

  • The DTA: A Jack-of-All-Trades Hero

  • by Brian Moran, [email protected]

    In the land of Not So Long Ago, the DBA was the hero of the IT shop. The DBA determined the content, internal structure, and access strategy for the IT shop's database; defined security and integrity rules; and monitored performance. But in the land of Here and Now, the up-and-coming hero is the data-tier architect, or DTA. This valiant character is a senior SQL Server DBA and a strong developer.

    I first wrote about our noble DTA hero in October 2000, noting that "as the lines blur between database technologies and the server products you use to build these e-solutions, the lines delineating the professionals who support these technologies also blur, creating a new position I call a data tier architect (DTA)" (see "Birth of the Data Tier Architect" at http://www.winnetmag.com/article/articleid/15789/15789.html ). Years ago, it was hard to imagine a company without a DBA to manage its relational-database infrastructure. Today, most of my smaller customers and many of my midsize customers don't employ anyone with the DBA title. Only my larger customers (Fortune 500-size) seem to have DBAs in the traditional sense. And yet, those small and midsize companies have big investments in relational-database technology.

    In the land of Here and Now, the mighty position of DBA is sometimes relegated to a junior role that's focused on simple backup tasks. The development team or roving architects who might be working on multiple projects at the same time now make the fundamental architectural decisions regarding the database. That's not necessarily a good thing.

    Companies can save money--in the short term anyway--by making their development team responsible for the database. But this decision might be significantly more expensive than cutting out DBAs in the long run if someone who doesn't have the experience of an expert DBA makes a very bad database decision. Organizations are also finding that daily database maintenance requirements, for SQL Server in particular, are easier to keep up with than they used to be, thanks to continual improvements that have made the software and administration tools easy to use. Today, setting up a SQL Server and creating a workable database doesn't take long (although you might not get a correctly or optimally implemented database solution).

    In general, right now, the sky isn't falling on IT shops that don't have full-time DBAs to manage their important database assets. But I wonder whether we're approaching a point where the lack of full-time, permanent DBAs will begin to create serious problems for organizations. On one hand, SQL Server management tools get easier to use all the time. On the other hand, applications are becoming increasingly complex and easier to build. And as we build more complex applications with easier-to-use tools, it's easy to make serious architectural mistakes.

    The core roles performed by DBAs will never go away. But as the tools make it easier to perform basic tasks, such as backing up a database, I expect the traditional DBA role will be less important than the role of a multifaceted DTA is.

    I coined the term DTA 4 years ago to describe the role I had been playing for several years. I wasn't a developer, although I could whip up a program if I needed to. And I wasn't a DBA because I had no operational responsibility for doing backups, ensuring fault tolerance, or performing other traditional database-management tasks. What I had was a firm grasp of the multidisciplinary skills required to plan, build, and maintain complex database solutions. A specialized jack-of-all-trades seems an oxymoron, but a DTA is just that: a master of everything to do with database issues. This is a common role at smaller shops that no longer have a full-time DBA. However, many of the folks playing the role of DTA might not be strong enough in advanced areas of database technology, such as performance tuning and disaster recovery, to fully replace the skills a DBA can offer. There are always a couple of developers that everyone looks to for answers to database questions. But is there a way for those developers to nurture their multidisciplinary data-management skills without becoming DBAs and giving up their development background? And, I think that large enterprises will need traditional DBAs for a long time to come. Backing up a handful of small databases might be a task for a junior employee, but designing a backup-and-recovery plan for an enterprise with hundreds of databases encompassing terabytes of data is beyond the reach of a simple wizard.

    The role of DTA has gone unrecognized long enough. The DTA has been an important piece of the corporate IT puzzle for years, and organizations would be well served to recognize the DTA as a distinct career path, different than that of a developer or generic software architect. In the coming months, I'll talk more about the DTA's role and how you can leverage this role to build your career as a SQL Server professional. I'll also share my thoughts on how SQL Server 2005 will affect both traditional DBAs as well as the evolving DTA role. But first, I want to hear your thoughts. Does your company have a full-time DBA? If so, how large is your company? Are you a traditional DBA, or did the lines between DBA and developer begin blurring for you long ago? I'll share the most interesting comments and perspectives in upcoming commentaries as I discuss the future of DBAs, DTAs, and SQL Server 2005.

    Sponsor: Amaze Your Coworkers with Your SQL Server Smarts
    Subscribe to SQL Server Magazine and get quality content that is relevant to all SQL Server users. In addition to receiving the magazine each month, you will find a treasury of SQL Server articles, hot-topic discussions, savvy advice, and timesaving tips online. You will also be given full access to the entire online article archive during your subscription term, with columns that feature such experts as Brian Moran, Itzik Ben-Gan, and Kalen Delaney. Subscribe today and get the newest System Table Map Poster and Subscriber Benefits Card. Order now:

    2. News and Views

  • SQL Server 2005 Express Edition to Replace MSDE

  • To try to win over nonprofessional developers, Microsoft announced the release of stripped-down "Express” versions of some of the company's products, including a replacement for Microsoft SQL Server Desktop Engine (MSDE). SQL Server Express Edition, which will be available free of charge is the evolution of MSDE 2000 in SQL Server 2005, it's a free, easy-to-use, and lightweight version of SQL Server 2005. The product aims to reduce the complexity of a typical database system by providing a simple but powerful development environment for building data-driven applications. Users can design schemas, add data, and query local databases inside the Visual Studio 2005 environment. Microsoft also announced Express-version programming tools, including Visual Web Developer Express Edition, Visual Basic Express Edition, and Visual C# Express Edition. Microsoft hasn't released pricing information for the developer tools yet, but industry experts expect the tools to cost around $50 each. The SQL Server Express Technical Preview is available for download at

  • Microsoft Releases Visual Studio 2005 Beta 1

  • At Microsoft TechEd Europe in Amsterdam on Tuesday, Microsoft announced the release of Visual Studio 2005 Beta 1. Microsoft Developer Network (MSDN) subscribers will be the first to receive the beta release, which will be available for download within the next few days. In a few weeks, customers who don't subscribe to MSDN will be able to order a copy of Beta 1 for a nominal fee. There's no "Go Live" license for the beta, so use is limited to testing and evaluation.

  • Results of Previous Instant Poll: New Microsoft BI Tools
  • The voting has closed in SQL Server Magazine's Instant Poll for the question, "Will you download the Business Scorecards Accelerator and the Excel Add-in for Analysis Services?" Here are the results (+/1 percent) from the 107 votes:

    • 26%    Yes, I will download both products
    • 4%     I will download the Business Scorecards Accelerator
    • 24%    I will download the Excel Add-in for Analysis Services
    • 46%    No, I won't download either product

  • New Instant Poll: DBA vs. DTA

  • The next Instant Poll question is "How would you classify your position?" Go to the SQL Server Magazine Web site and vote for 1) a traditional DBA, 2) a DTA, 3) a cross between a DBA and DTA, 4) a developer, or 5) other.

    3. Announcements

  • Get 25% Off the SQL Server Magazine Master CD

  • Introducing version 8 of the SQL Server Magazine Master CD. Subscribe today and get portable, high-speed access to a 5-year library of articles, code, tips, tricks, and expertise published in SQL Server Magazine and T-SQL Solutions. Let this helpful resource save you some time anywhere you are. Subscribe now!

  • Online Resource for SQL Server DBAs and Developers

  • 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:

    4. Resources

  • What's New at sqlmag.com: Pivot (or Unpivot) Your Data

  • Data integrity, data consistency, and avoiding anomalies are the important considerations when you store data in your database. However, when you query data, you usually think about your application's users and the information that you provide them. You often want to let them look at the data in a different format than the raw form in which it's stored. When you query data for analysis purposes, you typically want to produce either a small result set that fits on a screen or an intermediate result set that can be more easily manipulated by another query than your source data can. For these purposes, you might want to pivot your data. In his July T-SQL 2005 article, "Pivot (or Unpivot) Your Data," Itzik Ben-Gan discusses some techniques for pivoting data in SQL Server 2000 and introduces SQL Server 2005's PIVOT operator. Read this article today at

  • Hot Thread: Deleting Large Backup Files

  • Rashmic uses the Database Maintenance Plan Wizard to perform a database backup every 2 days. The scheduled task also deletes the previous backup file. In the backup log, Rashmic receives the error "Unable to delete the file D:\backup\livelink\livelink_db_200406212331.BAK. 0 file(s) deleted." When Rashmic tries to delete the 8GB backup file, the system says the file is too big for the Recycle Bin. Is there a way to override the Recycle Bin and permanently delete the backup file? Offer your advice and see what other people have said on SQL Server Magazine's Administration forum at

  • Tip: Finding File-Creation Time

  • by Brian Moran, [email protected]

    Q. Does SQL Server keep track of when new files and filegroups are added to or removed from a database, maybe in the form of a timestamp in the catalog? If so, how can I access that information?

    A. SQL Server doesn't store file-creation times in the system tables. However, if you have a database file's full name, you can retrieve the file's creation time by using xp_getfiledetails:

     EXEC master..xp_getfiledetails 'C:\Program Files\Microsoft SQL

    The command's result set will tell you when the file was created, among other things. This technique won't work for retrieving filegroup creation times, however, because filegroups don't have a physical structure at the OS level. I don't know of any way to determine when a filegroup was created or when a particular file was added to a filegroup.

    5. Events Central

    For a complete guide to Web and live events, see

  • Get Smart! Evaluate Your Options in the Entry-Level Server Market

  • Comparing the options in the server market, including the decision to purchase an OEM-supplied server versus building your own, can be a daunting task. This free Web seminar provides an introduction to entry-level servers, evaluates the current market of entry-level servers, and assesses the value of vendor-supplied service and support. Register now!

    6. New and Improved

    by Dawn Cyr, [email protected]

  • Incorporate SQL Statements into Automated Tasks

  • Unisyn Software announced AutoMate 5.0, software that automates frontand back-office IT tasks. By using the software's drag-and-drop interface, you can quickly assemble tasks without writing code. The product lets you incorporate SQL statements into your automated tasks. For example, you can create a task by dragging pre-built actions into AutoMate's Task Builder window, then drop an SQL object into the sequence. The entire task, including the SQL action, will run automatically when a scheduler or other criterion triggers it. DBAs can use this type of automated task to support such business needs as monitoring sales data. The product's new release includes TCP/IP-based remote administration capabilities, customizable error handling, improved debugging, and new triggers and actions. A free fully-functional trial version of AutoMate 5.0 is available for download. For more information or a price quote, contact Unisyn Software at 888-786-4796, 213-738-1700, or [email protected]

  • Increase SQL Server's Value to Your Organization

  • Imceda announced its Colors of Speed product line, three software products that provide SQL Server management capabilities designed to make DBAs more efficient and protect SQL Server 2000 and 7.0 databases. BlueSpeed Coefficient is a monitoring tool that tracks I/O activity and provides reports that you can use to fine-tune the database and improve performance. The software tracks SQL and stored-procedure execution statistics, including call frequency, CPU usage, execution duration, reads and writes, stored procedure recompilation and caching, timeouts, errors, and exceptions. GreenSpeed Change Manager is a database comparison tool that compares two databases, displays their differences, and generates scripts that you can use to reconcile those differences. GreenSpeed IDE is an integrated development environment that lets you quickly develop, store, and share code and ensures code accuracy and suitability. Free trial copies of all three products are available for download. For pricing and other information, contact Imceda at 888-763-7685, 781-229-6300, or [email protected]

  • Build Reports from Analysis Services Cubes

  • XMLA Consulting announced Report Portal 1.4, a zero-footprint OLAP tool that lets you create reports by accessing Analysis Services cubes through the XMLA API. The tool provides reporting features such as the ability to export reports to Microsoft Excel or send reports by email, drilldown capabilities, sorting and filtering, OLAP write-back capability, Key Performance Indicator (KPI) support, and data-mining report creation. The tool works with SQL Server 2000 and supports Business Objects Crystal Reports 8.0. Report Portal 1.4 costs $995 for a single-server or developer license, and $4995 for a companywide enterprise license. A free 60-day evaluation version is available for download. For more information, contact XMLA Consulting at 813-977-1562 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.

    Contact Us:

    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.

    Hide comments


    • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

    Plain text

    • No HTML tags allowed.
    • Web page addresses and e-mail addresses turn into links automatically.
    • Lines and paragraphs break automatically.