We have a main SQL Server which replicates to two backup servers via continuous merge replication. There are regular updates to the schema. Regular changes include: 1. Adding new tables and relationships with existing tables 2. Changing the data type of columns on tables already replicated **What is the easiest way to maintain replication in such an environment?** If changes are made to the publisher, some changes cannot be synced to the subscriber and the whole replication has to be setup again (agent + subscriptions). These databases are too big to replicate in its entirety every time there is an update. Any advice will be appreciated.

I would like to know if there are any queries to get article properties in a SQL Server merge replication. I'm able to get properties only by GUI. I select a publication properties then I get the page properties, then I select an article (a table) then if I want to check the Article Properties, I select Set Properties of Highlighted Table Article I get the four group of properties like as Copy Objects and Settings to Subscriber, Destination Object, Identification and Merging Changes. Anybody knows if there are any queries to get that properties or to get a script that create the article with of all of these properties?

I have a merge repliaction with retention period set to 1 day. Allow anonymous Subscriptions is set to False. I Add 2 subscribers. One of the subscriber machine name is wrong, so it is inaccessible. On Repliaction monitor, this inaccessible subscription keeps trying to connect and fails with could not connect to subscriber error. It has been days, but this subscriber still exists. It does not expire. Running Expired subscription clean up job succeed, but the subscription does not got removed from subscribers. What is the possible reason for this not to work?

I have a typical master-detail table relationship (e.g., Orders and OrderDetails). My application updates the detail records by: 1. Deleting all existing detail records for a given master record. 2. Inserting the new detail records. This update pattern is used because it's easier to replace all details than track row-level changes. I want that the subscriber can update the record in the publisher too. It's why I am using Merge Replication. The Problem:\ Once Merge Replication is enabled: The DELETE + INSERT operations at the Publisher get replicated to the Subscriber. The Subscriber, having already synced the previous detail set, treats this as a conflict or change, and re-applies its own version. The replication engine then thinks the Publisher has changed again and pushes the data back. This cycle continues infinitely, causing either: Endless insertions of the same data, or Continuous merge conflict resolution. The only way to stop it is to delete the subscription, which is not sustainable. Questions: Is this an expected behavior in Merge Replication when using Delete-Insert update logic? Is there a recommended best practice for updating detail records in Merge Replication scenarios to avoid this loop? Would using UPDATE statements instead of DELETE + INSERT be a reliable workaround? Are there settings (e.g., conflict resolution rules, rowguid column issues, or tracking settings) that can help avoid this behavior? Environment: SQL Server 2017 Merge Replication with bi-directional sync (Publisher Subscriber) Replicated tables have rowguid and conflict tracking enabled

I have set up merge replication web sync to sync a publication from a SQL Server 2012 database. I am trying to make some parameterised filters using SUSER_SNAME() or HOST_NAME(). When I do a sync is it possible to find somewhere what the values of SUSER_SNAME() and HOST_NAME() actually were for that sync? E.g. in the system tables. I will need to experiment with security to work out what settings will affect these values, but I will need to find out what these values actually are to verify my settings.

We are facing an issue with our replication server. We are currently setting up a new environment to replace the old replication server and we keep getting the following error. Server XXX\YYY, Level 16, State 1, Procedure sp_MSreplremoveuncdir, Line 83 Could not remove directory '\\\XXXXXX\VTWSnapshot\unc\XXXX$YYYY\77_11\'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. (Source: MSSQLServer, Error number: 20015) Sometimes when we run the job to generate a new snapshot, it fails and sometimes it succeeds. Since it sometimes succeeds, I'm sure it's not an issue with access or permissions, else it would always fail. My second thought was that something is locking some of the files, not allowing SQL to delete anything from the folders. Sadly I only have permission on SQL and not on the server or file share, making this hard to check. Communication also goes a bit slow, me being in Belgium and the head office of their company being in America, that's why I'd like to get as much information as possible so I could ask everything at the same time. [This may be coincidence, but I'll mention it anyway] When we run the snapshot job X+1 times, it succeeds, with X being the number of subscriptions we have. Meaning: If we have 3 subscriptions A, B and C - Job fails the first time on folder A - Job fails on folder B - Job fails on folder C - Job succeeds This kind of made me believe that the folders are somehow locked and the lock gets released after the job has run, but failed. The next time the job runs, it can continue with the next folder until they are all unlocked? [/end of conspiracy] Any help, thoughts, ideas are welcome.

