Wednesday, 12 August 2015

Query regression in SQL 2014 compared to earlier SQL versions

Lately I have had many queries like “I have a SQL server 2014 and a particular query does not complete in expected time frame, the same query was running without issues before migration in SQL Server2012/SQl2008R2” 
or 
“The Query that was running in SQL 2012/SQL 2008R2 stalls in SQL 2014 and never completes even though there are no resource issues”. 

In SQL 2014 Cardinality estimator for SQL Query optimizer has been modified for improved performance, although this may help most of your queries there are some queries which has shown regressive performance on SQL 2014 compared to earlier version. This is already a documented issue with Microsoft https://msdn.microsoft.com/en-us/library/dn600374(v=sql.120).aspx . The only way to overcome this is to test your workload before migration and modify your query.

Wednesday, 5 August 2015

SQL Server Kerberos Authentication Issues

Problem: - Not able to create Linked Server using current Windows security contest, The Linked server fails with “Login failed for user NT AUTHORITY\ANONYMOUS LOGIN”

OR

Unable to connect to SQL Server outside of the local server, SQL Server itself was up and running fine and we were able to connect the server when we RDP to the local server and access the SQL but when we connect SQL Server outside the local server were getting the following error,

“The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server)”

Troubleshooting:-

Usually this issue occurs when Kerberos Authentication Failure, Kerberos protocol uses SPN (Service Principal Name) for the authentication with the server and client.
Usually the SPN’s are created on SQL Start-up by using the SQL Server Service Account, but most of the cases we may fail to notice the SPN did not register on SQL start-up due to permission issues,
Even though we will not see any issues connecting the SQL Server as SQL will use NTLM protocol for Authentication, all the operation using Kerberos will fail(E.g.: Linked Server, OpenRowset etc.).

We can check the SQL Error log to find if the SPN was registered successfully on SQL Server Start-up. You will the below message if the SPN is not registered on SQL Server start-up,

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/TestServer.testdomain:<InstanceName> ] for the SQL Server service. Windows return code: 0x2098, state: 20. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [MSSQLSvc/TestServer.testdomain:<InstanceName>] for the SQL Server service. Windows return code: 0x2098, state: 20. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

Resolution:-

This is documented error in Microsoft, you need to provide READ and WRITE permissions to the SQL server domain account on the AD so that new SPN’s can be created on the AD for SQL Server.
Detailed steps to be taken for resolution of this issue is mentioned in the Microsoft article http://support.microsoft.com/kb/811889 and  http://blogs.technet.com/b/mdegre/archive/2009/11/20/the-sql-network-interface-library-was-unable-to-register-spn.aspx. The resolution task can be done only someone who has Admin access to AD.

Once the SPN’s are created and registered successfully you will able to create Linked Servers

Friday, 19 June 2015

XA Transaction, MSDTC and How to Configure Multiple MSDTC Instance on SQL AlwaysON Cluster Environment

Recently we had many issues due to one of our third party application using XA Transaction, We had 4 instances of SQL Server 2012 for this application set up on an active-active 4 node Cluster with Clustered DTC as a separate Role. Since the SQL instances were running XA Transactions they were sharing the DTC on the cluster. We had a hardware issue and had to restart the node holding the MSDTC unfortunately any restart to DTC will need a SQL Server restart otherwise XA Transactions will fail with the below error.

2013-11-16 17:22:38.630 spid342 Error: 8509, Severity: 16, State: 1.
2013-11-16 17:22:38.630 spid342 Import of Microsoft Distributed Transaction Coordinator (MS DTC) transaction failed: 0x8004d01c(XACT_E_CONNECTION_DOWN).
2013-11-16 17:22:46.600 spid112 Error: 8510, Severity: 20, State: 3.
2013-11-16 17:22:46.600 spid112 Enlist operation failed: 0x8004d01c(XACT_E_CONNECTION_DOWN). SQL Server could not register with Microsoft Distributed Transaction Coordinator (MS DTC) as a resource manager for this transaction. The transaction may have been stopped by the client or the resource manager.

