Questions tagged [transactional-replication]

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

Filter by
Sorted by
Tagged with
10 votes
1 answer
1k views

Initializing Transactional Replication From Backup

There is an option when setting up a publication for replication to Allow Initialization From Backup. We have been creating replicated databases for several years now and have always initialized from ...
-1 votes
1 answer
24 views

Peer to Peer Replication in MS SQL Server

Is it good practice to use Peer to Peer Replication in MS SQL 2022 (Enterprise Edition) within Windows 2019 Server (three servers)? I want to create three replicas of my client's database and to use ...
0 votes
2 answers
201 views

SQL Server Replication - Only weeks worth of data

I have a need for a test server that hosts a small subset of data from our production systems. Is it possible to setup a SQL Server Replication Job that only keeps a week's worth of data so developers ...
0 votes
1 answer
20 views

logical replication initial phase

I am about doing replication for 16 millions record. Just want to ask about the initial phase of logical replication : Do I need to copy the initial table from publisher to subscriber first ? Or is ...
1 vote
1 answer
199 views

Group Policy - replication snapshot

Has anyone seen a domain Group Policy stop a transactional replication snapshot from being configured and executed "A required privilege is not held by the client". To cut a long story short... I am ...
1 vote
0 answers
15 views

Transactional replication deadlock during setup

I'm encountering a problem setting up transactional replication between SQL Server 2017 and an Azure SQL database on a high throughput system. When trying to add any of the bigger, higher traffic ...
1 vote
2 answers
99 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 ...
0 votes
1 answer
42 views

SQL server replication conflicts

Once per week, a snapshot publication is done from a database "myDB" in PROD to "myDB" in PREPROD. Now in PREPROD we set up a transactional replication between "myDB" to ...
4 votes
1 answer
1k 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 ...
6 votes
1 answer
224 views

SQL Server Replication

We had an issue with replication, where a large (7.5 million row) update was done on a 33 million row table. Replication converted that to 7.5 million individual update statements. When my alerting ...
3 votes
1 answer
261 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 ...
1 vote
1 answer
158 views

MS-SQL transactional replication - How to figure out how much data is being pushed per day in replicated databases

We use MS SQL Server transactional replication in our environment. I would like to know if there is a way or script to figure out how much data is being pushed per day to replicated databases? How to ...
2 votes
1 answer
37 views

Load of Transactional Replication at the Publisher

I would like to enable replication on my SQL Server , as per the Document , the load on Server as a Remote Distributor is less(i.e. 8-10% for insert/update in publisher) when compared with load on ...
2 votes
2 answers
149 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 ...
2 votes
1 answer
267 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 ...
2 votes
1 answer
298 views

Transactional Replication breaks when schema is changed in Publisher

We have set up Transactional replication where two Publishers feed into one Subscriber. Now if try to change the schema of any one Publisher or both publishers, replication breaks. We have tried to ...
2 votes
1 answer
4k views

SQL Server transactional replication is out of sync

I have setup transactional replication in SQL Server and I am using PULL to retrieve the data from the subscriber. Recently, I noticed that the data in my subscriber is not synced up with the ...
1 vote
1 answer
735 views

Unexpected growth Transaction log file 100 GB which is part of Transactional Replication

I configured transactional replication on SQL Server 2014/Windows Server 2012. The database size is only 30 MB, but log file size is 100 GB. Every day, the log file size is growing 5-7 GB. Database ...
1 vote
1 answer
503 views

Re-initializing a transactional replication wihch was initially synced from backup

I have a transactional replication which was initially synced from backup. Now I need to add a new table which is really big so we have decided to backup and restore a fresh copy of the db to ...
0 votes
0 answers
23 views

Has anyone implemented TDE on two node AlwaysOn Cluster with Replication?

Looking for instructions to implement TDE on AlwaysOn with Replication. Current set up is Failover Cluster with Replication. Any gotchas to add TDE and AlwaysOn combination with Replication?
1 vote
1 answer
310 views

Best way to transfer a few tables from reserve server to production

We have two MS SQL Servers (2012 Standard Edition on Windows 2012 Server) which are located in the same subnet. One is production server. Another is reserve server. Currently there is a ...
0 votes
1 answer
1k views

Drop a replicated subscription table that has been orphaned after restore migration

We restored a database on a new server for a migration. When attempting to set a subscription to a transnational publication, we get the error: Can't drop the table because it is set for ...
0 votes
2 answers
1k views

SQL Replication Automatically Add table to Publisher

I am using SQL 2012 transactional replication for an entire database. Everytime I add a new table, say Table A, I have to manually go to the SSMS GUI, and add the table to be publisher, then I resync ...
1 vote
1 answer
29 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 ...
1 vote
0 answers
36 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 ...
2 votes
1 answer
395 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 ...
0 votes
1 answer
3k views

Replications fails with a vague message: "The last step to run was step 3: Detect nonlogged agent shutdown"

