Installing SQL Server 2016 CTP2 | and new features in SSMS


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 we will see here.

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 the features I want:
SQL Server 2016 Install 01

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

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.
 

–> After finishing the installing 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, which means 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 (or System-Versioned Tables) – also known as Temporal 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 you can see a SP present by default with name pdw.instpdw, with one parameter @DatabaseName NVARCHAR(MAX).


Microsoft SQL Server 2016 Public Preview (CTP2) Available – download now

May 28, 2015 1 comment

Just got an email from Microsoft:

Microsoft SQL Server 2016 Public Preview Available – Try it Today!
 

[Register and Download CTP-2 Evaluation version (180 days)]

 

Direct download link:
SQLServer2016CTP2-x64-ENU.box
SQLServer2016CTP2-x64-ENU.exe
 

SQLServer2016CTP2
 

Microsoft SQL Server 2016, the next major release of Microsoft’s flagship database and analytics platform, provides breakthrough performance for mission critical applications and deeper insights on your data across on-premises and cloud. The first public preview, SQL Server 2016 Community Technology Preview (CTP) 2, is now available for you to download to trial via Microsoft Azure.
 

–> Try it today for an early look at these new capabilities:

– Always Encrypted: helps protect data at rest and in motion

– Stretch Database: dynamically stretch your warm and cold transactional data to Microsoft Azure

– Real-time Operational Analytics: our in-memory technologies are enhanced to provide real-time analytics on top of breakthrough transactional performance
 

–> Additional capabilities include:

1. PolyBase: More easily manage relational and non-relational data with the simplicity of T-SQL.

2. AlwaysOn Enhancements: Achieve even higher availability and performance of your secondaries, with up to 3 synchronous replicas, DTC support and round-robin load balancing of the secondaries.

3. Row Level Security: Enables customers to control access to data based on the characteristics of the user. Security is implemented inside the database, requiring no modifications to the application.

4. Dynamic Data Masking: Supports real-time obfuscation of data so data requesters do not get access to unauthorized data. Helps protect sensitive data even when it is not encrypted.

5. Native JSON support: Allows easy parsing and storing of JSON and exporting relational data to JSON.

6. Temporal Database support: Tracks historical data changes with temporal database support.

7. Query Data Store: Acts as a flight data recorder for a database, giving full history of query execution so DBAs can pinpoint expensive/regressed queries and tune query performance.

8. MDS enhancements: Offer enhanced server management capabilities for Master Data Services.

9. Enhanced hybrid backup to Azure: Enables faster backups to Microsoft Azure and faster restores to SQL Server in Azure Virtual Machines. Also, you can stage backups on-premises prior to uploading to Azure.
 

–> Other Benefits:

1. Enhanced in-memory performance provide up to 30x faster transactions, more than 100x faster queries than disk based relational databases and real-time operational analytics

2. New Always Encrypted technology helps protect your data at rest and in motion, on-premises and in the cloud, with master keys sitting with the application, without application changes

3. Built-in advanced analytics provide the scalability and performance benefits of building and running your advanced analytics algorithms directly in the core SQL Server transactional database

4. Business insights through rich visualizations on mobile devices with native apps for Windows, iOS and Android

5. Simplify management of relational and non-relational data with ability to query both through standard T-SQL using PolyBase technology

6. Stretch Database technology keeps more of your customer’s historical data at your fingertips by transparently stretching your warm and cold OLTP data to Microsoft Azure in a secure manner without application changes

7. Faster hybrid backups, high availability and disaster recovery scenarios to backup and restore your on-premises databases to Microsoft Azure and place your SQL Server AlwaysOn secondaries in Azure
 

Learn more about SQL Server 2016: SQL Server 2016 Preview Page


Native JSON support – new feature in SQL Server 2016


In my [previous blog post] on “SQL Server 2016 Enhancements” we discussed about the new features coming in. Native JSON support is on of those feature that will help you support and manage NoSQL/Unstructured document data within a SQL Server database.
 

–> JSON (or JavaScript Object Notation) is a popular, language-independent, lightweight data-interchange format used in modern web and mobile applications, as well for storing Unstructured data. JSON is an alternate to XML and is more compact than that format, and thus has become the first choice for many applications that need to move data around on the web. One of the biggest reasons JSON is becoming more important than XML is that XML has to be parsed with an XML parser, while JSON can be parsed by a standard JavaScript function. This makes it easier and faster than working with XML.

For more details on JSON check at json.org

–> JSON in SQL Server 2016 is not treated as a separate Datatype by the DB Engine, like XML. For example, appending FOR JSON AUTO to a standard SELECT statement returns the result set in a JSON format. The JSON data is stored as NVARCHAR type, unlike XML datatype for XML data, thus JSON will be supported wherever NVARCHAR is supported.

