Query Tuning | SQL Conjuror

SQL Server – How To Configure Peer-to-Peer Replication in SQL Server 2016

By Enrique 10/03/2018 29/04/2020 SQL SERVER, Database Administration

First, let’s run through the Replication components before we setup Peer-to-Peer replication.

Article – the object we want to replicate
Publication – Group of articles
Publisher – Server that contains publication
Subscriber – Receives publication
Distributor – Controls agent and tracks subscribers
Agent – Tasks to move data

What is Peer-to-Peer Replication?

In Peer-to-Peer, what it does is that it takes the one-way street where everything that happens in the publisher gets pushed off to the subscriber and turns it into a two-way street (bi-directional replication). Everything that happens in the subscriber can also be pushed to the publisher. In Peer-to -Peer, we only have conflict detection not conflict resolution. The key with peer to peer is to avoid conflict entirely.

Steps To Setup Peer-to-Peer Replication:

1. Create distribution on all instances that are participating in Peer-to-Peer.

You must configure a distributor for each node that will be participating in peer-to-peer else you will encounter the error below.

To set up distributor, follow the steps below.

right-click on Replication > Configure Distribution…

Click Next.

Choose the first option. Click Next.

Specify a snapshot folder path. Click Next.

Click Next.

Click Next.

Click Finish.

Click Close.

Note: Do the same for all the instances that will be participating in Peer-to-Peer Replication

2. Backup the databases to replicate then restore it in the subscriber node (data needs to be synchronized first before we configure peer-to-peer)

3. Create publication.

Right-click on Local Publication > New Publication…

Click Next.

Choose the database where the articles to be published are located. Click Next.

Choose Peer-to-Peer Publication. Click Next.

Choose the object/s to publish. Click Next.

Click Next.

It is a best practice to specify the account that you will use to run the Log Reader Agent process. However, for the purpose of this Demo, I will just use the SQL Server Agent service account. Click OK.

Click Next.

Optionally, you can generate a script file with steps to create the publication. I will not choose to in this demo.

Click Next.

Give your publication a name then click Finish.

Click Close.

4. Configure Peer-to-Peer topology.

Right-click on your publication > Configure Peer-to-Peer Topology.

Choose the newly created publication. Click Next.

Hover your cursor on the first node. Take note of the Peer originator id. As you can see, it is 100.

Now, on the topology Wizard, right-click on the design surface> Add a New Peer Node.

Connect to subscriber node.

Select the database on your subscriber. This is the database that we restored from your publisher. If you recall, at the beginning of this tutorial, that we backed up the database that we want to replicate and restored it in the subscriber node.

The Peer Originator ID of the first node is 100 remember? This is now the 2nd node and we will set the Peer Originator ID to 101. If we want add more nodes to the topology, then just set the Peer Originator ID of the next node to 102, the next node to 103, etc. You get the idea.

Tick Connect to ALL displayed nodes and choose Use Push subscription (optionally you can choose Pull subscription)

Click OK.

Now we can see the 2nd node that we just added visually on the design surface.

Click Next.

Click the ellipsis…

It is a best practice to specify an account that you will use to run the Log Reader Agent process. However, for the purpose of this Demo, I will just use the SQL Server Agent service account. Click OK.

Click Next.

Click the ellipsis…

It is a best practice to specify an account that you will use to run the Distribution Agent process. However, for the purpose of this Demo, I will just use the SQL Server Agent service account. Click OK.

Tick the “Use the first peer’s security settings for all other peers”. This is useful especially if you have many peers configured.

Click Next.

In the next page of the wizard, we have to specify how we want the new peer(s) to be initialized. Choose the first option if you know that there are no changes made since the last the backup was taken. However, if there were data modification after the backup was taken, choose the second option.

Click Next.

Review the choices that you have made, then click Finish.

Click Close.

In this blog post, we have learned how to setup Peer-to-Peer Replication. Everything that happens in the subscriber will also be published to the publisher. Just take note that in Peer-to-Peer, we only have conflict detection not conflict resolution. We have to avoid conflict entirely.

