3.x, a new console application has been made available to efficiently handle imports from the remote instances. This is the preferred way to import data from the remote SQLWATCH databases.
SqlWatchImport.exe leverages the performance of the .NET
SqlBulkCopy and data streaming for fast inserts as well as framework’s Thread and Connection pooling which makes it very efficient and lightweight. In my test setup, importing data from the same, single remote instance takes ~10 seconds in SSIS and Linked Server and 1.2 second using the console application.
The more often the import runs, the quicker it is because it has less work to do. You will have to find the best balance in your environment, as a reference, I have it running every 1 minute and as you can see, some servers take less tha 1 second to import:
2020-08-23 20:36:31.669 SQLWATCH Remote Instance Importer
Imports remote SQLWATCH data into the Central Repository
Marcin Gminski 2020, SQLWATCH.IO
Version: 1.1.7537.25291 (8/20/2020 2:03:02 PM)
2020-08-23 20:36:31.732 Got 3 instances to import
2020-08-23 20:36:31.732 Got 44 tables to import from each instance
2020-08-23 20:36:31.748 Importing: "SQLWATCH-TEST-1"
2020-08-23 20:36:32.107 Importing: "SQL-1"
2020-08-23 20:36:32.138 Finished: "SQLWATCH-TEST-1". Time taken: 379.5364ms
2020-08-23 20:36:32.424 Importing: "SQLWATCH-TEST-2"
2020-08-23 20:36:33.201 Finished: "SQLWATCH-TEST-2". Time taken: 768.9659ms
2020-08-23 20:36:33.857 Finished: "SQL-1". Time taken: 1746.7494ms
2020-08-23 20:36:33.857 Import completed in 2176.7349ms
Prior to version
3.x the data import was done with SSIS or via Linked Server. The application was written to address the following problems:
- SSIS is fast but cumbersome to maintain - every time new table or column is changed or added to the SqlWatch database, the package requires manual changes to reflect database changes. This is very time consuming and error prone. I am also trying to stay away from BIML to reduce complexity of the solution.
- Threading in SSIS is limited by the design of the package.
- Not everyone has Integration Server.
- Linked Server does not require as much maintenance but it does rely on SQL Agent for data collection so cannot be run on SQL Express directly.
- Linked Server is not as fast as SSIS and does not handle XML fields.
The application configuration items are in the
App.config file, included in the application folder. Before the first run, you are going to have to configure Central Repository connection parameters:
<!-- Central repository connection details -->
<add key="CentralRepositorySqlInstance" value="REPOSITORY-SQLSERVER" />
<add key="CentralRepositorySqlDatabase" value="SQLWATCH" />
The application allows adding new and updating existing remote Sql Instances.
Add remote Sql Instance “SQLSERVER1” with SqlWatch database “SQLWATCH” using Windows Authentication:
SqlWatchImport.exe --add -s SQLSERVER1 -d SQLWATCH
Add remote Sql Instance “SQLSERVER1” with SqlWatch database “SQLWATCH” using Sql user “Marcin” and Sql password “Password”:
SqlWatchImport.exe --add -s SQLSERVER1 -d SQLWATCH -u Marcin -p Password
Add remote Sql Instance with custom hostname and port:
SqlWatchImport.exe --add -s SQLSERVER1 -d SQLWATCH -h 192.168.1.10 -o 1433
You only have to configure hostname if your
@@SERVERNAMEdoes not match the physical hostname. Normally this would be a configuration error apart from rare scenarios, such as running Sql as a Docker container.
Update existing remote instance:
SqlWatchImport.exe --update -s SQLSERVER1 -d SQLWATCH -u Marcin -p NewPassword
The configuration data is saved in table
Once all remote instances have been added, data import can be simply invoked by running the
SqlWatchImport.exe without any arguments.
It is advisable to use Windows Authentication instead of Sql Authentication. If you really have to use Sql Authentication you have to keep in mind few things.
The credentials will be encrypted and stored in the
[dbo].[sqlwatch_config_sql_instance] table. The encryption will be done using .NET
MachineKey.Protect method. This is a standard method often used to encrypt connection strings on web servers running .NET applications.
By default, the encryption will use the Machine Key, which means, the application will be able to decrypt passwords on the same machine where the password was encrypted. For example, if you run
SqlWatchImport.exe --add -s SQLSERVER1 -d SQLWATCH -u Marcin -p Password on your laptop, you will only be able to run the data import on your laptop. If you want to re-save the use secret on a new machine you will have to run the update command
SqlWatchImport.exe --update ...
You can also use custom MachineKey stored in the
App.config file. You can generate new key on https://www.allkeysgenerator.com/Random/ASP-Net-MachineKey-Generator.aspx and save it in the config file. This will allow the application to work from any machine.
If you ever decide to change the MachineKey, you will have generate new Sql Secrets by running the
Please note that anyone who has access to the machine, will be able to access the MachineKey and your config file. If they also have access to the SQLWATCH database where the secrets are saved, they will be able to decrypt it. Limit Read Only access to only authorised persons. If you use Sql Authentication, please create dedicated Sql Accounts for importing data. They should only have Read Only access to the remote database so if the passwords are broken, the intruder will not gain access to anything else but meaningless SQLWATCH data.