70462 My Virtual server Spec

My computer has the below spec
Intel(R) Core (TM) i7-3612QM CPU @2.10GHZ (8 CPUs)
Memory : 8192MB RAM
Harddisk : 1 TB
OS : Windows 8 Pro 64-bit

My Virtual server setting
Domain Controller (DC) - 1 Virtual Processor, RAM 512, Virtual harddisk - size 127gb (It will expand when disk needed); I DC file size (3 gb)
SQL-A - 1 Virtual Processor, RAM 1 GB, Virtual Harddisk - auto expand as well (11 GB when checking)
SQL-B - 1 Virtual Processor, RAM 1 GB, Virtual Harddisk - auto expand as well (11 GB when checking)
SQL-C - 1 Virtual Processor, RAM 1 GB, Virtual Harddisk - auto expand as well (11 GB when checking)
SQL-D - 1 Virtual Processor, RAM 1 GB, Virtual Harddisk - auto expand as well (11 GB when checking)

Most of the time I only turn On DC, SQL-A, SQL-B and it more then enough for Testing.

Quorum - Node and File Share Majority; Shutdown one node

Cluster service, Change Quorum to Node and File Share Majority; Shutdown one node

SQL-B is Primary Replicas


Shutdown SQL-B server, simulate Primary replicas SQL-B fail and see if the database will auto-failover to SQL-A

Cluster service - SQL-ALWAYSONCL.Contso.com still available with warning
SQL-B Node is down




Failover database Happen and SQL-A become Primary Replicas and Availability Database and Availability Group Listeners still can be access



SQL Availability Dashboard
- SQL-A Replicas is online
- SQL-B Replicas is offline


SQL Error log

Availability replica
'SECONDARY_NORMAL' to 'RESOLVING_NORMAL'; to 'PRIMARY_PENDING'; to 'PRIMARY_NORMAL'
Availability database
'SECONDARY' to 'RESOLVING'; Changed to 'PRIMARY'




Start SQL-B server
SQL-B node is back online
Current Host server still SQL-A and failover didn't happen



SQL Server


SQL Availability Dashboard


SQL Error Log (SQL-A)

Change Quorum to Node and File Share Majority

70-462 Cluster service, Change Quorum to Node and File Share Majority
Can sustain failure of 1 node(s) if the file share witness remains available.
Can sustain failure of 0 Node(s) if the file share witness becomes unavailable