–> Here is a sample & simple query to convert a row-set into a JSON format:

SELECT TOP 10 
	M.ProductModelID, 
	M.Name AS [ProductModel.Name],
	ProductID, 
	P.Name AS [Product.Name], 
	ProductNumber, 
	MakeFlag
FROM Production.Product P
INNER JOIN Production.ProductModel M 
ON P.ProductModelID = M.ProductModelID 
FOR JSON PATH, ROOT('ProductModel') -- here, JSON syntax similar to XML

Check the last line, the syntax is almost similar to XML datatype. So, if you familiar with XML syntax in T-SQL, working with JSON will be a seamless experience.

Check MSDN blog on JSON support in SQL Server 2016 http://blogs.msdn.com/b/jocapc/archive/2015/05/16/json-support-in-sql-server-2016.aspx
 

–> More from MS BoL and whitepapers on JSON support:

JSON syntax is simple and human-readable. JSON values consist of name/value pairs, and individual values are separated by commas. Objects are containers of one or more name/value pairs and are contained within curly brackets. JSON arrays can contain multiple objects, and arrays are contained within square brackets.

JSON is the storage format used in several NoSQL engines, including Azure DocumentDB. DocumentDB uses Azure Blob storage to store schema-less documents, but provides a rich SQL query dialect that allows you to conduct SQL queries over the data contained in the documents. In addition to DocumentDB, Azure Search also utilizes JSON. Azure Search is a fully managed search solution that allows developers to embed sophisticated search experiences into web and mobile applications without having to worry about the complexities of full-text search and without having to deploy, maintain, or manage any infrastructure.

The combination of SQL Server’s new support for JSON with these other Microsoft tools enables many scenarios for moving data back and forth between relational and schema-less storage and the applications that access such data. For example, these tools would allow you to set up periodical extractions of relational data in SQL Server, transforming the data into JSON and loading it to a JSON-based Azure DocumentDB storage that is searchable from a mobile device, along with data from many other sources, utilizing Azure Search.


SQL Myth | Nested Transactions in SQL Server and hidden secrets


There is no concept of Nested Transactions in SQL Server, Period.

There can be workarounds but Nested Transactions is not out of the box.

–> In my [previous post] we saw a scenario where you have a Nested Transaction. And we saw issues with Rolling back the inner Transaction and handling them gracefully with the Outer Transactions, which typically looked like this:

Nested Trsansaction

 
–> Here we will see how the nested Transactions behave internally by executing these SQL statements in chunks.

1. First of all we will create a sample table and execute the code till BEGIN outerTran section:

USE [tempdb]
GO

CREATE TABLE dbo.TranTest (ID INT)
GO

CHECKPOINT
GO

-- Outer Transaction - BEGIN
SELECT @@TRANCOUNT AS 'outerTran Begin', count(*) from dbo.TranTest -- 0, 0
BEGIN TRANSACTION outerTran

INSERT INTO dbo.TranTest values(1)
SELECT @@TRANCOUNT AS 'outerTran Begin', count(*) from dbo.TranTest -- 1, 1

– This was a simple one, one row got inserted with Transaction Count = 1, but its not committed yet.

– Let’s see the Transaction status by using the undocumented function fn_dblog(), how these are tracked in the DB Engine:

SELECT Operation, [Transaction ID], Description, Context
FROM fn_dblog(NULL, NULL) WHERE [Transaction ID] IN (
	SELECT [Transaction ID] 
	FROM fn_dblog(NULL, NULL) 
	WHERE [Description] like '%Tran%')

Nested Trsansaction fb_dblog 01

As you can see in the output above:
– The Outer BEGIN TRANSACTION statement is logged as LOP_BEGIN_XACT Operation and Description = ‘outerTran;0x01…’ for the outer Transaction.
– and INSERT statement is logged as LOP_INSERT_ROWS operation.
 

2. Now let’s execute the next section with the inner Transaction:

	-- Inner Transaction
	BEGIN TRANSACTION innerTran

	INSERT INTO dbo.TranTest values(2)
	SELECT @@TRANCOUNT AS 'innerTran Begin', count(*) from dbo.TranTest -- 2, 2

	COMMIT TRANSACTION innerTran
	SELECT @@TRANCOUNT AS 'innerTran Rollback', count(*) from dbo.TranTest -- 1, 2

– The first SELECT statement before the COMMIT statement gives count of two for the inserted rows in the table, with Transaction Count = 2.
– And after COMMIT Transaction the second SELECT statement gives Transaction count = 1.

– Let’s execute the same function and see the Transaction status:

