Tag Archives: SQL Server

SQL Server’s Binary Collations

I was reading up on database engine Always Encrypted feature while preparing for 70-473 exam, and bump into these binary collations which I somehow never heard of before.

While configure Always Encrypted one of the choices you have to made is whether to use Deterministic or Randomized encryption (and you have to know the differences very well for the exam). One of the caveats when using Deterministic encryption is that it have to use a column collation with a binary2 sort order for character columns. More specifically documentation states that: Deterministic encryption requires a column to have one of the binary2 collations. If you will be using SSMS Encrypt Columns wizard it will be converting your column collation into binary2 case sensitive collation.

These statement required me to investigate the topic of binary collation a little bit.

First of all you may have different collation settings on a SQL Server instance level (i.e. on its system databases) on your databases and on specific columns and expression level.

To list all the collations available on your instance of SQL Server you can issue the following SQL statement:

On Azure SQL Database you will get back 3955 rows or possible collations. Understanding collation requires you to understand set of related terms, such as Collation, Locale, Code page, Sort order.  You should also know  that there are three major sets of collations available to you:

  • Windows collations
  • Binary collations
  • SQL Server collations

These collation groups sort data differently. In the past my standard answer/explanation about Windows VS SQL collation was that Windows one is more frequently updated, more compatible and hence more preferable over SQL one. Technically speaking it is more about how sorting works, but as per MSFT documentation: “SQL Server supports supports a limited number (<80) of collations called SQL Server collations which were developed before SQL Server supported Windows collations. SQL Server collations are still supported for backward compatibility, but should not be used for new development work.” So what I was saying/writing seems to be correct.

I won’t be covering all the details and differences related to these sets of collations as I only want to focus here on Binary collations which are requirement for Always Encrypted Deterministic encryption.

Binary collations sort data based on the sequence of coded values that are defined by the locale and data type. They are case sensitive. A binary collation in SQL Server defines the locale and the ANSI code page that is used. This enforces a binary sort order. Because they are relatively simple, binary collations help improve application performance. For non-Unicode data types, data comparisons are based on the code points that are defined in the ANSI code page. For Unicode data types, data comparisons are based on the Unicode code points. For binary collations on Unicode data types, the locale is not considered in data sorts. For example, Latin_1_General_BIN and Japanese_BIN yield identical sorting results when they are used on Unicode data.

There are two types of binary collations in SQL Server; the older BIN collations and the newer BIN2 collations. In a BIN2 collation all characters are sorted according to their code points. In a BIN collation only the first character is sorted according to the code point, and remaining characters are sorted according to their byte values. (Because the Intel platform is a little endian architecture, Unicode code characters are always stored byte-swapped.)

So for  Always Encrypted Deterministic encryption any of the collations returned by the query below will do:

This leaves you with 133 collations to choose from 🙂 Generally speaking BIN and BIN2 collations use different sorting algorithms and BIN2 is more preferable in general, not only for Always Encrypted Deterministic encryption columns. Another interesting question is why we have BIN/BIN2 collations for different languages? Like Arabic_BIN2, French_BIN2 etc. The reason is that each of those uses different code page for encoding the characters sorting in the varchar type so linguistic collation type is very important and comes into play only for varchar data as this will be sorted based on the language selected (this is not applicable to nvarchar where it has no difference).

All collations which are not binary collation are linguistic collations. For example, Latin1_General_CI_AS is a linguistic collation and it uses a sorting algorithm compatible with several of English language and many Western European languages. Please don’t be confused by the name of Latin1_General, as it actually can sort all Unicode characters defined in Unicode 3.2 characters set and it can also sort many other languages correctly as well (if the language has no sorting conflict with the latin1_general sorting rule).

Binary collations have better performance than linguistic collations, and that is the main advantage of using then. A binary collation is always case sensitive and accent sensitive. BIN2 collation is generally more preferable than BIN collation.

Facebooktwittergoogle_plusredditpinterestlinkedinmail

SQL Script to switch all currently RO databases to RW mode

I was doing some testing of K2 databases consolidation process which required me to re-run database consolidation process more than once to re-try it. Unfortunately K2 Database Consolidation Tool leaves all databases in read-only mode if something fails during consolidation process. If you remember K2 used to have 14 separate data bases prior to consolidated DB was introduced (see picture below).

Typing 14 statements manually to bring all these database to read-write mode is a bit time consuming so I came up with the following script:

Essentially it will select all databases currently in RO state and will output bunch of statements to bring all of them to RW state as an output:

Just copy-paste this script output into new query window of SSMS and press F5 🙂

It may be useful for you once in a while (and if not for this specific use case, then as an example of generating some repetitive statements which contain select statement results inside).

Facebooktwittergoogle_plusredditpinterestlinkedinmail

