We have two MS SQL Servers (2012 Standard Edition on Windows 2012 Server) which are located in the same subnet. One is **production** server. Another is **reserve** server. Currently there is a transactional replication between two databases on these servers where **production** server is Publisher and Distributor and **reserve** server is Subscriber. On **production** server every night runs some tasks which inserts to (or updates) about of 5 tables one of them is about 6 Gb. We would like to move this tasks to **reserve** server and create on one another database with the same scheme. Then when tasks upload data to another database we need to synchronize one with **production** database on **production** server. **One moment :** For supporting actual data we must to include another database on **reserve** in replication as Subscriber from **production** server. As one way we consider linked server: 1. Truncate table on production (in one table there is FKs, so we can't) 2. Insert data from another database **Edit:** How about linked server and merge functionality? How do you think are there the better way?

I have 4 databases on the server that need to be replicated using Transactional replication. Each database gets 2 publications for itself, so the log reader is the same, but 2 publications for each. These publications are different in the articles that go into each. So, tables with the name not starting with XX_ go to the PublicationName and those with the name starting with XX_ go to the PublicationName_XX. Each publication then gets its own subscriber. I am able to set up 3 of those replications with no problems, but when I get to the 4th, I get the error that a The Distributor has not been installed correctly. Could not enable database for publishing. The confusing part is that it does not happens on a particular db among those 4. It seems to be by count .. I can set up first 3 in any order, and then 4th one fails. There is more, sometimes, I am able to set up the PublicationName4, and a failure occurs when PublicationName4_XX My initial thought was about space limitations, however I am not even close to a half on the disk size. When I check the distributor use master EXEC sp_get_distributor it shows as properly configured. So, I am at loss ...

I have configured a transactional replication from server A to Server B some 2 month ago. The source database is a new production server, and the data is being updated there constantly. I have some issues ... I am not sure how to resolve. 1. I see that the "last sync" occurred 2 month ago, when I initially set it up. - I was expecting a snapshot job to be of a periodic nature, but it is not. - It is actually set to a "One time" and is not enabled. I know, I can "enable it" and set the schedule for the future, however I am looking for how to do it at the time when I configure the replication. I may be configuring it with the script and be expecing the configuration go into effect from the power shell script rn, and want to make sure i know how to set the schedule from the beginning. This is what I see in a schedule of a Snapshot job on the Sql Server agent.

LATER ADDITION: I resolved with a reset master on surface which I had been hesitant to execute because it might negatively impact replication to offsite which it did. Hopefully that simpler replication is easily restored. This is a 4 node replica system: monitor->web->surface->offsite where monitor is a recent addition and adds a few tables to web which had previously been the sole primary node. A parallel replication chain backend->surface->offsite exists. web->surface replication takes place for inserts to tables replicating from monitor but does not occur for inserts to primary tables web (or for creation of a test table on web). I note that (not my choice) (1) monitor implemented a different database name which is accommodated in web's my.conf and (2) uses MySQL 8.0.42 (probably not relevant given that replication to web works and the MySQL change documentation does not suggest breaking changes but included for completeness):

replicate-rewrite-db = monitor_db_name->web_db_name

To restore replication with new tables on web, a full dump on web was made as follows:

mysqldump -u root -p --databases web_db_name --single-transaction --master-data --extended-insert --triggers --routines --events --set-gtid-purged=OFF | gzip > web_db.sql.gz

and manually changed the CHANGE MASTER line the the dump file: CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=220385066 for channel 'slave_channel_web'; On surface,

stop replica for channel 'slave_channel_web';
Confirm replication stopped:
show replica status for channel 'slave_channel_web'\G;
insert dump file
start replica for channel 'slave_channel_web';
Confirm replication is working:
show replica status for channel 'slave_channel_web'\G;

Here are the contents of /etc/my.cnf on web:

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin 
# default-authentication-plugin=mysql_native_password

# Specifying the timezone for the databases 
default-time-zone = 'US/Eastern'

# Setting the default authentication plugin
default_authentication_plugin=caching_sha2_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

symbolic-links=0
log-bin=/data/mysql-bin/mysql-bin
server-id=2
gtid_mode=ON
enforce-gtid-consistency=true
max_allowed_packet=64M

relay-log-info-repository=TABLE
master-info-repository=TABLE
#skip-slave-start=1

replicate-rewrite-db = monitor_db_name->web_db_name

relay-log=web-relay-bin

Here are the contents of /etc/my.cnf on surface:

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
# datadir=/var/lib/mysql

# Setting the timezone for the databases
default-time-zone = 'US/Eastern'

# Setting the default authentication plugin 
default_authentication_plugin=caching_sha2_password

datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

log-bin=mysql-bin
server-id=3
gtid_mode=ON
enforce-gtid-consistency=true

relay-log=surf-relay-bin
relay-log-info-repository=TABLE
master-info-repository=TABLE

#skip-slave-start=1

innodb_buffer_pool_size=4G
#innodb_flush_log_at_trx_commit=1 # Set to 2 for faster mysql insert from dump and back to 1 afterwards for data integrity
innodb_log_file_size=256M
innodb_flush_method=O_DIRECT

Mysql replica status suggests replication working (via an ssh tunnel to port 13306 on surface):

mysql> show replica status for channel 'slave_channel_web'\G;
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 127.0.0.1
                  Source_User: repl
                  Source_Port: 13306
                Connect_Retry: 60
              Source_Log_File: mysql-bin.000002
          Read_Source_Log_Pos: 235405762
               Relay_Log_File: cdms-dqsurf-relay-bin-slave_channel_web.000002
                Relay_Log_Pos: 15021022
        Relay_Source_Log_File: mysql-bin.000002
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 235405762
              Relay_Log_Space: 15021256
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File:
           Source_SSL_CA_Path:
              Source_SSL_Cert:
            Source_SSL_Cipher:
               Source_SSL_Key:
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Source_Server_Id: 2
                  Source_UUID: 8b3fb531-b226-11e8-81e7-b496912d2d25
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set: 1be71be7-2cf6-11f0-b3fe-e8cf832af101:177712-180447,
8b3fb531-b226-11e8-81e7-b496912d2d25:37213-40242
            Executed_Gtid_Set: 1be71be7-2cf6-11f0-b3fe-e8cf832af101:175644-175649:175672-175675:175702-180447,
29dbdef6-b155-11e8-a7f1-b49691213024:8-2421409,
8b3fb531-b226-11e8-81e7-b496912d2d25:1-40777:40864-2418597,
d4ee172d-bc52-11e8-b3eb-b496912d2f49:1-31882
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name: slave_channel_web
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)

