One of prerequisites for K2 is a SQL Server instance and in this blog post I am just going to walk you through the process of setting up this important part of your K2 deployment.
As with any other product before rushing into installation, you should take your time and do some planning. Good starting point for this is to familiarize yourself with prerequisites and check K2 blackpearl Compatibility Matrix, and in case installation of K2 smartforms is also on your least, do not fail to check with K2 smartforms Compatibility Matrix. As we talking about SQL server part we have special interest in what SQL Server versions are compatible with K2 – and you can find it Microsoft SQL Server section of K2 blackpearl 4.6 Compatibility Matrix. If you have a luxury of choice for SQL Server version (as a software assurance subscriber maybe) I would always recommend to go for latest version of SQL Server officially supported by K2, and starting from 4.6.8 this is SQL Server 2014 RTM. It is strange to see Azure SQL Server mentioned in K2 compatibility matrix without single one check-mark in respective column. Another thing of note is R2 editions, whenever you see K2 supporting both R2 and non R2 version of Microsoft products you should realize that while both are supported most of the testing being is being done on R2 versions so those are preferable to use. Why use the latest supported version of SQL? Well this is just a common sense, to have access to the newest feature and avoid pain of forced migration due to end of support cycle for older SQL version whether it is coming from Microsoft side or maybe from K2 or being dictated by your corporate standards.
Irrespective of environment type you are going to build I assume that you going for dedicated SQL server machine option. Even for tests you want to have an environment which is close to real world, and thus SQL Server as resource intensive application always being placed on separate machine.
With SQL, especially if we talk about non-clustered single server installation you can get away with “spousal” installation (one where you always say YES/NEXT/OK), but there is some caveats related with K2. K2 requires very specific collation and you want to make sure that you selected it for your SQL Server instance during setup process.
Basically if you check K2 blackpearl Installation and Configuration Guide Technology Requirements section you will be able to find Microsoft SQL Server & Reporting Services requirements where it is stated that Latin1_General_CI_AS is a required collation. Once again, quoting documentation:
Case sensitive databases are NOT supported.
The following collation setting is required: Latin1_General_CI_AS
Do not fail to interpret this required word correctly: using any other collation for your K2 database effectively means running unsupported configuration. This page does not mention that you have to have this collation on SQL Server instance level, but this is a requirement too, and K2 documentation expected to be updated to reflect this.
There is a way to change SQL Server collation without reinstalling SQL server, but what it does – it just rebuild system databases with new collation. And in case you run K2 setup manager and it created your K2 database with wrong collation which was configured on your SQL Server Instance at the time of installation, there is no easy way to change this. You will have to re-create your database with new collation and move all your data into it, this is not supported operation and also not a something you can do easily in a few clicks.
So the most important point is to have your K2 SQL Server instance configured with required collation before you start with K2 installation, as K2 Setup Manager will automatically create your K2 database using collation configured for your SQL Server instance. Note that there is no way to tell K2 Setup Manager to use collation different from collation set SQL Server instance (this is for a reason – K2 extensively uses temp database which inherit collation from SQL Server Instance and those have to match), and even if your selected SQL Service instance has unsupported collation Setup Manager won’t warn you about this.
I am not going to mention each and every step in SQL Server installation here, but mention those which are important. First I would recommend you to create named instance for K2. Yes it mean that you will have to specify instance name added to SQL server name whereas with default instance you can get away with server name only. But having meaningful instance name is more convenient, especially when you have multiple instances under your management, and given the fact that server names in enterprise environments are normally follow some weird naming conventions which are not always obvious/tell you much about what this box supports on application level. So you may want to go for named instance:
After that goes important collation settings step which you don’t want to miss or leave unverified. Remember for K2 we have to use only one collation – Latin1_General_CI_AS. See respective SQL Server installation wizard screens below.
Once you reached Server Configuration stage where you need to specify Service Accounts don’t forget to switch to Collation tab and configure it to use K2 required collation. When you switch to collation tab, you will see that bu default there is some collation selected and this selection is based on your base OS language settings. We need to change this by clicking Customize button.
You should also note that K2 required collation does not have “SQL_” prefix. And it mean that K2 requires use of Windows collation, so once you clicked Customize button on the previous screen you have to do the following selections:
Once all is selected as on the picture above, click OK and you have set your SQL Server instance to use Latin1_General_CI_AS collation:
Why Windows instead of SQL collation? “SQL_” collations use SQL’s own proprietary code pages, whereas Windows collations based on the Windows OS code pages. Windows keeps its collations up to date more often, and compatibility is better for the client applications. Hence the best practice is to use Windows collations, and K2 follows this best practice.
After this you may proceed towards completion of your SQL Server installation.
Assuming you installed your SQL Server instance on separate box you have to configure Windows Firewall rules to allow external connectivity, so that K2 server may access this SQL server instance over the network. This involves configuring 3 rules in Windows Firewall.
First of all you have to take a note of random TCP port which was assigned to your SQL server instance during installation phase. In order to do this you need to go to SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for your SQL Server instances (MSSQLK2 in this example) > TCP/IP and select Properties:
Take a note of TCP Dynamic Ports value – you will need this to create one of required Windows Firewall rules.
First you have to create a rule for your instance executable:
In this inbound rule you allow a program, and specify a path to your SQL Server instance executable – sqlservr.exe. For SQL Server 2014 default location is following: “%Program Files%\Microsoft SQL Server\MSSQL12.YOUR_INSTANCE_NAME\Binn”.
Second rule allows inbound access to your instance TCP port, which you noted earlier:
And third rule which you need to create will allow inbound access SQL Server Browser service, which by default uses UDP port 1434:
This is it for installing SQL Server service instance for K2. Additionally I may recommend you to create SQL Server alias on K2 server which will enable you to connect over short, nice and meaningful alias. And in case you have to change SQL Server machine or instance name it will be absolutely transparent for your K2 installation – you will only need to reconfigure your alias properties, nothing more. Apart from this you may want to adjust/verify SQL Server memory allocation settings and you are ready to go.
Once I get some time I will write up a guide on installing SQL Server Cluster for K2, but as you can see some of aforementioned recommendations are universal for SQL Server instance installation in general, and applicable for example when you installing SQL Server instance for SharePoint (don’t forget that it has its own compatibility with SQL Server versions and collation requirements, though) or any other application.
If you want to know more about SQL Server 2014 installation in general, including such an interesting option as installing it on Server Core refer to Install SQL Server 2014 documentation on TechNet. I guess this is desirable option for lab environments as it saves resources, and in case of production deployment it also means less resources and smaller attack surface for your SQL Server box.