SQL Server: new instance installation frozen on “SqlEngineConfigAction_install_startup_Cpu64” step

I was installing additional new instance of SQL Server 2012 on a machine where other instance was installed earlier. In the process I run into the problem where Setup wuzard just frozen on about 99% on SqlEngineConfigAction_install_startup_Cpu64 step without flagging any errors and making no progress for hours, so that Setup window looks like that all the time:

I realized that I’ve seen it before but not made any efforts to fix it beyond starting process from scratch. This time I did some googling and found that there may be quite a few reasons for this problem, for example see this blog post: “Your SQL Server Setup may hang forever when it’s almost at the 99 %!” on MSDN Blogs. I tried a few suggestion with no luck but in the process I’ve noticed that Windows service for my new named instance has been already created but frozen in “Starting” state, so killing and restarting it manually revived SQL Setup Manager and it completed installation process with flying colors in a minute after this. So it seems SQL Setup Manager just endlessly waits for service started confirmation or something like this without any time out in place… Anyhow I’m just taking a note of my fix in case somebody else or myself run into this again.

Facebooktwittergoogle_plusredditpinterestlinkedinmail

PS script to get SQL version from BAK file

Quite unpleasant thing about MS SQL Server database backup files it that you can’t restore them on an older version of SQL Server (it seems that even if you really want to create such BAK file it is not possible), moreover this is valid not only for major versions but also for things like R2, meaning you can’t restore BAK file created in SQL Server 2008 R2 on SQL Server 2008 Server (non-R2).

Just to save my time trying to restore BAK files against wrong versions of SQL Server I created this script which allows you to retrieve SQL version from BAK file headers and compare it with your server SQL version. Specify path to your BAK file and check the output – if BAK file SQL version is newer that your server version then BAK file can’t be restored on this server. Sample script output can be found below:

It tells you that backup was taken on SQL Server 2012 SP3, while you run SQL Server 2012 SP2. Once you install SP3 for SQL Server script output will change to this:

Once two numbers match you are ready for backup restore 🙂

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Installing SQL Server instance for K2 blackpearl

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:

SQL Server Creating 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.

SQL Server Collation 01You 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:

SQL Server Collation 02Once 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:

SQL Server Collation 03Why 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:

SQL Check Random Instance Port

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:

SQL Firewall Rules 1 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 ServerMSSQL12.YOUR_INSTANCE_NAMEBinn”.

Second rule allows inbound access to your instance TCP port, which you noted earlier:

SQL Firewall Rules 2 Random TCP Instance Port

And third rule which you need to create will allow inbound access SQL Server Browser service, which by default uses UDP port 1434:

SQL Firewall Rules 3 SQL Server Browser UDP 1434This 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.

Facebooktwittergoogle_plusredditpinterestlinkedinmail

How to check Microsoft SQL Server version

Sometimes you need to verify exact version of Microsoft SQL Server and you can do it in a different ways. GUI way of doing this is to access Help > About in Microsoft SQL Server Management Studio which will give you something like this:

SQL_Mgmt_Studio_About

Well this is good, but can you readily translate 11.0.5058.0 into SQL Server XXXX SPX? Maybe you can, but I don’t. So the better way is to employ some SQL commands to get this information in more readable form:

Will return you something like this (but in online):

Microsoft SQL Server 2012 – 11.0.5058.0 (X64)

May 14 2014 18:34:29

Copyright (c) Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Well at least it does show you that 11.0.5058.0 is SQL Server 2012, but doesn’t disclose what SP level you have… Then next command (works starting from SQL Server 2000) will help you:

This returns following details (build, SP level, edition):

11.0.5058.0 SP2 Enterprise Edition (64-bit)

But other frequent scenario if when you don’t have access to the server and have only build number provided to you, here you may refer to the following comprehensive list of SQL Server builds resolved to product name and SP/update level (pay attention that below on that page you may find the links for the similar data on Exchange and SharePoint though these two list don’t look as impressive as the list of SQL Server versions):

http://sqlserverbuilds.blogspot.ru

This should be enough to establish exact SQL version.

As a bonus there are couple of alternative way of checking SQL server version information. PowerShell way (almost).

1) Using Invoke-sqlcmd, strictly speaking is just use of old good sqlcmd but inside of PowerShell – so this is not a pure PowerShell method, bur rather an example of how to execute SQL query in PowerShell:

It should look similar to this (and the build on the screenshot is SQL Server 2014 SP1):

Check SQL Server version PS

2) Reading relevant registry keys with PowerShell (this is a bit cooler as it enumerates your instances):

I took this script from Shawn Melton‘s answer to “How do I check for the SQL Server Version using Powershell?” question on stackoverflow.com

Here is an output from my test server with two instances:

Check SQL Server version PS option 2 output

Facebooktwittergoogle_plusredditpinterestlinkedinmail