That is consistent with new data from a table (chilled_water) on monitor arriving on surface. The surface MySQL log file agrees that the replication connection to web was successful. On surface I see the chilled_water table from monitor updating:

mysql> select id,timestamp from chilled_water order by id desc limit 5;
+---------+---------------------+
| id      | timestamp           |
+---------+---------------------+
| 1696323 | 2025-07-16 09:21:22 |
| 1696322 | 2025-07-16 09:21:12 |
| 1696321 | 2025-07-16 09:20:52 |
| 1696320 | 2025-07-16 09:21:02 |
| 1696319 | 2025-07-16 09:20:51 |
+---------+---------------------+
5 rows in set (0.00 sec)

However, information inserted on web into web-specific tables (that do not exist in monitor) do not replicate to surface as is seen in new information on the ups table on web not showing up on surface and a test table created on web not replicating to surface.

mysql> select id,time from ups order by id desc limit 5;
+--------+---------------------+
| id     | time                |
+--------+---------------------+
| 359423 | 2025-07-15 11:36:03 |
| 359422 | 2025-07-15 11:35:02 |
| 359421 | 2025-07-15 11:34:02 |
| 359420 | 2025-07-15 11:33:02 |
| 359419 | 2025-07-15 11:32:02 |
+--------+---------------------+
5 rows in set (0.00 sec)

I'd appreciate any advise on how to proceed in debugging and addressing this. The form asked what I've tried to fix the issue. I tried stopping and restarting the replica, redoing the above process from a fresh database backup, and combing the web and MySQL documentation for input.

