Loading data to Azure SQL Data Warehouse - Attunity CloudBeam

Just a short time ago the Azure SQL Data Warehouse was announced. It is still in public preview and
you already have Attunity lining up to load your data to the warehouse.

Actually Azure SQL data Warehouse is their latest offering and they already have a similar service for

• Amazon Redshift
• Amazon RDS
• Amazon S3

Cloud supports democratic arrangements and everybody can have a piece of the cake.

Attunity CloudBeam beams up your data to the Azure SQL data Warehouse from many enterprise databases quickly, easily cost-effectively.

Here is the big picture.

Here are the features:

• High-performance data transfer
• Automatic recovery, security, auditing & reporting
• Real-time, scheduled, or on-demand task execution
• No need to install software on source / target machines
• Graphical user interface for easy configuration
• Schema & DDL replication
• Flexible target database schema mapping and transformations
• Content-based filtering

These are the benefits if you go for it:

• Database to Azure SQL Data Warehouse loading continuously
• Quick transfer speeds with guaranteed delivery
• Intuitive administration and scheduling
• Data integrity is assured by way of check mechanisms
• Monitoring for peace-of-mind, control, and auditing
• Industry-standard SSL encryption for security

Read more here and here.

Cost effective Data Warehousing with Azure SQL Data Warehouse

Azure SQL Data Warehouse Service is now in preview. You have to go to the preview portal to get started. If this is your new account, you can have a free trial.

Following on the heels of April's Build Conference Microsoft has announced the enterprise-class elastic data warehouse as a service- the Azure SQL Data Warehouse available for public preview.
The driving forces are of course the elasticity and cost effectiveness. If you have already invested in Azure cloud you have at your disposal Azure related tools, utilities and skills.

With SQL Data Warehouse Microsoft is offering:

• The first enterprise-class elastic data warehouse that includes the separation of compute and storage, enabling customers to pay for what they need, when they need it
• The ability to pause the database so you only pay for commodity storage costs
• Full SQL Server experience that includes PolyBase that allows you to combine queries for your structured and unstructured data using the skills you have today 
• Hybrid options – your data, your platform, your choice

Azure SQL Data Warehouse Service is based on massive, parallel processing architecture and integrates with PowerBI and Azure Machine Learning for advanced analytics. It also integrates with Azure Data Factory for event processing and its big data offering, the Azure HD Insight.
Here is the big picture:

You need to get to the preview portal to get started.

 

How do you query a database using PowerShell?

You can invoke the SQLCMD from PowerShell with the invoke-sqlcmd commandlet.

MSDN defines Invoke-SQL CMD thus,

"Invoke-Sqlcmd is a SQL Server cmdlet that runs scripts that contain statements from the languages (Transact-SQL and XQuery) and commands that are supported by the sqlcmd utility."

Now as to the exact procedure, you need to get to Powershell with the SQL Server snap-ins.

If you run the SQLPS.exe which is installed with your SQL Server installation(SQL Server 2012 for example) then you have got yourself a good start.

 

Double clicking SQLPS.exe in your computer will bring up the PowerShell command line for SQL Server as shown for both 2012 and 2016 CTP2 versions of SQL Server.

 

 

 

sqlps2012.png

sqlps2016ctp2.png

I will be using the 2012 version for this post.
Now you need to run the invoke-sqlcmd commandlet as shown. This is specific for my installation of SQL Server and you need to change parameters around. I have a SQL Server named instance RegencyPark on my laptop called Hodentek8 and I have chosen Windows authentication for access.
Now I run the following statement:
PS SQLSERVER:\> invoke-sqlcmd -ServerInstance Hodentek8\RegencyPark -Database AdventureWorks2012 -Query "Select top 2 * from Person.person"

I immediately get a response to my query as shown:
-----------------------------------
PS SQLSERVER:\> invoke-sqlcmd -ServerInstance Hodentek8\RegencyPark -Database AdventureWorks2012 -Query "Select top 2 * from Person.person"

BusinessEntityID      : 1
PersonType            : EM
NameStyle             : False
Title                 :
FirstName             : Ken
MiddleName            : J
LastName              : Sánchez
Suffix                :
EmailPromotion        : 0
AdditionalContactInfo :
Demographics          : <IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adv
                        enture-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></Indi
                        vidualSurvey>
