Dev Chat

I spent yesterday at the JHB SQL Server 2008 Dev Chat. Most definitely a day well spent. The two presenters (Peter Willmot and Eben de Wit) were both knowledgeable and entertaining and the material covered was interesting.

The sessions covered some of the larger changes in SQL Server 2008.

First up was a brief look at some of the changes on the administration side. The first topic was around the transparent database encryption. this is a fantastic feature for people concerned about the security of the database files themselves. The database is encrypted on disk, decrypted in memory, and any attempt to attach the encrypted files to another instance of Sql will fail, unless the certificate that was used to encrypt the database is present on the second instance.

One major warning on that. Loose the certificate, say goodbye to your database.

Second of the admin-related changes discussed was the DMF (Policy-based management). This is a fantastic new feature and there are two nice ways of using it.

You can define a policy on a server. It’s stored in SQL and exposed in Object Explorer in a new folder under Management. The policies can be assigned on just about any object within the server (database, login, table, user, etc) and can check any one of a large number of properties of those objects. The policies can either be checked on demand or on a schedule (in which case any violation of the policy is logged for the admin’s attention) or the policies can be enabled and any change to objects will be checked against the policy. Violations can be logged or prevented.

So, it’s possible on a production server to have a policy stating that all user databases will be in full recovery mode and set the policy to Enabled, Prevent violation. If anyone tries to change the recovery mode, an error will be raised and the change will not be done.

similarly, it’s possible to have a policy that checks all tables in a specific database to see if they have a primary key. The policy can be set to run once a week and log any violations.

The second way a policy can be used is to have it stored in the file system, typically of a client machine. An admin can then run that policy against a number of servers in a server group, checking all of them in one go.

That’s going to be a massive time saver for some tasks. It’s also going to reduce the need for complex DDL triggers all over the place.

Another change that was discussed, that’s more for admin than developers is the new data compression. There are two forms, and I’m not going to go into massive detail here. Firstly there’s row compression. This is probably most useful when there’s string data in the rows. The compression works by identifying patterns of data that appear within each row and replaying those repeating patterns with a pointer to one place where the pattern is stored. The second form of compresion is page-level. It looks like it works similar to row, but the repetition is checked over the entire page. Nice for data that’s got lots of (large) foreign key columns in it. The two, used together or separatly, have the potential to drastically reduce the size of a data file, at the expense of higher CPU usage to decompress the data when its queried.

The second section was all about the enhancements to the BI offerings. Some of the new stuff in reporting Services looks very cool. With the new Reporting services no longer needing IIS, I can see a greater usage of it. For some reason IIS gives corporate security admins a fit. <g>. The inclusion of the Dundas charts and Word export are both very welcome.

The main focus of the day was on the new features for developers. No surprise really, it was aimed at the developers. A lot of focus was on some of the new data types available in SQL 2008.

The new date and time data types are very welcome. There’s a new datetime data type that can take dates right back to 1 AD and has an accuracy of 100ns. No more 3ms rounding issues. There’s a date data type that has no time (I can hear a lot of people cheering) and a time data type with no date. The datetime offset, with it’s time zone support is going to be very interesting.

The filestream ‘data type’ resolves that old question of where you store images or movies. In the database (so it can be backed up and so that DB security affects it) or in the file system (which is more optimised for large files that SQL server is). The answer is both. Define a varbinary(max) column as FileStream and the contents of that column are put in the file system as files. However they’re accessed through SQL, backed up with the database and subject to all DB security, auditing and the like. Very much the best of both worlds.

The new hierarchy data type promises to make it easier to store and retrieve hierarchical data in a table. I’m going to have to play with it a bit more, as I don’t yet understand how it works.

The spacial data types got a lot of attention. It’s another area I need to look into before I understand. Essentially, there are now data types for points, lines and polygons and a whole lot of in-built functions to calculate area, intersection, distance and the like. I’ve also heard about spacial indexes. They weren’t mentioned at the Dev Chat, and I know no details about them.

The spacial stuff looks very pretty and it’s very likely going to massively useful in a number of situations.

All in all some nice stuff. I’m going to have to get my hands on the latest CTP and play.