Since the DTC is shared with 4 different database servers we ended up in a situation of restarting all the SQL Instances unnecessarily when we had a DTC outage. So we decided to have a dedicated instance of MSDTC for each Server instance.

I worked closely with Microsoft PSS Team to get the MSDTC configured.I am just sharing the information I got from Microsoft PSS Distributed Services Team on XA Transaction, and how to configure Multiple MSDTC Instance on SQL AlwaysON Cluster Environment,


Questions:-

Right set of steps to configure multiple instance of DTC.

The link below will provide you Right Set of Steps to Configure Multiple Instance of DTC:
<Comment:- Although the article is for SQL 2008, There are no changes in steps for SQL 2014/SQL 2012 both of which I was able to test in my environment>

However, the important point to note is to know pros and cons of having Independent Instance of Clustered DTCs vs Making SQL Group dependent on Clustered DTC resource. The link below provides you Pros and Cons:

Do I need to Run Tmapping in above case?

For your “4 DTC instances one per SQL instance, SQL dependent on DTC.  No TMMapping is necessary” you already have SQL dependent on MSDTC. So the mapped instance of SQL will be using the Clustered DTC instance. SQL and DTC will both failover together. So where does explicitly mapping the DTC arise?  
<Comment:- I have configured the DTC instances in the same Cluster Role as SQL Server which negates the use of TMapping but if you are configuring the MSDTC as a seperate Role on the Cluster TMapping is Mandatory to map the right SQL instanceAlthough it is not required to use TMapping when DTC Resource is Dependent on SQL doing the tmapping does not do any harm which I eventually ended up doing. 

use Msdtc –tmmappingview * or verify the mapping on Registry HKEY_LOCAL_MACHINE\Cluster\MSDTC\ to confirm the mapping has been completed successfully. Also I would suggest using DTCTester.exe to verify the transactions are getting enlisted on the right DTC Instance >

 The architecture of the JDBC driver which creates the XA Transaction

The driver is a pure Java implementation that can run on any OS. For this reason it cannot rely on having a DTC instance available on the client machine. To work around this issue, it uses a server-side component (sqljdbc_xa.dll) that is loaded in the SQL Server process space and manages DTC transactions on behalf of the client.

When an XA transaction is started in the client application, a call is made via an extended stored procedure in the dll to enlist the connection in a DTC transaction. When the XA transaction is aborted or committed by the client application, a corresponding call is made by the dll to rollback/commit the DTC transaction. So to the resource manager (SQL DB), it is as if extended stored proc performing transaction from the same server machine.

In simple SQL Server terms, distributed transactions are transactions that are capable of coordinating transaction state with 2 or more unique connections to either the same server or to different servers. SQL Server is capable of utilizing distributed transactions by communicating with MSDTC. MSDTC coordinates transaction state with SQL Server and other participants of the distributed transaction by communicating with other MSDTC instances on other machines.

In most scenarios that involve our Windows-based client technologies such as ADO, System.Data, ODBC, OLE DB etc., the client application that utilizes distributed transactions communicates with the local MSDTC on its machine. This local MSDTC communicates with the SQL Server MSDTC instance and potentially other MSDTC instances of other SQL Server instances.

Client                                                                                               Server

MSDTC  --------------------------- transaction state ------------------------------ MSDTC
 |                                                                                                          |
ADO      ------------------------ SQL Server connection -------------------------- SQL Server

What is XA

XA is an industry standard for implementing distributed transactions between generic transaction managers and resource managers. It is popular in the Java and Unix development community and JDBC drivers are expected to implement XA so that the JDBC driver can participate in distributed transactions. Most application servers such as Weblogic, Websphere, etc. use XA transactions even for single-connection scenarios so that the application server can manage transactions at a level higher than the application code.

XA Specification