rowguid               : 92c4279f-1207-48a3-8448-4636514eb7e2
ModifiedDate          : 2/8/2003 12:00:00 AM
BusinessEntityID      : 2
PersonType            : EM
NameStyle             : False
Title                 :
FirstName             : Terri
MiddleName            : Lee
LastName              : Duffy
Suffix                :
EmailPromotion        : 1
AdditionalContactInfo :
Demographics          : <IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adv
                        enture-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></Indi
                        vidualSurvey>
rowguid               : d8763459-8aa8-47cc-aff7-c9079af79033
ModifiedDate          : 2/24/2002 12:00:00 AM
That is all there is to it.

The most popular relational SQL database



SQlite is a serverless SQL Database that does not require a separate server process. You do not need a configuration (Zero configuration) and it does not need any administration. The whole database is stored in a single cross-platorm disk (or on your mobile device). It is very popular and widely used with mobile applications as the database is stored in the device and can be used without network connection. No external dependencies.

SQLite as opposed to client/server relational database engines is serverless and it's use is indicated where efficency, reliability, independence and simplicity are required and it does not look for a shared repository. SQLite trasactions are fully ACID-compliant. SQLite supports most query languages that are SQL92 compliant and it is written in ANSI-C which provides simple APIs.

SQLite is available on Linux, Mac OS-x, Android, iOS and Windows 32, WinCE and WinRT platforms.

SQLite download from here:
http://sqlite.org/download.html
Read more here:

http://sqlite.org/

Querying a SQL Server database with SQLCMD

Commandline access to the program is perhaps one of the nicest features any program can have. They can do most things you want done as long as you understand the switches and when & how to use them. A good wizard can take you to most of the things these days, but cannot beat the commandline. Did I hear you say, short and sweet?

SQLCMD is a commandline tool for working with SQL Server. Well regarding the various arguments(switches) it takes are obtained from this help file.

Switches.png

Now let me start SQLCMD with the switch -S. S stands for Server. In the present case, the SQL Server Instance 'RegencyPark' is installed in my Windows 8.1 laptop named 'Hodentek8'. I will then ask the program to change the database context to 'Northwind', the default is 'master' using the statement 'Use Northwind and Go'. Now follow the script here as I query the Customers table:

C:\Users\Jayaram>sqlcmd -S Hodentek8\RegencyPark
1> Use Northwind
2> go
Changed database context to 'Northwind'.
1> Select * from Customers
2> Where CompanyName like 'A%'
3> GO
CustomerID CompanyName                              ContactName                    ContactTitle
              Address                                                      City            Region
       PostalCode Country         Phone                    Fax
---------- ---------------------------------------- ------------------------------ -----------------
------------- ------------------------------------------------------------ --------------- ---------
------ ---------- --------------- ------------------------ ------------------------
ALFKI      Alfreds Futterkiste                      Maria Anders                   Sales Representat
ive           Obere Str. 57                                                Berlin          NULL
       12209      Germany         030-0074321              030-0076545
ANATR      Ana Trujillo Emparedados y helados       Ana Trujillo                   Owner
              Avda. de la Constitución 2222                                México D.F.     NULL
       05021      Mexico          (5) 555-4729             (5) 555-3745
ANTON      Antonio Moreno Taquería                  Antonio Moreno                 Owner
              Mataderos  2312                                              México D.F.     NULL
       05023      Mexico          (5) 555-3932             NULL
AROUT      Around the Horn                          Thomas Hardy                   Sales Representat
ive           120 Hanover Sq.                                              London          NULL
       WA1 1DP    UK              (171) 555-7788           (171) 555-6750
(4 rows affected)
1>
-----------------------------------------
If you type EXIT for the last line above, you will return to:
C:\Users\Jayaram>

Powershell Connection string to an instance of SQL Server 2012 on local computer

We are assuming a named instance 'RegencyPark' on a laptop machine named 'Hodentek8'. The Connection string used for connecting to this instance using PowerShell is in the string variable $ConnectionString:


$ConnectionString="Server=Hodentek8\RegencyPark;database=AdventureWorks2012;trusted_connection=true;"

