Questions tagged [transactional-replication]
The automated distribution of transactional changes from a publishing database to one or more subscriber databases.
483
questions
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 ...
- 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 ...
- 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 ...
- 153
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?
- 14.9k
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-...
- 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 ...
- 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 ...
- 808
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 ...
- 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 ...
- 173
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 ...
- 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 ...
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 ...
- 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 ...
- 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 ...
- 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 ...
- 73
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, ...
user507
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 ...
- 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 ...
- 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
...
- 14.9k
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 ...
- 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) ...
- 183
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 ...
- 61
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 ...
- 51
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 (...
- 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.
...
- 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 ...
- 299
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 ...
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 ...
- 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 ...
- 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? ...
- 175
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 ...
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
...
- 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, ...
- 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 ...
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 ...
- 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 ...
- 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 ...
- 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 ...
- 373
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 ...
- 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 ...
- 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 ...
- 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 (...
- 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 ...
- 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 ...
- 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.
...
- 403
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 ...
- 109
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 ...
- 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 ...
- 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 ...
- 611
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 ...
- 211