THIS ISSUE SPONSORED BY
SQL Server Magazine Connections: Win a Harley!
OmniReplicator Accelerates Data Integration
(below NEWS AND VIEWS)
SPONSOR: EXPERIENCE THE BENEFITS OF REAL TIME MONITORING
ELM Enterprise Manager 3.1 gives you the power to see the health and status of your systems at a single glance. Imagine the time savings and business continuity increases available when event frequencies, performance trends, and service state changes are clearly displayed and easily accessible. Equally important, be alerted by email, page, or instant message. To experience the benefits of real time monitoring, download your FREE evaluation copy of ELM Enterprise Manager 3.1 TODAY.
April 24, 2003—In this issue:
- Analysis Services Leads OLAP Market
2. SQL SERVER NEWS AND VIEWS
- Microsoft Christens Windows Server System
- Fix: DTS Designer Disappears After SP3 Upgrade
- Results of Previous Instant Poll: Business Intelligence
- New Instant Poll: Moving to 64-Bit
- Join the HP & Microsoft Network Storage Solutions Road Show!
- Security Bootcamp Online Advanced-Level Training
- What's New at T-SQL Solutions.com: Detailed Aggregates
- Hot Thread: Remote DBAs
- Tip: Moving SQL Server 7.0 Databases to SQL Server 2000
5. NEW AND IMPROVED
6. CONTACT US
See this section for a list of ways to contact us.
(contributed by Brian Moran, news editor, [email protected])
It took more than a decade for SQL Server to gain across-the-board credibility in the enterprise relational database management system (RDBMS) market. It's taken much less time for SQL Server to achieve a leading position in the OLAP space. The OLAP Report, an independent resource for OLAP technical information, recently crowned SQL Server 2000 Analysis Services the market leader in the highly competitive business intelligence (BI) space. You can read the entire report at http://www.olapreport.com/Market.htm , but here are two of the report's most interesting findings—as well as a prediction of my own.
First, according to The OLAP Report, Microsoft claimed 24.4 percent of the OLAP market in 2002, with Hyperion grabbing 22.9 percent of the market, and Cognos coming in at 12.6 percent. No other vendor had more than a 10 percent market share. The top two vendors accounted for almost 58 percent of the market, leaving a surprisingly large number of vendors fighting it out for the remaining 42 percent.
Second, Microsoft was the only major RDBMS vendor to have a significant piece of the OLAP market. Oracle had 4.7 percent, whereas IBM weighed in with 2.2 percent. And Oracle and IBM both lost market share, whereas Microsoft gained a few percentage points. "Microsoft's SQL Server Analysis Services grew faster than the market and therefore increased its market share again," noted Nigel Pendse, lead author of The OLAP Report's 2002 market-share report. "As in previous years, usage of Analysis Services grew faster than SQL Server itself, as Oracle and DB2 sites continue to select Analysis Services to build BI solutions on top of their existing relational databases."
And here's my prediction: Long term, BI will have huge ramifications for the competitive position of the major database vendors. The ultimate purpose of BI—whether you're using OLAP, data mining, or an old-fashioned spreadsheet—is to help users and companies make better decisions. So, it stands to reason that almost all companies should be using data analytics (or BI) in some capacity.
A somewhat artificial distinction exists today between the online transaction processing (OLTP) and OLAP worlds. When you get down to it, data is data, and most OLAP solutions have their roots in the OLTP world. After all, someone has to collect the transactions and raw data in the first place. And eventually, people use some sort of extraction, transformation, and loading (ETL) process to put that transactional data into a data warehouse, where other people analyze it and split it into smaller data sets. Because of technical limitations, doing complex analytics directly on live transactional databases simply isn't practical today. But I believe that software and hardware will eventually improve to the point where OLTP and OLAP data stores start to blur. When that happens, why bother with a complicated ETL solution if you can effectively build and query your data marts without it?
Industry reports tell us that the OLAP market is growing steadily—and typically much faster than the RDBMS market. And according to The OLAP Report, Microsoft is the only major RDBMS vendor that's successfully capturing OLAP market share. As the lines between OLAP and OLTP continue to blur, I predict that Microsoft will reap huge competitive advantages over its traditional OLTP competitors, who aren't taking the OLAP space seriously.
SQL SERVER MAGAZINE CONNECTIONS: WIN A HARLEY!
Register today for SQL Server Magazine Connections, and you'll get FREE access to Microsoft ASP.NET Connections and Visual Studio Connections. Attendees will also get a chance to win a brand-new Harley-Davidson motorcycle. SQL Server Magazine writer Brian Moran is the conference chair. Brian has planned sessions for DBAs, SQL developers, data warehouse/BI architects, and data-access specialists. In addition to Brian, the group of expert speakers includes world-renowned gurus such as Kalen Delaney, Bill Vaughn, Fernando Guerrero, Kimberly Tripp, and more. Get the latest SQL Server roadmap from Microsoft's Director of SQL Server Product Management, Stan Sorenson, during his opening keynote address.
2. SQL SERVER NEWS AND VIEWS
(contributed by Paul Thurrott, [email protected])
As expected, Microsoft announced that the company is renaming its .NET Enterprise Servers to the Windows Server System, in keeping with previously announced plans to drop the .NET moniker from most of its product names. As with the name of Microsoft's Office productivity suite, which the company renamed Microsoft Office System, the name Windows Server System suggests a comprehensive, integrated, and interoperable product line, Microsoft says, one that addresses the complexity of enterprise operations.
"There are really two primary reasons for this change," said Paul Flessner, senior vice president of the Server Platform Division. "First, we are sending a clear signal to our customers and industry partners that we have heard their feedback—that IT has become increasingly complex and costly and less able to deliver business value. With Windows Server System, we are helping them understand the value that our comprehensive, integrated, and interoperable server infrastructure delivers today, as well as making a long-term commitment to reduce IT complexity and costs. Second, by aligning the new brand with the server platform, we are clarifying that our long-term server business and technology strategy starts with Windows Server at the foundation. With this new brand, we are emphasizing to our customers and industry partners the business value of a top-to-bottom integrated server infrastructure. We want our customers and partners to know that we are working hard to ensure they are getting the best return on their investments with Windows Server System."
Flessner says that the Windows Server System encompasses all the company's business-server categories, including e-business (BizTalk Server, Commerce Server, Content Management Server, Host Integration Server), data management and analysis (SQL Server), messaging and collaboration (Exchange Server, SharePoint Portal Server, Project Server, Real-Time Communications—RTC—Server), security (Internet Security and Acceleration—ISA—Server) and management (Systems Management Server—SMS, Microsoft Operations Manager—MOM, Application Center). And, of course, Windows Server 2003 sits at the foundation of this product line.
Microsoft was set to begin rolling out the new product branding this week at the Windows 2003 launch and will provide further information about its server-product strategy at the Microsoft TechEd 2003 trade show in early June.
If the Data Transformation Services (DTS) Designer unexpectedly disappeared after you upgraded to SQL Server 2000 Service Pack 3 (SP3), Microsoft has a fix for you. Because of an unhandled access violation, the DTS Designer and Enterprise Manager might both disappear after you upgrade to SP3. The supported fix, described in the Microsoft article "FIX: DTS Designer May Generate an Access Violation After You Install SQL Server 2000 SP3," is intended only for computers that are experiencing this problem. Microsoft notes that the fix might receive additional testing, so if you aren't severely affected by this problem, Microsoft recommends that you wait for the next SQL Server 2000 service pack that contains the fix.
The voting has closed in SQL Server Magazine's nonscientific Instant Poll for the question, "Which SQL Server business intelligence (BI) tools are you using?" Here are the results (+/- 1 percent) from the 262 votes:
- 43% OLAP - 2% Data mining - 2% SQL Server Accelerator for Business Intelligence - 8% More than one of the above - 45% None of the above
The next Instant Poll question is "When do you plan to move to 64-bit SQL Server?" Go to the SQL Server Magazine Web site and vote for 1) Within 6 months, 2) Within 1 year, 3) Within 2 years, 4) We need more information before deciding, or 5) We have no plans to move to 64-bit.
SPONSOR: OMNIREPLICATOR ACCELERATES DATA INTEGRATION
Lakeview Technology's OmniReplicator provides real-time changed-data replication of your cross-platform information and automates data sharing across Oracle, DB2, Sybase and SQL Server. View the NEW OmniReplicator Demo by visiting our Web site.
(brought to you by SQL Server Magazine and its partners)
Now is the time to start thinking of storage as a strategic weapon in your IT arsenal. Come to our 10-city Network Storage Solutions Road Show, and learn how existing and future storage solutions can save your company money—and make your job easier! There is no fee for this event, but space is limited. Register today!
SSMU e-Learning Center, partnering with Scalability Experts, is offering a live online advanced-level training course titled "Microsoft SQL Server 2000 Security Bootcamp." DBAs, developers, and IT managers will learn how to leverage the security features of SQL Server 2000 to build intelligent, flexible, and secure database access. Go now to
COMPUTE and COMPUTE BY clauses aren't allowed within a view's definition, but they're important clauses to understand for detailed reporting. For summarizing data, many people use only GROUP BY. But when you want more than just the summary data (GROUP BY returns only one row for each grouping), using COMPUTE and COMPUTE BY is the best way to go. In her latest T-SQL Tutor column, "Detailed Aggregates," Kimberly L. Tripp explains the COMPUTE and COMPUTE BY T-SQL extensions, whose result sets produce summary data while including all the details. This article is available for free to registered visitors at the T-SQL Solutions Web site:
Netmikem, a longtime SQL Server user and administrator, wonders about the feasibility—practically more than technically—of DBAs working remotely for one or more companies. Is such a job situation just a pipe dream, or can it actually work? Read what other DBAs have said, and offer your advice, on SQL Server Magazine's Career Development forum at the following URL:
(contributed by Microsoft's SQL Server Development Team, [email protected])
Q. I have SQL Server 7.0 development databases installed in a directory called D:\mssql7. I want to move the user databases, Data Transformation Services (DTS) packages, database diagrams, maintenance plans, logins, and other relevant data to a SQL Server 2000 development box. I've had a hard time creating a SQL Server 2000 mirror image of our SQL Server 7.0 server. SQL Server 2000 tools such as the Database Copy Wizard offer only about 90 percent of the functionality I want. I decided to handle part of the problem—moving the databases—by manually moving data following these steps:
- I installed SQL Server 7.0 on the new server in a directory called D:\mssql7\data.
- I copied the .mdf and .ldf files from the old server into the new directory.
- I upgraded SQL Server 7.0 on the new server to SQL Server 2000.
The manual copy worked, and I'm on SQL Server 2000, but the overall directory name is still D:\mssql7—the upgrade didn't rename my directories. How can I rename the directory to D:\mssql7\data without damaging the SQL Server installation?
A. SQL Server doesn't rename directories as part of the upgrade process because doing so might affect your organization's carefully thought-out corporate standards for directory placement and naming. If you want to rename the directory, you have to relocate the data manually. Microsoft provides two ways to move databases and their log files. Your choice depends on the amount of space available on your new server. If you're short of space, consider using the sp_detach_db and sp_attach_db stored procedures to detach and move the files, then re-attach them by calling sp_attach_db with arguments that specify the new file locations. This method will work for all your user databases but not for the system databases (i.e., master, model, and msdb). SQL Server Books Online (BOL) explains how to specify the new file locations and attach the files. And the Microsoft article "INF: Moving SQL Server Databases to a New Location with Detach/Attach" has great step-by-step instructions for completing the move.
If you have plenty of available space or an external storage device to store the backup, you might want to use Enterprise Manager's backup and restore UI. This method lets you back up your database to disk (local or to a network share) or to tape, detach the original database, and restore the database to the new server. BOL explains how to use RESTORE DATABASE and move the file locations. You need to use the backup and restore functionality that BOL describes to move the master (in single-user mode), model, and msdb system databases. If you're using replication, you also need to reset the replication snapshot share before you modify your publications. You can reset this share in the Configure Publishing and Distribution Wizard and in the Create Publication Wizard.
Send your technical questions to [email protected]
5. NEW AND IMPROVED
(contributed by Carolyn Mader, [email protected])
HybridX announced SQL Spy and SQL Insider, freeware software for archiving, monitoring, and reporting on SQL Server 7.0 and later databases. SQL Spy provides realtime monitoring of your database. You can graphically display lock and process actions in a moving line graph, capture SQL statements sent to your SQL Server, set warning limits for the number of locks and processes, kill connections to SQL Server, and receive notifications when data objects are added to SQL Server. SQL Spy also lets you watch who is connected, for how long, and what the CPU and memory utilization levels are. SQL Insider lets you manage object permissions, check the size and row counts of tables, search words, scan passwords, and perform Internet keyword searches. Contact HybridX at [email protected]
White Town Software announced a line of shareware software for working with DBF files. The product line comprises CDBF (an advanced DBF viewer and editor), CDBFLite (software that lets you work with DBF files by using the command line and the Internet), and CDBFAPI.DLL (a library of more than 120 different tools for working with DBF files). The software can export data in SQL for any SQL Server, HTML, or XML formats. Contact White Town Software at [email protected]
6. CONTACT US
Here's how to reach us with your comments and questions:
- ABOUT THE COMMENTARY — [email protected]
- ABOUT THE NEWSLETTER IN GENERAL — [email protected]
(please mention the newsletter name in the subject line)
- TECHNICAL QUESTIONS — http://www.sqlmag.com/forums
- PRODUCT NEWS — [email protected]
- QUESTIONS ABOUT YOUR SQL SERVER MAGAZINE UPDATE SUBSCRIPTION?
Email Customer Support — [email protected]
WANT TO SPONSOR SQL SERVER MAGAZINE UPDATE?
More than 102,000 people read SQL Server Magazine UPDATE every week. Shouldn't they read your marketing message, too? To advertise in SQL Server Magazine UPDATE, contact Beatrice Stonebanks at [email protected] or 800-719-8718.
SQL Server Magazine UPDATE is brought to you by SQL Server Magazine, the only magazine completely devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.
The SQL Server Magazine Connections conference—loaded with best-practices information from magazine authors and Microsoft product architects—provides you with the latest SQL Server tools, tips, and real-life examples you need to do your job.
Receive the latest information about the Windows and .NET topics of your choice. Subscribe to our other FREE email newsletters.