Questions tagged [transactional-replication]

The automated distribution of transactional changes from a publishing database to one or more subscriber databases.

151 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
5 votes
1 answer
2k views

How to completely get rid of replication subscriptions?

I have restored a database in another server. I don't want any of the transactional subscriptions of the former database. I already called exec sp_removedbreplication 'MyRestoredDB' go And also ...
ReluctantDBA's user avatar
5 votes
1 answer
921 views

Replication throw an error after failover mirror partner is down in SQL Server

Received an error mentioned below after failover when mirror partner is down. Error : to fail over to a database which is not configured for database mirroring. code: 22037, text: 'Invalid ...
Paresh's user avatar
  • 213
4 votes
0 answers
2k views

SQL Server: Replicated transactions are waiting for the next Log back up or for mirroring partner to catch up

I have two SQL Server DB instances on the same machine with one hosting a replica of the other. In addition, I have transaction replication setup for all tables on the main instance to the replica. I ...
Andrew B.'s user avatar
3 votes
1 answer
722 views

Does ordinal position of columns matter for SQL Server replication

I am setting up replication on SQL Server 2017. Transaction replication with push subscriptions such that many smaller database are replicated into a single larger database for reporting. The ...
Booji Boy's user avatar
  • 145
3 votes
1 answer
489 views

Download the replication snapshot file using FTPS

I have two databases for two companies. Company A's database contains domain data. The other company is pulling the data using snapshot replication. We have used FTP to communicate: Created FTP ...
mukesh UN singh's user avatar
3 votes
0 answers
879 views

transaction replication causing too many deadlocks after adding a indexed view - what to monitor in order to avoid this?

I have created the following indexed view and its clustered index: This view was created on a subscriber database and all the tables used in the view are constantly being written to through the ...
Marcello Miorelli's user avatar
3 votes
0 answers
2k views

SQL Server replication - error when creating a subscription - The article '(null)' does not exist

When trying to add a subscription to an existing publication use [Product] exec sp_addsubscription @publication = N'Product', @subscriber = N'REPLON1', @destination_db = N'Product',...
Marcello Miorelli's user avatar
3 votes
0 answers
311 views

Domain name change of SQL servers involved in replication

I have a situation where client wants to change their domain name of servers hosting SQL Servers(e.g. from AB to CD). I have a transactional replication with updatable subscription configured with 1 ...
SQLPRODDBA's user avatar
  • 1,908
3 votes
0 answers
248 views

SQL Server replication - recommendations for stability

We have two SQL Server 2008R2 Standard servers, and we've set up replication of a single database between them. The replication is about as simple as it can get - it's a transactional replication from ...
KenD's user avatar
  • 303
3 votes
0 answers
805 views

Transactional Replication Error adding new subscription (null) article

When attempting to add a new subscription to an existing Transactional publication on 2008. It is all configured on 1 server as it is for Pre-Production testing. I get the following error when ...
DamagedGoods's user avatar
  • 2,591
3 votes
0 answers
70 views

List of changes required to upgrade scripts after starting to use replication in SQL Server

We have recently setup transactional replication on our test and production databases. As part of that change, we've had to make changes to how we create upgrade scripts as well. The goal is to make ...
GaTechThomas's user avatar
2 votes
0 answers
81 views

Transactional replication not replicating with 'The process is running and is waiting for a response from the server' error after adding new article

I have a Transactional Replication setup which has been working fine for a long time. It is a Pull Subscription. I got a new requirement to replicate a large article which is 10GB. allow_anonymous &...
Sowjanya Ravi's user avatar
2 votes
0 answers
629 views

SQL Server replication max server name length

Question Does SQL Server have a max server name limitation for SQL Server Replication with publishers or subscribers? Background: My coworker is worried about going above 15 characters for a server ...
SQLing4ever's user avatar
2 votes
2 answers
201 views

RAISERROR when replication falls behind

I have a third party incident management tool, that creates tickets from errors in the SQL logs. Occasionally replication stops replicating without creating errors in the SQL logs. I want to create ...
James Jenkins's user avatar
2 votes
0 answers
265 views

the mystery of the table showing up in the subscription, but not in the publication

TL;DR: This question refer to sql server 2016, transactional replication I've got a table that shows up in the subscription but not in the publication. Here are the details: In one of my servers ...
Marcello Miorelli's user avatar
2 votes
0 answers
64 views

Does Azure SQL natively support being the subscriber in transactional replication where Oracle is the publisher?...and if PostgreSQL is the publisher?

Is there a native way to setup transactional replication where an Azure SQL database is the subscriber and the publisher database is in Oracle? What if the publisher database is in PostgreSQL? If ...
J.D.'s user avatar
  • 37.5k
2 votes
0 answers
387 views

Foreign Key Error with Transactional Replication from Backup

