Azure

Polybase configuration on SQL Server 2017 Part II

maart 16, 2018

Nowadays your precious data can be stored everywhere, not just on several servers with different SQL versions, your data probably is wide spread in the cloud. It’s also a good idea to store data in the cloud with stretch database to release your local discs from excessive data and still be able to query it, but also use it in your SSIS and BI environment and keep an acceptable ETL. With Microsoft’s polybase you can access, import and export any data structured, semi, or non structured on the Hadoop platform and azure blob storage using T- SQL language.

The best business knowledge comes from the data you collect. So it might be a good idea to put the data you collect into some good use. Businesses collect lot’s of data, but in most cases this is also where it ends. Those who read my posts before, know I am all about combining various sources with linked servers, since SQL 2014 lot’s of new features are available for using all your data on business intelligence platforms.

In my last post, we had a first look and troubleshoot of a polybase installation. This time we are going to configure and use the polybase in SQL server 2017. I’m going to use the Blob storage on Azure to demonstrate how you can implement this solution in your (local) SQL database.

First things first, now you’ve got your polybase installation ready, check if the services for polybase exist and are running.

Services: ‘SQL Server PolyBase Data Movement’ and ‘SQL Server PolyBase Engine’

You need to configure Polybase in order to start using it. Fire up SSMS and open a new query window. Type

sp_configure ' hadoop connectivity', 4;

reconfigure

Option 4 is Azure blob storage (WASB[S]). For more info on availability of the Polybase connectivity configuration, take a look here. Run the query and make sure you restart both Polybase services on the machine to finish the configuration.

In order to start using the blob storage make sure you have an Azure storage account if you don’t have an Azure account yet, create one here.

Give it some time, once the storage is created, you also need to create a container on the Azure storage.

To connect your local db to azure storage, you need to get the azure storage key from your Azure storage account you just created and put it in the configuration file of your SQL installation.
Look for the core-site.xml file in the installation path of SQL Server.
The path looks simular to this: “C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn\Polybase\Hadoop\conf”
This will open the config directory with the core-site.xml file.

Open the file in notepad and add code before the block of code mentioning Kerebos.

Fill in the storage name, in my case polybasedemo and the storagekey and save the file.

 fs.azure.account.key..blob.core.windows.net

Now we have to create an external data source in SSMS. Replace containername@storagename with the names you created on Azure.

CREATE EXTERNAL DATA SOURCE PolyBaseDemo WITH
( TYPE = HADOOP, -- wasbs:// containername@storagename.blob.core.windows.net/ 
LOCATION = 'wasbs://containername@storagename.blob.core.windows.net/' );

Next up, we create the external file format to define external data on Azure blob storage, this needs to be done in order to create the external table

CREATE EXTERNAL FILE FORMAT PolybaseFormat 
WITH ( FORMAT_TYPE = DELIMITEDTEXT , FORMAT_OPTIONS ( FIELD_TERMINATOR = ',' ) );

This creates 2 new server objects in SSMS and now all is left to create the external table itself. In this demo I use an excel sheet with some irrelevant data to have some test data available.

USE [DemoPolybase] CREATE EXTERNAL TABLE [dbo].[Customers] ( [Name] VARCHAR(255) NULL, [adres] VARCHAR(255) NULL, [postalcode] VARCHAR(6) NULL ) WITH (LOCATION = N’/Customer_Export.csv’, DATA_SOURCE = PolyBaseDemo, FILE_FORMAT = PolybaseFormat, REJECT_TYPE = Value, REJECT_VALUE = 10) GO

And to see if this worked, just query the data 🙂

SELECT * FROM [Customers]

Now put this knowledge into action yourself with some real data!

Azure, Linked server, SQL, SQL Performance

SQL2016 Stretch database. Yes, it’s another linked server

september 17, 2016

Your manager always wants to keep all the data…now with bigdata being a thing and algoritmes are used more efficient (IOT), people want to hoard data even more, but it needs to be online to be valuable. If you have to restore the data first, your data is not very valuable. But all this extra data also slows down your database performance…and gives the dba’ers extra challenges….but not anymore.! Well, at least when your boss got a big wallet and don’t mind spending it on a SQL buget.

