SQL Replication issue when trying to add Subscriber through SQL job

Question

Post reply

SQL Replication issue when trying to add Subscriber through SQL job

cooldude001

Mr or Mrs. 500

Points: 525
More actions
March 6, 2020 at 10:06 am

#3731819
I'm using SQL 2017 ( Developer Edition), our environment has a requirement of setting up the replication weekly. (Because we had to reload the database back to an older version).
I've setup the transactional replication with initialize with backups option and I'm trying set them all up with SQL agent jobs.
My setup and scripts run fine, except when I add subscriber through SQL agent. SQL agent and the job will be in hung state and I had to restart agent, whenever I try to run the job from Publisher (with below code).
There's no permissions issue, both SQL server instances share a common service account and it is local admin/sysadmin.
DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
SET @publication = 'PublicationName'
SET @subscriber = 'SubscriberServerName'
SET @subscriptionDB ='DBName'
Declare @files table (ID int IDENTITY, FileName varchar(100))
Declare @Filename varchar(1000);
insert into @files execute xp_cmdshell 'dir FolderPath\ /b'
select @Filename =Filename from @files where Filename is not null
Set @Filename = 'Folderpath\'+@Filename
USE DBName
EXEC sp_addsubscription
@publication = @publication,
@subscriber = @subscriber,
@destination_db = @subscriptionDB,
@sync_type= N'initialize with backup',
@backupdevicetype='Disk',
@backupdevicename=@Filename,
@subscription_type = N'push',
@update_mode = N'read only';

Can anyone please help me with this issue.
- This topic was modified 4 years, 8 months ago by cooldude001.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

1st question - how big is the database? just asking in case a normal snapshot might be a better option

MVDBA

cooldude001 Mr or Mrs. 500 Points: 525 More actions · Answer 2

Database size is 650gb. I've tried using snapshot method, creating the snapshot was very slow.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

how long does the job hang for before you kill it - a 650GB restore across the network will take a bit of time … just thinking

also with backup and restore you get every table? are all tables included as articles in replication ?

just trying to see if we can find a better way

MVDBA

cooldude001 Mr or Mrs. 500 Points: 525 More actions · Answer 4

The Job and SQL agent gets hung in a few seconds(<10 sec).

Weird thing is, when I run sp_addsubscription manually in SSMS, it is successful. The issue is only when I run it through SQL job.

Yes, all the tables are included in the replication.

Michael L John One Orange Chip Points: 27055 More actions · Answer 5

You are using developer edition in a production enviroment?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 6

cooldude001 wrote:

The Job and SQL agent gets hung in a few seconds(<10 sec).
Weird thing is, when I run sp_addsubscription manually in SSMS, it is successful. The issue is only when I run it through SQL job.
Yes, all the tables are included in the replication.

ok but how long does it hang for before you kill it? - i'm thinking that it's hanging until the restore finishes, but also michael has a point... dev edition on a production envronment? or is this a dev/test scenario

MVDBA