Windows PowerShell ISE provides intellisense support and for the Connection provides the following drop-down:



You can use the following statements to connect to the instance and open the connection (Properties and Methods):



$ConnectionString="Server=Hodentek8\RegencyPark;database=AdventureWorks2012;trusted_connection=true;"
$Connection = New-Object System.Data.SQLClient.SQLConnection($ConnectionString)
$Connection.Open()
$Connection.State
The last statement when executed displays 'Open'

You can further looks at the properties of the connection string as shown here:
------
PS C:\Users\Jayaram>
 $ConnectionString="Server=Hodentek8\RegencyPark;database=AdventureWorks2012;trusted_connection=true;"

PS C:\Users\Jayaram>  $Connection = New-Object System.Data.SQLClient.SQLConnection($ConnectionString)

PS C:\Users\Jayaram>  $Connection.Open()

PS C:\Users\Jayaram>  $Connection.State
Open

PS C:\Users\Jayaram>  $Connection.Database
AdventureWorks2012

PS C:\Users\Jayaram>  $Connection.Credential

PS C:\Users\Jayaram>  $Connection.DataSource
Hodentek8\RegencyPark

PS C:\Users\Jayaram>  $Connection.ConnectionTimeout
15

PowerShell: Cannot connect to SQL Server 2012, Why?

I Recently changed the windows login.

SQL Server 2012 using old login and did not launch---Good
Corrected the login info in (Control Panel)services--OK
Can launch SSMS and connect to named instance Hodentek8/RegencyPark--Good
--------------
I can start PowerShell in the context of the named instance in SSMS (this, I suppose assumes I am already connected to the instance)--Good
I can even create a new database using these:
--
PS SQLSERVER:\SQL\HODENTEK8\REGENCYPARK> $server= new-object Microsoft.Sqlserver.management.smo.server  'Hodentek8\RegencyPark'
PS SQLSERVER:\SQL\HODENTEK8\REGENCYPARK> $dbname="Jun14"
PS SQLSERVER:\SQL\HODENTEK8\REGENCYPARK> $db=new-object  Microsoft.Sqlserver.management.smo.Database ($server,$dbname)
PS SQLSERVER:\SQL\HODENTEK8\REGENCYPARK> $db.Create()
PS SQLSERVER:\SQL\HODENTEK8\REGENCYPARK>
I verify Jun14 database created in SSMS--Good
--
However when I try to run a query, I get the 'named pipes' related error--Not good
I check the client protocols in SQL Server Configuration manager; Client protocol 'Named Pipes' is enabled--Good
Stop and start the instance.--Good
The error persists--Not good
I am stuck!!

Here are the PowerShell statements to check status of the server:
--------------
PS SQLSERVER:\SQL\HODENTEK8\REGENCYPARK> Get-Service -name *sql* | Where-object{$_.Status -eq "Running"} | Format-Table -AutoSize

Status  Name                        DisplayName
------  ----                        -----------
Running MSSQL$REGENCYPARK           SQL Server (REGENCYPARK)
Running MSSQLFDLauncher$REGENCYPARK SQL Full-text Filter Daemon Launcher (REGENCYPARK)
Unfinished troubleshooting:

This error:
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Named Pipes Provider: Could not open a connection to SQL Server [2]. .
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible.
I will post or edit this if I can resolve this.

Running queries against both structured and unstructured data

It is a Microsoft Data tool. It simplifies management of relational and non-relational data with the ability to query both.

With enormous amount of data that is unstructured that grows bigger each day and structured data both archived and live, a single querying technology is most desired. SQL Server has geared up with a new tool, the Polybase.

You want to query non-relational data. Do you modify it and bring it into SQL Server which is relational and then query it? Or do you buy another product to query non-relational data (like data in Hadoop, blobs and files)?

Well Polybase provides the capability to query non-relational data in-situ using the SQL Server using T-SQL. You need not move the data over to SQL Server although SQL Server gives the option to store in SQL Server if you want to do so. Polybase is supported out of the box in SQL Server 2016 CTP2 which means it will be available in SQL Server 2016.

Polybase was not supported out of the box in earlier version. Of course Polybase can process the queries whether it is on the premises or in the cloud.

Here is a rough schematic of what it is about.