04 November 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/
=================

Tempdb Configuration in SQL Server 2016

19 October 2015

Always ON Query's

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)


Thanks To: RAJU RASAGOUNDER NZ



23 September 2015

SQL Server Version List

 RTM (no SP)SP1SP2SP3SP4
↓ SQL Server 2016
     codename ?
CTP2.3
↓ SQL Server 2014
     codename Hekaton SQL14
12.0.2000.812.0.4100.1
or 12.1.4100.1
   
↓ SQL Server 2012
     codename Denali
11.0.2100.6011.0.3000.0
or 11.1.3000.0
11.0.5058.0
or 11.2.5058.0
  
↓ SQL Server 2008 R2
     codename Kilimanjaro
10.50.1600.110.50.2500.0
or 10.51.2500.0
10.50.4000.0
or 10.52.4000.0
10.50.6000.34
or 10.53.6000.34
 
↓ SQL Server 2008
     codename Katmai
10.0.1600.2210.0.2531.0
or 10.1.2531.0
10.0.4000.0
or 10.2.4000.0
10.0.5500.0
or 10.3.5500.0
10.0.6000.29
or 10.4.6000.29
↓ SQL Server 2005
     codename Yukon
9.0.1399.069.0.20479.0.30429.0.40359.0.5000
↓ SQL Server 2000
     codename Shiloh
8.0.1948.0.3848.0.5328.0.7608.0.2039
↓ SQL Server 7.0
     codename Sphinx
7.0.6237.0.6997.0.8427.0.9617.0.1063

22 September 2015

The version of the report server database is either in a format that is not valid, or it cannot be read.

Problem : 

The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is '163'. The expected version is '162'. (rsInvalidReportServerDatabase)

Solution:

1. Verify the ReportServer Database compatibility.

2. Change the Compatibility to previous version and restart the services.

3. If the above fails check the RSEXEC role has been granted for the DBO

  1. Open SQL Server Management Studio and connect to the database that hosts the ReportServer and ReportServerTempDB databases.
  2. In Object Explorer, expand the following nodes: Databases, ReportServer, Security, Roles, and Database Roles.
  3. Right-click db_owner, and click Properties.
  4. On the Database Role Properties page, click Add.
  5. On the Select Database User or Role page, type RSExecRole, and then click OK twice.
  6. Repeat these steps with the ReportServerTempDB.
  7. Restart the SSRS Services.

4. If above is already in place then it could be the latest security patch applied on the SQL Server.

5. Verify any CU/Hotfix  which has been applied and caused the issue.

6. Rollback the patch or if its on cluster apply the same patch on the other node too.

Note: Even though SSRS is not a cluster aware component But this security patch will also apply on the SQL Server and makes SSRS to stop working.



01 August 2015

COM Surrogate has stopped working" while viewing photos in Windows Photo Viewer Win7 64 Bit

ERROR:
Solution : 
Step 1: open CMD with elivated privilages
Step 2: Run the below commands one by one.
C:\>regsvr32 vbscript.dll

C:\>regsvr32 jscript.dll
regsvr32 vbscript.dll regsvr32 jscript.dll

28 July 2015

Could not redo log record - DB is SUSPECT Mode

We recently faced this issue after the maintenance window. The user database went to suspect mode and it was unable to bring online due to below error.
This case we rebuilt the logfile, to bring the DB online.

ERROR
Could not redo log record (1327929:29486:115), for transaction ID (1:1223281085), on page (1:1333024), database 'TestDB2 ' (database ID 8). Page: LSN = (1327558:113348:307), type = 2. Log: OpCode = 5, context 29, PrevPageLSN: (1327929:29486:18). Restore from a backup of the database, or repair the database.

Msg 3313, Level 21, State 2, Line 1

This case has only below options:

1. Restore the DB from the backup strategy.
2. Try DBCC REPAIR with REPAIR_FAST | REPAIR_REBUILD (No Data Loss)
3.  Final is REPAIR_ALLOW_DATA_LOSS

Alternate Solution:
===================

ALTER DATABASE TestDB2 REBUILD LOG ON (NAME=TestDB2_Log, FILENAME='D:\SQLLog\TestDB2_Log.ldf')
dbcc checkdb (testdb2)
ALTER DATABASE testdb2 SET MULTI_USER

Note: This approach might lead to data loss. Make sure we have enough backups in place.

27 June 2015

Upgrades from Earlier Versions to SQL Server 2014

We can find the supported upgrades to SQL Server 2014 from the below link:

https://msdn.microsoft.com/en-us/library/ms143393.aspx