Facebook

Google

Twitter

No comments

SQL Server – Get Index Creation Date

By Enrique 19/07/2017 29/04/2020 SQL SERVER, Database Administration, T-SQL

There are many T-SQL scripts to get the index creation date but I always use this one.

 DECLARE @filename VARCHAR(500) 
SELECT @filename = CAST(value AS VARCHAR(500)) 
FROM fn_trace_getinfo(DEFAULT) 
WHERE property = 2 
  AND value IS NOT NULL 

-- Go back 4 files since default trace only keeps the last 5 and start from there.
SELECT @filename = substring(@filename, 0, charindex('_', @filename)+1) + convert(varchar, (convert(int, substring(left(@filename, len(@filename)-4), charindex('_', @filename)+1, len(@filename)))-4)) + '.trc'

SELECT 
       gt.EventClass, 
       gt.EventSubClass,
       te.Name AS EventName,
       gt.HostName, 
       gt.StartTime, 
       gt.DatabaseName,
       gt.ObjectName
FROM fn_trace_gettable(@fileName, DEFAULT) gt 
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id 
WHERE EventClass = 46
  and ObjectType = 22601
  and gt.DatabaseName <> 'tempdb'
ORDER BY StartTime desc;

SQL Server – Get VLF Counts for All Databases in an Instance

By Enrique 21/03/2017 30/04/2020 SQL SERVER, Database Administration

Below is a script to get the VLF count for all databases on an instance.

CREATE TABLE #VLFInfo
(
    RecoveryUnitID INT
    ,FileID INT
    ,FileSize BIGINT
    ,StartOffset BIGINT
    ,FSeqNo BIGINT
    ,Status BIGINT
    ,Parity BIGINT
    ,CreateLSN NUMERIC(38)
);

CREATE TABLE #VLFCountResults
(
    DatabaseName sysname
    ,VLFCount INT
)
    
EXEC sp_MSforeachdb N'Use [?];

        INSERT INTO #VLFInfo
        EXEC sp_executesql N''DBCC LOGINFO([?])'';

        INSERT INTO #VLFCountResults
        SELECT DB_NAME(), COUNT(*)
        FROM #VLFInfo;

        TRUNCATE TABLE #VLFInfo;'

SELECT 
    DatabaseName
    ,VLFCount
FROM 
    #VLFCountResults
ORDER BY 
    VLFCount DESC

DROP TABLE #VLFInfo
DROP TABLE #VLFCountResults

-- High VLF counts can affect write performance
-- and they can make database restored and recovery take much longer

SQL Server – Check Latest Transaction Log Backup

By Enrique 13/03/2017 30/04/2020 SQL SERVER, Database Administration

To know what the last transaction log backup of your database was, execute the T-SQL script below.

SELECT

CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS ServerName,

msdb.dbo.backupset.database_name AS DatabaseName,

MAX(msdb.dbo.backupset.backup_finish_date) AS Last_TLog_Backup_Date

FROM msdb.dbo.backupmediafamily

INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE msdb..backupset.type = 'L'

GROUP BY

msdb.dbo.backupset.database_name

ORDER BY

msdb.dbo.backupset.database_name

SQL Server – Check SQL Server Error Logs via T-SQL

By Enrique 13/03/2017 30/04/2020 SQL SERVER, Database Administration, T-SQL, Uncategorized

Below is a script to check SQL Server Error logs.

declare @Time_Start datetime;

declare @Time_End datetime;

set @Time_Start=getdate()-2;

set @Time_End=getdate();

-- Create the temporary table

CREATE TABLE #ErrorLog (logdate datetime

, processinfo varchar(255)

, Message varchar(MAX))

-- Populate the temporary table

INSERT #ErrorLog (logdate, processinfo, Message)

EXEC master.dbo.xp_readerrorlog 0, 1, null, null , @Time_Start, @Time_End, N'desc';

-- Filter the temporary table

SELECT LogDate, Message FROM #ErrorLog

WHERE (Message LIKE '%error%' OR Message LIKE '%failed%') AND processinfo NOT LIKE 'logon'

