SQL Server Database Recovery Archive

Replication errors will have a key with which we should identify the missing row from the subscriber. We must then manually copy those records from Publisher and then insert into the subscriber. While doing this activity, we would have to turn off and turn on the identity column. In this article, we will learn how to deal with Unique keys in a replication.

Missing data:

Unique Keys In SQL Server Replication ErrorsConsider that you had initialized a subscription without a snapshot by using options like initialize with backup or initialize from log sequence number or replication support only. Before you change the configuration of the publication, some transactions can occur in the database and data might be changed. These changes will not get replicated. This will cause distribution agents to fail and your SQL Server error log might receive error messages to confirm that subscriber is missing some records or rows. You might also receive key violation error.

This can also happen when a user or an application deletes or adds records to the subscriber database.

Setup

The above said issue usually occurs when your replication was set up in any of following ways

  • SQL Server Replication ErrorsContinuous mode or a high frequent schedule is used for distribution agents
  • A long-running transaction has introduced a very big change in the data which in turn would develop a latency and stop the log reader agent
  • A same log reader agent is used to service multiple publications

Fix it

The first step to fix such issues is to identify IDs of commands that had failed. You should also identify transaction sequence numbers. You can easily do this using the replication monitor. If you prefer to use GUI, open the replication monitor and then navigate to the “Distributor to Subscriber History”. From the sessions of the distribution agent, select session that has errored out and then you can find the transaction sequence number from the error details section.
You can also use this script to fetch that information:

Use distribution
go
Select * from msrepl_errors
order by time desc
go

Use the transaction sequence number obtained from the previous script in this query:

Use distribution
go
Exec sp_browserreplcmds 'transaction sequence number' , 'transaction sequence number'
go

You can then continue to validate articles and identify missing records.

Turn On and Off

Now, if you manually try to push records into subscriber, you will receive an error stating that you are violating the primary key and the operation would fail. This is where the IDENTITY_INSERT comes into play. We must turn on this feature, insert records into our target table and then turn it off.

-- The statement below allows values to be inserted into the identity column 
SET IDENTITY_INSERT target_table ON
GO
-- Now SQL will allow you to insert values
INSERT INTO target_table(Col1, Col2) VALUES(anyvalye,anyvalue)
GO
--Turn of the IDENTITY_INSERT
SET IDENTITY_INSERT target_table OFF

When manually pushing records to Subscriber, please work with attention else you might end in corrupt mdf files.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including pst recovery and excel recovery software products. For more information visit www.datanumen.com

Be the first to comment

With the service pack 2 of SQL Server 2014, you can create a copy of a user database without the actual data. i.e., you would be creating just the schema and statistics of your source database. In this article, we will learn how to quickly create clone of multiple user databases

A copy needed for investigation:

Copy The Schemas Without DataTo find the root cause of a performance problem, you might need a copy of your existing database. In such cases, you can use the following query to create copies of multiple SQL Server databases.

The query given below creates a table-valued function in your master database. This function will help you to split a delimited string. We would be using this function to split and get individual database names.

USE [master]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[Split_String_Old]
(
 @dbs NVARCHAR(4000),
 @dl NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
 WITH tbl1(v_start,v_end)
 AS(
   SELECT 0 AS v_start, CHARINDEX(@dl,@dbs) AS v_end
   UNION ALL
   SELECT v_end+1, CHARINDEX(@dl,@dbs,v_end+1)
   FROM tbl1
   WHERE v_end > 0
 )
 SELECT 'S.NO' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
   'Database' = SUBSTRING(@dbs,v_start,COALESCE(NULLIF(v_end,0),LEN(@dbs)+1)-v_start)
 FROM tbl1
)

GO

T-SQLBefore executing the query mentioned below, update the variable dbcontainer with correct database names separated by a semicolon. By default, the clone command would create a copy of your SQL Server database in read only mode. So after creating a copy this query would also execute Alter database command to convert the copy of a database in to read write mode. Please do note that these database copies should be purely used for diagnostic and troubleshooting purposes and not as a production SQL Server database. Microsoft recommends to detach these database copies from the server soon after they are created. Please do note, databases created using this method will not help you in SQL recovery.

