DBCC Config and DBCC DBINFO

DBCC Config

When were the sp_configure options last changed?

Database Boot Page

Reading boot page (DBCC DBINFO)

https://mattsql.wordpress.com/2012/09/11/quick-tip-querying-database-metadata-with-dbcc-dbinfo/

Advertisements

Transactional Replication Troubleshooting basic commands

sp_replshowcmds 

Displays transactions pending in transaction log at publication database

sp_repldone 

  • Allows for skipping commands at Log Reader Agent
  • Large update you may not want propagated (e.g. millions of rows you don’t want replicated)
  • Situations where re-initialize would be too time consuming or costly
  • This procedure can be used in emergency situations to allow truncation of the transaction log when transactions pending replication are present.
  • Use as an exception, not as rule

sp_replflush
Flushes ‘article cache’ on transaction log and allows the Log Reader to resume work (only one log reading process can be used for a database at a time)

Extended Events: to track waits by session

Sample taken from Joe Sack’s Pluralsight course on Transactional Replication Fundamentals

CREATE EVENT SESSION ExtendedEventsSessionName
ON SERVER
ADD EVENT sqlos.wait_info(
ACTION (sqlserver.session_id)
WHERE ([package0].[equal_uint64]([sqlserver].[session_id],(61—spid)) OR
[package0].[equal_uint64]([sqlserver].[session_id],(53—spid))))
ADD TARGET package0.asynchronous_file_target
(SET FILENAME = N’C:\TEMP\xet.xel’, — CHECK that these are cleared
METADATAFILE = N’C:\TEMP\xet.xem’);

ALTER EVENT SESSION ExtendedEventsSessionName
ON SERVER STATE = START;
GO

—-Let the workload run

ALTER EVENT SESSION ExtendedEventsSessionName
ON SERVER STATE = STOP;
GO

— Raw data into intermediate table
— (Make sure you’ve cleared out previous target files!)
SELECT CAST(event_data as XML) event_data
INTO #xet_Stage_1
FROM sys.fn_xe_file_target_read_file
(‘C:\TEMP\xet*.xel’,
‘C:\TEMP\xet*.xem’,
NULL, NULL);

— Aggregated data into intermediate table
SELECT
event_data.value
(‘(/event/action[@name=”session_id”]/value)[1]’, ‘smallint’) as session_id,
event_data.value
(‘(/event/data[@name=”wait_type”]/text)[1]’, ‘varchar(100)’) as wait_type,
event_data.value
(‘(/event/data[@name=”duration”]/value)[1]’, ‘bigint’) as duration,
event_data.value
(‘(/event/data[@name=”signal_duration”]/value)[1]’, ‘bigint’) as signal_duration,
event_data.value
(‘(/event/data[@name=”completed_count”]/value)[1]’, ‘bigint’) as completed_count
INTO #xet_Stage_2
FROM #xet_Stage_1;

— Final result set
SELECT session_id,
wait_type,
SUM(duration) total_duration,
SUM(signal_duration) total_signal_duration,
SUM(completed_count) total_wait_count
FROM #xet_Stage_2
GROUP BY session_id,
wait_type
ORDER BY session_id,
SUM(duration) DESC;
GO

SQL Server 2016: Distributed availability groups Use cases

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-distributed-availability-groups

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/distributed-availability-groups

Disaster recovery and multi-site scenarios

A traditional availability group requires that all servers be part of the same WSFC cluster, which can make spanning multiple data centers challenging.

Distributed availability groups offer a more flexible deployment scenario for availability groups that span multiple data centers. You can even use distributed availability groups where features such as log shipping were used in the past. However, unlike traditional availability groups, distributed availability groups cannot have delayed application of transactions. This means that availability groups or distributed availability groups cannot help in the event of human error in which data is incorrectly updated or deleted.

Distributed availability groups are loosely coupled, which in this case means that they don’t require a single WSFC cluster and they’re maintained by SQL Server. Because the WSFC clusters are maintained individually and the synchronization is primarily asynchronous between the two availability groups, it’s easier to configure disaster recovery at another site. The primary replicas in each availability group synchronize their own secondary replicas.

Migrate by using a distributed availability group

Because distributed availability groups support two completely different availability group configurations, they enable not only easier disaster-recovery and multi-site scenarios, but also migration scenarios. Whether you are migrating to new hardware or virtual machines (on-premises or IaaS in the public cloud), configuring a distributed availability group allows a migration to occur where, in the past, you might have used backup, copy, and restore, or log shipping.

The ability to migrate is especially useful in scenarios where you’re changing or upgrading the underlying OS while you keep the same SQL Server version. Although Windows Server 2016 does allow a rolling upgrade from Windows Server 2012 R2 on the same hardware, most users choose to deploy new hardware or virtual machines.

To complete the migration to the new configuration, at the end of the process, stop all data traffic to the original availability group, and change the distributed availability group to synchronous data movement. This action ensures that the primary replica of the second availability group is fully synchronized, so there would be no data loss. After you’ve verified the synchronization, fail over the distributed availability group to the secondary availability group.

Scale out readable replicas with distributed availability groups

A single distributed availability group can have up to 16 secondary replicas, as needed. So it can have up 18 copies for reading, including the two primary replicas of the different availability groups. This approach means that more than one site can have near-real-time access for reporting to various applications.