Compare to Default Quorum - Node Majority (not recommended for your current number of nodes
can sustain failures of 0 node(s)

2 nodes with Default Quorum (Node Majority) setup; Shutdown Secondary node

Cluster service, 2 nodes with Default Quorum (Node Majority) setup;
Shutdown server node with Secondary Replicas host (SQL-A); What will happen to SQLServer?



Shutdown Server node SQL-A where Secondary Replica sit.

After SQL-A server node shutdown,
- SQL-B SQLServer availability database testing in "Recovery Pending"
- AlwaysOn High Availability groups - ALWAYSONAG in "Resolving"
- Availability Replicas, SQL-B in "Resolving" mode
- Availability Group Listeners is missing


Availability Dashboard


SQL Error log


why this happen ? We expect the Sqlserver (SQL-B) for Primary should not affected if Secondary server down (SQL-A).

System Error log (SQL-B)


Remember in the cluster quorum configuration, we use default quorum with node majority whereby cluster service will sustain with 0 node down?
When on node down, cluster service can't sustain and SQL Availability database is down as well
2 nodes with Default Quorum (Node Majority) setup; Cluster service will sustain with 0 node down

 Restart SQL-A server
SQLServer up

SQL Error Log

Shutdown Primary sqlserver service; 2 nodes with Default Quorum (Node Majority) setup;

SQL Cluster : 2 nodes with Default Quorum (Node Majority) setup;
Testing with Shutdown Primary sqlserver service and verify if the database failover to secondary Availability Replicas and secondary availability promote to Primary Replicas

Availability Group Properties
Synchronous commit , Failover mode = Automatics
SQL-A primary, SQL-B secondary




Shutdown SQL-A, SQLService to simulate Primary SQLServer (SQL-A) fail and see if the database will auto-failover to SQL-B

SQLServer SQL-B become Primary Replicas; Auto-Failover is happen when SQL-A fail.
From Replicas, we can see SQL-A become secondary and down



SQL AG (alwaysonag)Dashboard


SQL-B error log



SQL-B:
Availability Replica status : SECONDARY_NORMAL -- RESOLVING_NORMAL  -- PRIMARY_PENDING -- PRIMARY_NORMAL

Availability Group database status : SECONDARY - RESOLVING --PRIMARY

Bring back SQLService - SQL-A
SQL-B still act as Primary Availability Replicas
SQL-A is back online and show as Secondary Availability Replicas





SQL-B : SQL Error log

Shutdown secondary sqlserver service; 2 nodes with Default Quorum (Node Majority) setup;

Cluster service, 2 nodes (SQL-A,SQL-B) with default Quorum (Node Majority) setup;
SQL Server AG group (alwaysonag) with 2 node (SQL-A, SQL-B)
Test shutdown secondary sqlserver service (SQL-B), will it affect primary replicas (SQL-A)



Shutdown SQL-B, SQLService to simulate SQL-B SQLServer fail

From SQL-A, sql-error log:
- A connection timeout has occurred on a previously established connection to availability replica 'SQL-B' with 'DAF385F5-FE27-4A48-A5DC-7E21214498C7].
Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role
- AlwaysOn Availability Groups connection with secondary database terminated for primary database 'testing' on the availability replica with Replica ID : {daf385f5-fe27-4a48-a5dc-7e21214498c7}

SQL-A database still ONLINE, from
AlwaysOn High Availability  --Availability Group -- AlwaysONAG --  Availability Replicas
we can see SQL-B replicas is down



SQL AG (alwaysonag) Dashboard



From Availability group state -- Critical --- Critical (1), Warning (3)


Some synchronous replicas are not synchronized
In this availability group, at least one synchronous replica is not currently synchronized. The replica synchronization state could be either SYNCHONIZING or NOT SYNCHRONIZING

Some availability replicas are not synchronizing data
In this availability group, at least one secondary replica has a NOT SYNCHRONIZING synchronization state and is not receiving data from the primary replica

Some availability replicas are disconnected
In this availability group, at least one secondary replica is not connected to the primary replica. The connected state is DISCONNECTED

Availability group is not ready for automatic failover
The availability group is not ready for automatic failover. The primary replica and a secondary replica are configured for automatic failover. however, the secondary replica is not ready for an automatic failover. Possibly the secondary replica is unavailable, or its data synchronization state is currently not in the SYNCHRONIZED synchronization state.

From Availability replica -- Critical (1), Warnings (1)



Start SQLService --SQL-B
From SQL-A, sql-error log:
A connection for availability group 'ALWAYSONAG' from availability replica 'SQL-A' with id [C8B53A5D-75CE-40CC-BB27-50CFBA48CB32] to 'SQL-B' with id [DAF385F5-FE27-4A48-A5DC-7E21214498C7] has been successfully established. This is an informational message only. No user action is required.

AlwaysOn Availability Groups connection with secondary database established for primary database 'testag' on the availability replica with Replica ID :{DAF385F5-FE27-4A48-A5DC-7E21214498C7}. This is an informational message only. No user action is required

AlwaysON Availability Groups connection with secondary database established for primary database 'testag' on the availability replica with Replica ID :{DAF385F5-FE27-4A48-A5DC-7E21214498C7}. This is an informational message only. No user action is required

SQL-B back online in availability Replicas


SQL AG (alwaysonag) dashboard

2 nodes with Default Quorum (Node Majority) setup; Cluster service will sustain with 0 node down

Test cluster service, 2 node with Default Quorum (Node Majority);
What will happen if one of the node down;

Create a cluster – clustertest.contso.com with 2 node : SQL-C and SQL-D

Default Quorum : Node Majority

More information about quorum
WSFC Quorum Modes and Voting Configuration (SQL Server)http://msdn.microsoft.com/en-us/library/hh270280.aspx#QuorumModes


With Default Quorum (Node Majority) and 2 nodes cluster


Quorum Configuration warning
Node Majority - Warning: Failure of a  node will cause the cluster to fail. Check the status of the nodes


Shutdown SQL-D, simulate SQL-D server fail
System event log at SQL-C


SQL Cluster is missing from Failover Cluster Manager

Bring SQL-D back online
Cluster service - Clustertest.Contso.com back online.



Cluster service will offline, if other node down.
Custer service will sustain with 0 node down.

AGL testing - Cluster and Quorum

After Create a cluster, in Quorum Configuration, it give warning
Node Majority - Warning: Failure of node will cause the cluster to fail. Check the status of the nodes.

Check Default Quorum : Node Majority
With the Default Quorum(Node Majority) with 2 node cluster,  Cluster can sustain failure of 0 node(s)

SET XACT_ABORT ON - Testing

My developer ask me, inside one store procedure, we have 3 statements, if second statement fails, will first statement rollback? Will third statement continuous execute? What if we use begin tran.. commit?

Example
Create procedure tesme
As
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2); -- Foreign key error.
INSERT INTO t2 VALUES (3);

