SQL Server Magazine UPDATE, April 8, 2004--Data Modeling

SQL Server Magazine UPDATE—brought to you by SQL Server Magazine and SQL Server Magazine Connections

THIS ISSUE SPONSORED BY

LearnKey, Inc.

SQL Web Seminar—Tactics for Protecting SQL Server
(Below COMMENTARY)

April 8, 2004—In this issue:

1. SQL Server Perspectives

2. News and Views

3. Reader Challenge

4. Announcements

5. Resources

6. Events Central

7. New and Improved

Sponsor: LearnKey, Inc.

Enter to win FREE SQL Server 2000 Developer training on CD-ROM at http://lists.sqlmag.com/cgi-bin3/DM/y/efPX0FgQMn0BRZ02hO0A8 . LearnKey and industry experts Andy Baron & Mary Chipman will teach you techniques for installation and configuration, relational database design, and how to work with data to get results. Save 25 percent on additional SQL Server and .NET developer courses at http://lists.sqlmag.com/cgi-bin3/DM/y/efPX0FgQMn0BRZ02hO0A8 or call 800-865-0165.

1. SQL Server Perspectives

(contributed by Brian Moran, news editor)

In March, I pointed you to a Microsoft survey about a new data-modeling tool that Microsoft hasn't yet announced but is presumably planning. In my commentary "Data Modelers, Arise, and Take Microsoft's New Survey", I asked you what design features are most important for Microsoft to include in a data-modeling tool. I also promised to share my thoughts about what we need in a data-modeling solution.

First, I want to share a dirty little secret about database modeling: Most developers build databases without using a comprehensive logical model. Physical models that specify table design are common, but generally, those physical models are built piecemeal, a few tables at a time. If a logical model exists, the developer typically uses it only early in the project's life cycle. Second, I want to share an opinion that might seem ludicrous to many of my database colleagues. Building databases without logical models isn't the huge problem that most DBAs and data modelers believe it is. I'm not saying that a robust modeling exercise is a bad thing. I simply recognize that life goes on even though many customers skip the modeling phase. Egads! How could such heresy escape my lips? Will I be branded with a scarlet H by a band of angry data modelers? Maybe. But these two points will help you understand what I want in a data-modeling tool. (I assume that you understand basic data-modeling concepts and that I don't have to explain them here.)

Most data-modeling tools ignore the day-to-day modeling and design needs that designers face. Making nice pictures of my tables is a fraction of what I need to worry about and model. I might be stretching the textbook definition of database modeling, but I think a model should include all the physical components of the database system—components that fall outside the scope of traditional data-modeling tools. What about my Data Transformation Services (DTS) packages, SQL Server Agent jobs, stored procedures, indexes, views, indexed views, computed columns, users, roles, permissions, physical disk layout, and anything else I can think of? The list is endless. I want a data-modeling tool to provide traditional database modeling that addresses the physical database and my server-modeling needs.

Failing to model can lead to numerous problems, and I spend a lot of time modeling the systems that I work with. However, I've done performance tuning and other fix-it-after-the-fact problem solving for many customers who haven't conducted a rigorous modeling exercise. Most of these customers' applications are reasonably well designed and meet or exceed the needs of the business community they serve. Plus, few of my clients' performance problems end up being related to schema problems. I think that the developer community has learned basic data modeling. The applications aren't perfect and might not be well normalized, but they aren't crippled by egregious modeling errors either.

Modeling is important, but most people get away with doing less modeling than textbooks recommend. However, many customers suffer severe consequences related to performance, availability, and manageability because they don't have better tools for designing and managing the physical database and server. Maybe it's a pipe dream to think that Microsoft can give me a tool that addresses my logical data-modeling needs and the myriad physical-modeling problems that I need to consider. Maybe—but I can hope, can't I?

Sponsor: SQL Web Seminar—Tactics for Protecting SQL Server

Since SQL Server is used to power some of the most critical information applications, it's important to protect it from outside forces (weather, user error, system outage) that can jeopardize the application and associated data. Sign up now for a free, 1-hour Web seminar on May 4, sponsored by NSI Software. Not only will you learn several solutions associated with protecting SQL Server, but you will also work to identify the costs, risks, and advantages of each so you can determine if you have a protection scheme that is well matched to your business needs. Register today!

2. News and Views