declare @dbcontainer varchar(1000)
set @dbcontainer = 'Database1;Database2' -- list database(s) here. Separate multiple databases by ;
declare @dbname varchar(200)
Declare cur1 cursor for
Select [database] from [dbo].[Split_String_Old] (@dbcontainer,';')
open cur1
fetch next from cur1 into @dbname
while @@FETCH_STATUS = 0 
begin
declare @sql as Varchar(1000)
set @sql = 'DBCC CLONEDATABASE (''' + @dbname + ''', '''+ @dbname + '_Clone_Database'')'
print @sql
EXEC (@sql)
set @sql = 'ALTER DATABASE [' + @dbname + '_Clone_Database] SET READ_WRITE WITH NO_WAIT'
print @sql
EXEC (@sql)
fetch next from cur1 into @dbname
end
close cur1
deallocate cur1

The following query will help you to identify databases that are just a clone.

Create Table T1
(
dbname varchar(200), isclone sql_variant
)
declare @dbname varchar(200)
Declare cur1 cursor for
Select [name] from [dbo].[sysdatabases]
open cur1
fetch next from cur1 into @dbname
while @@FETCH_STATUS = 0 
begin
declare @sql as Varchar(1000)
set @sql = 'INSERT INTO T1 SELECT ''' + @dbname + ''' , DATABASEPROPERTYEX(''' + @dbname + ''', ''isClone'')'
print @sql
EXEC (@sql)
fetch next from cur1 into @dbname
end
close cur1
deallocate cur1
select * from T1 where isclone = 1
DRop table T1

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including pst repair and excel recovery software products. For more information visit www.datanumen.com

Be the first to comment

You might have added databases to your always-on group. When you use multi-subnet cluster and set up SQL Server Always On, you might receive an error message stating that the connection to your primary is inactive. In this article, we will learn how to fix that and synchronize databases participating in Always On.

Using Wizard or script

Inactive Connections To Your Primary ReplicaSQL Server always-on availability group can be created using the new availability group wizard or a TSQL script. One common reason that the action might fail is due to the issues in the connection between the primary replica and secondary replica. Service accounts on replicas

Review permission of SQL Service accounts on each replica. If the service account of the secondary replica is not added at the primary replica, then an attempt to contact the primary replica through the availability group wizard would fail.

-- Step 1 Identify the SQL service account
Select Top 1 service_account from sys.dm_server_services where servicename like '%SQL Server%'
-- Step 2 Check if this service account was added on the other replica
If not Exists (select loginname from master.dbo.syslogins 
   where name = 'SERVICE ACCOUNT FROM STEP 1')
Begin
PRINT 'This account does not exist in this replica. Please add it'
End

Endpoints

For always-on availability groups to functions properly, endpoints must have been created and their status should be “Started” on all secondary replicas and on the primary replica.

-- To View all end points, run this query
select * from sys.endpoints

-- To View HADR endpoint, please run this query
select * from sys.tcp_endpoints where type_desc='DATABASE_MIRRORING'

SQL Server Always On availability GroupIn some cases, though the endpoints were not started, their state_description might appear as ‘Started’. So it is recommended to execute the command ‘ALTER ENDPOINT’ for each HADR endpoint at all available replicas.
Many times, endpoints of SQL server are configured not to use SQL Authentication but use Windows Authentication. In such cases, verify and confirm that domain accounts are used as startup accounts by SQL Services. If you hace missed to verify and fix this, do not worry, this would be handled by the Availability Group Wizard. When the SQL Server Always-ON Availability Group Wizard identifies that the startup account is a local account, it would report this to you as a pop-up message. If you use TSQL script instead of the Wizard, commands like ‘SET HADR AVAILABILITY GROUP’ would fail.

SQL Server Always On availability group help you to quickly recover from corrupt SQL Server database pages by recieciving the stable version from other replicas.

Issue with ports

If a replica is not configured to listen on port 5022 your attempt to create an availability group would fail. To identify the port of your SQL server, you can use the query below:

SELECT local_tcp_port
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
GO

If all of above listed methods did not help you, try this query and it will help you to diagnose further.

select tbl2.replica_server_name as 'Server Name', tbl2.endpoint_url as 'ENDPOINT',
tbl1.connected_state_desc as 'Endpoint Status', tbl1.last_connect_error_description as 'Error description', 
tbl1.last_connect_error_number as 'Error number', tbl1.last_connect_error_timestamp as 'Time Stamp' 
from sys.dm_hadr_availability_replica_states tbl1 join sys.availability_replicas tbl2
on tbl1.replica_id=tbl2.replica_id
where tbl1.is_local=1

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Outlook fix and excel recovery software products. For more information visit www.datanumen.com

Be the first to comment