If second insert fail, will first insert data rollback? Will sql contineous execute sql statement ?

use tempdb
go
CREATE TABLE t1
    (a INT NOT NULL PRIMARY KEY);
CREATE TABLE t2
    (a INT NOT NULL REFERENCES t1(a));
GO
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (3);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (6);
GO
create procedure testme
as
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2); -- Foreign key error.
INSERT INTO t2 VALUES (3);
COMMIT TRANSACTION;
GO


exec testme
go
Result :

(1 row(s) affected)
Msg 547, Level 16, State 0, Procedure testme, Line 6
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__t2__a__32C16125". The conflict occurred in database "tempdb", table "dbo.t1", column 'a'.
The statement has been terminated.
(1 row(s) affected)

select * from t2
a
1
3
By Default, first insert didn't rollback and statement 3 been processes, even second insert fail

Check this :
SET XACT_ABORT { ON | OFF }
OFF is the default setting.
http://msdn.microsoft.com/en-us/library/ms188792.aspx

Explanation from mdsn
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting.

Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.
From the document, if we didn't define xact_abort in store procedure, the value will for xact_abort = off

By default xact_abort is off, how to check if xact_abort is on or off in current session
DECLARE @options INT
SELECT @options = @@OPTIONS
PRINT @options
IF ( (16384 &@options) = 16384 ) PRINT 'XACT_ABORT'

set xact_abort on
go
DECLARE @options INT
SELECT @options = @@OPTIONS
PRINT @options
IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT'

--reset to default
set xact_abort off


Enabled set xact_abort on inside store procedure
--clear data
delete from t2
go
alter procedure testme
as
set xact_abort on
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2); -- Foreign key error.
INSERT INTO t2 VALUES (3);
COMMIT TRANSACTION;
GO

exec testme
go
select * from t2 -- return no result

in this example, We enable set xact_abort on in the store procedure
Second statement fails with "The INSERT statement conflicted with the FOREIGN KEY constraint"

when set xact_abort on define in SQL store procedure, if second sql statement fail,
first sql statement will be rollback and third sql statement won't be run(set xact_abort on)


Two transaction in one store procedure
If Second transaction fails, will first transaction rollback
delete from t2
go
alter procedure testme
as
set xact_abort on
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (3);
COMMIT TRANSACTION;
BEGIN TRANSACTION
INSERT INTO t2 VALUES (6); 
INSERT INTO t2 VALUES (8);--this will fail
COMMIT TRANSACTION;
GO

exec testme
go
error :
Msg 547, Level 16, State 0, Procedure testme, Line 9
Msg 547, Level 16, State 0, Procedure testme, Line 10
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__t2__a__4D404D37". The conflict occurred in database "tempdb", table "dbo.t1", column 'a'.

select * from t2
first batch of query been execute and won't rollback
a
1
3
value 6 didn't add into table
Two transaction in one store procedure, If Second transaction fails,only second transaction will be rollback(set xact_abort on)

If First transaction in store procedure fail, will second transaction contienous?
delete from t2
go
alter procedure testme
as
set xact_abort on
BEGIN TRANSACTION
INSERT INTO t2 VALUES (6); 
INSERT INTO t2 VALUES (8);--this will fail
COMMIT TRANSACTION;
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (3);
COMMIT TRANSACTION;
GO
exec testme
go
select * from t2 -- no result return
if first transaction fails, second transaction won't be continuous(set xact_abort on)


If second calling store procedure, will sql rollback dml from first calling store procedure?
delete from t2
go
alter procedure testme
as
set xact_abort on
BEGIN TRANSACTION;
   exec testmecalling
   exec testmecalling2  -- this will fail
COMMIT TRANSACTION;
GO
create procedure testmecalling
as
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (3);
create procedure testmecalling2
as
INSERT INTO t2 VALUES (6); 
INSERT INTO t2 VALUES (8);
exec testme
go
select * from t2 --no result return


