not running expiring soon


Expiring Soon

When looking at the replication monitor, a message may appear under the ‘status’ column of the subscription watch tab that displays the message ‘not running expiring soon’.

This message is caused when a specific elapsed time has occurred since the last transaction.  This typically occurs on publications that are on a schedule or the msrepl_commands table has a high table count.   It can also occur when the replication monitor has not fully refreshed the history tables.

A publication that is not currently running and has this message displayed for a certain length of time may become marked inactive.  When a publication is marked inactive, the next remedy is to re-push a snapshot or change the system table flag.

To avoid the above situation:

  • Right click on agent and ‘Stop Synchronizing’
  • Once stopped, right-click and ‘Start Synchronizing’

Issuing both these commands will get the agent back online.  If this doesn’t clear the message within a minute,  you see a message that says ‘Expiring soon/ Expired’. Repeat the above steps.  This will cause the status to be updated in the history tables.  The agent into a critical status, but will eventually be marked as excellent.  If the status says critical, it is okay and just indicates that the history tables have not been updated yet.

 

 

Advertisements
Posted in Replication | Leave a comment

can’t insert duplicate key to ‘subscriber’’ or a can’t insert because of a duplicate key


This error message ‘can’t insert duplicate key to ‘subscriber’’ or a can’t insert because of a duplicate key message pertain to a record that is trying to be inserted that previously existed.

The primary reason this can occur is if two publications are set up containing the same article which are pushing data to the same location.

  • If the article exists in more than one publication:
    • Delete one of the articles if it is contained in multiple publications
        • Re-push a snapshot only if you are unable to delete duplicate transactions from the msrepl_transactions table and the msrepl_commands table in the distribution database.  This can be done by following the same steps listed in section here
      • If the article does not exist in more than one publication:
        • Delete the duplicate transaction causing the error in both the msrepl_transactions table and the msrepl_commands table in the distribution database.  This can be done by following the same steps listed in section here
      • Start the agent
Posted in Replication | Leave a comment

Subscriber Error: The row was not found at the Subscriber when applying the replicated command


The error “The row was not found at the Subscriber when applying the replicated command” can occur for three reasons:  1) A publication is setup using a no-sync initialization.  2)  Data was changed on the subscriber (which should never occur in a read-only environment).  3)  Either an update or delete statement is occurring on a record

that doesn’t exist.   This last error can occur when there is a relationship that exists on a set of tables (such as a cascade) that depend on one another and one table has a completed transaction occur before the dependent table is updated.

For example, transaction A issues an insert statement for record 1 and transaction B issues an update statement for record 1; however, transaction B gets sent through before transaction A.

When this error occurs, it may be possible to wait a few minutes and attempt a re-sync to correct the error.  If doing so does not correct the error, then the transaction that is causing the error needs to be looked up and verified to determine if it exists on the subscriber.

Running the stored procedure Usp_GetProblematicTransaction will assist in identifying the problematic transaction.

If the row does not exist in the subscriber then by following one of the steps below will assist in fixing the error without re-initializing the publication:

