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.

Monday, 27 October 2014

SQL Server 2014 new In-memory Tables

I have summarised basic concepts of In-Memory OLTP from various Microsoft articles which will help you to start up with the basics,

SQL Server 2014 new In-memory Tables:-

Microsoft has come up new concept called In-Memory OLTP with the introduction of SQL Server 2014 for performance optimization on OLTP database servers. The traditional SQL Server stores the tables in the disk and SQL OS moves the data pages from the disk to the memory (SQL Server Buffer) depending based on the query executed, most of the times the frequently accessed tables pages in the memory are paged to disk to accommodate other requested data pages resulting in frequent paging and high PAGIOLATCH waits. With the cost of hardware reducing over the years In-Memory OLTP gives us the option to store the frequently used table in memory instead of disk. In-Memory architecture is fully integrated with SQL Server and the Memory Optimized tables are fully transactional, durable, and are accessed using Transact-SQL in the same way as disk-based tables. 


In-Memory Architecture


Legacy SQL server did have similar function called DBCC PINTABLE which pins the data pages of the table to memory and these pages does not flush to the disks, but this was a SQL Server internal function meaning the pages are pinned to SQL Server Buffer cache which most of times creates a memory crunch for other data which needs processing which resulted in more of a problem than resolution this function was depreciated in SQL Server. In-Memory OLTP architecture unlike DBCC PINTABLE has its own memory space for storing memory optimized tables and indexes and don’t use Buffer cache which can be still used effective for disk based table operation. SQL server can interact with memory optimized table and a disk based table in same transaction meaning you can have both memory-optimized tables and disk-based tables in the same database, and query across both types of tables.



Transactional Concurrency:-

One of major advantages of the memory optimized table is locks on the table are very minimal irrespective of the isolation level of the transaction. Take for example in READ SNAPSHOT ISOLATION on a disk based table does not block a SELECT operation during a data modification due to row versioning of the data in tempdb database but this still blocks any process which tries to update/delete the data simultaneously but in Memory optimized architecture there are no locking if two transactions attempt to update the same row, rather it is managed by a conflict management. memory-optimized tables use multi versioning meaning each row has different version instead of single row version used in snapshot isolation, Also unlike snapshot isolation which stores the row version in tempdb, memory optimized tables row version are stored in memory. This architecture allows even higher isolation levels to have transactional concurrency. As a part of conflict management SQL server detect conflicts between concurrent transactions, If a conflict is detected, the transaction is terminated and the client needs to retry.


Durability:-

Memory optimized Tables can be configured as both fully durable(SCHEMA_AND_DATA) and non-durable(SCHEMA_ONLY). Memory optimized tables configured fully durable(SCHEMA_AND_DATA) can be recovered  without any data loss on an SQL Server restart whereas Tables configured with SCHEMA_ONLY durability will be able to recover only the table metadata on a SQL server restart, these tables can be typically your staging tables used in ETL process which can be flushed on database restart like tempdb tables.

Like Disk-based tables all changes are logged to a transaction log for durable memory optimized tables and the SQL server writes the transaction to the disk. Now this where it can be misleading on why the data is written to disk when the table is in memory, Memory-optimized tables is fully integrated with the same log stream used by disk-based tables, to recover the table point in time and to allow operations like T-Log, backup and restore without any additional steps, Memory optimized table maintains a copy of table and data on disk on a different data structure stored as free form data rows with the help of two files called data and delta files. The data and delta files are located in one or more containers (using the same mechanism used for FILESTREAM data). These containers are mapped to a new type of filegroup, called a memory-optimized filegroup. On a SQL Server restart memory optimized tables are flushed back into memory from the data and delta files for fully durable table.


Implementation:-

Create a database  with a memory optimized filegroup and container

CREATE DATABASE MEMORYTABLETEST
ON PRIMARY
(NAME = ‘MEMORYTABLETEST_PRIMARY’, FILENAME = ‘C:\Data\ MEMORYTABLETEST_PRIMARY.mdf)
FILEGROUP INMEMORTYOLTP CONTAINS MEMORY_OPTIMIZED_DATA
(NAME = ‘MEMORYTABLETEST_INMEMORY’, FILE = ‘C:\InMemory\Container’)
 
 
Create a Durable memory optimized table
 
CREATE TABLE dbo.durabletable_memory 
( Col1 int PRIMARY KEY,
  Col2 char(200) NONCLUSTERED HASH (Id) WITH (BUCKET_COUNT = 1024) 
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
 
   
Memory Optimization Indexes

Memory optimized tables supports indexes, Memory-optimized indexes exist only in memory and index operation are not logged into transaction logs.

There are two types of memory-optimized indexes

Non-clustered Hash Index
 Non clustered Index

Hash index architecture is different from traditional index and it uses a data structure called buckets to store index and data.
Each memory-optimized table must have at least one index. Note that each PRIMARY KEY constraint implicitly creates an index. Therefore, if a table has a primary key, it has an index. A primary key is a requirement for a durable memory-optimized table.


Limitations

In-Memory OLTP will improve performance best in OLTP with short-running transactions, but to gain the performance improvement we need to select the right table to be optimized otherwise it can cause huge performance issues, being a new component memory optimized OLTP has lot of function limitation which may be improved in future versions. For example cross database transaction, replication, mirroring, database snapshots is not supported and there are also various limitations on operation you can perform on these tables. But if a right table is configured to use this feature Microsoft suggests we may have have 5-20 times performance improvement which is a huge leap.