SELECT Operation, [Transaction ID], Description, Context
FROM fn_dblog(NULL, NULL) WHERE [Transaction ID] IN (
	SELECT [Transaction ID] 
	FROM fn_dblog(NULL, NULL) 
	WHERE [Description] like '%Tran%')

Nested Trsansaction fb_dblog 02

– Here we don’t see any row for the inner Transaction with LOP_BEGIN_XACT Operation.
– A separate row is logged for the second INSERT statement as LOP_INSERT_ROWS operation with the same Transaction ID = ‘0000:00000992′.
Thus, this inner Transaction points to the Outer Transaction internally.
 

–> Now we will see what happens when we COMMIT or ROLLBACK the outer Transaction:

3.a. So, let’s COMMIT the outer Transaction first:

-- Outer Transaction - COMMIT
COMMIT TRANSACTION outerTran
SELECT @@TRANCOUNT AS 'outerTran Commit', count(*) from dbo.TranTest -- 0, 2
GO

SELECT * FROM dbo.TranTest -- 2
GO

– After the COMMIT statement the second SELECT statement gives Transaction count = 0, and there is no active Transaction left.
– And the final SELECT lists the 2 records inserted in the outer & inner Transactions.

Now again let’s execute the same function and see the Transaction status:

SELECT Operation, [Transaction ID], Description, Context
FROM fn_dblog(NULL, NULL) WHERE [Transaction ID] IN (
	SELECT [Transaction ID] 
	FROM fn_dblog(NULL, NULL) 
	WHERE [Description] like '%Tran%')

Nested Trsansaction fb_dblog 03a
– When COMMITTING the Outer Transaction it is logged as LOP_COMMIT_XACT Operation with the same Transaction ID = ‘0000:00000992′.
 

3.b. In case of ROLLBACK lets see what happens: You will need to execute all the SQL statements in Step 1 to 3 again.

-- Outer Transaction - COMMIT
COMMIT TRANSACTION outerTran
SELECT @@TRANCOUNT AS 'outerTran Commit', count(*) from dbo.TranTest -- 0, 2
GO

SELECT * FROM dbo.TranTest -- 2
GO

DROP TABLE dbo.TranTest
GO

Now again let’s execute the same function and see the Transaction status:

SELECT Operation, [Transaction ID], Description, Context
FROM fn_dblog(NULL, NULL) WHERE [Transaction ID] IN (
	SELECT [Transaction ID] 
	FROM fn_dblog(NULL, NULL) 
	WHERE [Description] like '%Tran%')

Nested Trsansaction fb_dblog 03b
As the outer Transaction is ROLLEDBACKED the inner Transaction also also gets Rollebacked, and thus you can see:
– two DELETE logged rows for the two INSERTed rows above, with LOP_DELETE_ROWS Operation and Description = COMPENSATION.
– and final ROLLBACK log with LOP_ABORT_XACT Operation with the same Transaction IDs.

 
The above exercise shows us that SQL Server only tracks the outermost Transaction, and do not bother about the inner Transactions.

–> So what’s the Hidden Secret?

1. First one is what we saw above, no Nested Transactions.

2. COMMIT TRANSACTION has an option to apply the Transaction name, but the DB Engine simply ignores it and points to the previous BEGIN TRANSACTION statement. Thus while issuing a COMMIT TRANSACTION referencing the name of an outer transaction when there are outstanding inner transactions it only decrements the @@TRANCOUNT value by 1.

3. ROLLBACK TRANSACTION also have an option to apply the Transaction name, but you can only apply the outermost Transaction name in case of Nested Transactions. While using ROLLBACK in inner Transactions you have to use either just ROLLBACK TRANSACTION or ROLLBACK TRANSACTION SavePoint_name, only if the inner transaction are created with SAVE TRANSACTION option instead of BEGIN TRANSACTION.

4. ROLLBACK TRANSACTION SavePoint_name does not decrement @@TRANCOUNT value.

 
Thus it is advised be careful while using Nested Transactions, ROLLBACKS and SavePoints, or just simply ignore them.


SQL error | Msg 6401, Level 16, State 1 – Cannot roll back Transaction. No transaction or savepoint of that name was found

May 25, 2015 1 comment

Yesterday in a SQL forum I saw a question regarding this error, and the person who had asked this question was finding it difficult to fix it.

He had a scenario where the Outer Transaction has an Inner Transaction, as shown below. And he was trying to ROLLBACK the inner Transaction and was getting an error for this similar SQL batch that I’ve tried to simulated here:

–> I’ve created a sample table let’s executed the whole below:

USE [tempdb]
GO

CREATE TABLE dbo.TranTest (ID INT)
GO

-- Outer Transaction - BEGIN
SELECT @@TRANCOUNT AS 'outerTran Begin', count(*) from dbo.TranTest -- 0, 0
BEGIN TRANSACTION outerTran

