SQL Server bulks up

Last but not least, the new .Net integration releases SSIS from DTS’s dependence on the limits of VBscript and moves serious coders into the richness of VB.net. For this reason, file and string manipulations, math operations, and Win32 calls are much easier and faster.

Free IT resource Open Source Business Conference (OSBC) May 22-23, 2007 Sponsored by OSBC Free IT resource Virtualization Insights from Top Experts - Learn how virtualization gets real! Sponsored by Dell Return to special report DOWNLOAD PDF Click here to download InfoWorld's special report Microsoft SQL Server Microsoft SQL Server 2005 Microsoft, microsoft.com Excellent  9.1 criteria score weight Manageability 9 25% Performance 9 25% Availability 9 20% Scalability 9 20% Value 10 10% Cost: Standard Edition starts at $5,999 per processor or $1,849 with 5 CALs. Enterprise Edition 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 development, integration, management, and BI. Companies 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, although not yet officially supported, will provide automatic fail-over for log shipping scenarios. About our Reviews and Scoring Methodology

As you would expect, the increased power and flexibility of SSIS come at the price of increased complexity. SSIS presents a learning curve that can seem daunting, but after you learn how to use it, SSIS will change the way your company thinks about its data. Systems that couldn’t communicate before are now perfectly integrated and have the full power of .Net behind them. Complex data load operations into warehouses and disparate systems will take a fraction of the time to build, execute, and support. Other complicated tasks can also be performed much faster, which means deadlines will be met more easily and projects won’t be held up because of the limited functionality of DTS and the way operations have to be taped together.

Maximum DBA
Microsoft has also made some dramatic changes to its management tools, replacing Enterprise Manager and Query Analyzer with the new SSMS (SQL Server Management Studio). SSMS serves as both the database command line, with which you perform tasks such as creating databases and managing user accounts, and the tool used to write database code. SSIS and Profiler were left out of this merger.

These changes won’t leave users completely satisfied. For starters, doing normal query tasks, such as viewing execution plans and even just running queries, is much slower. Screens can become quite unresponsive while queries run, and it takes much longer to pull up execution plans, sometimes causing the window to seize up. Microsoft also changed the layout of the execution plans, so larger ones are much harder to read. Your favorite keyboard shortcuts have been taken away, and right-clicking on a view -- which used to allow you to edit it -- now takes you directly to query builder. A lot of DBAs won’t like this. 

But these annoyances are greatly outweighed by richer management capabilities overall. One place Microsoft has succeeded beyond expectations is in the management of SQL error logs. SSMS not only displays the SQL error logs, but combines them with the Windows NT event logs in the same pane, allowing DBAs to see all the relevant information in one place. While we’re on the topic of combined logs, this is a good place to mention the enhancements to Profiler, which now combines SQL trace logs with Perfmon logs. This makes it much easier to correlate performance counters with specific queries. In fact, it’s very easy to find exactly what you’re looking for because all you have to do is click on the performance spike in the Perfmon log and it automatically takes you to the relevant place in the Profiler log.

At the heart of SQL’s new management capabilities are the DMVs (Dynamic Management Views) and functions, which give admins much deeper insight into database operations. Microsoft used to hide the deeper aspects of the database, but meeting the demands of managing large databases means giving DBAs more control. DMVs provide greater visibility into memory, index usage, table stats, server stats, security, and on and on. So many things you couldn’t see at all before are now at your fingertips.

In addition, DBAs get ready-made reports on detailed database and server stats, including schema changes, memory and index usage, resource consumption, blocking, and many others. SSMS relies on SSRS for this functionality, and I find that it sometimes bogs down when bringing up a report on a busy server, or on a large database with a lot of objects. I’m also a little disappointed that Microsoft didn’t provide the RDL (Report Definition Language) code. As a result, you can’t change the layouts of the canned reports, nor can you add your own reports or set up subscriptions, as you normally can in SSRS.  You can manually export the reports to Excel or PDF, however.

One key design goal for SQL Server 2005 was tighter security out of the box. To reduce the surface area for attacks, the most dangerous features are disabled until the DBA turns them on. This is a help, but not earth-shattering to experienced DBAs, who mostly have their lockdown scripts written and don’t find it much trouble to run them on install. The truly significant security improvements revolve around password management and rights assignment. Native SQL passwords can now be set to respect Windows password policies, which means that you can require your SQL passwords to have the same password strength, expiration, history, lockout threshold, lockout duration, and age policies as your Windows accounts. You can even require the user to change the password at the next log in. Other important security enhancements include more control over schema changes (discussed under coding enhancements below) and the capability to encrypt data at the column level.