XA and MSDTC
MSDTC implements the XA protocol to allow Windows machines to participate in distributed transactions in a mixed OS environment. C++ applications can use functions such as xa_start using the XA protocol to communicate with MSDTC and by extension SQL Server.
  
XA Implementation in the Microsoft JDBC Driver
The Microsoft SQL Server JDBC Driver 1.2 and 2.0 implements XA transactions so that applications using it can participate in distributed transactions. Because the JDBC driver can run on any OS (not just Windows), the driver cannot assume that a local MSDTC instance exists. The only location where we know an MSDTC instance exists is the SQL Server.

For this reason, the JDBC driver implements a mechanism where the JDBC driver (client-side) communicates with the server-side MSDTC through a server-side component that lives on the SQL Server. This component - sqljdbc_xa.dll - is a C++ dll that is loaded by the SQL Server that implements helper extended stored procedures. This component uses the XA APIs such as xa_start to start/commit/rollback distributed transactions in the server-side MSDTC instance. The client-side JDBC driver component invokes the server-side component by launching a second connection to the SQL Server and executing extended stored procedures which perform the XA calls.

Client                                                                                                                    Server

JDBC  driver  ---- SQL Server connection 1: statements ---------------------------------- SQL Server ----------
 |                                                                                                               |                          |
   ------------------ SQL Server connection 2: extended stored procedure XA calls ------ sqljdbc_xa.dll         |
                                                                                                                 |                          |
                                                                                                                             MSDTC --------------

The sequence of operations that occur in a normal XA transaction sequence is listed below. This is a single-connection scenario in the sense that the JDBC application opens a single connection object to SQL Server. JDBC uses MSDTC as a tweak to give SQL sense of Distributed Transactions. As you would have realized by now, that resource manager (SQL DB), uses extended stored proc performing transaction from the same server machine. 

Wednesday, 1 April 2015

Create SQL Azure Database(PaaS)

