16
votes
Accepted
Length of LOB data (2135980) to be replicated exceeds configured maximum 65536
This configuration can be changed using Management Studio by right-clicking the server -> Properties -> Advanced tab -> Miscellaneous -> Max Text Replication Size.
Or you may want to try :
EXEC sys....
- 887
8
votes
How to fix a messed up replication on MS SQL Server
I had a mess with replication and solved it with this
DECLARE @subscriptionDB AS sysname
SET @subscriptionDB = N'DBName'
-- Remove replication objects from a subscription database (if necessary).
...
- 181
8
votes
Unable to truncate transaction log, log_reuse_wait_desc - AVAILABILITY_REPLICA
If you do this:
SELECT * FROM sys.databases
And the log_reuse_wait_desc shows AVAILABILITY_REPLICA, that means SQL Server is waiting to send log data to one of your Always On Availability Group ...
- 43.2k
8
votes
Sql Server : How to query list of subscriptions and Articles (for a given publication) from Publisher
To get subscription list from a publisher database, you can run below tsql :
use publisherDB;
select
db_name() PublisherDB
, sp.name as PublisherName
, sa.name as TableName
, UPPER(srv.srvname) ...
- 62.4k
7
votes
Accepted
is it possible to alter the primary key of a table (from non clustered to clustered) in the subscriber only and not break the replication?
No, It is not possible.
when the table is involved in replication it does not allow you to drop the primary key.
below is an example as how I have changed the primary key of a replicated table:
...
- 16.6k
7
votes
Accepted
Can not drop table due to replication error, though table is not being published
TL;DR: This error can occur if you restore (or move) a database that was previously replicated to a server of a lower version and/or patch level and then re-enable replication. See below for solutions ...
7
votes
Accepted
Domain Account or Windows account for Transactional Replication
Short Answer: Nothing at all is wrong with a domain account. When all the computers participating are in the same domain, go that way. Easier to manage and maintain and secure.
Your confusion here ...
- 18.2k
6
votes
Accepted
The Distributor has not been installed correctly. Could not enable database for publishing
I think I have nailed it,
what I did is very simple
A call to the procedure sp_adddistpublisher was missing.
Configures a Publisher to use a specified distribution database. This
stored ...
- 16.6k
6
votes
Accepted
Why does sp_replmonitorhelpsubscription not work without parameters?
Checking the code reveals that :
-- validate @publication_type
--
if (@publisher_db is NULL or @publication is NULL)
and @publication_type is NULL
begin
raiserror(20587, ...
- 62.4k
5
votes
is it possible to alter the primary key of a table (from non clustered to clustered) in the subscriber only and not break the replication?
Add the following checks before starting to deploy Marcello's scripts:
Make sure that immediate_sync and allow_anonymous are set to FALSE (0) to avoid the creation of the full snapshot and not just ...
- 171
5
votes
How can I determine the last synchronization time for a SQL Server replication subscription?
From the subscriber side, you should be able to run EXEC dbo.sp_helppullsubscription to get some level of detail.
As an example:
use [SomeDB]
EXEC dbo.sp_helppullsubscription
@publisher = N'...
- 70.5k
5
votes
Accepted
SQL Server - Linked Server error - Could not connect to server XYZ because
The linked server was also used for replication.
The order of Subscribe and Create linked server was the issue.
Reproduce the error:
1. Drop the subscription
2. Drop the linked server
3. ...
- 581
5
votes
Accepted
Can I use only 1 user for SQL Server replication?
If you use 1 user, you typically end up giving that user more access than it needs. By having the example use 4 different users, it helps demonstrate the good practice of using dedicated accounts for ...
- 16.2k
5
votes
Accepted
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?
Yes. No. No.
...
- 47.6k
5
votes
Where is located sp_msreplcheck_publish stored procedure?
If is hidden from general view but, if you connect using a Dedicated Administrator Connection, you can see it in master via:
USE [master];
GO
SELECT OBJECT_DEFINITION(OBJECT_ID(N'sys....
- 182k
5
votes
Subscription Initialization throws Error "The option "INLINE=ON" is not valid for this function" which is incorrect
Refer this article The only solution worked for me was drop and recreate the UDF after upgrading to SQL 2019.
The inline_Type column from Sys.sql_modules will be 1 after your upgrade to SQL 2019.
...
- 51
5
votes
Accepted
Changing collation of SQL Database in Production environment to minimize downtime
I just tried this and it worked in Transactional Replication:
Server Level Collation is Finnish_Swedish_CI_AS
Database Level Collation of both sides of the databases are the same which is ...
5
votes
Accepted
Running sp_repldone on a secondary node of an Availability Group with @reset = 1 not working
For background, I'm assuming this is a custom CDC solution, and the regular CDC jobs aren't running to populate the change tables and run sp_repldone, and so you must run sp_repldone to enable the log ...
- 47.6k
4
votes
Replication Monitor Information using T-SQL
You can get information you need by querying the dbo.MSReplication_monitordata table from a distribution database with this script, for all publications:
USE distribution
SELECT status AS ...
- 219
4
votes
Accepted
Replication on Always On
You can't have two Distribution databases. Put the distribution database in a Failover Cluster Instance that way it is highly available.
Maybe one day MS will allow the distribution database to be in ...
- 5,986
4
votes
Accepted
Availability Group of SQL Servers 2012, 2012 and 2016 possible?
1) --> What are potential problems if we add SQL Server 2016 as a third node to existing Availability Group that has two SQL Servers 2012?
Once you failover(or failover happens) to 2016, you wont be ...
- 62.4k
4
votes
Accepted
Syncronizing process difference between replication and alwayson high availability
Transactional Replication uses the TDS protocol and applies changes using SQL. The connections are established from the Distributor for Push Subscriptions, and from the Subscriber for Pull ...
- 47.6k
4
votes
Efficiently bulk upsert unrelated rows
To elaborate on my comment, I would use a set based approach for better performance with error handling, here is how I would do it. I mixed the two common methods, using JOIN and NOT EXISTS / EXISTS.
...
- 3,588
4
votes
Accepted
How to pause the subscriber end of transactional replication
Replication runs using "Agents"--essentially external executables that do the work. For transactional Replication, you have three agents:
Snapshot Agent - this is the process who does the snapshot to ...
- 16.3k
4
votes
SQL Server 2014 AOAG + Replication?
IN SQL Server 2014 and earlier, the replication jobs will not continue to work after failover if you put the Distribution Database in an AG.
If you upgrade to SQL Server 2016 or 2017, this becomes a ...
- 47.6k
4
votes
DBCC SHRINKFILE on the distribution database
It is ok to shink the datafile, there WON'T be any data loss.
The "issues" when shrinking could be performance, during the rellocation of data pages. Also if you know that your DF will grow again ...
- 472
4
votes
Failover strategy for SQL Server 2016 Standard Edition
Separate from your Replication solution, could use a Failover Cluster Instance or Basic Availability Group for HA of your publisher.
Or, if you have a stomach for obscure replication solutions, you ...
- 47.6k
4
votes
Accepted
One-directional database replication without delete on target database?
Yes you can choose to not replicate delete statement.
Try this:
- 4,685
4
votes
Accepted
Can you backup a replicated database?
Yes, you can and you should be backing up your replicated databases (inclusive of the system generated database called distribution) just the same as any other databases, to the tolerance of data loss ...
- 39.5k
3
votes
Accepted
SQL Server replication error "profile name is not valid"
It seems that you initialized your transactional replication using database backup. With database backup, there is no schema difference between the publisher and subscriber.
There might be trigger ...
- 46
Only top scored, non community-wiki answers of a minimum length are eligible