Wednesday, March 9, 2016

SP4 on SQL Server 2005 Fails_your account Information could not be verified



SP4 on SQL Server 2005 Fails 



  1. On the active node, use failover cluster manager to pause all passive nodes
  2. On the active node, run and complete the SQL 2005 SP4 installation
  3. On the active node, use failover cluster manager to resume all previously paused nodes
  4. On all passive nodes, run and complete the SQL 2005 SP4 installation using the /passive command line switch
  5. Reboot all node
This will ensue that any necessary system databases are updated as well, as opposed to only the binaries. This can be verified using the sql code below:
SELECT SERVERPROPERTY('productversion') as [SQL Version],
SERVERPROPERTY('productlevel') as [level],
SERVERPROPERTY('edition') as edition,
SERVERPROPERTY('resourceversion') as [Resource Version]



Wednesday, February 3, 2016

Change SQL Server Collation @ Instance Level

To change the collation at the instance level we need to follow below steps:

1. Go to Setup.exe path, this will be normally as follows:
     C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\

2. Take the SQL Services Offline.

3.  Go to command prompt and choose above location:

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME= (INSTANCE NAME) /SQLSYSADMINACCOUNTS= (SVC ACC) /SAPWD=(SAPWD) /SQLCOLLATION=SQL_Latin1_General_CP850_BIN 

Instance name:

To verify the instance name  use below query
select SERVERPROPERTY('instancename')

SVC Acc:

Use service account or any domain account which has full permissions on email.

SAPwd:

If its a mixed authentication mode then we need to use SA pwd to complete the rebuild.

4. Check the summary log once its completed for verification, we can verify under instance properties also.

Imp Note: Please take system db backups to restore once we complete rebuild. Else we will loose all SQL Server jobs and Logins. 

Wednesday, November 4, 2015

Availability Groups SQL Querys

SQL instance is part of Availability Groups details...
=======================================
SELECT
AG.name AS [AG_Group_Name],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
    ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
    ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
    ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
ORDER BY [Name] ASC


To get some information about the databases in the Availability Group:
=======================================================
SELECT
AG.name AS [AvailabilityGroupName],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole],
dbcs.database_name AS [DatabaseName],
ISNULL(dbrs.synchronization_state, 0) AS [SynchronizationState],
ISNULL(dbrs.is_suspended, 0) AS [IsSuspended],
ISNULL(dbcs.is_database_joined, 0) AS [IsJoined]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
   ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
ORDER BY AG.name ASC, dbcs.database_name

List of databases that are secondary in the Availability Group,  to be excluded when writes are required
================================================================================
SELECT DISTINCT
dbcs.database_name AS [DatabaseName]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
   ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
WHERE ISNULL(arstates.role, 3) = 2 AND ISNULL(dbcs.is_database_joined, 0) = 1
ORDER BY  dbcs.database_name

Monitoring AGs in WSFC Cluster
==========================
select * from sys.dm_hadr_cluster

select * from sys.dm_hadr_cluster_members

select * from sys.dm_hadr_cluster_networks

select * from sys.dm_hadr_instance_node_map

select * from sys.dm_hadr_name_id_map

Monitoring AGs
============
select * from sys.availability_groups

select * from sys.availability_groups_cluster

select * from sys.dm_hadr_availability_group_states

monitoring availability replicas

select * from sys.availability_replicas

select * from sys.availability_read_only_routing_lists

select * from sys.dm_hadr_availability_replica_cluster_nodes

select * from sys.dm_hadr_availability_replica_cluster_states

select * from sys.dm_hadr_availability_replica_states

select  sys.fn_hadr_backup_is_preferred_replica ('DBNAME') -- Used to determine if the current replica is the preferred backup replica.

Monitoring Availability databases :-
==============================
select * from sys.availability_databases_cluster

select * from sys.databases

select * from sys.dm_hadr_auto_page_repair

select * from sys.dm_hadr_database_replica_states

select * from sys.dm_hadr_database_replica_cluster_states

monitor the availability group listeners
=================================

select * from sys.availability_group_listener_ip_addresses

select * from sys.availability_group_listeners

select * from sys.dm_tcp_listener_states

Please refer the below queries for AlwaysOn Availability Groups monitoring reference:

    SERVERPROPERTY (Transact-SQL)

    sys.availability_group_listener_ip_addresses (Transact-SQL)

    sys.availability_group_listeners (Transact-SQL)

    sys.availability_databases_cluster (Transact-SQL)

    sys.availability_groups (Transact-SQL)

    sys.availability_read_only_routing_lists (Transact-SQL)

    sys.availability_replicas (Transact-SQL)

    sys.dm_hadr_availability_replica_cluster_nodes (Transact-SQL)

    sys.dm_hadr_availability_replica_cluster_states (Transact-SQL)

    sys.database_mirroring_endpoints (Transact-SQL)

    sys.dm_hadr_auto_page_repair (Transact-SQL)

    sys.dm_hadr_availability_group_states (Transact-SQL)

    sys.dm_hadr_availability_replica_cluster_states (Transact-SQL)

    sys.dm_hadr_availability_replica_states (Transact-SQL)

    sys.dm_hadr_database_replica_states (Transact-SQL)

    sys.dm_hadr_database_replica_cluster_states (Transact-SQL)

    sys.dm_hadr_cluster (Transact-SQL)

    sys.dm_hadr_cluster_members (Transact-SQL)

    sys.dm_hadr_cluster_networks (Transact-SQL)

    sys.dm_hadr_database_replica_cluster_states (Transact-SQL)

    sys.dm_hadr_database_replica_states (Transact-SQL)

    sys.dm_hadr_instance_node_map (Transact-SQL)

    sys.dm_hadr_name_id_map (Transact-SQL)

    sys.dm_os_performance_counters (Transact-SQL)

    sys.dm_tcp_listener_states (Transact-SQL)

    sys.fn_hadr_backup_is_preferred_replica (Transact-SQL)

MSDN Reference :- https://msdn.microsoft.com/en-nz/library/ff878305.aspx
==============
AlwaysON Dashboard :- http://www.sqlskills.com/blogs/joe/answering-questions-with-the-alwayson-dashboard/
=================