SQl Publication disappears

Hi Experts:
I have a SQL 2008R2 Distributor/Publisher replicating to a SQL 2014 subscriber. Its replicating about 200Gb. I set up the distributor and local publication on the 2008 server, then created the (pull) subscription the 2014 server. all worked well when i ran it manually. I scheduled it to run over night but when i cam in in the morning the local publication on the sql 2008 server was gone. I've recreated this sequence twice now and cant find any solution as to why the publication job is disappearing.
I'm fairly new to replication, so I'm not familiar with what all the various parameters do. Its a good possibility I'm not setting some retention setting properly.

Your help would be appreciated.
Thanks!

Manny Silva
mannymsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ZberteocCommented:
What kind of replication did you create?
mannymsAuthor Commented:
Its a snapshot replication.
Because we receive vendor database backups everynight to our staging server, we want to push out only the tables needed to the ETL server for processing to the data warehouse. This eliminates the need for linked servers.
ZberteocCommented:
So you restore and overwrite that database nightly?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

mannymsAuthor Commented:
Yes, the production servers will be restored nightly ( overwritten) and then the replication job will move the required tables to the ETL server for processing.

I've played with the idea of moving the .BAK files to the other server, but when restored its > 1tb.
THis way I only move the 40 tables i need for our Data warehouse.(approx 200gb).
I suppose i could write an ssis package to do the same with a table truncate/bulk insert for each table, but this was the first process I tried.
Figured I'd get familiar with replication in any event.
ZberteocCommented:
I think that is the problem. When you restore the database, which is also the publisher, the publication will be removed.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mannymsAuthor Commented:
Ahhhhh. ok. so I should probably script VIA SQL, the creation of the publication & the publication subscription on the Distributor server and script the local subscription on the subscriber server?
or would you recommend another method?
ZberteocCommented:
I would create a second database on the publisher server only with the tables you need to replicate and make that as publishing db, let say ETL_db but I would use a transnational replication to only update what changed.

Between your restore db and the ETL_db I would simply run some MERGE statements, ran from a SQL agent - maybe the same that does the restore but a second step - for each table which will take care of the data maintenance but only applying the differences, which will be picked up by the replication. This way the whole process would be faster.

Using MERGE statement:

https://www.simple-talk.com/sql/learn-sql-server/the-merge-statement-in-sql-server-2008/

would work the same for any SQL version 2008 and up.
mannymsAuthor Commented:
Thanks very much!
ZberteocCommented:
If you use MERGE you should use this example:
MERGE 
    SOURCE_DB.dbo.tbl AS src
USING 
    ETL_db.dbo.tbl AS trg
	   ON trg.PK_col = src.PK_col
WHEN MATCHED THEN
  UPDATE
    trg.col1=src.col1
    trg.col2=src.col2
    trg.col3=src.col3
    ...
    trg.coln=src.coln
WHEN NOT MATCHED THEN
  INSERT (PK_col, col1, col3, col3, ..., coln)
  VALUES (PK_col, col1, col3, col3, ..., coln)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE 
;

Open in new window

That statement ensures that:

1.

matching PK rows are updated from source to target

3.

PKs in source that don't exist in target are inserted

1.

PKs in target that don't exist in source are deleted
mannymsAuthor Commented:
I was wondering about the PKs. thanks for the quick assistance!
ZberteocCommented:
A more suitable MERGE would be one that only updates if any of the columns, or their NULL replacement valuees, are different:
MERGE 
    SOURCE_DB.dbo.tbl AS src
USING 
    ETL_db.dbo.tbl AS trg
	   ON trg.PK_col = src.PK_col
WHEN MATCHED AND
    (
		  ISNULL(trg.col1,'')		    <>ISNULL(src.col1,'')
	   or  ISNULL(trg.col2,0)		    <>ISNULL(src.col2,0)
	   or  ISNULL(trg.col3,'1900-01-01')   <>ISNULL(src.col3,'1900-01-01')
	   ...
	   or  ISNULL(trg.coln,'')		    <>ISNULL(src.coln,'')
    )
THEN
  UPDATE
    trg.col1=src.col1
    trg.col2=src.col2
    trg.col3=src.col3
    ...
    trg.coln=src.coln
WHEN NOT MATCHED THEN
  INSERT (PK_col, col1, col3, col3, ..., coln)
  VALUES (PK_col, col1, col3, col3, ..., coln)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE 
;

Open in new window

You need that ISNULL becaus you cannot compare a NULL value to anything. The NULL replacements values would correspond to the columns data type, varchar, number or date.
ZberteocCommented:
You will have to use the PK columns (or unique index) to join, and if the PK has multiple columns you will have to use them all with and clause:

MERGE 
    SOURCE_DB.dbo.tbl AS src
USING 
    ETL_db.dbo.tbl AS trg
	   ON  trg.PK_col = src.PK_col
	   AND trg.PK_co2 = src.PK_co2
	   ...
WHEN MATCHED AND
    (
		  ISNULL(trg.col1,'')		    <>ISNULL(src.col1,'')
	   or  ISNULL(trg.col2,0)		    <>ISNULL(src.col2,0)
	   or  ISNULL(trg.col3,'1900-01-01')   <>ISNULL(src.col3,'1900-01-01')
	   ...
	   or  ISNULL(trg.coln,'')		    <>ISNULL(src.coln,'')
    )
THEN
  UPDATE
    trg.col1=src.col1
    trg.col2=src.col2
    trg.col3=src.col3
    ...
    trg.coln=src.coln
WHEN NOT MATCHED THEN
  INSERT (PK_col1, PK_col2, col1, col3, col3, ..., coln)
  VALUES (PK_col1, PK_col2, col1, col3, col3, ..., coln)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE 
;

Open in new window

mannymsAuthor Commented:
How does this method compare to, say :
truncating all tables
dropping indexes,
performing bulk inserts
rebuilding the indexes

I'm trying to be conscious of the resources and maintenance windows and I have 12 other Databases to replicate, although they are much smaller and manageable. (less than 12 tables each.)
ZberteocCommented:
There is no comparation! :) That is not the way.

Even if you go on TRUNCATE and INSERT why dropping the indexes? Beside that it is not clear where would you do the truncate and bulk inserts? To which database?

I guess this entire issue is worth an new question.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.