Questions tagged [transactional-replication]
The automated distribution of transactional changes from a publishing database to one or more subscriber databases.
467
questions
1
vote
1answer
27 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 ...
0
votes
1answer
38 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 ...
0
votes
0answers
16 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 ...
1
vote
1answer
55 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 ...
0
votes
1answer
13 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 ...
0
votes
0answers
10 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 ...
2
votes
2answers
92 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 ...
0
votes
1answer
38 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 ...
1
vote
0answers
34 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,...
0
votes
2answers
67 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 ...
0
votes
1answer
94 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] ...
0
votes
0answers
31 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 ...
2
votes
0answers
97 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 ...
0
votes
1answer
36 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 ...
0
votes
0answers
98 views
Ideal CDC Latency for SQL Server Replication
I am seeking to understand what the ideal latency for an implementation of CDC in SQL Server Standard (2017) is.
The latency I am referring to can be found from the sys.dm_cdc_log_scan_sessions DMV ...
0
votes
0answers
92 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....
1
vote
1answer
85 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 ...
0
votes
0answers
35 views
sql publication / subscription 2008r2=>2012 Could not locate text information
I have replitation setup from sql 2008r2 publication to 2012 subscriber
it was working fine and after 4h i faced error:
Error messages:
The process could not execute 'sp_replcmds' on 'PB01\PROD'. (...
0
votes
1answer
22 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
0
votes
1answer
99 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 ...
0
votes
0answers
120 views
SQL Server Transactional Replication: Adding an existing column to an existing publication article
We are using Transactional replication and I've been fighting with it over the past few days trying to add an existing column to an existing article that we now need replicated to the subscriber.
I do ...
3
votes
2answers
317 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 ...
0
votes
0answers
75 views
Replication Monitor is not showing correct list of replications
I have one server, in which there are three instances of SQL Server 2019. In one instance, I had configured transactional replications, but all of those replications are currently dropped. But the ...
8
votes
1answer
507 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 ...
0
votes
1answer
42 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 ...
0
votes
1answer
91 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 ...
0
votes
1answer
252 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 ...
0
votes
1answer
73 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 ...
2
votes
1answer
295 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 ...
1
vote
1answer
2k 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 ...
0
votes
0answers
30 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 ...
0
votes
0answers
156 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, ...
0
votes
1answer
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-...
1
vote
1answer
50 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
1answer
26 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?
0
votes
1answer
27 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
0
votes
0answers
21 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 ...
0
votes
1answer
284 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 ...
0
votes
1answer
24 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?
0
votes
0answers
48 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 :...
1
vote
1answer
456 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 ...
0
votes
1answer
84 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 ...
0
votes
0answers
86 views
Transactional replication in sql server 2017 but job not running
I have created transactional replication and here is my script
Distribution script:
use master
exec sp_adddistributor @distributor = N'servername', @password = N''
GO
exec sp_adddistributiondb @...
0
votes
1answer
24 views
Adding articles to sql server
I am trying to add a few new articles to an existing sql server replication publication. The publication is for one way transactional replication. I have done this multiple times on multiple servers,...
3
votes
1answer
198 views
How to force subscriber to have foreign key WITH CHECK?
I know by default SQL Server replication adds WITH NOCHECK and NOT FOR REPLICATION to the foreign keys after transactional replication. But is there are way, where I can avoid these code changes made ...
0
votes
1answer
19 views
how to setup mongodb replica on cloud server?
I want to setup mongodb repicaset on my cloud server(AWS ec2) but I have only one IP address.
Currently I have make /etc/hosts two entries 127.0.0.1 dbnode1 and xx.xx.xx.xx dbnode2 but when I try to ...
1
vote
3answers
970 views
Sql Server replication: Initial snapshot for publication is not yet available?
I had setup transactional replication and my distributor, publisher are on same windows server and 2 subscriber one on same server and another on other windows server both using push snapshot.
I am ...
4
votes
4answers
1k views
Subscription Initialization throws Error "The option "INLINE=ON" is not valid for this function" which is incorrect
Scenario
We are upgrading our SQL estate to SQL Server 2019 and have setup what will be the production environment.
From:
SQL2008R2 Standard/Windows Server 2008R2 - Primary Server/Secondary Server (...
0
votes
1answer
99 views
Sql Server Replication - Modify Statement Delivery Stored Procedures before the replication starts
I want to create a subscriber (replica) where fields like Username, Email, and other personal customer info is masked (I'm creating a replica of production to help developers troubleshoot issues).
So ...
1
vote
1answer
80 views
Automatically add new dbo objects in SQL Publication
Is it possible to automatically add new dbo objects to SQL transactional replication? Is there some out of the box solution for that or that can be achieved by T-SQL?
Thanks