In SQL2016 Microsoft introduced the stretch database. which give you live access to ‘archived’ data and makes it feel like it’s on premise. And it’s a secretly just a linked server! A linked server between mssql and Azure. LS+azure

How does this work, and is it safe?

Yes! since it works on secure linked server technology and it stores an encryption key on the on-premises sqlserver, it’s suitable for all your cold data, not hot, because it does slow down the query speed, but it will not clogg your network and your data will be available at all times, even when the data is being stretched over to Azure the data still is fully query-able.

First ask yourself, Do you really need to keep this data? If yes, How do you create a stretchdatabase? Well, this is easy!

Hesitations? use this link for info on the MS Stretch database advisor.

I use a simple test database, the adventureworks2016 ctp3.

Before we can start, check if the data archive function is enabled, if not (0) enable it.

####Check status of Remote data archive####
SELECT @@VERSION AS 'SQL Server Version'; 
EXEC sp_configure 'remote data archive'; 
GO 
####Activate Remote data archive####
EXEC sp_configure 'remote data archive' , '1'; 
GO 
RECONFIGURE; 
GO

Next we can create a master key to encrypt all your data locally, this way, the data can’t be read in Azure.
Note: You can also create a masterkey from the stretchdb wizard, it’s up to you. I’ll show both options.

USE AdventureWorks2016CTP3; 
GO 

CREATE MASTER KEY ENCRYPTION BY PASSWORD='stretchdb2016'; 
GO

Next I will create a simple table and populate it with some data, to keep the demo quick and simple :

CREATE TABLE dbo.Stretchtable
(
FirstName VARCHAR (50),
LastName VARCHAR (50)
);
GO


Use [AdventureWorks2016CTP3]
GO
INSERT INTO dbo.Stretchtable (FirstName, lastName)
VALUES ('Reed', 'Richards'), ('Benjamin', 'Grimm'), ('Sue', 'Storm'), 
 ('Johnny', 'Storm'), ('Victor', 'Von Doom'), ('Willie','Lumpkin');
 GO

Keep your Azure account ready! I use the SQL logical server resource, you need to create a resource before you can stretch the database.

Azure resource

Select the table you will be stretching to Azure. Now enable the table for stretching and the wizard will open up.

Enable stretch database

It’s a Hybride archive – You can stretch the whole table or filter out the (c)old data from a massive table.
4_EnableStretchTable

Next thing you create a master key, if you didn’t make it before, if you made it already, It will ask you to fill in the master password. Masterkey_StretchTable
10_LoginStretchTable

Fill in the IP range for a firewall rule. IP_Azure

12_EnableStretchTable

Hey Ho, let’s go, that was easy!

Now the table will start to stretch, if you want to see if it stretched yet, try a select query and check the executionplan.
14_EnableStretchTable

Want your data back? No problem, You can bring back the data to your on-premise server, if you don’t want to use Azure. Because, when you delete your azure account, the data will be gone too!

bringbackmydata bringbackmydata2 bringbackmydata3

Note: Data storage can be expensive on Azure, but this function gives us a positive view on the ever growing databases and it’s possibilities.

Azure, Linked server, SQL

Linked server to Microsoft Azure #Error 405150

oktober 19, 2015

When Azure was launched, we missed one important feature, the linked server, It seemed impossible to do cross database queries from your local database to the Azure database, but Microsoft listened and made it possible to do database queries that spans multiple databases across platforms.

There is plenty of hits when googled on the errors, but none of these are complete! I will write down the process of linking your server to Azure.

when I first created a linked server to Azure, I got this error “reference to database and/or server name is not supported in this version error: 405150”, When trying to add a linked server from SSMS, you are not given option to set a default database. So I used Tsql instead, because without a specific catalog, your linked server will not work.

Azure doesn’t allow you to alter the master db, so you have to connect directly on the database you are going to use.

What people never tell LS newbies and make you google this error “Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure ‘sp_addlinkedserver’ ” and read tons of blogs and tutorials all providing different queries, but still no work, follow these steps”.

