Advancements in SQL Server 2005
As a technology guy at Microsoft, I’m actually excited about this whole SQL Server™ 2005 and Visual Studio® 2005 thing. Now know that there will be plenty of fanfare associated with the release, but fanfare doesn’t help you when you’re in the server room at 2:00 AM trying to do a migration. I’m here to cut through all the glitter and hype, to tell you all about the improvements in SQL Server 2005.
Makin' the move Already got SQL Server 2000 or SQL Server 7.0, but want to make the move to the newest version? The new Upgrade Advisor is there to save you. The Upgrade Advisor can tell you the exact order of the upgrade procedure. Do you need to know if you should rewrite any ActiveX® control code for your Data Transformation Service (DTS) packages before upgrading? Got any old references to undocumented system tables? The Advisor can tell you. The best part of the Advisor is the reporting capabilities. You can save out an existing report, check boxes as Resolved as you work your way through the provided checklist, and then rerun the report to see where you stand.
Are you gonna use that? I’m passionate about security, and the new version of SQL Server is really impressive in its solutions for so many security issues. One of the things we always recommend is to turn off all unneeded services in order to reduce the attack surface. In SQL Server 2005, you can use the Surface Area Configuration Tool. It will zip through your entire installation process and help you with disabling or enabling many of the Database Engine, Analysis Services, and Reporting Services features. It even explains why.
The database admin can’t touch me! One of the problems I had with SQL Server 2000 was that it didn’t allow me to apply my domain password policies to SQL Server logins. I’ve watched users make the simplest passwords imaginable—not a wise policy in any environment. In SQL Server 2005, you can tie the logins back to your domain password policy. No more users using "password" to log in. You can enforce the Account Lockout policies on users as well.
Going our separate ways
DBAs have long been asking us to fix the problem of user schema separation. Why does it matter? Previously, if my user, Celia, created her own table called Customer, the name of the table would be:
Anyone in the organization, given proper credentials, could write a statement such as:
SELECT CustomerID FROM Celia.Customer
Now what happens when Celia gets terminated by HR for using poor passwords (or any other infraction)? The DBA has to go in and change ownership of all the
tables, views, and queries (not to mention any custom apps making the call) to her replacement Melanie, so the end result will now be:
If you aren’t quick enough to change the ownership, queries and views start to fail. SQL Server 2005 does not use the name of the object owner in the object’s namespace. So instead you’d see something like
when Celia created the table initially, since Sales was her default schema. The object namespace remains the same irrespective of changes in ownership.
Five-year tune-up The next really nice thing we’ve fixed in SQL Server 2005 is the Index Tuning Wizard. It’s been completely redesigned and given an even sexier name: Database Tuning Advisor. It is one of my most favorite things about this new product. This Advisor is smart! You can have the Tuning Advisor look at all your Physical Design Structures (indexes, indexed views, and so on) partitions, and you can even limit how long it will run. After the Tuning Advisor gives your database the once over, it’ll print out a report on the things you can do to improve the performance of the database you selected. It handles tasks such as dropping older indexed views, creating new indexes, and repartitioning the tables. Now, the best part: clicking on the results gives the exact T-SQL statement for use in a new query. I can also select the recommendations I’d like and set them up on a schedule to execute when I’m busy. No longer do I need to pour through a T-SQL book trying to figure out the exact command I need to perform a job. It even gives me the percent of improvement I’ll get by making the recommended changes.
Mirror, mirror... The last thing I want to share with you is something called "database mirroring," and yes, it works just like it sounds. Database mirroring creates and maintains a copy of a database on a second instance of SQL Server 2005. You don’t need to buy a bunch of cluster-aware disk arrays to get this to work. All you need is another instance of SQL Server 2005 somewhere in the domain (but not necessarily even in the same room—geography is not a restraint for this solution). Any application that makes a transaction against the principal SQL Server, is logged to the mirror in one of three ways. Transactions can be logged in Asynchronous Mode, where the principal writes to the mirror as time allows; Synchronous Mode, where it waits until it can commit the transaction at both servers before committing the transaction at the principal server (which will increase latency); or, finally, Synchronous with Automatic Failover, which requires a third instance of SQL Server 2005 (it can even be SQL Server 2005 Express Edition) to serve as the "witness, " which will immediately notify everyone that the principal is down, and the mirror needs to assume that role. Scalability and availability have never been easier (or cheaper).
Kai Axford, CISSIP, MCSE-Security, has been with Microsoft for the past five years as a support engineer and TechNet speaker. He is currently serving as a Lead Security Presenter and speaks to thousands of IT pros at live events. Reach Kai at firstname.lastname@example.org
© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited
I want to leave you with an invitation to join us at a local TechNet Event near you. As you’re reading this, the TechNet team is out in force, demonstrating all the things you just read about here. Our goal is to help you learn all you need to know about SQL Server 2005. Please check out the event listings at www.technetevents.com
. If we’re not going to make it to your town, then check out our webcast series, SQL Server 2005