Questions tagged [transactional-replication]
The automated distribution of transactional changes from a publishing database to one or more subscriber databases.
432
questions
0
votes
1answer
27 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
22 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
26 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
29 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
21 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
21 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
35 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
16 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
42 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
36 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
21 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
57 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
65 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
78 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
12 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
20 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
29 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
29 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
99 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
73 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
35 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
37 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
171 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
70 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
54 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
74 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
18 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
26 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 ...
1
vote
1answer
574 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 ...
0
votes
1answer
22 views
Can replicated commands be forced to follow each other in transactional replication?
We have transactional replication setup between an SQL Server 2012 box and a SQL Server 2016 box.
Every half an hour the source table articles are altered by DELETE then INSERT sequence. This ...
0
votes
0answers
10 views
How to make replication continue to work after repairing?
I would like to share with you a problem I faced today and will be glad if you can help me to tackle that issue. The issue related to transactional replication. So, let's go step-by-step to know how ...
0
votes
1answer
33 views
Questions around migrating to a live replicated database from an existing log shipping database in SQL Server 2016 SP1
I have two large databases, one on my production server named S1 and the other on my reporting server named S2.
S1 is the live database used for our customer orders and accounting, and S2 is the ...
0
votes
0answers
31 views
Forgot to add a table in Transactional Replication : Initialize with backup
I have a transactional replication(with initialize from backup) setup in my server.
So i setup replication as below:
Used full backup(publisher database full back is scheduled every night) from the ...
0
votes
1answer
50 views
Transactional replication Issue
I am getting this error when i try to create a new publicaton in dbserver-1 after reinstalling SQL Server in my database server 2.
Msg 18482, Level 14, State 1, Line 1
Could not connect to server '...
0
votes
0answers
11 views
SQL Server 2014 Replication Subscription Sync
I have SQL Server 2014 Replication set up.
It is my understanding that when you add a new subscription that it will truncate the table and re-insert/re-initialize the tables you are subscribing to.
...
0
votes
1answer
57 views
Difference between is_published and is_merge_published in Sql Server?
What is the difference between the columns is_published and is_merge_published in Sql Server ?
I can't find docs describing it !
SELECT [name],[is_published],[is_merge_published] FROM sys....
0
votes
1answer
122 views
The publication 'My_publication' doesn't exist?
We are trying to test the success of Transactional replications.
by Googling , i found that i need to execute in our publisher this stored procedure :
sp_publication_validation
However , When i ...
0
votes
0answers
17 views
Transactional replication restore missing data
We have configured transactional replication. Today for some reason replication started to lock users running reports. By running sp_who2 I saw that replication job is constantly doing update and ...
0
votes
2answers
121 views
Transaction Replication as DR solution
I have a production database server and a failover database server set up.
All the transactions from the primary production db server is being replicated(transactional replication with initialize ...
5
votes
2answers
287 views
DBCC SHRINKFILE on the distribution database
I have an .mdf file from distribution database which is growing.
I know the advantages and disadvantages of shrinking database/files.
Is it okay to shrink the .mdf file to some a smaller size.
Is ...
1
vote
0answers
19 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 ...
0
votes
1answer
53 views
-skipErrors in Transactional Replication not happening
I have set up a transactional replication initialized using backup for a big database(1.6TB).Due to some error(space issue in the subscriber) the replication was not happening and there were ...
0
votes
0answers
26 views
Not in sync even after changing the Agent profile : Transaction Replication
Getting the transaction deadlock error,25098 and 547 in my replication monitor.
I checked the subscribers and in one of subscribers ,i can see that ,the Distributor to Subscriber History shows error.
...
0
votes
1answer
43 views
Transactional Replication data during rebuild
To offload reporting I have transactional replication running between my prod box and my reporting server (top image #1). This works great until the snapshot has to be rebuilt. During this time users/...
0
votes
1answer
113 views
how to change the publication settings via T-SQL?
I set up a new server to host some database publications.
On the same server I set the distributor db.
All goes on well, then while running a snapshot of a publication on transactional replication:
...
0
votes
0answers
18 views
Log Shipping and Transactional Replication
Is it possible to have a Log Shipping Secondary database be a publication database for transactional replication?
In my research I discovered the the database for log shipping has to stay in no ...
0
votes
0answers
19 views
Transactional Replication - Source/Dest table with different structure
I need to perform Int/BigInt migration on one of our large tables.
The Identity column needs to be changed from int to BigInt.
It's a very large table, hence I wanted to copy over all the data and ...
1
vote
1answer
123 views
How to read/write from replicated slave in case master went down in SQL Server?
I've created a transactional publication in SQL Server, now my requirement is to write on master and read from subscriber and in case of master going down for some reason, the subscriber should act as ...
