Questions tagged [transactional-replication]
The automated distribution of transactional changes from a publishing database to one or more subscriber databases.
419
questions
0
votes
1answer
16 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
22 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
40 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
40 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
22 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
34 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
77 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
55 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
51 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
18 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
25 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
55 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
31 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
29 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
25 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
22 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
95 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
13 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
45 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
127 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
15 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
33 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
25 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
35 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
45 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
15 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
16 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
63 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 ...
2
votes
1answer
85 views
Where is located sp_msreplcheck_publish stored procedure?
sp_msreplcheck_publish is a sp that checks if you have sufficient rights to run a statement to alter a published table (transactional replication).
Trying to alter a published table without elevated ...
1
vote
1answer
89 views
Only SQL Server Enterprise Edition supports compression - how to work around that?
I have a script that needs to create a temp table and in that temp table I create an index.
I noticed in some of my servers, the script would go on forever, and when checking from a different session ...
0
votes
1answer
95 views
Snapshot replication vs. Transactional replication
I have come across the following scenario in a practice test:
Data from DB1 is accessed periodically by an external application.
Data from DB1 will be sent to another DB, DB2, as soon as changes ...
1
vote
1answer
155 views
Replicaton - The current transaction cannot be committed and cannot be rolled back to a savepoint
After creating a replication publication, I go ahead and create a subscription using the script below:
-----------------BEGIN: Script to be run at Publisher 'MY_PUBLISHER_SERVER'--------------
use [...
0
votes
1answer
27 views
Are Snapshot files created during MS SQL replication one time use only?
I added a new article in an existing publication.
I saw that it generated a new snapshot folder (E:\Database\Snapshots\unc\S749TCAPDB01_NFS_XXXX_XXXX_PUBLICATION\20190417114556).
But this folder ...
0
votes
0answers
27 views
Synchronizing for reporting, SSIS job vs Transactional replication or other options?
We're faced with a fun challenge. A server has 60+ databases to segment data due to privacy and security. We have 7 tables in each of these databases that need to be merged into a master dataset for ...
0
votes
1answer
44 views
Will a Transactional Replication break the LSN chain in my backups
I need to create a fairly up to date read only copy of parts of a production database for reporting purposes.
I did look at log shipping to keep the copy up to date but this will break the LSN and ...
0
votes
0answers
74 views
Transactional replication from Oracle PL/SQL to MS SQL Server broke from the data in a Date column but previous records with same date are ok?
I have a heterogenous transactional replication subscription setup between PL/SQL (12C 12.1.0.1.0) and SQL Server (2008 R2), where the Oracle PL/SQL server is the publisher and the MS SQL server is ...
3
votes
1answer
128 views
how to prevent replication to stop if there is no activity for some time?
On one of my transaction replication publications, when there is no any activity, after some time, the replication stops.
Where can I see these replication settings?
what to put in place to prevent ...
0
votes
0answers
49 views
Can MSSQL CDC be used to capture old committed transactions?
I can successfully capture change data from mssql CDC by issuing the query
cdc.fn_cdc_get_all_changes_capture_instance ( from_lsn , to_lsn ,
'<row_filter_option>' )
Here, from_lsn is the ...
0
votes
0answers
70 views
Automate Replication Scripts
In SSMS, one can "Generate Scripts..." to Create or Drop Publications and Subscriptions and Distributor configurations. Is there any way to do this automatically?
We have several people on the DBA ...
2
votes
0answers
57 views
Does Azure SQL natively support being the subscriber in transactional replication where Oracle is the publisher?…and if PostgreSQL is the publisher?
Is there a native way to setup transactional replication where an Azure SQL database is the subscriber and the publisher database is in Oracle?
What if the publisher database is in PostgreSQL?
If ...
0
votes
1answer
46 views
Can an AlwaysOn Availability Group be used to mitigate downtime when there's replication syncing issues on the primary server?
Once in a while I'll run into syncing issues with my subscriber server in a transactional replication setup.
Taking a new snapshot of the publisher causes significant downtime on my subscriber server.
...
1
vote
2answers
138 views
Can Transactional Replication and triggers be used for near-realtime ETL [closed]
We want to have a data warehouse synced with our production OLTP database in near realtime. Is it possible to do this with Transactional Replication and triggers?
How we currently ETL
We use sql ...
0
votes
1answer
45 views
SQL Server Transactional Replication from 2005 to 2017
As per the msdn, Subscriber version depends on the type of publication: A Subscriber to a transactional publication can be any version within two versions (n-2) of the Publisher version. For example: ...
0
votes
1answer
31 views
Replication SQL Server 2016
Good afternoon,
I am trying to perform a transactional replication in sql server 2016, and I have the following problem.
The scenario is as follows:
A publisher server and a subscriber server, the ...
1
vote
1answer
194 views
Set up new publication on SQL Server 2014 database which is part of an availability group
I like to setup a publication (transactional replication) on a SQL Server 2014 database which is part of an availability group. We have a primary and one secondary (non-readable) replica.
On the ...
