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
48 votes
4 answers
36k 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 ...
user avatar
  • 8,683
27 votes
0 answers
1k views

Splitting Snapshot files with MaxBCPThreads for Transactional Replication

I've just set up a publication, and I'm attempting to get the snapshot to apply faster. So far the Distribution Agent is respecting the MaxBCPThreads settings, but the Snapshot Agent is not. I'm ...
user avatar
  • 271
15 votes
1 answer
20k views

Length of LOB data (2135980) to be replicated exceeds configured maximum 65536

I am getting the replication error 'Length of LOB data (2135980) to be replicated exceeds configured maximum 65536'. sp_configure was previously applied to set it to max value (-1) and a query on ...
user avatar
13 votes
3 answers
21k 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?
user avatar
11 votes
6 answers
75k 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-...
user avatar
  • 371
10 votes
4 answers
11k 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 ...
user avatar
  • 1,708
10 votes
1 answer
1k views

Initializing Transactional Replication From Backup

There is an option when setting up a publication for replication to Allow Initialization From Backup. We have been creating replicated databases for several years now and have always initialized from ...
user avatar
8 votes
2 answers
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 ...
user avatar
  • 327
8 votes
2 answers
8k views

SQL Server 2008 R2 Transactional Replication "Cannot insert explicit value for identity column..."

Right now I'm having an "identity crisis" with transactional replication in SQL Server 2008 R2. The database is running in compatibility 90. There's a table that has an identity column and won't ...
user avatar
8 votes
2 answers
34k views

Why do I get "The initial snapshot for publication is not yet available."?

We are using Transactional Replications with updatable subscriptions. 1 publisher, 1 subscriber. SQL Replication started encountering a constraint error on 3/6 at 10AM because people at two different ...
user avatar
  • 251
8 votes
1 answer
707 views

Changing collation of SQL Database in Production environment to minimize downtime

We have a scenario in which we want to change the collation of our production database (including columns) from SQL_Scandinavian_Pref_CP850_CI_AS to Finnish_Swedish_CI_AS. We have developed scripts to ...
user avatar
8 votes
1 answer
4k views

How to restore replication after server crash

We had two databases on one SQL Server 2005 instance with transactional replication between them (3 tables as articles). This server acted both as publisher and distributor. Then the RAID on the ...
user avatar
  • 221
7 votes
3 answers
21k views

Creating a read only replica of SQL Server database for reports

I've faced a problem when my .Net web application is running slow, even when I've optimized all queries. As it turned out another developer, who is making reports runs a bunch of non optimized queries ...
user avatar
  • 235
7 votes
2 answers
6k 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 ...
user avatar
  • 327
7 votes
1 answer
952 views

Database Compatibility Requirement for Transactional Replication

Does the subscriber "within two version requirement" for transactional replication from this MSDN article apply to the SQL Server Instance, Database Compatibility Level, or Both? For example, would ...
user avatar
7 votes
1 answer
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, ...
user avatar
7 votes
1 answer
2k views

Transactional Replication not sending all commands

I have a transactional replication topology as follows: NY Publication Database same server holds distributor NY - Subscriber LON - Subscriber HK - Subscriber We are using bog standard, one-way ...
user avatar
  • 1,436
6 votes
1 answer
1k views

Transactional Replication: can create 52 publication, but creating 53rd leads to problems

My apologies in advance for a long post, but I was really trying to make sure I do my due diligence before turning to the community for help, so here it goes. I am working on a proof of concept with ...
user avatar
  • 575
6 votes
1 answer
2k views

missing indexes on distributor database

Looking for missing indexes on my distributor databases, I surprisingly find too many. the tables that need to be indexed are generally 3: MSrepl_commands MSlogreader_history MSdistribution_history ...
user avatar
6 votes
3 answers
4k views

What type of SQL Server Replication fits our scenario? Merge or Transactional?

Background In our current setup, we have a single SQL Server 2005 instance on our remotely hosted web server. We additionally have another (non-MSSQL) database which we use for our POS system, which ...
user avatar
  • 163
6 votes
1 answer
563 views

Replication of specific tables using Postgres 9.4.4 and Amazon Web Services

I have currently setup a Postgres 9.4.4 RDS instance on Amazon Web Services. On this instance I hold 20 tables. In addition I also have Postgres 9.4.4 installed on my local server (OS: 64-bit Linux) ...
user avatar
6 votes
1 answer
224 views

SQL Server Replication

We had an issue with replication, where a large (7.5 million row) update was done on a 33 million row table. Replication converted that to 7.5 million individual update statements. When my alerting ...
user avatar
5 votes
2 answers
4k views

Transactional Replication Concerns

