I am happy to share that the newest release of the CISL (Columnstore Indexes Script Library) – 1.3.1 is live!
Do not get confused by the small incremental of the build version – this release is something that anyone should be upgrading to, because of the all new functionalities and the increased stability and the possibility of using easier installation are absolutely worth the trouble of an easy upgrade.
The Details of what’s new in CISL 1.3.1
Here is the small description of what is new in this release:
- The database snapshots (.dacpac) for all platforms are now included in the Releases\DacPacs.
- Includes new Powershell functions for installing and removing CISL from the Instances:
- Install-CISL.ps1 will allow you to install the CISL at multiple databases of a SQ Server Instance (or Azure SQLDB).
- Remove-CISL.ps1 will allow you to remove the CISL from the multiple databases of a SQL Server Instance (or Azure SQLDB).
- Support for the different collation is included.
- Includes information on all recent SQL Server updates.
- Included support of the new Columnstore Indexes Trace Flags in SQL Server 2016.
- Basic Unit Tests (based on t-sqlt) are included for SQL Server 2012 & SQL Server 2014, guaranteeing the quality of the released code.
- A good number of bug fixes.
- Further parameter enhancements for the existing functions.
Let’s review some of the changes in the details:
The database snapshots (.dacpac)
With the 1.3.1 version of the CISL, The subfolder \Releases\DacPacs\ within the archive, includes the .DacPac files for each of the supported SQL Server version, as you can see on the screenshot below:
Additionally, those very same database snapshot files (.dacpac) can be used by the new powershell function Install-CISL for installations on the SQL Server instances.
Powershell
If you put all the files from the .ZIP archive into a CISL folder, this one will serve as a Powershell Module, since I have added the cisl.psd1 (Manifest) & cisl.psm1 files to the root.
Using the following command within the CISL folder, you can import the CISL Powershell Module into your Powershell session, directly without copying it to the Modules folder (which would be an alternative, if you plan to use it very frequently):
Import-Module .\cisl.psd1 -Force -PassThru
As you can see on the image above, the issued command installs 2 commands to be used: Install-CISL and Remove-CISL.
Install-CISL
The Powershell function Install-CISL allows to install the CISL library, with automated recognition of the used SQL Server or Azure SQLDB version, or alternative one can easily force the installed version.
The function will use the provided script sources (.sql) or the provided .dacpac files for installing them on the specified target server.
The only required parameter for this release is the $SQLInstance, which will ask you to specify the server where the CISL should be installed.
Invoking the following command will install the CISL library on all user databases (minus those which are ignored by default and can be overridden by the usage of the $excludeDBs parameter
Install-CISL -SQLInstance .\SQL16
On the sample image above, you can see that the powershell script itself will automatically determine the version of SQL Server that the instance is referring to (the SQL Server 2012, 2014, 2016 & Azure SQLDatabase are supported) and will install the CISL on all the user databases that it can find or have access.
If you have a Database that is set as ReadOnly or is in the Recovering state, or you have a Database Snapshot or something else is wrong with it that the script can’t access it, you will get a message and the installation proceed on the rest of the databases.
Notice that for this version no multiple servers are supported for the parameter$SQLInstance, but this might change in the upcoming versions until the end of the year.
We have used the .sql scripts for the installation of the CISL, but as I have already mentioned above, we can also use the .dacpac for the installation, if you organisation requires it. By specifying the value “dacpac” for the parameter $installType, the function will use the .dacpac files, stored in the \Releases\DacPacs\ folder.
The other possible value for this variable is “script” and it is a default one.
While using the “dacpac” installation type, you might need to specify the location of the SQLPackage.exe and for that purpose the variable $sqlPackageLocation exists. It’s default value is set to “C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin\sqlpackage.exe”, but you can set it to the SQL Server version and location that you are using, if needed.
If for some reason, you do not want the Install-CISL cmdlet to determine the SQL version, you can use the $forceSqlVersion parameter, to force the specific SQL version script usage. The possible supported values are of all currently implemented platforms – 2012, 2014, 2016 & Azure.
If you are using non-default (executing) user credentials to access the SQL Server or Azure SQLDatabase, then using the parameter $cred will allow you to pass the used pscredential into the function.
Here is an example of installing CISL on the Azure SQLDatabase with the credentials which you will be asked upon running the script:
$azureCreds = Get-Credential Install-CISL -SQLInstance cisl.database.windows.net $cred $azureCreds
If you need to specify a specific database or databases where to install the CISL on your server, then using the parameter $installDBs will allow you to limit the number of databases that will receive the CISL. Here is an example of installing CISL on the SQL Server instance with the name .\SQL14 only on the database ContosoRetailDW.
Install-CISL -SQLInstance .\SQL14 -installDBs ContosoRetailDW
There is another important variable that will filter out by default the system databases from being used for the CISL installation. The name of this parameter is $excludeDBs and the following databases are being ignored by default:
(“master”, “model”, “tempdb”, “msdb”, “SSISDB”, “MDS”, “ReportServer”, “ReportServerTempDB”, “DQS_MAIN”, “DQS_PROJECTS”, “DQS_STAGING_DATA”)
If you want to exclude a specific database from receiving CISL, simply add its name to the parameter.
Another interesting parameter that is useful for the upgrades is the $installForExistingCStoreOnly, which if being set to $true, will allow to install CISL only on the databases where it has been previously installed. By default this parameter is set to $false, and so the verification will not be done when invoking the Install-CISL command by default.
Remove-CISL
Remove-CISL will allow you to remove the CISL from the multiple databases of a SQL Server Instance (or Azure SQLDB). It has almost the same (though more limited set of parameters) as the Install-CISL function.
Right now this function has only 4 parameters, but I consider expanding it the future. The currently supported parameters are $SQLInstance, $cred, $installDBs & $excludeDBs. If you are looking for more information about them, then read the information about them in the Install-CISL section above.
The only important thing here to add is that the $excludeDBs parameter has no default values, allowing you to clean up system databases if you have installed CISL there by the mistake.
Support for the different collation is included.
Thanks to the requests from the Hugo Alhandra & David Barbarin, the CISL 1.3.1 will support databases with Columnstore Indexes, having different collation than the TempDB database.
Includes information on all recent SQL Server updates.
As always, the newest Cumulative Updates & Service Packs with the information on the corrected bugs related to Columnstore Indexes are included in the script sqlserver_instance_info.sql & cstore_GetSQLInfo stored procedures.
Included support of the new Columnstore Indexes Trace Flags in SQL Server 2016.
Some of the new Trace Flags for the SQL Server 2016m that I have described in Columnstore Indexes – part 86 (“New Trace Flags in SQL Server 2016”), like for example the Trace Flag 10204 will automatically trigger the rebuild possibility (if any of the other conditions are met and if the recommendation parameter @useRecommendations is enabled – and it is enabled by default).
Basic Unit Tests (based on t-sqlt) are included for SQL Server 2012 & SQL Server 2014, guaranteeing the quality of the released code.
In a continuos struggle of improving the quality of the CISL, I have started implementing the Unit Tests for the CISL and for this release over 30 test-cases have been implemented for the SQL Server 2014 and around 15 test-cases for the SQL Server 2012. I will be making a lot of the improvements for increasing stability and quality of the framework in the next months.
I decided to use TSQLT framework for this purpose and if you are interested in helping, please let me know or start committing them on the GitHub.
A good number of bug fixes.
The implementation of the unit tests allowed me to uncover a couple of bugs, which have been fixed in this release. Mostly they were small things, like the comparison for the number of rows for SuggestedTables script would not work, because the used operator was > instead of >=.
Further parameter enhancements for the existing functions.
There has been some small enhancements for the parameters, which affect SQL Server 2012 version mostly. I will be syncing the total parameters and their names in the next months, so you can run the very same command without caring which SQL Server version you are working with.
The CISL 1.3.1 release is already available on Github! So go ahead and download it!