Despite the growth in OLAP sales and usage, estimating the exact size of the OLAP market is becoming increasingly difficult. One way of measuring market consolidation is to track the collective market share of the top 5 or 10 vendors in the market each year. The OLAP Report's recent market-share analysis shows that the OLAP market was fragmenting until 1999 but has been consolidating since then, with the 2003 market being clearly more consolidated than in any other year in the past decade. The OLAP Report says that 2003's 7 percent growth in the OLAP market was the best since 2000 but that much of the reported "growth" was a result of a weak dollar. Microsoft's growth rate fell, but it consolidated its lead over Hyperion Solutions and is now the largest OLAP vendor. The top 10 OLAP vendors now control a record 95 percent of the market. Read the full report

The SQL Server Web Data Administrator lets you easily manage your SQL Server databases, wherever you are. From Microsoft Internet Explorer or another Web browser, you can use the new Web Data Administrator's built-in features to create and edit databases in SQL Server 2000 or Microsoft SQL Server Desktop Engine (MSDE), perform ad hoc queries against databases and save them to your file system, export and import database schema and data, manage users and roles, and view, create, and edit stored procedures. If you're doing Windows or Web development or need remote access to data, download the Web Data Administrator athttp://www.microsoft.com/downloads/details.aspx?familyid=c039a798-c57a-419e-acbc-2a332cb7f959&displaylang=en

Are you attending SQL Server Magazine Connections in Orlando, Florida, April 18-21? If so, the editors of SQL Server Magazine would like to treat you to lunch and pick your brain. We are organizing a reader lunch to learn more about your responsibilities, challenges, resource needs, and how you use SQL Server Magazine, SQL Server Magazine UPDATE, and our Web site to help you do your jobs better and faster. The lunch will be Tuesday, April 20, at 11 a.m. Space is limited, so if you'd like to share your needs and help shape the direction of future content, please send an email today with your name, company name, email address, and daytime phone number to [email protected]. We look forward to seeing you in Orlando!

The voting has closed in SQL Server Magazine's Instant Poll for the question, "How long have you worked with SQL Server?" Here are the results (+/- 1 percent) from the 435 votes (deviations from 100 are due to a rounding error):

The next Instant Poll question is "What type of authentication do you use in your SQL Server environment?" Go to the SQL Server Magazine Web site and vote for 1) SQL Server and Windows authentication (mixed), 2) SQL Server authentication, 3) Windows authentication, 4) both, for different situations, or 5) I'm not sure.
   http://www.sqlmag.com

3. Reader Challenge

contributed by Umachandar Jayachandran
Congratulations to Zsolt Peter, a developer for Cosys, Ltd., in St. George, Romania, and Kristofer Andersson, a senior software developer for RR Technologies in Fort Lauderdale, Florida. Zsolt won first prize of $100 for the best solution to the April Reader Challenge, "Protecting Against SQL Injection." Kristofer won second prize of $50. You can find a recap of the problem and the solution to the April Reader Challenge at
    http://www.sqlmag.com/articles/index.cfm?articleid=42216

Now, test your SQL Server savvy in the May Reader Challenge, "Trading Stocks" (below). Submit your solution in an email message to [email protected] by April 15. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We'll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Here's the challenge: Jane is a database programmer for a company that provides stock-trading services online. A SQL Server 2000 database stores the transactions users create. The database contains two tables, Stocks and TradeSummary, that contain information about traded stocks and the trade details. The tables' schema with sample data are available. Jane needs to create a report that provides daily summary information about the various stock trades. For each stock, she wants daily stock details, the opening buy or sell price, the closing buy or sell price, the volume of stocks purchased, the highest buy or sell price, and the lowest buy or sell price. Help Jane write a query that provides these report details.

4. Announcements

Microsoft's SQL Server team has launched a new survey to help it and its community partners better understand your needs and help improve your experience with SQL Server. Take time to let Microsoft know how satisfied you are with the availability of SQL Server information and peer support in the SQL Server communities. Click here:
    http://lists.sqlmag.com/cgi-bin3/DM/y/efPX0FgQMn0BRZ0BGzj0AL

If you are an administrator or developer and work with SQL Server, SQL Server Magazine can help you at work. Subscribe today and you will gain access to a treasury of SQL Server experts, content, tips, code listings, articles and more. BONUS—the System Table Map Poster. Click here for details:
    http://lists.sqlmag.com/cgi-bin3/DM/y/efPX0FgQMn0BRZ0BG8W0Au

5. Resources

Secrets are always intriguing, and programming back doors-secret entrances into application code—are no different. In his April T-SQL Black Belt column, "UDF Back Doors," Itzik Ben-Gan brings you around back for a peek into the hidden workings of some undocumented user-defined function (UDF) features. To learn about creating your own UDFs and UDFs that modify data, read this article today at
    http://www.sqlmag.com/articles/index.cfm?articleid=41845