SSRS Upgrade: 

  • Upgrade: You upgrade the Reporting Services components on the servers and instances where they are currently installed. This is commonly called an “in place” upgrade. In-place upgrade is not supported from one mode of Reporting Services server to another. For example, you cannot upgrade a Native Mode report server to a SharePoint mode report server. You can migrate your report items from one mode to another. For more information, see the ‘Native to SharePoint Migration’ section later in this document.
  • Migrate: You install and configure a new SharePoint environment, copy your report items and resources to the new environment, and configure the new environment to use existing content. A lower level form of migration is to copy the Reporting Services databases, configuration files, and if you are using SharePoint mode, the SharePoint content databases.

https://msdn.microsoft.com/en-us/library/ms143747%28v=sql.120%29.aspx

Well defined steps of SSRS to new Server:

http://www.mssqltips.com/sqlservertip/2692/migrating-sql-reporting-services-to-a-new-server-by-moving-the-reporting-services-databases/

In Place Upgrade Of SQL Server:

http://sqlmag.com/sql-server-2014/migrating-sql-server-2014

Known Issues:

http://www.sanssql.com/2013/11/upgrade-error-valid-database.html

24 June 2015

Grant execute permission to all SP's in the DB

/*
Below code will grant execute permission to all SP's in the DB.
Using below script we can grant permission to all other objects/other permission
 in the DB to diffrent users.


*/
select name into #temp_sp_list from sys.objects where type ='P' order by 1

declare @sp_count int,@sp_name varchar(50)

--select top 1 @sp_name=name from #temp_sp_list
--print @sp_name

select @sp_count=count(*) from #temp_sp_list

while(@sp_count>0)
begin
select top 1 @sp_name=name from #temp_sp_list

EXEC ('grant execute on [' + @sp_name + '] to SBUReportStaging') -- Change the user name/permission;

delete from #temp_sp_list where name=@sp_name

select @sp_count=count(*) from #temp_sp_list

print @sp_count;
end

drop table #temp_sp_list


-- Thanks to Rakesh Rao :)

02 June 2015

To stop multiple traces on server







Here in the steps to stop the traces:_



Step 1: Find out what are all running

select * FROM :: fn_trace_getinfo(default)

Step 2: Stop them by passing trace id and status
 
sp_trace_setstatus 3, 0

go

sp_trace_setstatus 3, 2



Syntax

sp_trace_setstatus [ @traceid = ] trace_id     , [ @status = ] status



0
Stops the specified trace.
1
Starts the specified trace.
2
Closes the specified trace and deletes its definition from the server.




01 June 2015

Replication _ IMP

Merge publication on a 64-bit SQL Server 2012 instance supports a maximum of 256 articles. Snapshot and transactional replication support up to 32,767 articles.

Merge publication on a 64-bit SQL Server 2012 instance supports a maximum of 246 columns in a table. Snapshot and transactional replication support up to 1,000 columns in a table.

Merge publication supports up to 1,024 bytes for a column used in a row filter. Snapshot and transactional replication support up to 8,000 bytes for a column used in a row filter.

Merge publication supports more than 30 unique indexes in a table. The unique index limit is determined by the number of articles and columns.

25 May 2015

SSIS 2012 Deployment Errors

Failed to deploy project. For more information, query the operation_messages view for the operation identifier ’219′. (Microsoft SQL Server, Error: 27203)


Solution:
To verify detail error message:
======================
select * from catalog.operation_messages
where operation_id=10069


The message column reads as follows:
 
Failed to deploy the project. Fix the problems and try again later.:Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.
As the person deploying the project, I can’t “help” the Deployment Wizard with tweaking any queries, but I could see what it was doing using SQL Profiler.  The query that caught my attention is the following:
 
exec [internal].[sync_parameter_versions] @project_id=2,@object_version_lsn=10
Notice the number of reads(1.7M) and the duration(30 seconds).  I acquired the query plan for this stored procedure and found that it would run much faster if the following two indexes are applied to the SSISDB database.

USE
 [SSISDB]
GO
CREATE NONCLUSTERED INDEX [ix1_internal_object_parameters_inc] ON [internal].[object_parameters]
(
        [project_id] ASC ,
        [project_version_lsn] ASC
)
INCLUDE (      [parameter_id],
        [object_type],
        [object_name],
        [parameter_name],
        [parameter_data_type],
        [required],
        [sensitive]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF , ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ix2_internal_object_parameters_inc] ON [internal].[object_parameters]
(
        [project_id] ASC ,
        [project_version_lsn] ASC ,
        [object_type] ASC ,
        [object_name] ASC ,
        [parameter_data_type] ASC ,
        [required] ASC ,
        [sensitive] ASC
)
INCLUDE (      [parameter_name],
        [default_value],
        [sensitive_default_value],
        [value_type],
        [value_set],
        [referenced_variable_name]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF , ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO