Send Email Notification when SQL SERVER restarts

May 7, 2015November 22, 2015 ~ tatroc2015 ~ Leave a comment

The following script can be used to create a SQL AGENT Job which will fire when SQL SERVER is restarted. This can reduce the time it takes to trouble shoot issues. For example, I support a SQL transactional replication environment. Sometimes I get random replication failures only to find out a the publisher a database server I do not support has restarted.

USE [msdb]
GO

/****** Object:  Job [DBM113_Service_Restart_Notification]    Script Date: 05/07/2015 07:09:10 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 05/07/2015 07:09:10 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBM_Service_Restart_Notification', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Send_Email_Step]    Script Date: 05/07/2015 07:09:10 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Send_Email_Step', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'EXEC msdb.dbo.sp_send_dbmail
@profile_name=''Alert_Profile'',
@recipients = ''[email protected]'',
@copy_recipients = ''[email protected]'',
@body = ''This is an informational message only: SQL services possibly restarted on SERVER01'',
@subject = ''SQL Services Restarted on SERVER01'';', 
		@database_name=N'master', 
		@output_file_name=N'C:\temp\Service_Restart_Notification.log', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Service_Restart_Notification', 
		@enabled=1, 
		@freq_type=64, 
		@freq_interval=0, 
		@freq_subday_type=0, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20141008, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959, 
		@schedule_uid=N'd1fdebc3-e6fd-4653-8663-cf26986cb7db'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Upgrading ESXi 5.5 to ESXi 6.0

May 4, 2015May 6, 2015 ~ tatroc2015 ~ Leave a comment

In my home lab I run ESXi 5.5 on a HP DL380. I recently upgraded from ESXi 5.5 free to ESXi 6.0. Below are the steps I used in the upgrade process. The upgrade process is actually very simple. STEP 1. Download and install VMware Software Manager STEP 2. After completing the install of VMware Software Manager, select the following options in the VMware Software Manager do download the ESXi 6.0 upgrade. After completing the download of the ESXi 6 packages the offline bundle should be located in the following folder: c:\depot\content\dig_ESXI600\VMware-ESXi-6.0.0.0-2494585-depot.zip STEP 3. Upload the ESXi offline bundle (VMware-ESXi-6.0.0.0-2494585-depot.zip) to the ESXi 5.5 host STEP 4. Put the ESXi host into maintenance mode, all guest VMs must be shutdown on the ESXi host being upgraded. STEP 5. SSH into the ESXi host and run the following command to install the offline bundle.

esxcli software profile update -d /vmfs/volumes/datastore1/ISOs/VMware-ESXi-6.0.0-2494585-depot.zip -p ESXi-6.0.0-2494585-standard

Output from the upgrade… You will be required to restart the ESXi host.

Update Result
   Message: The update completed successfully, but the system needs to be rebooted for the changes to be effective.
   Reboot Required: true
   VIBs Installed: VMWARE_bootbank_mtip32xx-native_3.8.5-1vmw.600.0.0.2494585, VMware_bootbank_ata-pata-amd_0.3.10-3vmw.600.0.0.2494585, VMware_bootbank_ata-pata-atiixp_0.4.6-4vmw.600.0.0.2494585, VMware_bootbank_ata-pata-cmd64x_0.2.5-3vmw.600.0.0.2494585, VMware_bootbank_ata-pata-hpt3x2n_0.3.4-3vmw.600.0.0.2494585, VMware_bootbank_ata-pata-pdc2027x_1.0-3vmw.600.0.0.2494585, VMware_bootbank_ata-pata-serverworks_0.4.3-3vmw.600.0.0.2494585, VMware_bootbank_ata-pata-sil680_0.4.8-3vmw.600.0.0.2494585, VMware_bootbank_ata-pata-via_0.3.3-2vmw.600.0.0.2494585, VMware_bootbank_block-cciss_3.6.14-10vmw.600.0.0.2494585, VMware_bootbank_cpu-microcode_6.0.0-0.0.2494585, VMware_bootbank_ehci-ehci-hcd_1.0-3vmw.600.0.0.2494585, VMware_bootbank_elxnet_10.2.309.6v-1vmw.600.0.0.2494585, VMware_bootbank_emulex-esx-elxnetcli_10.2.309.6v-0.0.2494585, VMware_bootbank_esx-base_6.0.0-0.0.2494585, VMware_bootbank_esx-dvfilter-generic-fastpath_6.0.0-0.0.2494585, VMware_bootbank_esx-tboot_6.0.0-0.0.2494585, VMware_bootbank_esx-xserver_6.0.0-0.0.2494585, VMware_bootbank_ima-qla4xxx_2.02.18-1vmw.600.0.0.2494585, VMware_bootbank_ipmi-ipmi-devintf_39.1-4vmw.600.0.0.2494585, VMware_bootbank_ipmi-ipmi-msghandler_39.1-4vmw.600.0.0.2494585, VMware_bootbank_ipmi-ipmi-si-drv_39.1-4vmw.600.0.0.2494585, VMware_bootbank_lpfc_10.2.309.8-2vmw.600.0.0.2494585, VMware_bootbank_lsi-mr3_6.605.08.00-6vmw.600.0.0.2494585, VMware_bootbank_lsi-msgpt3_06.255.12.00-7vmw.600.0.0.2494585, VMware_bootbank_lsu-hp-hpsa-plugin_1.0.0-1vmw.600.0.0.2494585, VMware_bootbank_lsu-lsi-lsi-mr3-plugin_1.0.0-1vmw.600.0.0.2494585, VMware_bootbank_lsu-lsi-lsi-msgpt3-plugin_1.0.0-1vmw.600.0.0.2494585, VMware_bootbank_lsu-lsi-megaraid-sas-plugin_1.0.0-1vmw.600.0.0.2494585, VMware_bootbank_lsu-lsi-mpt2sas-plugin_1.0.0-1vmw.600.0.0.2494585, VMware_bootbank_lsu-lsi-mptsas-plugin_1.0.0-1vmw.600.0.0.2494585, VMware_bootbank_misc-cnic-register_1.78.75.v60.7-1vmw.600.0.0.2494585, VMware_bootbank_misc-drivers_6.0.0-0.0.2494585, VMware_bootbank_net-bnx2_2.2.4f.v60.10-1vmw.600.0.0.2494585, VMware_bootbank_net-bnx2x_1.78.80.v60.12-1vmw.600.0.0.2494585, VMware_bootbank_net-cnic_1.78.76.v60.13-2vmw.600.0.0.2494585, VMware_bootbank_net-e1000_8.0.3.1-5vmw.600.0.0.2494585, VMware_bootbank_net-e1000e_2.5.4-6vmw.600.0.0.2494585, VMware_bootbank_net-enic_2.1.2.38-2vmw.600.0.0.2494585, VMware_bootbank_net-forcedeth_0.61-2vmw.600.0.0.2494585, VMware_bootbank_net-igb_5.0.5.1.1-5vmw.600.0.0.2494585, VMware_bootbank_net-ixgbe_3.7.13.7.14iov-20vmw.600.0.0.2494585, VMware_bootbank_net-mlx4-core_1.9.7.0-1vmw.600.0.0.2494585, VMware_bootbank_net-mlx4-en_1.9.7.0-1vmw.600.0.0.2494585, VMware_bootbank_net-nx-nic_5.0.621-5vmw.600.0.0.2494585, VMware_bootbank_net-tg3_3.131d.v60.4-1vmw.600.0.0.2494585, VMware_bootbank_net-vmxnet3_1.1.3.0-3vmw.600.0.0.2494585, VMware_bootbank_nmlx4-core_3.0.0.0-1vmw.600.0.0.2494585, VMware_bootbank_nmlx4-en_3.0.0.0-1vmw.600.0.0.2494585, VMware_bootbank_nmlx4-rdma_3.0.0.0-1vmw.600.0.0.2494585, VMware_bootbank_nvme_1.0e.0.35-1vmw.600.0.0.2494585, VMware_bootbank_ohci-usb-ohci_1.0-3vmw.600.0.0.2494585, VMware_bootbank_qlnativefc_2.0.12.0-5vmw.600.0.0.2494585, VMware_bootbank_rste_2.0.2.0088-4vmw.600.0.0.2494585, VMware_bootbank_sata-ahci_3.0-21vmw.600.0.0.2494585, VMware_bootbank_sata-ata-piix_2.12-10vmw.600.0.0.2494585, VMware_bootbank_sata-sata-nv_3.5-4vmw.600.0.0.2494585, VMware_bootbank_sata-sata-promise_2.12-3vmw.600.0.0.2494585, VMware_bootbank_sata-sata-sil24_1.1-1vmw.600.0.0.2494585, VMware_bootbank_sata-sata-sil_2.3-4vmw.600.0.0.2494585, VMware_bootbank_sata-sata-svw_2.3-3vmw.600.0.0.2494585, VMware_bootbank_scsi-aacraid_1.1.5.1-9vmw.600.0.0.2494585, VMware_bootbank_scsi-adp94xx_1.0.8.12-6vmw.600.0.0.2494585, VMware_bootbank_scsi-aic79xx_3.1-5vmw.600.0.0.2494585, VMware_bootbank_scsi-bnx2fc_1.78.78.v60.8-1vmw.600.0.0.2494585, VMware_bootbank_scsi-bnx2i_2.78.76.v60.8-1vmw.600.0.0.2494585, VMware_bootbank_scsi-fnic_1.5.0.45-3vmw.600.0.0.2494585, VMware_bootbank_scsi-hpsa_6.0.0.44-4vmw.600.0.0.2494585, VMware_bootbank_scsi-ips_7.12.05-4vmw.600.0.0.2494585, VMware_bootbank_scsi-megaraid-mbox_2.20.5.1-6vmw.600.0.0.2494585, VMware_bootbank_scsi-megaraid-sas_6.603.55.00-2vmw.600.0.0.2494585, VMware_bootbank_scsi-megaraid2_2.00.4-9vmw.600.0.0.2494585, VMware_bootbank_scsi-mpt2sas_19.00.00.00-1vmw.600.0.0.2494585, VMware_bootbank_scsi-mptsas_4.23.01.00-9vmw.600.0.0.2494585, VMware_bootbank_scsi-mptspi_4.23.01.00-9vmw.600.0.0.2494585, VMware_bootbank_scsi-qla4xxx_5.01.03.2-7vmw.600.0.0.2494585, VMware_bootbank_uhci-usb-uhci_1.0-3vmw.600.0.0.2494585, VMware_bootbank_xhci-xhci_1.0-2vmw.600.0.0.2494585, VMware_locker_tools-light_6.0.0-0.0.2494585
   VIBs Removed: VMware_bootbank_ata-pata-amd_0.3.10-3vmw.550.0.0.1331820, VMware_bootbank_ata-pata-atiixp_0.4.6-4vmw.550.0.0.1331820, VMware_bootbank_ata-pata-cmd64x_0.2.5-3vmw.550.0.0.1331820, VMware_bootbank_ata-pata-hpt3x2n_0.3.4-3vmw.550.0.0.1331820, VMware_bootbank_ata-pata-pdc2027x_1.0-3vmw.550.0.0.1331820, VMware_bootbank_ata-pata-serverworks_0.4.3-3vmw.550.0.0.1331820, VMware_bootbank_ata-pata-sil680_0.4.8-3vmw.550.0.0.1331820, VMware_bootbank_ata-pata-via_0.3.3-2vmw.550.0.0.1331820, VMware_bootbank_block-cciss_3.6.14-10vmw.550.0.0.1331820, VMware_bootbank_ehci-ehci-hcd_1.0-3vmw.550.0.0.1331820, VMware_bootbank_elxnet_10.0.100.0v-1vmw.550.0.0.1331820, VMware_bootbank_esx-base_5.5.0-2.39.2143827, VMware_bootbank_esx-dvfilter-generic-fastpath_5.5.0-0.0.1331820, VMware_bootbank_esx-tboot_5.5.0-2.33.2068190, VMware_bootbank_esx-xlibs_5.5.0-0.0.1331820, VMware_bootbank_esx-xserver_5.5.0-0.0.1331820, VMware_bootbank_ima-qla4xxx_2.01.31-1vmw.550.0.0.1331820, VMware_bootbank_ipmi-ipmi-devintf_39.1-4vmw.550.0.0.1331820, VMware_bootbank_ipmi-ipmi-msghandler_39.1-4vmw.550.0.0.1331820, VMware_bootbank_ipmi-ipmi-si-drv_39.1-4vmw.550.0.0.1331820, VMware_bootbank_lpfc_10.0.100.1-1vmw.550.0.0.1331820, VMware_bootbank_lsi-mr3_0.255.03.01-2vmw.550.1.16.1746018, VMware_bootbank_lsi-msgpt3_00.255.03.03-1vmw.550.1.15.1623387, VMware_bootbank_misc-cnic-register_1.72.1.v50.1i-1vmw.550.0.0.1331820, VMware_bootbank_misc-drivers_5.5.0-2.39.2143827, VMware_bootbank_mtip32xx-native_3.3.4-1vmw.550.1.15.1623387, VMware_bootbank_net-be2net_4.6.100.0v-1vmw.550.0.0.1331820, VMware_bootbank_net-bnx2_2.2.3d.v55.2-1vmw.550.0.0.1331820, VMware_bootbank_net-bnx2x_1.72.56.v55.2-1vmw.550.0.0.1331820, VMware_bootbank_net-cnic_1.72.52.v55.1-1vmw.550.0.0.1331820, VMware_bootbank_net-e1000_8.0.3.1-3vmw.550.0.0.1331820, VMware_bootbank_net-e1000e_1.1.2-4vmw.550.1.15.1623387, VMware_bootbank_net-enic_1.4.2.15a-1vmw.550.0.0.1331820, VMware_bootbank_net-forcedeth_0.61-2vmw.550.0.0.1331820, VMware_bootbank_net-igb_5.0.5.1.1-1vmw.550.1.15.1623387, VMware_bootbank_net-ixgbe_3.7.13.7.14iov-11vmw.550.0.0.1331820, VMware_bootbank_net-mlx4-core_1.9.7.0-1vmw.550.0.0.1331820, VMware_bootbank_net-mlx4-en_1.9.7.0-1vmw.550.0.0.1331820, VMware_bootbank_net-nx-nic_5.0.621-1vmw.550.0.0.1331820, VMware_bootbank_net-tg3_3.123c.v55.5-1vmw.550.2.33.2068190, VMware_bootbank_net-vmxnet3_1.1.3.0-3vmw.550.2.39.2143827, VMware_bootbank_ohci-usb-ohci_1.0-3vmw.550.0.0.1331820, VMware_bootbank_qlnativefc_1.0.12.0-1vmw.550.0.0.1331820, VMware_bootbank_rste_2.0.2.0088-4vmw.550.1.15.1623387, VMware_bootbank_sata-ahci_3.0-21vmw.550.2.39.2143827, VMware_bootbank_sata-ata-piix_2.12-10vmw.550.2.33.2068190, VMware_bootbank_sata-sata-nv_3.5-4vmw.550.0.0.1331820, VMware_bootbank_sata-sata-promise_2.12-3vmw.550.0.0.1331820, VMware_bootbank_sata-sata-sil24_1.1-1vmw.550.0.0.1331820, VMware_bootbank_sata-sata-sil_2.3-4vmw.550.0.0.1331820, VMware_bootbank_sata-sata-svw_2.3-3vmw.550.0.0.1331820, VMware_bootbank_scsi-aacraid_1.1.5.1-9vmw.550.0.0.1331820, VMware_bootbank_scsi-adp94xx_1.0.8.12-6vmw.550.0.0.1331820, VMware_bootbank_scsi-aic79xx_3.1-5vmw.550.0.0.1331820, VMware_bootbank_scsi-bnx2fc_1.72.53.v55.1-1vmw.550.0.0.1331820, VMware_bootbank_scsi-bnx2i_2.72.11.v55.4-1vmw.550.0.0.1331820, VMware_bootbank_scsi-fnic_1.5.0.4-1vmw.550.0.0.1331820, VMware_bootbank_scsi-hpsa_5.5.0-44vmw.550.0.0.1331820, VMware_bootbank_scsi-ips_7.12.05-4vmw.550.0.0.1331820, VMware_bootbank_scsi-lpfc820_8.2.3.1-129vmw.550.0.0.1331820, VMware_bootbank_scsi-megaraid-mbox_2.20.5.1-6vmw.550.0.0.1331820, VMware_bootbank_scsi-megaraid-sas_5.34-9vmw.550.2.33.2068190, VMware_bootbank_scsi-megaraid2_2.00.4-9vmw.550.0.0.1331820, VMware_bootbank_scsi-mpt2sas_14.00.00.00-3vmw.550.1.15.1623387, VMware_bootbank_scsi-mptsas_4.23.01.00-9vmw.550.0.0.1331820, VMware_bootbank_scsi-mptspi_4.23.01.00-9vmw.550.0.0.1331820, VMware_bootbank_scsi-qla2xxx_902.k1.1-9vmw.550.0.0.1331820, VMware_bootbank_scsi-qla4xxx_5.01.03.2-6vmw.550.0.0.1331820, VMware_bootbank_uhci-usb-uhci_1.0-3vmw.550.0.0.1331820, VMware_locker_tools-light_5.5.0-2.39.2143827
   VIBs Skipped:
/vmfs #

STEP 6. After updating ESXi to version 6, you will be required to update the vsphere client While installing the Vsphere 6 client I received the following error on my windows 7 workstation. I followed the suggestion here: https://communities.vmware.com/thread/453296 to set the TMP and TEMP variables to C:\TEMP. However, i still encountered the error above. Only after I logged in as another user on the computer was I able to get the installer to complete successfully. Then you must reinstall your ESXi free license key. References: http://www.v-front.de/2015/03/vsphere-6-is-ga-ultimate-guide-to.html

Profiling MySQL 5.6 InnoDB Fulltext index Performance

May 2, 2015November 22, 2015 ~ tatroc2015 ~ Leave a comment

This test was performed on Centos 6.6 running MySQL 5.6.23.
I have a MySQL table which is populated by syslog messages via syslog-ng. I discussed how to configure syslog-ng to persist logs to a MySQL database in one of my earlier posts. I wanted to use this post to review some of the performance gains you will see by using a fulltext index on TEXT column in MySQL.

The syslog messages table layout looks like the following:

CREATE TABLE `logs` (
  `host` varchar(32) DEFAULT NULL,
  `facility` varchar(10) DEFAULT NULL,
  `priority` varchar(10) DEFAULT NULL,
  `level` varchar(10) DEFAULT NULL,
  `tag` varchar(10) DEFAULT NULL,
  `datetime` datetime DEFAULT NULL,
  `program` varchar(15) DEFAULT NULL,
  `msg` text,
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`seq`),
  KEY `date_host` (`datetime`,`host`),
  KEY `idx_msg` (`msg`(255)),
  FULLTEXT KEY `fulltext_msg` (`msg`)
) ENGINE=InnoDB AUTO_INCREMENT=56252 DEFAULT CHARSET=utf8

Total table record count: 56,274

Sample SYSLOG data in table

Test 1
For my first test a perform a query using the LIKE operator.
The query completes fairly quickly in 0.00057450 seconds. This is because the MySQL query optimizer is able to use the index.

SET profiling = 1;
select * from logs
where msg like '[e8725a%';
SHOW PROFILES;

I then performed the same test using the FULL TEX index.
This query finished slower in 0.00090200 seconds. Both the LIKE query and FULL TEXT index query examined 8 rows.

SET profiling = 1;
SELECT * FROM logs
WHERE MATCH (msg) AGAINST ('[e8725a');
SHOW PROFILES;

Test 2
In my second test I performed a another LIKE query using a wildcard on both ends of the string. This resulted in the query examining 56,274 rows and returning 25 in 0.15842475 seconds. When using a wildcard on both ends of the string the query optimizer is unable to use the index and must do a full index scan.

SET profiling = 1;
select * from logs
where msg like '%/PolicyKit1%';
SHOW PROFILES;

When doing a FULL TEXT query the optimizer examined 25 rows and returned 25 row in 0.00083450 seconds. Resulting in a significantly faster query.

SET profiling = 1;
SELECT * FROM logs
WHERE MATCH (msg) AGAINST ('/PolicyKit1');
SHOW PROFILES;

In conclusion, FULL TEXT search can significantly increase the performance for queries run against large TEXT fields.

Reference: https://dev.mysql.com/doc/refman/5.6/en/fulltext-natural-language.html

Create Machine Keytab on Linux for Active Directory authentication

April 22, 2015November 22, 2015 ~ tatroc2015 ~ 1 Comment

The blog posts outline the troubleshooting I had gone through to get a machine keytab file working with Active Directory 2012 and CentOS 6.5

STEP 1. My first attempt was to create the machine keytab file using samba’s net utility.

[root@mysql04p ~]# net ads keytab create -U tatroc

Warning: "kerberos method" must be set to a keytab method to use keytab functions.
Enter tatroc's password:

In my /etc/samba/smb.conf I had the following line.

kerberos method = secrets and keytab

STEP 2. Verify that the machine principle names were created in the /etc/krb5.keytab file

[root@mysql04p ~]# klist -Kke
Keytab name: FILE:/etc/krb5.keytab
KVNO Principal
---- --------------------------------------------------------------------------
  36 host/[email protected] (des-cbc-crc)  (0x5e97f7e98083a85e)
  36 host/[email protected] (des-cbc-md5)  (0x5e97f7e98083a85e)
  36 host/[email protected] (aes128-cts-hmac-sha1-96)  (0xd96b9e5d0d6bc1f062b7faba698e1b4c)
  36 host/[email protected] (aes256-cts-hmac-sha1-96)  (0x9ebfb182419bbfda5d650a6c8a9769aaac4b7382c8fb58dcbc162978b4956a44)
  36 host/[email protected] (arcfour-hmac)  (0x31ad3e73de7991b275c269743fb0215a)
  36 host/[email protected] (des-cbc-crc)  (0x5e97f7e98083a85e)
  36 host/[email protected] (des-cbc-md5)  (0x5e97f7e98083a85e)
  36 host/[email protected] (aes128-cts-hmac-sha1-96)  (0xd96b9e5d0d6bc1f062b7faba698e1b4c)
  36 host/[email protected] (aes256-cts-hmac-sha1-96)  (0x9ebfb182419bbfda5d650a6c8a9769aaac4b7382c8fb58dcbc162978b4956a44)
  36 host/[email protected] (arcfour-hmac)  (0x31ad3e73de7991b275c269743fb0215a)
  36 [email protected] (des-cbc-crc)  (0x5e97f7e98083a85e)
  36 [email protected] (des-cbc-md5)  (0x5e97f7e98083a85e)
  36 [email protected] (aes128-cts-hmac-sha1-96)  (0xd96b9e5d0d6bc1f062b7faba698e1b4c)
  36 [email protected] (aes256-cts-hmac-sha1-96)  (0x9ebfb182419bbfda5d650a6c8a9769aaac4b7382c8fb58dcbc162978b4956a44)
  36 [email protected] (arcfour-hmac)  (0x31ad3e73de7991b275c269743fb0215a)

STEP 3. Try to initialize the keytab file. I fail to get a Kerberos ticket from Active Directory.

Received the message:
Client ‘host/[email protected]’ not found in Kerberos database while getting initial credentials

[root@mysql04p ~]# kinit -k
kinit: Client 'host/[email protected]' not found in Kerberos database while getting initial credentials
[root@mysql04p ~]#

STEP 4. After some googling, I discovered that I could create the machine keytab through another avenue. By logging into the domain controller and running the ktpass.exe program as administrator.

C:\Windows\system32>ktpass /princ host/[email protected] /mapuser LAB\mysql04p$ +rndPass /crypto all /ptype KRB5_
NT_PRINCIPAL /out krb5.keytab

Targeting domain controller: DC01.lab.net
Using legacy password setting method
Successfully mapped host/mysql04p.lab.net to mysql04p$.
WARNING: Account mysql04p$ is not a user account (uacflags=0x11001).
WARNING: Resetting mysql04p$'s password may cause authentication problems if mysql04p$ is being used as a server.

Reset mysql04p$'s password [y/n]?  y
WARNING: pType and account type do not match. This might cause problems.
Key created.
Key created.
Key created.
Key created.
Key created.
Output keytab to krb5.keytab:
Keytab version: 0x502
keysize 56 host/[email protected] ptype 1 (KRB5_NT_PRINCIPAL) vno 37 etype 0x1 (DES-CBC-CRC) keylength 8 (0xf1730
404c80bd37a)
keysize 56 host/[email protected] ptype 1 (KRB5_NT_PRINCIPAL) vno 37 etype 0x3 (DES-CBC-MD5) keylength 8 (0xf1730
404c80bd37a)
keysize 64 host/[email protected] ptype 1 (KRB5_NT_PRINCIPAL) vno 37 etype 0x17 (RC4-HMAC) keylength 16 (0x54eaee
166661deb5ee63eec76faec0e1)
keysize 80 host/[email protected] ptype 1 (KRB5_NT_PRINCIPAL) vno 37 etype 0x12 (AES256-SHA1) keylength 32 (0x553
cb01aba00c555c88d355b7196585cb9968ce4ecda7070cd1d89e6ee4413d0)
keysize 64 host/[email protected] ptype 1 (KRB5_NT_PRINCIPAL) vno 37 etype 0x11 (AES128-SHA1) keylength 16 (0xee5
9e989a9033978ef299330a36c7d0b)

C:\Windows\system32>

STEP 5. Copy the keytab file to the Linux computer mysql04p:/etc/krb5.keytab
The initialize the keytab file.

[root@mysql04p ~]# kinit -k

STEP 6. run klist to verify that the machine has received a ticket.

[root@mysql04p ~]# klist
Ticket cache: FILE:/tmp/krb5cc_0
Default principal: host/[email protected]

Valid starting     Expires            Service principal
04/22/15 17:56:13  04/23/15 03:56:13  krbtgt/[email protected]
        renew until 04/29/15 17:56:13
[root@mysql04p ~]#

STEP 7. You can now use the keytab file to authenticate to resources in Active Directory.

kinit -k; ls -la

Summary:
I was able to successfully create the machine keytab by using the ktpass.exe on the windows 2012 domain controller. Then copying that file to the Linux system.

Extend Linux Logical Volume

April 14, 2015April 14, 2015 ~ tatroc2015 ~ Leave a comment

This support document describes the steps needed to extend the a logical volume in Linux

STEP 1. View the current file system to device mappings

 a.  df -h

STEP 2. Use cfdisk to create another partition with the free space on /dev/sdb

 a.  cfdisk /dev/sdb
 b. In the cfdisk menu select the available free space
 c. Select create PRIMARY
 d. Select the SIZE of the new primary partition
 e. Select TYPE > Select type 8E (Linux LVM)
 f. Select WRITE 
 g.  fdisk -l (run fdisk -l to verify the new partition was created)

STEP 3. The next step is to use pvcreate which initializes the Physical Volume for later use by the Logical Volume Manager (LVM)

reboot server previous to using pvcreate, or it won’t see the new partition

pvcreate /dev/sdb2

STEP 4. Run volume group display to see the name of the volume groups on the server:

vgdisplay

STEP 5. Add the new partition you created to the volume group which you are extending

vgextend vg_server01 /dev/sdb2

STEP 6. Extend the volume on the volume group

lvextend /dev/mapper/vg_server01-lv_home /dev/sdb2

STEP 7. Resize the file system:

 a.  resize2fs /dev/mapper/vg_server01-lv_home

Create custom SNMP OID in Linux

April 4, 2015April 4, 2015 ~ tatroc2015 ~ 2 Comments

This guide was written using CentOS 6.5. I describe how to create a custom script to obtain the 1 minute load average on a LInux system and expose that script in SNMP to be polled by an SNMP poller like Nagios or Solarwinds.

STEP 1. create the 1 minute load average script.

vim /usr/sbin/1minloadavg.sh

#!/bin/bash
awk '{print $1}' /proc/loadavg

STEP 2. Update the snmpd.conf file to extend SNMP.

vim /etc/snmp/snmpd.conf

extend 1minloadavg /usr/sbin/1minloadavg.sh

STEP 3. Restart SNMPD

service snmpd restart

STEP 4. Walk the NET-SNMP-EXTEND MIB.

snmpwalk -v2c -c public localhost NET-SNMP-EXTEND-MIB::nsExtendObjects | grep 1minloadavg

NET-SNMP-EXTEND-MIB::nsExtendCommand."1minloadavg" = STRING: /usr/sbin/1minloadavg.sh
NET-SNMP-EXTEND-MIB::nsExtendArgs."1minloadavg" = STRING:
NET-SNMP-EXTEND-MIB::nsExtendInput."1minloadavg" = STRING:
NET-SNMP-EXTEND-MIB::nsExtendCacheTime."1minloadavg" = INTEGER: 5
NET-SNMP-EXTEND-MIB::nsExtendExecType."1minloadavg" = INTEGER: exec(1)
NET-SNMP-EXTEND-MIB::nsExtendRunType."1minloadavg" = INTEGER: run-on-read(1)
NET-SNMP-EXTEND-MIB::nsExtendStorage."1minloadavg" = INTEGER: permanent(4)
NET-SNMP-EXTEND-MIB::nsExtendStatus."1minloadavg" = INTEGER: active(1)
NET-SNMP-EXTEND-MIB::nsExtendOutput1Line."1minloadavg" = STRING: 0.08
NET-SNMP-EXTEND-MIB::nsExtendOutputFull."1minloadavg" = STRING: 0.08
NET-SNMP-EXTEND-MIB::nsExtendOutNumLines."1minloadavg" = INTEGER: 1
NET-SNMP-EXTEND-MIB::nsExtendResult."1minloadavg" = INTEGER: 0
NET-SNMP-EXTEND-MIB::nsExtendOutLine."1minloadavg".1 = STRING: 0.08

STEP 5. Grab the named OID you wish to monitor and use snmptranslate to translate the named OID to a numerical value.

[root@mysql01p ~] snmptranslate -On NET-SNMP-EXTEND-MIB::nsExtendOutputFull.\"1minloadavg\"
.1.3.6.1.4.1.8072.1.3.2.3.1.2.11.49.109.105.110.108.111.97.100.97.118.103

STEP 6.
The OID can then be entered into your favorite SNMP poller. In this case I use Solarwinds Universal Device Poller. However, there are many open source solutions like Nagios which also support this.

Create a keytab file on Windows and use Java and Kerberos to connect to SQL SERVER

March 27, 2015March 27, 2015 ~ tatroc2015 ~ Leave a comment

I currently work in a mixed environment containing box Linux and Windows computers. This can make authentication at times challenging. Below is an example java program which allows you to connect using kerberos to a SQL SERVER from a Windows or Linux client. The process involves creating a keytab file and a java login context file. This keytab file can be used to authenticate to windows resources like SQL SERVER and file servers using Java. The keytab file stores your username and password in an encrypted format.

STEP 1. Create a keytab file


cd "C:\Program Files\Java\jdk1.8.0_31\bin"
ktab.exe -a [email protected] P@ssword -k user01.keytab

STEP 2. You reference the keytab in your java login conf file.

java-login {
 com.sun.security.auth.module.Krb5LoginModule required
 useTicketCache=false
 doNotPrompt=true
 useKeyTab=true
 debug=true
 keyTab="c:/eclipse/user01.keytab"
 principal="[email protected]"
 };

STEP 3. The Java code then references the java login configuration file, then you make the SQL SERVER connection using the subject [email protected].

import java.security.PrivilegedExceptionAction;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.security.auth.Subject;
import javax.security.auth.login.LoginContext;
import javax.security.auth.login.LoginException;


public class main {


        public static void main(String[] args) {

                System.setProperty("java.security.auth.login.config", "c:/eclipse/java-login.conf");

                Subject subject = null;



                try {
                    LoginContext loginContext = new LoginContext("java-login");
                    loginContext.login();
                    subject = loginContext.getSubject();

                }
                catch (LoginException e)
                {
                    e.printStackTrace();
                }


        //  This application passes the javax.security.auth.Subject
        //  to the driver by executing the driver code as the subject
try {
                @SuppressWarnings("unchecked")
                Connection con = (Connection) Subject.doAs(subject, new PrivilegedExceptionAction() {

                                    public Object run() {

                                        Connection con = null;
                                    try {
                                         //
                                         Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                                         String url = "jdbc:sqlserver://SQLSERVER.lab.net;instanceName=sqlinstance01;database=Accounting;integratedSecurity=true;authenticationScheme=JavaKerberos";
                                         con = java.sql.DriverManager.getConnection(url);
                                        }
                                     catch (Exception except) {
                                                except.printStackTrace();
                                     //log the connection error
                                           return null;
                                        }

                                        return con;
                                    }
                });


                String SQL = "select * from dbo.table01";
                Statement stmt = con.createStatement();
                ResultSet rs = stmt.executeQuery(SQL);

                while (rs.next())
                {
                        System.out.println(rs.getString(1));
                }
}
catch (Exception e)     {
        e.printStackTrace();
}



        } // end of method main



} // end of class main

SolarWinds SAM Appinsight for SQL SERVER causing deadlocks

March 23, 2015November 22, 2015 ~ tatroc2015 ~ Leave a comment

I recently found a bug in the way Solar winds monitor’s our databases. I am running Solarwinds Server application Monitor 11.1 and SQL SERVER Application Insight. Ever since I turned on the Application Insight feature I started seeing deadlock’s occurring in my SQL SERVER environment. Upon further investigation I noticed the Solarwinds Application Insight for SQL SERVER monitoring application was doing a read committed operation on system tables in all my database.

I sent the email below to Solar winds support and suggested the Solar winds developer change the query to a read uncommitted. Several days later I heard back from the Solar winds application engineer stating that they had a script for me to run. I ran the script which updated the monitoring query to be read uncommitted. I saw an immediate drop in the number of deadlocks alerts coming from my SQL SERVER. The moral of the story is to test monitoring thoroughly before using it in production. As in this case the monitoring server was at times causing transactions to be terminated unexpectedly.

—–Original Message—–
From: [email protected]
Sent: Monday , January 12, 2015 12:31 pm GMT (GMT+00:00)
Subject: appinsight for sql is causing deadlocks

The issue I am seeing is that solarwinds is locking system tables when its
app insight sql monitor runs. Here is one example where app insight
deadlocked on tempdb.sys.sysidxstats. Might I suggest that solarwinds
change the software so it does a “dirty read” or in other words read
uncommitted. This would prevent the deadlocks from happening.

Here is a excerpt from the deadlock XML file. The SAM App Insight monitor is running in isolation level read committed.

<process id="process7481048" taskpriority="0" logused="0" waitresource="KEY: 2:281474980249600 (45001d8e01fc)" waittime="1219" ownerI
d="825842724" transactionname="SELECT" lasttranstarted="2015-01-11T13:36:28.200" XDES="0x80039960" lockMode="S" schedulerid="6" kpid="2680" status="suspended" spid="143" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2015-01-11T13:36:28.133
" lastbatchcompleted="2015-01-11T13:36:28.090" clientapp=".Net SqlClient Data Provider" hostname="SW01" hostpid="16352" loginname="DOMAIN\sa-monitor" isolationlevel="read committed (2)" xactid="825842724" currentdb="2" lockTimeout="4294967295" cliento
ption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="26" sqlhandle="0x02000000b680b70233f6dc8a15fa42960c50200f89756a25">
SELECT TOP 10
	t.NAME AS TableName,
	t.object_id,
	mainIndex.data
_space_id,
	p.rows AS RowCounts,
	SUM(a.total_pages) * 8 AS TotalSpaceKB,
	SUM(CASE i.[type]
	WHEN 2 THEN 8 * a.used_pages
	ELSE 0
	END) AS IndexSpaceKB
FROM [sys].[tables] t
INNER JOIN sys.indexes mainIndex ON (t.object_id = mainIndex.object_id AND mainIn
dex.[type] IN (0,1))
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN [sys].[partitions] p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN [sys].[allocation_units] a ON p.partition_id = a.container_id
WHERE t.NAME NOT LI
KE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID &gt; 255 GROUP BY t.Name, t.object_id, mainIndex.data_space_id, p.Rows
ORDER BY TotalSpaceKB DESC     </frame>
     <frame procname="adhoc" line="21" stmtstart="1748" stmtend="1786" sqlhandle="0x0200000
0db583517807077e95c2490bb681fca4f9e78724f">
EXECUTE (@sqlQuery)     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>


<keylock hobtid="281474980249600" dbid="2" objectname="tempdb.sys.sysidxstats" indexname="clst" id="lock30e2f1d00" mode="X" associatedObjectId="
281474980249600">
    <owner-list>
     <owner id="process39d30c088" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process7481048" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>

Solarwinds Support provided the following SQL to change the ISOLATION LEVEL to READ UNCOMMITTED. Since included the new SQL in Solarwinds I have not seen any deadlocks.

-- FB395905 AppInsight for SQL is causing SQL Deadlocks
-- specifying isolation level for polling
UPDATE cts SET Value = 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @TimeStamp datetime
SET @TimeStamp = GETUTCDATE()
SELECT
	name as dbName,
	d.database_id,
	[compatibility_level],
	collation_name,
	[state],
	recovery_model,
	DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), bk.last_backup) as last_backup,
	avg_read_latency,
	avg_write_latency,
	avg_bytes_per_read,
	avg_bytes_per_write,
	num_of_reads,
	num_of_writes,
	@TimeStamp as TimeStamp
FROM sys.databases d
LEFT JOIN
(
SELECT bs.database_name, MAX(bs.backup_finish_date) last_backup
FROM msdb.dbo.backupset bs
WHERE server_name = SERVERPROPERTY(''ServerName'') GROUP BY bs.database_name
) AS bk ON d.name = bk.database_name
LEFT JOIN
(
SELECT  database_id,
	SUM(io_stall_read_ms / NULLIF(num_of_reads, 0)) AS avg_read_latency,
	SUM(io_stall_write_ms / NULLIF(num_of_writes, 0)) AS avg_write_latency,
	SUM(num_of_bytes_read / NULLIF(num_of_reads, 0)) AS avg_bytes_per_read,
	SUM(num_of_bytes_written / NULLIF(num_of_writes, 0)) AS avg_bytes_per_write,
	SUM(num_of_reads) AS num_of_reads,
	SUM(num_of_writes) AS num_of_writes
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
GROUP BY database_id
)AS [io] ON d.database_id = [io].database_id
WHERE name NOT IN (${UnmanagedDatabases})'
FROM APM_ComponentTemplateSetting cts
INNER JOIN APM_ComponentTemplate ct ON cts.ComponentTemplateID = ct.ID
WHERE ct.UniqueId = '15868507-6248-4DE6-9A83-561535EBC058' AND cts.[Key] = 'SqlQuery'

-- Enforce job rescheduling for ABSA applications
UPDATE a SET LastModified = GETUTCDATE()
FROM APM_Application a INNER JOIN APM_ApplicationTemplate at ON a.TemplateID = at.ID
WHERE at.CustomApplicationType = 'ABSA'

SQL Server deadlock logging with email alerts

March 23, 2015March 23, 2015 ~ tatroc2015 ~ Leave a comment

This configuration was setup on Windows 2012 and SQL SERVER 2012, it was also tested on SQL SERVER 2008.

Create a table named DeadlockEvents, this will be used to store the date and time stamp of the deadlock event as well as the XML generated from the deadlock event.

STEP 1.
Create a table to hold the SQL SERVER deadlocks. One field will contain the time at which the deadlock occurred and the other the XML from the deadlock.


/** 
Create Table to hold deadlocks
**/