We are debating an architecture that employs sql server 2012 and transaction replication. Idea is to offload reporting activity to a secondary server and have the ability to include/exclude what is ...
user avatar
5 votes
4 answers
2k 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 (...
user avatar
  • 668
5 votes
3 answers
19k views

Is replication possible on two different domains?

I am implementing a database replication between different geograhical locations where each one is on a different domain. After going through all the process, when I start the agent it runs and stops. ...
user avatar
  • 657
5 votes
1 answer
831 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 ...
user avatar
5 votes
2 answers
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 ...
user avatar
5 votes
2 answers
2k 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 ...
user avatar
  • 1,295
5 votes
2 answers
22k views

Sql Server : How to query list of subscriptions and Articles (for a given publication) from Publisher

In Sql Server 2014, how to query a list of Subscriptions & its articles for a given Publication from Publisher? (transactional replication) Yes, there are easier ways to query from Distributor ...
user avatar
  • 565
5 votes
2 answers
6k views

Recommended distributor architecture for transactional replication?

We are planning to create a Transactional replication on 2 nodes of SQL Server 2008 R2, each node will be a publisher and a subscriber. What is the best practice in order to have this scenario done? ...
user avatar
5 votes
1 answer
3k views

Should snapshot agent continue to run in transactional replication?

We have transactional replication running for a large number of publications on sql server 2008 R2 2 node active/active cluster. I have noticed that the snapshot agent job runs hourly and it looks ...
user avatar
5 votes
1 answer
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 ...
user avatar
  • 53
5 votes
2 answers
2k views

Many-to-One replication / Transactional Replication

I have several PostgreSQL DBs in different geographical locations (local sites). Each local site DB have the same schema, but unique data. For example, Take a table with columns: Site_ID, ...
user avatar
  • 51
5 votes
1 answer
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 ...
user avatar
5 votes
1 answer
1k views

Indexed view doesn't exist on the subscriber

I am configuring transactional replication in SQL Server. The subscription is configured as push from 2008R2 publisher (distributor is the same server) to 2012 subscriber. The object I want to ...
user avatar
  • 1,405
5 votes
2 answers
942 views

Updateable Subscriptions: Rows do not match between Publisher and Subscriber

I have transactional replication with updatable subscribers set up in SQL Server 2008. It has 3 subscribers and 1 publisher. I had to setup replication again due to some errors related to the database ...
user avatar
  • 111
5 votes
2 answers
548 views

Transactional replication creating a fixup index that's sleeping, but blocking other processes

I've just migrated my ERP database server from SQL Server 2000 to SQL Server 2008 R2 (both Enterprise Edition) and that migration went fairly smoothly. However, now that I'm trying to recreate ...
user avatar
  • 10.4k
5 votes
1 answer
2k views

SQL Server Troubleshooting Replication and Long-Running fn_dblog query

I am trying to get (pull) Transactional Replication working again for a subscriber. Currently, in Replication Monitor I am seeing messages in the Publisher to Distributor tab and the Distributor to ...
user avatar
5 votes
1 answer
836 views

Replication throw an error after failover mirror partner is down in SQL Server

Received an error mentioned below after failover when mirror partner is down. Error : to fail over to a database which is not configured for database mirroring. code: 22037, text: 'Invalid ...
user avatar
  • 213
4 votes
1 answer
4k views

Can not drop table due to replication error, though table is not being published

I am attempting to drop a table in a database that is a publisher. The table in question is not being published. The error I get is: Msg 15021, Level 16, State 1, Procedure sp_MStran_ddlrepl, Line 14 ...
user avatar
  • 12k
4 votes
1 answer
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 ...
user avatar
  • 111
4 votes
3 answers
6k views

Replication Blocks Code Deployment

We have a database (SQL Server 2008r2) that is being transactionally replicated, namely tables, sprocs, views, indexed views and UDFs only, to another server (SQL Server 2014) as part of a cautious (...
user avatar
  • 205
4 votes
1 answer
970 views

With SQL transactional replication, does changing user permissions to a table trigger a new snapshot?

I was hoping to get some help with a SQL Server 2012 transactional replication scenario - or maybe just my understanding of where permissions are stored in SQL server. If I change a users permissions ...
user avatar
  • 43
4 votes
1 answer
333 views

SQL Server transaction replication ignores DML which does not change data

I have a transactional replication setup between the two servers and I noticed that if a run a statement similar to this: UPDATE mytable SET mycolumn = mycolumn the replication somehow knows to ...
user avatar
  • 575
4 votes
1 answer
344 views

Can I use only 1 user for SQL Server replication?

I want to setup transactional replication on sql server. By following the instructions of the ms replication preparation tutorial and the ms replication configuration tutorial, everything works fine. ...
user avatar
4 votes
5 answers
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 ...
user avatar
4 votes
1 answer
4k views

Invalid object name 'master.dbo.MSreplication_options' when restoring DB

I've tried Googling for an answer to this but have got nowhere. I have one SQL Server instance whose replication is set up to be a publisher and a distributor of FooDatabase. I have a second ...
user avatar
  • 653
4 votes
1 answer
4k views

How can I determine the last synchronization time for a SQL Server replication subscription?

I'm using SQL Server replication to mirror a few tables from an on-premises SQL Server instance to an Azure SQL database. We had an issue where the replication encountered an error, and we incorrectly ...
user avatar
  • 217
4 votes
1 answer
3k views

MSDTC and its relation to SQL Server

So I'm doing some research on MSDTC and its role it does or does not play in various types of calls within SQL Server, and it got me thinking on the following questions: Are there any 3rd party ...
user avatar
4 votes
1 answer
544 views

Two-way replication between SQL Server 2008 R2 and SQL Server 2008 R2 SP3

I know that in SQL Server you can't have two-way replication between different versions. I wanted to know if SQL Server 2008 R2 and SQL Server 2008 R2 SP3 are considered "different versions"? Did ...
user avatar
  • 211

1
2 3 4 5
10