Microsoft SQL Server 2005 by uzd19483


									     Microsoft SQL Server 2005
     EXCELLENT                       9.1
            Manageability (25%)                   9 9
             Performance (25%)                    9 9
               Availability (20%)                 9 9
               Scalability (20%)                  9 9
                     Value (10%)                     10
     CoSt: Standard Edition starts at $5,999 per
     processor or $1,849 with 5 CALs. Enterprise Edi-
     tion starts at $24,999 per processor or $13,969
     with 25 CALs.
     platFormS: Windows Server 2003
     Bottom lIne: SQL Server 2005 vastly improves
     capabilities on all fronts, including develop-
     ment, integration, management, and BI. Com-
     panies will be able to run safer databases,
     better manage their environment, and finally
     create a truly 24/7 operation. Among new
     high-availability features, partial restores will
     allow databases to be brought online faster
     after failures, and database mirroring, al-
     though not yet officially supported, will provide
     automatic fail-over for log shipping scenarios.

26       I N F OWO R L D . C O M 	     01.16.06
                             SQL Server
                             Bulks Up
                             Microsoft’s makeover brings high-availability,
                             disaster recovery, and other heavy-duty improvements
                             to its heavyweight DBMS

                                        o call SQL server 2000 a          brings sweeping improvements in

                                        90-pound weakling because it      almost every area. Management tasks
                                        lacks certain high-end features   have been streamlined, and monitoring
                             is a bit like calling Hercules a wimp be-    and diagnostics have been beefed up.
                             cause he never ran a marathon. Not ev-       A default locked-down configuration,
                             ery strongman can perform every feat,        data encryption, and other new features
                             and not every company needs every            strengthen security. Analysis Services,
                             heavyweight feature. Many enterprises,       Reporting Services, and Data Mining are
                             large and small, have been running their     now fully integrated and mature. DTS
                             businesses on SQL Server for years.          (Data Transformation Services) has been
         BY SEAN MCCOWN         Still, there’s no denying that SQL        rewritten from the ground up, emerging
                             Server always fell short of Oracle Da-       as the more powerful and flexible SQL
