25 May 2018
25 May 2018

Using SQL Data Compare to Synchronize Custom Error Messages

Steve Jones shows how to get SQL Data Compare to synchronize custom error messages, stored in the sys.messages system catalog view, across SQL Server instances. This can be a handy way of ensuring your DR and HA instances keep their messages in sync.

One way to offer a better experience to our application users is to anticipate likely mistakes they could make, such as when filling in a web form, and provide them with a meaningful error message that allows them to continue quickly with their work. One way to do this in SQL Server to is to use the sp_addmessage system stored procedure, to add custom error messages to the master.sys.messages system catalog view, which can then be referenced in our error handling routines, either using RAISERROR or, preferably, TRY…CATCH…THROW.

To ensure consistent behavior, the administrator needs to ‘synchronize’ the contents of sys.messages across all SQL Server instances. Unfortunately, there’s no easy, built-in way to do this. I wrote an article on SQLServerCentral.com that showed one technique, using simple string concatenation to capture custom messages from one instance, and then a cursor that called sp_addmessage, to add them to sys.messages on another instance. However, it would still be a cumbersome and tedious task for an administrator to make sure that each message is added to all relevant SQL Server instances, and for all languages in which you need to display the messages.

Surely there must be an easier way? SQL Data Compare is designed specifically to synchronize data between source and target tables. However, we need a workaround to make SQL Data Compare work when comparing system views, which involves creating a proxy view, and some INSTEAD OF triggers on the view, in the master database (a practice of which I generally disapprove).

This trick might be useful for small data sets and periodic synchronization, such as to ensure that High Availability or Disaster Recovery instances that are not clustered have their custom error messages synched, but I wouldn’t recommend it as a general-purpose technique, for other types of tables and views.

Using SQL Data Compare with a ‘proxy’ view

SQL Data Compare will the source database (or scripts folder) to the target and generate a script to synchronize the data in the two. When you deploy the script to the target, it will INSERT any rows that exist in the source but not the target, DELETE any that are in the target but not the source, and UPDATE any that are in both, but different, to match what is in the source.

It’s a perfect tool for synchronizing small volumes of static data, including reference data, seed data, test data, and custom error messages, which a developer or administrator needs to ensure are deployed to various downstream environments. However, the first problem we have is that the tool can’t compare system views and tables, at least not directly.

The way around this is to create a “proxy” view of sys.messages, in the master database. You need to do this on both the source and target instances. This is a bit unorthodox, and I don’t like creating objects in master. However, it will work, and credit goes to Sam Blackburn, one of my colleagues at Redgate Software, who came up with this as a workaround.

To try this out, first let’s put a test message in the source database

Listing 1

Now create the proxy view in the master on the source instance, plus another copy of the view (empty) in master on the target.

Listing 2

Open SQL Data Compare. In this case, I’m comparing a SQL Server 2014 and SQL Server 2016 instance. On the Data Sources tab, set the source and target instances, with the Database as master, in each case.

Figure 1

On the Options tab, under Mapping Behavior, make sure the Include views checkbox is ticked. Now click over to the Tables&Views tab, where you’ll need to specify the Comparison Key for our SysMessagesProxy view. I’ll use the message_id and language_id columns. These will produce a unique key for the messages.

Figure 2

I’ve also set a WHERE clause to ensure I’m only trying to compare user-defined (not system) error messages.

Figure 3

We can now run the comparison. SQL Data Compare will generate a script to insert the row into the target view but if you try to deploy it, it will fail, because direct inserts and updates are not allowed on system tables.

We need one more ‘workaround’ step.

Using an INSTEAD OF trigger on the target view

We need to create three INSTEAD OF triggers on the SysMessageProxy view, one each to handle INSERT, DELETE and UPDATE actions. So, for example, an attempt to INSERT a row into the target view will fire the AddCustomMessage trigger, and using an embedded cursor, it will iterate through each message in the Inserted table. For each row, we first check to see if the message exists in sys.messages. If so, we move to the next row.

If the row does not exist, we get the name of the language, corresponding to the row’s language ID, from sys.syslanguages. This is because the ID is stored in the table, but we need the name of the language for sp_addmessage. The trigger then executes sp_addmessage, to insert the row into sys.messages, and then fetches the next row, and so on.

Listing 3 shows the code for the three triggers.

Listing 3

Now, we’re ready to re-run the comparison project. I’ve added a few more custom messages to SysMessageProxy on the source SQL Server 2014 instance, and one to the target SQL Server 2016 instance. Figure 4 shows the results of the comparison, using SQL Data Compare.

Figure 4

Click the Deploy button and choose Deploy using Data Compare. On clicking next, you’ll see the deployment script, which will be saved. I can see this has 1 DELETE and 4 INSERTs.

Figure 5

Click deploy, and you’ll see a confirmation dialog that you’re about to make permanent change to the target; once this is deployed, you can’t undo the changes. If you haven’t captured the previous state of the data, you won’t be able to easily go back.

Now, with our triggers in place, the deployment will work. Assuming you had the “recompare after deployment” box checked, SQL Data Compare will re-run the comparison is run and you will see that the data is now in sync.

Figure 6

Summary

I’ve presented a trick to get SQL Data Compare to synchronize custom error messages, stored in the sys.messages system catalog view, across SQL Server instances. This can be a handy way of ensuring your DR and HA instances keep their messages in sync. That said, the idea of creating object tin master will not be appealing to most administrators. Also, a trigger running a cursor is very inefficient, and could cause resource and performance issues if used for large data sets, so I would not recommend this technique for other types of tables.

Tools in this post

SQL Data Compare

Compare and deploy SQL Server database contents.

Find out more

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Event

    Microsoft Future Decoded

    Microsoft Future Decoded is your essential guide to disruptive technology and how it will impact people and businesses, today and tomorrow. Back for a fourth year, Microsoft Future Decoded will focus on current and emerging trends at the cross over of business and technology. With cloud, big data and artificial intelligence already rewriting the rules

  • Webinar

    How to extend DevOps practices to the database

    Including the database in DevOps initiatives greatly improves performance. In this webinar Arneh Eskandari will walk you through Redgate’s Database DevOps solution, showing reliable, scalable and repeatable processes for automating your database development and deployment

  • Webinar

    Extending DevOps to the database: release management

    Microsoft MVP Steve Jones and Redgate’s Arneh Eskandari show how Redgate’s Database DevOps solution works to improve your database development and deployment processes. You'll see a demo of tools from Redgate's SQL Toolbelt and see how they plug into your usual technology stack.

  • Article

    Monitoring Azure SQL Database with SQL Monitor

    Grant Fritchey explains the Azure-tailored metrics and alerts that SQL Monitor provides to diagnose any performance problems with your Azure SQL Databases, and let you know which resource (CPU, data IO or log IO) is the bottleneck.

  • University

    Take the SQL Toolbelt course

    SQL Toolbelt includes all of the components that enable Database DevOps. This step-by-step guide takes you through the process right from being able to analyze the impact of database changes through to making those changes, source controlling them, deploying them out to your target environments, and finally monitoring and documenting those environments.

    Here you will see an overview of each tool and how it can benefit your organization, but be sure to check in the module description whether there is a full course available for the tool you are learning.

  • Forums

    SQL Data Compare Forum

    Compares and synchronizes SQL Server database contents