Having a couple of hours to play I created a SQL Server 2014 box on Azure and followed This SQLServerCentral Post to create a File Group and table with T-SQL. All good. I then decided to take a look at doing it with Powershell and I cannot create the table. I think it is to do with my Index creation. Can anyone see my mistake as I am going round in circles now.
Here's the T-SQL
-- Create new database
CREATE DATABASE TestDatabase
GO
--Add MEMORY_OPTIMIZED_DATA filegroup to the database.
ALTER DATABASE TestDatabase
ADD FILEGROUP XTPFileGroup CONTAINS MEMORY_OPTIMIZED_DATA
-- Add a new file to the previous created file group
ALTER DATABASE TestDatabase ADD FILE
(
NAME = N'HekatonFile1',
FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\HekatonFile1')
TO FILEGROUP [HekatonFileGroup]
GO
-- Let's create a new Memory Optimized Table
CREATE TABLE TestTable
(
Col1 INT NOT NULL,
Col2 VARCHAR(100) NOT NULL,
Col3 VARCHAR(100) NOT NULL
CONSTRAINT chk_PrimaryKey PRIMARY KEY NONCLUSTERED HASH (Col1) WITH (BUCKET_COUNT = 1024)
) WITH (MEMORY_OPTIMIZED = ON)
GO
And Here is my Powershell
# To Load SQL Server Management Objects into PowerShell
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMOExtended’) | out-null
$server= new-Object Microsoft.SqlServer.Management.Smo.Server("(local)")
$dbname = "HekatonTest"
$db = New-Object Microsoft.SqlServer.Management.Smo.Database($server, $dbname)
# Add FileGroups
$FG1Name = "PRIMARY"
$Normalfg = new-object ('Microsoft.SqlServer.Management.Smo.FileGroup') ($db, $FG1Name)
$db.FileGroups.Add($Normalfg)
$FG2Name = "MemOpt"
$MemOptFG = new-object ('Microsoft.SqlServer.Management.Smo.FileGroup') ($db, $FG2Name)
$MemOptFG.FileGroupType = "MemoryOptimizedDataFileGroup"
$db.FileGroups.Add($MemOptFG)
#Create datafiles
$normallogname = "HekatonTest_Data"
$dbdfnormal = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($Normalfg, $normallogname)
$dbnormalfile= $server.Information.MasterDBPath + '\' + $normallogname + '.mdf'
$normalfg.Files.Add($dbdfnormal)
$dbdfnormal.FileName = $dbnormalfile
$dbdfnormal.Size = [double](5.0 * 1024.0)
$dbdfnormal.GrowthType = 'Percent'
$dbdfnormal.Growth = 25.0
$dbdfnormal.IsPrimaryFile = 'True'
$MemOptFilename = "MemOpt_Data"
$MemOptDataFile = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($MemOptFG, $MemOptFilename)
$MemOptDataFilePath= $server.Information.MasterDBPath + '\' + $MemOptFilename + '.ndf'
$MemOptFG.Files.Add($MemOptDataFile)
$MemOptDataFile.FileName = $MemOptDataFilePath
$db.Create()
#Create Table
$dbname = "HekatonTest"
$db = $server.databases[$dbname]
$tb = new-object Microsoft.SqlServer.Management.Smo.Table($db, "MemOptTable")
#Add Columns
$col1 = new-object Microsoft.SqlServer.Management.Smo.Column($tb,"Col1", [Microsoft.SqlServer.Management.Smo.DataType]::Int)
$col2 = new-object Microsoft.SqlServer.Management.Smo.Column($tb, "Col2", [Microsoft.SqlServer.Management.Smo.DataType]::varchar(100))
$col3 = new-object Microsoft.SqlServer.Management.Smo.Column($tb, "Col3", [Microsoft.SqlServer.Management.Smo.DataType]::varchar(100))
$tb.Columns.Add($col1)
$tb.Columns.Add($col2)
$tb.Columns.Add($col3)
$C1Name =$col1.Name
#Create Index
$IX = New-Object Microsoft.SqlServer.Management.Smo.Index ($Tb, "PK_PrimaryKeyName")
$IX_col = New-Object Microsoft.SqlServer.Management.Smo.IndexedColumn ($IX,$C1Name)
$IX.IndexedColumns.Add($IX_col)
$IX.IndexKeyType = "DriPrimaryKey"
$IX.IndexType = "HashIndex"
$IX.BucketCount = "1024"
$IX.FileGroup = "PRIMARY"
$IX.IsMemoryOptimized = $true
$Tb.Indexes.Add($IX)
$tb.FileGroup = "MemOpt"
$tb.Durability = "SchemaAndData"
$tb.IsMemoryOptimized = $true
$tb.Create()
I can create the filegroups and the database with PS and use SSMS to create Memory Optimised Tables (XTP) and if I take out the index creation then the table creates but isnt XTP obviously.
I know I can take the T-SQL and put it into Invoke-SQLCMD
but I figured you ought to be able to do it with Powershell.
Can anyone point me in the right direction? For interest only, maybe its not available in the CTP