If calling store procedure inside tran..commit, one fail will rollback other (set xact_abort on)

Prepare SQL 2012 Exam 70-462

Search for material on 70-462 and found the below:

Exam 462: Administering Microsoft SQL Server 2012 Databases
Video on how to prepare the exam, good sharing - 1 hour

Video


Skills measured
Install and configure (19%)

• Plan installation
  
  • Evaluate installation requirements; design the installation of SQL Server and its components (drives, service accounts, etc.); plan scale-up vs. scale-out basics; plan for capacity, including if/when to shrink, grow, autogrow, and monitor growth; manage the technologies that influence SQL architecture (for example, service broker, full text, scale out, etc.); design the storage for new databases (drives, filegroups, partitioning); design database infrastructure; configure a SQL Server standby database for reporting purposes; Windows-level security and service level security; Core mode installation; benchmark a server before using it in a production environment (SQLIO, Tests on SQL Instance); choose the right hardware
• Install SQL Server and related services
  
  • Test connectivity; enable and disable features; install SQL Server database engine and SSIS (not SSRS and SSAS); configure an OS disk
• Implement a migration strategy
  
  • Restore vs detach/attach; migrate security; migrate from a previous version; migrate to new hardware; migrate systems and data from other sources
• Configure additional SQL Server components
  
  • Set up and configure all SQL Server components (Engine, AS, RS and SharePoint integration) in a complex and highly secure environment; configure full-text indexing; SSIS security; filestream; filetable
• Manage SQL Server Agent
  
  • Create, maintain, and monitor jobs; administer jobs and alerts; automate (setup, maintenance, monitoring) across multiple databases and multiple instances; send to "Manage SQL Server Agent jobs"

Preparation resources

• Understanding Surface Area Configuration
• Hardware and Software Requirements for Installing SQL Server 2012
• Quick-Start Installation of SQL Server 2012
• File Locations for Default and Named Instances of SQL Server
• Storage Top 10 Best Practices
• Features Supported by the Editions of SQL Server 2012
• FILESTREAM (SQL Server)
• SQL Server Browser Service (Database Engine and SSAS)
• Automated Administration Across an Enterprise

Maintain instances and databases (17%)

• Manage and configure databases
  
  • Design multiple file groups; database configuration and standardization: autoclose, autoshrink, recovery models; manage file space, including adding new filegroups and moving objects from one filegroup to another; implement and configure contained databases; data compression; configure TDE; partitioning; manage log file growth; DBCC
• Configure SQL Server instances
  
  • Configure and standardize a database: autoclose, autoshrink, recovery models; install default and named instances; configure SQL to use only certain CPUs (affinity masks, etc.); configure server level settings; configure many databases/instance, many instances/server, virtualization; configure clustered instances including MSDTC; memory allocation; database mail; configure SQL Server engine: memory, filffactor, sp_configure, default options
• Implement a SQL Server clustered instance
  
  • Install a cluster; manage multiple instances on a cluster; set up subnet clustering; recover from a failed cluster node
• Manage SQL Server instances
  
  • Install an instance; manage interaction of instances; SQL patch management; install additional instances; manage resource utilization by using Resource Governor; cycle error logs

Preparation resources

• ALTER DATABASE File and Filegroup Options (Transact-SQL)
• Contained Databases
• Data Compression
• Database Engine Instances
• Database Mail
• Server Configuration Options (SQL Server)
• Create a New SQL Server Failover Cluster (Setup)
• Resource Governor

Optimize and troubleshoot (14%)

• Identify and resolve concurrency problems
  
  • Examine deadlocking issues using the SQL server logs using trace flags; design reporting database infrastructure (replicated databases); monitor via DMV or other MS product; diagnose blocking, live locking and deadlocking; diagnose waits; performance detection with built in DMVs; know what affects performance; locate and if necessary kill processes that are blocking or claiming all resources
• Collect and analyze troubleshooting data
  
  • Monitor using Profiler; collect performance data by using System Monitor; collect trace data by using SQL Server Profiler; identify transactional replication problems; identify and troubleshoot data access problems; gather performance metrics; identify potential problems before they cause service interruptions; identify performance problems;, use XEvents and DMVs; create alerts on critical server condition; monitor data and server access by creating audit and other controls; identify IO vs. memory vs. CPU bottlenecks; use the Data Collector tool
