Azure SQL (via Collector method) - v3.0.0

Open in new tab

About Collectors

Collector Method

Pre-requisites

Collector server minimum requirements

SQL Server Requirements

Setting up SQL Server for metadata extraction is a 2 step process.

Step 1: Establish SQLServer Access

Apply in MASTER using an Azure SQL Admin user

CREATE LOGIN kadauser WITH password='PASSWORD';
CREATE USER kadauser FROM LOGIN kadauser;

Apply per database in scope for metadata collection.

CREATE USER kadauser FROM LOGIN kadauser;
GRANT VIEW DEFINITION TO kadauser;
GRANT VIEW DATABASE STATE to kadauser;
GRANT CONTROL to kadauser;  -- required for extended events sys.fn_xe_file_target_read_file

The following table should also be available to SELECT by the user created in each database

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 2: Setup Extended Event Logging

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.

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

Create a source in K

Go to Settings, Select Sources and click Add Source
Select “Load from File” option
Give the source a Name - e.g. SQLServer Azure Production
Add the Host name for the SQLServer Azure Instance
Click Next & Finish Setup

Step 2: Getting Access to the Source Landing Directory

Collector Method

Step 3: Install the Collector

It is recommended to use a python environment such as pyenv or pipenv if you are not intending to install this package at the system level.

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.

You can download the Latest Core Library and Azure SQL whl via Platform Settings → Sources → Download Collectors

Run the following command to install the collector

You will also need to install the corresponding common library kada_collectors_lib-x.x.x for this collector to function properly.

Step 4: Configure the Collector

The collector requires a set of parameters to connect to and extract metadata from SQLServer Azure.

FIELD	FIELD TYPE	DESCRIPTION	EXAMPLE

FIELD	FIELD TYPE	DESCRIPTION	EXAMPLE
server	string	SQLServer Azure server. If using a custom port append with comma `Example: mydatabase.database.windows.net,5678`	“mydatabase.database.windows.net”
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.	“mydatabase.database.windows.net”
username	string	Username to log into the SQLServer Azure account	“myuser”
password	string	Password to log into the SQLServer Azure account
databases	list<string>	A list of databases to extract from SQLServer Azure	[“dwh”, “adw”]
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? We currently only support true	true
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
events_name	string	The created extended event session name for each database, the event name should be exactly the same per database. This needs to be specified when meta_only is false	KADA

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.

kada_sqlserver_azure_extractor_config.json

Step 5: Run the Collector

The following code is an example of how to run the extractor. You may need to uplift this code to meet any code standards at your organisation.

This can be executed in any python environment where the whl has been installed.

This code sample uses the kada_sqlserver_azure_extractor.py for handling the configuration details

Advance options:

If you wish to maintain your own high water mark files elsewhere you can use the above section’s script as a guide on how to call the extractor. The configuration file is simply the keyword arguments in JSON format. Refer to this document for more information Collector Integration General Notes | Storing HWM in another location

If you are handling external arguments of the runner yourself, you’ll need to consider additional items for the run method. Refer to this document for more information Collector Integration General Notes | The run method

username: username to sign into sqlserver
password: password to sign into sqlserver
server: sqlserver host
host: the onboarded host value in K, generally it will be the same as the server
driver: sqlserver driver name
databases: list of databases to extract
output_path: full or relative path to where the outputs should go
mask: To mask the META/DATABASE_LOG files or not
compress: To gzip output files or not
meta_only: To extract without extended events enabled
events_name: Extended events session name

Step 6: Check the Collector Outputs

K Extracts

A set of files (eg metadata, databaselog, linkages, events etc) will be generated. These files will appear in the output_path directory you set in the configuration details

High Water Mark File

A high water mark file is created in the same directory as the execution called sqlserver_azure_hwm.txt and produce files according to the configuration JSON. This file is only produced if you call the publish_hwm method.

Step 7: Push the Extracts to K

Once the files have been validated, you can push the files to the K landing directory.

You can use Azure Storage Explorer if you want to initially do this manually. You can push the files using python as well (see Airflow example below)

Example: Using Airflow to orchestrate the Extract and Push to K

Collector Method