EXAMPLE (replication command being issued):

  CALL [sp_MSupd_dbotblDelays] (,,,,,,,,,1,25451,,,,,,,{ts '2007-04-30

                                13:04:00.360'},25451,8424052,0x000606)}

Repeat steps 1 through 5 until there are no more errors or you have manually inserted 5 records into the subscriber.  As a rule of thumb, more than 5 record errors may be an indicator of data integrity errors.  If this is the case, re-initializing the table may be the best path to take.

  1. Execute the stored procedure Usp_GetProblematicTransaction from the publisher server.
  2. Using the example above, log on to the subscriber server and select from the tblDelays tables where the key field is equal to 8424052.  (Note:  the primary key is always located in the second to last column of the transaction)
  3. If the record does not exist change, the connection to the publisher database and run the same query to retrieve the record needed.
  4. Write an insert statement using the values from step 3 and change the connection back to the subscriber and execute the query.
  5. Check to see if the agent in error has started automatically.  If it has not, then stop and restart the agent.  Refresh the agent a few times to make sure you do not see any more error messages such as ‘error executing a batch of commands’.  This is an indication that there is another row not found error.

NOTE:  If the error continues after 5 tries than the publication will have to be re-initialized in the evening.  In order to prevent any delays in the publication change the distribution agent profile to skip the error

  1. Within Replication Monitor right click on the subscriber agent à Agent Profile à Continue on data consistency errors
  2. Stop and start the agent for the new profile to be used
  3. After the publication has been re-initialized change the distribution agent back to its original profile.

Another method is to execute the Tablediff.exe utility for the table in error.  To utilize this method, follow these steps:

  1. Execute the tablediff.exe with a file output specified.
  2. Open the created SQL file on the subscriber
  3. Temporarily change the distribution agent profile:
    1. Within Replication Monitor right click on the subscriber agent à Agent Profile à Continue on data consistency errors
    2. Stop and start the agent for the new profile to be used
  4. Utilize the new profile for a day in order to avoid any more errors.  Otherwise, duplicate rows that may occur from running the script in step 2.

If you run the stored procedure Usp_GetProblematicTransaction and the transaction returned is a delete transaction, than follow the following steps:

  1. Run a select statement on the subscriber passing in the primary key to verify if the record exists.
  2. If the record does not exist, than delete the command by executing the stored procedure Usp_DeleteProblematicTransaction.
  3. Check to see if the agent in error has started automatically.  If it has not, then stop and restart the agent.  Refresh the agent a few times to make sure you do not see any more error messages such as ‘error executing a batch of commands’.  This is an indication there is another row not found.
Posted in Replication | 1 Comment

Publisher Error: could not connect to server ‘Newname’ because distributor_admin is not defined as a remote login at the server


can occur if there was replication setup on the server and the server was renamed before replication was dropped.  In order to correct this you will need to run the proper SQL statements to drop both the publisher and distributor on the server.  Then manually go through the original publisher and distributor server to ensure all replication has been removed before you begin setting up replication on the server.

If the server was renamed then you can run the following SQL statements on the corresponding servers to check if they return the correct server names.

Check Server Properties

 

Use Master

go

Select @@Servername

select SERVERPROPERTY(‘ServerName’)

If the @@ServerName returns a NULL than try the following query:

Use Master

go

Sp_DropServer ‘OldName’

GO

 

Use Master

go

Sp_Addserver ‘NewName’, ‘local’

GO

You can use the @@SERVERNAME global variable or the SERVERPROPERTY(‘ServerName’) function in SQL Server to find the network name of the computer running SQL Server.

The ServerName property of the SERVERPROPERTY function automatically reports the change in the network name of the computer when you restart the computer and the SQL Server service.

The @@SERVERNAME global variable retains the original SQL Server computer name until the SQL Server name is manually reset.

 

Posted in Replication | Leave a comment

SQL Server 2012 AlwaysOn High Availability and Disaster Recovery Design Patterns


The following design patterns emerge as end-to-end HA+DR solution:

  • Using Multi-site Failover Cluster Instance (FCI) for local high availability and disaster recovery solution
  • Using Availability Groups (AG) for local high availability and disaster recovery solution
  • Using Failover Cluster Instance (FCI) for local high availability, and Availability Groups (AG) for disaster recovery solution

(1) Multi-site Failover Cluster Instance (FCI) for HA and DR

The ability to implement a multi-site FCI as a HA and DR solution has been available in the SQL Server product for a number of previous releases, and many customers have been successfully using the solution (example:http://sqlcat.com/sqlCat/b/whitepapers/archive/2010/09/20/sql-server-high-availability-and-disaster-recovery-for-sap-deployment-at-qr-a-technical-case-study.aspx). In earlier versions of SQL Server, multi-site FCI required a stretch VLAN. SQL Server 2012 removes that requirement (along with a number of other improvements to the failover cluster instance technology) enabling multi-site FCI to be more commonly adopted as a HA and DR solution.

Multi-site FCI requires storage level replication (provided by the storage vendor) to maintain a copy of the databases at the DR site. Even though there are separate storage volumes at each site, to SQL Server, this looks like a Shared Storage solution. Other important attributes of this solution are:

  • The unit of failover for both local HA, and remote DR is SQL Server instance.
  • No requirement on database recovery model, as storage level replication is used for maintaining the remote copy of the data.
  • The DR copy of the data is not readable.

The whitepaper http://sqlcat.com/sqlcat/b/whitepapers/archive/2011/12/22/sql-server-2012-alwayson_3a00_-multisite-failover-cluster-instance.aspx provides architecture details and best practices for this solution.

(2) Availability Group for HA and DR

Using Database Mirroring for local high availability, and combining it with Log Shipping for a disaster recovery solution is a popular deployment architecture prior to SQL Server 2012 (example:http://sqlcat.com/sqlcat/b/whitepapers/archive/2010/10/29/high-availability-and-disaster-recovery-for-microsoft-s-sap-data-tier-a-sql-server-2008-technical-case-study.aspx).

With SQL Server 2012, the Database Mirroring and Log Shipping solution can be replaced with an Availability Group solution with multiple secondaries.

This is considered a non-shared storage solution, as each SQL Server in the topology has its own copy of data and does not need to share storage.  Other important attributes of this solution are:

  • The unit of failover for local HA, and DR is the Availability Group (a group of one or more databases).
  • The database is required to be in the FULL recovery model.
  • The DR replica can be utilized as an Active Secondary (Readable Secondary ReplicasBackup on Secondary Replicas).

The whitepaper http://sqlcat.com/sqlcat/b/whitepapers/archive/2012/06/22/alwayson-architecture-guide-building-a-high-availability-and-disaster-recovery-solution-by-using-alwayson-availability-groups.aspx provides architecture details and best practices for this solution.

(3) Failover Cluster Instance for local HA and Availability Group for DR

Using Failover Cluster Instance for local high availability, and combing it with database mirroring for a disaster recovery solution is a popular deployment architecture prior to SQL Server 2012 (example:http://sqlcat.com/sqlcat/b/whitepapers/archive/2009/08/04/high-availability-and-disaster-recovery-at-serviceu-a-sql-server-2008-technical-case-study.aspx).

With SQL Server 2012, the Database Mirroring can be replaced with an Availability Group for the DR solution, while continuing to use Failover Cluster instance for local HA.

This architecture is a combined Shared Storage and Non-Shared Storage solution. Other important attributes of this solution are:

  • The unit of failover for local HA is the SQL Server instance.
  • The unit of failover for DR is the Availability Group (a group of one or more databases).
  • The database is required to be in the FULL recovery model.
  • The DR replica can be utilized as an Active Secondary (Readable Secondary ReplicasBackup on Secondary Replicas).

The whitepaper http://sqlcat.com/sqlcat/b/whitepapers/archive/2012/06/22/alwayson-architecture-guide-building-a-high-availability-and-disaster-recovery-solution-by-using-failover-cluster-instances-and-availability-groups.aspx provides architecture details and best practices for this solution.

 

 

 

Posted in SQL SERVER 2012 | Leave a comment

Not upgrading your hardware for SQL Server 2012


SQL Server 2012 does not support AWE. That means if you still have a 32-bit operating system and are currently using AWE in order to see beyond 4GB of RAM, if you upgrade to SQL 2012 you will be limited to only* that 4GB of physical RAM.
It’s time for you to move to a server and operating system that was released within the past ten years. If you are expecting to have more than 4GB of RAM, then you need to use the 64-bit version of SQL 2012.

Posted in SQL SERVER 2012 | Leave a comment

Key Learnings from Early Customer Deployments for SQL Server 2012


Windows Cluster

Is the foundation for HA and DR in SQL Server 2012 AlwaysOn

AlwaysOn inherits all “characteristics” of Windows Cluster

Windows Cluster

every single AlwaysOn deployment is a Windows Cluster deployment

Windows Cluster

understand Windows Cluster for succesfully deploy, operate, monitor, troubleshoot, administer AlwaysOn

key areas are: quorum model, cluster network communication, DR procedures, cluster.exe, PowerShell

Windows Cluster

SQL Cluster (SQL Server Failover Cluster Instance)

therefore, is NOT necessarily a shared-storage cluster

Windows Cluster

many key enhancements have been made to Windows Cluster specifically for SQL Server 2012 AlwaysOn

Asymmetric Disk

Node Votes

Asymmetric Disk as Quorum resource

Posted in SQL SERVER 2012 | Tagged | Leave a comment