Advertisements

Archive

Posts Tagged ‘Polybase’

Polybase error in SQL Server 2016 : Row size exceeds the defined Maximum DMS row size, larger than the limit of [32768 bytes]


I got an email form one of my reader regarding issues while working with SQL Server 2016 and Polybase, and it is as follows:
 

I am able to successfully install SQL with Polybase and able to query data in Azure storage but for a table I am getting error.

I am trying to pull data by creating External Data Source connection in SQL enabled Polybase features. I am getting below error as:

Cannot execute the query “Remote Query” against OLE DB provider “SQLNCLI11” for linked server “(null)”. 107093;Row size exceeds the defined Maximum DMS row size: [40174 bytes] is larger than the limit of [32768 bytes]


 

With the error description its quiet evident that the External tables does not support row size more than 32768 bytes. But still I take a look online and found in Azure Documentation that this is a limitation right now with Polybase. The Azure document mentions:

Wide rows support is not supported yet, “If you are using Polybase to load your tables, define your tables so that the maximum possible row size, including the full length of variable length columns, does not exceed 32,767 bytes. While you can define a row with variable length data that can exceed this figure, and load rows with BCP, you will not be be able to use Polybase to load this data quite yet. Polybase support for wide rows will be added soon. Also, try to limit the size of your variable length columns for even better throughput for running queries.”

link: https://azure.microsoft.com/en-us/documentation/articles/sql-data-warehouse-develop-table-design/


Advertisements

Installing SQL Server 2016 CTP2 | and new features in SSMS

May 29, 2015 4 comments

SQL Server 2016 was released as the Community Technology Preview (CTP) 2 just yesterday (27th May 2015). And in my [previous post] I discussed about it with the Registration and Direct Download link.

After downloading the bits I installed it on my machine and the setup process was very smooth with few changes, check the YouTube video or go through the post below:
 


 

1. After you execute the Setup.exe: you will be greeted with the similar Install window You just need to click on the Installation link on the left and then click on the first link shown below:
SQL Server 2016 Install 00

2. This will open up a new Setup window and takes you to the Setup Role page which this gives you 2 options, 1st Feature (custom) install and 2nd Default. I chose the first option to select only the features I want. Click Next.

3. Now on the Feature Selection page: you can select what exactly you want to work on. You can see here one more option PolyBase Query Service for External Data. Just select the features and click Next:
SQL Server 2016 Install 02

4. I got stuck at the the Feature Rule page: with this error Oracle JRE 7 Update 51 or higher is required, Failed. Just click on the Failed link and it will give you the URL from where you can download the JRE or Java Runtime Environment, download it from [here].
SQL Server 2016 Install 03
… the above Page also shows the limitation of Polybase that it will run only in one Instance per computer.

Re-run the rules and it will be Passed this time, click Next.

5. You will come to Instance Config page: If its is first SQL Server install on your PC then select as Default Instance, otherwise if you had installed SQL Server earlier you will have to choose a Named Instance and Click Next.

6. In the Database Engine Config page: you will see an extra option to set number of TempDB files. The label below mentions “The default value is 8 or the number of cores, whichever is lower. This value can be increased up to the number of cores”.
SQL Server 2016 Install 06

Update: Now with CTP 2.4 release the installer provides a separate tab for tempdb files configuration.

SQL Server 2016 CTP 2.4 Setup 02

So, in my [C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER2016\MSSQL\DATA\] folder I could see 8 data files, with 1 log file:
SQL Server 2016 Install 07

7. on Ready to Install page just click Install, and it will take some time to finish the installation.
 

What’s new in SSMS:

–> After finishing the installation I opened the SQL Server Management Studio 2016: and the object explorer looked like this:
SQL Server 2016 SSMS 01

You will see three Databases present by default:
1. DWConfiguration
2. DWDiagnostics
3. DWQueue

… some of these tables in these databases contains pdw prefix, these tables & databases are related to Polybase feature. I will blog about this feature in my coming posts as in when I get more information on this.
 

