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
0 votes
1 answer
13 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 ...
user avatar
  • 95
1 vote
1 answer
33 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. ...
user avatar
0 votes
0 answers
23 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:...
user avatar
  • 125
1 vote
1 answer
52 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 ...
user avatar
  • 73
1 vote
1 answer
29 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 ...
user avatar
1 vote
0 answers
27 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, ...
user avatar
  • 424
1 vote
1 answer
60 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 ...
user avatar
  • 253
0 votes
1 answer
61 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 ...
user avatar
0 votes
0 answers
29 views

Why can I not manually run more than one snapshot agent at a time

My organization has a SQL Server 2016 instance with multiple databases, and multiple publications/subscriptions for each database, in a transactional replication set-up. Occasionally, we need to ...
user avatar
  • 1
0 votes
0 answers
54 views

Error when replicating to Azure SQL Database

I've configured transactional replication between my on-prem SQL 2017 instance and an Azure SQL Database. The publisher, distributor, subscriber have all been set up. The snapshot agent has generated ...
user avatar
1 vote
1 answer
143 views

Is there anyway in sql server transactional replication to not drop the target tables?

I have transactional replication setup between Server1 database and Server2 database on few tables. Now the problem is when I start the replication for the first time, it drops the table on target ...
user avatar
0 votes
1 answer
35 views

Consolidating data from Subscriber database at Publisher

We have some tables published from one SQL server and another server subscribed to it. However we just realised on two tables data was added at the subscriber end that doesn't exist at the publisher. ...
user avatar
  • 101
1 vote
1 answer
90 views

Calling triggers on replicated table when reinitalising from snapshot

I noticed my triggers weren't being fired after a subscriber was reinitialised. Is there a configuration to allow this functionality. Currently my setting for 'Action if name is in use' is 'Delete ...
user avatar
0 votes
1 answer
42 views

Performing operation on transactional insert

Using MS SQL Server to replicate data between 2 servers for security purposes. Is it possible to perform an operation on the data sent from the publisher database before it is inserted into the ...
user avatar
0 votes
0 answers
19 views

How to configure transactional replication with availability groups

I'm looking to implement transactional replication with AOAGs. Has anyone implemented it? If so, have the steps been documented? Otherwise, can anyone direct me to documentation that supports such a ...
user avatar
  • 1
1 vote
1 answer
102 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 ...
user avatar
  • 13
0 votes
1 answer
26 views

Sql server transactional replication latency

I'm analyzing the distribution database to see if there any latency in the replication between publisher and subscriber The architecture is one publisher, 1 distrubution db (on publisher server) and ...
user avatar
  • 101
0 votes
0 answers
12 views

Local SQL server replication to AWS RDS

I have a local licensed SQL server, I need to have an online replica for one of the local databases I have been reading and checking Amazon Web Service (AWS). I tried to do SQL Server Replication ...
user avatar
2 votes
2 answers
247 views

How do I resolve lock issue when replicating a table with over 16Billion records

I have two SQL Server databases that I am trying to replicate. I am able to replicate small tables without any issues. However I have a table with over 16 Billion rows that is quite problematic. I am ...
user avatar
  • 31
0 votes
1 answer
94 views

Is there a way to setup one Alert for all Replication errors?

I have a SQL Server 2016 (Standard) instance, with DatabaseA, who is the Publisher in a Transactional Replication (push subscription) topology. A second SQL Server 2016 (Standard) instance, with ...
user avatar
  • 24.2k
1 vote
0 answers
98 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,...
user avatar
  • 11
0 votes
2 answers
79 views

Solution for syncing a database between two server and secondary can be used for all DML operation

I need a solution to synchronize a database (SQL Server 2016 Enterprise) between two servers, and I would be able to connect my secondary application to the secondary database which is getting ...
user avatar
  • 357
0 votes
1 answer
239 views

MSSQL transaction replication - How to resync the data?

I have 1 transaction replication (peer to peer topology). After I setup, I found that the data on 2 table was difference. The first peer table [VPMYN204].[AdventureWorks2019].[Person].[AddressType] ...
user avatar
  • 133
0 votes
0 answers
81 views

Delete data from big replicated table

I have a table with more than million rows. I need to copy data to temporary table, make changes to table structure and copy back all data. This table is on transactional replication. And I wanted to ...
user avatar
2 votes
0 answers
175 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 ...
user avatar
0 votes
1 answer
65 views

One-directional Transactional Replication is causing one table's Clustered Index to be copied over as a Nonclustered Index in the subscriber database?

This seems really odd to me, as I've used SQL Server's replication feature for a while now and have never seen this occur before. I'm using One-Directional Transactional Replication and I have ServerA ...
user avatar
  • 24.2k
0 votes
0 answers
149 views

How to avoid Timeout Error while turning on DB Replication

I recently tried to replicate a database with only one table in it using transactional replication, using GUI of SQL server management studio. But, as soon, the publication is set up, I get this error....
user avatar
  • 165
1 vote
1 answer
209 views

One-directional database replication without delete on target database?

I have two databases: A small one where data is permanently added and a big one where the data is copied from the small one and is supposed to be stored long term. The small database deletes old data ...
user avatar
0 votes
1 answer
32 views

