My SSAS training from Pragmatic Works actually completed on Wednesday. However they added an extra optional day to cover some of the new features coming soon in Denali. Brad was replaced by Roger Doherty from Microsoft. Roger started us off with an overview of Denali, showing the timeline of SQL back from the beginning, way back in the Sybase days, to the present and beyond.
Brian took over and we saw what was new for DBA’s The big feature here is “Always On”, a way to keep your servers up. It’s kind of like a combination of mirroring and transaction log shipping. But it’s more flexible – you can use it for all your databases in on shot, not one at a time. you can have up to 4 secondary servers. Compression and encryption are build in. It integrates right into Management Studio through availability groups, and it works with both filestreaming and replication. Best of all, you can read off of and backup the secondary server. And you can use multi subnets, great for setting up in a remote location.
Another advantage of using Always On is the use of contained database. When you contain a database it removes all dependencies on other databases. You won’t need to create logins inside the master database anymore; each database will have its own accounts. No more copying logins to the failover server. You set the instance to allow containment, then on each user database set the containment type to partial. Partial and none are the only options for now. There are a few gotchas, though. You won’t be able to use linked servers or fully qualified names to other databases inside the instance. And you’ll still need msdb for Agent jobs.
There’s a new type of index, called columnstore. This allows indexing on a column level rather than a row level. It’s a non-clustered index, but you’re limited to 1 per table. And it’s read only; you shouldn’t put it on table with lots of inserts. It’s meant more for large tables inside a data warehouse. But it can be disabled and rebuilt during data loads. However it will take about 1 1/2 as long as a regular index to build. But the columnstore index is so efficient that you can eliminate other indexes. Brian showed some demos and the difference in the time it took for a query to run with and without the new index was huge – 9 minutes down to seconds. It will use less IO but it will increase the buffer count.
SSIS wasn’t left out, there’s new features and improvements here too. First of all, Undo now works. Copying a task and pasting it puts the object where you click, not somewhere at the bottom. You still deploy projects, but that’s changed. You can’t deploy individual packages through BIDS anymore. Deployed projects don’t go to msdb anymore, they go into a new database. You create folders inside the database for the packages and permissions can be handled on each level. There’s now a versioning option where you can rollback to a previous release. And there’s now reports showing execution history.
I’ve left out a lot. There’s the new Tabular module in SSAS. There’s the file tables improvement to fliestreaming. There’s the new THROW keyword to replace the RAISERROR. I haven’t mentioned data quality services yet, or “Juneau”, or “Crescent”. Frankly I’m still absorbing it all. There was a ton I learned in the last three days and I’m still a bit overwhelmed. But I plan on coming back to the new features in the next week or two, creating my own demos to share.
Until then.