ILLUSTRATION BY BILL MAYER   tabase when it came to true enter-           SSIS (Server Integration Services). Inte-
                             prise features, especially in the areas      gration with Visual Studio and the .Net/
                             of high-availability and disaster recov-     CLR opens SQL Server internals to .Net
                             ery. Whereas Oracle met the demands          developers and .Net to SQL developers,
                             of mission-critical, 24/7 operation with     meaning the power of .Net can now be
                             features such as online re-indexing,         tapped for stored procedures, data flows
                             snapshot-based restores, and fail-over       and transformations, BI applications,
                             log shipping, SQL Server couldn’t pro-       and more (see the online-only sidebar,
                             vide true zero-percent data loss with-       “Taking the database beyond SQL,” at
                             out the help of third-party solutions.
                             For customers who didn’t need these            SQL Server 2005 also promises bet-
                             protections, SQL Server offered a solid      ter performance. I didn’t run perfor-
                             database with lower licensing fees. But      mance benchmarks for this review,
                             maintenance-related downtime was a           but in addition to allowing DBAs to
                             fact of life.                                partition the database to balance load,
                                Until now. More than five years in        Microsoft has worked hard to improve
                             the re-making, SQL Server 2005 not           memory management, indexing strat-
                             only addresses shortcomings in high-         egies, and query optimization. Users
                             availability and disaster recovery, but      will see the difference.

                                                                              I N F OWO R L D . C O M 	   01.16.06    27
        Many of the changes in SQL                                                                      ration and the package will run on
     Server 2005 will take current SQL                                                                  the new server.
     developers and DBAs some time                                                                         Last but not least, the new .Net
     to learn. One key high-availabil-                                                                  integration releases SSIS from
     ity feature, database mirroring, is                                                                DTS’s dependence on the limits of
     for practical purposes still “beta.” SSIS’s new control-flow containers, which allow you to        VBscript and moves serious coders
     Microsoft still has work to do, es- group operations and execute them in sequence, go far          into the richness of For this
     pecially in integrating and refining beyond the capabilities of DTS.                               reason, file and string manipula-
     SQL Server’s many tools. Micro-                                                                    tions, math operations, and Win32
     soft’s extreme makeover closes the                                                            calls are much easier and faster.
     gap between SQL Server and Oracle,          SSIS SnapShot                                        As you would expect, the increased
     however, and makes SQL Server a true        Sweeping changes in the integration toolset       power and flexibility of SSIS come at
     contender even for very large databas-      present a welcome challenge to DBAs.              the price of increased complexity. SSIS
     es and the most demanding enterprise        proS                                              presents a learning curve that can
     environments.                               b modeling paradigm, flow control
                                                   New                                             seem daunting, but after you learn how
                                                   containers, and .Net integration extend         to use it, SSIS will change the way your
     A New Way to Move Your Data                   functionality                                   company thinks about its data. Systems
     SSIS, the revamped set of tools for         bPackages are easy to write, store, and          that couldn’t communicate before are
     moving and manipulating data, not             deploy                                          now perfectly integrated and have the
     only provides greatly enhanced per-         bComplicated operations perform much             full power of .Net behind them. Com-
     formance, reliability, and functional-        faster than ever before                         plex data load operations into ware-
     ity over DTS, but also offers many new      ConS                                              houses and disparate systems will take
     prebuilt tasks that reduce the amount       bore complex toolset has quirks that
                                                   M                                               a fraction of the time to build, execute,
     of coding needed to perform the most          make it difficult to learn                      and support. Other complicated tasks
     common operations, including back-          bNot integrated with Management Studio            can also be performed much faster,
     ing up databases, re-indexing tables,                                                         which means deadlines will be met
     and running integrity checks. More                                                            more easily and projects won’t be held
     important, SSIS completely changes ing, these are natively supported in                       up because of the limited functionality
     the modeling paradigm that DTS uses, SSIS, meaning you can run data-min-                      of DTS and the way operations have to
     logically dividing its building blocks ing queries, work with slowly chang-                   be taped together.
     into separate control-flow and data- ing dimensions, and send the data
     flow components.                            wherever you like, even to Reporting              Maximum DBA
        Control-flow containers are graphi- Services reports. In fact, SSIS serves as              Microsoft has also made some dramat-
     cal representations of operations that a bridge between SSAS and SSRS and                     ic changes to its management tools, re-
     would otherwise take developers doz- any ODBC-compliant data source.                          placing Enterprise Manager and Query
     ens of lines of code and many hours           Package configurations are another              Analyzer with the new SSMS (SQL
     of debugging to create. Some of the huge enhancement. These allow you                         Server Management Studio). SSMS
     operations you can perform with the to define certain elements of the pack-                   serves as both the database command
     new containers (namely Sequence, age — file locations, database con-                          line, with which you perform tasks
     For Loop, and Foreach) can’t even be nections, special log-ins, etc. — to be                  such as creating databases and manag-
     done in DTS or would be too difficult read in from an external source (like                   ing user accounts, and the tool used to
     to manage.                                  an XML file) and applied at run time.             write database code. SSIS and Profiler
        SSIS also extends the reach into dif- This function lets you deploy packages               were left out of this merger.
     ferent platforms, and not just different very quickly because you don’t have to                  These changes won’t leave users
     databases. For example, whereas DTS change them to move them from one                         completely satisfied. For starters, doing
     provided limited visibility into OLAP system to another; you simply change                    normal query tasks, such as viewing ex-
     and practically none into data min- the dynamic elements in the configu-                      ecution plans and just running queries,

30      I N F OWO R L D . C O M 	   01.16.06
is much slower. Screens can be-                                                                 you can’t change the layouts of
come quite unresponsive while                                                                   the canned reports, nor can you
queries run, and it takes much                                                                  add your own reports or set up
longer to pull up execution                                                                     subscriptions, as you normally
plans, sometimes causing the                                                                    can in SSRS. You can manually
window to seize up. Microsoft Management Studio, which unifies OLTP and OLAP management export the reports to Excel or
also changed the layout of the and development, uses Reporting Services to provide detailed     PDF, however.
execution plans, so larger ones reports on the database.                                          One key design goal for SQL
are much harder to read. Your                                                                   Server 2005 was tighter security
favorite keyboard shortcuts have been                                                    out of the box. To reduce the surface
taken away, and right-clicking on a view management SnapShot                             area for attacks, the most dangerous
— which used to allow you to edit it — Dramatic changes to management tools              features are disabled until the DBA
now takes you directly to query builder. won’t leave users completely satisfied.         turns them on. This is a help, but not
A lot of DBAs won’t like this.            proS                                           earth-shattering to experienced DBAs,
   But these annoyances are greatly birroring, improved replication, online
                                           M                                             who mostly have their lockdown scripts
outweighed by richer management            restores, and other features boost            written and don’t find it much trouble
capabilities overall. One place Micro-     availability and speed disaster recovery      to run them on install. The truly signif-
soft has succeeded beyond expecta- b      Dynamic Management Views and                  icant security improvements revolve
tions is in the management of SQL          monitoring improvements provide               around password management and
error logs. SSMS not only displays the     greater visibility into database operations   rights assignment. Native SQL pass-
SQL error logs, but combines them b       Security improvements include sup-            words can now be set to respect Win-
with the Windows NT event logs in the      port for Windows password policies,           dows password policies, which means
same pane, allowing DBAs to see all        more control over schema changes, and         that you can require your SQL pass-
the relevant information in one place.     native encryption                             words to have the same password
While we’re on the topic of combined ConS                                                strength, expiration, history, lockout
logs, this is a good place to mention the banagement Studio suffers from
                                           M                                             threshold, lockout duration, and age
enhancements to Profiler, which now        performance and stability issues              policies as your Windows accounts.
combines SQL trace logs with Perfmon bProfiler not merged with SSMS                      You can even require the user to change
logs. This makes it much easier to cor- bMirroring not yet officially supported          the password at the next log in. Other
relate performance counters with                                                         important security enhancements
specific queries. In fact, it’s very easy                                                include more control over schema
to find exactly what you’re looking for server stats, security, and on and on. So changes (discussed under coding en-
because all you have to do is click on many things you couldn’t see at all be- hancements below) and the capability
the performance spike in the Perfmon fore are now at your fingertips.                    to encrypt data at the column level.
log and it automatically takes you to       In addition, DBAs get ready-made                SQL Server 2005 also brings some
the relevant place in the Profiler log.   reports on detailed database and exciting new features for increasing
   At the heart of SQL’s new man- server stats, including schema chang- availability and smoothing disaster
agement capabilities are the DMVs es, memory and index usage, resource recovery. In the area of HA (high avail-
(Dynamic Management Views) and consumption, blocking, and many ability), Microsoft has introduced
functions, which give admins much others. SSMS relies on SSRS for this database mirroring and made some
deeper insight into database opera- functionality, and I find that it some- important changes to replication. Now
tions. Microsoft used to hide the deep- times bogs down when bringing up a schema changes will be replicated au-
er aspects of the database, but meeting report on a busy server, or on a large tomatically to the subscribers; no lon-
the demands of managing large data- database with a lot of objects. I’m also ger must the DBA run code by hand
bases means giving DBAs more con- a little disappointed that Microsoft to do it. Snapshots no longer need
trol. DMVs provide greater visibility didn’t provide the RDL (Report Defi- to be restarted after failures, but will
into memory, index usage, table stats, nition Language) code. As a result, automatically resume after network

                                                                                            I N F OWO R L D . C O M 	   01.16.06     31
                                                                                                development SnapShot
                                                                                                New tools and constructs give developers
                                                                                                more power without much pain.
                                                                                                b integration allows developers to tap
     errors or other glitches, allowing you        completely restore a multi-terabyte da-       .Net languages for stored procedures and
     to get your HA scenario up and run-           tabase, online restores could mean the        SSIS packages
     ning much faster. Also, logical record        difference between operating produc-          New
                                                                                                b T-SQL constructs and improve-
     replication, which allows you to define       tively and being crippled for hours.          ments to old ones extend power while
     parent and child data that will either           Similarly, you can now fix database        simplifying coding
     be replicated together or not at all,         corruption with less impact on users.         Native XML storage allows developers to
     ensures more consistent HA scenarios          Page-level restore allows you to restore      combine the power of XML documents
     and eliminates logical errors on the          a single corrupted page without hin-          and relational data
     subscriber side.                              dering the availability of the rest of the   ConS
        Mirroring allows you to specify a          database.                                     M
                                                                                                banagement Studio lacks important
     database on another server to be kept                                                       code editing features such as IntelliSense
     in synch with a primary database. Do-         Developing for a New Age                      Unresponsive interface makes
     ing this allows you to automatically fail     Code enhancements can be divid-               Management Studio an inefficient tool
     over to the mirror server should the          ed into two basic categories: those           for running a lot of complex queries
     primary fail, providing SQL Server’s          for developers and those for DBAs.            Some
                                                                                                b DMVs require longer and more
     first zero data loss HA scenario. This        Apart from the .Net/CLR integration,          complex queries than their deprecated
     configuration can be expensive to ap-         CTEs (Common Table Expressions)               counterparts
     plications and networks, so you’ll need       are arguably the most significant for
     to test it thoroughly, but the capability     developers. Besides elegantly solving
     is finally here. Note, however, that mir-     the problem of recursive queries, which      (see “Cool Tools for SQL Coders,” pg. 33).
     roring doesn’t yet qualify as a produc-       I won’t get into, CTEs allow developers      Many developers will also be singing in
     tion feature in this release. Microsoft       to simplify code by using inline query       their Cheerios over SQL Server 2005’s
     is shipping SQL Server 2005 with mir-         definitions in place of views or derived     native XML storage capabilities. As I
     roring turned off, and the feature is         tables. CTEs not only streamline com-        mentioned in my April 2004 compari-
     unsupported.                                  plex queries, but perform the same as        son of the leading relational databases’
        Mirroring performed solidly in my          derived tables, so there isn’t any extra     XML handling capabilities (infoworld.
     tests, handling fail-overs of a 100GB         overhead to implementing them.               com/2318), SQL Server 2000 was lim-
     database without a hitch. The moni-              Another important enhancement:            ited to “shredding” XML into relational
     toring around mirroring, however, is          The TOP clause, which allows you to          data, a cumbersome process that fails
     still very primitive, consisting of only      define that only a certain number of         to preserve XML hierarchies. Now, you
     a small number of DMVs and perfor-            a result set is returned (such as the        can import XML into a column and not
     mance counters, so it’s not especially        top 10 salespeople by revenue in the         only perform native XML queries, but
     easy to find out how much data has            United States), can now take a param-        also index it for fast retrieval.
     been replicated, how far behind the           eter as an argument. I know it doesn’t          DBAs haven’t been left out of the
     secondary system is from the primary,         sound like much, but this is one of          coding revolution. A number of cod-
     or the cost of the replication. The dif-      those cases where a relatively small         ing changes give admins management
     ficulty in gaining visibility into the pro-   change can make a huge difference in         capabilities from the command line
     cess may fray some nerves.                    development effort. In short, instead of     that are not accessible through Man-
        As for disaster recovery, SQL Server       having to include a static value (TOP        agement Studio. One in particular,
     2005 slashes the downtime necessary           10, for example) in the code, you can        DDL (Data Definition Language), will
     when restoring a backup. The new              now include a variable, allowing users       not only change the way DBAs do their
     online restore capability allows users        to report the TOP 10 or TOP 100 (or any      jobs but will also help their CEOs sleep
     to begin working in the database as           number they like) on the fly.                much better at night. DDL triggers fire
     objects become available. With SQL               Other goodies for developers include      whenever changes to the database
     Server 2000, the entire database must         ranking, random data sampling, pivot/        schema occur, and they can be defined
     be restored before it can be made avail-      unpivot, synonyms, much richer error         on pretty much any object type in the
     able to users. Given the time it takes to     handling, and code security context          server or database, helping to make

32      I N F OWO R L D . C O M 	   01.16.06
                          Cool Tools for SQL Coders
                          A number of code enhancements in SQL Server 2005 bring greater functionality and ease to developers.

                          Common Table Expressions You can simplify code by using an inline query definition in place of views or derived tables.
unwanted changes a Dynamic TOP You can use parameters (no longer just static values) as arguments in TOP clauses.
thing of the past.
                           Pivot/Unpivot You can shift the rows of a table to columns and back again.
   Indexing is always
a volatile subject in Ranking You can all sequence information to your data sets.
any busy production Random data sampling You can take samples of large tables to get an idea of data distribution.
environment. DBAs Synonyms You can rename objects with friendlier names.
need to maintain Code security context You can run code as a specified user no matter who calls it.
the database; pro- OUTPUT clause You can see what changes have been made during Delete and Update operations.
duction managers
                           .WRITE on Update You can perform chunked updates in large text objects without dealing with pointers.
don’t want to inter-
rupt operations. On- APPLY join operator You can apply results of a user-defined function at the column level.
line indexing allows INTERSECT and EXCEPT Similar to UNION, these allow you to find rows that result sets have in common or in difference.
DBAs to create or
rebuild indexes without taking the da- you and these sources and shows you achieved its goal to bring data mining
tabase offline. The index is built in par- the view of the data you request. UDM into the hands of nonprofessionals, but
allel to the table and then applied once brings several important changes to it is far easier than using the third-par-
it is created. Of course, you have to be the way you will operate with SSAS. ty mining tools.
aware of resources, but this feature will One is that now you can have multiple                       Tying the BI package together is
greatly increase availability for shops fact tables, and in fact, you have your SSRS. With this new release, Micro-
that have to be up 24/7. DBAs can now entire relational schema available to soft has taken away much of the pain
also control the level of locking used by you. UDM eliminates the need for vir- of writing and distributing reports.
indexing operations.                           tual cubes and virtual dimensions, pro- Perhaps the most important improve-
                                               viding not only increased functionality ment, Report Builder, is a new Web
Not Your Father’s OLAP                         but a much easier model with which interface that allows end-users to
SQL Server 2005’s suite of BI tools to work.                                                       write and publish their own reports.
includes SSAS (SQL Server Analysis                One of my absolute favorite new Admins publish a report model, which
Services), SSDM (SQL Server Data Min- features is proactive caching. This is basically a definition of the data they
ing), and SSRS (SQL Server Reporting mechanism allows you to set up met- want the users to be able to write re-
Services), all of which are finally de- rics that determine when your data ports against, and users step through
signed to work together to answer your gets refreshed. There are several ways the wizard interface to create what-
intelligence needs. SSAS is looking to do this, but one of the better ones ever reports they like. In a way, these
pretty good these days, thanks to many is to define your cube to be refreshed reports can be richer than tradition-
usability enhancements. The new Busi- when the base data changes. The data al SSRS reports because they come
ness Intelligence Wizard comes loaded is refreshed at the partition level, and with infinite drilldown already built
with pre-canned solutions to common there are many options for balancing in, which is something that designed
problems. Currency conversions, semi- performance and latency.                                     reports simply don’t have.
additive measures, and time-based                 SQL Server Data Mining has grown                    Report Builder’s controls allow
calculations are just some of the situa- from what seemed like an experiment developers to embed reports in
tions that the wizard handles.                 into a full-fledged application. Where- applications. Along with this desirable
   SSAS has had one very important as SQL Server 2000 had two mining capability, Report Builder also includes
structural change — the UDM (Unified algorithms — simple clustering and support for custom controls that allow
Dimensional Model). It’s hard to fully simple decision trees — SQL Server developers (most likely third-party
grasp the UDM until you work with 2005 has 10. It also has 25 new visual- vendors) to extend SSRS functionality.
it, but it basically provides a bridge izations and visual query editors. All of                      SSRS     also      includes   lesser
between clients such as Excel to any these tools make it much easier to de- enhancements that end-users will
number of heterogeneous data sources. velop and work with mining models. I find helpful. One of these is multi-
Or put in simpler terms, it sits between wouldn’t say that Microsoft has exactly valued parameters, which allow us-

                                                                                                      I N F OWO R L D . C O M 	   01.16.06          33
     ers to pass more than one                                                                           need true 24/7 availability, rely
     value (multiple sales regions,                                                                      heavily on DTS, or need to com-
     for example) inside a single                                                                        ply with Sarbanes-Oxley audits.
     parameter. This functionality                                                                         If you’re at the point where
     previously relied on passing in                                                                     you just can’t keep pulling
     delimited elements into a single Analysis Services, which now taps Visual Studio for development, downtime for maintenance, or
     parameter, and then parsing sports a cleaner interface that will speed development of report- for any other reason, then this
     them out into single entities ing and business intelligence applications.                           upgrade shouldn’t wait. SQL
     in the database. Other nice                                                                         Server 2005’s replication en-
     additions are interactive sorting,                                                          hancements, online reindexing, partial
     which allows users to change the sort- BuSIneSS IntellIgenCe                                restores, and (soon) database mirror-
     ing criteria of the columns in their re- SnapShot                                           ing will offer welcome relief.
     ports as they see fit, and a calendar Analysis Services, Data Mining, and                     If you are currently pushing DTS to
     picker, which allows users to choose a Reporting Services are finally designed to           the limit, converting to SSIS will ulti-
     date from a pop-up calendar instead work together.                                          mately streamline operations but may
     of having to type it.                      proS                                             not be as easy as you’d like. Proper
        For developers, SSRS works directly b Unified Dimensional Model makes
                                                 New                                             planning will be essential. The best
     with SSAS and SSIS, using them both         Analysis Services a bridge to any number        way to go about it is to put a dedicated
     as data sources. This is a huge step for-   of heterogeneous data sources                   SSIS server in place and upgrade your
     ward because current SSRS users can b mining matures with richer set of
                                                 Data                                            packages one at a time. This method
     follow processes already in place to        mining algorithms and visualizations            will give you an opportunity to upgrade
     produce any number of reports in any b     Reporting Services now allows end-users         and test at your leisure.
     number of ways. The SSIS integration        to create their own reports and develop-          If you’re concerned about compli-
     alone gives you the capability to pull      ers to embed reports in applications            ance, the new security features and the
     in data from multiple sources, includ- bComplex cubes perform well and reliably             DDL triggers will whip your database
     ing those merged from different data- ConS                                                  into shape very quickly. Here again,
     bases, Web services, RSS feeds, SSAS, banagement Studio supports writing of
                                                 M                                               though, implementing password
     and data-mining repositories. The pos-      BI code but not reports                         policies can cause some grief in your
     sibilities are limitless.                  b new interface presents a slight learn-
                                                 The                                             environment, so you’ll have to plan
                                                 ing curve, especially for advanced features     appropriately.
     Wish List                                                                                     For shops without these pressing
     What got left out of this release? Many                                                     problems, SQL Server 2005 offers plen-
     developers and DBAs were looking SQL DBAs have grown to live with and ty of other reasons to consider it. From
     forward to IntelliSense for T-SQL cod- work around. Finally, Microsoft still management to development, just pick
     ing. Many were also hoping for some hasn’t offered an answer to Oracle’s your woe and chances are SQL Server
     sort of centralized code repository. grid solution, which allows you to clus- 2005 will help alleviate it.
     We’ve still got templates, but those ter multiple database servers for load                   SQL Server 2005 is an excellent
     typically reside on clients; DBAs and balancing and fail-over and manage release that will gain Microsoft some
     developers can’t easily pull them from them as a group. That’s the last major real respect in the high-end database
     a centralized online resource. SSIS is advantage Oracle can still claim over market. SQL Server’s tools now lead
     a huge improvement over DTS, but it SQL Server.                                             Oracle and DB2 in pretty much every
     still isn’t an actual ETL server, meaning    Who should upgrade and when? area — management, monitoring, and
     it still uses the resources of the server Shops who should look at an upgrade especially development — and of the
     that kicks off the package. This has right away — assuming their applica- three vendors, Microsoft seems to be
     been a source of grief for many DBAs, tions have been certified for the new the most concerned about improving
     although the current architecture has version and migrating won’t void their them. Maybe those other guys should
     advantages too, and it’s something support contracts — are those that start hitting the weights. i

34      I N F OWO R L D . C O M 	   01.16.06

To top