SQL Relay Notes and Queries

  • Article

I wanted to something different for SQL Relay last week as this is a community driven event and I didn’t want to cover areas that the other experts on the Relay team would cover. This left some gaps and I have had several follow up questions via twitter and e-mail which I want to cover in this post.

Columnstore

I covered several uses of the in memory column based technology in my session 2 of which are in SQL Server 2008 R2 (PowerPivcot for Excel and PowerPivot for SharePoint) and two are new for SQL Server 2012: Tabular Analysis Services and Columnstore indexes.  Columnstore indexes are part of the database engine rather than part of Analysis services and are created much in the same way as for other indexes:

CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_CS_FactProductInventory]

ON dbo.FactProductInventory

(

ProductKey, DateKey, UnitCost, UnitsIn, UnitsOut, UnitsBalance

)

They can speed up a query  by 10-100x compared to a normal index however you can’t update a table with a Columsntore index on you have to disable it and then re-index after you have made your changes.  There a good guide on its usage on the SQL Server wiki.

Security

I would like to have covered of contained database security which as the name suggests means that the login credentials of users get stored in a given database rather than in master and for SQL Server authentication this means that the password is in there as well.  As well as making the database more portable a user who just has their credentials in that database can’t change to another database and have little or no permissions outside that database.  Therefore when you connect to a contained database you need to specify the database as well as the server/instance (for example in management studio)  I can see there being huge advantages of this approach for developers wishing to make applications cloud ready and ISV’s can make deployments of their applications more easily.

I also got asked about crypto enhancements in SQL Server and these are:

  • Create certificate from bytes
  • 4K certificates supported for import
  • SMK/DMK default to AES256
  • Key backups encrypted with AES256
  • SHA2 (256 and 512) support
  • Password hashes use SHA512
  • RC4 deprecated

LocalDB

I have to admit to glossing over this as I am not primarily a developer and I rarely play with SQL Server Express.  LocalDB is an installation option in SQL Express and allows for a cutdown version of SQL Server to run against a local database. Note LocalDB doesn’t support Filestream and cannot be a merge replication subscriber and for more on this refer to LocalDB in MSDN

Power View

I have now checked and there is currently no drill down capability in Power View. If htis is important to you r business you can register your interest in this feature oadn/or clusteringn Connect (Microsoft portal for feedback on products)

SQL Server Guest Clustering/Always On

No matter how good your virtualisation stack is if you want to make SQL Server highly available you need some form of solution whereby a virtual machine can hand off the running of a SQL server database instance to another virtual machine for planned and unplanned downtime. Correct me if I am wrong but Vmware DRS simply doesn’t do this (neither does Hyper-V so I am not trying to criticise Vmware per se). Your choices were mirroring before the launch of SQL Server 2012 and now that’s out you have Always On. This should work well on Vmware as well as Hyper-V as there is no dependency on shared storage and hence iscsi support in your VMs.   

So hopefully that helps clear up a few things I had to skate over in the interest of time, do ping me if I have missed your query off and look forward to chatting with you all agin at SQL Bits or some other community event soon.