If you are a database administrator, you already know how important database design, indexes, partitions, and other things are. However, what is equally important are the character sets and collations that are in use on your database instances. In this blog post we will tell you what you need to know to monitor collations in SQL Server with dbWatch.
What are Collations?
In the database world, collations are sets of symbols and encodings – in other words, collations specify how data is being sorted in a certain database engine (in our example, SQL Server). Collations can have multiple “flavors” – for example, you can have a Chinese collation, a Swedish collation, a UTF-8 collation, a UTF-16 collation or even a binary collation – it all depends on the use case of your database instance. One character set always has at least one collation – some character sets have more than one.
Collations, in general, are just a set of rules defining how to compare characters. If you have, say, a MySQL instance and a Latin-based character set (say, latin1), your default collation is latin1_swedish_ci. If you have a SQL server instance, your collation should be SQL_Latin1_General_CP1_CI_AS or DATABASE_DEFAULT (default collation) – changing collations can allow your applications to support multiple languages (for example, if you want to support the Chinese language, you will use UTF-8 etc.
Why Do You Need to Keep an Eye Out for Collations?
By now you might be thinking “wow, this is really complex. Why do I need all of this?” Thankfully, there is an answer – you would need to keep an eye out for the collations in your database instances or farms every time you are supporting multiple languages in any of your applications connected to database servers – choose a wrong collation and the text in certain languages (for example, Russian, Chinese, Japanese, Korean etc.) will be shown as question marks (“???”) instead of the actual text, so it’s important to choose wisely.
Monitoring Collations in SQL Server with dbWatch
Now that you know what collations are and why you should monitor the collations in your database instances, you should know how to monitor collations in SQL Server too, right? Thankfully, everything is not as complex as it might seem – just fire up dbWatch, click on your SQL Server instance on the left-hand side and locate the collation check job under Availability:
The collation check job is simple to set up – simply right click on it, click Configure and you will be able to see a list of items that can be modified to set up the collation check job as you see fit:
If you want to, you can specify collations which may be ignored (you can use a wildcard (“%”) character too), specify a list of databases you might want to ignore, also specify a parameter for ignoring the databases that are offline to only monitor database instances that are available, and even set up the status (0 means “OK” (represented in green), “1” produces a warning in orange and “2” produces an alarm in red):
Configuring the collation checking job can be helpful if you want to check if there are some collation conflicts with temporary tables and table variables or if you are doing some regular maintenance and want to check that all your SQL Server collations are working as intended.
Monitoring collations is something that every database administrator inevitably deals with at least once in their career. Proper monitoring of your database collations means that you should use appropriate character sets and collations for the characters that you intend to store in the database instance – that can be especially useful if you are dealing with or want to support multiple languages in the future. Properly monitoring your SQL Server collations (and instances in general) is important to avoid problems that might cause disruption or even downtime in the future. If you’reyou are looking for a tool that could help you monitor your SQL Server instances, dbWatch can jump to the rescue – if you need a little more help though, consider having a look through the documentation or contacting the team to learn more about the product.
About the Author:
Lukas Vileikis is an ethical hacker and a frequent conference speaker.
Since 2014, Lukas has found and responsibly disclosed security flaws in some of the most visited websites in Lithuania.
He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.