Questions tagged [transactional-replication]

The automated distribution of transactional changes from a publishing database to one or more subscriber databases.

Filter by
Sorted by
Tagged with
13
votes
3answers
20k views

Replication Monitor Information using T-SQL

The image below shows a transaction replication problem that I am currently investigating. The image comes from the Replication Monitor. How can I get this information using T-SQL?
4
votes
1answer
9k views

Transactional replication altering tables and adding stored procedures

I have a database that is replicated in several locations. It is setup as transactional replication. However, now I need to add more columns to the table. Add more stored procedures and etc. Hence, if ...
11
votes
6answers
65k views

How to fix a messed up replication on MS SQL Server

I restored a database from a backup. The database uses replication to publish to a different server. Assuming the database restore would break the replication, I tried to delete the replication and re-...
0
votes
1answer
287 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 ...
5
votes
1answer
6k views

Transactional Replication to DB on Same Server instance

We have a SQL Server 2012 instance that contains a database with data for many product lines. We are looking for a way to take subsets of this data into another database on the same instance. This ...
8
votes
2answers
2k views

SQL Server : Transactional Replication Computed Column

I am a subscriber to a transactional replication subscription. I have no control over the publisher. I have read the following article about schema changes to the publication database: Make Schema ...
7
votes
2answers
5k views

Transactional replication latency issue

I inherited database systems like this. Currently I have publisher database in SQL Server 2005 compatibility mode on Windows Server 2008 R2 with SQL Server 2008R2 SP2 machine. Distributor is on the ...
1
vote
4answers
10k views

How to restart the distributor agent of transactional replication?

After troubleshooting query time out error I would like to restart my distributor agent. So I go to my distributor server, in my distributor DB (called distribution_SA) I run the following query: ...
7
votes
1answer
3k views

Replicated Database Log File Maintenance

