Category: Uncategorized

Microsoft Contoso BI Demo Dataset for Retail Industry

    Today, I was working on demo where I wanted to have test dataset for Retail db and after some googing I found MS has it’s own database and here are details –
    The Contoso BI Demo dataset is used to demonstrate DW/BI functionalities across the entire Microsoft Office product family. This dataset includes C-level, sales/marketing, IT, and common finance scenarios for the retail industry and support map integration. In addition, this dataset offers large volumes of transactions from OLTP and well-structured aggregations from OLAP, along with reference and dimension data.
    Steps to restore Contoso BI Demo Database
    You can download from below location –
    2.  After download please run the .exe files and it will ask you to unzip
    3. It will generate ContosoRetailDW.bak file along with Data Dictionary of Contoso BI demo dataset for Retail Industry.xls file
    4. Restore database with below script – you can modify your file

Script

USE [master]
RESTORE DATABASE [ContosoRetailDW] FROM DISK = N’C:\ContosoBIDB\ContosoRetailDW.bak’ WITH FILE = 1, MOVE N’ContosoRetailDW2.0′ TO N’D:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\ContosoRetailDW.mdf’, MOVE N’ContosoRetailDW2.0_log’ TO N’D:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\ContosoRetailDW.ldf’, NOUNLOAD, STATS = 5

GO

 

Advertisements

Configuring deadlock and Storing Deadlock events in a table

Configuring deadlock and Storing Deadlock events in a table

SQL Server 2012 and above.

Verification –

Ensure Extended Event “system_health” is running.

SSMS, checking Management -> Extended Events -> Sessions. These should be green arrow which indicate its running.

p1

 

The system_health session writes to two targets, both an event file and the ring buffer:

p2

 

Right click on package0.event_file and click on View Target Data…

p3

p4

Configuring Deadlock Event Id and logging.

Step 1 –

Enable deadlock logging

p5_1

–TO CHECK IF LOGGING IS ENABLED (is_event_logged)
SELECT * FROM sys.messages WHERE language_id = 1033 AND message_id = 1205

–TO ALTER THE LOGGING PARAMETER
EXEC sp_altermessage @message_id = 1205, @parameter = ‘WITH_LOG’ ,@parameter_value = ‘true’
GO

Step 2 –

Create SQL Server Job called “Deadlock Capture Job” and add step with script called “Deadlock_Capture_Script”

SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON

DECLARE @LastDeadLock DATETIME2, @XE_EVENT_FILE_TARGET XML, @XE_FILE_PATH NVARCHAR(260)

SELECT @XE_EVENT_FILE_TARGET = CONVERT(XML, xst.target_data)
FROM sys.dm_xe_sessions xs
JOIN sys.dm_xe_session_targets xst
ON xs.address = xst.event_session_address
WHERE xs.name = ‘system_health’
AND xst.target_name = ‘event_file’

SELECT @XE_FILE_PATH = t.c.value(‘File[1]/@name’, ‘varchar(260)’) FROM @XE_EVENT_FILE_TARGET.nodes(‘/EventFileTarget’) AS t(c)

SELECT CAST(event_data AS XML) AS event_data
INTO #DEADLOCKS_DATA
FROM sys.fn_xe_file_target_read_file(@XE_FILE_PATH, null, null, null)
WHERE object_name = ‘xml_deadlock_report’

IF (SELECT OBJECT_ID(‘DEADLOCKS_Details’)) IS NULL BEGIN
CREATE TABLE DEADLOCKS_Details
(
DeadlockTimestamp DATETIME2 NOT NULL,
DeadlockEvent NVARCHAR(250) NOT NULL,
VictimProcessId NVARCHAR(250) NOT NULL,
Processid_1 NVARCHAR(250) NOT NULL,
WaitResource_1 NVARCHAR(250) NULL,
WaitTime_1 INT NULL,
OwnerId_1 BIGINT NULL,
CurrentDb_1 INT NULL,
LastTranStarted_1 DATETIME2 NULL,
LastBatchStarted_1 DATETIME2 NOT NULL,
Statement_1 NVARCHAR(MAX) NULL,
Client_1 NVARCHAR(250) NULL,
Hostname_1 NVARCHAR(250) NULL,
Loginname_1 NVARCHAR(250) NULL,
Processid_2 NVARCHAR(250) NOT NULL,
WaitResource_2 NVARCHAR(250) NULL,
WaitTime_2 INT NULL,
OwnerId_2 BIGINT NULL,
CurrentDb_2 INT NULL,
TranStarted_2 DATETIME2 NULL,
LastBatchStarted_2 DATETIME2 NOT NULL,
Statement_2 NVARCHAR(MAX) NULL,
Client_2 NVARCHAR(250) NULL,
Hostname_2 NVARCHAR(250) NULL,
Loginname_2 NVARCHAR(250) NULL
)
END