• Audit SQL Server instances
  
  • Implement a security strategy for auditing and controlling the instance; configure an audit; configure server audits; track who modified an object; monitor elevated privileges as well as unsolicited attempts to connect; policy-based management

Preparation resources

• blocked process threshold Server Configuration Option
• Configure Login Auditing (SQL Server Management Studio)
• Data Collection
• Administer Servers by Using Policy-Based Management

Manage data (19%)

• Configure and maintain a back-up strategy
  
  • Manage different backup models, including point-in-time recovery; protect customer data even if backup media is lost; perform backup/restore based on proper strategies including backup redundancy; recover from a corrupted drive; manage a multi-TB database; implement and test a database implementation and a backup strategy (multiple files for user database and tempdb, spreading database files, backup/restore); back up a SQL Server environment; back up system databases
• Restore databases
  
  • Restore a database secured with TDE; recover data from a damaged DB (several errors in DBCC checkdb); restore to a point in time; file group restore; page level restore
• Implement and maintain indexes
  
  • Inspect physical characteristics of indexes and perform index maintenance; identify fragmented indexes; identify unused indexes; implement indexes; defrag/rebuild indexes; set up a maintenance strategy for indexes and statistics; optimize indexes (full, filter index); statistics (full, filter) force or fix queue; when to rebuild vs. reorg and index; full text indexes; column store indexes
• Import and export data
  
  • Transfer data; bulk copy; bulk insert

Preparation resources

• Back Up and Restore of SQL Server Databases
• File Restores (Full Recovery Mode)
• DBCC INDEXDEFRAG (Transact-SQL)
• UPDATE STATISTICS (Transact-SQL)
• Bulk Import and Export of Data (SQL Server)

Implement security (18%)

• Manage logins and server roles
  
  • Configure server security; secure the SQL Server using Windows Account / SQL Server accounts, server roles; create log in accounts; manage access to the server, SQL Server instance, and databases; create and maintain user-defined server roles; manage certificate logins
• Manage database permissions
  
  • Configure database security; database level, permissions; protect objects from being modified
• Manage users and database roles
  
  • Create access to server / database with least privilege; manage security roles for users and administrators; create database user accounts; contained logins
• Troubleshoot security
  
  • Manage certificates and keys; endpoints

Preparation resources

• Server-Level Roles
• Permissions (Database Engine)
• Database-Level Roles
• SQL Server Certificates and Asymmetric Keys

Implement high availability (12%)

Details refer to here

70-462 Setup SQL 2012 Availability Group

The below setup for Testing on AG setup :
Server configuration
DC 10.10.10.10
SQL-A 10.10.10.20
SQL-B 10.10.10.30
alwaysonAGL - 10.10.10.70
SQL-AlwaysonCL 10.10.10.90

SQLServer service account =SQL cluster

from Failover Cluster->create a cluster

From DC server, sql-alwaysonCL is automatically appear

Enable AG in SQL Server at both node (SQL-A, SQL-B) and restart

Create a database at sql-A
create database Testdatabase1

Criteria for database participant in AG
- auto-close = off

ALTER DATABASE testdatabase1 SET AUTO_CLOSE OFF WITH NO_WAIT

select is_auto_close_on, * from sys.databases where name='testdatabase1'

alter database testdatabase1 set recovery full

SELECT name, recovery_model_desc  FROM sys.databases WHERE name = 'testdatabase1



Backup database Testdatabase1

Backup database testdatabase1 to disk='c:\backup\testdatabase1.bak'

Setup AG

AG setup fail!!Are we missing anything ??
Fail to create Windows Server Failover Clustering (WSFC) resource with name 'alwaysonAG' and
type SQL Server Availability Group'. The resource type is not registered in the WSFC
cluster. The WSFC cluster many have been destroyed and created again. To register the
resource type in the WSFC cluster, disable and then enable alwaysOn in the SQL Server Configuration Manager.
Failed to create availability group 'alwaysonAG'. The operation encountered SQL Server error
41105 and has been rolled back. Check the SQL Server error log for more details. When the
cause of the error has been resolved, retry CREATE AVAILABILITY GROUP command. (Microsoft SQL Server, Error: 41105);

How to solve
From DC server, active directory user and computer

Right click on computer,

Restart SQL Server SQL-A and SQL-B and back to sql-a resetup AG

Add AG Listener

From DC, add sql cluster (SQL service account) into alwaysonCL with full permission