Sitemap

Amicuk Programming Answers

Problems starting Replication on large databases - SQL 2012

-0001-11-30   Views:0

Advertisement

Hi all, I have been battling SQL Server for a week trying to get replication up and running, now I realize that most probably I am doing something wrong and I hope that one of you can help pointing me in the right direction. I want to start replicati

Hi all,
I have been battling SQL Server for a week trying to get replication up and running, now I realize that most probably I am doing something wrong and I hope that one of you can help pointing me in the right direction.
I want to start replication of a big (180GB) database in my lab environment and every time the end result is errors of the type "The row was not found at the Subscriber when applying the replicated command. (Source: MSSQLServer, Error number: 20598)".
There are more than one database to be replicated and the smaller ones (<= 4GB) have started without problems, a medium database of 21GB had one similar error and has been running for a few days since I found and inserted the missing data in the subscriber
DB.
A couple of facts:
- I have set up the distributor as a remote distributor since the publishers are mirrored databases.
- I assume the distributor setup is OK since all the replicated databases are taking the same 'path' and the smaller ones are working.
- The distributor and the subscriber are set up on the same server.
- All in all the following procedure takes about 3.5-4 hours.
- The database is running in Full Recovery mode
- All log backups on the publisher are turned off for the duration.
Here's what I do:
On the publisher
Create the publication and add all the articles
use [Zaragoza]
exec sp_replicationdboption @dbname = N'Zaragoza',
@optname = N'publish',
@value = N'true'
GO
exec [Zaragoza].sys.sp_addlogreader_agent @job_login = N'NS-LAB-DB-40\SqlServiceUser',
@job_password = N'********', -- Modify Here
@publisher_security_mode = 1,
@job_name = null
GO
-- Adding the transactional publication
exec sp_addpublication @publication = N'Zaragoza_Full',
@description = N'Transactional publication of database ''Zaragoza'' from Publisher ''NS-LAB-DB-38''.',
@sync_method = N'concurrent',
@retention = 0,
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'false',
@enabled_for_internet = N'false',
@snapshot_in_defaultfolder = N'true',
@compress_snapshot = N'false',
@ftp_port = 21,
@allow_subscription_copy = N'false',
@add_to_active_directory = N'false',
@repl_freq = N'continuous',
@status = N'active',
@independent_agent = N'true',
@immediate_sync = N'true', -- Needs TRUE
@allow_sync_tran = N'false',
@allow_queued_tran = N'false',
@allow_dts = N'false',
@replicate_ddl = 1,
@allow_initialize_from_backup = N'true', -- Modify Here
@enabled_for_p2p = N'false',
@enabled_for_het_sub = N'false'
GO
/* SHOULD not need this since no snapshots will be generated
exec sp_addpublication_snapshot @publication = N'Zaragoza_Full',
@frequency_type = 1,
@frequency_interval = 1,
@frequency_relative_interval = 1,
@frequency_recurrence_factor = 0,
@frequency_subday = 8,
@frequency_subday_interval = 1,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 0,
@active_end_date = 0,
@job_login = N'NS-LAB-DB-40\SqlServiceUser',
@job_password = N'********',
@publisher_security_mode = 1
exec sp_addarticle @publication = N'Zaragoza_Full',
@article = N'__MigrationLog',
@source_owner = N'dbo',
@source_object = N'__MigrationLog',
@type = N'logbased',
@description = null,
@creation_script = null,
@pre_creation_cmd = N'drop',
@schema_option = 0x000000000803509F,
@identityrangemanagementoption = N'manual',
@destination_table = N'__MigrationLog',
@destination_owner = N'dbo',
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_MSins_dbo__MigrationLog',
@del_cmd = N'CALL sp_MSdel_dbo__MigrationLog',
@upd_cmd = N'SCALL sp_MSupd_dbo__MigrationLog'
GO
-- MANY more "EXEC sp_addarticle"....
On the publisher
I recompile the subscription SPs since I had problems with them once.
I use the output from the following statement:
USE Zaragoza;
EXEC sp_scriptpublicationcustomprocs @publication=N'Zaragoza_Full';
On the publisher
I backup the database & log
BACKUP DATABASE Zaragoza TO DISK = 'E:\Backup\Zaragoza_FULL.bkp' WITH INIT, STATS=5;
BACKUP LOG Zaragoza TO DISK = 'E:\Backup\Zaragoza_LOG.bkp' WITH INIT, STATS=5;
On the subscriber
Copy the backups from the publisher
CD /D E:\Backup
COPY /Y \\NS-LAB-DB-38-mirror\Backup\Zaragoza_FULL.bkp .
COPY /Y \\NS-LAB-DB-38-mirror\Backup\Zaragoza_LOG.bkp .
On the subscriber
Restore the database and log backups
USE [master]
GO
RESTORE DATABASE Zaragoza_Sub
FROM DISK = 'E:\Backup\Zaragoza_FULL.bkp'
WITH NORECOVERY, REPLACE, STATS=5,
MOVE 'Zaragoza' TO 'I:\mssql_repl\Zaragoza_Sub\Zaragoza_Sub.mdf',
MOVE 'Zaragoza_log' TO 'I:\mssql_repl\Zaragoza_Sub\Zaragoza_Sub_log.ldf';
GO
RESTORE LOG Zaragoza_Sub
FROM DISK = 'E:\Backup\Zaragoza_LOG.bkp'
WITH RECOVERY, STATS=25;
GO
On the subscriber
Remove all triggers since I noticed that they interfered with the replication SPs
USE Zaragoza_Sub;
DECLARE @Trigger nvarchar(100),
@SQL nvarchar(2000);
DECLARE curTriggers CURSOR
LOCAL FAST_FORWARD FOR
SELECT DISTINCT tr.name TriggerName--, te.type_desc TriggerType, ob.name TableName, tr.is_disabled, c.text
FROM sys.triggers tr INNER JOIN
sys.trigger_events te ON tr.object_id = te.object_id INNER JOIN
sys.objects ob ON tr.parent_id = ob.object_id;
OPEN curTriggers;
FETCH NEXT FROM curTriggers INTO @Trigger;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'DROP TRIGGER dbo.' + @Trigger + ';';
EXEC sp_executesql @SQL;
FETCH NEXT FROM curTriggers INTO @Trigger;
END;
CLOSE curTriggers;
DEALLOCATE curTriggers;
On the publisher
Add the subscription and the push agent
-----------------BEGIN: Script to be run at Publisher 'NS-LAB-DB-38'-----------------
use [Zaragoza]
exec sp_addsubscription @publication = N'Zaragoza_Full',
@subscriber = N'NS-LAB-DB-40',
@destination_db = N'Zaragoza_Sub',
@subscription_type = N'Push',
@sync_type = N'initialize with backup', [email protected]_type = N'replication support only',
@status = N'active', -- ADD this line
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0,
@backupdevicetype=N'disk',
@backupdevicename=N'E:\backup\Zaragoza_LOG.bkp';
exec sp_addpushsubscription_agent @publication = N'Zaragoza_Full',
@subscriber = N'NS-LAB-DB-40',
@subscriber_db = N'Zaragoza_Sub',
@job_login = N'NS-LAB-DB-40\SqlServiceUser',
@job_password = N'********', -- Modify Here
@subscriber_security_mode = 1,
@frequency_type = 64,
@frequency_interval = 0,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 0,
@frequency_subday = 0,
@frequency_subday_interval = 0,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 20140618,
@active_end_date = 99991231,
@enabled_for_syncmgr = N'False',
@dts_package_location = N'Distributor';
GO
-----------------END: Script to be run at Publisher 'NS-LAB-DB-38'-----------------
The worst part is that this is only a LAB system, when I apply this to Live I will have to deal with a 24/7 environment with 10,000+ transactions a day, so downtime is a total No-No.
If you are missing any details do not hesitate to ask, I will gladly provide all the details I have.
Have a great day
DS

The replay answer
Advertisement
Hi Ashwin,
I have tried a few things these last couple of days and here's where I'm at.
I looked through the publisher database for the missing row so I could just copy it over but the row was nowhere to be found. After talking to the developers I found out that the table in question has a clean-up job that deletes records when they are finished
- OK, no problem, the replication should take care of that.
When I then look at the trace from the Distributor I find a bunch of records where the ID of the missing row is used. However, I find it peculiar that the replication first does updates to a record, then deletes it only to continue doing updates afterwards.
A sample from the Trace will explain better:
The SPs are
sp_MSupd_dboMessageDialog => This SP tries to do the failing UPDATE on the table, the second to last parameter is the RowId in the table (1075152)
sp_MSdel_dboMessageDialog => This SP tries to DELETE from the table, the only parameter is the RowId in the table (1075152)
There is one other SP in the replication of this article (table) and it is called sp_MSins_dboMessageDialog and it should be used for inserting records into the table. However it is never called.
RowNumber
EventClassName
TextData
EventSequence
StartTime
EndTime
2899078
RPC:Starting
exec [sp_MSupd_dboMessageDialog] default,default,default,default,0,default,'2014-08-13 12:10:56.503',1075152,0x50
74255940
2014-08-13 14:22:15.763
NULL
2899372
SP:Starting
exec [sp_MSupd_dboMessageDialog] default,default,default,default,0,default,'2014-08-13 12:10:56.503',1075152,0x50
74256234
2014-08-13 14:22:16.270
NULL
2899446
SP:Completed
exec [sp_MSupd_dboMessageDialog] default,default,default,default,0,default,'2014-08-13 12:10:56.503',1075152,0x50
74256312
2014-08-13 14:22:16.270
2014-08-13 14:22:17.923
2899447
RPC:Completed
exec [sp_MSupd_dboMessageDialog] default,default,default,default,0,default,'2014-08-13 12:10:56.503',1075152,0x50
74256313
2014-08-13 14:22:15.763
2014-08-13 14:22:17.923
2899462
RPC:Starting
exec [sp_MSdel_dboMessageDialog] 1075152
74256328
2014-08-13 14:22:17.927
NULL
2899558
SP:Starting
exec [sp_MSdel_dboMessageDialog] 1075152
74256420
2014-08-13 14:22:17.940
NULL
2899606
SP:Completed
exec [sp_MSdel_dboMessageDialog] 1075152
74256468
2014-08-13 14:22:17.940
2014-08-13 14:22:17.940
2899607
RPC:Completed
exec [sp_MSdel_dboMessageDialog] 1075152
74256469
2014-08-13 14:22:17.927
2014-08-13 14:22:17.940
2915054
RPC:Starting
exec [sp_MSupd_dboMessageDialog] default,default,default,default,0,default,'2014-08-13 12:10:56.503',1075152,0x50
74272198
2014-08-13 14:22:56.577
NULL
2915118
SP:Starting
exec [sp_MSupd_dboMessageDialog] default,default,default,default,0,default,'2014-08-13 12:10:56.503',1075152,0x50
74272262
2014-08-13 14:22:56.587
NULL
2915353
SP:Completed
exec [sp_MSupd_dboMessageDialog] default,default,default,default,0,default,'2014-08-13 12:10:56.503',1075152,0x50
74272497
2014-08-13 14:22:56.587
2014-08-13 14:22:56.597
2915354
RPC:Completed
exec [sp_MSupd_dboMessageDialog] default,default,default,default,0,default,'2014-08-13 12:10:56.503',1075152,0x50
74272498
2014-08-13 14:22:56.577
2014-08-13 14:22:56.597
2994616
RPC:Starting
exec [sp_MSupd_dboMessageDialog] default,default,default,default,0,default,'2014-08-13 12:10:56.503',1075152,0x50
74352996
2014-08-13 14:26:13.227
NULL
2994701
SP:Starting
exec [sp_MSupd_dboMessageDialog] default,default,default,default,0,default,'2014-08-13 12:10:56.503',1075152,0x50
74353159
2014-08-13 14:26:13.647
NULL
2994749
SP:Completed
exec [sp_MSupd_dboMessageDialog] default,default,default,default,0,default,'2014-08-13 12:10:56.503',1075152,0x50
74353266
2014-08-13 14:26:13.647
2014-08-13 14:26:13.647
2994750
RPC:Completed
exec [sp_MSupd_dboMessageDialog] default,default,default,default,0,default,'2014-08-13 12:10:56.503',1075152,0x50
74353267
2014-08-13 14:26:13.227
2014-08-13 14:26:15.163
2995292
RPC:Starting
exec [sp_MSdel_dboMessageDialog] 1075152
74353810
2014-08-13 14:26:15.240
NULL
2995331
SP:Starting
exec [sp_MSdel_dboMessageDialog] 1075152
74353849
2014-08-13 14:26:15.250
NULL
2995389
SP:Completed
exec [sp_MSdel_dboMessageDialog] 1075152
74353911
2014-08-13 14:26:15.250
2014-08-13 14:26:15.603
2995390
RPC:Completed
exec [sp_MSdel_dboMessageDialog] 1075152
74353912
2014-08-13 14:26:15.240
2014-08-13 14:26:15.603
3000727
RPC:Starting
exec [sp_MSupd_dboMessageDialog] default,default,default,default,0,default,'2014-08-13 12:10:56.503',1075152,0x50
74359306
2014-08-13 14:26:29.413
NULL
3000795
SP:Starting
exec [sp_MSupd_dboMessageDialog] default,default,default,default,0,default,'2014-08-13 12:10:56.503',1075152,0x50
74359374
2014-08-13 14:26:29.423
NULL
3000843
SP:Completed
exec [sp_MSupd_dboMessageDialog] default,default,default,default,0,default,'2014-08-13 12:10:56.503',1075152,0x50
74359422
2014-08-13 14:26:29.423
2014-08-13 14:26:29.427
3000844
RPC:Completed
exec [sp_MSupd_dboMessageDialog] default,default,default,default,0,default,'2014-08-13 12:10:56.503',1075152,0x50
74359423
2014-08-13 14:26:29.413
2014-08-13 14:26:29.427
3081879
RPC:Starting
exec [sp_MSupd_dboMessageDialog] default,default,default,default,0,default,'2014-08-13 12:10:56.503',1075152,0x50
74441576
2014-08-13 14:29:56.223
NULL
3082017
SP:Starting
exec [sp_MSupd_dboMessageDialog] default,default,default,default,0,default,'2014-08-13 12:10:56.503',1075152,0x50
74441868
2014-08-13 14:29:58.060
NULL
3082209
SP:Completed
exec [sp_MSupd_dboMessageDialog] default,default,default,default,0,default,'2014-08-13 12:10:56.503',1075152,0x50
74442059
2014-08-13 14:29:58.060
2014-08-13 14:29:58.067
3082210
RPC:Completed
exec [sp_MSupd_dboMessageDialog] default,default,default,default,0,default,'2014-08-13 12:10:56.503',1075152,0x50
74442060
2014-08-13 14:29:56.223
2014-08-13 14:29:58.070
3082470
RPC:Starting
exec [sp_MSdel_dboMessageDialog] 1075152
74442320
2014-08-13 14:29:58.097
NULL
3082471
SP:Starting
exec [sp_MSdel_dboMessageDialog] 1075152
74442321
2014-08-13 14:29:58.100
NULL
3082519
SP:Completed
exec [sp_MSdel_dboMessageDialog] 1075152
74442369
2014-08-13 14:29:58.100
2014-08-13 14:29:58.100
3082520
RPC:Completed
exec [sp_MSdel_dboMessageDialog] 1075152
74442378
2014-08-13 14:29:58.097
2014-08-13 14:29:58.400
3090751
RPC:Starting
exec [sp_MSupd_dboMessageDialog] default,default,default,default,0,default,'2014-08-13 12:10:56.503',1075152,0x50
74450665
2014-08-13 14:30:17.940
NULL
3090796
SP:Starting
exec [sp_MSupd_dboMessageDialog] default,default,default,default,0,default,'2014-08-13 12:10:56.503',1075152,0x50
74450712
2014-08-13 14:30:17.953
NULL
3090844
SP:Completed
exec [sp_MSupd_dboMessageDialog] default,default,default,default,0,default,'2014-08-13 12:10:56.503',1075152,0x50
74450760
2014-08-13 14:30:17.953
2014-08-13 14:30:17.957
3090845
RPC:Completed
exec [sp_MSupd_dboMessageDialog] default,default,default,default,0,default,'2014-08-13 12:10:56.503',1075152,0x50
74450761
2014-08-13 14:30:17.940
2014-08-13 14:30:17.953
Correct me if I'm wrong but this seems a bit weird. Why don't I see any INSERT to create the row again after the DELETE?
For the time being, and since this is a test system, I added the parameter "-SkipErrors 20598" to the Distribution Agent and restarted the agent. The replication started by skipping 20 records with that error and then continuing to work fine, as
suspected. I will have to verify with the developers and testers to see if all the data is still available.
Hopefully you will have some great insight into this matter and be able to explain what is happening.
Cheers!
DS

Go to See the other 12 answers

Problems starting Replication on large databases - SQL 2012

Category:DefaultRelease time:-0001-11-30Views:130

Hi all, I have been battling SQL Server for a week trying to get replication up and running, now I realize that most probably I am doing something wrong and I hope that one of you can help pointing me in the right direction. I want to start replicati[More]

Failover Cluster in SQL 2012 in not auto switch when stop service SQL

Category:DefaultRelease time:2015-10-11Views:130

Good Morning to all, I'm Mr.Tuyen from VietNam. I have a big problem with AlwaysOn High Availabity in SQL 2012. I setup 3 server and join Availabilty Group. Ex: Server A (Primary), Server B (Secondary), Cluster Qourum is only node. I have test: 1. St[More]

SQL 2012 AlwaysON High Availability for SharePoint 2013

Category:DefaultRelease time:2015-10-11Views:130

Our Company have 2 Webfront Servers for Sharepoint 2013 and one Database SQL 2012 Server. We got one more Server & we don't have Storage so need to configure Always On. There are Some Confusions: 1- Database Server is in production, so how much down[More]

How to handle SQL connection if password Active directory always change? (Connection using Active directory via network SQL 2012 )

Category:DefaultRelease time:-0001-11-30Views:130

I have 3 server (Web server, database sql 2012 server and Active directory). I'm using sqlsvr version 3.0,  PHP version 5.3 ,IIS version 7 and windows server 2008. Right now my php connection to SQL 2012 using AD id, so How to handle if password on a[More]

Fulltext search of binary files outside of database in SQL 2012/Server 2012

Category:DefaultRelease time:2015-10-11Views:130

This may be more of a Windows Server 2012 question but I'm not sure. I have a system that holds large amounts of binary files (pdf, doc, etc.).  The files themselves are held outside the database in a folder and a SQL table holds the metadata.  I use[More]

SQL 2012 and later will fail to publish a database for any tables with a default constraint that references a user defined function.

Category:DefaultRelease time:2015-10-11Views:130

Script will create database, 3 database objects and publish.  The error is due to the generation script to create the conflict tables that is not stripping out default constraints that reference a UDF.  As you can see below, the failure is on the gen[More]

ISSUE Sharepoint 2013 databases for reporting services on the second server SQL 2012

Category:DefaultRelease time:2015-10-11Views:130

Hello, I have server A: Operating system windows 2012 standard, SQL server 2012 standard instance: Sharepoint contains data for sharepint instance: Reporting should be contain databases for reporting Server B: Windows server 2012 standard contains in[More]

Moving Service Manager Database From SQL 2008 R2 SP2 to SQL 2012 SP1

Category:DefaultRelease time:-0001-11-30Views:130

Hi Guys, Just looking for some clarification about moving our Service Manager 2012 R2 database to a new SQL Server. Our Service Manager database is currently on a remote SQL server, shared with other databases, running SQL 2008 R2 SP2. It was origina[More]

SQL 2012 database behaves differently with a unique index and ignore_dup_key = on when inserting data. It works on SQL 2008 db

Category:DefaultRelease time:-0001-11-30Views:130

If you have a table with a unique index and ignore_dup_key = on and you INSERT rows into that table with an ORDER BY clause (because you want to control which of the duplicate key rows gets inserted),  the wrong row gets inserted in SQL2012.  It work[More]

SQL 2012 Transactional Replication Publisher is also AG Primary, AG fine, but Replication status continuously 'waiting for next Log backup'

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I have a SQL 2012 instance that hosts 3 databases that are publishers in (push) transactional replication to subscribers in other domains and are also primary in an Availability Group to other instances in the same domain.  The AG is healty (sync[More]

SQL 2005 to SQL 2012 Replication for SSRS

Category:DefaultRelease time:-0001-11-30Views:130

I recently successfully implemented one-way log shipping from a production SQL 2005 database to a new SQL 2012 database in the hopes that I can use the 2012 database to run SSRS 2012 on it. Unfortunately, the 2012 database is kept offline since log s[More]

Hot
hi, when i run my application i get the next message: java.io.IOException: CreateProcess: javaw -classpath "..." pruebas.Prueba2 error=267 anybody knows what kind of error is 267? thanks byeSorry, I don't have an answer for you. Maybe you've alr [More]
I have been playing around with an auto updating client for a project I am working on. I have been able to get the client to update fine and save a copy of the new client into where ever the original was run from using the below code: File file; Code [More]
Hi, I have written an application that reads emails and extracts information to be stored in a DB. Whilst this application has been in production for 3 years, we have recently noticed that it is having problems retrieving the recipients from some ema [More]
i cant unlock my iphone what do i do?Haileypage wrote: i cant unlock my iphone what do i do? What do you mean by  Unlock ..? What Troubleshooting steps have you tried..?Read other 5 answers [More]
My sons iPad will not switch on we have had it charge for several hours with no luckPerform a Reset... Try again... Reset  ( No Data will be Lost ) Press and hold the Sleep/Wake button and the Home button at the same time for at least ten seconds, un [More]
I have the following .ctl file and I am having problems creating a custom control out of it. It is a push button and LED tied together with a frame around it. How can I create this as one control that I can use in my other .vi's? ThanksOkay, I went a [More]
Hi I'm new to this and I have a question about printing our invoices by page count. We generate our invoice using Crystal 2008 we have invoices that can range from a single page to as many as 6 pages. I would like Crystal to preview/print the reports [More]
I recently downloaded a new book in iBooks on my iPad and got the error message, "It is formatted incorrectly or not in a format iBooks can open." In addition, some other iBooks that I have been reading now fail to open, with the message "t [More]
Hi guys, I'm making up a guitar pedal using the 12AX7A vacuum tube and I'm running a check for voltages and amps going into the tube using the multimeter. Basically checking the voltages going into terminal 1 shows as around 77V and the current is ar [More]
Good morning! I am just learning how to include audio in my Captivate files. Here's my scenario. I'd like the user to be able to read the information on a screen, then, click a button or a click box to play the audio. I can record just fine, but, whe [More]