• Article

TechNet UpdateAdvancements in SQL Server 2005

Kai Axford

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:

Celia.Customer

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

Celia.Customer

tables, views, and queries (not to mention any custom apps making the call) to her replacement Melanie, so the end result will now be:

Melanie.Customer

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

Sales.Customer

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).

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.

Get Ready for Microsoft SQL Server 2005

Microsoft® SQL Server 2005 is the next-generation data management and analysis software designed to deliver enterprise-level scalability and availability, and bring security enhancements to enterprise data and analytical apps while making them easier to create, deploy, and manage. Microsoft Learning resources can help you take immediate advantage of new functionality in SQL Server 2005, including features for enterprise data management, developer productivity, and business intelligence.

Free Assessments

The online assessments help you analyze your current skills, and provide you with a learning plan that recommends books, training, and exams to meet your goals in using SQL Server (see Discover What You Know Today).

  • Introducing Microsoft SQL Server 2005 for Database Administrators
  • Introducing Microsoft SQL Server 2005 for Database Developers
  • Introducing Microsoft SQL Server 2005 for Business Intelligence Developers

Instructor-Led Training

Two hands-on courses are available through Microsoft Certified Partner for Learning Solutions (CPLS).

2733: Updating Your Database Administration Skills to SQL Server 2005

2734: Updating Your Database Development Skills to SQL Server 2005

Free E-Learning

Whether you are interested in database administration, database development, or business intelligence in SQL Server 2005, you can access Microsoft E-Learning to help you get up to speed on the newest features. Microsoft E-Learning courses are an effective way to learn on your own schedule.

The nine courses available for SQL Server 2005 provide an in-depth online training experience that includes hands-on virtual labs and offline functionality. Courses are free until November 2006 (see Get Ready for Microsoft SQL Server 2005).

For Database Administrators:

2936: Installing and Securing Microsoft SQL Server 2005

2937: Administrating and Monitoring Microsoft SQL Server 2005

2938: Data Availability Features in Microsoft SQL Server 2005

For Database Developers:

2939: Programming Microsoft SQL Server 2005

2940: Building Services and Notifications Using Microsoft SQL Server 2005

2941: Creating the Data Access Tier Using Microsoft SQL Server 2005

For Database Business Intelligence Developers:

2942: New Features of Microsoft SQL Server 2005 Analysis Services

2943: Updating Your Data ETL Skills to Microsoft SQL Server 2005 Integration Services

2944: Updating Your Reporting Skills to Microsoft SQL Server 2005 Reporting Services

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 kaiax@microsoft.com

© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.