Nezster is building a view that creates a large un-normalized recordset that a third party reporting tool will use as a base data cube. Nezster has 15 tables on a SQL Server 2000 database. An Orders table has 165,000 rows, and a Customer Master table has 150,000 rows. The Orders and Customer Master tables need to be linked, but when Nezster runs the view, a timeout error occurs. How can Nezster get his view to run without timing out? Offer your advice and see what other people have said on SQL Server Magazine's Development forum at
    http://www.winnetmag.com/sqlserver/forums/messageview.cfm?catid=1670&threadid=119273

by Brian Moran

Q. I accidentally deleted the system stored procedure sp_grantdbaccess. I've added it back into the master database, and the owner is correctly listed as dbo. However, the owner column value in Enterprise Manager shows up as "user" when I look at the list of procedures in Enterprise Manager. I'm worried that something won't work properly if SQL Server thinks the procedure is a user procedure rather than a system procedure. How can I get Enterprise Manager to list sp_grantdbaccess as a system procedure again?

A. First, the owner column value being "user" won't affect the procedure's ability to work as it's intended for managing account security in SQL Server. However, you can use the sp_MS_marksystemobject procedure to correctly display the procedure as a system object:

   EXEC sp_MS_marksystemobject 'sp_grantdbaccess'

The sp_MS_marksystemobject procedure sets a bit in the status column of the master..sysobjects table, which various SQL Server tools (such as Enterprise Manager) use to determine if the object is a system object. Interestingly, sp_MS_marksystemobject is one of the few procedures that doesn't show up in Enterprise Manager's list of stored procedures, even when Enterprise Manager is configured to show system objects. However, you can view the procedure's text by using

   sp_helptext 'sp_MS_marksystemobject'

6. Events Central

For a complete guide to Web and live events, see
   http://www.winnetmag.com/events

SQL Server Magazine Connections will be held April 18-21 with concurrently running events Microsoft ASP.NET Connections and Visual Studio Connections. Register now and receive access to all three conferences for one low price plus a chance to win a Harley. Register online or call 203-268-3204 or 800-438-6720.
    http://lists.sqlmag.com/cgi-bin3/DM/y/efPX0FgQMn0BRZ0ggP0A1

More than ever, data availability is crucial for business service. Sign up today for a free, 1-hour Web seminar on April 29, sponsored by BMC Software, and learn the associated best practices for SQL Server backup and recovery. Register now and get a free SQL-BackTrack License.
    http://lists.sqlmag.com/cgi-bin3/DM/y/efPX0FgQMn0BRZ0BGzl0AN

7. New and Improved

(contributed by Dawn Cyr, [email protected])

Prophet 21 announced the latest release of CommerceCenter, its Windows-based SQL Server enterprise software solution. CommerceCenter 10.0 includes multiple new features including audit trails, electronic document imaging functionality, and enhanced PDA capabilities. Audit trails let users see who modified data, when changes were made, and the new and old values of data. Document imaging lets users link documents such as vendor invoices, customer purchase orders, handwritten letters, and email messages to any transaction or maintenance record within the solution. Improved PDA support lets users integrate PDAs into processes to validate bar-coded items and receive documents. CommerceCenter 10.0 works with SQL Server. For pricing and other information, contact Prophet 21 at 1-800-776-7438 or [email protected].
    http://lists.sqlmag.com/cgi-bin3/DM/y/efPX0FgQMn0BRZ0BG8X0Av

MARSYS announced Colada 4.1, software that lets enterprises manage disaster recovery, increase network reliability, prevent outages, and minimize downtime risks. Colada lets you maintain realtime data replication and synchronization across diverse LAN and WAN environments by using your existing hardware and software. The product creates a reliable, synchronous replication of transactions across all your databases within a logical pool without affecting system performance and without the aid of clusters or other devices. Colada 4.1 works with SQL Server 2000 and 7.0 and has been tested with SQL Server 2005 Beta 1. For pricing and other information, contact MARSYS at 800-883-7560 or 800-796-6581.
    http://lists.sqlmag.com/cgi-bin3/DM/y/efPX0FgQMn0BRZ0BG8Y0Aw

Software FX
Chart FX OLAP-.NET front-end OLAP for Visual Studio developers.
   http://lists.sqlmag.com/cgi-bin3/DM/y/efPX0FgQMn0BRZ0BGx10AM

DB Ghost for SQL Server
Take control of your source code! Change management for SQL is here.
   http://lists.sqlmag.com/cgi-bin3/DM/y/efPX0FgQMn0BRZ0BEkO0Ab

SQL Server Magazine UPDATE is brought to you by SQL Server Magazine,the only magazine devoted to helping developers and DBAs master newand 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:

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.