In Transactional Replication of SQL Server, Synchronization job starts well and proceeds a while. However, in middle of the work it shows this error: The job failed. The Job was invoked by User WIN-...
1 vote
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 ...
0 votes
0 answers
20 views

Report (log) of each transaction on SQL Server Replication?

I've configured a transactional type of Replication between two SQL Servers. Since it's a project that it's still under review on my side I have only selected a couple of articles for the replication. ...
0 votes
1 answer
105 views

Replicate SQL Server 2008 across domains

I have been tasked with migrating a database, size being up to 15Gb, from a SQL Server 2008 database in a 3rd party cloud VM, to SQL Azure. On go-live day, when we switch from the old host, the Azure, ...
0 votes
1 answer
3k views

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

I am facing the error 20598 with Transactional Replication: The row was not found at the Subscriber when applying the replicated command. Normally this error occurs when an UPDATE or DELETE ...
1 vote
3 answers
4k views

Rows deleted in the subscriber - what to do? - SQL Server Transactional Replication

Someone has delete by mistake a row of a table in the subscribed server/database. I am getting the following error message: The row was not found at the Subscriber when applying the replicated (...
5 votes
1 answer
836 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 ...
0 votes
1 answer
186 views

pglogical library

I would like to try pglogical to set up an archive server from a live database, filtering with INSERT, UPDATE statements (DELETE statements will not be replicated in the archive server). I follow the ...
0 votes
1 answer
38 views

Replication jobs are not getting created after failover

I added distribution database to the alwaysOn group of dist1 and dist2 server. I setup replication for the database which are part of AAG and act as a publisher( node 1 and node2) and subscriber ...
1 vote
1 answer
5k 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 ...
1 vote
1 answer
98 views

SQL Server replication msRepl_commands msRepls_transactions tables growing

I'm not getting any clue how to safely clean up the above tables. Last Friday, these tables had 22m and 6m records, respectively. Over the weekend, the number of rows has been reduced to 10m and 2.5m. ...
7 votes
1 answer
4k views

Replicated Database Log File Maintenance

I have a database on the publisher that is involved in replication (publication configured for merge and transaction). Trying to regain control of the log file for this particular database (VLF count, ...
1 vote
1 answer
118 views

How to modify the Replication Subscription Job/script to make it point an IP Address and a Port number?

I have an SQL Server 2012 server set up replicating one Database. It is behind a firewall. I have poked a hole in the firewall so I can access the server on Port 1433. I am using an IP Address and a ...
2 votes
1 answer
126 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 ...
0 votes
0 answers
44 views

MSreplication_subscriptions updated tens of thousands of times per sync

I have a server that is a subscriber to several transactional replications. When looking into Activity Monitor in SSMS on the subscriber, I frequently see entries like this in Recent Expensive Queries:...
5 votes
2 answers
6k views

Recommended distributor architecture for transactional replication?

We are planning to create a Transactional replication on 2 nodes of SQL Server 2008 R2, each node will be a publisher and a subscriber. What is the best practice in order to have this scenario done? ...
1 vote
1 answer
35 views

Transactional Replication Architecture - One Article - Multiple Subscribers

Q: Is less expensive/more efficient for a Distributor server to use 1 publication to 2 subscribers or to use 2 publications with the same articles going to a single subscriber each? Details: We have ...
0 votes
1 answer
148 views

High Availability with Transactional Replication

We are using SQL 2017 Enterprise and setting up a new DR site. One of our servers (Server B) is used for reporting and has many databases, one of which has tables populated through Transactional ...
1 vote
0 answers
33 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, ...
0 votes
1 answer
124 views

Transactional replication upgrade from SQL Server 2017

We would like to inplace upgrade of transactional replication topology from SQL Server 2017 to SQL2019 with minimal downtime. The following is the setup. All the all current instances are on SQL ...
1 vote
1 answer
97 views

Can you backup a replicated database?

I would like to backup a publication SQL Server database that is being replicated with transactional replication. Would it cause any issues (with replication or any other issue) if I took a backup of ...
0 votes
1 answer
112 views

Replication SQL Server to a Cluster with error

I have a source DB MSSQL Server and deploy transactional-replication to a cluster with 2 Node(A&B). The subscriber was added using SQL IP of Cluster and using "sa" account. It run fine when ...
3 votes
2 answers
2k views

Cleaning up transaction replication del/ins/upd stored procedures?

I'm referring to the ones that start with: dbo.sp_MSdel... dbo.sp_MSins... dbo.sp_MSupd... I've noticed they are not removed when I run exec sp_dropsubscription on the Publisher then run ...
0 votes
1 answer
145 views

Deleting old snapshots SQL

This may be a silly or dumb question but... We just changed the snapshot path and I was wondering if the cleanup job also clears up the old snapshots from the previous folder path or is it something ...

1
2 3 4 5
10