INSERT INTO dbo.TranTest values(1)
SELECT @@TRANCOUNT AS 'outerTran Begin', count(*) from dbo.TranTest -- 1, 1

	-- Inner Transaction
	BEGIN TRANSACTION innerTran

	INSERT INTO dbo.TranTest values(2)
	SELECT @@TRANCOUNT AS 'innerTran Begin', count(*) from dbo.TranTest -- 2, 2

	ROLLBACK TRANSACTION innerTran
	SELECT @@TRANCOUNT AS 'innerTran Rollback', count(*) from dbo.TranTest -- 2, 2

-- Outer Transaction - COMMIT
COMMIT TRANSACTION outerTran
SELECT @@TRANCOUNT AS 'outerTran Commit', count(*) from dbo.TranTest -- 1, 2
GO

SELECT * FROM dbo.TranTest -- 2
GO

– You can see the count of rows inserted in the comments with every SELECT statement.
– But the ROLLBACK TRANSACTION innerTran statement at Line 20 fails with following error:
Msg 6401, Level 16, State 1, Line 20
Cannot roll back innerTran1. No transaction or savepoint of that name was found.

– And thus the last SELECT statement still gives Transaction count = 2, as this Transaction is not Rollbacked and neither committed.

–> NOTE: With the ROLLBACK TRANSACTION statement as per MS BoL you can only specify the name of the outermost BEGIN TRANSACTION statement. So, how can we make this code fail-proof?
 

There are 2 ways to solve this:
1. Just use ROLLBACK TRANSACTION without the name of any transaction.
2. Use SAVE TRANSACTION instead of BEGIN TRANSACTION for inner Transactions.
 

Option #1: Using just ROLLBACK TRANSACTION statement:

-- Outer Transaction - BEGIN
SELECT @@TRANCOUNT AS 'outerTran Begin', count(*) from dbo.TranTest -- 0, 0
BEGIN TRANSACTION outerTran

INSERT INTO dbo.TranTest values(1)
SELECT @@TRANCOUNT AS 'outerTran Begin', count(*) from dbo.TranTest -- 1, 1

	-- Inner Transaction
	BEGIN TRANSACTION innerTran

	INSERT INTO dbo.TranTest values(2)
	SELECT @@TRANCOUNT AS 'innerTran Begin', count(*) from dbo.TranTest -- 2, 2

	ROLLBACK TRANSACTION -- here, removed inner Transaction name
	SELECT @@TRANCOUNT AS 'innerTran Rollback', count(*) from dbo.TranTest -- 0, 0

-- Outer Transaction - COMMIT
COMMIT TRANSACTION outerTran
SELECT @@TRANCOUNT AS 'outerTran Commit', count(*) from dbo.TranTest -- 1, 3
GO

SELECT * FROM dbo.TranTest -- 0
GO

– This ROLLBACK statement rollbacks the whole Transaction resulting no rows in dbo.TransTest table.
– But the COMMIT TRANSACTION outerTran statement at Line 20 fails with following error:
Msg 3902, Level 16, State 1, Line 24
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

 

Option #2: Using SAVE TRANSACTION instead of BEGIN TRANSACTION

-- Outer Transaction - BEGIN
SELECT @@TRANCOUNT AS 'outerTran Begin', count(*) from dbo.TranTest -- 0, 0
BEGIN TRANSACTION outerTran

INSERT INTO dbo.TranTest values(1)
SELECT @@TRANCOUNT AS 'outerTran Begin', count(*) from dbo.TranTest -- 1, 1

	-- Inner Transaction
	SAVE TRANSACTION innerTran -- here, created a SavePoint

	INSERT INTO dbo.TranTest values(2)
	SELECT @@TRANCOUNT AS 'innerTran Begin', count(*) from dbo.TranTest -- 1, 2

	ROLLBACK TRANSACTION innerTran -- Rolling back a SavePoint
	SELECT @@TRANCOUNT AS 'innerTran Rollback', count(*) from dbo.TranTest -- 1, 1

-- Outer Transaction - COMMIT
COMMIT TRANSACTION outerTran
SELECT @@TRANCOUNT AS 'outerTran Commit', count(*) from dbo.TranTest -- 0, 1
GO

SELECT * FROM dbo.TranTest -- 1
GO

As you can see that we have converted the Inner Transaction to a Save Point, as as per the error we’ve taken care of this with option #2.

In this case a SavePoint was created and you can Rollback a SavePoint inside the outer Transaction. Thus the outer transaction was finally committed with table having value “1” and inner SavePoint was Rollbacked with row having value “2”.

COMMIT vs ROLLBACK


Follow

Get every new post delivered to your Inbox.

Join 443 other followers