Posts Tagged ‘AlwaysOn’

Preparing for 70-462 Exam : Administering Microsoft SQL Server 2012 Databases | Certification

May 1, 2015 10 comments

I’m from a Database development background and working mostly with T-SQL code only. I passed Exam 70-461 back in Nov-2012, and now I’m planing to give 70-462 Exam i.e. Administering Microsoft SQL Server 2012 Databases.

This exam is intended for Database Administrators who perform:
– Installation
– Maintenance
– Configuration tasks
– and other responsibilities like:
– – setting up Database systems
– – making sure those systems operate efficiently
– – regularly storing, backing up, and securing data from unauthorized access

Thus being a Database Developer (primarily) this exam is going to be the most toughest and trickiest exam in this series as I don’t have DBA experience and haven’t worked on anything in Database Administration yet.

For preparation Iā€™m referring to Training Kit (Exam 70-462) Administering Microsoft SQL Server 2012 Databases book.

ā€“> The Exam is divided into 6 modules:
1. Install and Configure
2. Maintain Instances and Databases
3. Optimize and Troubleshoot
4. Manage Data
5. Implement Security
6. Implement High Availability

You need to brush up on following Skills in the these Modules:
-:Check links with the below items for more information:-

1. Install and Configure | 19%
– Plan installation
– Install SQL Server and related services
– Implement a migration strategy
– Configure additional SQL Server components
– Manage SQL Server Agent
– PREP Links: Understanding surface area configuration | Hardware and software requirements for installing SQL Server 2012 | Quick-start installation of SQL Server 2012

2. Maintain Instances and Databases | 17%
– Manage and configure databases
– Configure SQL Server instances
– Implement a SQL Server clustered instance
– Manage SQL Server instances
– PREP Links: ALTER DATABASE file and filegroup options (Transact-SQL) | Contained databases | Data compression

3. Optimize and Troubleshoot | 14%
– Identify and resolve concurrency problems
– Collect and analyze troubleshooting data
– Audit SQL Server instances
– PREP Links: blocked process threshold server configuration option | Configure login auditing (SSMS) | Data collection

4. Manage Data | 19%
– Configure and maintain a back-up strategy
– Restore databases
– Implement and maintain indexes
– Import and export data
– PREP Links: Back up and restore of SQL Server databases | File restores (full recovery mode) | DBCC INDEXDEFRAG (Transact-SQL)

5. Implement Security | 18%
– Manage logins and server roles
– Manage database permissions
– Manage users and database roles
– Troubleshoot security
– PREP Links: Server-level roles | Permissions (database engine) | Database-level roles

6. Implement High Availability | 12%
– Implement AlwaysOn
– Implement Database Mirroring
– HADR (High Availability and Disaster Recovery)
– PREP Links: AlwaysOn Availability Groups (SQL Server) | Microsoft SQL Server AlwaysOn solutions guide for HADR | AlwaysOn architecture guide: Building a HADR solution by using AlwaysOn Availability Groups

-ā€“> You can visit following Study materials to prepare for this Exam:

Microsoft Link for this Certification:

Book on Training Kit (Exam 70-462) Administering Microsoft SQL Server 2012 Databases

Will try to update this blog post or will put up a new post with my learning while preparing for this exam.

All The Best!!!


Query to check AlwaysON Replica Latency for the LSNs (Log Sequence Numbers) by using DMVs

April 11, 2014 Leave a comment

	 ag.NAME AS 'AG Name'
	,ar.replica_server_name AS 'AG Replica Server'
	,DB_NAME(drs.database_id) AS 'Database Name'
		WHEN ars.is_local = 1
	END AS 'Is AG Replica Local'
		WHEN ars.role_desc IS NULL
		ELSE ars.role_desc
	END AS 'AG Replica Role'
	,ar.availability_mode_desc AS 'Sync Mode'
	,drs.synchronization_state_desc AS SyncState
	,agl.dns_name AS 'Listener Name'
	,datediff(s, last_hardened_time, getdate()) / 60 AS 'Seconds Behind Primary'

FROM sys.availability_groups AS ag 

INNER JOIN sys.availability_replicas AS ar 
ON ag.group_id = ar.group_id

INNER JOIN sys.dm_hadr_availability_replica_states AS ars 
ON ar.replica_id = ars.replica_id

INNER JOIN sys.dm_hadr_database_replica_states drs 
ON ag.group_id = drs.group_id
AND drs.replica_id = ars.replica_id

INNER JOIN sys.availability_group_listeners AS agl
ON agl.group_id = ars.group_id

ORDER BY datediff(s, last_hardened_time, getdate()) DESC;