The Tutorial, the Solution:

Open up ODBC, or simply run odbcad32.exe. and start by making a new system DNS with the Native client.

ls_SYSDNS

In the next step, you provide your datasource name and the complete server name as you can find on the Azure server.

LS_makedns

Also provide the database you want to connect to, don’t skip this one!

LS_Choosedb

Test the connection, see it succeed! if not, go through the above printscreens you probably missed a step.

Now we can create the linked server on your database, by creating a query on your SSMS and can run your linked server query, it should look similar to this:

EXEC master.dbo.sp_addlinkedserver @server = N’Azure_server’, @srvproduct=N”, @provider=N’sqlncli’, @datasrc=N’t20ko02v18.database.windows.net’, @catalog=N’Teletraan IV’ EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’Azure_server’, @useself=N’False’,@locallogin=NULL,@rmtuser=N’skyscream’,@rmtpassword=’password’

To test if the LS is added, you could run exec sp_helpserveror just test the connection. LS_SPhelpserver

These are all the steps to a Linked Azure server. Congratulations, you are now connected to the Azure mothership of data.

Data manipulation

Next step is to insert data from the Azure linked server into our local database. Btw, this query works both ways, you could also insert data into the Azure database.

Create an INSERT statement

BEGIN TRAN INSERT INTO [Azure_demo].[dbo].[Demo] (Initials,Prefix,LastName,Gender,Street,HouseNumber ,Zipcode,City,PhoneNumber,MobileNumber,EmailAddress) SELECT Initials,Prefix,LastName,Gender,Street,HouseNumber ,Zipcode,City,PhoneNumber,MobileNumber,EmailAddress FROM [Azure_server].[Teletraan IV].[dbo].[AzureData] WHERE InsertDate BETWEEN ‘2008-08-22 00:00:00.000’ AND ‘2009-02-06 00:00:00.000’ ROLLBACK TRAN –COMMIT TRAN

Now all rows have been added through our linked server into our local database.
If this blog saved your ass or just made your day, please let me know 🙂

Algemeen, Azure, Code, SQL, Tooltip

Connect your Azure database from your local SSMS #Error 40615

oktober 12, 2015

Ofcourse we wanna access our fresh imported database on Azure locally!

And this is so easy if you just follow these simple steps.

In order to be able to access the database located on your Azure cloud, you have to know the Azure address, which you can find on the Windows Azure Management Portal. Click the database icon and open up the database. Here you will find the connectionstring.

Next step is to make sure Azure knows your device by adding it to the trusted devices in Azure, if your device is unknow to Azure it will bring you the following message:

you can add the device by going to the database icon in Azure and click the CONFIGURE tab. Here you add and save your device IP.

Now you can login to your Azure cloud with the credentials you made while creating the database on Azure.

Algemeen, Azure, Code, SQL, Tooltip

SQL database migration to Microsoft Azure with codeplex

oktober 8, 2015

There are multiple ways to migrate your database to the Azure cloud. Today, I am testing this sql azure plugin by codeplex. I unzipped it to my local folder and run it as admin.

Now fire up the SQLAzureMW.exe and the script wizard starts up, which is very intuitive, choose the available options, In my case, I will migrate from SQL database to Azure SQL Database. Fill in your database details and run the export. This might take a while depending on how big your SQL Database is. You can also make a selection of tables/views you want to export, or just export the whole database.

Make sure you fill in the correct details for your target server (you can find your sql connection data on your azure Database configure page.)

Now the database will be brought to the Azure cloud, again this might need some time, but you can see the progress on screen.

I’m a big fan of this sql azure plugin by codeplex , Like i said it’s very intuitive and gives you several options to work with your local and azure data.

Tag: Azure

Polybase configuration on SQL Server 2017 Part II

SQL2016 Stretch database. Yes, it’s another linked server

Linked server to Microsoft Azure #Error 405150

Connect your Azure database from your local SSMS #Error 40615

SQL database migration to Microsoft Azure with codeplex

Categories

Social Profile

Search Website

Recent Posts Thumb