SELECT @LastDeadLock = ISNULL(MAX(DeadlockTimestamp), ‘1900-01-01’) FROM DEADLOCKS_Details

;WITH CTE_EVENTS AS
(
SELECT
c.value(‘@timestamp’, ‘DATETIME2’) AS DeadlockTimestamp,
c.value(‘(@name)[1]’, ‘nvarchar(250)’) AS DeadlockEvent,
c.value(‘(data/value/deadlock/victim-list/victimProcess)[1]/@id’, ‘nvarchar(250)’) AS VictimProcessId,
c.value(‘(data/value/deadlock/process-list/process)[1]/@id’, ‘nvarchar(250)’) AS Processid_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@waitresource’, ‘nvarchar(250)’) AS WaitResource_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@waittime’, ‘int’) AS WaitTime_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@ownerId’, ‘bigint’) AS OwnerId_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@currentdb’, ‘int’) AS CurrentDb_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@lasttranstarted’, ‘datetime2’) AS LastTranStarted_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@lastbatchstarted’, ‘datetime2’) AS LastBatchStarted_1,
c.value(‘(data/value/deadlock/process-list/process/inputbuf)[1]’, ‘nvarchar(max)’) AS Statement_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@clientapp’, ‘nvarchar(250)’) AS Client_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@hostname’, ‘nvarchar(250)’) AS Hostname_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@loginname’, ‘nvarchar(250)’) AS Loginname_1,
c.value(‘(data/value/deadlock/process-list/process)[2]/@id’, ‘nvarchar(250)’) AS Processid_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@waitresource’, ‘nvarchar(250)’) AS WaitResource_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@waittime’, ‘int’) AS WaitTime_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@ownerId’, ‘bigint’) AS OwnerId_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@currentdb’, ‘int’) AS CurrentDb_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@lasttranstarted’, ‘datetime2’) AS LastTranStarted_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@lastbatchstarted’, ‘datetime2’) AS LastBatchStarted_2,
c.value(‘(data/value/deadlock/process-list/process/inputbuf)[2]’, ‘nvarchar(max)’) AS Statement_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@clientapp’, ‘nvarchar(250)’) AS Client_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@hostname’, ‘nvarchar(250)’) AS Hostname_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@loginname’, ‘nvarchar(250)’) AS Loginname_2
FROM #DEADLOCKS_DATA
CROSS APPLY event_data.nodes(‘//event’) AS t (c)
)
INSERT DEADLOCKS_Details
SELECT *
FROM CTE_EVENTS
WHERE DeadlockTimestamp > @LastDeadLock

DROP TABLE #DEADLOCKS_DATA

Select the database from Dropdown

p5

Step 3 –

Create deadlock Alerts in SQL Server Agent-

p7

p8

 

 

 

 

SQL Server 2012 Always On Step by Step Configuration

Learn Sql Team

SQL Server 2012 always on Step by step configuration –

Prerequisite to configure always on –

1. Domain accounts for SQL Server services should have local admin   access
2. Two IP addess
One is for Cluster
Other one is for always ON listener
Note – These IP address should be in customer facing sub-net
3. UNC share on any non-cluster server and non-domain controller.
Cluster computer account and each node computer account should have  RW access to this share.

if you are not having domain controller access ask your Window Admin team

– Pre-staged cluster computer account in Active Directory. Account can be pre-staged according to the following article: http://technet.microsoft.com/en-us/library/cc731002.aspx#BKMK_steps_precreating

– Pre-staged AlwaysOn listener computer account in Active Directory. Account can be pre-staged according to the following article: http://technet.microsoft.com/en-us/library/cc731002(WS.10).aspx#BKMK_steps_precreating2

Windows cluster installation: 

1) Install Failover Cluster feature using Server Manager on both/all nodes.

2) Open Failover Cluster Manager and create a new cluster:

Cluster1.PNG
3)…

View original post 621 more words