I have transactional replication setup in SQL Server 2022. I have a distribution server running push subscriptions to subscribers. I'm trying to figure out if I need to stop replication jobs (log reader and distribution jobs) when running maintenance jobs such as backups, re-indexing, update stats, and DBCC maintenance? I'm trying to avoid causing any replication outage due to maintenance jobs. Replication jobs are setup to run continuously when SQL Server starts. I did this for best practices because we were seeing latency when I scheduled these jobs instead of running them continuously. I made reset jobs to run continuously among a few other changes and we no longer see latency. If I need to stop jobs while running maintenance jobs; can someone recommend the best way to do this. Like I said, I was trying to avoid scheduling these jobs. I was thinking if needed; I could put a step in the first maintenance job that is scheduled to run to stop replication job and restart it after the last step of the last maintenance job. Any thoughts? I haven't tried to run maintenance jobs yet as this is in a new production environment, and I didn't want to have to apply another snapshot in case maintenance jobs break replication.

I am assigned to create a transactional replication with a SQL Server clustered instance for reporting purposes. I'm planning to use the Distributor and the Subscriber on the same box. Regarding the snapshot folder, is it ok to place it on the same box where the Distributor\Subscriber resides? Will there by any problems?

some of the tables inside my distribution database are very intensely populated as you can see: this was caused by adding a few large tables to the publication a couple of days ago the cleaning job is running regularly: Inside that job the following command is run:

EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72

it looks like it is not doing the cleaning as expected. what alternatives do I have?

I have a source DB MSSQL Server and deploy transactional-replication to a cluster with 2 Node(A&B). The subscriber was added using SQL IP of Cluster and using "sa" account. It run fine when server A active and server B passive. When i shutdow the server A the replication stop and show error: The process could not connect to Subscriber 'AHTDWHSQL'. (Source: MSSQL_REPL, Error number: MSSQL_REPL0) Get help: http://help/MSSQL_REPL0 Named Pipes Provider: Could not open a connection to SQL Server . (Source: MSSQLServer, Error number: 1326) Get help: http://help/1326 A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (Source: MSSQLServer, Error number: 1326) Get help: http://help/1326 Login timeout expired (Source: MSSQLServer, Error number: HYT00) Get help: http://help/HYT00

I am a newbie to this Replication concept and I am trying to build some scripts to automate our Build Process where we will be disabling/re-enabling Replication on a regular basis. **Background:** We have an Application Database Server which is on High Availability (Primary & Secondary Nodes). Our Staging Database Server will be communicating with the HA Group either through a Load Balancer or Listener. **Requirement:** We would like to enable Replication at the Table level and not the Database level from HA Group to the Staging Server as we are only interested in few tables. Is this even possible? If so, I am looking for some scripts (from your Toolbox, if you have any) to create Publisher, Distributor, and Subscriber and then disable/re-enable them on a regular basis during our Build process. Note: This would be more of a "Pull" and I am looking for Scripts that can be executed from the HA Group as part of the Build Process. Your help with this is very much appreciated. Thanks!

The setup: A push transaction replication is configured for about 20 tables/articles at SQL2019 CU31 Standard. Some of the tables are permanently under a load of thousends of inserts per second. In general replication works fine except the moment when a new table is added to the replication. Initially we execute sp_addarticle to add a new article at the publisher. Then, we have to call either sp_refreshsubscriptions or sp_addsubscription, to add a subscription to this article at the subscriber. The issue: The stored procedure sp_refreshsubscriptions puts an SCH-M lock not only on the table that is added. It also put the lock on other tables belonged to the same publication, including the ones highly loaded with inserts. As SCH-M is not compatible with any other locks inserts start to wait for the SCH-M session while the SCH-M session itself waits for other concurrent inserts resulting in deadlocks and locks for 15..20 seconds. This is a downtime for business and unacceptable. An alternative approach with sp_addsubscription brings no releif. Even though the new article is explicitly specified in parameters still the procedure puts SCH-M locks on all tables in the same publication. In contrast when inserts are paused (at a Stage environment) the same operation executes instantly. The question: Why the locks of unrelated tables and is it possible to workaround them besides pausing inserts?

Distribution clean up job ran without errors according to schedule, but I noticed that the snapshot files were not removed even if when created beyond max_disretention period. Records from msrepl_commands and msrepl_transactions were removed, but the files were not. - immediate_sync = 1 - max_disretention = 72 hours