Today we can see the step by step procedure to create SQL Azure database(PaaS),As mentioned in the previous couple of articles any Microsoft Cloud based service you need a Azure Subscription (http://azure.microsoft.com/en-us/pricing/); Select SQL database section in the pricing page (http://azure.microsoft.com/en-us/pricing/details/sql-database/) and choose the appropriate Tier based on your requirement. Let’s I assume you have purchased the required subscription which should grant you access to you own customized Azure Portal page when you login to https://manage.windowsazure.com as below,

In the portal page Select SQL Databases, and click "CREATE NEW DATABASE"



Now Specify the Database name, Select the right Tier Level and Collation. If this is the first database you are creating leave the Server as "New SQL Database Server"



Choose the Login name and password to access the database, This login will act as your SA Login.



Verify the successful creation of new SQL Azure database,



You can view the Server name details in the servers tab as below, As mentioned earlier this is just a virtual name you will not be able to access the physical server in this type of service offering.


You can click on the server and view the dashboard and properties of the virtual Server.


Now Select Configure on the Server Page and Provide the IP List or IP Range of the clients which will be connecting this database. Any other IP will be blocked from accessing this database.



Now Try connecting the server,


And There you Go!!!


As in all Windows Cloud Services, you can view various performance metrics on the dashboard.



In the next Article we can discuss on differences between the Native SQL Database and SQL Azure..


Tuesday, 13 January 2015

Create SQL Server on Windows Azure Virtual Machine

In the last post we saw the two different offering of SQL server on cloud services by Microsoft, Today we will go through the Step by Step Procedure on how to create a SQL 2014 SQL server on Windows Azure, As mentioned in the last post to create any Microsoft Cloud based service you need a Azure Subscription http://azure.microsoft.com/en-us/pricing/purchase-options/ . There are various Tiers of Servers offered based on the performance and Scalability. Let’s I assume you have purchased the required subscription which should grant you access to you own customized Azure Portal page when you login to https://manage.windowsazure.com as below,


In the Portal Page select on Virtual Machine and click Create a virtual Machine, 



As you can see in the above screen it will prompt you for configuration details, either you can select the configuration image from gallery(Pre-Define images) or select your own if you have subscribed for the configuration and Select Create Virtual Machine.

I have Chose "From Gallery" for our example,


As you can see in the above image you will be presented with various images of different products of Microsoft, As I want a Windows server Hosting SQL Server 2014 I Choose SQL Server->SQL Server 2014 RTM enterprise Image.

Note:- You can Still just create a Windows Machine and install SQL Server as in On-Premises Machines if you have the Licence for SQL Server Already.



Now It will Prompt you select Virtual Machine Name and Server Configuration/Server Tier Along with a Local Administrator and Password.(Above Image)


Next is if your creating a Azure for the first time it will prompt you create a Cloud Service Account and a Storage Account.(Above Image)


Next is if you want to Install any Anti-Virus or other Agents to your base Windows build, Select Appropriately. (Above Image). Once you click ok Microsoft will start provisioning your server
(Below Image)

Once the server is provisioned and ready to use you should see the Status as Running and at the bottom of the portal you should see the Connect option enabled.(Image Below)



Click Connect, It will download a RDP File, Click the RDP file to Remote Desktop to the new server(Image Below)





As you can see in the above figure, You can now access the server as a usual VM server with SQL Server 2014 Installed and it is no different from your On-Premises server.

Now Lets go back to the portal again and see what option we have from the Azure Portal, First Lets see the home page(Image Below)


As you can see you have Virtual server created for your account, let see what happens when you click the virtual server.




Which we select the virtual server from the home page we will be displayed with a dashboard with Server details and Performance. As you can see in the top Tabs as the name suggests you can also monitor the performance separately, configure endpoints for the server.


The Last page is the Configure Tab in the server setting page, In this page you change the server configuration to different Tier for scalability and also select something called Availability Set with the High availability option for the server which is not in the scope of this post.

In the next post we will see how we can create a SQL Azure from the Portal with Screen Shots.

Monday, 12 January 2015

Understanding SQL Server on Windows Azure VS Azure SQL database

Often people get confused with the two different types of SQL Server service available on Azure or Microsoft cloud services,

1.       SQL Server on Windows Azure
2.       Azure  SQL Server Databases

SQL Server on Windows Azure:-

SQL Server on Windows Azure is nothing but a regular instance of SQL Server installed on a Virtual Windows machine hosted on Cloud (Windows Azure), a virtual server hosted in Microsoft data centre instead of On-Premises Data Centre. You can use the Windows and SQL Server similar to your on-premises system. The Windows and SQL will still Admin to manage the environment. Microsoft calls the category of SQL Server in Azure Virtual Machine (VM) as Infrastructure as a Service (IaaS) 


Microsoft has come up with many pre-defined SQL Server configured images so when you provision your virtual Machine in Azure you can straight away request for a SQL Server Image which will have your SQL server installed when the system is delivered.
There are various purchase option http://azure.microsoft.com/en-us/pricing/purchase-options/ for cloud based SQL Services available, without going much into various option broadly you can either subscribe a licence for SQL Server Image which holds both windows or SQL Server Licence or you can just subscribe a Windows Azure image and install the SQL Server yourself and use if have any existing licence.

Azure SQL Server Databases:-

Azure SQL Database is a relational database-as-a-service, where Microsoft offers Database as a service. These databases are hosted in servers maintained by Microsoft and the server layer is not visible to the users or admins. In this configuration database maintenance requirements are very minimal as we do not control any physical features of the database and you do not have to maintain the server infrastructure.Microsoft calls the category of Azure SQL Databases as Platform as a Service (PaaS)


Since Azure SQL databases works on a slight different architecture to standard SQL databases there also some limitation with this type of databases. All the server features are in accessible as only database is offered as service and almost all system objects are not visible making it difficult to manage the database.

Find more details on features not supported by Azure SQL databases.


Below is an image of a SQL Instance hosted of Windows Azure and Azure SQL database just to have look and feel difference when accessed through SSMS. The First Image is the Windows Portal for Azure which has two sections highlighted Virtual Machine section is where we create Windows Azure and SQL Databases is the section we can create Azure SQL databases. The second Image shows how SQL database on Windows Azure and Azure SQL databases looks like in SSMS.

In my next post we will discuss how to create  SQL Server on Windows Azure and Azure SQL databases in detail with some screen shots.




Monday, 5 January 2015

Transaction Log Backups on Availability databases and its effect on database shrink

With the introduction of availability databases, I have been queried frequently about how to schedule a T-Log backup on Availability databases, do we need to schedule the backup on both the servers or only on Primary. To clear these doubts I planned to do some tests to explain the effect of T-Log backup on Availability databases. The architecture of T-Log has been clearly explained with respect to Availability databases in the below article, All I am going to do is do some testing to prove this.


I am going to use a availability database called AGTest for this testing,

Create the test Table on Primary database,

USE [AGTest]
GO
CREATE TABLE [dbo].[TLogTest](
       [Cnt] [int] NULL
) ON [PRIMARY]

GO

Get the T-Log usage before data updates

DBCC SQLPERF(LOGSPACE)

Primary Database:-
Database Name
Log Size (MB)
Log Space Used (%)
Status
AGTest
0.9921875
37.20472
0

Secondary HA Database:-
Database Name
Log Size (MB)
Log Space Used (%)
Status
AGTest
0.9921875
12.79528
0


Do a logged operation on Primary database for T-Log usage,

DECLARE @intLimit AS INT = 1000000
DECLARE @intCounter AS INT = 1

WHILE @intCounter <= @intLimit
BEGIN
INSERT INTO TLogTest VALUES (@intCounter)
SET @intCounter = @intCounter + 1
END

Get the T-Log usage after logged operation

Primary Database:-
Database Name
Log Size (MB)
Log Space Used (%)
Status
AGTest
51.17969
98.73397
0

Secondary HA Database:-
Database Name
Log Size (MB)
Log Space Used (%)
Status
AGTest
51.17969
98.21497
0

The above metric looks logical as per the HA architecture, now I am going to Take T-Log backup of the AGTest Database in Primary alone,

BACKUP LOG [AGTest] TO DISK = 't:\Test.bkp'

Now let’s look at the T-Log size,

Primary Database:-
Database Name
Log Size (MB)
Log Space Used (%)
Status
AGTest
51.17969
3.325828
0

Secondary HA Database:-
Database Name
Log Size (MB)
Log Space Used (%)
Status
AGTest
51.17969
2.814456
0


So the a T-Log backup on the primary does truncate the log records on both Primary and the secondary HA database. Technically backup on either of the database should be enough but there is a catch here, Now I try to shrink the T-Log on primary let’s see what happens.


Now let’s look at the Log File size,

Primary Database:-
Database Name
Log Size (MB)
Log Space Used (%)
Status
AGTest
51.17969
6.031522
0

Secondary HA Database:-
Database Name
Log Size (MB)
Log Space Used (%)
Status
AGTest
51.17969
5.566898
0

Whatever I try to do I was not able to shrink the file, Now I decided to the only step I missed BACKUP Secondary HA database.

BACKUP LOG [AGTest] TO DISK = 'P:\Test.bkp'

Now I ran the shrink again on the primary database, Let’s see the Log file size now,

Primary Database:-
Database Name
Log Size (MB)
Log Space Used (%)
Status
AGTest
0.9921875
30.01969
0

Secondary HA Database:-
Database Name
Log Size (MB)
Log Space Used (%)
Status
AGTest
0.9921875
5.659449
0

:-) And there it goes log file was shrunk in both Primary and secondary databases. Hope this article gives you an idea of how to schedule a log backup on HA databases.