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

learn more… | top users | synonyms

30
votes
4answers
18k 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 ...
9
votes
1answer
758 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 ...
7
votes
2answers
861 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 ...
6
votes
1answer
2k 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 ...
6
votes
1answer
2k 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, ...
6
votes
1answer
1k 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 ...
5
votes
3answers
3k 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 ...
5
votes
3answers
2k 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 ...
5
votes
2answers
3k 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? ...
5
votes
1answer
257 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 ...
5
votes
1answer
815 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 ...
5
votes
0answers
64 views

Low performance, high availability, replicated key/value store with audit trail

I'm looking for a database system with fairly specific specs. I've looked most of the major relational databases and NoSQL products out there and did not find one that even remotely satisfied my ...
4
votes
2answers
2k 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 ...
4
votes
1answer
185 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
177 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 ...
4
votes
1answer
155 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 ...
4
votes
1answer
241 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 ...
4
votes
2answers
4k 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 ...
4
votes
1answer
46 views

Transactional Replication to a Version Two Versions Older--Count by Name or Number?

According to Microsoft SQL Server documentation, a "Subscriber to a transactional publication can be any version within two versions of the Publisher version." In this context, are versions named ...
4
votes
2answers
5k 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 ...
4
votes
1answer
109 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 ...
4
votes
1answer
4k views

SQL Server 2008 R2 replication high delivery latency

I am seeing an unusually high delivery latency between our distributor and subscribers and i do not understand why. We have in this configuration 3 sql servers using transactional push replication to ...
4
votes
1answer
1k views

Replication between SQL Server 2005 and Azure database

Is it possible to set up transitional replication between a SQL Server 2005 and a SQL Azure database? My server is nearing end of life and I would rather migrate to a cloud infrastructure instead of ...
4
votes
2answers
2k views

transactional replication, failing with communication link failure half way through bcp

I am trying to replicate to a 4th subscriber on a remote site. 3 subscribers work fine locally. the other difference is this is a 2012 subscriber from a 2008 r2 publisher and remote distributor. When ...
4
votes
0answers
66 views

transaction replication causing too many deadlocks after adding a indexed view - what to monitor in order to avoid this?

I have created the following indexed view and its clustered index: This view was created on a subscriber database and all the tables used in the view are constantly being written to through the ...
4
votes
1answer
333 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 ...
3
votes
2answers
4k 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 ...
3
votes
2answers
422 views

Copying MySQL Raw Data Files for Master-Slave Replication and GTIDs

I have been developing and running a mysql-based web application (intranet-based) for some time now. I wish to establish a step-by-step sane sequence for creating a slave from the running MySQL ...
3
votes
1answer
465 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 ...
3
votes
3answers
6k 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. ...
3
votes
1answer
498 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 ...
3
votes
2answers
49 views

Is my Publisher database my actual source database or is it a new database?

I am setting up transactional replication for the first time and am working through my backup policies I need to set. Is my Publisher database my actual source database? Or is it a new database that ...
3
votes
2answers
47 views

Replication DB move

I have 2TB of DB involved in SQL Server transactional replication. I want to move publisher DB files to a newer disk with minimum amount of downtime and without breaking replication. Please let me ...
3
votes
1answer
2k 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 ...
3
votes
1answer
791 views

What causes Transactional SQL Server Replication to need to be rebuilt/re-initialised

In a company I've recently joined we have two SQL Server instances, connected via transactional replication. I'm a developer and hadn't really come across SQL replication before so am a complete ...
3
votes
2answers
900 views

Cleaning up transaction replication del/ins/upd stored procedures?

I'm referring to the ones that start with: dbo.sp_MSdel... dbo.sp_MSins... dbo.sp_MSupd... I've noticed they are not removed when I run exec sp_dropsubscription on the Publisher then run ...
3
votes
2answers
102 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 ...
3
votes
1answer
888 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
1answer
1k views

Cannot delete publication

When attempting to delete a publication, I am getting the error cannot drop publication 'XX' cannot drop article 'XX'. The subscription has been deleted at the subscriber. Any ideas on how I can ...
3
votes
1answer
777 views

What is the difference between 'two way transactional replication' and 'peer to peer replication'?

Can someone explain in detail the exact differences between 2 way transactional replication and peer to peer replication?
3
votes
1answer
774 views

How to break out of error loop in transactional replication?

I was running some simple tests on a publisher and subscriber on the same instance: I inserted 50,000 rows into a published article and the data was correctly pushed to the subscriber. I ...
3
votes
0answers
25 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 ...
3
votes
1answer
53 views

What about redundancy if distributor goes down?

I'm setting up load balancing, transactional replication and mirroring. However, what I actually thought might work from the beginning, making the system redudant doesn't actually work because a ...
3
votes
0answers
103 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 ...
3
votes
0answers
175 views

SQL Server replication - error when creating a subscription - The article '(null)' does not exist

When trying to add a subscription to an existing publication use [Product] exec sp_addsubscription @publication = N'Product', @subscriber = N'REPLON1', @destination_db = ...
2
votes
1answer
2k 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 ...
2
votes
2answers
1k views

SQL Server Replication breaks up after doing DBCC CheckDB with Allow data loss on Primary

We have one way transactional replication enabled on SQL Server 2008 databases. Due to some issues we had to run DBCC checkDB, bringing the database to single user mode. When we complete the DBCC ...
2
votes
1answer
3k 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?
2
votes
2answers
1k 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
4k 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 ...