So I have a SQL server 2016 on a Windows Server 2012 instance. We have 140 databases(small to med). So the SQL instance was setup for self replication and in the process we have 140 SQL agent replication jobs. When the server reboots it can handle ~120 replication jobs, then when one is stopped it can not be re-started - it fails - but with no message at all - then says "between retries". In replication monitor - the agent has the red x and or is showing not running. In the event viewer I see logread.exe application crash. I was trying to research to see if there is a cap on the number of agent jobs running. Our memory is good, the disks and CPU usage is low. I have not been part of something of this size before and I am currently out of ideas. My current work around is to have a PowerShell script read the current failures and manually kick off the -Publisher [xxx] -PublisherDB [DbName] -Distributor [xxx] -DistributorSecurityMode 1 -Continuous . That is what makes me think there is a cap on the number of agents because I can run it with out the SQL-Agent. I was thinking maybe I need to change that to more of a scheduler but we have reports run that need timely info. What am I missing? Or am I right in looking to breaking up the job runs?

I am replicating the data from the Database1 on Server1 to respectively, Database2 on Server2. Now, the requirement is to remove certain tables form being replicated - so I will remove them from the Publication. I have many articles, so am doing it using script and cursor .. but for simplicity, I will use example of one: So, I am following this routine, but have a question: **Questions are:** Is there anywhere in the below, before or after where I need to - Stop / start snapshot agent? - Stop / start log reader agent? - Stop / start distribution agent? If I do have to start / stop respective agent jobs, do I do it for every article or I can do it before (stop) and after (start) the cursor loop where each article is individually dropped? The routine followed is USE Database 1 SELECT @publicationName = name FROM dbo.syspublications sp -- Change the allow_anonymous property of the publication to FALSE EXEC sp_changepublication @publication = @publicationName, @property = N'allow_anonymous', @value = 'FALSE' -- Next, disable Change immediate_sync EXEC sp_changepublication @publication = @publicationName, @property = N'immediate_sync', @value = 'FALSE' Assuming, the following is used in the cursor-loop, and there is a variable for @articleName SELECT articleName FROM ( SELECT sp.pubid, sp.name as 'publicationName', sp.description, sa.artid, sa.name as 'articleName', sa.dest_table, sa.schema_option FROM dbo.syspublications sp INNER JOIN dbo.sysarticles sa ON sp.pubid=sa.pubid ) arts WHERE articleName = '' --In reality it will be 'like '%%' -- Must drop subscription first EXEC sp_dropsubscription @publication = @publicationName, @article = @articleName,-- obtained from above @subscriber = 'Server 2' -- Drop the transactional article. EXEC sp_droparticle @publication = @publicationName, @article = @articleName, @force_invalidate_snapshot = 1; -- Next, Enable Change immediate_sync EXEC sp_changepublication @publication = @publicationName, @property = N'immediate_sync', @value = 'TRUE' -- Change the allow_anonymous property of the publication to TRUE EXEC sp_changepublication @publication = @publicationName, @property = N'allow_anonymous', @value = 'TRUE'

In Transactional Replication of SQL Server, Synchronization job starts well and proceeds a while. However, in middle of the work it shows this error: > The job failed. The Job was invoked by User WIN-XXXXX\Administrator. > The last step to run was step 3 (Detect nonlogged agent shutdown.). It's a very generic error message. How can I get more details about the error?

I would like to try pglogical to set up an archive server from a live database, filtering with INSERT, UPDATE statements (DELETE statements will not be replicated in the archive server). I follow the official documentation but when i set shared_preload_libraries = 'pglogical' in my configuration file (postgresql.conf) the server could not start. There is no pglogical file in ./usr/pgsql-9.6/lib/ directory. How setting up all prerequisites to start properly (with v9.5 and 9.6)? I am using PostgreSQL v9.5 and v9.6 for POC on CentOS 7. Many Thanks.

