donderdag 23 april 2020

Azure Series : Log analytics introduction

Introduction

Log analytics is part of Azure Monitor and it is a log collection of different services. You need to set up a workspace (You can setup multiple workspaces). In this blogpost I'll setup diagnostic settings for a VM and see if we can use log analytics for some analysis.

There are a couple of resources available for monitoring and newly are added continuously as you can see below.

Setting up diagnostic settings of a VM

At first, we cannot use diagnostics because the VM is not turned on. So, we need to turn the VM on.


Now, first start the VM in order to enable diagnostic settings.


And now we can enable diagnostic settings in the VM and we can use it in Azure monitor.




This will take some time. I had to refresh the window because it kept on saying "busy"..These are the standard settings of the diagnostic settings.


 What to log more? These are some of the other options.


Some more tabs are available but will not go into detail for now. But let's move on to the Azure Monitor Log analytics. Now, we can see that the VM is monitored and used in the Azure Monitor


Some of the Storage accounts are being monitored too.



Now let's try some log analytics queries.  First setup the scope for log analytics.


And now we can write some log analytics queries. Here are some simple log analytics query to retrieve the perf counters of the Computer "azhennievm".


Here is another example where I've gather the logs of 2 minutes ago until now.


I tried some examples of the Microsoft docs.



Resources

Some great resources I found so far about log analytics:


Final thoughts

This tool can be used for monitoring your Azure resources and it is mostly used in administrative tasks in Azure.

Hennie

donderdag 16 april 2020

Azure Series : Synchronization between Azure Databases

Introduction

I have to copy a couple of tables of about 200 million rows between a couple Azure SQL databases, just once or perhaps twice. I don't know it exactly. Now, in SQL Server (on-prem) you have some options and it's fairly easy copying data but in Azure it's a different ballgame. If the data is not very much you can use the "Generate scripts" and choose the option "data only" resulting in scripted data in INSERT statements. I tried the bacpac option in SSMS but I received a lot of validation errors because (perhaps) the database was not in a consistent state. I didn't investigate this much further. One another trick I tried was using SSIS, my old favorite ETL Tool. Although I enjoy the tool a lot, it seems that the integration and alignment with Azure should be better. Although, It is a good backup for my problem, I would like to know if there is somethnig better, easier or faster to use. So my options were starting to get smaller and smaller. Elastic queries could be an option but I have been there, done that before and so I ended up with experimenting with synchronization groups in Azure SQL Databases. This blogpost is a description of the process and investigation on how to setup synchronization between Azure SQL Database. I hope you find it useful and leave me note when you have remarks or questions.

The setup

Firstly, what are synchronization groups in Azure SQL Database? Well, it is a synchronization tool for data synchronization between Azure SQL Databases and on-premise SQL Servers (you have to install an agent). For this blogpost, I am only interested in synchronization between Azure SQL databases.



There are two types of databases: Hubs and Members.  

The configuration

First, create a sync group in the Azure Portal, Navigate to the database and search for "Sync to other databases" and click on that.


Create a new sync group with "New Sync Group"


Enter the Sync group name and I choose to use existing database and all of the databases are shown in the drop down box and I choose the Hub database. The next step is choosing the member database that is used for the synchronization.


The member database is used for the sync member.


And the next step is choosing the tables (and clumns if you wish) to sync from, but in my case it seems like saying for hours: "loading tables"....hmpf...



After a couple of tries and clicking around the following error message appears and now things were getting more and more clearer. The service has nog access to the Azure SQL Database Server.


So I set this option : Allow Azure services to access the server!


And now I recieved another error message, something about a bad login.


After correcting the password, all of a sudden I received a list of tables. I'm not sure but I took some while to manage this, but may be it's just me.


In the next step I can even choose columns for synchronization. There are some not supported columns over there. I leave that for later to investigate.

The execution

All ready and I pressed on the Sync button and some magic happened. The table is synchronized to the other database!


Some logging appears and it seems that the synchronization is succeeded.



Let's take a look in the database, but hey there are some tables in the database I didn't expect and seems a bit awkward. These tables are needed for the synchronization between the databases.


Also in the member database a lot of synchronization (meta) tables were created.



Final thoughts

I expected/hoped that the synchronization of databases is a kind of replacement of the import/export of data of the on premise SQL Server version, a one stop copy and paste method, but it's more like a synchronization tool, as off course the name implies. So for a simple copy action it's usable but you will get a lot of tables in your database unless you use a meta data database. 



Hennie

zaterdag 29 februari 2020

Executing a stored procedure with Powershell

Introduction

I had some troubles finding a good example on how to execute a Stored procedure in Powershell and so I made a couple of examples my own and this blog is the result of some experimentation.

First, let's start with an simple example. Execute a stroed procedure without a parameters. I've created a sample database, a sample table and a sample stored procedure.