I'm getting a missing foreign key error when I begin transactional replication. Steps taken: Created a pull publication (live server) Created a copy only full backup Restored backup on reporting ...
user2200550's user avatar
2 votes
1 answer
149 views

Snapshot Folder Location for Replication with Clustered Instance

I am assigned to create a transactional replication with a SQL Server clustered instance for reporting purposes. I'm planning to use the Distributor and the Subscriber on the same box. Regarding the ...
SQL_NoExpert's user avatar
  • 1,049
2 votes
0 answers
73 views

Migrating a Replicated Database from one SQLServer 2012 instance to another

We are working on a migration plan from old hardware to new hardware, these are SQL 2012 servers. A couple of the databases heavily use transaction replication so I'm trying to avoid recreating and re-...
MrTCS's user avatar
  • 699
2 votes
0 answers
237 views

How to mirror a replicated database

Forgive me if this question has already been asked - I've hunted around, but can't find something that matches my situation. Say I have my Principle server (DB1), with a database Mir1. I set up ...
Jamie Burns's user avatar
2 votes
1 answer
342 views

Identity management in Transaction replication with updatable subscription

I have migrated my publisher DB to new server yesterday. For migration what I did was removed subscriptions and publication completely backed up DB on which publication was configured restored DB to ...
SQLPRODDBA's user avatar
  • 1,908
2 votes
0 answers
1k views

SQL Server 2008 Replication "between retries" or "worker thread no available"

All our existing servers are in SQL 2008 and I am in the process of upgrading to SQL 2014 and as well moving SAN to different SAN storage. As part of it I have to upgrade my distributor. We got about ...
Nachi's user avatar
  • 21
2 votes
0 answers
3k views

Create transactional publication error in SQL Server 2014

In attempting to create a new Publication for Transactional Replication in SQL Server, I end up getting the following error, with pretty much no way around it: SQL Server could not create ...
dthree's user avatar
  • 261
2 votes
0 answers
1k views

Transactional Replication in SQL server 2014

We need to set up transactional replication in SQL server 2014, Enterprise edition so that: Database to be replicated from Publisher which is approximately 2 TB in size. This would be used 24*6. Here ...
KASQLDBA's user avatar
  • 7,159
2 votes
1 answer
2k views

Distribution clean up job in transactional replication removed records but not files

Distribution clean up job ran without errors according to schedule, but I noticed that the snapshot files were not removed even if when created beyond max_disretention period. Records from ...
Eldar's user avatar
  • 143
2 votes
1 answer
256 views

Is there a Group Policy that can block replication snapshots?

Has anyone seen a domain Group Policy stop a transactional replication snapshot from being configured and executed with the message: A required privilege is not held by the client. I am having ...
David's user avatar
  • 21
2 votes
0 answers
117 views

Snapshot Agent - Transactional Replication - Why a complete snapshot?

Often, I will add a single article to an existing transactional replication publication. I then start the snapshot agent to copy the new article and it's data over to the subscriber. Most of the time, ...
Kevin's user avatar
  • 21
2 votes
0 answers
497 views

dropping and adding subscription only picking up new article on some subscribers

I have used the following script to drop aand re-add a table from a subscription so I could move replace the ntext fields with nvarchar(max) columns. the script worked fine, but when I ran the ...
DamagedGoods's user avatar
  • 2,591
2 votes
0 answers
268 views

SQL Server : Transactional Replication Filter By Computed Column

There is a database that contains records for multiple groups of people. These records do not contain a column indicating which group the record is "owned" by (very frustrating). I need to setup ...
jzacharuk's user avatar
  • 327
1 vote
0 answers
44 views

PostgreSQL replication role without permission to see or delete other replication slots

We have a PostgreSQL server with multiple databases for different projects. We want to use logical replication on those databases so the applications receive all the changes for their replication slot ...
Eleasar's user avatar
  • 111
1 vote
0 answers
1k views

Publication Error: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction

I got some requirement to implement transactional replication, so I am doing some research on it, but I am getting some issues while creating the publication. I have restored adventure works database ...
Ankit Gupta's user avatar
1 vote
1 answer
47 views

Drop Subscriber at SQL Transactional replication

We have SQL Transactional pull replication: Publisher and Distributor are at the same server - ServerA. It publicates Publication1. Pull Subscribers to Publication1 are - ServerB, ServerC and ...
NewUser's user avatar
  • 11
1 vote
0 answers
83 views

Peer to Peer replication ID number

I'm familiar with P2P replication conflicts which normally show along the lines of conflict detected between peer 1 (incoming) and peer 2 (on disk). The issue I'm seeing is that the conflicts are ...
BobD's user avatar
  • 11
1 vote
0 answers
83 views

SQL Server Replication - One-to-Many in Same DB

