While planning to configure always on availability group for first time on a multi subnet cluster found following use articles, it provides clear picture in terms of design and architecture, our requirement was to build SQL 2012 cluster on DC1 along with a reporting standalone SQL instance in same DC1, along with it, we want to have DR solution in DC2 where two standalone SQL instances are present on their respective server, to achieve this […]

    For setting up replication on AlwaysOn availability group in SQL Server 2012 and above, following need to be taken care to ensure replication will work irrespective of the AAG placement.   The following sections build the environment described above:   Configure a remote distributor Configure the Publisher at the original Publisher Configure Remote distribution on possible publishers Configure the Secondary Replica Hosts as Replication Publishers Redirect the Original Publisher to the AG Listener […]

Starting Windows 2012, Windows cluster supports asymmetric disks which is required for Cluster participating in Always ON technology hence is a useful concept to be aware of,   http://www.sqlserverfaq.net/2012/09/16/when-do-we-need-asymmetric-storage-with-alwayson-fci-and-availability-storage/   https://social.technet.microsoft.com/Forums/windows/en-US/493bf438-eb9d-405c-81c5-42aaa31eef8a/configure-asymmetric-storage-for-crosssite-windows-failover-cluster?forum=winserverClustering  

Came across weird situation where received alert for SQL Server transaction log file full and found log_reuse_wait_desc=replication from sys.databases   Now before mentioning troubleshooting step, setup of the database is question is little bit complex because Database is part of SQL Server replication where database publisher is at vendor side and we don’t have access to publisher DB 🙁 Oracle Golden Gate replication is configured from this database to Oracle DB Database in question is […]

Issue: Trying to turn of database mirroring, disconnected mirroring from Principal database but when checked the partner Database, found that database still in restoring mode, when trying to delete it, it failed stating database is part of mirroring as below,     Then ran following command/s which resulted in error, ALTER DATABASE [STASxref] SET PARTNER OFF; ALTER DATABASE [STASxref] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;   Msg 1404, Level 16, State 11, Line 1 The command failed because […]

Found SQL Server 2005 SP4 CU3 A/P failover cluster SQL services are stopping and starting several times in interval of 30mins to 1 hour. Following errors are found,   Application Log Errors: [sqsrvres] printODBCError: sqlstate = 08S01; native error = 0; message = [Microsoft][SQL Native Client]Communication link failure [sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed [sqsrvres] OnlineThread: QP is not online. [sqsrvres] printODBCError: sqlstate = 08S01; native error = 2746; message = [Microsoft][SQL Native Client]Communication link failure [sqsrvres] […]

Dropping publisher database may be required in various scenarios; please find steps which can help you to drop in case you are facing issues with it. I am including roll back approach as well, it helps sometimes.   First step -> Drop Publication and subscription Second Step -> Drop Database   First step, Right click Publication > Generate Scripts > Generate Drop replication and objects script > Save script to local drive Right click Publication […]

Today I have to restore a publisher database from production backup in QA environment which is part of transactional replication in SQL Server 2005, now there are multiple ways to do it, visit this link for Microsoft recommendation. My approach was, Stop and remove replication, restore database, recreate replication without reinitializing   Before beginning ensure that there is no pending distributed items by going to replication monitor > publication > double click the subscription > […]

In my case System admin accidently deleted SQL Server 2005 SQL Service resource from SQL cluster group (when trying to remove a disk from cluster) and re-added again but after re-adding SQL server failed to come up and following error noted in SQL Error log, ———– Error: 26054, Severity: 16, State: 1. Could not find any IP address that this SQL Server instance depends upon. Make sure that the cluster service is running, that the […]

Today while troubleshooting log full issue, came across following scenario where two sql servers are in different domain and both are part of simple transactional replication where log file full was on subscriber, after investigation found that replication is working fine and no transaction are in undistributed state still log file is not shrinking, we tried changing recovery model to simple, etc but still issue didn’t get resolved. Finally we manually executed script to mark […]