Production database is hosted on a SQL Server Standard instance and is replicated using merge replication with web synchronization to 10 subscriber databases hosted on 10 separate SQL Express server instances. Each subscriber database is accessible by another 5 local users, who can read/write/process the data. SQL Server Standard instance is licensed through CAL scheme. 1. Do we need to have CAL for every of the 5 local users on each subscriber server instance? 2. Do we need to have CAL for every SQL Server Express instance? Web synchronization is achieved through IIS Server on the same server with the SQL Server Standard instance. 3. Do we need to license every SQL Express instance, or it is free for use? 4. There is an additional capability for 20 users, to access the production database directly through a desktop thin client application, where they can read/write/process data, which data are then replicated to every SQL Server Express instance. Do we need CAL for every user of this thin client application? The application is communicating with SQL Server through ODBC using one specific login user credentials. Thanx

We create a new table in the Publication database and add a new Article to include that table in replication. We then create a new snapshot. The next sync will duly create that new table in the subscription(s) database, however, subsequent syncs will again create that table. This behavior repeats for each sync. This issue has only popped up in our last two Publication updates and we have successfully done many previous updates that involved new tables. The very strange thing is that if the sync is going to a subscriber database in the same SQL Server instance (i.e. a test subscriber on the same SQL Server as the Publication) then there is no problem. The issue only happens when the subscriber database is on another SQL Server. The sync will complete fine, uploading all schema changes and data as expected, but on the next sync, will drop the new table(s) and re-create them again. We examined the merge agent logs very closely and could see various stored procedures in action i.e.sp_MSissnapshotitemapplied, sp_MSrecordsnapshotdeliveryprogress and table MSsnapshotdeliveryprogress. I enabled Profiler during the syncs but have yet to identify the issue. I then created a simple ReplTest database, having one non-partitioned table which was added to the ReplTest Publication, with a subscriber database on both the Publication server and a remote server. Unfortunately, the issue did not appear in this simplifed setup. Our production Publication has 120+ tables, many of which have Article Filters 9i.e. partitioned) and 200+ stored procs. My next step would be to add a Filtered Article into ReplTest and do all the monitoring again using the agent logs and Profiler, however, I am hoping that someone can shortcut all that work and provide any clues ???

- merge replication publication - I would like to change the property only for this particular table myTable ,so that it will truncate the table at the subscriber, but I would like to leave all other articles unchanged. is this possible?

TITLE: Publication Properties
------------------------------

Cannot save properties for article 'myTable'.

------------------------------
ADDITIONAL INFORMATION:

Property "pre_creation_command" update with value "Truncate" failed. (Microsoft.SqlServer.Rmo)

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Property 'pre_creation_command' of article 'myTable' cannot be changed.
Changed database context to 'mypublication_DB'. (Microsoft SQL Server, Error: 21416)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-21416-database-engine-error 

------------------------------
BUTTONS:

OK
------------------------------

it is not my way to check the replication , however, on this instance I have many publishers on my replication monitor , as you can see on the picture below: where does sql server store the publishers names that are on the replication monitor? If knowing where this info is stored I might have a way to automate, or export, import it. Just clarifying the question here: I run the replication monitor from my own laptop. I have no live databases on my laptop. I have a set of publishers added to the replication monitor that sits on my laptop, and you can see it is a long list, each publisher is a different serve. I am not after data about each publication, for that I have the queries here . I would like to know where does sql server saves (presumably on my laptop) the names of the publishers I added to my replication monitor.

I'm setting up a merge replication web publication for a table that needs to use the DateTime (Later Wins) conflict resolver so that the most recently modified row wins in any conflict. This table (and hundreds of other tables) use a datetimeoffset for their modified date as subscribers can be in many timezones. In order to get the required datetime value as a column I've added a ModifiedDateUTC datetime computed column which does a simple CONVERT to a datetime. The issue I'm seeing is that even though the article setup accepts this as a valid column, whenever I generate a conflict I get the error 'The specified conflict resolution column 'ModifiedDateUTC' could not be found.'

I am looking for the location of the snapshot folder for my merge replication I have tried:

select top 5 * FROM sysmergepublications pub
select *       FROM dbo.sysmergeSubscriptions

but that does not show me the folder name and how needs permissions on it where can I get this information from using t-sql?

I have setup a merge replication publication and added a subscriber but when I view the synchronization status I just get the error message: "No subscription is on this publication or article". Looking through replication monitor shows this as an Error 14050 but I haven't been able to find much detail on why this is happening. I had the same tables replicating using transactional replication without any issues, but need to use merge since the subscriber gets updates that need to push back to the publisher. I have tried turning on verbose logging but it's not writing a log so I can't tell what's happening. I have also tried deleting and rebuilding both the publication and subscription but continue to get the same message. What do I need to do to resolve this? Thanks

