Questions tagged [transactional-replication]
The automated distribution of transactional changes from a publishing database to one or more subscriber databases.
406
questions
0
votes
0answers
9 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
27 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 ...
-3
votes
0answers
16 views
migrate Prodection DB To New Datacenter without downtime [duplicate]
I need to migrate our centralized Production databases from the current datacenter (SQL SERVER 2012 alwayson) to the new Datacenter without downtime , what is the best practices for this migration ? I ...
0
votes
0answers
25 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
18 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
9 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
18 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
89 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
33 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
103 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
14 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
29 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
24 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
32 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
26 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
13 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
14 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
51 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
64 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
71 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
54 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
91 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
19 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
25 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
40 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
65 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
123 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
42 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
56 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
54 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
39 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.
...
0
votes
2answers
86 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
38 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
29 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
146 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 ...
2
votes
1answer
52 views
SQL Server 2014 AOAG + Replication?
I have an existing SQL Server (2014) setup using Always On Availability Groups. All the documentation says I should not try to install replication (aka distributor role) on any of the servers ...
0
votes
1answer
53 views
How to check when a record is replicated in SQL Server
I have configured a database installed On-Premise to replicate certain tables on a Cloud SQL Server database hosted on Azure.
The replication task is scheduled to run at specific time everyday and I ...
0
votes
0answers
22 views
SQL Server replication model snapshot, transactional and merge - which method can be used
I am trying to implement sql server database replication between 2 branch servers to a Head Office Server.
My application is a distributed one, the main application is hosted on head office which is ...
0
votes
1answer
48 views
Transaction Replication on Azure MI servers
I'm struggling to verify whether an Azure Managed Instance of SQL Server can both subscribe to Transactional Replication, and Publish, at the same time - I've been googling like a demon and ...
0
votes
2answers
176 views
How to pause the subscriber end of transactional replication
How do I go about pausing transactional replication on the subscriber end.
We have a publisher on Server A, a distributor on Server B and a subscriber on Server C. All are in the same network.
Some ...
0
votes
1answer
93 views
Upgrade SQL Server 2008 R2 to SQL Server 2012 and then set up data replication to SQL Server 2016
Currently I have a Windows 2008 server with SQL Server 2008 R2 and I want set up a data replication to a Windows 2016 Server with SQL Server 2016.
I am aware that the aforementioned is not possible ...
0
votes
0answers
65 views
SQL Server Replication Error - The process could not connect to Subscriber
I have setup transactional replication by following link. Everything I did as per document. But when I verify synchronization status, as per mentioned link, I receive an error
The process could ...
0
votes
1answer
34 views
How to handle set up real time DML and DDL statements sync between two DB servers SQL Servers
We are looking to offload the data from a Production enviroment to a reporting environment. I currently have an SSIS package setup to extract and load data between the two servers.
However, now ...
0
votes
1answer
33 views
Replication stalling on one subscriber
I currently have transactional replication set up for three databases from the same Publisher to the same three Subscribers in SQL Server 2005. Publisher and Distributor are on the same server.
One ...
0
votes
1answer
76 views
is this time to remove the databases from the availability group? which ones?
While patching one of my servers that is part of an availability group,
as a secondary, I had some problems - the real problem was that the host machine of one of the virtual servers in the ...
1
vote
0answers
113 views
What are the requirements for SQL Replication on Azure SQL DB Managed Instance?
We are seeing if it is feasible to setup Azure SQL DB Managed Instance (SQL MI) and an Azure VM with SSMS installed for this replication scenario:
We have a vendor who needs access to our SQL Server ...
1
vote
0answers
108 views
Resynchronize transactional replication in SQL Server 2012 without doing a complete reinitialization after disk filled
We have a DB that is doing a transactional replication to one other server. There are three tables it is replicating in the DB. Yesterday the subscriber ran out of disk space. This caused the ...
1
vote
0answers
23 views
Heterogenous Database Replication
How can we establish a real time synchronization from SQL server to PostgreSQL Database? We have tried to automate the jobs in BI but that does not serve the purpose as we need real time data from ...
1
vote
1answer
95 views
How to mark a table as pre-populated with postgresql logical replication
I accidentally loaded data into a table before setting up a logical replication subscription for it and now I'm getting errors when I try to sync it (duplicate key already exists).
I can't delete ...
