zondag 24 november 2013

Creating my first Hekaton table with SQL Server 2014

Introduction

SQL Server 2014 CTP2 has arrived and I have decided to install this new beta version of SQL Server 2014. One of the key new features is the new Hekaton Engine component and this is fully integrated in SQL Server 2014. This new feature takes advantages of the in-memory capabilities. Microsoft proposes to use the in Memory tables for OLTP purposes. Although I've haven't read all the information about Hekaton yet, but one question : "I've  is it possible to this feature also for OLAP functionality? And how is this related to the Columnstore indexes?". I hope to find out in future blogposts....

The first thing I did was installing the CTP2 version on a Virtual box machine. You can find numerous posts about creating a virtual machine with Oracle VM Virtualbox and installing CTP Versions of SQL Server on my blog. I'll not elaborate about this subject.

Resources

For this blogpost I've used the following links:


Creating a In memory table

1) First we create a new database with SQL Server 2014 Management Studio.



2) It seems to be necessary to create a Filegroup and a container for the Hekaton memory optimized tables. The filegroup will be used to guarantee the durability of the data resident in memory in case of a crash or a restart. For the container of the filegroup we need to create a directory. Expand the Databases node, right-click your database and then click Properties. To add a new memory optimized data filegroup, click the Filegroups page. Under MEMORY OPTIMIZED DATA, click Add filegroup and then enter the values for the filegroup.


3) To add a file to the filegroup, click the General page. Under Database files, click Add and then enter the values for the file. Use file type FILESTREAM Data.


In the C:\data folder a new folder is created.



4) Create a In Memory table with the following script:

CREATE TABLE dbo.Test (
   TestId int not null primary key nonclustered hash with (bucket_count=2000),
   TestName int not null index ix_TestName nonclustered hash with (bucket_count=1000),
   CreatedDate datetime2 not null,
)
WITH (MEMORY_OPTIMIZED=ON)
GO


CREATE TABLE dbo.Test2 (
   TestId int not null primary key nonclustered hash with (bucket_count=2000),
   TestName int not null index ix_TestName nonclustered hash with (bucket_count=1000),
   CreatedDate datetime2 not null,
)
WITH (MEMORY_OPTIMIZED=ON,  DURABILITY=SCHEMA_ONLY)
GO

And the tables are created in the Memory. In the folder I've noticed that some files are created and/or modified:


When we take a look at the properties of the table we see that there are some options that are indicating that we have created a memory optimized table:


Conclusions

Woohoo,  this is my first created in memory table in SQL Server 2014 CTP2 and it seems very easy doing this. I'll have to look into the details further but it seems an easy job to create and use the memory optimized table in SQL Server 2014.

Greetz,
Hennie


woensdag 20 november 2013

Installing SSDT for SQL Server 2014 CTP2

Today I've installed SQL Server 2014 CTP2 and it seems working pretty fine. But, hey where is SSDT? The replacement of BIDS? I already found out (in one of my former blogposts) that there are 2 versions of SSDT that belonged to SQL Server 2012: SSDT Database developer and SSDT Business Intelligence. But now I can't find anything in the installation procedure about BIDS or SSDT and neither after the installation in the menus, panes or whatever.

There are different downloads available of SSDT on Microsoft.com. I tried multiple versions of SSDT and it seemed troublesome. I tried the different versions multiple times and errors occured. Grrrrrrrrrr. Below a couple of versions and there are differente sizes of the downloads.


And googling SSDT gives me confusing downloads. On the blog of Koen Verbeeck you can find more information about this strange situation. He is also confused about the SSDT situation. But Koen helped me further understanding the problem.  On the blog of Jamie thomson more information is found. Here is some information on the SQL Server Data Tools Blog.

As it seems to me you need to install the following software:

  1. SQL Server 2014 CTP
  2. Visual Studio 2012
  3. Update software for SSDT and SQL Server 2014 CTP2

You can download the correct version od SSDT 2012 here. The total amount of software to download is about 800 MB(??). Don't bother the titles of the windows during the installation steps :"Installing SQL Server 2012".


And there's more software needed for the installation...


And...Succeeded....


The templates are installed...


According to the blog of SSDT (SQL Server Data Tools Blog.) a new version or a upgrade version of SSDT for SQL Server 2014 is available. So I decided to install this one too and it seems to be working.


Here you can see some information about SQL Server 2014 during the installation steps.


The installation went well and a confirmation can be found in the Help-About window of visual studio



The version of SSDt for SQL Server 2014 is 11.1.31024.0




I think that the installation procedure isn't quite ready at this moment and that Microsoft is working on it. Hopefully there comes an end to the confusion about SSDT.

Greetz,
Hennie