USE DBA ;
GO

IF OBJECT_ID('DeadlockEvents', 'U') IS NOT NULL
BEGIN
    DROP TABLE DeadlockEvents ;
END ;
GO

CREATE TABLE DeadlockEvents
    (AlertTime DATETIME, DeadlockGraph XML) ;
GO

STEP 2.
Add a SQL Agent job to trigger an insert of a deadlock record when a deadlock event occurs.

The following fields will need to be populated with your environment specific information.
@owner_login_name=N’DOMAIN\sa-job-owner’


/** Add JOB**/

USE [msdb]
GO

/****** Object:  Job [DBM2_Capture_Deadlock_Graph]    Script Date: 11/13/2014 13:54:07 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 11/13/2014 13:54:07 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBM2_Capture_Deadlock_Graph', 
		@enabled=1, 
		@notify_level_eventlog=2, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Job for responding to DEADLOCK_GRAPH events', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'DOMAIN\sa-job-owner', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Insert graph into LogEvents]    Script Date: 11/13/2014 13:54:08 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert graph into LogEvents', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'INSERT INTO DeadlockEvents
                (AlertTime, DeadlockGraph)
                VALUES (getdate(), N''$(ESCAPE_SQUOTE(WMI(TextData)))'')', 
		@database_name=N'DBA', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [send email]    Script Date: 11/13/2014 13:54:08 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'send email', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'exec sp_deadlock_alert', 
		@database_name=N'DBA', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

STEP 3.
This stored procedure is run when the SQL Agent job is triggered. I sends a copy of the deadlock XML to the email address specified as the recipient.


/** Add stored proc **/

