Typically transactional replication was used for load balancing and read only copy of database for reporting purposes.

– It is a failover solution
– any subscriber becomes a publisher
– data movement is bi-directional
– the status is known that replication is done so that it doesnt repeat again and again.
– flexible as both server can be both read or read-write simultaneously

Mostly DB Servers are read-intensive, so have more read-only nodes so overall I/O thoroughput can be increased.

Above also works as fail-over solution because if either of them fail the application can be easily pointed to the node acting as main DB.

It is used to collect more information about database like its properties.
Like it can be used to check the recovery model, online/offline status, compatibility level, auto shrink, published/subscribed, version etc.,

SELECT name,
DATABASEPROPERTYEX(name, ‘Recovery’),
DATABASEPROPERTYEX(name, ‘Status’)
FROM master.dbo.sysdatabases

Most SQL installations have default collation. i.e case insensitive or SQL_Latin1_General_CP1_CI_AS in SQL Server glossary.

It can be changed at server, DB or column levels.

How to check current collation?
SELECT SERVERPROPERTY (‘Collation’)

Helps in evaluating how applications are running on SQL Server. It helps to track CPU, memory and RAM.

In 2000, it was done by using the command DBCC SQLPERF (‘WAITSTATS’)

In 2005, it is replaced with DMV known as sys.dm_os_wait_stats.
It gives information about the columns wait_type, waiting_time etc.,

Different waittypes that are captured are:

1. Latches/Locks
2. Backups
3. Tlogs
4. Checkpoints
5. Lazy writer

It helps in troubleshooting query performance issues .