SQL Server bulks up
Microsoft's beefy remake brings hefty new capabilities and a slew of new tools to master
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.
SQL Server 2005 also brings some exciting new features for increasing availability and smoothing disaster recovery. In the area of HA (high availability), Microsoft has introduced database mirroring and made some important changes to replication. Now schema changes will be replicated automatically to the subscribers; no longer must the DBA run code by hand to do it. Snapshots no longer need to be restarted after failures, but will automatically resume after network errors or other glitches, allowing you to get your HA scenario up and running much faster. Also, logical record replication, which allows you to define parent and child data that will either be replicated together or not at all, ensures more consistent HA scenarios and eliminates logical errors on the subscriber side.