ORDER BY logdate DESC

-- Drop the temporary table

DROP TABLE #ErrorLog

SQL Server – TSQL Script to Check Job Run Status

By Enrique 11/03/2017 30/04/2020 SQL SERVER, Database Administration

Below is a T-SQL script to check SQL job run status.

SET NOCOUNT ON

--Checking for SQL Server verion

IF CONVERT(tinyint,(SUBSTRING(CONVERT(CHAR(1),SERVERPROPERTY('productversion')),1,1))) <> 8

BEGIN

---This is for SQL 2k5 and SQL2k8 servers

SET NOCOUNT ON

SELECT Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName,

j.name AS job_name,

CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END AS job_status,

CASE jh.run_status WHEN 0 THEN 'Error Failed'

WHEN 1 THEN 'Succeeded'

WHEN 2 THEN 'Retry'

WHEN 3 THEN 'Cancelled'

WHEN 4 THEN 'In Progress' ELSE

'Status Unknown' END AS 'last_run_status',

ja.run_requested_date as last_run_date,

CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS run_duration,

ja.next_scheduled_run_date,

CONVERT(VARCHAR(500),jh.message) AS step_description

FROM

(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)

join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id

WHERE ja.session_id=(SELECT MAX(session_id) from msdb.dbo.sysjobactivity) ORDER BY job_name,job_status

END

ELSE

BEGIN

--This is for SQL2k servers

SET NOCOUNT ON

DECLARE @SQL VARCHAR(5000)

--Getting information from sp_help_job to a temp table

SET @SQL='SELECT job_id,name AS job_name,CASE enabled WHEN 1 THEN ''Enabled'' ELSE ''Disabled'' END AS job_status,

CASE last_run_outcome WHEN 0 THEN ''Error Failed''

WHEN 1 THEN ''Succeeded''

WHEN 2 THEN ''Retry''

WHEN 3 THEN ''Cancelled''

WHEN 4 THEN ''In Progress'' ELSE

''Status Unknown'' END AS last_run_status,

CASE RTRIM(last_run_date) WHEN 0 THEN 19000101 ELSE last_run_date END last_run_date,

CASE RTRIM(last_run_time) WHEN 0 THEN 235959 ELSE last_run_time END last_run_time,

CASE RTRIM(next_run_date) WHEN 0 THEN 19000101 ELSE next_run_date END next_run_date,

CASE RTRIM(next_run_time) WHEN 0 THEN 235959 ELSE next_run_time END next_run_time,

last_run_date AS lrd, last_run_time AS lrt

INTO ##jobdetails

FROM OPENROWSET(''sqloledb'', ''server=(local);trusted_connection=yes'', ''set fmtonly off exec msdb.dbo.sp_help_job'')'

exec (@SQL)

--Merging run date & time format, adding run duration and adding step description

select Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName,jd.job_name,jd.job_status,jd.last_run_status,

CONVERT(DATETIME,RTRIM(jd.last_run_date)) +(jd.last_run_time * 9 + jd.last_run_time % 10000 * 6 + jd.last_run_time % 100 * 10) / 216e4 AS last_run_date,

CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS run_duration,

CONVERT(DATETIME,RTRIM(jd.next_run_date)) +(jd.next_run_time * 9 + jd.next_run_time % 10000 * 6 + jd.next_run_time % 100 * 10) / 216e4 AS next_scheduled_run_date,

CONVERT(VARCHAR(500),jh.message) AS step_description

