SQL 2012 AG Failover and Failover Modes

Read this from Microsoft MSDN, and it make me understand how properties of failover modes affect AG failover

SQL 2012 AG Failover and Failover Modes

SQL AG - Availability Mode

I always ask myself why we need set synchronous /asynchronous mode in both primary and secondary group and what happen if synchronous set and secondary down? what will happen to primary.

Today get this answer:
If Primary server Availability mode = asynchronous
- Primary server will no wait for any secondary harden the log and it will commit immediately.
- The secondary will commit asynchronous no matter what mode you're configure on the AG mode in secondary

If Primary server Availability Mode = synchronous,
- Primary server will wait for transaction write into secondary server with AG mode=synchronous before being written to the local log file
- If secondary with synchronous timeout, Primary replica temporarily shifts into asynchronous-commit mode for that secondary replica. When the secondary replica reconnects with the primary replica, they resume synchronous-commit mode


Reference :
Availability Modes

SQL server slow with wait_type RESOURCE_SEMAPHORE

SQL Server slow without any reason ? lot timeout complain? if wait_type = RESOURCE_SEMAPHORE , can try this

We have issue when users keep complain their query timeout, query can’t complete

We run some query in SSMS, result return very fast; The server with low CPU, no blocking, no long run query and active connection keep accumulate.

We can try this method and check

--watch for waiter_count

-- if waiter_count> 0, mean query need wait for memory. Normally we should expect this value =0

--resource_semaphore_id = 0 , large query

--resource_semaphore_id=1, small query

 

SELECT waiter_count,grantee_count,* FROM sys.dm_exec_query_resource_semaphores

--if waiter_count>0 all the time, check this

--what query have granted_memory_kb
select granted_memory_kb ,grant_TIME,SESSION_ID,resource_semaphore_id from
sys.dm_exec_query_memory_grants
order by 1 desc

Look for session_id with largest granted_memory,

Check what query is running with this session,

Dbcc inputbuffeR(session_id)

Remember capture hostname, loginame and how long the query already runs.

Sp_who2(session_id)

Good Article :
SQL wait_type RESOURCE_SEMAPHORE troubleshooting

Setup Virtual Server connect to internet (Exam 70-462)

Setup Virtual server in Hyper-V connect to Internet (Exam 70-462)

Setup up External Virtual Switch
From Hyper-V manager, click on Virtual Switch Manager

In Create Virtual Switch Manager, that is 3 options (External;Internal;Private)
External
Creates a virtual switch that binds to the physical network adapter so that virtual machines can access a physical network.
Internal
Creates a virtual switch that can be used only by the virtual machines that run on this physical computer, and between the virtual machines and the physical computer. An internal virtual switch does not provide connectivity to a physical network connection.
Private
Creates a virtual switch that can be used only by the virtual machines that run on this physical computer.

I want setup virtual server to use internet to active my window license, in this case, I create external virtual switch.

 

I change the name to "External" and choose my network card.

 

When I press ok, it give me Apply networking changes warning, I press "Yes"

 

I can see the External Virtual switch created

 

From virtual server-> DC ->Settings

 

I select "Add Legacy Network Adapter" and press "Add"

 

I change Virtual switch option from "Not Connected" to "external"; the virtual switch I created just now and press "ok"

 

 

 

After complete setting, I start my virtual server and virtual server can connect to internet

 

 

The above is for Exam 70-462 lab setting environment.

 

Active Window License in Lab Environment (exam 70-462)

Active Window License in Lab Environment (exam 70-462)
After download window 2008 evaluation and install, we need active window online, else the window will be expired

from command prompt, run this
slmgr.vbs -dli

From computer, properties

 

Press Active Windows online now

 

After minutes, It will show activation was successful

 

 

 Check the window activation status, now license is valid for 180 days

 

The above activation for 70-462 lab setting environment.

Virtual Server auto-shutdown

My virtual server auto-shutdown and I don't know why
Google and found this
hyper-v virtual server will sometime auto shutdown unexpected without any error or massage.

Virtual server auto shutdown maybe because Windows 2008 evaluation expired.

I try the instruction in this page to extend the evaluation period
How to extend the Windows Server 2008 evaluation period

slmgr.vbs -dli to check current window evaluation status.
slmgr.vbs –rearm, to reset evaluation period to 60 days

After I reset evaluation and restart virtual server, I rerun slmgr.vbs -dli to check the status again
It ONLY extend 10 days !

SQL January free resource

JAN 2013
Video watched
Performance and Query Optimizations in SQL Server 2012
Published Date: December 03, 2012
Presented By:  Bob Beauchemin