use DBA;
GO
create procedure sp_deadlock_alert
AS

DECLARE @profile varchar(30)
set @profile = (select top 1 name from msdb.dbo.sysmail_profile)

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @profile,
    @recipients = '[email protected]',
    @body = 'DeadLock Graph',
    @subject = 'A deadlock has occurred, please review the provided attachment...',
    @body_format = 'TEXT',
    @query_attachment_filename = 'Deadlockgraph.xml',
    @attach_query_result_as_file = 1,
    @query_no_truncate = 1,
     --@query_result_width = 512,
     --@query_result_no_padding = 1,
    @query = 'SELECT TOP 1 [DeadlockGraph]
  FROM [DBA].[dbo].[DeadlockEvents]
  order by alerttime desc
    for xml PATH(''ROW''), root(''ROOT''), TYPE';

STEP 4.
Add alert to fire SQL Agent job when a deadlock event occurs.
The job named DBM2_Capture_Deadlock_Graph will file whenever a deadlock event occurs.
For this to work you token replacement enabled on the SQL AGENT.
Right-click SQL Server Agent in Object Explorer, select Properties, and on the Alert System page, select Replace tokens for all job responses to alerts to enable these tokens.

/** Add Alert **/
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Respond to DEADLOCK_GRAPH', 
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', 
    @wmi_query=N'SELECT * FROM DEADLOCK_GRAPH', 
    @job_name='DBM2_Capture_Deadlock_Graph' ;