DROP TABLE IF EXISTS TestSP
GO

CREATE TABLE TestSP (
id int identity(1,1),
Name varchar(50),
Insertdate  datetime DEFAULT GETDATE()
)
GO

CREATE PROCEDURE InsertTestSP AS
BEGIN 
 INSERT INTO TestSP (Name) VALUES ('HENNIE')
END

EXEC InsertTestSP
GO

SELECT * FROM TestSP

Resulting in a record in the table.


Execute a simple stored procedure

Now let's find out if we can execute the stored procedure from Powershell.

cls
Write-Host "Script started..."

# General settings
$date = Get-Date
$dateString = $date.ToString("yyyyMMddHHmmss") 

# SQL Server settings
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=localhost;Database=TestSPWithPowershell;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "InsertTestSP"
$SqlCmd.Connection = $SqlConnection

$sqlConnection.Open()
$Result = $SqlCmd.ExecuteNonQuery()
$sqlConnection.Close()

And this results in a record in the table.


Execute Stored procedure with a parameter

Now we add a parameter to the stored procedure. First change the stored procedure a bit in order to have a proper testsituation : Parameter added and value is inserted in the table.

DROP PROCEDURE IF EXISTS InsertTestSP 
GO

CREATE PROCEDURE InsertTestSP @name as varchar(50) AS
BEGIN 
 INSERT INTO TestSP (Name) VALUES (@name)
END

EXEC InsertTestSP 'Jack'
GO

SELECT * FROM TestSP

Resulting in :


So that works too. The record with the name 'Jack' is properly inserted into the table with the stored procedure. Let's find out if we can do that with Powershell too.

cls
Write-Host "Script started..."

# General settings
$date = Get-Date
$dateString = $date.ToString("yyyyMMddHHmmss") 

# SQL Server settings
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=localhost;Database=TestSPWithPowershell;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "InsertTestSP @name"
$SqlCmd.Connection = $SqlConnection

$SqlCmd.Parameters.Add("@name",[system.data.SqlDbType]::VarChar) | out-Null
$SqlCmd.Parameters['@name'].Direction = [system.data.ParameterDirection]::Input
$SqlCmd.Parameters['@name'].value = "Jean"

$sqlConnection.Open()
$Result = $SqlCmd.ExecuteNonQuery()
$sqlConnection.Close()

And this results in the following table. And again this works too.


Just a blogpost on how to execute a stored procedure from Powershell. Just enough for my situation at my project.

Hennie





maandag 24 februari 2020

SQL Server 2019 : Login failed with polybase

I was trying to investigate usage of polybase on my local SQL Server 2019 installation, the on -premise version and I was wondering whether I could use Polybase for loading Parquet files into SQL Server and I ran in a couple of errors that I would like to share with you.

The first error I would like to discuss is an obvious one : When creating an EXTERNAL FILE FORMAT in SQL Server an error happens because I've not installed the polybase software.

-- Create an external file format for PARQUET files.  
CREATE EXTERNAL FILE FORMAT Parquet  
WITH (  
    FORMAT_TYPE = PARQUET  
);

This is resulting in an error

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'EXTERNAL'.

Therefore we need to install the "Polybase Query Service for External Data" with the installation disk


and so I did. Now the next thing that is needed is enabling Polybase with the sp_configure command.

exec sp_configure @configname = 'polybase enabled', @configvalue = 1;
RECONFIGURE;

When the above statement is finished the following message appears

Configuration option 'polybase enabled' changed from 0 to 1. 
Run the RECONFIGURE statement to install.

Then, I tried the following statement again, but now again but a different error occurred.

-- Create an external file format for PARQUET files.  
CREATE EXTERNAL FILE FORMAT Parquet  
WITH (  
    FORMAT_TYPE = PARQUET  
);

After a while waiting for some response from SQL Server the following error occurred. Now at least we don't get an error near 'EXTERNAL'.

OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "Login timeout expired".
OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message 
 "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. 
 Server is not found or not accessible. Check if instance name is correct and if SQL Server is 
 configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 10061, Level 16, State 1, Line 10
TCP Provider: No connection could be made because the target machine actively refused it.

A hint from Bob Duffy helped me a bit further. You will need to set TCP/IP protocol to Enabled under SQL Server Network Configuration\Protocols for MSSQLSERVER.  Then restart the main SQL Service so settings take effect, and then the SQL Server PolyBase Engine service should start.

Msg 46721, Level 20, State 1, Line 39
Login failed. The login is from an untrusted domain and 
cannot be used with Integrated authentication.

Now from Pinal Dave I was pointed to that the login was set on Windows Authentication.


But no luck with that adjustment. On Stackoverflow I've found that there is an issue with using Polybase with a non-domain joined machine and I haven't installed an AD controller and so that must be the problem

