Tag Info

Hot answers tagged

11

And nothing about the functions. Why is the function information missing in the actual plan? This is by design, for performance reasons. Functions that contain BEGIN and END in the definition create a new T-SQL stack frame for each input row. Put another way, the function body is executed separately for each input row. This single fact explains ...


7

It's highly unlikely you'll find a better merge replication solution than what's built-in. Merge replication in particular is hard to implement. I would suggest that if you're having issues with the current setup breaking, take it up with Microsoft. They should be very motivated to help you find the root cause of the problem so their customers are happy, ...


5

So are you telling me that every user of my database has to have sysadmin access if they are going to be doing deletes? No, as per BOL's documentation on sp_trace_generateevent: User must have ALTER TRACE permission.


5

Basically, they say that the application should never bring into its domain something that is used by replication only (it's only "DBA stuff" for them). I agree completely. But... the primary key isn't only used for replication (presumably the application uses it in some way). The argument makes no sense in this context. In any event, as far as I'm ...


4

This is a complicated situation, but I think that the best solution would be to make an additional table (which I'll call UserFilters), just to hold the consolidated data from the Road, UserRegion and SyncSetting tables in a single combined row format and then to Join your Replication Filter to that table instead. This UserFilters table would need to be ...


4

Please check for orphaned rows for deleted subscriptions in sysmergesubscriptions and delete them. For example, the subscriber that this is failing on may have duplicate entries in sysmergesubscriptions, one being orphaned from a previously deleted subscription. This has been the cause of this error and solution for clients of mine in the past, ymmv. I ...


4

This is the sort of thing that Microsoft Consulting Services can help you with. They know replication really well, and setting up large merge replication farms needs to be done just right or the whole thing will come crashing down. If there are specific errors that need troubleshooting you can post those here so that they can be figured out, but if it's ...


3

When using Merge with identity columns — will need to specify an identity range management option of either automatic or manual so that duplicates do not occur. With automatic, replication will automatically manage the assignment of identity ranges for nodes participating in the Merge topology. With manual, you will need to manually assign "islands" of ...


3

The prefered method would be to alter the merge agent profile. See this link for a detailed explaination on how to do that: http://msdn.microsoft.com/en-us/library/ms152515.aspx The setting will take effect the next time the merge agent runs.


3

You need to inspect the Conflict Viewer and observe the conflict types and the conflicting row values. Something to note is that the Conflict Winner column will display the winning row values which are pulled from the base tables. If you think about it, these values are volatile and can change, so the values you see in the Conflict Winner column may not be ...


3

A few thoughts. It sounds to me like you may be able to get away with a couple options if replication is what you end up with. First a word of caution - Replication isn't something that should just be configured in production and enabled/used unless you have some experience with it. This becomes truer with more important workloads and busier systems. ...


3

It turns out that the Import wizard ( because it is considered like a Bulk Insert or BCP ) is disabling replication triggers on the tables they are loading data to, which prevents Merge Replication from detecting new records. This article: Bulk loading data into a merge replicated table explains this with more details. One workaround is to use the ...


3

You should be able to apply the SP3 without dropping any merge replication publications. The order of upgrade should be to upgrade (1) distributor, then (2) publisher, then (3) subscribers. The SP3 is for SQL Server 2008, not the SQL CE 3.5 mobile tools. Since you are running merge replication then I assume that you have SQL CE 3.5 SP2. So all should be ...


2

No, sequences are not valid as articles for any type of replication. I don't have a good reason for you (yet). But I'm curious what the purpose of replicating the sequence itself would be. If your intention is to utilize the same sequence on the subscriber to populate other tables there that aren't replicated, this wouldn't really work unless you had some ...


2

The only answer here, unfortunately is that mirroring is only supported for the pulbisher and only if the distributor is on a different server from with the principal or mirror. I would recommend a dedicated server for the distributor, but even using the subscriber server would work. Database mirroring is not supported for distributors or for subscribers, ...


2

There is a lot of information here, http://msdn.microsoft.com/en-us/library/ms151769(v=sql.105).aspx And here, http://msdn.microsoft.com/en-us/library/ms152502(v=sql.105).aspx So the thing to be aware of, because I was using parameterised filters the snapshot folder above doesn't contain any data, only schema. Also I was not filtering correct using ...


2

Straight from Advanced Merge Replication Conflict Detection and Resolution: The Conflict Viewer displays information from three system tables: Replication creates a conflict table for each table in a merge article, with a name in the form MSmerge_conflict__. Conflict tables have the same structure as the tables on which they are based. A row in one of ...


2

This is now a known bug (present only in SQL 2012), that Microsoft is currently addressing. Workaround is to give user ALTER TRACE permission until is resolved. You can track status on Connect.


2

This lock compatibility matrix can shed some light. Note if granted mode is S (shared read), transaction requesting for U (can update) will be granted access. Also note same is true other way, i.e. U is existing lock and request for S is granted. X (Exclusive access )is not compatible with neither S not U. Also U is not compatible with U i.e. Existing ...


2

I don't know how to get past the error (I'm not sure how you're trying to "run" the subscriber), but you obviously can't use Agent to distribute changes back to the publisher, because SQL Server Express does not include Agent. This thread seems to imply you can schedule the task outside of SQL Server using replmerg.exe from the command line: replmerg.exe ...


2

Check the history of the agent job for the Snapshot agent on Whatever server is running as the Distributor. Drill down into the history of the agent job and you should find an error. (The error is often listed in the step preceeding the one that is retrying). Will probably be lack of space, etc. If this does not reveal anything useful then you may need to ...


1

I have run into this in the past. In my experience typically this will resolve it's self by just restaring the sync process. From the client in a pull situation, or restart from the publisher in a push situation. In short the application of DDL changes ran into a conflict but restarting the sync will usually resolve the issue. And no, it doesn't start ...


1

We are using a merge replication topology between our servers where: Initial snapshot is made available to subscribers through ftp further data exchange (in our case, merge sync between servers) is done through web replication Here is a script I once proposed to launch such a replication. You will notice that one of the sp_addmergepullsubscription_agent ...


1

Intriguing question... as long as you have an actual scenario to apply it to it wouldn't be too difficult to figure out. Perform your BCP process, run sp_addtabletocontents and then before the subscriber syncs verify the existence of the records in the MSmerge_current_partition_mappings and MSmerge_past_partition_mappings tables. According to this ...


1

Please follow the steps in Create a Snapshot for a Merge Publication with Parameterized Filters to generate a new snapshot. You must first generate a standard snapshot, then generate a Subscriber-specific partitioned snapshot. After generating the snapshots, mark the Subscriber for reinitialization and start the Merge Agent to pull down the schema and data ...


1

You can use sp_showlineage and sp_showcolv to inspect the lineage and columns that have been changed. The sp_showcolv results contain a column, colidx, which is the index of the column(s) that were changed for a given version of the row. If you need to take a more proactive approach, you can setup an auditing scheme. I've provided an example of how to ...


1

You should use a uniqueidentifer - a GUID - instead of an integer identity. Then both databases will have uniquely identifiable row ID that is guaranteed to be unique. See http://technet.microsoft.com/en-us/library/ms151206(v=sql.90).aspx


1

I'm not convinced about the publication article limitation of 256. If we look here, http://social.msdn.microsoft.com/forums/en-US/sqlreplication/thread/f3f505ff-7a62-4131-badb-2ecf58f20808/ It is talking about a particular query that causes issues on SQL 2005 when you have more than 256 articles. First I ran the stored procedure sp_MSmakesystableviews. It ...


1

The first link you provided is official documentation for SQL Server 2012. http://msdn.microsoft.com/en-us/library/ms143432.aspx The maximum number of articles supported in Merge Replication is 256. This means that Microsoft will ensure up to 256 articles will work as expected. I'm able to exceed 256 articles as well but I wouldn't do it in production. ...


1

You could do it this way: Add the new field. Force a software upgrade that includes the logic to insert new data into the new field and to read the data from the new field if it is not null, otherwise to read that field from the old field. ( in case of update: you read the value from the old field then do an update to the new field ) Wait for a while, ...



Only top voted, non community-wiki answers of a minimum length are eligible