High Availability for SQL Server Replication using Database Mirroring
Discuss on Providing High Availability for SQL Server Transactional Replictaion using Database Mirroring
Source : channel9.msdn.com


Article/Whitepaper readed
When to Break Down Complex Queries
Problematic query scenario/ example and how to solve.
Published Date:21 Oct 2011

70-462 Virtual Server Preparation

70-462 Virtual Server Preparation
In this session : Virtualization Hardware Requirements,

Virtual Server, we need,
8.0 GB of RAM.
80 GB of available hard disk space if you are using differencing virtual hard disks.

I using Hyper-V and follow this instruction to create Parent Configuration and Child server can be created using Parent Configuration.

We can install server OS once and use it to create another 5 servers which required in the lab

Follow all the instruction on the site and it will prepare us to next step of Setup Lab (Domain Controller and other SQL Server)

This is My Virtual Server Configuration

Free ebooks for many different technologies

Free ebooks for many different technologies

http://blogs.msdn.com/b/mssmallbiz/archive/2012/07/27/large-collection-of-free-microsoft-ebooks-for-you-including-sharepoint-visual-studio-windows-phone-windows-8-office-365-office-2010-sql-server-2012-azure-and-more.aspx

I download the below topic for SQL

Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery

Transact-SQL Data Manipulation Language (DML) Reference
SQL Server 2012 Developer Training Kit
Introducing Microsoft SQL Server 2012

September Free online SQL Training video

Found out this Free SQL Training: until September 11th


You can watch the below three courses free, each of which are 4+ hours long, are:


•SQL Server: Performance Tuning Using Wait Statistics (by Pauls Randal)

•SQL Server: Transact-SQL Basic Data Retrieval (by Joe)

•SQL Server: Collecting and Analyzing Trace Data (by Jonathan)



1. To sign up for this, all you have to do is follow SQLskills and Pluralsight on Twitter

Sign up at: http://pluralsight.com/training/TwitterOffer/sqlskills


2. You will get a registration code from your direct message in twitter, sit back and learn. The special-offer sign-up is available until September 11th,

3. You have 30 days from when you sign up to view the three courses.

source: SQLSKills

Replication - find replication error command

Find Replication error command :
from distribution database, Keying publisherDB name and commandId

declare @PublisherDB sysname,
@PublisherDBID int,@SeqNo nchar(22),@CommandID int

-- Set publisher database name and values from Replication Monitor
set @PublisherDB='publisherdatabasename'

--Set @SeqNo = N'0x0000030100001D68000A0000000'
--Set @CommandID=956
Set @SeqNo = N'valuefromreplicationmonitor'
Set @CommandID ='value from replicationmonitor'

select top 1 @PublisherDBID =publisher_database_id from MSdistribution_agents(nolock)
where publisher_db=@PublisherDB
-- Get the command
Exec sp_browsereplcmds
@xact_seqno_start = @SeqNo,
@xact_seqno_end = @SeqNo,
@command_id = @CommandID,
@publisher_database_id=@PublisherDBID;

In result return, look for command column

SQL Reporting service - move reporting database

Resource 
Create the RSExecRole
"Reporting Services uses a predefined database role called RSExecRole to grant report server permissions to the report server database. The RSExecRole role is created automatically with the report server database. As a rule, you should never modify it or assign other users to the role. However, when you move a report server database to a new or different SQL Server Database Engine, must re-create the role in the Master and MSDB system databases. "
  This page show how to manual create RSExecRole database role in MSDB and Masater database. Assign all permission in the database role - RSExecRole.

I create RSExecRole manually in my Mirroring database before I failover.

Moving the Report Server Databases to Another Computer
- Detaching and Attaching the Report Server Databases

- Backing Up and Restoring the Report Server Databases

Initialize a Report Server - Mircosoft
Understand how Reporting Service creates and stores a symmetric key used for encryption.


Reporting Service - Encryption Key - Mircosoft

How to: Migrate a Reporting Services Installation
Step to setp to migrate a reporting service

BLOG..
Progress on Reporting Services Failover (SQL 2008 R2 SSRS Failover) - Dataman in LasVegas
Reporting service is turn on at both Principle and mirroring reporting database, Restore encryption key on both sqlserver as well

Migrating SQL Reporting Services to a new server by moving the Reporting Services databases - www.mssqltips.com, Dale Kelly
Step by Step with UI

Replication Case Studies

Replication Whitepaper
Using Replication for High Availability and Disaster Recovery: A SQL Server 2008 Technical Case Study and Best Practices. SQL Server Technical Article and Best ...
http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/Replication_HADR_CaseStudy.docx