GO

STEP 5.
Testing the deadlock alerting

Open a SSMS query window and execute the following SQL.

use dba;
BEGIN TRAN
update table_a set ID=ID where ID = 100;

update table_b set ID=ID where ID =100;

Open a second SSMS query window and execute the following SQL.
This should result in a deadlock which inserts a record into the DeadlockEvents table and sends an email alerting you of the deadlock.

use dba;
BEGIN TRAN
update table_b set ID=ID where ID =100;

update table_a set ID=ID where ID = 100;

Connecting to MSSQL SERVER using Microsoft Java ODBC driver and Kerberos

March 22, 2015March 22, 2015 ~ tatroc2015 ~ Leave a comment

I did not find a lot of documentation for connecting to SQL SERVER using Kerberos authentication and JAVA. So I decided to write this little blog post up.

The key piece of information is using authenticationScheme=JavaKerberos in the connection string.
This works on both Windows and Linux Operating System as long as you have Kerberos ticket. You can verify if you have a Kerberos ticket by typing klist on the command line.
You should see something like the following for your user.

[user1@vm01 ~]$ klist
Ticket cache: FILE:/tmp/krb5cc_16777216_kbQnZ2
Default principal: [email protected]
 
Valid starting     Expires            Service principal
10/22/14 07:23:58  10/22/14 17:23:58  krbtgt/[email protected]
    renew until 10/29/14 07:23:58

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import com.microsoft.sqlserver.*;

public class main {
	public static void main(String[] args) {
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		String connectionUrl = "jdbc:sqlserver://SQLSERVER01;instanceName=SQLINSTANCE01;database=Inventory;integratedSecurity=true;authenticationScheme=JavaKerberos";
		
		try {
			Connection con = java.sql.DriverManager.getConnection(connectionUrl);
			System.out.println("connected...");
			
			String SQL = "select * from dbo.table01";
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(SQL);
			

			while (rs.next())
			{
				System.out.println(rs.getString(1));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}