transactional replication-snapshot agent failure ... can anyone help me out

configured transactional replication ..source acting as publisher and distributor,target server acting as subscriber.. but I got error for snapshot agent at 93 % saying that in below screenshot
user avatar
  • 1
0 votes
1 answer
150 views

Transactional Replication between SQL Server 2017(Publisher+Distributor) and SQL Server 2019(Subscriber) [closed]

So here's the replication scenario that we need to set up. At organization A, We have SQL Server 2017 which is both the publisher and a distributor. At organization B, on a remote server, we have a ...
user avatar
3 votes
2 answers
461 views

Pre- and Post- Snapshot Replication Scripts Not Executing and No Errors

I have a Snapshot Replication Publication setup on Server 1. Server 2 subscribes to the publication on Server 1. Data is replicating successfully. The Distributor Agent and Agent job for the ...
user avatar
  • 24.2k
8 votes
1 answer
643 views

Changing collation of SQL Database in Production environment to minimize downtime

We have a scenario in which we want to change the collation of our production database (including columns) from SQL_Scandinavian_Pref_CP850_CI_AS to Finnish_Swedish_CI_AS. We have developed scripts to ...
user avatar
0 votes
1 answer
103 views

Replication of only updates and inserts

I have two sql servers, in the first I want to keep the data from the last three months and in the second I want to keep all the data with the purpose of getting historical reports. I know how to do a ...
user avatar
  • 7
0 votes
1 answer
113 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 ...
user avatar
  • 73
0 votes
1 answer
316 views

sql transactional replication - slow renitialization / slow bulk insert

i recently set up sql transnational replication and i have some performance issue when i'm initializing/re-initializing it from snapshot. The biggest table have is about 8gb as snapshot file network ...
user avatar
  • 101
0 votes
1 answer
102 views

SQL Server 2016 -number of SQL agent replication jobs(transaction log repl)

So I have a SQL server 2016 on a Windows Server 2012 instance. We have 140 databases(small to med). So the SQL instance was setup for self replication and in the process we have 140 SQL agent ...
user avatar
2 votes
1 answer
433 views

Replication error masked by sp_MSdetect_nonlogged_shutdown returning 'String or binary data would be truncated'

One of our replication jobs is failing on step 3, which runs sp_MSdetect_nonlogged_shutdown, returns a 'String or binary data would be truncated'. Which of course led us down the path of trying to ...
user avatar
1 vote
1 answer
4k 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 ...
user avatar
  • 111
0 votes
0 answers
40 views

SQL Replication Subscriber with AlwaysON setup

We have 2 AlwaysON cluster and we want that to set up as a SQL Replication server which is the Always-on Cluster 1 is the Publisher and Always-on Cluster 2 is the SUBSCRIBER, my question is it ...
user avatar
0 votes
0 answers
195 views

Why am I unable to skip a transaction in SQL Server 2017 transactional replication using the sp_setsubscriptionxactseqno stored procedure?

I have a transaction number of a transaction that is throwing the following error: Command attempted: if @@trancount > 0 rollback tran (Transaction sequence number: 0x00036322000B2628000100000000, ...
user avatar
0 votes
1 answer
2k 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-...
user avatar
  • 151
1 vote
1 answer
66 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 ...
user avatar
  • 143
0 votes
1 answer
33 views

SQL Sever Transactional Replication on DB with deadlocks and blocking issues

Is it feasible to try and start Transactional Replication on a SQL Server DB with deadlocks and blocking issues. Are there any other options for a close to hot backup?
user avatar
0 votes
1 answer
28 views

Replication types

How can I find out, if my current replication pattern is any of following or not? I am using SQL2008R2. 1)Peer to peer replication 2)Bidirectional transactional replication
user avatar
  • 21
0 votes
0 answers
22 views

database replication or mirroring

I have a database server in HQ, while in branch I need user able to get the data from database server to perform daily task. The problem is the connectivity is not that good. I wish to have another ...
user avatar
0 votes
1 answer
415 views

How to Migrate complete Transactional Replication to other servers without Snapshot?

We have large databases that are being kept in sync using Transactional Replication. They are all running in VMs and we are going to move to a new provider and reorganize the network structure and ...
user avatar
  • 345
0 votes
1 answer
31 views

SQL Server Script to replicate recent inserted data to subscriber and to remove same data from publisher [closed]

I want to replicate newly added data from publisher DB to subscriber DB. When newly added data is replicated in subscriber dB the same data will be removed from publisher DB. How to do this?
user avatar
0 votes
0 answers
50 views

To automatically stop/disable transactional replication whenever table get altered

I have set up a transactional replication from multiple Databases (6 Different Server) to 1 consolidated database. The schema is identical. Segregation is done by the column SiteName . My problem is :...
user avatar
1 vote
1 answer
714 views

Unable to replicate schema changes in transactional replication

I have implemented a transactional replication on one of my dbs. In the documents provided by Microsoftsql (here and here), I should be able to change the schema and that these changes will be ...
user avatar
  • 165
0 votes
1 answer
124 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 ...
user avatar

1
2 3 4 5
10