Scroll ignore | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||||||||
About Collectors
Insert excerpt | ||||||
---|---|---|---|---|---|---|
|
...
Pre-requisites
Python 3.6 8 - 3.1011
Access to the KADA Collector repository that contains the SQL Server whl
The repository is currently hosted in KADA’s Azure Blob Storage. You will be given a SAS token to access the repository. Reach out to KADA Support (support@kada.ai) if you do not have access.
Download the SQL Server whl (e.g. kada_collectors_extractors_sqlserver_azure-#.#.#-py3-none-any.whl)
Access to K landing directory
Access to SQL Server (see section below)
Check the SQLServer instance port
Run the following query and note the local tcp port.
Code Block SELECT local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID GO
...
Code Block | ||
---|---|---|
| ||
--Query To Create Extended Events Session
CREATE EVENT SESSION [KADA] ON SERVER ADD EVENT sqlserver.sp_statement_completed (
ACTION(package0.collect_system_time, package0.event_sequence, sqlos.task_time, sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_id, sqlserver.database_name, sqlserver.nt_username, sqlserver.query_hash, sqlserver.server_instance_name, sqlserver.server_principal_name, sqlserver.server_principal_sid, sqlserver.session_id, sqlserver.session_nt_username, sqlserver.transaction_id, sqlserver.username) WHERE (
(
[statement] LIKE '%CREATE %'
OR [statement] LIKE '%DROP %'
OR [statement] LIKE '%MERGE %'
OR [statement] LIKE '%FROM %'
)
AND [sqlserver].[server_principal_name] <> N'USERS_TO_EXCLUDE'
AND [sqlserver].[is_system] = (0)
AND NOT [statement] LIKE 'Insert into % Values %'
AND [sqlserver].[Query_hash] <> (0)
)
), ADD EVENT sqlserver.sql_statement_completed (
SET collect_statement = (1) ACTION(package0.collect_system_time, package0.event_sequence, sqlos.task_time, sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_id, sqlserver.database_name, sqlserver.nt_username, sqlserver.query_hash, sqlserver.server_instance_name, sqlserver.server_principal_name, sqlserver.session_id, sqlserver.session_nt_username, sqlserver.transaction_id, sqlserver.username) WHERE (
(
[statement] LIKE '%CREATE %'
OR [statement] LIKE '%DROP %'
OR [statement] LIKE '%MERGE %'
OR [statement] LIKE '%FROM %'
)
AND [sqlserver].[server_principal_name] <> N'N'USERS_TO_EXCLUDE'
AND [sqlserver].[is_system] = (0)
AND NOT [statement] LIKE 'Insert into % Values %'
AND [sqlserver].[Query_hash] <> (0)
)
) ADD TARGET package0.event_file (SET filename = N'G:\extended events\Extendedevents.xel', max_file_size = (20), max_rollover_files = (100))
WITH (MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0 KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = ON, STARTUP_STATE = ON)
GO
-- Check if the session is dropping events and see other data about the session
-- https://sqlperformance.com/2019/10/extended-events/understanding-event-loss-with-extended-events
SELECT
s.name,
s.total_regular_buffers,
s.regular_buffer_size,
s.total_large_buffers,
s.large_buffer_size,
s.dropped_event_count,
s.dropped_buffer_count,
s.largest_event_dropped_size
FROM sys.dm_xe_sessions AS s;
-- Also check log growth rate. Apply filters to remove noise.
-- some filters:
-- [sqlserver].[server_principal_name] = N'name of principal'
-- [sqlserver].[is_system] = (0)
-- [sqlserver].[client_app_name] = N'name of app'
|
...
Some python packages also have dependencies on the OS level packages, so you may be required to install additional OS packages if the below fails to install.to install.
You can download the Latest Core Library and SQL Server whl via Platform Settings → Sources → Download Collectors
...
Run the following command to install the collector
...
The collector requires a set of parameters to connect to and extract metadata from SQL Server.
FIELD | FIELD TYPE | DESCRIPTION | EXAMPLE |
---|---|---|---|
server | string | SQLServer server. If using a custom port append with comma | “10.1.18.19” |
host | string | The onboarded host value in K, generally this would be the same as the server value, depending on what you onboard it as. | “mysqlserver” |
username | string | Username to log into the SQLServer account | “myuser” |
password | string | Password to log into the SQLServer account |
|
databases | list<string> | A list of databases to extract from SQLServer | [“dwh”, “adw”] |
sqlserver_version | string | Version of SQLServer release name, supported is 2012, 2016, 2017, 2019 | 2016 |
driver | string | This is the ODBC driver, generally its ODBC Driver 17 for SQL Server, if you another driver installed please use that instead | “ODBC Driver 17 for SQL Server” |
meta_only | boolean | Do you want to extract metadata only without enabling extended events? | true |
events_path | string | This is the extended events file pattern configuration for SQLServer. Only required if meta_only is false. | “/tmp/eevents*.xel” |
output_path | string | Absolute path to the output location where files are to be written | “/tmp/output” |
mask | boolean | To enable masking or not | true |
compress | boolean | To gzip the output or not | true |
These parameters can be added directly into the run or you can use pass the parameters in via a JSON file. The following is an example you can use that is included in the example run code below.
...
Code Block | ||
---|---|---|
| ||
import os import argparse from kada_collectors.extractors.utils import load_config, get_hwm, publish_hwm, get_generic_logger from kada_collectors.extractors.sqlserver import Extractor get_generic_logger('root') # Set to use the root logger, you can change the context accordingly or define your own logger _type = 'sqlserver' dirname = os.path.dirname(__file__) filename = os.path.join(dirname, 'kada_{}_extractor_config.json'.format(_type)) parser = argparse.ArgumentParser(description='KADA SqlServer Extractor.') parser.add_argument('--config', '-c', dest='config', default=filename, help='Location of the configuration json, default is the config json in the same directory as the script.') parser.add_argument('--name', '-n', dest='name', default=_type, help='Name of the collector instance.') args = parser.parse_args() start_hwm, end_hwm = get_hwm(_typeargs.name) ext = Extractor(**load_config(args.config)) ext.test_connection() ext.run(**{"start_hwm": start_hwm, "end_hwm": end_hwm}) publish_hwm(_type, end_hwm) |
...