Are you running the DDL from a local Windows account 
(i.e., non-domain joined machine)? There is a regression in SQL Server 2019 
where you will get this error when trying to use PolyBase. 
We are in the process of fixing the issue in an upcoming CU.

So it seems that the machine should be joined in a Active Directory Domain service or we have to wait for a CU.

Another thing I experienced was that Polybases services was continously stating that they were 'starting' but nothing happens, SQL Server agent was also not restarting because of the Polybase services. Killing the Polybase services and restarting the services did the trick. Off course, take precautions in a non local dev environment, like production systems

As far as I know now it seems that reading local parquet files on disk in SQL Server is not possible. I've seen examples with Hadoop and Blob storage, but no luck finding a solution for reading parquet files into SQL Server.

Hennie

zondag 2 februari 2020

Azure Series : Passing a parameter to an Azure Data Factory pipeline from SQLDW

Introduction

I've the following scenario for you: there are all kind of deliveries of information in an information factory and when these arrives, We want to trigger an event and load tables that are dependent on this information. So I have a process and I have a delivery(number). This is a base for loading certain tables for some of the customers.

So I have a SQL DWH (Synapse now), a Blob Storage and an Azure Data Factory configured. I've created a solution in the following manner:
  1. Create a file in a blobcontainer with the following format '<process>_<deliverynumber>.trg' from within a stored procedure in Azure SQL Data warehouse.
  2. When a file is detected in a blob container an eventtrigger in Azure Data Factory is executed.
  3. This runs an Azure Data Factory pipeline.
  4. The Azure Data Factory Pipeline will gather the filename and splits the process and delivery information from the filename.
  5. This passed to a stored procedure and the data is written in a table (for demo purposes).
This is described in this blogpost.

1. Create a trigger file

The first step is creating a file in the Blob Storage. This is done with the following statement.

IF EXISTS (SELECT * FROM sys.external_tables 
  WHERE object_id = OBJECT_ID('TestHennieCallPipeline') )  
    DROP EXTERNAL TABLE TestHennieCallPipeline 
    
CREATE EXTERNAL TABLE  TestHennieCallPipeline WITH (
    LOCATION = 'TestHennieCallPipeline_123456790.trg',
    DATA_SOURCE = [BLOB_TRIGGER_CONTAINER],
    FILE_FORMAT = [DoublePipeDelimited_FR2_Outbound]
   ) as select 1 as i from [someschema].[sometable] where 1=2;

Excuting this command will create a file in the blobcontainer (and also a directory).


Creating an event trigger

Now, when the file is created an Event trigger in Azure Data Factory is executed. Here is an example of the Event trigger setup.




This trigger has a parameter but it is not possible to create a parameter in the trigger section. This has to be done in the pipeline pane. There is an option to create triggers available for that.


So, choose for Edit/New Trigger and the following window is shown. The pipeline that will respond on the triggerevent 'TestHennie'. Parameters is equal to 1 because I've already created a parameter.


Click on the name, in this case 'TestHennie', and click two times on Continue until the following screen appears. Here you can enter a parameter and I've included a system variable here.



The system variable is needed for retrieving the name of the file.

@triggerBody().fileName

Create a ADF Pipeline

One of the next steps is creating an Azure Data Factory pipeline for this experiment, I have created two activities: a set variable and stored procedure activity.

The first one is for setting values in variables and the other one is for debugging. The stored procedure will insert the data into a table.

Create a parameter in the pipeline

Creating a parameter in the pipeline is the following step in this experiment. I've created a parameter SourceFile. This parameter is used in the trigger.



Create a user variable in the pipeline

Now in order to do some manipulation of the filename that is retrieved and stored in the parameter, I've added an user variable to the pipeline. This will be used in the Set Variable activity.


Set the variable as a parameter to a stored procedure

Then, I've created an expression in the Set Variable Activity. In this actvity it's possible to use different kinds of funtions and these can be mixed together in order to get the desired result.




Next, I've built an expression and it would be nicer if I could have used less functions and perhaps it's possible. But for now, it will do.

@substring(pipeline().parameters.SourceFile, 
    add(indexof(pipeline().parameters.SourceFile,'_'),1),
    sub(indexof(pipeline().parameters.SourceFile,'.'), 
     add(indexof(pipeline().parameters.SourceFile,'_'),1)
    )
    )

Pass the variable as a parameter to a stored procedure

I've created a stored procedure that adds a record to a table. This way it's possible to investigate the values.



Here the simple table and procedure and some scripting

CREATE TABLE _Hennie (
  Deliverynumber  nvarchar(50)
  )

SELECT * FROM  _Hennie

CREATE PROCEDURE TestHennie (@deliverynumber varchar(50))
AS
BEGIN
 INSERT INTO _Hennie (Deliverynumber) VALUES (@deliverynumber)
END


TRUNCATE TABLE  _Hennie

And the result is that the deliverynumber is derived from the file name based on the expression and written in the table.



Thats it!