Wednesday, August 7, 2013

SQL Server 2012 Express installation failed issue

I got an strange error when installing SQL Server 2012 Express

Window Server 2008 R2 Enterprise + SP1
Microsoft Visual Studio 2012 installed
.Net 4.5 framework Installed

When I double clicked SQLEXPRWT_x64_ENU.exe, it just disappeared immediately, no setup window popped up. then I tried
1. unzip the installation code 
SQLEXPRWT_x64_ENU.exe /x

2. Open cmd.exe with administrator role

3. run setup.exe from command line, then I got error message

ERROR: Runtime error: This method explicitly uses CAS policy, which has been obs
oleted by the .NET Framework. In order to enable CAS policy for compatibility re
asons, please use the NetFx40_LegacySecurityPolicy configuration switch. Please
see for more information.

looks like the error is caused by higher version of .Net Framework.

4. Uninstalled .Net 4.5 framework

5. rerun setup.exe, this time it worked as expected.

sometimes unzip the installation file and run from command line can get more detail error message, especially when you install hotfix and service patch.

Sunday, August 4, 2013

Alwayson availability groups failover monitoring - Part 1

There are a lot of ways to monitor AG, sql server errorlog, window event log, cluster log......, here I list another way to check and monitor AG state with the log files below:

1. AlwaysOn Health Diagnostics Log
These files in the SQL Server Log directory have the following format: <HOSTNAME>_<INSTANCENAME>_SQLDIAG_X_XXXXXXXXX.xel.

2. Extended Events session "AlwaysOn_health" log
it is auto created when setup alwayson AG,

and by default it does not auto startup, so you can enable it manually

Now, let's use the 2 log files to monitor alwayson AG failover
>2 nodes  alwayson AG, SQL2012-01(Principle), SQL2012-02(Secondary)
>AG Name: MyAG
>Availability mode: Synchronous commit
>Failover mode:Automatica

1. Manually Failover :   Failover AG from SQL2012-01(01) to SQL2012-02(02)
Please note: Do not use the Failover Cluster Manager to move availability groups to different nodes or to fail over availability groups. The Failover Cluster Manager is not aware of the synchronization status of the availability replicas, and doing so can lead to extended downtime.
ou must use Transact-SQL or SQL Server Management Stud

We run the T-sql below on 02

a) On 01, Open "Merge Extended Event Files"

b)Open "AlwaysOn Health Diagnostics Log" file and Extended Events session "AlwaysOn_health" log

c)Sort result by "timestamp"

you can also click "Choose Columns" to add more column to display.

d)Check the log by failover time.
> the first log we find is AVAILABILITY GROUP "offline Pending" State

so AG is taking offline.

>Next, the replica state on 01 is changed from "Primary" to "Resolving"

>Next, AG is offline completely

>finally, replica state is changed from "Resolving" to "Secondary"

e) On 02, use the same way to check the log file
>first, the failover command was issue on 02, begin failover

>Next, replica state is changed from "Secondary" to "Resolving_Pending_Failover"

there are also some "info" message among all events, for instance, it shows the "Diagnostics" is started on new primary 02

>Next, replica state is changed from "Resolving_Pending_Failover" to "Resolving_Normal", then changed to "Primary_Pending"

>Next, AG came online on 02

>Finally, "Alter availability group" command was commit, and replica became to "Primary"

Next, I will try to monitor the AG in other conditions.