I have a database on the publisher that is involved in replication (publication configured for merge and transaction). Trying to regain control of the log file for this particular database (VLF count, ...
5
votes
1answer
1k views

Move transactional replication subscriber to another server without re-snapshotting

I have publisher and subscriber at same server. I need to move subscriber's DB to another server but without creating new snapshot. I've tried this steps: Stop all agents Backup subscriber's DB ...
3
votes
1answer
2k views

How to PUSH data between a Local DB Server to Cloud DB Server?

I have an implementation doubt regarding replicating data between SQL Server 2014 database servers (Replicating data from Local Premises SQL Server to Cloud DB Server, which are not in same domain or ...
1
vote
2answers
4k views

is it possible to alter the primary key of a table (from non clustered to clustered) in the subscriber only and not break the replication?

I have a table that has an clustered unique index and a non clustered primary key with the same structure as the index. IF OBJECT_ID('[dbo].[tblBAccountHolder]') IS NOT NULL DROP TABLE [dbo].[...
0
votes
1answer
477 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: ...
49
votes
4answers
35k views

Clustering vs. transactional replication vs. availability groups

Assuming you need to make sure your application that relies on SQL Server 2012 as its database backend is available around the clock, even if one server machine fails. As a developer and not a DBA, I ...
2
votes
2answers
14k views

The Distributor has not been installed correctly. Could not enable database for publishing

while in the process of creating a publication in one of my servers I continuously get the following error message The Distributor has not been installed correctly. Could not enable database for ...
5
votes
2answers
8k views

Snapshot for transactional replication taking too long for large database

I want to establish a transaction replication of a large database (200GB). But when I create the publisher and the snapshot agent starts, it takes very very long time and it sticks on the following ...
4
votes
1answer
1k views

How to set ALL the replication to DEADLOCK_PRIORITY LOW?

I have just got this email here: Then looking at the sql server log I find this deadlock was caused by one of our procedures (that was the victim) against the replication. O.K. in this case in ...
4
votes
5answers
10k views

SQL Agent still attempting replication for deleted publication

We recently retired a replication publication, deleting each subscription (push subscriptions) and then deleting the publication, all from within SSMS. However, we continue to get the following ...
3
votes
1answer
1k views

Availability Groups and Transactional Replication

My client has a setup as shown below. AG Group A ---------> Server A (DB--> A) ----> Server B ( DB--> A) Transactional Rep --> Server A ( DB-->A ) ---> Server C ( DB--...
2
votes
1answer
852 views

SQL Server transactional replication distributor

If I have a SQL Server 2012, with two databases, both publishers, replicating to a bunch of subscribers. Can each database go through a different distributor? Is the publisher the server or the ...
1
vote
1answer
16k views

The process is running and is waiting for a response from the server - transactional replication

I could not yet figure it out why one of my transaction replication subscription is constantly coming out with the following annoying message: The process is running and is waiting for a response ...
1
vote
1answer
2k views

Transactional replication between SQL Server 2014 (Publisher) and SQL Server 2012 (subscriber)

We need to set up transactional replication between two servers A and B, configs listed below: Server A (Publisher) Microsoft SQL Server 2014 - 12.0.2254.0 (X64) Enterprise Edition (64-bit) ...
10
votes
4answers
9k views

Unable to truncate transaction log, log_reuse_wait_desc - AVAILABILITY_REPLICA

This morning i was woken up by a transaction log full alert on one of our database. This server is an alwayson cluster and also a transactional replication subscriber. I checked log_reuse_wait_desc ...
5
votes
1answer
780 views

Moving Transaction Data To Another Database For Reporting Purpose

We got a requirement from our developer group, to do the followings: Frequently (every 30mins) move the transaction data out of live database to another database Secondary database will be utilize ...
4
votes
1answer
15k views

sql server - NoSync subscriptions will need to be dropped and recreated

I am having this error message from the sql server replication monitor: Error messages: The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to ...
3
votes
2answers
1k views

Transactional Replication Reinitialize

I'm using SQL Server transactional replication and looking for ideas on reducing the amount of downtime caused by re-initializing subscriptions. I have multiple publishers pushing data to a single ...
3
votes
1answer
831 views

Convert existing subscriber Database to a publisher database. Current publisher will be subscriber

We have a transactional replication setup. The publisher is on a separate SQL Server and the distributor and the subscriber together on the other server. The replication was done using restore from ...
3
votes
2answers
1k views

How to wait for SQL Server replication reinitialization via TSQL

We use transaction and push replication. Every night after the data import 2 replication targets are initialized with the following commands. USE [dbImport] -- Execute at the Publisher to ...
2
votes
2answers
4k views

Dropped the Distibution database before disabling publishing and distribution, how to proceed?

I'm setting up my first Distributor database on SQL Server 2012. I successfully created one and then wanted to blow it away and perform the task again just for practice. This was a brand new ...
2
votes
1answer
5k views

Could I realistically use SQL Server Express as a distributor database for transactional replication?

We have a group of users within my company that are running reports directly against our production database. It has gotten to the point where they are hammering it enough to cause performance issues ...
1
vote
1answer
1k views

Replication Error when adding small table as new article

I have just added a small (200 row) table to my current replication which has now broken my entire replication scenario as it seems to have broken the Log reader. The 2 tables are unrelated. I am ...
1
vote
1answer
2k views

How to set up a transactional replication to an Always On Availability Group (AG) with two replicas through connecting to the AG Listener

I want to replicate some tables on a SQL Server instance (Publisher) to an Always On Availability Group (AG) with two replicas (nodes) using its Availability Group Listener. The Distributor is the ...
1
vote
1answer
1k views

Transaction Replication Without Snapshot

So I have looked up many other people trying to get Transaction Replication Without Snapshot working and I'm stuck. I've followed what everyone has said so I don't know what I'm missing. Prod server ...
1
vote
2answers
7k views

Replication between two different SQL Server versions

We have mirroring configured (transactional replication) in our primary and secondary SQL Servers, which are running on Windows 2008 R2 SP2 OS with 2008 R2 SP3 as the SQL Server version. If we first ...
1
vote
1answer
746 views

Why my transaction has not been replicated? What was the transaction sequence number?

I have been troubleshooting a replication problem, according to the picture below: with reference to the picture above, the bit that says Sessions of the distribution agent I can get using the ...
1
vote
1answer
495 views

MS SQL Server: Transactional replication not working but no errors are present

(MS SQL Server 2008 R2) I have transactional replication setup from server A to server B. Server A is an Oracle server, so the publisher and subscriber both reside on server B (SQL 2008 R2). It was ...
1
vote
0answers
1k views

SQL Server Transactional Replication Distribution Database not keeping up

We have following setup at the moment: 8 Cluster servers (SQL Server 2012 Standard Edition) talking to 2,000 store servers in the field via Merge Replication. The 8 servers are replicating to a ...
1
vote
3answers
896 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 ...
0
votes
1answer
6k views

"Uninitialized subscription" on replication monitor when creating a subscription on transactional replication [closed]

we have the same question here. However, that does not answer my question below. I'm using SQL Server 2012 and trying to implement transactional replication. I'm using the system stored procedures to ...
0
votes
0answers
829 views

SQL Server 2012 Replication Subsystem Increase Number of Working Threads

I am trying to create Transaction Replication (Push) between two servers, using a distribution database on a3rd server. Server 1 is SQL Server 2008, Server 2 is SQL Server 2012. The Distribution ...