| Scroll ignore | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||||||||
About Collectors
| Insert excerpt | ||||||
|---|---|---|---|---|---|---|
|
...
Collector server minimum requirements
| Insert excerpt | ||||||||
|---|---|---|---|---|---|---|---|---|
|
...
INFORMATION_SCHEMA.ROUTINES
INFORMATION_SCHEMA.VIEWS
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
INFORMATION_SCHEMA.TABLES
INFORMATION_SCHEMA.COLUMNS
INFORMATION_SCHEMA.VIEWS
sys.foreign_key_columns
sys.objects
sys.tables
sys.schemas
sys.columns
sys.databases
...
Step
...
| Info |
|---|
Extended Events Setup is in pilot for Azure SQL |
An Azure SQL Admin will need to setup an extended events process to capture Query Execution in SQLServer.
Some tuning of the logging parameters may be needed depending on event volumes generated on your SQLServer instance.
First create or reuse an existing Azure Storage Account.
Then create a blob in the example the blob is called extended-events
Run the following script to setup Extended Events logging.
| Note |
|---|
Apply per database in scope for metadata collection. |
| Code Block | ||
|---|---|---|
| ||
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<REPLACE with your key: abc1234>';
CREATE DATABASE SCOPED CREDENTIAL [https://your.blob.core.windows.net/extended-events]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = '< REPLACE WITH YOUR SAS TOKEN: sp=racwdl ...>';
-- Make sure this file name is unique per database: ADD TARGET package0.event_file (SET filename = N'...'
CREATE EVENT SESSION [KADA] ON DATABASE
ADD EVENT sqlserver.sp_statement_completed (
ACTION(package0.event_sequence, sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_id, sqlserver.database_name, sqlserver.query_hash, sqlserver.session_id, 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.event_sequence, sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_id, sqlserver.database_name, sqlserver.query_hash, sqlserver.session_id, 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'https://your.blob.core.windows.net/extended-events/<REPLACE with your db name: database1>.xel')
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
|
Step 1: Create the Source in K
...
Step 2: Getting Access to the Source Landing Directory
| Insert excerpt | ||||||
|---|---|---|---|---|---|---|
|
...
Example: Using Airflow to orchestrate the Extract and Push to K
| Insert excerpt | ||||||
|---|---|---|---|---|---|---|
|
...