from (##jobdetails jd LEFT JOIN msdb.dbo.sysjobhistory jh ON jd.job_id=jh.job_id AND jd.lrd=jh.run_date AND jd.lrt=jh.run_time) where step_id=0 or step_id is null

order by jd.job_name,jd.job_status

--dropping the temp table

drop table ###jobdetails

END

SQL Server – Setup SQL Profiler to Capture Costly Queries / Stored Procedures

By Enrique 09/03/2017 30/04/2020 SQL SERVER, Performance Optimization

Click Tools > SQL Server Profiler

Connect to the instance

On this page, perform the following:
- Indicate Trace name
- Choose “Tuning” template
- Save to file
- Set 1000 maximum file size
- Set trace stop time (optional. You may stop the trace anytime)

Go to Events Selection tab

$C:\Users\Ricky\AppData\Local\Temp\SNAGHTML4fc4102.PNG$

Tick Show all columns and choose the following columns

Click Column Filters

Filter DatabaseName and Duration (>= 500ms)

Click Run

SQL Server – Get CPU Usage History for last 256 minutes

By Enrique 15/11/2016 30/04/2020 SQL SERVER, Troubleshooting

The other day, I was asked by our Application Team Lead to check why the CPU usage is very high in the Production DB server. It remained consistently high throughout the day. He asked whether it is coming from SQL Server or other processes.

Below is a useful script to review the CPU usage history for last 256 minutes (This will work on SQL Server 2008 and up)

DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info WITH (NOLOCK));

SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization],

SystemIdle AS [System Idle Process],

100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],

DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]

FROM (

SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')

AS [SystemIdle],

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',

'int')

AS [SQLProcessUtilization], [timestamp]

FROM (

SELECT [timestamp], CONVERT(xml, record) AS [record]

FROM sys.dm_os_ring_buffers WITH (NOLOCK)

WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'

AND record LIKE N'%<SystemHealth>%') AS x

) AS y

ORDER BY record_id DESC OPTION (RECOMPILE);

Now, if the value in “Other Process CPU Utilization (%)” column is higher than “SQL Server Process CPU”, then you may ask your sys admins to investigate the cause. But in our case, it was indicated clearly that the cause of high CPU usage was coming from SQL Server Process. Below is the output of the script.

After seeing the results, I investigated from the SQL queries perspective and found out that there are few expensive queries that are causing high CPU usage. How did I find out? That would be the subject of another blog.

SQL Server – Error: 5042 – The file ‘MyFileName’ cannot be removed because it is not empty.

By Enrique 29/10/2016 30/04/2020 SQL SERVER, Troubleshooting

I was trying to delete a data file but I encountered the error below.

Error: 5042 - The file 'MyFileName' cannot be removed because it is not empty.

I executed the script below to empty the file.

DBCC SHRINKFILE(First_Data_File,EMPTYFILE )

After the file has been emptied, I was able to remove the data file.

ALTER DATABASE DBNAME REMOVE FILE First_File;

EMPTYFILE Migrates all data from the specified file to other files in the same filegroup. In other words, EmptyFile will migrate the data from the specified file to other files in the same filegroup. Emptyfile assures you that no new data will be added to the file.The file can be removed by using the ALTER DATABASE statement.

SQL Server – Agent Job Fails with Error: Unable to connect to SQL Server ‘(local)’. The step failed

By Enrique 06/09/2016 30/04/2020 SQL SERVER, Troubleshooting

I was checking one of our client’s DB server for the first time. I noticed a couple of their SQL Agent Jobs have been failing due to the error “Unable to connect to SQL Server ‘(local)’”

The reason for this error is because the Database (drop down box) to run the step from is empty.

The solution is to choose the relevant database to run the job step against.

Facebook

Google

Twitter

Tag: Query Tuning

SQL Server – How To Configure Peer-to-Peer Replication in SQL Server 2016

SQL Server – Get Index Creation Date

SQL Server – Get VLF Counts for All Databases in an Instance

SQL Server – Check Latest Transaction Log Backup

SQL Server – Check SQL Server Error Logs via T-SQL

SQL Server – TSQL Script to Check Job Run Status

SQL Server – Setup SQL Profiler to Capture Costly Queries / Stored Procedures

SQL Server – Get CPU Usage History for last 256 minutes

SQL Server – Error: 5042 – The file ‘MyFileName’ cannot be removed because it is not empty.

SQL Server – Agent Job Fails with Error: Unable to connect to SQL Server ‘(local)’. The step failed

Recent Posts

Archives

Categories

Recent Posts

Archives

Categories

Tag Cloud