I'm setting up some canary tables to more effectively monitor replication latency between on-prem and Azure. I've set it up using one source table that gets updated with a timestamp every few seconds, ...
pix1985's user avatar
  • 474
1 vote
0 answers
306 views

Login Failed while settting up transactional replication on a single computer

I'm trying to learn how to implement transactional replication on SQL Server by testing it out on my PC. Is it possible to set up transactional replication on a single PC, using 2 SQL Server instances,...
Marcus's user avatar
  • 59
1 vote
1 answer
8k views

Why are my postgres logical replication workers crashing after syncing a certain amount of data?

I am trying to setup a logical replication between two postgresql 12 DBs run in Docker containers on AWS ECS. I gave the task 2 vCPU and 6 GB of ram so quite luxurious for a DB with ~1e7 rows in two ...
SiOx's user avatar
  • 111
1 vote
2 answers
572 views

I am looking for a query to determine when the snapshot has been finished applying on the subscriber

Is there any way to determine with a query when the replication has finished applying the snapshot on the subscriber? I have a transnational pull replication set up for reporting purposes and it also ...
LadyBug1's user avatar
  • 144
1 vote
0 answers
86 views

Replicate data with minimal downtime using transactional replication

I have a SQL Server database with nearly 7TB of data. I want to replicate it using transaction replication. What will be its approximate downtime? Please suggest a method to implement it with minimal ...
Yadhu Krishnan PD's user avatar
1 vote
0 answers
2k views

Transactional Replication : Issues with Identity Column in Subscriber

I have an Transactional replication set up form Server A to Server B. I have a table which has an Identity column as a primary key. In my publisher ,for the table tb1,the the identity column,Id1 was ...
user9516827's user avatar
  • 1,315
1 vote
0 answers
117 views

Transactional Replication and distribution database size

I have a transactional replication set up with initialize from backup. In the secondary database server,i have a drive(d:) for data files alone. I have noticed that this drive is getting filled and i ...
user9516827's user avatar
  • 1,315
1 vote
0 answers
331 views

What are the requirements for SQL Replication on Azure SQL DB Managed Instance?

We are seeing if it is feasible to setup Azure SQL DB Managed Instance (SQL MI) and an Azure VM with SSMS installed for this replication scenario: We have a vendor who needs access to our SQL Server ...
cyberdog's user avatar
1 vote
0 answers
870 views

Resynchronize transactional replication in SQL Server 2012 without doing a complete reinitialization after disk filled

We have a DB that is doing a transactional replication to one other server. There are three tables it is replicating in the DB. Yesterday the subscriber ran out of disk space. This caused the ...
Kevin Vasko's user avatar
1 vote
0 answers
35 views

Heterogenous Database Replication

How can we establish a real time synchronization from SQL server to PostgreSQL Database? We have tried to automate the jobs in BI but that does not serve the purpose as we need real time data from ...
Atty's user avatar
  • 99
1 vote
0 answers
75 views

Configure Always On for publisher

We have a pull transaction replication.Publisher runs MSSQL 2016. Distributer runs on separate server with MSSQL 2016. Is it possible to define Always On over publisher and another new server, so ...
Liya Tansky's user avatar
1 vote
1 answer
137 views

Enable Replication/CDC from High Availability Group

I am a newbie to this Replication concept and I am trying to build some scripts to automate our Build Process where we will be disabling/re-enabling Replication on a regular basis. Background: We ...
Julaayi's user avatar
  • 111
1 vote
0 answers
96 views

Transactional Replication from Copy Only Backup

Is it possible to use a copy only backup when initializing a subscriber from a backup? I've been trying it but getting all kinds of issues with: Trying to update records that don't exist Inserting ...
user2200550's user avatar
1 vote
1 answer
72 views

is there a way to improve this query by not re calculating functions? cross apply?

when I set up replication I like to take a count of each table in the publisher, then I go to the subscriber I do the same thing in order to compare both pictures. this is the picture from the ...
Marcello Miorelli's user avatar
1 vote
1 answer
41 views

Publication shows up in secondary server

I have created log shipping and the primary server is also used for transactional replication. Now after creating log shipping I have noticed the publisher shows up on the secondary and it wont let ...
SQL_NoExpert's user avatar
  • 1,049
1 vote
0 answers
153 views

Schema changes in transactional replication

I have a publisher (SQL Server cluster) in one box and the distributor/subscriber together is on the other box. I have a requirement by the vendor that if there is a new build (schema/database ...
SQL_NoExpert's user avatar
  • 1,049
1 vote
0 answers
44 views

Replicate multiple on-site SQL Server databases to Azure

I have a SQL Server Express 2014 named instance containing 3 databases (One for each office). connected to a WinForms application. Each database is uniquely named after the respective office although ...
Emile Cloete's user avatar