I am trying to setup Transactional Replication to from Server A (Publisher) to Server B (Subscriber) using a remote Distribution Server - Dist A (Distribution). Dist A already services multiple publications from multiple publishers. ***Details about environment:*** **Server A:** - Version: SQL 2017 v 14.0.3465.1 - Cluster: Traditional FCI - No Availability Groups - Server A is a new installation with 1 empty database that has never been setup for replication before. **Dist A:** - Version: SQL 2022 v 16.0.4085.2 - Cluster: Traditional FCI - No Availability Groups - Dist A is an existing Distribution Server used for Production Transactional and Merge Replication by 4 Publisher Servers and 20 Subscriber Servers with ~30 Unique Publications. I am trying to create my first publication on Server A using a brand new database. I successfully configured the Publisher settings on Dist A (allowing Server A to be a Publisher and dictating which Distribution Database it will use). On Server A, I added Dist A as its distributor, then enabled the database for Transactional Replication. I am now attempting to create the Log Reader for the Database on Server A and receiving the following error: USE [Database]; GO EXEC sys.sp_addlogreader_agent @job_login = N'', @job_password = '', @publisher_security_mode = 1; GO > Msg 3933, Level 16, State 1, Procedure > sys.sp_MSrepl_addlogreader_agent, Line 184 [Batch Start Line 16] > Cannot promote the transaction to a distributed transaction because > there is an active save point in this transaction. When researching this error, I am only finding posts referencing Replication and Availability Groups. **We do not have Availability Groups on Server A or Dist A.** We have confirmed that the items in sys.configurations match those of other servers who are actively publishing. Linked Server settings to the Distributor are equal to those working servers as well. I am trying to find out what setting or configuration item was missed.

I have a third party incident management tool, that creates tickets from errors in the SQL logs. Occasionally replication stops replicating without creating errors in the SQL logs. I want to create a job that runs every 10 minutes or so to check on replication and then uses [RAISERROR](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql?view=sql-server-ver15) to start our ticketing (*and alerting*) process. There are a number of stored procedures to [Programmatically Monitor Replication](https://learn.microsoft.com/en-us/sql/relational-databases/replication/monitor/programmatically-monitor-replication?view=sql-server-ver15) that will show issues, and there is also a pretty good solution at [Monitoring Transactional Replication in SQL Server(*by Francis Hanlon 11 April 2013*)](https://www.red-gate.com/simple-talk/sql/database-administration/monitoring-transactional-replication-in-sql-server/) that is good start on what I want. But would need to be tweaked a bit to meet my needs. Before I start reworking Francis's solution I am wondering if there are any other solutions I might leverage on. I have searched around here and google, and Francis's solution is the only one I found that gets close to inhouse monitoring, without new third party tools. Are there any solutions to monitor SQL replication real time with T-SQL? * SQL 2008 to SQL 2019 * Mostly transactional replication * Same AND cross server replication

Per this article , a Publisher can only have one remote Distributor in doing SQL Server Transactional Replication. > Each Publisher can be assigned to only a single Distributor instance, but multiple publishers can share a Distributor. If this is the case, I am wondering how to scale out the publication of a database to a large number of Subscribers. Supposing a Publisher had 200 Subscribers with a large number of transactions, what would be a workable distribution architecture that could handle that load? Scenario is 'one source of truth' - up to 200 read-only slaves in different geographical areas that each get a slice of the central source, based on data pertinent to their local area (filtered rows). All writes are made to the central master, which then need to propagate down to the local slave in as close to instant as possible. I've done a bit of research and transactional replication seems to be the best bet.

Has anyone seen a domain Group Policy stop a transactional replication snapshot from being configured and executed with the message: > A required privilege is not held by the client. I am having trouble getting to much from our server admin guys. All they would do was create me a new server, added it to an OU with a filter to block the Group Policy and I got it to work (using domain user for agent) but now I can't get anything out of them about the Group Policies being delivered. Does anyone know the policy setting that could be blocking the making of a snapshot?

We would like to inplace upgrade of transactional replication topology from SQL Server 2017 to SQL2019 with minimal downtime. The following is the setup. All the all current instances are on SQL Server 2017 enterprise * Server A has 2 databases serving as publisher * Server B has 2 databases serving as subscriber * Server C is the remote distributor. From inplace upgrade perspective, we have to first upgrade distributor --->publisher--->subscriber. I would like to know the rollback steps for distributor database if inplace upgrade fails to SQL Server 2019 and we have to revert to SQL Server 2017 with minimal possible downtimes. Requesting some insights on procedure to do the same. Kevin