Questions tagged [transactional-replication]
The automated distribution of transactional changes from a publishing database to one or more subscriber databases.
447
questions
1
vote
1answer
37 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
14 views
Transactional replication setup on non default port not working [closed]
Transactional replication setup on non default port not working - when adding publication, the underlying name being used is still the one without the port no due to which it is not being able to add ...
0
votes
1answer
30 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
24 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
0answers
13 views
SQL Server Bi-direction replication with different schemas
I have a requirement when upgrade legacy system, the legacy database schema is quite different from the new database schema, we need to change user back and forth to use the system since the new one ...
0
votes
1answer
17 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
71 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
9 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
82 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 ...
3
votes
1answer
112 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
0answers
27 views
Adding article to transactional replication causing blocking - schema mod lock
We have a transaction replication initialized from backup on a highly transactional OLTP database. When we add new articles(either from SSMS or using T-SQL) it causes massive blocking as the adding ...
0
votes
1answer
21 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
29 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
1
vote
1answer
55 views
Replication fails with “Transaction log for database is full due to LOG_BACKUP”
Replication is failing with the subject error.
The log_reuse_wait_desc states LOG_BACKUP.
I have performed both database and log backups.
After changing the recovery mode to single, performing ...
0
votes
1answer
52 views
Exception when starting Snapshot Agent when setting up database replication
I am trying to set up replication between databases. I followed these tutorials:
Tutorial: Prepare SQL Server for replication (publisher, distributor, subscriber)
and after:
Tutorial: Configure ...
0
votes
0answers
15 views
One or more subscriptions have been marked inactive. Drop and re-create all subscriptions for this node that are failing with this error [duplicate]
We have a production server error causing replication agent got failed and subscription became inactive. When I tried to restart synchronizing process, it is getting failed.
Should I delete/drop the ...
1
vote
1answer
31 views
Can I add new tables to a transactional replication subscriber
I'm looking into using transactional replication in MS SQL Server 2016 to create a reporting database from our production HR system.
I'm wondering if it's possible to create new stored procedures and ...
0
votes
0answers
52 views
SQL Server Replication with Temporal Tables
After a bit of advise. I have an existing database that extensively uses Temporal tables (i.e. System versioned tables). We are looking at an environment where we have a near to real time replica of ...
0
votes
1answer
41 views
Replication with Always On availability groups
I have a three server set up:
SQL01 – Primary server
SQL02 – Secondary server
SQL04 – Remote distribution server
SQL04 – Subscribe
SQL01 / SQL02 DB's are part of basic availability groups and i am ...
0
votes
0answers
15 views
Changing stored procedure in Transactional replication in order to avoid restoring from snapshot
As the title suggest, I have a transactional replication set up on a database of about 4 TB in size. This is an old replication that was set up a couple of years ago and what I can see is that the ...
0
votes
1answer
82 views
SQL Server Automate refresh of one table only from one environment to another
I'm looking for a best practice/option to refresh one table only from Prod to Staging environment.
Technical Details:
The table in Source is loading daily with new data.
The table in destination ...
0
votes
0answers
31 views
SQL Server: Replicating a Table to a Subscriber back to the Publisher
We have a Transactional Replication that replicates all tables to a subscriber. Without going into detail, we've been facing performance issues on the Publisher database whenever there are ...
0
votes
1answer
29 views
Migrate servers involved in replication
We are planning to migrate two servers with MSSQL 2012 servers with configured transactional replication. New servers will have new names, ip addresses and MSSQL 2014. Subscriber is used for reporting ...
0
votes
0answers
89 views
How to configure transactional replication for managed instance as a publisher and a subscriber for two different sql server at the same time?
The scenario is such that a subscriber is subscribed to transaction replication based publisher (distributor running on publisher).
I then want this subscriber to be a publisher to another subscriber....
0
votes
0answers
29 views
What happens to the transactional replication if azure sql Vm(publisher and distributor) is stopped and restarted from portal?
I am a newbie...I have azure sql vm and sql managed instance. I have setup transactional replication between them. Now if I stop the vm from the portal will the replication still continue after ...
0
votes
0answers
22 views
T-Replication Periodically Falls Behind
We have two SQL Server 2016 boxes. One uses transaction replication to the other. There is also a distribution server. The one subscriber has been periodically falling behind in replication and the ...
0
votes
1answer
37 views
Subscriber Node down in transactional replication
1) I have 2 node which node 1 is publisher and distributor in same server and subscriber in node 2. In case publisher is down for few hours and i need to bring up the subscriber for read and write by ...
0
votes
0answers
18 views
Log Reader Agent is not starting
I have setup transactional replication on SQL SERVER 2014. It was working fine yesterday. Today, I am seeing below error. How to investigate what is the problem. Why log reader agent is not starting ...
1
vote
0answers
47 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 ...
0
votes
0answers
40 views
Transactional Replication with 2 subscribers
I have an existing subscriber and want to setup another subscriber but on different machine.So 2 subscribers, 1 publisher
I want to initiate it from a backup rather than snapshot but when i try set ...
0
votes
0answers
18 views
what is the difference between replication options proc exec and definition only?
I understand proc exec option as for example when you run a procedure which inserts 1000 records into table, those insert afterwards will be replicated to the subscriber. When it comes to definition ...
0
votes
0answers
23 views
Is subscription database considered as read-only?
I am not sure about that because while users implement SELECT from subscription database, publisher replicates data to the subscription server meaning that INSERT and UPDATE also take place (which are ...
2
votes
1answer
65 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
1answer
92 views
Why does sp_replmonitorhelpsubscription not work without parameters?
I am just starting to play around with all the cool replication tools to Programmatically Monitor Replication
One of the early finds is sp_replmonitorhelpsubscription
When I run it as
...
3
votes
0answers
103 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 ...
0
votes
0answers
16 views
Indexing with transactional replication snapshot
Is there a way to set up a database table structure and indexes on a replication subscriber prior to running the snapshot agent in order to skip having to add the indexes AFTER the snapshot has been ...
0
votes
1answer
23 views
Transaction publication with merge publication
I have a transaction publication setup on SQL Server 2014 lets call it (node A) that replicates to SQL Server 2008 (node B), then I use merge replication to synchronize changes from (A) through (B) to ...
0
votes
1answer
47 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 ...
0
votes
0answers
30 views
Transaction Replication - re-initialization the subscription after changing an article's structure
I'm using SQL Server 2014 SP3CU2 Enterprise Edition.
I have a transaction replication on a DB with some very large tables.
I needed to change the structure of a column on one of the tables that were ...
1
vote
2answers
169 views
Migration to SQL Database using Transaction Replication - down time?
I am looking to migrate two on-prem SQL Servers into Azure Hyperscale. Both 28 cores, 1.5 TB of RAM with 10TB data disks.
I can NOT afford any downtime during the migration and have heard transaction ...
0
votes
0answers
12 views
Segregated databases transactions consolidation into one database
I have regions and every region has 5 locations. currently we have an application which is pushing and pulling data on the logged in user branch on which user has permission. Every location is ...
0
votes
2answers
96 views
Memory issue in Transactional Replication
I want to replicate a data of 500 GB through transactional replication once in a day, so my query is this, will it create a new file in the snapshot folder on daily basis of 200-300 GB or will update ...
0
votes
0answers
37 views
I'm using Transaction Replication and some commands are deadlocking on the subscriber, do I need to re-initialize replication?
I'm trying to understand the nuts and bolts of transactional replication and I have a few questions:
1) If a subscriber command fails to replicate, where is this logged?
2) How am I notified if a ...
2
votes
0answers
38 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 ...
1
vote
2answers
261 views
Do long-running and locking queries on subscriber databases cause performance issues on the publisher dbs?
One of our servers has a subscriber database. I've not used replication before and cannot find information on how the mechanism works.
Does the publisher have to wait on the replication completing ...
2
votes
2answers
95 views
Failover strategy for SQL Server 2016 Standard Edition
I am using SQL Server 2016 Standard Edition.
I have two database servers, SQL01 and SQL02 with only one database, and I am utilizing transactional replication for bringing data from SQL01 to SQL02.
...
0
votes
1answer
57 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
0answers
216 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 ...
0
votes
0answers
20 views
Replication On Different Number of Columns
I have two schema with the same table name:
Example:schema_a.dbo.item having column 'a','b','c','d' where 'a' is primary key
and schema_b.dbo.item having column 'a','b','c','d','e','f','g' where 'a' ...
0
votes
0answers
29 views
Subscription data got deleted from distributor
Looking for some help. Here’s our replication scenario –
We have a publisher servers P105 & P106, as publishers with always on.
We have always-on distributor on servers Q103 and Q104.
And we ...
