SQL Server 2005 Launch event

[fuzzymallard]

I attended Microsoft's Technet launch event for SQL Server 2005 today. It was an excellent event, hosted by an excellent speaker, Michael Murphy. Notes about the features I found most valueable are inside.

• Availability is a little better this time around. If one of you database files gets busted, you can bring up the remaining database objects so that the DB is partially available while you're doing the restore. You may also reindex now with the server still available...it will all happen in a temporary table, and and transactions done since the reindex operation started will be factored in. Trick. Database mirroring is released, but unsupported, because it "don't work". But it looks like a very promising technology. You have a primary and backup SQL Server, and a third machine called a witness. If the connection dies between the secondary and primary, the secondary server asks the witness if the primary is still up. If the witness can't connect to the primary server, it will initiate failover, and any clients would now point to the secondary server while the admin gets the primary up again. What's really nice, is that if the secondary server isn't being used for reporting purposes, you do not need a license on it. The witness machine can just run SQL Server Express (free).
• Microsoft cares about security a bit more with this version. The product ships with most features locked down. Permissions are much more fine-grained, giving administrators the freedom to let IT guys and developers be able to get work done without having to grant them godly access to the DB. Most importantly, binding between a database user and the schema is gone! The owner's user name is no longer part of the object name, preventing annoying work that needs to be done when the DBA gets a better job offer and leaves the company for a better-than-4.8% raise. Lastly, metadata is now secure...a database user cannot see objects that they have no permission to manipulate. A central tool called the Surface Area Configuration Tool puts all security features together at the admin's fingertips.
• IIS blows goats, so its nice that the new SQL Server comes with built-in web services. Even though you'll probably be running this on Windows 2003 Professional, leave that IIS service off.
• Analysis tools now make use of Windows' built-in native performance monitoring and log viewer. I have no idea why they could not have done it this way in the first place. The Database Engine Tuning Advisor has been added, which takes in sample query data and spits out some recommendations on how to tune your DB.
• Databases often get hosed by nasty processes that do impure things, causing the service to hang, making the database unavailable. To prevent from having to restart the service (or even the whole machine), the SQL command dedicated admin connection was created. A small high-priority service sits connected with the SQL server service. When things get ugly, the administrator can wander on in, see what process is raping his server, and kill it, so that the DB will spring back to life.

Hope this information was useful to somebody. Microsoft was kind enough to distribute media and licenses to the software for us to play. However, they did not hand out copies of Windows 2003 Professional. Copies are selling on eBay for around $700, so the cost-of-entry is still quite a bit high. I've been thinking of getting a 64-bit system sometime in the future, so maybe I can buy one with a license.