Wanderer.... wandering through SQL and wondering about it...

So I was at the Full-day SQL Bits conference at Microsoft in reading, as you would do on a nice Saturday… And I must say I was pleasantly impressed by the content and the speakers. 4 different venue – 6 sessions in each venue, giving us 24 different sessions across 4 Tracks (5 if you count vendor): ”DBA, DEV, BI, SQL 2008, and Vendor”. They had a draw for an x-box 360 at the Quest stand, but even going over to chat to the account manager, who happens to be the Tesco Quest account manager, didn’t seem to improve my chances. C'est la vie …

                I ended up predominantly attending SQL 2008 and SSIS sessions, and there are some really nice things coming in SQL 2008 – especially if I can make sense out of my notes. I’ll list the sessions, the speaker and any details/nuggets I can. Some of the speakers were letting us in on ‘under-the-covers’ stuff – things that will only be in the next CTP (5), or else the one after. Well, without further ado…

                SQL Server 2008 for the DBA – Keith Burns:

                After I got used to Keith’s accent, I really enjoyed his chats. He’s got insight in to a lot of the ‘real things’ happening, and gave a good chat. Here’s what I can remember/decipher from my notes:

·         SQL 2008 will only be GOLD in Q2 2008. 28 Feb 2008 is Marketing launch day. SQL 2008 will be the ‘poor kid’ of the launch – much of the fuss will be about Longhorn and Orcas (I think Vis Studio 2008 is Orcas – anyone?). looks like the launch tour will be in the UK about 2-3 weeks after the big launch.

·         In SQL 2005, we have column encryption – but we cannot index encrypted columns. SQL 2008 will bring database encryption as an option, and we will be able to index tables in an encrypted database (probably wouldn’t have been shipped otherwise – can you imagine a DB without indexes?)

·         The ‘pluggable CPU’ that will be an option in SQL 2008 – Keith thinks this is going to be more useful where people are making use of virtualizations, and can dynamically ‘add’ CPUS to a virtual server. I can see where we might use that…

·         DB Mirroring will be enhanced. Apparently, you will be able to use a DB mirror to repair page corruption on the source or primary. Interesting…

·         There’s been a lot of talk about DMF (declarative management framework – the “policy administration idea”). Not going to go into that, but here are some notes:

o   a lot of default policies will be shipped – disabled – you can choose what you want.

o   Also, you can choose how to apply policies: ALL DBA, USER DBA, SYSTEMS DB’s, Specific DB. Be careful what you choose – for example you can easily make a system DB come up as ‘non-compliant’ – Kieth showed us that be enabling a “no dbo schema” policy on all databases

o   DB policies will NOT stop a DB being created (for example a policy that says Logs on F: and Data on G: - a creation of a DB with everything on G: will work, but will mark the database as non-compliant

o   TB policies WILL stop a table being created – so the “no dbo schema” policy should stop users creating dbo.table

o   Policies can be exported and imported – they are XML files

o   Policies will covers most (maybe all) DB pbjects (although I doubt CLR assemblies internals)

o   Resource Pools defined in DMF create perfmon counters – have a look at them

·         Performance Studio will come with SQL 2008. This is going to be great for people who don’t have a tool like Quest Performance Analysis.

o   Basically, most DMV data can now be saved into a Performance Warehouse database, which will allow you historical analysis, and reporting.

o   You can schedule the capture of DMV data.

o   Some statistics could be quite ‘heavy’ on the db servers-  in the order of running a profiler trace. Collection sets will be ‘lighter’

o   You can define duration to keep data – so if will self-archive/housekeep.

o   May NOT be able to collect from SQL 2005 – he didn’t seem sure, but felt it was likely this would only work on SQL 2008 servers

o   You will be able to source data from:

§  T-SQL (selecting DMV data)

§  SQL traces

§  PerfMon

§  In the future maybe WMI events…

·         Data Compression

o   Similar to vardecimal (which came with SQL 2005 SP2 and apparently gave especially CUBES a huge saving on space), there will be VAR types for most data – so VARINT for example. The usage of these data types is what they are referring to as ROW compression

o   PAGE compression is more ‘traditional’ in that it is an algorithm compressing data before storing page on disk, and decompressing when reading. They expect that, especially for IO constrained systems, this may give PERFORMANCE improvements, because the cost of the CPU time to compress will be less that the cost of the IO time saved by ‘getting’ 1 page, and retrieving , for example, 4-7 pages – i.e. saving several page IO’s. This will bear testing, and naturally depends on the data being stored – blobs may not compress well, but I think I can see significant benefits for users of XML data type!

o   Compression can be done at partition level, so if you have 1 active partition, and then 11 old partitions (for example in a month of year rolling partition model), you could compress the older partitions – for example the oldest 6.

o   No numbers available, but Keith’s testing shows up to 7:1 compression ratios for BACKUP compression

o   You can use BACKUP COMPRESSION on COMPRESSED database, and because they are different algorithms, you may still see a gain.

·         CTP 5 – expect it end of October. Not sure if it will be released as a VHD again. Apparently the install process will change significantly

Well, that’s a LOT – that all from 1 session – but I think my notes get worse as the day wore on – so I’ll probably wrap this up next week.