–> I created a new Database by name TestManDB, and I got this while expanding some Objects in Object Explorer:
SQL Server 2016 SSMS 02

– – On expanding Tables you can see 2 new table options:
1. System Tables
2. External Tables – for Polybase to query Non-Relational data (also relational)

– – There is a new category External Resources (for Polybase), on expanding you get:
1. Data Sources
2. File Formats

– – And as you expand Stored Procedures folder you can see a SP present by default with following name under pdw schema:
pdw.instpdw, with one parameter @DatabaseName NVARCHAR(MAX).
 

–> There is one hidden feature in SSMS, i.e. Query Store, check this [blog post] on how to enable it fr a database.


SQL Server 2014 CTP1 now Available… download now!!!

June 25, 2013 4 comments

SQLServer2014_Banner

Finally the wait is over to get your hands dirty on SQL Server 2014 as the first Community Test Preview (CTP1) is released and is available for download form the Microsoft Official Site.

–> Download the SQL Server 2014 CTP1 [here].

You will need to login with your Windows Live ID to go to the download page.

–> Microsoft has released following Installation Limitations for SQL Server 2014 Community Technology Preview 1 (CTP1):

  • Microsoft SQL Server 2014 CTP1 is pre-release software and should be installed only on a clean machine. It does not support upgrade from or side-by-side installations with any previous version of SQL Server, including SQL Server 2012. In addition it does not work with or support side by side installations with any client redistributables of SQL Server such as feature pack components, Visual Studio 2012 or earlier versions, SQL Server Data Tools, Data Tier Application Framework, etc.
  • To administer Microsoft SQL Server 2014 CTP1 use only SQL Server Management Studio that ships as a part of Microsoft SQL Server 2014 CTP1.
  • The Microsoft SQL Server 2014 CTP1 release is NOT supported by Microsoft Customer Services and Support (CSS).
  • The Microsoft SQL Server 2014 CTP1 release is only available in the X64 architecture.
  • The Microsoft SQL Server 2014 CTP1 release is a NON-PRODUCTION release and should not be installed and used in production environments.
  • The Microsoft SQL Server 2014 CTP1 release does NOT have an associated Microsoft SQL Server 2014 CTP1 Feature Pack.
  • The Microsoft SQL Server 2014 CTP1 release on Windows Azure VM is only intended for functional development and testing, and not for performance testing.
  • The Microsoft SQL Server 2014 CTP1 release contains branding and versioning from older Microsoft SQL Server releases.

–> System Requirements to install SQL Server 2014 CTP1: [here]

  • Minimum Windows 8 or Windows Server 2012
  • X64 architecture only
  • MS.Net Framework 3.5 SP1

–> Product Detail Page, [link].

Imp Note: As the first limitation point says to install it on a Clean Machine, this means the machine should not have any Pre-Releases of SQL Server and/or Visual Studio Installed.

So, to start with SQL Server 2014 CTP1 you need to get a clean machine, or a new machine with a freshly installed Windows OS.

Check more on SQL Server 2014 CTP1, here: http://www.microsoft.com/en-us/sqlserver/sql-server-2014.aspx

SQL Server vNext (2012) coming up with lot a new features | Hekaton, Polybase, PDW, and many more

November 27, 2012 1 comment

This year’s PASS Summit Microsoft announced lot of new features coming up for “SQL Server 2012” and version vNext.
 

1. Released SQL Server 2012 Service Pack 1: with bug fixes and lot of improvements, like Selective XML Indexes & enhancements in Self-Service BI & Excel 2013.

 

2. Column Store Indexes: will be extended to be created with Clustered Indexes as well.


 

3. Hekaton & Polybase: will be major ingredients in SQL Server vNext by 2014-15.


 

4. SQL Server version Next: will use Hekaton for its OLTP database to take database objects into in-memory and “memory optimize” tables, thus challenging SAP-Hana and Oracle much hyped Exadata soluition.


 

5. SQL Server 2012 PDW: (Parallel Data Warehouse) will be using Polybase to interact between PDW and Hadoop clusters.


 

… I’ll be discussing more about these things in next posts, so keep tuned!!!