Enable XA transactions for SQL Server

An XA transaction is a  global transaction usually covering multiple resources.

Use  the  Microsoft SQL Server JDBC Driver to support the   Java Platform based distributed transactions

Steps to enable XA transactions

1)  Enable MSDTC

On Windows 2003 > Start > Run > “dcomconfg” > Expand Component Services > Expand Computers > Right -click  My Computer > Click Properties > Click MSDTC tab > Click Security Configuration > Check the “Enable XA Transactions”

Use MS DTC and sys.dm_tran_active_transactions  to monitor the state of the MSDTC transactions

2) Install  SQLJDBC_XA.dll 

Prior to running this script  copy the extended stored procedure dll SQLJDBC_XA.dll  to the target SQL Server's Binn folder.  Restart the SQL Server Instance

3) Create a role  and Attach the role to relevant users

Permissions to the distributed transaction support procedures for the Microsoft SQL Server 2005  JDBC Driver are granted through the SQL Server role [SqlJDBCXAUser].  Maintain a secure default configuration, no user is granted access to this role by default. Ensure the actions you execute are compatible with your SQL Server Security Policy

 

use master
go

-- Drop any existing procedure definitions.
exec sp_dropextendedproc 'xp_sqljdbc_xa_init' 
exec sp_dropextendedproc 'xp_sqljdbc_xa_start'
exec sp_dropextendedproc 'xp_sqljdbc_xa_end'
exec sp_dropextendedproc 'xp_sqljdbc_xa_prepare'
exec sp_dropextendedproc 'xp_sqljdbc_xa_commit'
exec sp_dropextendedproc 'xp_sqljdbc_xa_rollback'
exec sp_dropextendedproc 'xp_sqljdbc_xa_forget'
exec sp_dropextendedproc 'xp_sqljdbc_xa_recover'
exec sp_dropextendedproc 'xp_sqljdbc_xa_rollback_ex'
exec sp_dropextendedproc 'xp_sqljdbc_xa_forget_ex'
exec sp_dropextendedproc 'xp_sqljdbc_xa_prepare_ex'
go

-- Install the procedures.
exec sp_addextendedproc 'xp_sqljdbc_xa_init', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_start', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_end', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_prepare', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_commit', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_rollback', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_forget', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_recover', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_rollback_ex', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_forget_ex', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_prepare_ex', 'SQLJDBC_XA.dll'
go
use master 
GO
sp_addrole [SqlJDBCXAUser]
go

-- Grant privileges to [SqlJDBCXAUser] role to the extended stored procedures.
grant execute on xp_sqljdbc_xa_init to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_start to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_end to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_prepare to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_commit to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_rollback to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_recover to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_forget to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_rollback_ex to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_forget_ex to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_prepare_ex to [SqlJDBCXAUser]
go

 

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server test data generation testing tools

Question:  How do I generate test data for SQL Server ?  I don’t want to buy a benchmarking tool, but I  require a representative test case with real SQL statements.  How do the professional DBA’s capture SQL Server workloads for testing?

Answer:  First, try to use real-world data, not artificial test data sets.  You want a representative benchmark.  

A one dimensional  view of SQL Server can lead to vastly different results. Data that is not representative and workloads executed on the data can create a false sense of results.

Always remember that there is no substitute for a real-world test that simulates the actual behaviour of your production systems. However , this is not always possible.

SQL Server  test data generation testing tools

 There are several tools and methods for generating test data for SQL Server:

• SQL Server Profiler- using the T-SQL Replay template. 
• DBGEN - The TPC-H tools, dbgen and qgen allow for the development of up to a terabyte databases.
    
• Quest Benchmark Factory for Databases – Track the effects of SQL Server changes on the environment
  
   

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Should libraries be installed on database servers ?

A developer asked me if he could install “microsoft.office.interop.excel.dll” on a Production database server. This dll is part of the Microsoft Excel 11.0 Object Library.

This would satisfy his requirement to run a transformation on an Excel file , as part of a SSIS job.

The database server policy is to not install extra libraries unless they are part of a standardised  database server build.  

One of the main reasons for standardising all builds is to improve management. Including rebuilds and Disaster Recover situations.

A sample of the standard builds for database servers include:

Tivoli Endpoint

Virus Scan         

Resource Kit 

Tivoli Storage Mgr

Tivoli Workload Scheduler

Tivoli Data Protection

SQL Management Studio

OSQL & sqlcmd

BCP

Prior client version compatibility with 2005 release of RDBMS (8.0)

SQL Server binaries

What I’d like to know is how you manage requests for ad-hoc libraries ? Do you have a policy? If you do allow , then what sort of documentation standards do you maintain?

SQL Sp_send_dbmail and bcc

To send BCC and Copy in SQL Sp_send_dbmail,  use this way to send email from a configured Database Mail server.

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'JackVamvas@sqlserver-dba.com',
@copy_recipients ='JackVamvas@sqlserver-dba.com’
@blind_copy_recipients ='tuning@sqlserver-dba.com’
@body= 'Some text for body', 
@subject = 'Some text for subject',
@profile_name = 'MyProfile1';

The email addresses in @recipients go on the To:  line.

To  use   COPY and BCC on sp_send_dbmail , the parameters are :

@copy_recipients ='JackVamvas@sqlserver-dba.com’
@blind_copy_recipients = 'tuning@sqlserver-dba.com'

Related Posts

Set up Database Mail on SQL Server 2005  

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL SSIS and service pack upgrade

SSIS 2005 latest service pack upgrade.How to create instructions for tech support to upgrade the SSIS version

From the following link http://support.microsoft.com/kb/942177  find out your SSIS version

 For example :

I have this  SSIS version in blue in the following table:

 

Service pack level	Version number	Name of the root node
The release version of SSIS	9.0.1399	MyServerName (Integration Services 9.0.1399 - MyDomain\MyUser)
SSIS with Service Pack 1 (SP1)	9.0.2047	MyServerName (Integration Services 9.0.2047 - MyDomain\MyUser)
SSIS with Service Pack 2 (SP2)	9.0.3042	MyServerName (Integration Services 9.0.3042 - MyDomain\MyUser)

 

To upgrade the SSIS version to 9.0.3042, download from

http://www.microsoft.com/downloads/en/details.aspx?FamilyId=D07219B2-1E23-49C8-8F0C-63FA18F26D3A&displaylang=en

Source:Jack Vamvas (http://www.sqlserver-dba.com)