generoussmooth

1. Redgate Sql Search For Sql Server 2017
2. Redgate Sql Server Search Tool

Overview

SQL Monitor custom metrics Collect the SQL Server performance data you care about with tried and tested SQL Monitor custom metrics. Redgate has brought together a range of custom metrics for you to install directly into SQL Monitor.

I don’t know if you have ever used the Redgate Sql Data Compare Tool. It is a great tool that allows you to compare data in tables when you have a matching key to go on. I use it quite a bit to make sure configuration data is in sync across systems. In those cases, the data in the tables should match exactly, so the tool works really well.

The Problem

I have a situation in my current position where we have an engine that produces output to a data table that affects the manufacturing floor. We have a need to do regression testing whenever a change is made to this engine. In this case we will restore a backup of the production database in test and then run the same job that was run in production and compare the results. In most cases the data should match exactly. In a few cases we expect different data because of the engine change.

So the issue is: currently this is a manual process to check the data over 50 to 100 previously run jobs to verify the engine change didn’t break or affect anything else. I wanted to speed up this regression testing process so I looked into using the Redgate SQL Data Compare API so it could do the heavy lifting for me.

The Solution

When I use Redgate SQL Data Compare API, I will set the server to be the same, the database will be the same, the table will be the same. I will change the key so it is no longer the primary key of the table to a Serial Number column that stays the same across runs of the job in the engine.

Step 1

If you don’t already own the Redgate tools, that would be step one. My company provides me with a license so I have the full suite of tools including the folder “SQL Comparison SDK 12”. Inside this folder is the .NET dlls you will need to do the API calls.

Step 2

First, create a .NET project and set a reference to these dlls:

Honey singh latest songs. Most likely you will find them in this folder: C:Program Files (x86)Red GateSQL Comparison SDK 12AssembliesSQL Data Compare

Step 3

These examples are in C#, so they would be slightly different if you are using a different .NET language. Add these using statements:

Step 4

Declare the database and session variables. Just like SQL Data Compare, you have to setup and register your two database connections. In this case since they point to the same server and database it is pretty straight forward.

Step 5

Set the database server name and database and register.

Step 6

Setup the table mapping and set the new key to match the rows in the table.

Step 7

Remove any columns you don’t want to have a compare done on. Like primary keys, identity columns, date columns etc.

Step 8

Set the WHERE clause so we know which sets of data in the table to compare against. In my case, each job run is given a unique HeaderID so I can use that when comparing the two sets of data in the same table.

Redgate Sql Search For Sql Server 2017

Step 9

Redgate Sql Server Search Tool

We now need to compare the data and display the results.

Final Step

Don’t forget to dispose of the database and session objects.

Conclusion

In my actual use, I call this compare multiple times and just pass in different HeaderIDs. The registering of the databases takes the longest amount of time, so I do that first and then hold on to those objects and call the data compare just passing the database and session objects in. For me this process decreased the time it takes to do regression testing. It also does a better job since we know the old and new data matches exactly.

I'm a huge fan of a lot of the Redgate tools, however at my current workplace they are very paranoid over anything interacting with live servers (in particular affecting performance).

So my question is specifically on their SQL Search tool, what impact does it have on a server? I'm assuming that there will be:

1. Initial indexing (what does it interact with, idea on impact on server, can it be customised to not index live servers)
2. Subsequent connections/refreshes (how is it determining if there is a change to be re-indexed and what performance/load impact that will have on a server)?

2 Answers

SQL Server Profiler is very useful when it comes to solving problems like this - use the ApplicationName filter to trace data specific to a particular application (i.e. SQL Search in this case), capture the data when it is used and then analyze executed queries and their performance.

Answer received from Redgate support:

Following are answers to your questions:

1. When you load a database in SQL Search, it indexes and stores the info on cache for giving search results. To not index specific servers, just do not select 'All database' nor pick the database you do not want to index in the database filter.

2. Whenever you have mode modifications or want the index to be refreshed, manually refresh the index from the menu in SQL Search.

Hope that helps.

Thanks, Anuradha Deshpande

Not the answer you're looking for? Browse other questions tagged sql-servertools or ask your own question.