I am restoring databases to each side of my transactional and merge replications. Basically the publication and the subscriber are the same database initially. I still need to run the snapshot agent to get things started. How can I run the snapshot without runnin the bcp commands, I mean, without copying the data accross? I am ok with the data structure to be copied over though.

This type of error is unfortunately not uncommon. One report of many is this one on Stack Overflow: https://stackoverflow.com/questions/11360331/sql-server-merge-replication-error-the-schema-script-xxx-sch-could-not-be-pro The problem is that in every report of this error I can find the error message displays a file name ending in ".sch" as part of it, here it displays part of a function that is being replicated. The function in question is using SUSER_SNAME(), which is also used for filtering rows in the replicated tables. Imagine that the function in question has the following code (this is an example, I'm not going to post the actual code publicly): ALTER FUNCTION [dbo].[GetDataForCurrentUser] ( ) RETURNS TABLE AS RETURN ( select data_id, data_type_id, data_key, data_value from UserDataTable where user_name = REPLACE(SUSER_SNAME(), 'OURDOMAIN\', '') and data_is_valid = 1 order by data_key ) Then the actual error that appears is the following: The schema script ', '') and data_is_valid = 1 order by data_key )' could not be propagated to the subscriber In other words, instead of a file name what appears is the last part of the function in question, starting after the string literal, in single quotes. It seems like either the use of SUSER_SNAME() is causing the problem, or perhaps the backslash that appears as part of the function. However I am not aware of reasons not to use that function in a user-defined function, or a need to escape backslashes in string literals. Also, the function itself works with no problems when actually called. Is there a problem with the function definition or is this a bug in replication itself?

I have two databases on my local machine and the server. I want to sync them whenever they are being changed. For my scenario I'm using SQL Server replication. I use merge type to sync these databases twoway. I'm placing the distributor in my server and I have created publisher is my server. And the publisher is running when I'm reaching it from replication monitor. On my local machine, I have created a subscriber to that publisher but I can't connect to my publisher. I followed these steps to solve this problem. - I have turned off my firewalls in both machines - SQL Server browsing is running in both machines - Remote connection is enabled on both - TCP is enabled on both - I don't have any errors in SQL Server logs in neither of them - SQL Server service is running on both I'm stuck in a loop and do not know what to do to make it work. Do you have any ideas how I can make it work? Is it possible at all to connect them when I'm using local machine and server? ***UPDATE*** I Find out that they have to be in the same network and as my local machine doesn't have any ip address I tried to use OpenSSH for reverse tunneling and i didn't reach any result ***UPDATE_2*** after i tried to connect via static ip address, i can ping my subscriber on my publication machine via telnet but in replication monitor i get this error

The full text of the message is "Running the Merge Agent process at this time would exceed the maximum number of processes allowed by this publication. The process will run when resources become available." It couldn't be put on the title. We have a merge publication on a database. While a subscriber is synchronising, the above message often appears and synchronisation stalls for several minutes and might even time out. The publication's "maximum concurrent processes" setting is 20, and using SSMS's activity monitor I see only 4 processes that seem to be related to this publication. In any case, there is only one subscriber for the publication and so it seems unlikely it could be using 20 processes. Am I correct in my assumption above (that one subscriber shouldn't use 20 processes) and, if I am, what could be causing this problem? Searching Google for the message's text brings only two results. This is a merge publication where both publisher and subscriber use SQL Server 2008 R2.

We are having to redo a publication/subscription and while running the snapshot agent it stalls at 48% with the message "The process is running and is waiting on a response from the server." Eventually it times out. I increased the timeout, but even after 4 hours it's still stuck at the same place. I can't figure out what it's 'waiting on' or how to get it to progress further. I've tried deleting and recreating the publication from scratch but same results. Anyone have ideas on how I might troubleshoot it to figure out what the issue is?

I have been trying out merge replication with SQL Server databases, and was just wondering how merge replication works on SQL Server CE? For instance merge replication with SQL Server databases normally relies on triggers, and SQL Server CE doesn't have any triggers. Also do tables such as MSmerge_conflicts_info and sysmergesubscriptions etc exist with the SQL Server CE version of merge replication?

I have 2 databases, A and B. database A contains a subset of database B. I want to replicate any changes on database A (insert, update, delete one way replication) to database B without affecting any of database B own data. Both databases have the same schema. I think merge replication would be my best bet. Be aware that SQL Server is the latest version running on Ubuntu 20.04